import ExcelJS from "exceljs";
import { one, query } from "../../db/client.js";

/** Shared Excel import/export engine for members (owner order 2026-10-29, section 7). */

const norm = (s: string) => s.replace(/\s+/g, " ").replace(/[أإآ]/g, "ا").trim();

export interface ParsedRow {
  row: number;
  name: string;
  membershipNo: string | null;
  phone: string | null;
  governorateId: number | null;
  errors: string[];
}

export interface ParseResult {
  rows: ParsedRow[];
  errors: { row: number; name: string; reason: string }[];
  validCount: number;
}

/** Loads the official governorates once and returns a normalized-name → id matcher. */
export async function governorateMatcher(): Promise<(name: string) => number | null> {
  const rows = (await query<{ id: number; name_ar: string }>(`SELECT id, name_ar FROM governorates`)).rows;
  const map = new Map<string, number>();
  for (const g of rows) map.set(norm(g.name_ar), g.id);
  return (name: string) => map.get(norm(name)) ?? null;
}

/** Parses an uploaded member workbook. No writes — preview and commit share this. */
export async function parseWorkbook(buffer: Buffer): Promise<ParseResult> {
  const wb = new ExcelJS.Workbook();
  await wb.xlsx.load(buffer as unknown as ArrayBuffer);
  const ws = wb.getWorksheet(1);
  if (!ws || ws.rowCount < 2) {
    return { rows: [], errors: [{ row: 1, name: "—", reason: "الملف فارغ أو بلا صفوف بيانات" }], validCount: 0 };
  }
  const matchGov = await governorateMatcher();

  const takenNos = new Set(
    (await query<{ membership_no: string }>(`SELECT membership_no::text AS membership_no FROM members WHERE membership_no IS NOT NULL AND deleted_at IS NULL`)).rows.map((r) => r.membership_no),
  );
  const seenNos = new Set<string>();

  const errors: ParseResult["errors"] = [];
  const rows: ParsedRow[] = [];
  for (let r = 2; r <= ws.rowCount; r++) {
    const v = ws.getRow(r).values as unknown[];
    const name = String(v[1] ?? "").replace(/\s+/g, " ").trim();
    if (!name && !String(v[2] ?? "").trim() && !String(v[3] ?? "").trim() && !String(v[4] ?? "").trim()) continue; // blank line
    const rowErrors: string[] = [];
    if (name.length < 3) rowErrors.push("الاسم مطلوب (3 أحرف على الأقل)");

    let membershipNo: string | null = String(v[3] ?? "").trim() || null;
    // Owner decision (2026-10-29): the OFFICIAL number is REQUIRED at interactive import
    if (!membershipNo) rowErrors.push("رقم العضوية مطلوب (يبدأ الترقيم من 1 ويعتمد من إدارة الحزب)");
    if (membershipNo) {
      if (!/^[1-9][0-9]{0,9}$/.test(membershipNo)) {
        rowErrors.push(`رقم العضوية يجب أن يكون رقمًا طبيعيًا (${membershipNo})`);
      } else if (seenNos.has(membershipNo) || takenNos.has(membershipNo)) {
        rowErrors.push(`رقم العضوية مكرر (${membershipNo})`);
      } else {
        seenNos.add(membershipNo);
      }
    }

    let phone: string | null = String(v[4] ?? "").replace(/[^0-9]/g, "") || null;
    if (phone && phone.length === 10 && phone.startsWith("1")) phone = "0" + phone;
    if (phone && !/^01[0-9]{9}$/.test(phone)) rowErrors.push(`رقم موبايل غير صالح (${phone})`);

    const govName = String(v[2] ?? "").trim();
    const governorateId = govName ? matchGov(govName) : null;
    if (govName && !governorateId) rowErrors.push(`محافظة غير معروفة (${govName})`);

    const row = { row: r, name, membershipNo, phone, governorateId, errors: rowErrors };
    if (rowErrors.length === 0) rows.push(row);
    else errors.push({ row: r, name: name || "—", reason: rowErrors.join(" · ") });
  }
  return { rows, errors, validCount: rows.length };
}

