import { createHash, randomUUID } from "node:crypto";
import postgres from "postgres";
import * as XLSX from "xlsx";
import { createHourlyJsonlWriter, logRetentionBytesForService } from "../../../shared/src/rotating-jsonl";

type JsonValue = string | number | boolean | null | JsonValue[] | { [key: string]: JsonValue };
type JsonRecord = Record<string, JsonValue>;

type ProjectStatusFilter = "all" | "completed" | "active" | "unpaid";

interface RuntimeConfig {
  host: string;
  port: number;
  databaseUrl: string;
  logFile: string;
  databasePoolMax: number;
  deployServiceId: string;
  deployRef: string;
  deployRepo: string;
  deployCommit: string;
  deployRequestedCommit: string;
}

interface ProjectRow {
  id: string;
  sequence_no: number | null;
  contract_no: string;
  name: string;
  current_status: string;
  pending: string;
  payment_status: string;
  payment_ratio: string | number | null;
  notes: string;
  raw: JsonValue;
  source_file: string;
  imported_at: string | null;
  created_at: string;
  updated_at: string;
}

interface ProjectRecord extends JsonRecord {
  id: string;
  sequenceNo: number | null;
  contractNo: string;
  name: string;
  currentStatus: string;
  pending: string;
  paymentStatus: string;
  paymentRatio: number | null;
  notes: string;
  raw: JsonValue;
  sourceFile: string;
  importedAt: string | null;
  createdAt: string;
  updatedAt: string;
}

interface ProjectInput {
  sequenceNo?: unknown;
  contractNo?: unknown;
  name?: unknown;
  currentStatus?: unknown;
  status?: unknown;
  pending?: unknown;
  todo?: unknown;
  paymentStatus?: unknown;
  paymentRatio?: unknown;
  notes?: unknown;
  raw?: unknown;
  sourceFile?: unknown;
}

interface ImportCandidate {
  id?: string;
  sequenceNo: number | null;
  contractNo: string;
  name: string;
  currentStatus: string;
  pending: string;
  paymentStatus: string;
  paymentRatio: number | null;
  notes: string;
  raw: JsonRecord;
  sourceFile: string;
}

const serviceStartedAt = new Date().toISOString();
const recentLogs: JsonRecord[] = [];
const EXCEL_CONTENT_TYPE = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

function configFromEnv(): RuntimeConfig {
  const databaseUrl = process.env.DATABASE_URL || "";
  if (!databaseUrl) throw new Error("DATABASE_URL is required");
  return {
    host: process.env.HOST || "0.0.0.0",
    port: Number(process.env.PORT || 4233),
    databaseUrl,
    logFile: process.env.LOG_FILE || "",
    databasePoolMax: Math.max(1, Math.min(8, Number(process.env.DATABASE_POOL_MAX || 1) || 1)),
    deployServiceId: process.env.UNIDESK_DEPLOY_SERVICE_ID || "project-manager",
    deployRef: process.env.UNIDESK_DEPLOY_REF || "",
    deployRepo: process.env.UNIDESK_DEPLOY_REPO || "",
    deployCommit: process.env.UNIDESK_DEPLOY_COMMIT || "",
    deployRequestedCommit: process.env.UNIDESK_DEPLOY_REQUESTED_COMMIT || "",
  };
}

const config = configFromEnv();
const sql = postgres(config.databaseUrl, {
  max: config.databasePoolMax,
  idle_timeout: 20,
  connect_timeout: 10,
  connection: { application_name: "unidesk-project-manager" },
});
const logWriter = config.logFile
  ? createHourlyJsonlWriter({
      baseLogFile: config.logFile,
      service: "project-manager",
      maxBytes: logRetentionBytesForService("project-manager"),
    })
  : null;
logWriter?.prune();

function log(event: string, detail: JsonRecord = {}): void {
  const record: JsonRecord = { at: new Date().toISOString(), event, ...detail };
  recentLogs.push(record);
  if (recentLogs.length > 300) recentLogs.shift();
  if (logWriter !== null) {
    try {
      logWriter.appendJson(record, new Date(String(record.at)));
    } catch {
      // Logging must not break request handling.
    }
  }
}

