import { createHash, randomUUID } from "node:crypto";
import { constants as fsConstants, createWriteStream } from "node:fs";
import { open, readdir, rename, rm, stat, unlink } from "node:fs/promises";
import path from "node:path";
import { ZipArchive } from "archiver";
import type Database from "better-sqlite3";
import ExcelJS from "exceljs";
import type { AppConfig } from "./config.js";
import { readStorageFile, safeStoragePath, sanitizeOriginalName } from "./files.js";

export type ExportAttachment = {
  id: string;
  kind: "payment_proof" | "invoice";
  originalName: string;
  mimeType: string;
  storagePath: string;
  sizeBytes: number;
  sha256: string;
};

export type ExportExpense = {
  id: string;
  paidAt: number;
  amountCents: number;
  note: string;
  invoiceMissingReason: string | null;
  status: "unreimbursed" | "reimbursed";
  attachments: ExportAttachment[];
};

export type ExportSnapshot = { expenses: ExportExpense[]; includeManifest?: boolean };

// The application is intentionally single-instance, but a queued export can
// still be triggered twice by a retry or two browser tabs. Keep one builder
// per job so both calls cannot write the same .part file concurrently.
const activeExportBuilds = new Set<string>();

export function safeExcelText(value: string): string {
  const cleaned = value.replace(/[\u0000-\u0008\u000b\u000c\u000e-\u001f]/g, "").slice(0, 32_767);
  return /^[\s\u0000-\u001f]*[=+\-@]/.test(cleaned) ? `'${cleaned}` : cleaned;
}

function dateParts(timestamp: number, timezone: string): { display: string; compact: string } {
  const formatter = new Intl.DateTimeFormat("zh-CN", {
    timeZone: timezone,
    year: "numeric",
    month: "2-digit",
    day: "2-digit",
    hour: "2-digit",
    minute: "2-digit",
    hour12: false,
  });
  const pieces = Object.fromEntries(formatter.formatToParts(timestamp).map((part) => [part.type, part.value]));
  return {
    display: `${pieces.year}-${pieces.month}-${pieces.day} ${pieces.hour}:${pieces.minute}`,
    compact: `${pieces.year}${pieces.month}${pieces.day}`,
  };
}

function uniqueAttachmentName(attachment: ExportAttachment, seen: Set<string>): string {
  const parsed = path.parse(sanitizeOriginalName(attachment.originalName));
  const fallbackExtension = path.extname(attachment.storagePath);
  const extension = (parsed.ext || fallbackExtension).slice(0, 16);
  const base = (parsed.name || attachment.kind).slice(0, 100);
  if ([base, extension].some((part) => part.includes("/") || part.includes("\\") || part === "." || part === "..")) {
    throw new Error("附件文件名包含非法路径片段");
  }
  let candidate = `${base}${extension}`;
  let counter = 2;
  while (seen.has(candidate.toLocaleLowerCase("und"))) candidate = `${base}_${counter++}${extension}`;
  seen.add(candidate.toLocaleLowerCase("und"));
  return candidate;
}

async function workbookBuffer(snapshot: ExportSnapshot, config: AppConfig): Promise<Buffer> {
  const workbook = new ExcelJS.Workbook();
  workbook.creator = "TallyNote";
  workbook.created = new Date();
  const sheet = workbook.addWorksheet("报销清单", { views: [{ state: "frozen", ySplit: 1 }] });
  sheet.columns = [
    { header: "序号", key: "sequence", width: 8 },
    { header: "支付时间", key: "paidAt", width: 22 },
    { header: "金额（元）", key: "amount", width: 16 },
    { header: "备注", key: "note", width: 44 },
    { header: "状态", key: "status", width: 14 },
    { header: "记录 ID", key: "id", width: 38 },
    { header: "付款凭证", key: "proofs", width: 38 },
    { header: "发票", key: "invoices", width: 38 },
    { header: "无发票原因", key: "invoiceMissingReason", width: 44 },
  ];
  sheet.getRow(1).font = { bold: true, color: { argb: "FFFFFFFF" } };
  sheet.getRow(1).fill = { type: "pattern", pattern: "solid", fgColor: { argb: "FF175CD3" } };
  sheet.getRow(1).height = 24;
  snapshot.expenses.forEach((expense, index) => {
    const row = sheet.addRow({
      sequence: index + 1,
      paidAt: dateParts(expense.paidAt, config.timezone).display,
      amount: expense.amountCents / 100,
      note: safeExcelText(expense.note),
      status: expense.status === "reimbursed" ? "已报销" : "未报销",
      id: expense.id,
      proofs: safeExcelText(expense.attachments.filter((item) => item.kind === "payment_proof").map((item) => item.originalName).join("；")),
      invoices: safeExcelText(expense.attachments.filter((item) => item.kind === "invoice").map((item) => item.originalName).join("；")),
      invoiceMissingReason: safeExcelText(expense.invoiceMissingReason || ""),
    });
    row.getCell("amount").numFmt = '¥#,##0.00';
    row.alignment = { vertical: "top", wrapText: true };
  });
  const totalRow = sheet.addRow({
    sequence: "合计",
    amount: snapshot.expenses.reduce((sum, expense) => sum + expense.amountCents, 0) / 100,
  });
  totalRow.font = { bold: true };
  totalRow.getCell("amount").numFmt = '¥#,##0.00';
  sheet.autoFilter = { from: "A1", to: "I1" };
  return Buffer.from(await workbook.xlsx.writeBuffer());
}

