import { randomUUID } from "node:crypto";
import pg from "pg";

import type { ExecutionReport, Milestone, TaskGroup, TaskGroupOverview, TaskItem, TaskStatus, Timeline } from "./contracts.ts";
import {
  TASKTREE_BACKUP_SCHEMA_VERSION,
  type TaskTreeBackupState,
  type TaskTreeSnapshot,
} from "./backup-contracts.ts";
import type { MdtodoImportPlan } from "./mdtodo-import.ts";

const { Pool } = pg;

type SchemaMigration = { id: string; statements: string[] };

const migrationLedgerStatement = `CREATE TABLE IF NOT EXISTS tasktree_schema_migrations (
  migration_id TEXT PRIMARY KEY, applied_at TIMESTAMPTZ NOT NULL DEFAULT now())`;
const schemaMigrations: SchemaMigration[] = [
  {
    id: "tasktree-20260716-v1",
    statements: [
      `CREATE TABLE IF NOT EXISTS tasktree_groups (
        id TEXT PRIMARY KEY, name TEXT NOT NULL, description TEXT NOT NULL DEFAULT '',
        created_at TIMESTAMPTZ NOT NULL DEFAULT now(), updated_at TIMESTAMPTZ NOT NULL DEFAULT now())`,
      `CREATE TABLE IF NOT EXISTS tasktree_tasks (
        id TEXT PRIMARY KEY, group_id TEXT NOT NULL REFERENCES tasktree_groups(id) ON DELETE CASCADE,
        parent_id TEXT REFERENCES tasktree_tasks(id) ON DELETE CASCADE, kind TEXT NOT NULL CHECK (kind IN ('task','subtask')),
        title TEXT NOT NULL, description TEXT NOT NULL DEFAULT '', status TEXT NOT NULL DEFAULT 'pending'
          CHECK (status IN ('pending','in_progress','completed','blocked')),
        start_at TIMESTAMPTZ, due_at TIMESTAMPTZ, sort_order INTEGER NOT NULL DEFAULT 0,
        created_at TIMESTAMPTZ NOT NULL DEFAULT now(), updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
        CHECK (due_at IS NULL OR start_at IS NULL OR due_at >= start_at))`,
      `CREATE TABLE IF NOT EXISTS tasktree_milestones (
        id TEXT PRIMARY KEY, group_id TEXT NOT NULL REFERENCES tasktree_groups(id) ON DELETE CASCADE,
        task_id TEXT REFERENCES tasktree_tasks(id) ON DELETE CASCADE, title TEXT NOT NULL,
        occurs_at TIMESTAMPTZ NOT NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT now())`,
      `CREATE TABLE IF NOT EXISTS tasktree_execution_reports (
        id TEXT PRIMARY KEY, task_id TEXT NOT NULL REFERENCES tasktree_tasks(id) ON DELETE CASCADE,
        title TEXT NOT NULL, body TEXT NOT NULL, status TEXT NOT NULL DEFAULT 'succeeded',
        created_at TIMESTAMPTZ NOT NULL DEFAULT now())`,
      `CREATE INDEX IF NOT EXISTS idx_tasktree_tasks_group_order ON tasktree_tasks(group_id, sort_order, created_at)`,
      `CREATE INDEX IF NOT EXISTS idx_tasktree_tasks_parent ON tasktree_tasks(parent_id)`,
      `CREATE INDEX IF NOT EXISTS idx_tasktree_reports_task ON tasktree_execution_reports(task_id, created_at DESC)`,
    ],
  },
  { id: "tasktree-20260717-v2-subsubtask", statements: [
    `ALTER TABLE tasktree_tasks DROP CONSTRAINT IF EXISTS tasktree_tasks_kind_check`,
    `ALTER TABLE tasktree_tasks ADD CONSTRAINT tasktree_tasks_kind_check CHECK (kind IN ('task','subtask','subsubtask'))`,
  ] },
  { id: "tasktree-20260718-v3-github-backup", statements: [
    `CREATE TABLE IF NOT EXISTS tasktree_backup_state (
    target TEXT PRIMARY KEY, pending BOOLEAN NOT NULL DEFAULT false,
    requested_at TIMESTAMPTZ, request_reason TEXT NOT NULL DEFAULT '',
    last_attempt_at TIMESTAMPTZ, last_success_at TIMESTAMPTZ,
    content_sha256 TEXT NOT NULL DEFAULT '', git_commit TEXT NOT NULL DEFAULT '',
    status TEXT NOT NULL DEFAULT 'never' CHECK (status IN ('never','pending','succeeded','failed')),
    error_code TEXT NOT NULL DEFAULT '', error_message TEXT NOT NULL DEFAULT '',
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now())`,
  ] },
  { id: "tasktree-20260721-v4-report-completes-task", statements: [
    `UPDATE tasktree_groups g SET updated_at=now()
      WHERE EXISTS (
        SELECT 1 FROM tasktree_tasks t
        JOIN tasktree_execution_reports r ON r.task_id=t.id
        WHERE t.group_id=g.id AND t.status <> 'completed'
      )`,
    `UPDATE tasktree_tasks t SET status='completed',updated_at=now()
      WHERE t.status <> 'completed'
        AND EXISTS (SELECT 1 FROM tasktree_execution_reports r WHERE r.task_id=t.id)`,
  ] },
];
const schemaMigrationIds = schemaMigrations.map((migration) => migration.id);
const schemaAdvisoryLockKeys = [0x48574c42, 0x54545245] as const;

