import type { DatabaseSync } from "node:sqlite";
import { chromium } from "playwright-core";
import fs from "node:fs";
import os from "node:os";
import path from "node:path";

const now = () => new Date().toISOString();
const id = (prefix: string) => `${prefix}:${Date.now().toString(36)}:${Math.random().toString(36).slice(2, 8)}`;
const HOME = process.env.HOME || os.homedir();
const APP_SUPPORT = path.join(HOME, "Library", "Application Support", "DewLink");

export function ensureSchema(db: DatabaseSync): void {
  db.exec(`
    CREATE TABLE IF NOT EXISTS dev_srs (
      project_id TEXT PRIMARY KEY, overview TEXT NOT NULL DEFAULT '', users TEXT NOT NULL DEFAULT '',
      requirements TEXT NOT NULL DEFAULT '', modules TEXT NOT NULL DEFAULT '', flows TEXT NOT NULL DEFAULT '',
      acceptance TEXT NOT NULL DEFAULT '', constraints TEXT NOT NULL DEFAULT '', open_questions TEXT NOT NULL DEFAULT '',
      updated_at TEXT NOT NULL);
    CREATE TABLE IF NOT EXISTS dev_work_item (
      id TEXT PRIMARY KEY, project_id TEXT NOT NULL, kind TEXT NOT NULL DEFAULT 'task', title TEXT NOT NULL,
      status TEXT NOT NULL DEFAULT 'open', priority TEXT NOT NULL DEFAULT 'normal', module TEXT NOT NULL DEFAULT '',
      owner TEXT NOT NULL DEFAULT '', source TEXT NOT NULL DEFAULT 'manual', url TEXT NOT NULL DEFAULT '',
      evidence_path TEXT NOT NULL DEFAULT '', session_id TEXT NOT NULL DEFAULT '', summary TEXT NOT NULL DEFAULT '',
      created_at TEXT NOT NULL, updated_at TEXT NOT NULL);
    CREATE TABLE IF NOT EXISTS dev_memory (
      id TEXT PRIMARY KEY, project_id TEXT NOT NULL, kind TEXT NOT NULL DEFAULT 'fact', body TEXT NOT NULL,
      source TEXT NOT NULL DEFAULT '', confidence TEXT NOT NULL DEFAULT 'medium', status TEXT NOT NULL DEFAULT 'proposed',
      owner TEXT NOT NULL DEFAULT '', verified_at TEXT NOT NULL DEFAULT '', created_at TEXT NOT NULL, updated_at TEXT NOT NULL);
    CREATE TABLE IF NOT EXISTS dev_agent (
      id TEXT PRIMARY KEY, project_id TEXT NOT NULL, name TEXT NOT NULL, tool TEXT NOT NULL DEFAULT '',
      role TEXT NOT NULL DEFAULT 'worker', status TEXT NOT NULL DEFAULT 'idle', current_task TEXT NOT NULL DEFAULT '',
      session_id TEXT NOT NULL DEFAULT '', heartbeat_at TEXT NOT NULL DEFAULT '', created_at TEXT NOT NULL, updated_at TEXT NOT NULL);
    CREATE TABLE IF NOT EXISTS dev_decision (
      id TEXT PRIMARY KEY, project_id TEXT NOT NULL, title TEXT NOT NULL, status TEXT NOT NULL DEFAULT 'proposed',
      proposal TEXT NOT NULL DEFAULT '', rationale TEXT NOT NULL DEFAULT '', decided_by TEXT NOT NULL DEFAULT '',
      created_at TEXT NOT NULL, updated_at TEXT NOT NULL);
    CREATE TABLE IF NOT EXISTS dev_browser_session (
      id TEXT PRIMARY KEY, project_id TEXT NOT NULL, title TEXT NOT NULL DEFAULT 'Browser QA', url TEXT NOT NULL DEFAULT '',
      engine TEXT NOT NULL DEFAULT 'cef', status TEXT NOT NULL DEFAULT 'runtime_missing', last_capture_path TEXT NOT NULL DEFAULT '',
      last_error TEXT NOT NULL DEFAULT '', created_at TEXT NOT NULL, updated_at TEXT NOT NULL);
    CREATE INDEX IF NOT EXISTS ix_dev_work_project ON dev_work_item(project_id, kind, status, updated_at);
    CREATE INDEX IF NOT EXISTS ix_dev_memory_project ON dev_memory(project_id, status, updated_at);
    CREATE INDEX IF NOT EXISTS ix_dev_agent_project ON dev_agent(project_id, status, updated_at);
    CREATE INDEX IF NOT EXISTS ix_dev_decision_project ON dev_decision(project_id, status, updated_at);
    CREATE INDEX IF NOT EXISTS ix_dev_browser_project ON dev_browser_session(project_id, updated_at);
  `);
}