function jsonResponse(body: JsonValue, status = 200): Response {
  return new Response(JSON.stringify(body), {
    status,
    headers: { "content-type": "application/json; charset=utf-8" },
  });
}

function errorToJson(error: unknown): JsonRecord {
  if (error instanceof Error) return { name: error.name, message: error.message, stack: error.stack || "" };
  return { message: String(error) };
}

function errorResponse(error: unknown, status = 500): Response {
  log("request_error", { status, error: errorToJson(error) });
  return jsonResponse({ ok: false, error: error instanceof Error ? error.message : String(error) }, status);
}

function textValue(value: unknown): string {
  if (value === null || value === undefined) return "";
  if (typeof value === "string") return value.trim();
  if (typeof value === "number" || typeof value === "boolean" || typeof value === "bigint") return String(value).trim();
  return "";
}

function numberOrNull(value: unknown): number | null {
  if (value === null || value === undefined || value === "") return null;
  if (typeof value === "number") return Number.isFinite(value) ? value : null;
  const raw = String(value).trim();
  if (!raw) return null;
  const percent = raw.endsWith("%");
  const normalized = raw.replace(/[%\s,]/g, "");
  const parsed = Number(normalized);
  if (!Number.isFinite(parsed)) return null;
  return percent ? parsed / 100 : parsed;
}

function integerOrNull(value: unknown): number | null {
  const number = numberOrNull(value);
  if (number === null) return null;
  return Number.isInteger(number) ? number : Math.round(number);
}

function jsonRecordOrEmpty(value: unknown): JsonRecord {
  if (typeof value === "object" && value !== null && !Array.isArray(value)) return value as JsonRecord;
  return {};
}

function projectIdFromContract(contractNo: string): string {
  const compact = contractNo.trim().replace(/[^A-Za-z0-9_-]+/g, "_").replace(/^_+|_+$/g, "").slice(0, 96);
  if (compact) return `pm_${compact}`;
  return `pm_${randomUUID()}`;
}

function projectFromRow(row: ProjectRow): ProjectRecord {
  return {
    id: row.id,
    sequenceNo: row.sequence_no,
    contractNo: row.contract_no,
    name: row.name,
    currentStatus: row.current_status,
    pending: row.pending,
    paymentStatus: row.payment_status,
    paymentRatio: numberOrNull(row.payment_ratio),
    notes: row.notes,
    raw: row.raw,
    sourceFile: row.source_file,
    importedAt: row.imported_at,
    createdAt: row.created_at,
    updatedAt: row.updated_at,
  };
}

function rowHash(input: ImportCandidate): string {
  return createHash("sha256").update(JSON.stringify({ contractNo: input.contractNo, name: input.name, raw: input.raw })).digest("hex");
}

async function ensureSchema(): Promise<void> {
  await sql`
    CREATE TABLE IF NOT EXISTS project_manager_projects (
      id TEXT PRIMARY KEY,
      sequence_no INTEGER,
      contract_no TEXT NOT NULL DEFAULT '',
      name TEXT NOT NULL DEFAULT '',
      current_status TEXT NOT NULL DEFAULT '',
      pending TEXT NOT NULL DEFAULT '',
      payment_status TEXT NOT NULL DEFAULT '',
      payment_ratio NUMERIC,
      notes TEXT NOT NULL DEFAULT '',
      raw JSONB NOT NULL DEFAULT '{}'::jsonb,
      source_file TEXT NOT NULL DEFAULT '',
      imported_at TIMESTAMPTZ,
      created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
      updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
    )
  `;
  await sql`CREATE INDEX IF NOT EXISTS idx_project_manager_projects_sequence ON project_manager_projects(sequence_no NULLS LAST, created_at ASC)`;
  await sql`CREATE INDEX IF NOT EXISTS idx_project_manager_projects_contract ON project_manager_projects(contract_no)`;
  await sql`CREATE INDEX IF NOT EXISTS idx_project_manager_projects_updated ON project_manager_projects(updated_at DESC)`;
}

