import { createServerFn } from "@tanstack/react-start";
import { z } from "zod";
import { requireViewer, viewerMiddleware } from "./middleware";
import { all, batch, first, run, stmt } from "~/server/db";
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,
  visibleItemClause,
} 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 { clause, viewerId } = visibleItemClause(context.viewer);
    const rows = await all<{
      number: number;
      title: string;
      description: string;
      visibility: string;
      updated_at: number;
      items: number;
    }>(
      `SELECT p.number, p.title, p.description, p.visibility, p.updated_at,
         (SELECT COUNT(*) FROM project_items pi LEFT JOIN issues i ON i.id = pi.issue_id
          WHERE pi.project_id = p.id AND ${clause}) AS items
       FROM projects p WHERE p.owner_id = ?5 AND p.closed = ?2 AND (?3 = 1 OR p.visibility = 'public')
       ORDER BY p.updated_at DESC LIMIT ?4`,
      viewerId,
      data.closed ? 1 : 0,
      canCreate ? 1 : 0,
      PLANNING_CAPS.projectsPerOwner,
      account.id,
    );
    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");
    await assertCount(
      "SELECT COUNT(*) AS count FROM projects WHERE owner_id = ?",
      account.id,
      PLANNING_CAPS.projectsPerOwner,
      "projects",
    );
    const id = randomId();
    const now = Date.now();
    const next = await first<{ number: number }>(
      "SELECT COALESCE(MAX(number), 0) + 1 AS number FROM projects WHERE owner_id = ?",
      account.id,
    );
    const number = next?.number ?? 1;
    await 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),
      ),
    ]);
    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 { clause, viewerId } = visibleItemClause(context.viewer);
    const [fields, items] = await Promise.all([
      projectFields(project.id),
      all<{
        id: string;
        note: string | null;
        position: number;
        number: number | null;
        title: string | null;
        state: "open" | "closed" | null;
        is_pull: number | null;
        repo_owner: string | null;
        repo_name: string | null;
      }>(
        `SELECT pi.id, pi.note, pi.position, i.number, i.title, i.state, i.is_pull,
           (SELECT a.name FROM repos r JOIN accounts a ON a.id = r.owner_id WHERE r.id = i.repo_id) AS repo_owner,
           (SELECT r.name FROM repos r WHERE r.id = i.repo_id) AS repo_name
         FROM project_items pi LEFT JOIN issues i ON i.id = pi.issue_id
         WHERE pi.project_id = ?2 AND ${clause} ORDER BY pi.position LIMIT ?3`,
        viewerId,
        project.id,
        PLANNING_CAPS.itemsPerProject,
      ),
    ]);
    const values = await 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.number !== null && item.repo_owner && item.repo_name
            ? {
                owner: item.repo_owner,
                repo: item.repo_name,
                number: item.number,
                title: item.title ?? "",
                state: item.state ?? "open",
                isPull: item.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 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);
    await run("DELETE FROM projects WHERE id = ?", project.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",
    );
    await 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 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);
    await assertCount(
      "SELECT COUNT(*) AS count FROM project_items WHERE project_id = ?",
      project.id,
      PLANNING_CAPS.itemsPerProject,
      "items per project",
    );
    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;
    }
    await 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 = ?), ?)`,
      randomId(),
      project.id,
      issueId,
      issueId ? null : data.reference,
      project.id,
      Date.now(),
    );
    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);
    await run("DELETE FROM project_items WHERE id = ? AND project_id = ?", data.itemId, project.id);
    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 [item, field] = await Promise.all([
      first("SELECT 1 FROM project_items WHERE id = ? AND project_id = ?", data.itemId, project.id),
      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 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 };
  });