function projectId(db: DatabaseSync, slug: string): string {
  const p = db.prepare(`SELECT id FROM project WHERE slug=?`).get(slug) as any;
  if (!p?.id) throw new Error(`project '${slug}' not found`);
  return p.id;
}

export function devProjectSrsGet(db: DatabaseSync, a: { project_slug: string }) {
  ensureSchema(db);
  const pid = projectId(db, a.project_slug);
  return db.prepare(`SELECT * FROM dev_srs WHERE project_id=?`).get(pid) ?? null;
}

export function devProjectSrsUpsert(db: DatabaseSync, a: { project_slug: string; overview?: string; requirements?: string; modules?: string; acceptance?: string; open_questions?: string }) {
  ensureSchema(db);
  const pid = projectId(db, a.project_slug);
  const current = db.prepare(`SELECT * FROM dev_srs WHERE project_id=?`).get(pid) as any;
  const row = {
    overview: a.overview ?? current?.overview ?? "",
    requirements: a.requirements ?? current?.requirements ?? "",
    modules: a.modules ?? current?.modules ?? "",
    acceptance: a.acceptance ?? current?.acceptance ?? "",
    open_questions: a.open_questions ?? current?.open_questions ?? "",
  };
  db.prepare(`
    INSERT INTO dev_srs(project_id, overview, users, requirements, modules, flows, acceptance, constraints, open_questions, updated_at)
    VALUES(?,?,?,?,?,?,?,?,?,?)
    ON CONFLICT(project_id) DO UPDATE SET overview=excluded.overview, requirements=excluded.requirements,
      modules=excluded.modules, acceptance=excluded.acceptance, open_questions=excluded.open_questions, updated_at=excluded.updated_at`)
    .run(pid, row.overview, "", row.requirements, row.modules, "", row.acceptance, "", row.open_questions, now());
  return { ok: true, project_slug: a.project_slug };
}

export function devTaskCreate(db: DatabaseSync, a: { project_slug: string; title: string; priority?: string; module?: string; owner?: string; summary?: string }) {
  return createWorkItem(db, { ...a, kind: "task", source: "agent" });
}

export function devBugCreate(db: DatabaseSync, a: { project_slug: string; title: string; priority?: string; module?: string; owner?: string; url?: string; evidence_path?: string; summary?: string }) {
  return createWorkItem(db, { ...a, kind: "bug", source: "agent" });
}

export function devWorkList(db: DatabaseSync, a: { project_slug: string; kind?: string; status?: string; limit?: number }) {
  ensureSchema(db);
  const pid = projectId(db, a.project_slug);
  return db.prepare(`
    SELECT * FROM dev_work_item
    WHERE project_id=? AND (?='' OR kind=?) AND (?='' OR status=?)
    ORDER BY updated_at DESC LIMIT ?`)
    .all(pid, a.kind ?? "", a.kind ?? "", a.status ?? "", a.status ?? "", a.limit ?? 50);
}

function createWorkItem(db: DatabaseSync, a: { project_slug: string; kind: string; title: string; priority?: string; module?: string; owner?: string; source?: string; url?: string; evidence_path?: string; session_id?: string; summary?: string }) {
  ensureSchema(db);
  const pid = projectId(db, a.project_slug);
  const wid = id(`dev:${a.kind}`);
  const ts = now();
  db.prepare(`
    INSERT INTO dev_work_item(id, project_id, kind, title, status, priority, module, owner, source, url, evidence_path, session_id, summary, created_at, updated_at)
    VALUES(?,?,?,?, 'open', ?,?,?,?,?,?,?,?,?,?)`)
    .run(wid, pid, a.kind, a.title, a.priority ?? "normal", a.module ?? "", a.owner ?? "", a.source ?? "agent",
      a.url ?? "", a.evidence_path ?? "", a.session_id ?? "", a.summary ?? "", ts, ts);
  return { id: wid, project_slug: a.project_slug, kind: a.kind };
}