async function waitForSchema(): Promise<void> {
  const started = Date.now();
  let lastError: unknown = null;
  for (let attempt = 1; attempt <= 30; attempt += 1) {
    try {
      await ensureSchema();
      log("schema_ready", { attempt });
      return;
    } catch (error) {
      lastError = error;
      log("schema_wait", { attempt, error: errorToJson(error) });
      await Bun.sleep(Math.min(1000 + attempt * 250, 5000));
    }
  }
  const elapsedMs = Date.now() - started;
  throw new Error(`Project Manager schema initialization failed after ${elapsedMs}ms: ${JSON.stringify(errorToJson(lastError))}`);
}

async function readJson(req: Request): Promise<unknown> {
  const text = await req.text();
  if (text.length > 1_000_000) throw new Error("request body is too large");
  if (!text.trim()) return {};
  return JSON.parse(text) as unknown;
}

function normalizeProjectInput(input: ProjectInput, existing?: ProjectRecord): ImportCandidate {
  const currentStatus = "currentStatus" in input ? textValue(input.currentStatus) : "status" in input ? textValue(input.status) : existing?.currentStatus ?? "";
  const pending = "pending" in input ? textValue(input.pending) : "todo" in input ? textValue(input.todo) : existing?.pending ?? "";
  const contractNo = "contractNo" in input ? textValue(input.contractNo) : existing?.contractNo ?? "";
  const name = "name" in input ? textValue(input.name) : existing?.name ?? "";
  const paymentRatio = "paymentRatio" in input ? numberOrNull(input.paymentRatio) : existing?.paymentRatio ?? null;
  const raw = "raw" in input ? jsonRecordOrEmpty(input.raw) : jsonRecordOrEmpty(existing?.raw);
  if (!contractNo && !name) throw new Error("contractNo or name is required");
  return {
    sequenceNo: "sequenceNo" in input ? integerOrNull(input.sequenceNo) : existing?.sequenceNo ?? null,
    contractNo,
    name,
    currentStatus,
    pending,
    paymentStatus: "paymentStatus" in input ? textValue(input.paymentStatus) : existing?.paymentStatus ?? "",
    paymentRatio,
    notes: "notes" in input ? textValue(input.notes) : existing?.notes ?? "",
    raw,
    sourceFile: "sourceFile" in input ? textValue(input.sourceFile) : existing?.sourceFile ?? "manual",
  };
}

async function getProject(id: string): Promise<ProjectRecord | null> {
  const rows = await sql<ProjectRow[]>`
    SELECT id, sequence_no, contract_no, name, current_status, pending, payment_status, payment_ratio, notes, raw, source_file, imported_at, created_at, updated_at
    FROM project_manager_projects
    WHERE id = ${id}
    LIMIT 1
  `;
  return rows[0] ? projectFromRow(rows[0]) : null;
}

async function insertProject(candidate: ImportCandidate, id = projectIdFromContract(candidate.contractNo)): Promise<ProjectRecord> {
  const rows = await sql<ProjectRow[]>`
    INSERT INTO project_manager_projects (id, sequence_no, contract_no, name, current_status, pending, payment_status, payment_ratio, notes, raw, source_file, imported_at)
    VALUES (${id}, ${candidate.sequenceNo}, ${candidate.contractNo}, ${candidate.name}, ${candidate.currentStatus}, ${candidate.pending}, ${candidate.paymentStatus}, ${candidate.paymentRatio}, ${candidate.notes}, ${sql.json(candidate.raw)}, ${candidate.sourceFile}, now())
    RETURNING id, sequence_no, contract_no, name, current_status, pending, payment_status, payment_ratio, notes, raw, source_file, imported_at, created_at, updated_at
  `;
  return projectFromRow(rows[0]!);
}

async function updateProject(id: string, candidate: ImportCandidate): Promise<ProjectRecord> {
  const rows = await sql<ProjectRow[]>`
    UPDATE project_manager_projects
    SET sequence_no = ${candidate.sequenceNo},
        contract_no = ${candidate.contractNo},
        name = ${candidate.name},
        current_status = ${candidate.currentStatus},
        pending = ${candidate.pending},
        payment_status = ${candidate.paymentStatus},
        payment_ratio = ${candidate.paymentRatio},
        notes = ${candidate.notes},
        raw = ${sql.json(candidate.raw)},
        source_file = ${candidate.sourceFile},
        updated_at = now()
    WHERE id = ${id}
    RETURNING id, sequence_no, contract_no, name, current_status, pending, payment_status, payment_ratio, notes, raw, source_file, imported_at, created_at, updated_at
  `;
  if (!rows[0]) throw new Error(`project not found: ${id}`);
  return projectFromRow(rows[0]);
}