export class TaskTreeStore {
  private readonly pool: pg.Pool;
  private ready = false;
  private schemaPromise: Promise<void> | null = null;

  constructor(databaseUrl: string) {
    if (!databaseUrl) throw new Error("TASKTREE_DATABASE_URL or DATABASE_URL is required");
    this.pool = new Pool({ connectionString: databaseUrl, max: 4, connectionTimeoutMillis: 5000, ssl: taskTreePostgresSsl(databaseUrl) });
  }

  async ensureSchema() {
    if (this.ready) return;
    this.schemaPromise ??= this.migrateSchema();
    try {
      await this.schemaPromise;
    } catch (error) {
      this.schemaPromise = null;
      throw error;
    }
  }

  private async migrateSchema() {
    if (await schemaIsCurrent(this.pool)) {
      this.ready = true;
      return;
    }
    const client = await this.pool.connect();
    try {
      await client.query("BEGIN");
      await client.query("SELECT pg_advisory_xact_lock($1, $2)", [...schemaAdvisoryLockKeys]);
      const ledger = await client.query("SELECT to_regclass('tasktree_schema_migrations') AS relation");
      if (ledger.rows[0]?.relation == null) await client.query(migrationLedgerStatement);
      const appliedResult = await client.query("SELECT migration_id FROM tasktree_schema_migrations");
      const applied = new Set(appliedResult.rows.map((row) => String(row.migration_id)));
      for (const migration of schemaMigrations) {
        if (applied.has(migration.id)) continue;
        try {
          for (const statement of migration.statements) await client.query(statement);
          await client.query("INSERT INTO tasktree_schema_migrations (migration_id) VALUES ($1)", [migration.id]);
        } catch (error) {
          if (error && typeof error === "object") Object.assign(error, { tasktreeMigrationId: migration.id });
          throw error;
        }
      }
      await client.query("COMMIT");
      this.ready = true;
    } catch (error) {
      await client.query("ROLLBACK").catch(() => {});
      throw error;
    } finally {
      client.release();
    }
  }

  async health() {
    await this.ensureSchema();
    const result = await this.pool.query("SELECT current_database() AS database, now() AS now");
    return { storage: "native-postgresql", schemaReady: true, database: result.rows[0]?.database, checkedAt: result.rows[0]?.now };
  }

  async listGroups(): Promise<TaskGroup[]> {
    await this.ensureSchema();
    const result = await this.pool.query("SELECT * FROM tasktree_groups ORDER BY updated_at DESC, name");
    return result.rows.map(groupRow);
  }

  async groupOverview(): Promise<TaskGroupOverview[]> {
    await this.ensureSchema();
    const result = await this.pool.query(`
      SELECT g.*,
        COUNT(DISTINCT t.id) FILTER (WHERE t.kind='task')::int AS task_count,
        COUNT(DISTINCT t.id) FILTER (WHERE t.kind='subtask')::int AS subtask_count,
        COUNT(DISTINCT t.id) FILTER (WHERE t.kind='subsubtask')::int AS subsubtask_count,
        COUNT(DISTINCT t.id) FILTER (WHERE t.status='completed')::int AS completed_count,
        COUNT(DISTINCT r.id)::int AS report_count,
        MIN(CASE
          WHEN t.id IS NULL THEN NULL
          WHEN t.start_at IS NOT NULL THEN t.start_at
          WHEN t.due_at IS NOT NULL THEN LEAST(t.created_at,t.due_at)
          ELSE t.created_at
        END) AS start_at,
        MAX(CASE
          WHEN t.id IS NULL THEN NULL
          WHEN t.due_at IS NOT NULL THEN t.due_at
          ELSE GREATEST(
            COALESCE(t.start_at,t.created_at),
            t.updated_at,
            COALESCE(r.created_at,t.updated_at)
          )
        END) AS due_at
      FROM tasktree_groups g
      LEFT JOIN tasktree_tasks t ON t.group_id=g.id
      LEFT JOIN tasktree_execution_reports r ON r.task_id=t.id
      GROUP BY g.id
      ORDER BY GREATEST(
        g.updated_at,
        COALESCE(MAX(t.updated_at),g.updated_at),
        COALESCE(MAX(r.created_at),g.updated_at),
        COALESCE((SELECT MAX(m.created_at) FROM tasktree_milestones m WHERE m.group_id=g.id),g.updated_at)
      ) DESC, g.name
    `);
    return result.rows.map(overviewRow);
  }

