import { documentLabel } from "../lib/document-labels.ts";
import { createHash, randomUUID } from "node:crypto";
import { readFile } from "node:fs/promises";
import { resolve, sep } from "node:path";
import { DB } from "./database.ts";
import { FILES } from "./storage.ts";
import {
  generateApplicationPdf,
  type ApplicationPdfData,
} from "../pdf/application.ts";
import { pdfConfig } from "../config/pdf.ts";
const env = { DB, FILES };
type Main = {
  public_number: string;
  submitted_at: string;
  year_name: string;
  response_name: string;
  full_name: string;
  birth_date: string;
  current_student: number;
  student_number: string | null;
  address: string;
  postal_code: string;
  locality: string;
  citizen_card_number: string;
  citizen_card_expires_at: string;
  tax_number: string;
  social_security_number: string;
  guardian_mode: string;
  social_support_type: string | null;
  works_in_influence_area: number;
};
type Guardian = {
  role: string;
  full_name: string;
  relationship: string | null;
  address: string;
  postal_code: string;
  locality: string;
  citizen_card_number: string;
  citizen_card_expires_at: string;
  tax_number: string;
  phone: string;
  email: string;
};
type Member = {
  full_name: string;
  relationship: string;
  monthly_net_income_cents: number;
};
type Expense = { type: string; amount_cents: number };
type Doc = { type: string; original_name: string };

const money = (cents: number) =>
  new Intl.NumberFormat("pt-PT", { style: "currency", currency: "EUR" }).format(
    cents / 100,
  );
