import type { Board, BoardLabel, BoardType, BoardWithTasks, Task } from "@vtit-agent-coding/shared";
import { HTTPException } from "hono/http-exception";
import { customAlphabet } from "nanoid";
import { seedBuiltinAgents } from "./agentRepo";
import { asJson, type D1, newId, parseJsonFields, queryDb } from "./db";
import { computeBlocked } from "./taskDeps";

const nanoidSlug = customAlphabet("0123456789abcdefghijklmnopqrstuvwxyz", 10);
const HEX_COLOR = /^#[0-9A-Fa-f]{6}$/;

function parseBoard<T extends Board | BoardWithTasks>(board: T): T {
  return parseJsonFields(board, ["labels"] as (keyof T)[]);
}

function normalizeLabel(label: BoardLabel): BoardLabel {
  if (!label || typeof label.name !== "string" || typeof label.color !== "string") {
    throw new HTTPException(400, { message: "Label name and color are required" });
  }
  const name = label.name.trim();
  const color = label.color.trim();
  if (!name) throw new HTTPException(400, { message: "Label name is required" });
  if (!HEX_COLOR.test(color)) throw new HTTPException(400, { message: "Label color must be a hex color like #22D3EE" });
  return { name, color, description: label.description?.trim() || "" };
}

function normalizeLabels(labels: BoardLabel[]): BoardLabel[] {
  const seen = new Set<string>();
  return labels.map(normalizeLabel).map((label) => {
    if (seen.has(label.name)) throw new HTTPException(400, { message: `Duplicate label: ${label.name}` });
    seen.add(label.name);
    return label;
  });
}

export async function createBoard(
  db: D1,
  ownerId: string,
  name: string,
  type: BoardType,
  description?: string,
  projectId?: string | null,
): Promise<Board> {
  const id = newId();
  if (projectId) {
    await queryDb(
      db,
      "INSERT INTO boards (id, owner_id, project_id, name, description, type, created_at, updated_at) VALUES ($1, $2, $3, $4, $5, $6, NOW(), NOW())",
      [id, ownerId, projectId, name, description || null, type],
    );
  } else {
    await queryDb(
      db,
      "INSERT INTO boards (id, owner_id, name, description, type, created_at, updated_at) VALUES ($1, $2, $3, $4, $5, NOW(), NOW())",
      [id, ownerId, name, description || null, type],
    );
  }

  await seedBuiltinAgents(db, ownerId);

  const res = await queryDb<Board>(db, "SELECT * FROM boards WHERE id = $1", [id]);
  return parseBoard(res.rows[0]);
}

export async function listBoards(db: D1, ownerId: string, projectId?: string | null): Promise<Board[]> {
  let sql = "SELECT * FROM boards WHERE owner_id = $1";
  const params: unknown[] = [ownerId];
  if (projectId) {
    sql += " AND project_id = $2";
    params.push(projectId);
  }
  sql += " ORDER BY created_at DESC";
  const res = await queryDb<Board>(db, sql, params);
  return res.rows.map(parseBoard);
}

export async function getBoardByName(db: D1, ownerId: string, name: string): Promise<Board | null> {
  const res = await queryDb<Board>(db, "SELECT * FROM boards WHERE owner_id = $1 AND name = $2", [ownerId, name]);
  return res.rows[0] ? parseBoard(res.rows[0]) : null;
}