  async getGroup(id: string): Promise<TaskGroup | null> {
    await this.ensureSchema();
    const result = await this.pool.query("SELECT * FROM tasktree_groups WHERE id=$1", [id]);
    return result.rows[0] ? groupRow(result.rows[0]) : null;
  }

  async createGroup(name: string, description = ""): Promise<TaskGroup> {
    await this.ensureSchema();
    const result = await this.pool.query("INSERT INTO tasktree_groups (id,name,description) VALUES ($1,$2,$3) RETURNING *", [`tg_${randomUUID()}`, name, description]);
    return groupRow(result.rows[0]);
  }

  async deleteGroup(id: string): Promise<boolean> {
    await this.ensureSchema();
    return (await this.pool.query("DELETE FROM tasktree_groups WHERE id=$1", [id])).rowCount === 1;
  }

  async groupStats(groupId: string) {
    await this.ensureSchema();
    const result = await this.pool.query(`
      SELECT COUNT(*)::int AS total,
        COUNT(*) FILTER (WHERE status='completed')::int AS completed,
        COUNT(*) FILTER (WHERE status='in_progress')::int AS in_progress,
        COUNT(*) FILTER (WHERE status='pending')::int AS pending,
        COUNT(*) FILTER (WHERE status='blocked')::int AS blocked,
        COUNT(*) FILTER (WHERE kind='task')::int AS tasks,
        COUNT(*) FILTER (WHERE kind='subtask')::int AS subtasks,
        COUNT(*) FILTER (WHERE kind='subsubtask')::int AS subsubtasks,
        (SELECT COUNT(*)::int FROM tasktree_execution_reports r
          JOIN tasktree_tasks rt ON rt.id=r.task_id WHERE rt.group_id=$1) AS reports
      FROM tasktree_tasks WHERE group_id=$1
    `, [groupId]);
    const row = result.rows[0] ?? {};
    return {
      total: Number(row.total),
      completed: Number(row.completed),
      inProgress: Number(row.in_progress),
      pending: Number(row.pending),
      blocked: Number(row.blocked),
      tasks: Number(row.tasks),
      subtasks: Number(row.subtasks),
      subsubtasks: Number(row.subsubtasks),
      reports: Number(row.reports)
    };
  }

  async listTasks(groupId: string): Promise<TaskItem[]> {
    await this.ensureSchema();
    const result = await this.pool.query("SELECT * FROM tasktree_tasks WHERE group_id=$1 ORDER BY sort_order,created_at", [groupId]);
    return result.rows.map(taskRow);
  }

  async getTask(id: string): Promise<TaskItem | null> {
    await this.ensureSchema();
    const result = await this.pool.query("SELECT * FROM tasktree_tasks WHERE id=$1", [id]);
    return result.rows[0] ? taskRow(result.rows[0]) : null;
  }

  async createTask(input: { groupId: string; title: string; description?: string; startAt?: string; dueAt?: string; parentId?: string }): Promise<TaskItem> {
    return (await this.createTasks({ ...input, titles: [input.title] }))[0];
  }

  async createTasks(input: { groupId: string; titles: string[]; description?: string; startAt?: string; dueAt?: string; parentId?: string }): Promise<TaskItem[]> {
    await this.ensureSchema();
    ensureTimeRange(input.startAt ?? null, input.dueAt ?? null);
    const client = await this.pool.connect();
    try {
      await client.query("BEGIN");
      const group = await client.query("SELECT id FROM tasktree_groups WHERE id=$1 FOR UPDATE", [input.groupId]);
      if (!group.rows[0]) throw domainError("group_not_found", "taskgroup was not found");
      let kind: TaskItem["kind"] = "task";
      if (input.parentId) {
        const parent = await client.query("SELECT group_id,kind FROM tasktree_tasks WHERE id=$1", [input.parentId]);
        if (!parent.rows[0]) throw domainError("parent_not_found", "parent task was not found");
        if (parent.rows[0].group_id !== input.groupId) throw domainError("parent_group_mismatch", "parent task belongs to another taskgroup");
        if (parent.rows[0].kind === "subsubtask") throw domainError("maximum_depth_exceeded", "TaskTree supports task, subtask, and subsubtask only");
        kind = parent.rows[0].kind === "task" ? "subtask" : "subsubtask";
      }
      const orderResult = await client.query("SELECT COALESCE(MAX(sort_order),-1)::int AS value FROM tasktree_tasks WHERE group_id=$1", [input.groupId]);
      const firstOrder = Number(orderResult.rows[0]?.value ?? -1) + 1;
      const tasks: TaskItem[] = [];
      for (let index = 0; index < input.titles.length; index += 1) {
        const result = await client.query(
          `INSERT INTO tasktree_tasks (id,group_id,parent_id,kind,title,description,start_at,due_at,sort_order)
           VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9) RETURNING *`,
          [`tt_${randomUUID()}`, input.groupId, input.parentId ?? null, kind, input.titles[index], input.description ?? "", input.startAt ?? null, input.dueAt ?? null, firstOrder + index]
        );
        tasks.push(taskRow(result.rows[0]));
      }
      await client.query("UPDATE tasktree_groups SET updated_at=now() WHERE id=$1", [input.groupId]);
      await client.query("COMMIT");
      return tasks;
    } catch (error) {
      await client.query("ROLLBACK").catch(() => {});
      throw error;
    } finally {
      client.release();
    }
  }

