import { createServerFn } from "@tanstack/react-start";
import { z } from "zod";
import { requireViewer, viewerMiddleware } from "./middleware";
import { first } from "~/server/db";
import { accountDb, stmt } from "~/server/scoped-db";
import { forgetLocator, registerLocator } from "~/server/scoped-db/locators";
import { scopedId } from "~/server/scoped-db/ids";
import { randomId } from "~/server/crypto";
import { limitWrite } from "~/server/limits";
import { canCreateInAccount, requireRepoAccess } from "~/server/repos";
import { loadIssue } from "~/server/issues";
import { assertCount, PLANNING_CAPS } from "~/server/planning";
import {
  loadProjectAccess,
  notifyProject,
  parseOptions,
  projectFields,
  requireProjectWrite,
  visibleProjectIssues,
} from "~/server/projects";
import type { ProjectIssue } from "~/server/projects";
import { badRequest, notFoundError } from "~/server/errors";
import { accountName } from "~/lib/validation";

const projectRef = z.object({ owner: accountName, number: z.number().int().positive() });
const DEFAULT_STATUS = ["Todo", "In progress", "Done"];
const ITEM_REF = /^([^/\s]+)\/([^#\s]+)#(\d+)$/;

export const listProjects = createServerFn({ method: "GET" })
  .middleware([viewerMiddleware])
  .validator(z.object({ owner: accountName, closed: z.boolean().default(false) }))
  .handler(async ({ data, context }) => {
    const account = await first<{ id: string; name: string }>(
      "SELECT id, name FROM accounts WHERE name = ?",
      data.owner,
    );
    if (!account) throw notFoundError("Account not found");
    const canCreate = await canCreateInAccount(account.id, context.viewer);
    const db = accountDb(account.id);
    const projects = await db.all<{
      id: string;
      number: number;
      title: string;
      description: string;
      visibility: string;
      updated_at: number;
    }>(
      `SELECT id, number, title, description, visibility, updated_at
       FROM projects WHERE owner_id = ?4 AND closed = ?1 AND (?2 = 1 OR visibility = 'public')
       ORDER BY updated_at DESC LIMIT ?3`,
      data.closed ? 1 : 0,
      canCreate ? 1 : 0,
      PLANNING_CAPS.projectsPerOwner,
      account.id,
    );
    const items = await db.all<{ project_id: string; issue_id: string | null }>(
      `SELECT pi.project_id, pi.issue_id FROM project_items pi JOIN projects p ON p.id = pi.project_id
       WHERE p.owner_id = ?1 AND p.closed = ?2`,
      account.id,
      data.closed ? 1 : 0,
    );
    const visible = await visibleProjectIssues(
      items.map((item) => item.issue_id),
      context.viewer,
    );
    const counts = new Map<string, number>();
    for (const item of items) {
      if (item.issue_id !== null && !visible.has(item.issue_id)) continue;
      counts.set(item.project_id, (counts.get(item.project_id) ?? 0) + 1);
    }
    const rows = projects.map(({ id, ...project }) => ({ ...project, items: counts.get(id) ?? 0 }));
    return { owner: account.name, canCreate, projects: rows };
  });

export const createProject = createServerFn({ method: "POST" })
  .middleware([viewerMiddleware])
  .validator(
    z.object({
      owner: accountName,
      title: z.string().trim().min(1).max(120),
      description: z.string().max(1000).default(""),
      visibility: z.enum(["public", "private"]).default("private"),
    }),
  )
  .handler(async ({ data, context }) => {
    const viewer = requireViewer(context.viewer);
    await limitWrite(viewer);
    const account = await first<{ id: string }>("SELECT id FROM accounts WHERE name = ?", data.owner);
    if (!account || !(await canCreateInAccount(account.id, viewer))) throw notFoundError("Account not found");
    const db = accountDb(account.id);
    await assertCount(
      "SELECT COUNT(*) AS count FROM projects WHERE owner_id = ?",
      account.id,
      PLANNING_CAPS.projectsPerOwner,
      "projects",
      db,
    );
    const id = scopedId(account.id);
    const now = Date.now();
    const next = await db.first<{ number: number }>(
      "SELECT COALESCE(MAX(number), 0) + 1 AS number FROM projects WHERE owner_id = ?",
      account.id,
    );
    const number = next?.number ?? 1;
    await db.batch([
      stmt(
        `INSERT INTO projects (id, owner_id, number, title, description, visibility, created_by, created_at, updated_at)
         VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,
        id,
        account.id,
        number,
        data.title,
        data.description,
        data.visibility,
        viewer.id,
        now,
        now,
      ),
      stmt(
        "INSERT INTO project_fields (id, project_id, name, kind, options, position) VALUES (?, ?, 'Status', 'single_select', ?, 0)",
        randomId(),
        id,
        JSON.stringify(DEFAULT_STATUS),
      ),
    ]);
    await registerLocator("project", id, account.id);
    return { number };
  });

export const getProject = createServerFn({ method: "GET" })
  .middleware([viewerMiddleware])
  .validator(projectRef)
  .handler(async ({ data, context }) => {
    const { project, ownerName, canWrite } = await loadProjectAccess(data.owner, data.number, context.viewer);
    const db = accountDb(project.owner_id);
    const [fields, allItems] = await Promise.all([
      projectFields(project.id, project.owner_id),
      db.all<{ id: string; note: string | null; position: number; issue_id: string | null }>(
        "SELECT id, note, position, issue_id FROM project_items WHERE project_id = ? ORDER BY position LIMIT ?",
        project.id,
        PLANNING_CAPS.itemsPerProject,
      ),
    ]);
    const issues = await visibleProjectIssues(
      allItems.map((item) => item.issue_id),
      context.viewer,
    );
    const items = allItems.flatMap((item): Array<typeof item & { issue: ProjectIssue | null }> => {
      if (item.issue_id === null) return [{ ...item, issue: null }];
      const issue = issues.get(item.issue_id);
      return issue ? [{ ...item, issue }] : [];
    });
    const values = await db.all<{ item_id: string; field_id: string; value: string }>(
      "SELECT v.item_id, v.field_id, v.value FROM project_item_values v JOIN project_items pi ON pi.id = v.item_id WHERE pi.project_id = ?",
      project.id,
    );
    const valuesByItem = new Map<string, Record<string, string>>();
    for (const value of values) {
      valuesByItem.set(value.item_id, { ...valuesByItem.get(value.item_id), [value.field_id]: value.value });
    }
    return {
      project: {
        id: project.id,
        owner: ownerName,
        number: project.number,
        title: project.title,
        description: project.description,
        visibility: project.visibility,
        closed: project.closed === 1,
      },
      canWrite,
      fields: fields.map((field) => ({
        id: field.id,
        name: field.name,
        kind: field.kind,
        options: parseOptions(field),
      })),
      items: items.map((item) => ({
        id: item.id,
        position: item.position,
        note: item.note,
        issue: item.issue
          ? {
              owner: item.issue.repo_owner,
              repo: item.issue.repo_name,
              number: item.issue.number,
              title: item.issue.title,
              state: item.issue.state,
              isPull: item.issue.is_pull === 1,
            }
          : null,
        values: valuesByItem.get(item.id) ?? {},
      })),
    };
  });

export const updateProject = createServerFn({ method: "POST" })
  .middleware([viewerMiddleware])
  .validator(
    projectRef.extend({
      title: z.string().trim().min(1).max(120),
      description: z.string().max(1000),
      visibility: z.enum(["public", "private"]),
      closed: z.boolean(),
    }),
  )
  .handler(async ({ data, context }) => {
    const viewer = requireViewer(context.viewer);
    await limitWrite(viewer);
    const { project } = await requireProjectWrite(data.owner, data.number, viewer);
    await accountDb(project.owner_id).run(
      "UPDATE projects SET title = ?, description = ?, visibility = ?, closed = ?, updated_at = ? WHERE id = ?",
      data.title,
      data.description,
      data.visibility,
      data.closed ? 1 : 0,
      Date.now(),
      project.id,
    );
    await notifyProject(project.id, viewer.name);
    return { ok: true };
  });

export const deleteProject = createServerFn({ method: "POST" })
  .middleware([viewerMiddleware])
  .validator(projectRef)
  .handler(async ({ data, context }) => {
    const viewer = requireViewer(context.viewer);
    await limitWrite(viewer);
    const { project } = await requireProjectWrite(data.owner, data.number, viewer);
    const db = accountDb(project.owner_id);
    const items = await db.all<{ id: string }>("SELECT id FROM project_items WHERE project_id = ?", project.id);
    await db.run("DELETE FROM projects WHERE id = ?", project.id);
    await Promise.all([
      forgetLocator("project", project.id),
      ...items.map((item) => forgetLocator("project_item", item.id)),
    ]);
    await notifyProject(project.id, viewer.name);
    return { ok: true };
  });

export const addProjectField = createServerFn({ method: "POST" })
  .middleware([viewerMiddleware])
  .validator(
    projectRef.extend({
      name: z.string().trim().min(1).max(50),
      kind: z.enum(["text", "number", "date", "single_select"]),
      options: z.array(z.string().trim().min(1).max(50)).max(PLANNING_CAPS.optionsPerField).default([]),
    }),
  )
  .handler(async ({ data, context }) => {
    const viewer = requireViewer(context.viewer);
    await limitWrite(viewer);
    const { project } = await requireProjectWrite(data.owner, data.number, viewer);
    await assertCount(
      "SELECT COUNT(*) AS count FROM project_fields WHERE project_id = ?",
      project.id,
      PLANNING_CAPS.fieldsPerProject,
      "fields per project",
      accountDb(project.owner_id),
    );
    await accountDb(project.owner_id).run(
      `INSERT INTO project_fields (id, project_id, name, kind, options, position)
       VALUES (?, ?, ?, ?, ?, (SELECT COALESCE(MAX(position), 0) + 1 FROM project_fields WHERE project_id = ?))`,
      randomId(),
      project.id,
      data.name,
      data.kind,
      JSON.stringify(data.kind === "single_select" ? [...new Set(data.options)] : []),
      project.id,
    );
    await notifyProject(project.id, viewer.name);
    return { ok: true };
  });

export const deleteProjectField = createServerFn({ method: "POST" })
  .middleware([viewerMiddleware])
  .validator(projectRef.extend({ fieldId: z.string().max(64) }))
  .handler(async ({ data, context }) => {
    const viewer = requireViewer(context.viewer);
    await limitWrite(viewer);
    const { project } = await requireProjectWrite(data.owner, data.number, viewer);
    await accountDb(project.owner_id).run(
      "DELETE FROM project_fields WHERE id = ? AND project_id = ?",
      data.fieldId,
      project.id,
    );
    await notifyProject(project.id, viewer.name);
    return { ok: true };
  });

export const addProjectItem = createServerFn({ method: "POST" })
  .middleware([viewerMiddleware])
  .validator(projectRef.extend({ reference: z.string().trim().min(1).max(300) }))
  .handler(async ({ data, context }) => {
    const viewer = requireViewer(context.viewer);
    await limitWrite(viewer);
    const { project } = await requireProjectWrite(data.owner, data.number, viewer);
    const db = accountDb(project.owner_id);
    await assertCount(
      "SELECT COUNT(*) AS count FROM project_items WHERE project_id = ?",
      project.id,
      PLANNING_CAPS.itemsPerProject,
      "items per project",
      db,
    );
    const match = ITEM_REF.exec(data.reference);
    let issueId: string | null = null;
    if (match) {
      const { repo } = await requireRepoAccess(match[1], match[2], viewer, "read");
      const issue = await loadIssue(repo.id, Number(match[3]));
      if (!issue) throw notFoundError("Issue not found");
      issueId = issue.id;
    }
    const itemId = scopedId(project.owner_id);
    const inserted = await db.run(
      `INSERT OR IGNORE INTO project_items (id, project_id, issue_id, note, position, created_at)
       VALUES (?, ?, ?, ?, (SELECT COALESCE(MAX(position), 0) + 1 FROM project_items WHERE project_id = ?), ?)`,
      itemId,
      project.id,
      issueId,
      issueId ? null : data.reference,
      project.id,
      Date.now(),
    );
    if (inserted.meta.changes > 0) await registerLocator("project_item", itemId, project.owner_id);
    await notifyProject(project.id, viewer.name);
    return { ok: true };
  });

export const removeProjectItem = createServerFn({ method: "POST" })
  .middleware([viewerMiddleware])
  .validator(projectRef.extend({ itemId: z.string().max(64) }))
  .handler(async ({ data, context }) => {
    const viewer = requireViewer(context.viewer);
    await limitWrite(viewer);
    const { project } = await requireProjectWrite(data.owner, data.number, viewer);
    const removed = await accountDb(project.owner_id).run(
      "DELETE FROM project_items WHERE id = ? AND project_id = ?",
      data.itemId,
      project.id,
    );
    if (removed.meta.changes > 0) await forgetLocator("project_item", data.itemId);
    await notifyProject(project.id, viewer.name);
    return { ok: true };
  });

/** Sets one custom-field value and optionally re-positions the item (board drag-and-drop). */
export const updateProjectItem = createServerFn({ method: "POST" })
  .middleware([viewerMiddleware])
  .validator(
    projectRef.extend({
      itemId: z.string().max(64),
      fieldId: z.string().max(64),
      value: z.string().max(1000).nullable(),
      position: z.number().finite().optional(),
    }),
  )
  .handler(async ({ data, context }) => {
    const viewer = requireViewer(context.viewer);
    await limitWrite(viewer);
    const { project } = await requireProjectWrite(data.owner, data.number, viewer);
    const db = accountDb(project.owner_id);
    const [item, field] = await Promise.all([
      db.first("SELECT 1 FROM project_items WHERE id = ? AND project_id = ?", data.itemId, project.id),
      db.first<{ kind: string; options: string }>(
        "SELECT kind, options FROM project_fields WHERE id = ? AND project_id = ?",
        data.fieldId,
        project.id,
      ),
    ]);
    if (!item || !field) throw notFoundError("Item or field not found");
    const value = data.value?.trim() || null;
    if (value && field.kind === "single_select" && !parseOptions(field).includes(value))
      throw badRequest("Unknown option");
    if (value && field.kind === "number" && !Number.isFinite(Number(value))) throw badRequest("Not a number");
    await db.batch([
      value === null
        ? stmt("DELETE FROM project_item_values WHERE item_id = ? AND field_id = ?", data.itemId, data.fieldId)
        : stmt(
            "INSERT OR REPLACE INTO project_item_values (item_id, field_id, value) VALUES (?, ?, ?)",
            data.itemId,
            data.fieldId,
            value,
          ),
      ...(data.position === undefined
        ? []
        : [stmt("UPDATE project_items SET position = ? WHERE id = ?", data.position, data.itemId)]),
      stmt("UPDATE projects SET updated_at = ? WHERE id = ?", Date.now(), project.id),
    ]);
    await notifyProject(project.id, viewer.name);
    return { ok: true };
  });