export function devMemoryPropose(db: DatabaseSync, a: { project_slug: string; kind?: string; body: string; source?: string; owner?: string; confidence?: string }) {
  ensureSchema(db);
  const pid = projectId(db, a.project_slug);
  const mid = id("mem");
  const ts = now();
  db.prepare(`
    INSERT INTO dev_memory(id, project_id, kind, body, source, confidence, status, owner, verified_at, created_at, updated_at)
    VALUES(?,?,?,?,?,?, 'proposed', ?, '', ?, ?)`)
    .run(mid, pid, a.kind ?? "fact", a.body, a.source ?? "agent", a.confidence ?? "medium", a.owner ?? "", ts, ts);
  return { id: mid, project_slug: a.project_slug, status: "proposed" };
}

export function devMemoryApprove(db: DatabaseSync, a: { memory_id: string; owner?: string }) {
  ensureSchema(db);
  const ts = now();
  const r = db.prepare(`UPDATE dev_memory SET status='approved', owner=COALESCE(?, owner), verified_at=?, updated_at=? WHERE id=?`)
    .run(a.owner ?? null, ts, ts, a.memory_id);
  return { ok: (r.changes ?? 0) > 0 };
}

export function devMemoryList(db: DatabaseSync, a: { project_slug: string; status?: string; limit?: number }) {
  ensureSchema(db);
  const pid = projectId(db, a.project_slug);
  return db.prepare(`SELECT * FROM dev_memory WHERE project_id=? AND (?='' OR status=?) ORDER BY updated_at DESC LIMIT ?`)
    .all(pid, a.status ?? "", a.status ?? "", a.limit ?? 50);
}

export function devAgentRegister(db: DatabaseSync, a: { project_slug: string; name: string; tool: string; role?: string; status?: string; current_task?: string; session_id?: string }) {
  ensureSchema(db);
  const pid = projectId(db, a.project_slug);
  const aid = `agent:${pid}:${a.tool}:${a.name}`.toLowerCase().replace(/\s+/g, "-");
  const ts = now();
  db.prepare(`
    INSERT INTO dev_agent(id, project_id, name, tool, role, status, current_task, session_id, heartbeat_at, created_at, updated_at)
    VALUES(?,?,?,?,?,?,?,?,?,?,?)
    ON CONFLICT(id) DO UPDATE SET role=excluded.role, status=excluded.status, current_task=excluded.current_task,
      session_id=excluded.session_id, heartbeat_at=excluded.heartbeat_at, updated_at=excluded.updated_at`)
    .run(aid, pid, a.name, a.tool, a.role ?? "worker", a.status ?? "idle", a.current_task ?? "", a.session_id ?? "", ts, ts, ts);
  return { id: aid, project_slug: a.project_slug };
}

export function devAgentHeartbeat(db: DatabaseSync, a: { agent_id: string; status?: string; current_task?: string }) {
  ensureSchema(db);
  const ts = now();
  const r = db.prepare(`UPDATE dev_agent SET status=COALESCE(?, status), current_task=COALESCE(?, current_task), heartbeat_at=?, updated_at=? WHERE id=?`)
    .run(a.status ?? null, a.current_task ?? null, ts, ts, a.agent_id);
  return { ok: (r.changes ?? 0) > 0 };
}

export function devDecisionPropose(db: DatabaseSync, a: { project_slug: string; title: string; proposal: string; rationale?: string }) {
  ensureSchema(db);
  const pid = projectId(db, a.project_slug);
  const did = id("dec");
  const ts = now();
  db.prepare(`INSERT INTO dev_decision(id, project_id, title, status, proposal, rationale, decided_by, created_at, updated_at)
              VALUES(?,?,?, 'proposed', ?, ?, '', ?, ?)`)
    .run(did, pid, a.title, a.proposal, a.rationale ?? "", ts, ts);
  return { id: did, project_slug: a.project_slug, status: "proposed" };
}