  async updateTask(id: string, patch: { title?: string; description?: string; status?: TaskStatus; startAt?: string | null; dueAt?: string | null }): Promise<TaskItem | null> {
    await this.ensureSchema();
    const current = await this.pool.query("SELECT * FROM tasktree_tasks WHERE id=$1", [id]);
    if (!current.rows[0]) return null;
    const row = taskRow(current.rows[0]);
    const startAt = patch.startAt === undefined ? row.startAt : patch.startAt;
    const dueAt = patch.dueAt === undefined ? row.dueAt : patch.dueAt;
    ensureTimeRange(startAt, dueAt);
    const result = await this.pool.query(
      `UPDATE tasktree_tasks SET title=$2,description=$3,status=$4,start_at=$5,due_at=$6,updated_at=now() WHERE id=$1 RETURNING *`,
      [id, patch.title ?? row.title, patch.description ?? row.description, patch.status ?? row.status,
        startAt, dueAt]
    );
    await this.pool.query("UPDATE tasktree_groups SET updated_at=now() WHERE id=$1", [row.groupId]);
    return taskRow(result.rows[0]);
  }

  async deleteTask(id: string): Promise<boolean> {
    await this.ensureSchema();
    const current = await this.getTask(id);
    if (!current) return false;
    const deleted = (await this.pool.query("DELETE FROM tasktree_tasks WHERE id=$1", [id])).rowCount === 1;
    if (deleted) await this.pool.query("UPDATE tasktree_groups SET updated_at=now() WHERE id=$1", [current.groupId]);
    return deleted;
  }

  async completeTask(id: string): Promise<TaskItem | null> {
    await this.ensureSchema();
    const client = await this.pool.connect();
    try {
      await client.query("BEGIN");
      const current = await client.query("SELECT * FROM tasktree_tasks WHERE id=$1 FOR UPDATE", [id]);
      if (!current.rows[0]) {
        await client.query("ROLLBACK");
        return null;
      }
      const reports = await client.query("SELECT COUNT(*)::int AS count FROM tasktree_execution_reports WHERE task_id=$1", [id]);
      if (Number(reports.rows[0]?.count ?? 0) < 1) throw domainError("execution_report_required", "an execution report is required before completing a task");
      const result = await client.query("UPDATE tasktree_tasks SET status='completed',updated_at=now() WHERE id=$1 RETURNING *", [id]);
      await client.query("UPDATE tasktree_groups SET updated_at=now() WHERE id=$1", [result.rows[0].group_id]);
      await client.query("COMMIT");
      return taskRow(result.rows[0]);
    } catch (error) {
      await client.query("ROLLBACK").catch(() => {});
      throw error;
    } finally {
      client.release();
    }
  }

  async createMilestone(input: { groupId: string; title: string; occursAt: string; taskId?: string }): Promise<Milestone> {
    await this.ensureSchema();
    const client = await this.pool.connect();
    try {
      await client.query("BEGIN");
      const group = await client.query("SELECT id FROM tasktree_groups WHERE id=$1 FOR UPDATE", [input.groupId]);
      if (!group.rows[0]) throw domainError("group_not_found", "taskgroup was not found");
      if (input.taskId) {
        const task = await client.query("SELECT group_id FROM tasktree_tasks WHERE id=$1", [input.taskId]);
        if (!task.rows[0]) throw domainError("task_not_found", "task was not found");
        if (task.rows[0].group_id !== input.groupId) throw domainError("task_group_mismatch", "task belongs to another taskgroup");
      }
      const result = await client.query(
        "INSERT INTO tasktree_milestones (id,group_id,task_id,title,occurs_at) VALUES ($1,$2,$3,$4,$5) RETURNING *",
        [`tm_${randomUUID()}`, input.groupId, input.taskId ?? null, input.title, input.occursAt]
      );
      await client.query("UPDATE tasktree_groups SET updated_at=now() WHERE id=$1", [input.groupId]);
      await client.query("COMMIT");
      return milestoneRow(result.rows[0]);
    } catch (error) {
      await client.query("ROLLBACK").catch(() => {});
      throw error;
    } finally {
      client.release();
    }
  }

