import { db, transaction } from "./db";
import {
  isClosedStatus,
  type Issue,
  type IssueLabel,
  type IssuePatch,
  type IssueSummary,
  type Member,
  type NewIssueInput,
  type Status,
} from "./issue-meta";

export type { Issue, IssueLabel, IssueSummary, Member };

export type Comment = { id: number; author: string; body: string; createdAt: number };
export type IssueEvent = {
  id: number;
  actor: string;
  field: string;
  oldValue: string | null;
  newValue: string | null;
  createdAt: number;
};

type IssueRow = {
  id: number;
  number: number;
  title: string;
  body?: string;
  author_id: number;
  author: string;
  assignee: string | null;
  state: "open" | "closed";
  status: Status;
  priority: number;
  assignee_id: number | null;
  estimate: number | null;
  due_date: string | null;
  sort_order: number;
  created_at: number;
  updated_at: number;
  comment_count: number;
};

const COLUMNS = `i.id, i.number, i.title, i.author_id, i.state, i.status, i.priority, i.assignee_id, i.estimate,
  i.due_date, i.sort_order, i.created_at, i.updated_at, u.username AS author, a.username AS assignee,
  (SELECT COUNT(*) FROM issue_comments c WHERE c.issue_id = i.id) AS comment_count`;

const FROM = `FROM issues i JOIN users u ON u.id = i.author_id LEFT JOIN users a ON a.id = i.assignee_id`;

const toSummary = (r: IssueRow, labels: IssueLabel[]): IssueSummary => ({
  id: r.id,
  number: r.number,
  title: r.title,
  authorId: r.author_id,
  author: r.author,
  status: r.status,
  priority: r.priority,
  assigneeId: r.assignee_id,
  assignee: r.assignee,
  estimate: r.estimate,
  dueDate: r.due_date,
  sortOrder: r.sort_order,
  labels,
  createdAt: r.created_at,
  updatedAt: r.updated_at,
  commentCount: r.comment_count,
});

function labelsByIssue(where: string, ...params: number[]) {
  const rows = db
    .prepare(
      `SELECT il.issue_id AS issueId, l.id, l.name, l.color FROM issue_labels il
       JOIN labels l ON l.id = il.label_id JOIN issues i ON i.id = il.issue_id WHERE ${where} ORDER BY l.name`,
    )
    .all(...params) as (IssueLabel & { issueId: number })[];
  const map = new Map<number, IssueLabel[]>();
  for (const { issueId, ...label } of rows) {
    if (!map.has(issueId)) map.set(issueId, []);
    map.get(issueId)!.push(label);
  }
  return map;
}

/** Every issue in a repository, without descriptions. Filtering and grouping happen in the browser. */
export function listIssues(repoId: number) {
  const rows = db.prepare(`SELECT ${COLUMNS} ${FROM} WHERE i.repo_id = ? ORDER BY i.number DESC`).all(repoId) as IssueRow[];
  const labels = labelsByIssue("i.repo_id = ?", repoId);
  return rows.map((r) => toSummary(r, labels.get(r.id) ?? []));
}

export function openIssueCount(repoId: number) {
  return (db.prepare("SELECT COUNT(*) AS n FROM issues WHERE repo_id = ? AND state = 'open'").get(repoId) as { n: number }).n;
}

export function getIssue(repoId: number, number: number): Issue | null {
  const row = db
    .prepare(`SELECT ${COLUMNS}, i.body ${FROM} WHERE i.repo_id = ? AND i.number = ?`)
    .get(repoId, number) as IssueRow | undefined;
  if (!row) return null;
  const labels = labelsByIssue("i.id = ?", row.id).get(row.id) ?? [];
  return { ...toSummary(row, labels), body: row.body ?? "", state: row.state };
}

export function listComments(issueId: number) {
  return db
    .prepare(
      `SELECT c.id, u.username AS author, c.body, c.created_at AS createdAt FROM issue_comments c
       JOIN users u ON u.id = c.author_id WHERE c.issue_id = ? ORDER BY c.id`,
    )
    .all(issueId) as Comment[];
}