export function devDecisionList(db: DatabaseSync, a: { project_slug: string; status?: string; limit?: number }) {
  ensureSchema(db);
  const pid = projectId(db, a.project_slug);
  return db.prepare(`SELECT * FROM dev_decision WHERE project_id=? AND (?='' OR status=?) ORDER BY updated_at DESC LIMIT ?`)
    .all(pid, a.status ?? "", a.status ?? "", a.limit ?? 30);
}

export function devBrowserSessionUpdate(db: DatabaseSync, a: { project_slug: string; url?: string; title?: string; status?: string; last_capture_path?: string; last_error?: string }) {
  ensureSchema(db);
  const pid = projectId(db, a.project_slug);
  const bid = `browser:${pid}`;
  const ts = now();
  db.prepare(`
    INSERT INTO dev_browser_session(id, project_id, title, url, engine, status, last_capture_path, last_error, created_at, updated_at)
    VALUES(?,?,?,?, 'cef', ?,?,?,?,?)
    ON CONFLICT(id) DO UPDATE SET title=excluded.title, url=excluded.url, status=excluded.status,
      last_capture_path=excluded.last_capture_path, last_error=excluded.last_error, updated_at=excluded.updated_at`)
    .run(bid, pid, a.title ?? "Browser QA", a.url ?? "", a.status ?? "runtime_missing", a.last_capture_path ?? "", a.last_error ?? "", ts, ts);
  return { id: bid, project_slug: a.project_slug };
}

export function devBugCaptureCreate(db: DatabaseSync, a: { project_slug: string; title: string; url?: string; note?: string; evidence_path?: string; session_id?: string }) {
  devBrowserSessionUpdate(db, { project_slug: a.project_slug, url: a.url, status: "captured", last_capture_path: a.evidence_path });
  return devBugCreate(db, { project_slug: a.project_slug, title: a.title, priority: "high", module: "Browser QA", url: a.url, evidence_path: a.evidence_path, session_id: a.session_id, summary: a.note });
}

export async function devBrowserScan(db: DatabaseSync, a: { project_slug: string; url: string; wait_ms?: number }) {
  ensureSchema(db);
  const pid = projectId(db, a.project_slug);
  const target = normalizeUrl(a.url);
  const stamp = new Date().toISOString().replace(/[:.]/g, "-");
  const evidenceDir = path.join(APP_SUPPORT, "BrowserQA", a.project_slug, stamp);
  fs.mkdirSync(evidenceDir, { recursive: true });
  const screenshotPath = path.join(evidenceDir, "screenshot.png");
  const evidencePath = path.join(evidenceDir, "evidence.json");
  const profileDir = path.join(APP_SUPPORT, "BrowserQA", "_profiles", a.project_slug);

  const consoleMessages: any[] = [];
  const pageErrors: string[] = [];
  const failedRequests: any[] = [];
  const badResponses: any[] = [];
  let finalUrl = target;
  let title = "";
  let navigationError = "";
  let status = "scanned";

  try {
    const context = await chromium.launchPersistentContext(profileDir, {
      channel: "chrome",
      headless: process.env.DEWX_BROWSER_QA_HEADFUL !== "1",
      viewport: { width: 1440, height: 1000 },
      args: ["--no-first-run", "--no-default-browser-check"],
    });
    const page = context.pages()[0] ?? (await context.newPage());
    page.on("console", (msg) => {
      if (["error", "warning"].includes(msg.type())) {
        consoleMessages.push({ type: msg.type(), text: msg.text(), location: msg.location() });
      }
    });
    page.on("pageerror", (err) => pageErrors.push(err.message));
    page.on("requestfailed", (request) => {
      failedRequests.push({ url: request.url(), method: request.method(), failure: request.failure()?.errorText ?? "" });
    });
    page.on("response", (response) => {
      const s = response.status();
      if (s >= 400) badResponses.push({ url: response.url(), status: s, statusText: response.statusText() });
    });
    try {
      await page.goto(target, { waitUntil: "domcontentloaded", timeout: 45_000 });
      await page.waitForLoadState("networkidle", { timeout: a.wait_ms ?? 5_000 }).catch(() => {});
    } catch (e: any) {
      navigationError = e?.message ?? String(e);
      status = "failed";
    }
    finalUrl = page.url();
    title = await page.title().catch(() => "");
    await page.screenshot({ path: screenshotPath, fullPage: true }).catch(() => {});
    await context.close();
  } catch (e: any) {
    navigationError = e?.message ?? String(e);
    status = "failed";
  }

  const issueCount = consoleMessages.length + pageErrors.length + failedRequests.length + badResponses.length + (navigationError ? 1 : 0);
  const evidence = {
    project_slug: a.project_slug,
    url: target,
    final_url: finalUrl,
    title,
    status,
    issue_count: issueCount,
    navigation_error: navigationError,
    console_messages: consoleMessages,
    page_errors: pageErrors,
    failed_requests: failedRequests,
    bad_responses: badResponses,
    screenshot_path: fs.existsSync(screenshotPath) ? screenshotPath : "",
    captured_at: now(),
  };
  fs.writeFileSync(evidencePath, JSON.stringify(evidence, null, 2));

  const ts = now();
  db.prepare(`
    INSERT INTO dev_browser_session(id, project_id, title, url, engine, status, last_capture_path, last_error, created_at, updated_at)
    VALUES(?,?,?,?, 'playwright-chromium', ?,?,?,?,?)
    ON CONFLICT(id) DO UPDATE SET title=excluded.title, url=excluded.url, engine=excluded.engine,
      status=excluded.status, last_capture_path=excluded.last_capture_path, last_error=excluded.last_error, updated_at=excluded.updated_at`)
    .run(`browser:${pid}`, pid, title || "Browser QA", finalUrl || target, status, evidencePath, navigationError, ts, ts);

  let bug: any = null;
  if (issueCount > 0) {
    bug = createWorkItem(db, {
      project_slug: a.project_slug,
      kind: "bug",
      title: `Browser QA found ${issueCount} issue${issueCount === 1 ? "" : "s"} on ${title || finalUrl || target}`,
      priority: navigationError || pageErrors.length || failedRequests.length ? "high" : "normal",
      module: "Browser QA",
      owner: "unassigned",
      source: "browser-scan",
      url: finalUrl || target,
      evidence_path: evidencePath,
      summary: browserIssueSummary(evidence),
    });
  }
  return { ok: true, status, issue_count: issueCount, evidence_path: evidencePath, screenshot_path: evidence.screenshot_path, bug };
}