function andFragments(fragments: any[]): any {
  if (fragments.length === 0) return sql`TRUE`;
  return fragments.slice(1).reduce((acc, fragment) => sql`${acc} AND ${fragment}`, fragments[0]);
}

function statusFilterFromUrl(url: URL): ProjectStatusFilter {
  const value = String(url.searchParams.get("status") || "all").toLowerCase();
  if (value === "completed" || value === "active" || value === "unpaid") return value;
  return "all";
}

function projectFilters(url: URL): any[] {
  const filters: any[] = [];
  const q = String(url.searchParams.get("q") || "").trim();
  if (q) {
    const like = `%${q}%`;
    filters.push(sql`(contract_no ILIKE ${like} OR name ILIKE ${like} OR current_status ILIKE ${like} OR pending ILIKE ${like} OR notes ILIKE ${like})`);
  }
  const status = statusFilterFromUrl(url);
  if (status === "completed") filters.push(sql`(current_status ILIKE ${"%完成%"} OR pending ILIKE ${"%完成%"} OR notes ILIKE ${"%完成%"})`);
  if (status === "active") filters.push(sql`NOT (current_status ILIKE ${"%完成%"} AND pending ILIKE ${"%完成%"})`);
  if (status === "unpaid") filters.push(sql`COALESCE(payment_ratio, 0) < 1`);
  return filters;
}

async function listProjects(url: URL): Promise<Response> {
  const page = Math.max(1, Number(url.searchParams.get("page") || 1) || 1);
  const pageSize = Math.max(1, Math.min(200, Number(url.searchParams.get("pageSize") || 100) || 100));
  const offset = (page - 1) * pageSize;
  const where = andFragments(projectFilters(url));
  const countRows = await sql<{ count: number }[]>`SELECT count(*)::int AS count FROM project_manager_projects WHERE ${where}`;
  const rows = await sql<ProjectRow[]>`
    SELECT id, sequence_no, contract_no, name, current_status, pending, payment_status, payment_ratio, notes, raw, source_file, imported_at, created_at, updated_at
    FROM project_manager_projects
    WHERE ${where}
    ORDER BY sequence_no NULLS LAST, created_at ASC, id ASC
    LIMIT ${pageSize}
    OFFSET ${offset}
  `;
  const summaryRows = await sql<{ total: number; completed: number; unpaid: number; average_payment: number | null }[]>`
    SELECT
      count(*)::int AS total,
      count(*) FILTER (WHERE current_status ILIKE ${"%完成%"} OR pending ILIKE ${"%完成%"} OR notes ILIKE ${"%完成%"})::int AS completed,
      count(*) FILTER (WHERE COALESCE(payment_ratio, 0) < 1)::int AS unpaid,
      avg(payment_ratio)::float8 AS average_payment
    FROM project_manager_projects
  `;
  const summary = summaryRows[0] ?? { total: 0, completed: 0, unpaid: 0, average_payment: null };
  return jsonResponse({
    ok: true,
    projects: rows.map(projectFromRow),
    page,
    pageSize,
    total: Number(countRows[0]?.count ?? 0),
    summary: {
      total: Number(summary.total || 0),
      completed: Number(summary.completed || 0),
      active: Math.max(0, Number(summary.total || 0) - Number(summary.completed || 0)),
      unpaid: Number(summary.unpaid || 0),
      averagePayment: numberOrNull(summary.average_payment),
    },
  });
}