/** Builds the downloadable import template (headers + one guidance row). */
export async function buildTemplate(): Promise<Buffer> {
  const wb = new ExcelJS.Workbook();
  const ws = wb.addWorksheet("الأعضاء");
  ws.columns = [
    { header: "الاسم (إلزامي)", width: 34 },
    { header: "المحافظة (اسم رسمي)", width: 20 },
    { header: "رقم العضوية (إلزامي — رقم طبيعي فريد يبدأ من 1)", width: 30 },
    { header: "رقم الموبايل (اختياري — 01xxxxxxxxx)", width: 26 },
  ];
  ws.addRow(["مثال: أحمد محمد", "القاهرة", "", "01000000000"]);
  ws.getRow(1).font = { bold: true };
  ws.getRow(2).font = { italic: true, color: { argb: "FF888888" } };
  const out = Buffer.from(await wb.xlsx.writeBuffer());
  return out;
}

/** Neutralizes spreadsheet formula injection in untrusted text. */
export function safeCell(value: string | null | undefined): string | null {
  if (value == null) return null;
  const v = String(value);
  return /^[=+\-@\t]/.test(v) ? `'${v}` : v;
}

export interface ExportRow {
  membership_no: string | null;
  system_no: string | null;
  full_name: string;
  governorate_name: string | null;
  secretariat_name: string | null;
  formal_status_ar: string | null;
  created_at: string;
}

/** Builds the authorized member-list export workbook (no restricted fields by design). */
export async function buildExport(items: ExportRow[], scopeLabel: string): Promise<Buffer> {
  const wb = new ExcelJS.Workbook();
  const ws = wb.addWorksheet("الأعضاء");
  ws.addRow(["تصدير أعضاء بوصلة — النطاق: " + scopeLabel, "", "", "", "", "", ""]);
  ws.addRow(["تاريخ التوليد: " + new Date().toISOString().slice(0, 10) + " — بيانات محدودة بالصلاحيات (بلا بيانات اتصال أو أرقام قومية)"]);
  ws.addRow([]);
  ws.addRow(["رقم العضوية", "الرقم النظامي", "الاسم", "المحافظة", "الأمانة", "الحالة", "تاريخ الإنشاء"]);
  ws.getRow(3).font = { bold: true };
  for (const it of items) {
    ws.addRow([
      it.membership_no ?? "",
      safeCell(it.system_no),
      safeCell(it.full_name),
      safeCell(it.governorate_name),
      safeCell(it.secretariat_name),
      safeCell(it.formal_status_ar),
      it.created_at?.slice(0, 10) ?? "",
    ]);
  }
  ws.getColumn(1).width = 14;
  ws.getColumn(2).width = 18;
  ws.getColumn(3).width = 34;
  ws.getColumn(4).width = 16;
  ws.getColumn(5).width = 26;
  ws.getColumn(6).width = 24;
  ws.getColumn(7).width = 14;
  return Buffer.from(await wb.xlsx.writeBuffer());
}

/** Inserts validated rows transactionally-safe data (caller wraps in a transaction). */
export async function insertValidatedRows(
  client: { query: <T>(sql: string, params?: unknown[]) => Promise<{ rows: T[]; rowCount: number | null }> },
  rows: ParsedRow[],
  adminId: number,
  batch: string,
): Promise<{ members: number; contacts: number }> {
  let members = 0;
  let contacts = 0;
  for (const r of rows) {
    const ins = await client.query(
      `INSERT INTO members (public_uuid, full_name, governorate_id, formal_membership_status_id, membership_no, record_source, is_demo, created_by)
       VALUES (gen_random_uuid(), $1, $2,
         (SELECT s.id FROM statuses s JOIN status_groups sg ON sg.id = s.group_id WHERE sg.code = 'member_formal_status' AND s.code = 'unspecified'),
         $3, $4, FALSE, $5) RETURNING id`,
      [r.name, r.governorateId, r.membershipNo, batch, adminId],
    );
    members++;
    const memberId = (ins.rows[0] as { id: number }).id;
    if (r.phone) {
      await client.query(
        `INSERT INTO member_contacts (member_id, phone_primary, created_by) VALUES ($1, $2, $3)`,
        [memberId, r.phone, adminId],
      );
      contacts++;
    }
  }
  return { members, contacts };
}

/** Membership numbers that already exist among live members (for import-time checks). */
export async function existingMembershipNos(): Promise<Set<string>> {
  return new Set(
    (await query<{ membership_no: string }>(`SELECT membership_no::text AS membership_no FROM members WHERE membership_no IS NOT NULL AND deleted_at IS NULL`)).rows.map((r) => r.membership_no),
  );
}

export async function sysadminId(): Promise<number | null> {
  const r = await one<{ id: number }>(`SELECT id FROM users WHERE username = 'sysadmin'`);
  return r?.id ?? null;
}