import { chunks, first, placeholders, run } from "./db";
import { accountDb, repoDb, stmt } from "./scoped-db";
import type { ScopedDb, ScopedStatement, ScopedValue } from "./scoped-db";
import { forgetLocator, locate } from "./scoped-db/locators";
import { scopedId } from "./scoped-db/ids";
import { allocateIssueNumber, indexIssue } from "./issues";
import type { IssueRow } from "./issues";
import { addIssueEvent } from "./pulls";
import { assertBelowCap } from "./limits";
import { issuesChanged } from "./repo-state";
import type { RepoRow } from "~/lib/types";

/** Per-scope count caps for workstream 6 entities; kept here so `limits.ts` stays merge-friendly. */
export const PLANNING_CAPS = {
  milestonesPerRepo: 100,
  issueTypesPerRepo: 25,
  subIssuesPerParent: 100,
  pinnedPerRepo: 3,
  projectsPerOwner: 100,
  fieldsPerProject: 30,
  optionsPerField: 50,
  itemsPerProject: 1000,
  categoriesPerRepo: 25,
  discussionCommentsPerThread: 2000,
} as const;

/** `db` is where the counted table lives: the D1 directory by default, else a `repoDb` / `accountDb`. */
export async function assertCount(
  sql: string,
  param: string,
  cap: number,
  what: string,
  db: Pick<ScopedDb, "first"> = { first },
): Promise<void> {
  const row = await db.first<{ count: number }>(sql, param);
  assertBelowCap(row?.count ?? 0, cap, what);
}

export type Progress = { total: number; closed: number };

/** Closed/total counts of sub-issues for each parent issue id; sub-issues share their parent's repository. */
export async function subIssueProgress(parentIds: string[], repoId?: string): Promise<Map<string, Progress>> {
  const out = new Map<string, Progress>();
  const unique = [...new Set(parentIds)];
  if (unique.length === 0) return out;
  const scope = repoId ?? (await locate("issue", unique[0]));
  if (!scope) return out;
  for (const part of chunks(unique)) {
    const rows = await repoDb(scope).all<{
      parent_id: string;
      total: number;
      closed: number;
    }>(
      `SELECT parent_id, COUNT(*) AS total, SUM(state = 'closed') AS closed FROM issues
       WHERE parent_id IN (${placeholders(part.length)}) GROUP BY parent_id`,
      ...part,
    );
    for (const row of rows) out.set(row.parent_id, { total: row.total, closed: row.closed ?? 0 });
  }
  return out;
}

/** Resolves an issue number, following redirects left behind by transfers (into whichever repository it went). */
export async function resolveIssueNumber(repoId: string, number: number): Promise<IssueRow | null> {
  const db = repoDb(repoId);
  const direct = await db.first<IssueRow>("SELECT * FROM issues WHERE repo_id = ? AND number = ?", repoId, number);
  if (direct) return direct;
  const redirect = await db.first<{ issue_id: string }>(
    "SELECT issue_id FROM issue_redirects WHERE repo_id = ? AND number = ?",
    repoId,
    number,
  );
  if (!redirect) return null;
  const target = await locate("issue", redirect.issue_id);
  return target ? repoDb(target).first<IssueRow>("SELECT * FROM issues WHERE id = ?", redirect.issue_id) : null;
}

type Row = Record<string, ScopedValue>;

function insertRow(table: string, row: Row): ScopedStatement {
  const columns = Object.keys(row);
  return stmt(
    `INSERT OR REPLACE INTO ${table} (${columns.join(", ")}) VALUES (${placeholders(columns.length)})`,
    ...columns.map((column) => row[column]),
  );
}

/** Tables whose rows belong to one issue and travel with it. */
const ISSUE_CHILD_TABLES = ["comments", "issue_events", "issue_assignees", "thread_subscriptions"] as const;

/**
 * Moves an issue (with its comments, events, assignees, subscriptions and reactions) into another repository's
 * object and leaves a redirect behind. The copy into the target lands first, so a crash before the source delete
 * leaves a duplicate rather than a lost issue.
 */