  async createReport(input: { taskId: string; title: string; body: string; status?: string }): Promise<ExecutionReport> {
    await this.ensureSchema();
    const client = await this.pool.connect();
    try {
      await client.query("BEGIN");
      const task = await client.query("SELECT group_id FROM tasktree_tasks WHERE id=$1 FOR UPDATE", [input.taskId]);
      if (!task.rows[0]) throw domainError("task_not_found", "task was not found");
      const result = await client.query(
        "INSERT INTO tasktree_execution_reports (id,task_id,title,body,status) VALUES ($1,$2,$3,$4,$5) RETURNING *",
        [`tr_${randomUUID()}`, input.taskId, input.title, input.body, input.status ?? "succeeded"]
      );
      await client.query("UPDATE tasktree_tasks SET status='completed',updated_at=now() WHERE id=$1", [input.taskId]);
      await client.query("UPDATE tasktree_groups SET updated_at=now() WHERE id=$1", [task.rows[0].group_id]);
      await client.query("COMMIT");
      return reportRow(result.rows[0]);
    } catch (error) {
      await client.query("ROLLBACK").catch(() => {});
      throw error;
    } finally {
      client.release();
    }
  }

  async listReports(taskId: string): Promise<ExecutionReport[]> {
    await this.ensureSchema();
    const result = await this.pool.query("SELECT * FROM tasktree_execution_reports WHERE task_id=$1 ORDER BY created_at DESC,id", [taskId]);
    return result.rows.map(reportRow);
  }

  async getReport(reportId: string): Promise<ExecutionReport | null> {
    await this.ensureSchema();
    const result = await this.pool.query("SELECT * FROM tasktree_execution_reports WHERE id=$1", [reportId]);
    return result.rows[0] ? reportRow(result.rows[0]) : null;
  }

  async writeReport(input: { taskId: string; title: string; body: string; status?: string }): Promise<{
    report: ExecutionReport;
    mutation: boolean;
    task: TaskItem;
    taskMutation: boolean;
  }> {
    await this.ensureSchema();
    const client = await this.pool.connect();
    try {
      await client.query("BEGIN");
      const taskResult = await client.query("SELECT * FROM tasktree_tasks WHERE id=$1 FOR UPDATE", [input.taskId]);
      if (!taskResult.rows[0]) throw domainError("task_not_found", "task was not found");
      const current = await client.query(
        "SELECT * FROM tasktree_execution_reports WHERE task_id=$1 AND title=$2 ORDER BY created_at DESC,id LIMIT 1",
        [input.taskId, input.title]
      );
      const status = input.status ?? "succeeded";
      const mutation = !current.rows[0] || current.rows[0].body !== input.body || current.rows[0].status !== status;
      const reportResult = !mutation
        ? current
        : current.rows[0]
          ? await client.query(
            "UPDATE tasktree_execution_reports SET body=$2,status=$3,created_at=now() WHERE id=$1 RETURNING *",
            [current.rows[0].id, input.body, status]
          )
          : await client.query(
            "INSERT INTO tasktree_execution_reports (id,task_id,title,body,status) VALUES ($1,$2,$3,$4,$5) RETURNING *",
            [`tr_${randomUUID()}`, input.taskId, input.title, input.body, status]
          );
      const taskMutation = taskResult.rows[0].status !== "completed";
      const completedTask = taskMutation
        ? await client.query("UPDATE tasktree_tasks SET status='completed',updated_at=now() WHERE id=$1 RETURNING *", [input.taskId])
        : taskResult;
      if (mutation || taskMutation) {
        await client.query("UPDATE tasktree_groups SET updated_at=now() WHERE id=$1", [taskResult.rows[0].group_id]);
      }
      await client.query("COMMIT");
      return {
        report: reportRow(reportResult.rows[0]),
        mutation,
        task: taskRow(completedTask.rows[0]),
        taskMutation,
      };
    } catch (error) {
      await client.query("ROLLBACK").catch(() => {});
      throw error;
    } finally {
      client.release();
    }
  }