export async function buildExportJob(sqlite: Database.Database, config: AppConfig, jobId: string): Promise<void> {
  if (activeExportBuilds.has(jobId)) return;
  activeExportBuilds.add(jobId);
  try {
    await buildExportJobOnce(sqlite, config, jobId);
  } finally {
    activeExportBuilds.delete(jobId);
  }
}

async function buildExportJobOnce(sqlite: Database.Database, config: AppConfig, jobId: string): Promise<void> {
  const job = sqlite.prepare("SELECT snapshot_json AS snapshotJson FROM export_jobs WHERE id=? AND status IN ('queued','building')").get(jobId) as { snapshotJson: string } | undefined;
  if (!job) return;
  sqlite.prepare("UPDATE export_jobs SET status='building', error_message=NULL WHERE id=?").run(jobId);
  // Use a fresh O_EXCL path for every build. A deterministic `.part` path can
  // be pre-created as a symlink by another local process and then followed by
  // createWriteStream. The final rename remains atomic and replaces only the
  // destination entry itself.
  const partialPath = path.join(config.exportsDir, `${jobId}.zip.part-${randomUUID()}`);
  const finalPath = path.join(config.exportsDir, `${jobId}.zip`);
  try {
    const snapshot = JSON.parse(job.snapshotJson) as ExportSnapshot;
    const output = createWriteStream(partialPath, { flags: "wx", mode: 0o600 });
    const archive = new ZipArchive({ zlib: { level: 6 } });
    const completed = new Promise<void>((resolve, reject) => {
      output.on("close", resolve);
      output.on("error", reject);
      archive.on("warning", reject);
      archive.on("error", reject);
    });
    archive.pipe(output);
    archive.append(await workbookBuffer(snapshot, config), { name: "报销清单.xlsx" });
    if (snapshot.includeManifest === true) {
      const manifest = {
        generatedAt: new Date().toISOString(),
        records: snapshot.expenses.map((expense) => ({
          id: expense.id,
          paidAt: dateParts(expense.paidAt, config.timezone).display,
          amountCents: expense.amountCents,
          invoiceMissingReason: expense.invoiceMissingReason || null,
          attachments: expense.attachments.map((attachment) => ({
            id: attachment.id,
            kind: attachment.kind,
            originalName: attachment.originalName,
            mimeType: attachment.mimeType,
            sizeBytes: attachment.sizeBytes,
            sha256: attachment.sha256,
          })),
        })),
      };
      archive.append(JSON.stringify(manifest, null, 2), { name: "manifest.json" });
    }
    for (const [index, expense] of snapshot.expenses.entries()) {
      const date = dateParts(expense.paidAt, config.timezone).compact;
      const folder = `${String(index + 1).padStart(3, "0")}_${date}_${(expense.amountCents / 100).toFixed(2)}_${expense.id.slice(0, 8)}`;
      const seen = new Set<string>();
      for (const attachment of expense.attachments) {
        const group = attachment.kind === "payment_proof" ? "付款凭证" : "发票";
        const fileName = uniqueAttachmentName(attachment, seen);
        const absolute = safeStoragePath(config.filesDir, attachment.storagePath);
        const bytes = await readStorageFile(config, attachment.storagePath);
        const digest = createHash("sha256").update(bytes).digest("hex");
        if (bytes.length !== attachment.sizeBytes || digest !== attachment.sha256) {
          throw new Error(`附件校验失败：${attachment.id}`);
        }
        archive.append(bytes, { name: `${folder}/${group}/${fileName}` });
      }
    }
    await archive.finalize();
    await completed;
    await rename(partialPath, finalPath);
    // Open without following symlinks and keep the descriptor for the digest
    // and size read. This closes the check/use gap around the published file.
    const handle = await open(finalPath, fsConstants.O_RDONLY | (fsConstants.O_NOFOLLOW ?? 0));
    let bytes: Buffer;
    let info;
    try {
      info = await handle.stat();
      if (!info.isFile()) throw new Error("导出文件类型无效");
      bytes = await handle.readFile();
    } finally {
      await handle.close();
    }
    const published = sqlite.prepare(`
      UPDATE export_jobs SET status='ready', file_path=?, size_bytes=?, sha256=?, ready_at=?
      WHERE id=? AND status='building'
    `).run(path.basename(finalPath), info.size, createHash("sha256").update(bytes).digest("hex"), Date.now(), jobId);
    if (published.changes !== 1) await rm(finalPath, { force: true });
  } catch (error) {
    await rm(partialPath, { force: true });
    await rm(finalPath, { force: true });
    // Never expose filesystem paths, attachment IDs, or raw OS errors through
    // the export status API. Keep a small allowlist of actionable messages.
    const raw = error instanceof Error ? error.message : "";
    const safe = (error as { code?: unknown } | null)?.code === "ATTACHMENT_MISSING"
      || raw.startsWith("附件校验失败") || raw.includes("ENOENT")
      ? "导出失败：附件文件缺失或校验不通过"
      : "导出失败：服务器无法生成导出文件";
    sqlite.prepare("UPDATE export_jobs SET status='failed', error_message=? WHERE id=? AND status='building'").run(safe, jobId);
  }
}

