import Database from "better-sqlite3";
import { mkdirSync } from "node:fs";
import { dirname, resolve } from "node:path";

const DB_PATH = process.env.DB_PATH ?? "./data/bot.db";

export type DB = Database.Database;

const SCHEMA = `
PRAGMA journal_mode = WAL;
PRAGMA foreign_keys = ON;

CREATE TABLE IF NOT EXISTS sessions (
  id TEXT PRIMARY KEY,
  visitor_id TEXT,
  current_block_id TEXT,
  status TEXT NOT NULL DEFAULT 'active',  -- active | completed | abandoned
  started_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  completed_at TEXT,
  utm_source TEXT,
  utm_medium TEXT,
  utm_campaign TEXT,
  user_agent TEXT
);

CREATE TABLE IF NOT EXISTS session_vars (
  session_id TEXT NOT NULL,
  key TEXT NOT NULL,
  value TEXT,
  PRIMARY KEY (session_id, key),
  FOREIGN KEY (session_id) REFERENCES sessions(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS messages (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  session_id TEXT NOT NULL,
  block_id TEXT,
  role TEXT NOT NULL,  -- bot | user | system
  content TEXT NOT NULL,
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (session_id) REFERENCES sessions(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS events (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  session_id TEXT NOT NULL,
  block_id TEXT,
  type TEXT NOT NULL,  -- block_viewed | block_answered | session_started | session_completed | session_abandoned | webhook_called
  payload TEXT,        -- JSON
  created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (session_id) REFERENCES sessions(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_sessions_status ON sessions(status);
CREATE INDEX IF NOT EXISTS idx_sessions_started ON sessions(started_at);
CREATE INDEX IF NOT EXISTS idx_session_vars_session ON session_vars(session_id);
CREATE INDEX IF NOT EXISTS idx_messages_session ON messages(session_id);
CREATE INDEX IF NOT EXISTS idx_events_session ON events(session_id);
CREATE INDEX IF NOT EXISTS idx_events_block_type ON events(block_id, type);
`;

export function initDatabase(): DB {
  mkdirSync(dirname(resolve(DB_PATH)), { recursive: true });
  const db = new Database(DB_PATH);
  db.exec(SCHEMA);
  return db;
}

// ─── Helpers tipados ────────────────────────────────────────

export function createSession(
  db: DB,
  args: {
    id: string;
    visitor_id?: string;
    start_block_id: string;
    utm?: { source?: string; medium?: string; campaign?: string };
    user_agent?: string;
  },
): void {
  db.prepare(
    `INSERT INTO sessions
     (id, visitor_id, current_block_id, utm_source, utm_medium, utm_campaign, user_agent)
     VALUES (?, ?, ?, ?, ?, ?, ?)`,
  ).run(
    args.id,
    args.visitor_id ?? null,
    args.start_block_id,
    args.utm?.source ?? null,
    args.utm?.medium ?? null,
    args.utm?.campaign ?? null,
    args.user_agent ?? null,
  );
}

export function getSession(db: DB, id: string) {
  return db
    .prepare(`SELECT * FROM sessions WHERE id = ?`)
    .get(id) as
    | {
        id: string;
        visitor_id: string | null;
        current_block_id: string;
        status: string;
        started_at: string;
        completed_at: string | null;
      }
    | undefined;
}

export function updateSessionBlock(
  db: DB,
  sessionId: string,
  blockId: string,
): void {
  db.prepare(
    `UPDATE sessions SET current_block_id = ? WHERE id = ?`,
  ).run(blockId, sessionId);
}

export function completeSession(db: DB, sessionId: string): void {
  db.prepare(
    `UPDATE sessions SET status = 'completed', completed_at = CURRENT_TIMESTAMP WHERE id = ?`,
  ).run(sessionId);
}

export function setVar(
  db: DB,
  sessionId: string,
  key: string,
  value: unknown,
): void {
  const v = typeof value === "string" ? value : JSON.stringify(value);
  db.prepare(
    `INSERT INTO session_vars (session_id, key, value) VALUES (?, ?, ?)
     ON CONFLICT(session_id, key) DO UPDATE SET value = excluded.value`,
  ).run(sessionId, key, v);
}

export function getVars(
  db: DB,
  sessionId: string,
): Record<string, string> {
  const rows = db
    .prepare(`SELECT key, value FROM session_vars WHERE session_id = ?`)
    .all(sessionId) as Array<{ key: string; value: string }>;
  return Object.fromEntries(rows.map((r) => [r.key, r.value]));
}

export function appendMessage(
  db: DB,
  sessionId: string,
  role: "bot" | "user" | "system",
  content: string,
  blockId?: string,
): void {
  db.prepare(
    `INSERT INTO messages (session_id, block_id, role, content) VALUES (?, ?, ?, ?)`,
  ).run(sessionId, blockId ?? null, role, content);
}

export function getMessages(
  db: DB,
  sessionId: string,
  limit = 50,
): Array<{ role: string; content: string; block_id: string | null }> {
  return db
    .prepare(
      `SELECT role, content, block_id FROM messages
       WHERE session_id = ? ORDER BY id DESC LIMIT ?`,
    )
    .all(sessionId, limit)
    .reverse() as Array<{ role: string; content: string; block_id: string | null }>;
}

export function logEvent(
  db: DB,
  sessionId: string,
  type: string,
  blockId?: string,
  payload?: unknown,
): void {
  db.prepare(
    `INSERT INTO events (session_id, block_id, type, payload) VALUES (?, ?, ?, ?)`,
  ).run(sessionId, blockId ?? null, type, payload ? JSON.stringify(payload) : null);
}