  async importMdtodo(plan: MdtodoImportPlan) {
    await this.ensureSchema();
    const client = await this.pool.connect();
    const groupId = `tg_${randomUUID()}`;
    const taskIds = new Map<string, string>();
    try {
      await client.query("BEGIN");
      const groupResult = await client.query(
        "INSERT INTO tasktree_groups (id,name,description) VALUES ($1,$2,$3) RETURNING *",
        [groupId, plan.groupName, plan.groupDescription]
      );
      for (let index = 0; index < plan.tasks.length; index += 1) {
        const task = plan.tasks[index];
        const taskId = `tt_${randomUUID()}`;
        taskIds.set(task.sourceId, taskId);
        const parentId = task.parentSourceId ? taskIds.get(task.parentSourceId) ?? null : null;
        const parentTask = parentId ? plan.tasks.find((candidate) => candidate.sourceId === task.parentSourceId) : null;
        const kind = !parentId ? "task" : parentTask?.parentSourceId ? "subsubtask" : "subtask";
        await client.query(
          `INSERT INTO tasktree_tasks (id,group_id,parent_id,kind,title,description,status,start_at,due_at,sort_order)
           VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10)`,
          [taskId, groupId, parentId, kind, task.title, task.description,
            task.reports.length > 0 ? "completed" : task.status, task.startAt, task.dueAt, index]
        );
        for (const report of task.reports) {
          await client.query(
            "INSERT INTO tasktree_execution_reports (id,task_id,title,body,status,created_at) VALUES ($1,$2,$3,$4,$5,$6)",
            [`tr_${randomUUID()}`, taskId, report.title, report.body, report.status, report.createdAt]
          );
        }
      }
      await client.query("COMMIT");
      return { dryRun: false, sourcePath: plan.sourcePath, group: groupRow(groupResult.rows[0]), parserMode: plan.parserMode, summary: plan.summary, warnings: plan.warnings };
    } catch (error) {
      await client.query("ROLLBACK").catch(() => {});
      throw error;
    } finally {
      client.release();
    }
  }

  async timeline(groupId: string): Promise<Timeline | null> {
    await this.ensureSchema();
    const group = await this.getGroup(groupId);
    if (!group) return null;
    const [tasks, milestones, reports] = await Promise.all([
      this.pool.query("SELECT * FROM tasktree_tasks WHERE group_id=$1 ORDER BY sort_order,created_at", [groupId]),
      this.pool.query("SELECT * FROM tasktree_milestones WHERE group_id=$1 ORDER BY occurs_at", [groupId]),
      this.pool.query("SELECT r.* FROM tasktree_execution_reports r JOIN tasktree_tasks t ON t.id=r.task_id WHERE t.group_id=$1 ORDER BY r.created_at DESC", [groupId])
    ]);
    const taskItems = tasks.rows.map(taskRow);
    const reportItems = reports.rows.map(reportRow);
    return {
      group,
      tasks: projectTimelineTasks(taskItems, reportItems),
      milestones: milestones.rows.map(milestoneRow),
      reports: reportItems
    };
  }

  async exportSnapshot(): Promise<TaskTreeSnapshot> {
    await this.ensureSchema();
    const client = await this.pool.connect();
    try {
      await client.query("BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ READ ONLY");
      const groups = await client.query("SELECT * FROM tasktree_groups ORDER BY id");
      const tasks = await client.query("SELECT * FROM tasktree_tasks ORDER BY id");
      const milestones = await client.query("SELECT * FROM tasktree_milestones ORDER BY id");
      const reports = await client.query("SELECT * FROM tasktree_execution_reports ORDER BY id");
      await client.query("COMMIT");
      return {
        schemaVersion: TASKTREE_BACKUP_SCHEMA_VERSION,
        groups: groups.rows.map(groupRow),
        tasks: tasks.rows.map(taskRow),
        milestones: milestones.rows.map(milestoneRow),
        reports: reports.rows.map(reportRow),
      };
    } catch (error) {
      await client.query("ROLLBACK").catch(() => {});
      throw error;
    } finally {
      client.release();
    }
  }