export function insertExportJob(
  sqlite: Database.Database,
  config: AppConfig,
  input: { adminId: string; sessionHash: string; selection: unknown; snapshot: ExportSnapshot },
): string {
  const id = randomUUID();
  const now = Date.now();
  sqlite.prepare(`
    INSERT INTO export_jobs (
      id, admin_id, session_hash, status, selection_json, snapshot_json,
      file_name, created_at, expires_at
    ) VALUES (?, ?, ?, 'queued', ?, ?, ?, ?, ?)
  `).run(
    id,
    input.adminId,
    input.sessionHash,
    JSON.stringify(input.selection),
    JSON.stringify(input.snapshot),
    `TallyNote_报销资料_${id.slice(0, 8)}.zip`,
    now,
    now + config.exportTtlMs,
  );
  return id;
}

export async function resumeExports(sqlite: Database.Database, config: AppConfig): Promise<void> {
  const jobs = sqlite.prepare("SELECT id FROM export_jobs WHERE status IN ('queued','building') AND expires_at > ?").all(Date.now()) as Array<{ id: string }>;
  for (const job of jobs) await buildExportJob(sqlite, config, job.id);
}

export async function expireExports(sqlite: Database.Database, config: AppConfig): Promise<void> {
  const rows = sqlite.prepare("SELECT id, file_path AS filePath FROM export_jobs WHERE status != 'expired' AND expires_at <= ?").all(Date.now()) as Array<{ id: string; filePath: string | null }>;
  for (const row of rows) {
    if (row.filePath) {
      await unlink(safeStoragePath(config.exportsDir, row.filePath)).catch((error: NodeJS.ErrnoException) => {
        if (error.code !== "ENOENT") throw error;
      });
    }
    sqlite.prepare("UPDATE export_jobs SET status='expired', file_path=NULL WHERE id=?").run(row.id);
  }
}

export async function cleanupOrphanedExports(sqlite: Database.Database, config: AppConfig): Promise<void> {
  const referenced = new Set((sqlite.prepare("SELECT file_path AS filePath FROM export_jobs WHERE status='ready' AND file_path IS NOT NULL AND expires_at > ?").all(Date.now()) as Array<{ filePath: string }>).map((row) => row.filePath));
  const activeJobs = sqlite.prepare("SELECT id FROM export_jobs WHERE status IN ('queued','building') AND expires_at > ?").all(Date.now()) as Array<{ id: string }>;
  for (const job of activeJobs) {
    referenced.add(`${job.id}.zip`);
  }
  const cutoff = Date.now() - 10 * 60 * 1000;
  for (const entry of await readdir(config.exportsDir, { withFileTypes: true })) {
    if (!entry.isFile() && !entry.isSymbolicLink()) continue;
    const target = path.join(config.exportsDir, entry.name);
    const info = await stat(target).catch(() => null);
    const belongsToActiveBuild = activeJobs.some((job) => entry.name.startsWith(`${job.id}.zip.part-`));
    if (info && info.mtimeMs < cutoff && !referenced.has(entry.name) && !belongsToActiveBuild) await rm(target, { force: true });
  }
}