export function listEvents(issueId: number) {
  return db
    .prepare(
      `SELECT e.id, u.username AS actor, e.field, e.old_value AS oldValue, e.new_value AS newValue, e.created_at AS createdAt
       FROM issue_events e JOIN users u ON u.id = e.actor_id WHERE e.issue_id = ? ORDER BY e.id`,
    )
    .all(issueId) as IssueEvent[];
}

// node:sqlite rows have a null prototype, which can't be passed to client components.
const plain = <T extends object>(rows: unknown[]) => rows.map((r) => ({ ...(r as T) }));

/** People who can be assigned: the owner and the collaborators. */
export function listMembers(repoId: number, ownerId: number) {
  return plain<Member>(
    db
      .prepare(
        `SELECT id, username, name FROM users
         WHERE id = ? OR id IN (SELECT user_id FROM collaborators WHERE repo_id = ?) ORDER BY username`,
      )
      .all(ownerId, repoId),
  );
}

export function createIssue(repoId: number, authorId: number, input: NewIssueInput) {
  return transaction(() => {
    const { next } = db
      .prepare("SELECT COALESCE(MAX(number), 0) + 1 AS next FROM issues WHERE repo_id = ?")
      .get(repoId) as { next: number };
    const now = Date.now();
    const status = input.status ?? "todo";
    const { lastInsertRowid } = db
      .prepare(
        `INSERT INTO issues (repo_id, number, title, body, author_id, state, status, priority, assignee_id, estimate,
         due_date, sort_order, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      )
      .run(
        repoId,
        next,
        input.title,
        input.body,
        authorId,
        isClosedStatus(status) ? "closed" : "open",
        status,
        input.priority ?? 0,
        input.assigneeId ?? null,
        input.estimate ?? null,
        input.dueDate ?? null,
        -now,
        now,
        now,
      );
    const insert = db.prepare("INSERT INTO issue_labels (issue_id, label_id) VALUES (?, ?)");
    for (const labelId of input.labelIds ?? []) insert.run(lastInsertRowid, labelId);
    return next;
  });
}

export function addComment(issueId: number, data: { body: string; authorId: number }) {
  const now = Date.now();
  db.prepare("INSERT INTO issue_comments (issue_id, author_id, body, created_at) VALUES (?, ?, ?, ?)").run(
    issueId,
    data.authorId,
    data.body,
    now,
  );
  db.prepare("UPDATE issues SET updated_at = ? WHERE id = ?").run(now, issueId);
}

const username = (id: number | null) =>
  id === null ? null : ((db.prepare("SELECT username FROM users WHERE id = ?").get(id) as { username: string } | undefined)?.username ?? null);

/**
 * Applies an already-validated patch and records what changed in the activity
 * feed. Reordering alone doesn't count as an update.
 */
export function updateIssue(issue: Issue, patch: IssuePatch, actorId: number) {
  transaction(() => {
    const now = Date.now();
    const event = db.prepare(
      "INSERT INTO issue_events (issue_id, actor_id, field, old_value, new_value, created_at) VALUES (?, ?, ?, ?, ?, ?)",
    );
    const sets: string[] = [];
    const values: (string | number | null)[] = [];
    const set = (column: string, value: string | number | null) => {
      sets.push(`${column} = ?`);
      values.push(value);
    };
    const str = (v: number | null) => (v === null ? null : String(v));

    if (patch.title !== undefined && patch.title !== issue.title) {
      set("title", patch.title);
      event.run(issue.id, actorId, "title", issue.title, patch.title, now);
    }
    if (patch.body !== undefined && patch.body !== issue.body) set("body", patch.body);
    if (patch.status !== undefined && patch.status !== issue.status) {
      set("status", patch.status);
      set("state", isClosedStatus(patch.status) ? "closed" : "open");
      event.run(issue.id, actorId, "status", issue.status, patch.status, now);
    }
    if (patch.priority !== undefined && patch.priority !== issue.priority) {
      set("priority", patch.priority);
      event.run(issue.id, actorId, "priority", String(issue.priority), String(patch.priority), now);
    }
    if (patch.assigneeId !== undefined && patch.assigneeId !== issue.assigneeId) {
      set("assignee_id", patch.assigneeId);
      event.run(issue.id, actorId, "assignee", issue.assignee, username(patch.assigneeId), now);
    }
    if (patch.estimate !== undefined && patch.estimate !== issue.estimate) {
      set("estimate", patch.estimate);
      event.run(issue.id, actorId, "estimate", str(issue.estimate), str(patch.estimate), now);
    }
    if (patch.dueDate !== undefined && patch.dueDate !== issue.dueDate) {
      set("due_date", patch.dueDate);
      event.run(issue.id, actorId, "due_date", issue.dueDate, patch.dueDate, now);
    }
    let labelsChanged = false;
    if (patch.labelIds) {
      const before = new Set(issue.labels.map((l) => l.id));
      const after = new Set(patch.labelIds);
      const names = labelNames([...before, ...after]);
      for (const id of after) {
        if (before.has(id)) continue;
        db.prepare("INSERT OR IGNORE INTO issue_labels (issue_id, label_id) VALUES (?, ?)").run(issue.id, id);
        event.run(issue.id, actorId, "label", null, names.get(id) ?? null, now);
        labelsChanged = true;
      }
      for (const id of before) {
        if (after.has(id)) continue;
        db.prepare("DELETE FROM issue_labels WHERE issue_id = ? AND label_id = ?").run(issue.id, id);
        event.run(issue.id, actorId, "label", names.get(id) ?? null, null, now);
        labelsChanged = true;
      }
    }
    if (sets.length > 0 || labelsChanged) set("updated_at", now);
    if (patch.sortOrder !== undefined && patch.sortOrder !== issue.sortOrder) set("sort_order", patch.sortOrder);
    if (sets.length > 0) db.prepare(`UPDATE issues SET ${sets.join(", ")} WHERE id = ?`).run(...values, issue.id);
  });
}

export function deleteIssue(issueId: number) {
  db.prepare("DELETE FROM issues WHERE id = ?").run(issueId);
}

// ---------------------------------------------------------------------------
// Labels

export class LabelError extends Error {}

export function listLabels(repoId: number) {
  return plain<IssueLabel>(db.prepare("SELECT id, name, color FROM labels WHERE repo_id = ? ORDER BY name").all(repoId));
}

function labelNames(ids: number[]) {
  if (ids.length === 0) return new Map<number, string>();
  const rows = db
    .prepare(`SELECT id, name FROM labels WHERE id IN (${ids.map(() => "?").join(",")})`)
    .all(...ids) as { id: number; name: string }[];
  return new Map(rows.map((r) => [r.id, r.name]));
}

export function labelUsage(repoId: number) {
  const rows = db
    .prepare(
      `SELECT l.id, COUNT(il.issue_id) AS n FROM labels l LEFT JOIN issue_labels il ON il.label_id = l.id
       WHERE l.repo_id = ? GROUP BY l.id`,
    )
    .all(repoId) as { id: number; n: number }[];
  return new Map(rows.map((r) => [r.id, r.n]));
}

export function createLabel(repoId: number, input: { name: string; color: string }): IssueLabel {
  const name = input.name.trim().slice(0, 50);
  if (!name) throw new LabelError("Label names can't be blank.");
  if (!/^#[0-9a-f]{6}$/i.test(input.color)) throw new LabelError("Pick a label color.");
  if (db.prepare("SELECT 1 FROM labels WHERE repo_id = ? AND name = ?").get(repoId, name)) {
    throw new LabelError(`A label named "${name}" already exists.`);
  }
  const { lastInsertRowid } = db
    .prepare("INSERT INTO labels (repo_id, name, color) VALUES (?, ?, ?)")
    .run(repoId, name, input.color.toLowerCase());
  return { id: Number(lastInsertRowid), name, color: input.color.toLowerCase() };
}

export function deleteLabel(repoId: number, labelId: number) {
  db.prepare("DELETE FROM labels WHERE id = ? AND repo_id = ?").run(labelId, repoId);
}