  async restoreSnapshot(snapshot: TaskTreeSnapshot): Promise<void> {
    await this.ensureSchema();
    const client = await this.pool.connect();
    try {
      await client.query("BEGIN");
      await client.query("DELETE FROM tasktree_groups");
      for (const group of snapshot.groups) {
        await client.query(
          "INSERT INTO tasktree_groups (id,name,description,created_at,updated_at) VALUES ($1,$2,$3,$4,$5)",
          [group.id, group.name, group.description, group.createdAt, group.updatedAt],
        );
      }
      const pending = new Map(snapshot.tasks.map((task) => [task.id, task]));
      while (pending.size > 0) {
        let inserted = 0;
        for (const [id, task] of pending) {
          if (task.parentId !== null && pending.has(task.parentId)) continue;
          await client.query(
            `INSERT INTO tasktree_tasks
              (id,group_id,parent_id,kind,title,description,status,start_at,due_at,sort_order,created_at,updated_at)
             VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12)`,
            [task.id, task.groupId, task.parentId, task.kind, task.title, task.description, task.status,
              task.startAt, task.dueAt, task.sortOrder, task.createdAt, task.updatedAt],
          );
          pending.delete(id);
          inserted += 1;
        }
        if (inserted === 0) throw domainError("backup_reference_cycle", "backup snapshot contains a cyclic task hierarchy");
      }
      for (const milestone of snapshot.milestones) {
        await client.query(
          "INSERT INTO tasktree_milestones (id,group_id,task_id,title,occurs_at,created_at) VALUES ($1,$2,$3,$4,$5,$6)",
          [milestone.id, milestone.groupId, milestone.taskId, milestone.title, milestone.occursAt, milestone.createdAt],
        );
      }
      for (const report of snapshot.reports) {
        await client.query(
          "INSERT INTO tasktree_execution_reports (id,task_id,title,body,status,created_at) VALUES ($1,$2,$3,$4,$5,$6)",
          [report.id, report.taskId, report.title, report.body, report.status, report.createdAt],
        );
      }
      await client.query("COMMIT");
    } catch (error) {
      await client.query("ROLLBACK").catch(() => {});
      throw error;
    } finally {
      client.release();
    }
  }

  async requestBackup(target: string, reason: string): Promise<void> {
    await this.ensureSchema();
    await this.pool.query(
      `INSERT INTO tasktree_backup_state (target,pending,requested_at,request_reason,status)
       VALUES ($1,true,now(),$2,'pending')
       ON CONFLICT (target) DO UPDATE SET
         pending=true,requested_at=now(),request_reason=excluded.request_reason,status='pending',updated_at=now()`,
      [target, reason],
    );
  }

  async beginBackupAttempt(target: string): Promise<void> {
    await this.ensureSchema();
    await this.pool.query(
      `INSERT INTO tasktree_backup_state (target,last_attempt_at,status)
       VALUES ($1,now(),'pending')
       ON CONFLICT (target) DO UPDATE SET last_attempt_at=now(),status='pending',updated_at=now()`,
      [target],
    );
  }

  async finishBackupSuccess(target: string, contentSha256: string, gitCommit: string): Promise<void> {
    await this.ensureSchema();
    await this.pool.query(
      `INSERT INTO tasktree_backup_state
         (target,pending,last_attempt_at,last_success_at,content_sha256,git_commit,status,error_code,error_message)
       VALUES ($1,false,now(),now(),$2,$3,'succeeded','','')
       ON CONFLICT (target) DO UPDATE SET
         pending=false,last_success_at=now(),content_sha256=excluded.content_sha256,
         git_commit=excluded.git_commit,status='succeeded',error_code='',error_message='',updated_at=now()`,
      [target, contentSha256, gitCommit],
    );
  }

  async finishBackupFailure(target: string, code: string, message: string): Promise<void> {
    await this.ensureSchema();
    await this.pool.query(
      `INSERT INTO tasktree_backup_state
         (target,pending,last_attempt_at,status,error_code,error_message)
       VALUES ($1,true,now(),'failed',$2,$3)
       ON CONFLICT (target) DO UPDATE SET
         pending=true,status='failed',error_code=excluded.error_code,error_message=excluded.error_message,updated_at=now()`,
      [target, code, message.slice(0, 2000)],
    );
  }

  async backupState(target: string): Promise<TaskTreeBackupState> {
    await this.ensureSchema();
    const result = await this.pool.query("SELECT * FROM tasktree_backup_state WHERE target=$1", [target]);
    if (!result.rows[0]) {
      const created = await this.pool.query(
        "INSERT INTO tasktree_backup_state (target) VALUES ($1) ON CONFLICT (target) DO UPDATE SET target=excluded.target RETURNING *",
        [target],
      );
      return backupStateRow(created.rows[0]);
    }
    return backupStateRow(result.rows[0]);
  }

  async close() { await this.pool.end(); }
}