export async function getBoard(db: D1, boardId: string): Promise<BoardWithTasks | null> {
  const res = await queryDb<Board>(db, "SELECT * FROM boards WHERE id = $1", [boardId]);
  const board = res.rows[0];
  if (!board) return null;

  const tasksRes = await queryDb<Task>(
    db,
    `
    SELECT t.*, a.name as agent_name, a.public_key as agent_public_key, r.name as repository_name FROM tasks t
    LEFT JOIN agents a ON t.assigned_to = a.id
    LEFT JOIN repositories r ON t.repository_id = r.id
    WHERE t.board_id = $1
    ORDER BY
      CASE t.status WHEN 'todo' THEN 0 WHEN 'in_progress' THEN 1 WHEN 'in_review' THEN 2 WHEN 'done' THEN 3 ELSE 4 END,
      CASE WHEN t.status = 'todo' THEN t.position END ASC,
      CASE WHEN t.status != 'todo' THEN t.updated_at END DESC
  `,
    [boardId],
  );
  const tasks = tasksRes.rows;

  const taskIds = tasks.map((t: Task) => t.id);
  if (taskIds.length > 0) {
    const blockedSet = await computeBlocked(db, taskIds);
    for (const task of tasks) {
      task.blocked = blockedSet.has(task.id);
    }
  }

  return parseBoard({ ...board, tasks: tasks.map((t) => parseJsonFields(t, ["labels", "input", "metadata"])) });
}

export async function getDefaultBoard(db: D1, ownerId: string): Promise<Board | null> {
  const res = await queryDb<Board>(db, "SELECT * FROM boards WHERE owner_id = $1 ORDER BY created_at ASC LIMIT 1", [ownerId]);
  return res.rows[0] ? parseBoard(res.rows[0]) : null;
}

export async function updateBoard(
  db: D1,
  boardId: string,
  updates: { name?: string; description?: string; visibility?: "private" | "public"; labels?: BoardLabel[]; project_id?: string | null },
): Promise<Board | null> {
  const sets: string[] = [];
  const values: unknown[] = [];
  let paramIndex = 1;

  if (updates.name !== undefined) {
    sets.push(`name = $${paramIndex++}`);
    values.push(updates.name);
  }
  if (updates.description !== undefined) {
    sets.push(`description = $${paramIndex++}`);
    values.push(updates.description || null);
  }
  if (updates.project_id !== undefined) {
    sets.push(`project_id = $${paramIndex++}`);
    values.push(updates.project_id || null);
  }
  if (updates.visibility !== undefined) {
    sets.push(`visibility = $${paramIndex++}`);
    values.push(updates.visibility);
    if (updates.visibility === "public") {
      const existing = await queryDb<{ share_slug: string | null }>(db, "SELECT share_slug FROM boards WHERE id = $1", [boardId]);
      if (existing.rows[0] && !existing.rows[0].share_slug) {
        sets.push(`share_slug = $${paramIndex++}`);
        values.push(nanoidSlug());
      }
    }
  }
  if (updates.labels !== undefined) {
    sets.push(`labels = $${paramIndex++}`);
    values.push(JSON.stringify(normalizeLabels(updates.labels)));
  }

  if (sets.length === 0) {
    const res = await queryDb<Board>(db, "SELECT * FROM boards WHERE id = $1", [boardId]);
    return res.rows[0] ? parseBoard(res.rows[0]) : null;
  }

  sets.push("updated_at = NOW()");
  values.push(boardId);

  await queryDb(db, `UPDATE boards SET ${sets.join(", ")} WHERE id = $${paramIndex}`, values);
  const res = await queryDb<Board>(db, "SELECT * FROM boards WHERE id = $1", [boardId]);
  return res.rows[0] ? parseBoard(res.rows[0]) : null;
}

export async function createBoardLabel(db: D1, boardId: string, input: BoardLabel): Promise<Board | null> {
  const res = await queryDb<Board>(db, "SELECT * FROM boards WHERE id = $1", [boardId]);
  if (!res.rows[0]) return null;
  const labels = parseBoard(res.rows[0]).labels;
  const label = normalizeLabel(input);
  if (labels.some((existing) => existing.name === label.name)) throw new HTTPException(409, { message: `Label already exists: ${label.name}` });
  return updateBoard(db, boardId, { labels: [...labels, label] });
}