export async function transferIssue(
  issue: IssueRow,
  source: RepoRow,
  target: RepoRow,
  actorId: string,
): Promise<number> {
  const number = await allocateIssueNumber(target.id);
  const open = issue.state === "open" ? 1 : 0;
  const from = repoDb(issue.repo_id);
  const to = repoDb(target.id);
  const row = await from.first<Row>("SELECT * FROM issues WHERE id = ?", issue.id);
  if (!row) return number;
  // Ids carry their repository, so the moved issue and its comments get fresh ids in the target; the source
  // keeps a redirect from the old number to the new id.
  const newId = scopedId(target.id);
  const children = await Promise.all(
    ISSUE_CHILD_TABLES.map(async (table) => ({
      table,
      rows: await from.all<Row>(`SELECT * FROM ${table} WHERE issue_id = ?`, issue.id),
    })),
  );
  const commentIds = new Map(children[0].rows.map((comment) => [String(comment.id), scopedId(target.id)]));
  const reactions = await from.all<Row>(
    `SELECT * FROM reactions WHERE (subject_type = 'issue' AND subject_id = ?1)
       OR (subject_type = 'comment' AND subject_id IN (SELECT id FROM comments WHERE issue_id = ?1))`,
    issue.id,
  );
  const now = Date.now();
  await to.batch([
    insertRow("issues", {
      ...row,
      id: newId,
      repo_id: target.id,
      number,
      milestone_id: null,
      type_id: null,
      parent_id: null,
      updated_at: now,
    }),
    ...children.flatMap(({ table, rows }) =>
      rows.map((child) =>
        insertRow(table, {
          ...child,
          issue_id: newId,
          ...(table === "comments" ? { id: commentIds.get(String(child.id)) ?? child.id } : {}),
        }),
      ),
    ),
    ...reactions.map((reaction) =>
      insertRow("reactions", {
        ...reaction,
        subject_id:
          reaction.subject_type === "issue"
            ? newId
            : (commentIds.get(String(reaction.subject_id)) ?? reaction.subject_id),
      }),
    ),
  ]);
  await from.batch([
    stmt("UPDATE issues SET parent_id = NULL WHERE parent_id = ?", issue.id),
    stmt(
      `DELETE FROM reactions WHERE (subject_type = 'issue' AND subject_id = ?1)
         OR (subject_type = 'comment' AND subject_id IN (SELECT id FROM comments WHERE issue_id = ?1))`,
      issue.id,
    ),
    ...ISSUE_CHILD_TABLES.map((table) => stmt(`DELETE FROM ${table} WHERE issue_id = ?`, issue.id)),
    stmt("DELETE FROM issue_labels WHERE issue_id = ?", issue.id),
    stmt("DELETE FROM pinned_issues WHERE issue_id = ?", issue.id),
    stmt("DELETE FROM issues_fts WHERE issue_id = ?", issue.id),
    stmt("DELETE FROM issues WHERE id = ?", issue.id),
    stmt(
      "INSERT OR REPLACE INTO issue_redirects (repo_id, number, issue_id) VALUES (?, ?, ?)",
      issue.repo_id,
      issue.number,
      newId,
    ),
  ]);
  // Project items pointing at the old id live in the owners' accounts.
  for (const ownerId of new Set([source.owner_id, target.owner_id])) {
    await accountDb(ownerId).run("UPDATE project_items SET issue_id = ? WHERE issue_id = ?", newId, issue.id);
  }
  await forgetLocator("issue", issue.id);
  for (const oldId of commentIds.keys()) await forgetLocator("comment", oldId);
  await run("UPDATE repos SET open_issues_count = MAX(open_issues_count - ?, 0) WHERE id = ?", open, issue.repo_id);
  await run("UPDATE repos SET open_issues_count = open_issues_count + ? WHERE id = ?", open, target.id);
  await indexIssue({
    id: newId,
    repo_id: target.id,
    title: issue.title,
    body: issue.body,
  });
  issuesChanged(issue.repo_id, [issue.id]);
  await addIssueEvent({ id: newId, repo_id: target.id }, actorId, "transferred", {
    from: `${source.owner_name}/${source.name}#${issue.number}`,
  });
  return number;
}