async function createProject(req: Request): Promise<Response> {
  const body = jsonRecordOrEmpty(await readJson(req));
  const candidate = normalizeProjectInput(body);
  const id = textValue(body.id) || projectIdFromContract(candidate.contractNo);
  const existing = await getProject(id);
  if (existing !== null) return jsonResponse({ ok: false, error: `project already exists: ${id}` }, 409);
  const project = await insertProject(candidate, id);
  log("project_created", { id: project.id, contractNo: project.contractNo, name: project.name });
  return jsonResponse({ ok: true, project }, 201);
}

async function updateProjectRoute(req: Request, id: string): Promise<Response> {
  const existing = await getProject(id);
  if (existing === null) return jsonResponse({ ok: false, error: `project not found: ${id}` }, 404);
  const body = jsonRecordOrEmpty(await readJson(req));
  const candidate = normalizeProjectInput(body, existing);
  const project = await updateProject(id, candidate);
  log("project_updated", { id, contractNo: project.contractNo, name: project.name });
  return jsonResponse({ ok: true, project });
}

async function deleteProjectRoute(id: string): Promise<Response> {
  const rows = await sql<{ id: string }[]>`DELETE FROM project_manager_projects WHERE id = ${id} RETURNING id`;
  if (!rows[0]) return jsonResponse({ ok: false, error: `project not found: ${id}` }, 404);
  log("project_deleted", { id });
  return jsonResponse({ ok: true, id });
}

function normalizeHeader(value: unknown): string {
  return textValue(value).replace(/\s+/g, "");
}

function readCell(row: unknown[], index: number): string {
  return textValue(row[index]);
}

function sheetRowsFromWorkbook(buffer: Buffer, sheetName?: string): unknown[][] {
  const workbook = XLSX.read(buffer, { type: "buffer", cellDates: false });
  const effectiveSheet = sheetName && workbook.Sheets[sheetName] ? sheetName : workbook.SheetNames[0];
  if (!effectiveSheet) throw new Error("Excel workbook has no sheets");
  return XLSX.utils.sheet_to_json(workbook.Sheets[effectiveSheet]!, { header: 1, raw: false, defval: "" }) as unknown[][];
}

function parseExcelProjects(buffer: Buffer, sourceFile: string, sheetName?: string): ImportCandidate[] {
  const rows = sheetRowsFromWorkbook(buffer, sheetName);
  const headerIndex = rows.findIndex((row) => row.some((cell) => normalizeHeader(cell) === "合同号") && row.some((cell) => normalizeHeader(cell) === "项目名称"));
  if (headerIndex < 0) throw new Error("Excel header row not found; expected 合同号 and 项目名称");
  const headerRow = rows[headerIndex] ?? [];
  const headers = headerRow.map((cell) => normalizeHeader(cell));
  const indexOf = (header: string): number => headers.findIndex((cell) => cell === header);
  const sequenceIndex = indexOf("序号");
  const contractIndex = indexOf("合同号");
  const nameIndex = indexOf("项目名称");
  const statusIndex = indexOf("当前状况");
  const pendingIndex = indexOf("待完成");
  const paymentIndex = indexOf("付款情况");
  const notesIndex = indexOf("其它");
  const projects: ImportCandidate[] = [];
  for (const row of rows.slice(headerIndex + 1)) {
    const record: JsonRecord = {};
    headers.forEach((header, index) => {
      if (header) record[header] = readCell(row, index);
    });
    const contractNo = contractIndex >= 0 ? readCell(row, contractIndex) : "";
    const name = nameIndex >= 0 ? readCell(row, nameIndex) : "";
    if (!contractNo && !name) continue;
    const paymentStatus = paymentIndex >= 0 ? readCell(row, paymentIndex) : "";
    const candidate: ImportCandidate = {
      sequenceNo: sequenceIndex >= 0 ? integerOrNull(readCell(row, sequenceIndex)) : null,
      contractNo,
      name,
      currentStatus: statusIndex >= 0 ? readCell(row, statusIndex) : "",
      pending: pendingIndex >= 0 ? readCell(row, pendingIndex) : "",
      paymentStatus,
      paymentRatio: numberOrNull(paymentStatus),
      notes: notesIndex >= 0 ? readCell(row, notesIndex) : "",
      raw: { ...record, _sourceRowHash: "" },
      sourceFile,
    };
    candidate.raw._sourceRowHash = rowHash(candidate);
    projects.push(candidate);
  }
  return projects;
}