export async function updateBoardLabel(db: D1, boardId: string, name: string, input: Partial<BoardLabel>): Promise<Board | null> {
  const res = await queryDb<Board>(db, "SELECT * FROM boards WHERE id = $1", [boardId]);
  if (!res.rows[0]) return null;
  const labels = parseBoard(res.rows[0]).labels;
  const current = labels.find((label) => label.name === name);
  if (!current) throw new HTTPException(404, { message: `Label not found: ${name}` });
  const next = normalizeLabel({
    name: input.name ?? current.name,
    color: input.color ?? current.color,
    description: input.description ?? current.description,
  });
  if (next.name !== name && labels.some((label) => label.name === next.name)) {
    throw new HTTPException(409, { message: `Label already exists: ${next.name}` });
  }

  const updatedLabels = labels.map((label) => (label.name === name ? next : label));
  await updateBoard(db, boardId, { labels: updatedLabels });

  if (next.name !== name) {
    const tasksRes = await queryDb<{ id: string; labels: string }>(db, "SELECT id, labels FROM tasks WHERE board_id = $1 AND labels IS NOT NULL", [
      boardId,
    ]);
    const affectedTasks = tasksRes.rows
      .map((task) => ({ id: task.id, labels: asJson<string[]>(task.labels, []) }))
      .filter((task) => task.labels.includes(name));

    for (const task of affectedTasks) {
      await queryDb(db, "UPDATE tasks SET labels = $1, updated_at = NOW() WHERE id = $2", [
        JSON.stringify(task.labels.map((label) => (label === name ? next.name : label))),
        task.id,
      ]);
    }
  }

  const nextBoardRes = await queryDb<Board>(db, "SELECT * FROM boards WHERE id = $1", [boardId]);
  return nextBoardRes.rows[0] ? parseBoard(nextBoardRes.rows[0]) : null;
}

export async function deleteBoardLabel(db: D1, boardId: string, name: string): Promise<Board | null> {
  const res = await queryDb<Board>(db, "SELECT * FROM boards WHERE id = $1", [boardId]);
  if (!res.rows[0]) return null;
  const labels = parseBoard(res.rows[0]).labels;
  if (!labels.some((label) => label.name === name)) throw new HTTPException(404, { message: `Label not found: ${name}` });

  const tasksRes = await queryDb<{ id: string; labels: string }>(db, "SELECT id, labels FROM tasks WHERE board_id = $1 AND labels IS NOT NULL", [
    boardId,
  ]);
  const affectedTasks = tasksRes.rows
    .map((task) => {
      const current = asJson<string[]>(task.labels, []);
      return { id: task.id, current, next: current.filter((label) => label !== name) };
    })
    .filter((task) => task.current.length !== task.next.length);

  for (const task of affectedTasks) {
    await queryDb(db, "UPDATE tasks SET labels = $1, updated_at = NOW() WHERE id = $2", [JSON.stringify(task.next), task.id]);
  }

  return updateBoard(db, boardId, { labels: labels.filter((label) => label.name !== name) });
}

export async function getBoardBySlug(db: D1, slug: string): Promise<BoardWithTasks | null> {
  const res = await queryDb<Board>(db, "SELECT * FROM boards WHERE share_slug = $1 AND visibility = 'public'", [slug]);
  const board = res.rows[0];
  if (!board) return null;

  const tasksRes = await queryDb<Task>(
    db,
    `
    SELECT t.*, a.name as agent_name, a.public_key as agent_public_key, r.name as repository_name FROM tasks t
    LEFT JOIN agents a ON t.assigned_to = a.id
    LEFT JOIN repositories r ON t.repository_id = r.id
    WHERE t.board_id = $1
    ORDER BY
      CASE t.status WHEN 'todo' THEN 0 WHEN 'in_progress' THEN 1 WHEN 'in_review' THEN 2 WHEN 'done' THEN 3 ELSE 4 END,
      CASE WHEN t.status = 'todo' THEN t.position END ASC,
      CASE WHEN t.status != 'todo' THEN t.updated_at END DESC
  `,
    [board.id],
  );

  return parseBoard({ ...board, tasks: tasksRes.rows.map((t) => parseJsonFields(t, ["labels", "input", "metadata"])) });
}

export async function deleteBoard(db: D1, boardId: string): Promise<boolean> {
  const result = await queryDb(db, "DELETE FROM boards WHERE id = $1", [boardId]);
  return (result.rowCount ?? 0) > 0;
}