export function devHealthSnapshot(db: DatabaseSync, a: { project_slug?: string }) {
  ensureSchema(db);
  const counts = {
    projects: (db.prepare(`SELECT COUNT(*) AS n FROM project`).get() as any)?.n ?? 0,
    open_bugs: (db.prepare(`SELECT COUNT(*) AS n FROM dev_work_item WHERE kind='bug' AND status!='done'`).get() as any)?.n ?? 0,
    open_tasks: (db.prepare(`SELECT COUNT(*) AS n FROM dev_work_item WHERE kind='task' AND status!='done'`).get() as any)?.n ?? 0,
    proposed_memory: (db.prepare(`SELECT COUNT(*) AS n FROM dev_memory WHERE status='proposed'`).get() as any)?.n ?? 0,
    runtime: "cef_required",
  };
  return { project_slug: a.project_slug ?? null, counts };
}

function normalizeUrl(input: string): string {
  const trimmed = input.trim();
  if (!trimmed) throw new Error("url is required");
  const url = /^https?:\/\//i.test(trimmed) ? trimmed : `https://${trimmed}`;
  const parsed = new URL(url);
  if (!["http:", "https:"].includes(parsed.protocol)) throw new Error("only http/https URLs are allowed");
  return parsed.toString();
}

function browserIssueSummary(evidence: any): string {
  const parts: string[] = [];
  if (evidence.navigation_error) parts.push(`Navigation: ${evidence.navigation_error}`);
  if (evidence.page_errors.length) parts.push(`Page errors: ${evidence.page_errors.slice(0, 3).join(" | ")}`);
  if (evidence.failed_requests.length) parts.push(`Failed requests: ${evidence.failed_requests.slice(0, 3).map((r: any) => r.url).join(" | ")}`);
  if (evidence.bad_responses.length) parts.push(`HTTP errors: ${evidence.bad_responses.slice(0, 3).map((r: any) => `${r.status} ${r.url}`).join(" | ")}`);
  if (evidence.console_messages.length) parts.push(`Console: ${evidence.console_messages.slice(0, 3).map((m: any) => m.text).join(" | ")}`);
  return parts.join("\n");
}