export async function loadApplicationPdfData(
  id: string,
): Promise<ApplicationPdfData> {
  const main = await env.DB.prepare(
    "SELECT a.public_number,a.submitted_at,y.name year_name,s.name response_name,c.full_name,c.birth_date,c.current_student,c.student_number,c.address,c.postal_code,c.locality,c.citizen_card_number,c.citizen_card_expires_at,c.tax_number,c.social_security_number,d.guardian_mode,d.social_support_type,d.works_in_influence_area FROM applications a JOIN academic_years y ON y.id=a.academic_year_id JOIN social_responses s ON s.id=a.social_response_id JOIN children c ON c.application_id=a.id JOIN application_declarations d ON d.application_id=a.id WHERE a.id=? AND a.deleted_at IS NULL",
  )
    .bind(id)
    .first<Main>();
  if (!main) throw new Error("application_not_found");
  const [guardians, members, expenses, documents, siblings] = await Promise.all(
    [
      env.DB.prepare(
        "SELECT role,full_name,relationship,address,postal_code,locality,citizen_card_number,citizen_card_expires_at,tax_number,phone,email FROM guardians WHERE application_id=? ORDER BY is_education_guardian DESC,created_at",
      )
        .bind(id)
        .all<Guardian>(),
      env.DB.prepare(
        "SELECT full_name,relationship,monthly_net_income_cents FROM household_members WHERE application_id=? ORDER BY position",
      )
        .bind(id)
        .all<Member>(),
      env.DB.prepare(
        "SELECT type,amount_cents FROM expenses WHERE application_id=? ORDER BY created_at",
      )
        .bind(id)
        .all<Expense>(),
      env.DB.prepare(
        "SELECT type,original_name FROM documents WHERE application_id=? ORDER BY created_at",
      )
        .bind(id)
        .all<Doc>(),
      env.DB.prepare(
        "SELECT student_number FROM siblings WHERE application_id=?",
      )
        .bind(id)
        .all<{ student_number: string }>(),
    ],
  );
  return {
    publicNumber: main.public_number,
    submittedAt: main.submitted_at,
    academicYear: main.year_name,
    socialResponse: main.response_name,
    child: [
      { label: "Nome", value: main.full_name },
      { label: "Data de nascimento", value: main.birth_date },
      { label: "Aluno atual", value: main.current_student ? "Sim" : "Não" },
      { label: "Número de aluno", value: main.student_number || "" },
      {
        label: "Morada",
        value: `${main.address}, ${main.postal_code} ${main.locality}`,
      },
      {
        label: "Cartão de cidadão",
        value: `${main.citizen_card_number} (válido até ${main.citizen_card_expires_at})`,
      },
      { label: "NIF", value: main.tax_number },
      { label: "NISS", value: main.social_security_number },
      {
        label: "Irmãos na instituição",
        value:
          siblings.results.map((s) => s.student_number).join(", ") || "Não",
      },
    ],
    guardians: guardians.results.map((item, index) => ({
      title: `Responsável ${index + 1} - ${item.role}`,
      lines: [
        { label: "Nome", value: item.full_name },
        { label: "Parentesco", value: item.relationship || "" },
        {
          label: "Morada",
          value: `${item.address}, ${item.postal_code} ${item.locality}`,
        },
        {
          label: "Cartão de cidadão",
          value: `${item.citizen_card_number} (válido até ${item.citizen_card_expires_at})`,
        },
        { label: "NIF", value: item.tax_number },
        { label: "Telefone", value: item.phone },
        { label: "Email", value: item.email },
      ],
    })),
    household: members.results.map((item) => ({
      name: item.full_name,
      relationship: item.relationship,
      incomeCents: item.monthly_net_income_cents,
    })),
    expenses: expenses.results.map((item) => ({
      label:
        { HOUSING: "Habitação", TRANSPORT: "Transportes", HEALTH: "Saúde" }[
          item.type
        ] || item.type,
      value: money(item.amount_cents),
    })),
    documents: documents.results.map((item) => ({
      type: documentLabel(item.type),
      name: item.original_name,
    })),
    declarations: [
      { label: "Responsáveis", value: main.guardian_mode },
      { label: "Apoio social", value: main.social_support_type || "Não" },
      {
        label: "Trabalha na área de influência",
        value: main.works_in_influence_area ? "Sim" : "Não",
      },
      {
        label: "Confirmações",
        value:
          "Informação, veracidade e política de privacidade aceites na submissão",
      },
    ],
  };
}
export async function ensureApplicationPdf(id: string, regenerate = false) {
  const active = await DB.prepare(
    "SELECT id FROM applications WHERE id=? AND deleted_at IS NULL",
  )
    .bind(id)
    .first();
  if (!active) throw new Error("application_not_found");
  const existing = await DB.prepare(
    "SELECT storage_key FROM generated_files WHERE application_id=? AND kind='APPLICATION_PDF'",
  )
    .bind(id)
    .first<{ storage_key: string }>();
  if (existing && !regenerate) {
    const stored = await FILES.get(existing.storage_key);
    if (stored) return stored.body;
  }
  const data = await loadApplicationPdfData(id);
  let logo: Uint8Array | undefined;
  if (pdfConfig.logoPath) {
    const root = resolve("public"),
      path = resolve(root, pdfConfig.logoPath);
    if (!path.startsWith(root + sep)) throw new Error("invalid_logo_path");
    logo = await readFile(path);
  }
  const bytes = await generateApplicationPdf(data, logo),
    key = `applications/${id}/generated/ficha-inscricao.pdf`,
    now = new Date().toISOString();
  await FILES.put(key, bytes);
  await DB.prepare(
    "INSERT INTO generated_files(id,application_id,kind,storage_key,original_name,mime_type,size_bytes,sha256,created_at,updated_at) VALUES (?,?,'APPLICATION_PDF',?,'ficha-inscricao.pdf','application/pdf',?,?,?,?) ON CONFLICT(storage_key) DO UPDATE SET size_bytes=excluded.size_bytes,sha256=excluded.sha256,updated_at=excluded.updated_at",
  )
    .bind(
      randomUUID(),
      id,
      key,
      bytes.length,
      createHash("sha256").update(bytes).digest("hex"),
      now,
      now,
    )
    .run();
  return bytes;
}