async function importProjects(candidates: ImportCandidate[], replace: boolean): Promise<JsonRecord> {
  const unique = new Map<string, ImportCandidate>();
  for (const candidate of candidates) {
    const id = candidate.id || projectIdFromContract(candidate.contractNo);
    unique.set(id, { ...candidate, id });
  }
  let imported = 0;
  await sql.begin(async (tx) => {
    if (replace) await tx`DELETE FROM project_manager_projects`;
    for (const [id, candidate] of unique.entries()) {
      await tx`
        INSERT INTO project_manager_projects (id, sequence_no, contract_no, name, current_status, pending, payment_status, payment_ratio, notes, raw, source_file, imported_at, updated_at)
        VALUES (${id}, ${candidate.sequenceNo}, ${candidate.contractNo}, ${candidate.name}, ${candidate.currentStatus}, ${candidate.pending}, ${candidate.paymentStatus}, ${candidate.paymentRatio}, ${candidate.notes}, ${tx.json(candidate.raw)}, ${candidate.sourceFile}, now(), now())
        ON CONFLICT (id) DO UPDATE SET
          sequence_no = EXCLUDED.sequence_no,
          contract_no = EXCLUDED.contract_no,
          name = EXCLUDED.name,
          current_status = EXCLUDED.current_status,
          pending = EXCLUDED.pending,
          payment_status = EXCLUDED.payment_status,
          payment_ratio = EXCLUDED.payment_ratio,
          notes = EXCLUDED.notes,
          raw = EXCLUDED.raw,
          source_file = EXCLUDED.source_file,
          imported_at = EXCLUDED.imported_at,
          updated_at = now()
      `;
      imported += 1;
    }
  });
  const totalRows = await sql<{ count: number }[]>`SELECT count(*)::int AS count FROM project_manager_projects`;
  return { imported, replace, total: Number(totalRows[0]?.count ?? 0) };
}

async function importExcelRoute(req: Request): Promise<Response> {
  const body = jsonRecordOrEmpty(await readJson(req));
  const base64 = textValue(body.contentBase64);
  if (!base64) return jsonResponse({ ok: false, error: "contentBase64 is required" }, 400);
  const fileName = textValue(body.fileName) || "import.xlsx";
  const sheetName = textValue(body.sheetName) || undefined;
  const replace = body.replace === true;
  const buffer = Buffer.from(base64, "base64");
  const candidates = parseExcelProjects(buffer, fileName, sheetName);
  const result = await importProjects(candidates, replace);
  log("projects_imported_excel", { fileName, sheetName: sheetName || "", imported: result.imported, replace });
  return jsonResponse({ ok: true, fileName, parsed: candidates.length, ...result });
}

async function importRowsRoute(req: Request): Promise<Response> {
  const body = jsonRecordOrEmpty(await readJson(req));
  const rows = Array.isArray(body.projects) ? body.projects : Array.isArray(body.rows) ? body.rows : [];
  const sourceFile = textValue(body.sourceFile) || "api";
  const candidates = rows.map((row) => normalizeProjectInput(jsonRecordOrEmpty(row), undefined)).map((candidate) => ({ ...candidate, sourceFile }));
  const result = await importProjects(candidates, body.replace === true);
  log("projects_imported_rows", { sourceFile, imported: result.imported, replace: body.replace === true });
  return jsonResponse({ ok: true, parsed: candidates.length, ...result });
}