export function projectTimelineTasks(tasks: TaskItem[], reports: ExecutionReport[]): TaskItem[] {
  const latestReportAt = new Map<string, number>();
  for (const report of reports) {
    const createdAt = new Date(report.createdAt).valueOf();
    latestReportAt.set(report.taskId, Math.max(latestReportAt.get(report.taskId) ?? createdAt, createdAt));
  }

  const ranges = new Map<string, { start: number; due: number }>();
  const children = new Map<string, string[]>();
  for (const task of tasks) {
    const createdAt = new Date(task.createdAt).valueOf();
    const updatedAt = new Date(task.updatedAt).valueOf();
    const explicitStart = task.startAt === null ? null : new Date(task.startAt).valueOf();
    const explicitDue = task.dueAt === null ? null : new Date(task.dueAt).valueOf();
    const start = explicitStart ?? (explicitDue === null ? createdAt : Math.min(createdAt, explicitDue));
    const due = explicitDue ?? Math.max(start, updatedAt, latestReportAt.get(task.id) ?? updatedAt);
    ranges.set(task.id, { start, due });
    if (task.parentId !== null) children.set(task.parentId, [...(children.get(task.parentId) ?? []), task.id]);
  }

  const resolved = new Map<string, { start: number; due: number }>();
  const visiting = new Set<string>();
  const resolve = (taskId: string): { start: number; due: number } => {
    const existing = resolved.get(taskId);
    if (existing) return existing;
    const own = ranges.get(taskId);
    if (!own || visiting.has(taskId)) return own ?? { start: 0, due: 0 };
    visiting.add(taskId);
    const range = { ...own };
    for (const childId of children.get(taskId) ?? []) {
      const child = resolve(childId);
      range.start = Math.min(range.start, child.start);
      range.due = Math.max(range.due, child.due);
    }
    visiting.delete(taskId);
    resolved.set(taskId, range);
    return range;
  };

  return tasks.map((task) => {
    const range = resolve(task.id);
    return { ...task, startAt: new Date(range.start).toISOString(), dueAt: new Date(range.due).toISOString() };
  });
}

async function schemaIsCurrent(pool: pg.Pool): Promise<boolean> {
  const relation = await pool.query("SELECT to_regclass('tasktree_schema_migrations') AS relation");
  if (relation.rows[0]?.relation == null) return false;
  try {
    const applied = await pool.query(
      "SELECT COUNT(*)::int AS count FROM tasktree_schema_migrations WHERE migration_id = ANY($1::text[])",
      [schemaMigrationIds],
    );
    return Number(applied.rows[0]?.count ?? 0) === schemaMigrationIds.length;
  } catch (error: any) {
    if (error?.code === "42P01") return false;
    throw error;
  }
}

function groupRow(row: any): TaskGroup { return { id: row.id, name: row.name, description: row.description, createdAt: iso(row.created_at), updatedAt: iso(row.updated_at) }; }
function overviewRow(row: any): TaskGroupOverview { return { group: groupRow(row), taskCount: Number(row.task_count), subtaskCount: Number(row.subtask_count), subsubtaskCount: Number(row.subsubtask_count), completedCount: Number(row.completed_count), reportCount: Number(row.report_count), startAt: nullableIso(row.start_at), dueAt: nullableIso(row.due_at) }; }
function taskRow(row: any): TaskItem { return { id: row.id, groupId: row.group_id, parentId: row.parent_id, kind: row.kind, title: row.title, description: row.description, status: row.status, startAt: nullableIso(row.start_at), dueAt: nullableIso(row.due_at), sortOrder: row.sort_order, createdAt: iso(row.created_at), updatedAt: iso(row.updated_at) }; }
function milestoneRow(row: any): Milestone { return { id: row.id, groupId: row.group_id, taskId: row.task_id, title: row.title, occursAt: iso(row.occurs_at), createdAt: iso(row.created_at) }; }
function reportRow(row: any): ExecutionReport { return { id: row.id, taskId: row.task_id, title: row.title, body: row.body, status: row.status, createdAt: iso(row.created_at) }; }
function backupStateRow(row: any): TaskTreeBackupState {
  return {
    target: row.target,
    pending: row.pending,
    requestedAt: nullableIso(row.requested_at),
    requestReason: row.request_reason,
    lastAttemptAt: nullableIso(row.last_attempt_at),
    lastSuccessAt: nullableIso(row.last_success_at),
    contentSha256: row.content_sha256,
    gitCommit: row.git_commit,
    status: row.status,
    errorCode: row.error_code,
    errorMessage: row.error_message,
    updatedAt: iso(row.updated_at),
  };
}
function iso(value: unknown): string { return new Date(value as any).toISOString(); }
function nullableIso(value: unknown): string | null { return value == null ? null : iso(value); }
function domainError(code: string, message: string) { return Object.assign(new Error(message), { code }); }
function taskTreePostgresSsl(databaseUrl: string) {
  try {
    return new URL(databaseUrl).searchParams.get("sslmode") === "disable" ? false : { rejectUnauthorized: false };
  } catch {
    return { rejectUnauthorized: false };
  }
}
function ensureTimeRange(startAt: string | null, dueAt: string | null) {
  if (startAt !== null && dueAt !== null && new Date(startAt).valueOf() > new Date(dueAt).valueOf()) {
    throw domainError("invalid_time_range", "startAt must be before or equal to dueAt");
  }
}