async function exportProjects(): Promise<Response> {
  const rows = await sql<ProjectRow[]>`
    SELECT id, sequence_no, contract_no, name, current_status, pending, payment_status, payment_ratio, notes, raw, source_file, imported_at, created_at, updated_at
    FROM project_manager_projects
    ORDER BY sequence_no NULLS LAST, created_at ASC, id ASC
  `;
  const table = [
    ["序号", "合同号", "项目名称", "当前状况", "待完成", "付款情况", "其它", "ID", "来源", "更新时间"],
    ...rows.map((row) => {
      const project = projectFromRow(row);
      return [
        project.sequenceNo ?? "",
        project.contractNo,
        project.name,
        project.currentStatus,
        project.pending,
        project.paymentStatus,
        project.notes,
        project.id,
        project.sourceFile,
        project.updatedAt,
      ];
    }),
  ];
  const workbook = XLSX.utils.book_new();
  const worksheet = XLSX.utils.aoa_to_sheet(table);
  worksheet["!cols"] = [
    { wch: 8 }, { wch: 22 }, { wch: 28 }, { wch: 32 }, { wch: 38 }, { wch: 12 }, { wch: 18 }, { wch: 30 }, { wch: 24 }, { wch: 24 },
  ];
  XLSX.utils.book_append_sheet(workbook, worksheet, "项目列表");
  const buffer = XLSX.write(workbook, { type: "buffer", bookType: "xlsx" }) as Buffer;
  const stamp = new Date().toISOString().slice(0, 10).replace(/-/g, "");
  const body = new Uint8Array(buffer.byteLength);
  body.set(buffer);
  return new Response(body.buffer, {
    status: 200,
    headers: {
      "content-type": EXCEL_CONTENT_TYPE,
      "content-disposition": `attachment; filename="project-manager-${stamp}.xlsx"`,
      "cache-control": "no-store",
    },
  });
}

async function health(): Promise<Response> {
  const rows = await sql<{ count: number }[]>`SELECT count(*)::int AS count FROM project_manager_projects`;
  return jsonResponse({
    ok: true,
    service: "project-manager",
    storage: { primary: "postgres", table: "project_manager_projects", projects: Number(rows[0]?.count ?? 0) },
    capabilities: ["crud", "excel-import", "excel-export"],
    deploy: {
      serviceId: config.deployServiceId,
      ref: config.deployRef,
      repo: config.deployRepo,
      commit: config.deployCommit,
      requestedCommit: config.deployRequestedCommit,
    },
    startedAt: serviceStartedAt,
  });
}

function notFound(pathname: string): Response {
  return jsonResponse({ ok: false, error: `not found: ${pathname}` }, 404);
}

async function handleRequest(req: Request): Promise<Response> {
  const url = new URL(req.url);
  const method = req.method.toUpperCase();
  if (method === "OPTIONS") return new Response(null, { status: 204 });
  if (url.pathname === "/health" && (method === "GET" || method === "HEAD")) return health();
  if (url.pathname === "/logs" && (method === "GET" || method === "HEAD")) return jsonResponse({ ok: true, logs: recentLogs.slice(-200) });
  if (url.pathname === "/api/projects/export.xlsx" && (method === "GET" || method === "HEAD")) return exportProjects();
  if (url.pathname === "/api/projects" && (method === "GET" || method === "HEAD")) return listProjects(url);
  if (url.pathname === "/api/projects" && method === "POST") return createProject(req);
  if (url.pathname === "/api/import/excel" && method === "POST") return importExcelRoute(req);
  if (url.pathname === "/api/import/projects" && method === "POST") return importRowsRoute(req);
  if (url.pathname.startsWith("/api/projects/")) {
    const id = decodeURIComponent(url.pathname.slice("/api/projects/".length));
    if (!id) return jsonResponse({ ok: false, error: "project id is required" }, 400);
    if (method === "GET" || method === "HEAD") {
      const project = await getProject(id);
      return project === null ? jsonResponse({ ok: false, error: `project not found: ${id}` }, 404) : jsonResponse({ ok: true, project });
    }
    if (method === "PUT" || method === "POST") return updateProjectRoute(req, id);
    if (method === "DELETE") return deleteProjectRoute(id);
  }
  return notFound(url.pathname);
}

async function main(): Promise<void> {
  await waitForSchema();
  const server = Bun.serve({
    hostname: config.host,
    port: config.port,
    async fetch(req): Promise<Response> {
      try {
        return await handleRequest(req);
      } catch (error) {
        return errorResponse(error, 500);
      }
    },
  });
  log("server_started", { host: config.host, port: config.port, url: server.url.toString() });
  console.log(`project-manager listening on ${server.url.toString()}`);
}

main().catch((error) => {
  log("server_failed", { error: errorToJson(error) });
  console.error(error);
  process.exit(1);
});
