/**
 * Reconcile the `cylinders` table against an Excel sheet.
 *
 * The sheet is the source of truth for both *which* cylinders exist and their
 * details. So:
 *   - in sheet + in DB  -> overwrite every mapped column from the sheet
 *   - in sheet, not DB  -> create from the sheet columns
 *   - in DB, not sheet  -> delete (cascades to logs / maintenance / client_cylinders)
 *
 * Rows are matched on `item_id`.
 *
 * Usage:
 *   npx ts-node --transpile-only scripts/import-cylinders.ts <file.xlsx> [--apply] [--sheet="Sheet1"]
 *
 * Without --apply the script only prints the plan and touches nothing.
 */
import * as path from 'path';
import * as readline from 'readline';
import { PrismaClient, CylinderStatus } from '@prisma/client';
import * as ExcelJS from 'exceljs';

const CREATED_BY = '807a86a0-ac5f-4c01-8efc-d164601eca12';

const prisma = new PrismaClient();

type SheetRow = {
  row: number;
  item_id: string;
  rfid: string | null;
  tareWeight: string | null;
  hydrostaticTestDate: Date | null;
  hydrostaticDueDate: Date | null;
  waterCapacity: string | null;
  manufacturingDate: Date | null;
  status: CylinderStatus;
};

/** `Hydrostatic Test Date` / `hydrostaticTestDate` / `HYDROSTATIC_TEST_DATE` all collapse to the same key. */
const canonicalize = (header: string) => header.toLowerCase().replace(/[^a-z0-9]/g, '');

const COLUMNS: Record<string, keyof SheetRow> = {
  itemid: 'item_id',
  rfid: 'rfid',
  tareweight: 'tareWeight',
  hydrostatictestdate: 'hydrostaticTestDate',
  hydrostaticduedate: 'hydrostaticDueDate',
  watercapacity: 'waterCapacity',
  manufacturingdate: 'manufacturingDate',
  status: 'status',
};

const REQUIRED_COLUMNS = ['item_id'];

/** Unwrap the shapes ExcelJS hands back: formulas, hyperlinks, rich text. */
function cellValue(cell: ExcelJS.Cell): unknown {
  const v = cell.value;
  if (v === null || v === undefined) return null;
  if (typeof v === 'object') {
    if (v instanceof Date) return v;
    if ('result' in v) return (v as ExcelJS.CellFormulaValue).result ?? null;
    if ('text' in v) return (v as ExcelJS.CellHyperlinkValue).text ?? null;
    if ('richText' in v) {
      return (v as ExcelJS.CellRichTextValue).richText.map((r) => r.text).join('');
    }
    if ('error' in v) throw new Error(`cell error: ${String((v as ExcelJS.CellErrorValue).error)}`);
  }
  return v;
}

function asText(value: unknown): string | null {
  if (value === null || value === undefined) return null;
  if (value instanceof Date) return value.toISOString();
  const s = String(value).trim();
  return s === '' ? null : s;
}

/**
 * Excel stores naive datetimes; ExcelJS reads them as UTC instants. Both the
 * sheet and Prisma's DateTime treat these as date-only values, so pass through.
 */
function asDate(value: unknown, field: string, row: number): Date | null {
  if (value === null || value === undefined || value === '') return null;
  if (value instanceof Date) return value;

  const parsed = new Date(String(value));
  if (Number.isNaN(parsed.getTime())) {
    throw new Error(`row ${row}: cannot parse ${field} value ${JSON.stringify(value)} as a date`);
  }
  return parsed;
}

function asStatus(value: unknown, row: number): CylinderStatus {
  const text = asText(value);
  if (!text) return CylinderStatus.NEW;

  const upper = text.toUpperCase();
  if (!(upper in CylinderStatus)) {
    throw new Error(
      `row ${row}: status ${JSON.stringify(text)} is not one of ${Object.keys(CylinderStatus).join(', ')}`,
    );
  }
  return CylinderStatus[upper as keyof typeof CylinderStatus];
}

async function readSheet(file: string, sheetName?: string): Promise<SheetRow[]> {
  const workbook = new ExcelJS.Workbook();
  await workbook.xlsx.readFile(file);

  const sheet = sheetName ? workbook.getWorksheet(sheetName) : workbook.worksheets[0];
  if (!sheet) {
    const available = workbook.worksheets.map((w) => w.name).join(', ');
    throw new Error(`sheet ${JSON.stringify(sheetName)} not found. Available: ${available}`);
  }

  const headerRow = sheet.getRow(1);
  const fieldByCol = new Map<number, keyof SheetRow>();
  headerRow.eachCell((cell, col) => {
    const header = asText(cellValue(cell));
    if (!header) return;
    const field = COLUMNS[canonicalize(header)];
    if (field) fieldByCol.set(col, field);
  });

  const mapped = new Set(fieldByCol.values());
  const missing = REQUIRED_COLUMNS.filter((c) => !mapped.has(c as keyof SheetRow));
  if (missing.length) {
    throw new Error(`sheet "${sheet.name}" is missing required column(s): ${missing.join(', ')}`);
  }

  const rows: SheetRow[] = [];
  const errors: string[] = [];
  const lossyRfid: string[] = [];

  for (let rowNumber = 2; rowNumber <= sheet.rowCount; rowNumber++) {
    const row = sheet.getRow(rowNumber);
    const raw: Partial<Record<keyof SheetRow, unknown>> = {};
    let empty = true;

    for (const [col, field] of fieldByCol) {
      const value = cellValue(row.getCell(col));
      raw[field] = value;
      if (value !== null && value !== '') empty = false;
    }
    if (empty) continue;

    try {
      const item_id = asText(raw.item_id);
      if (!item_id) throw new Error(`row ${rowNumber}: item_id is empty`);

      // Excel holds numbers as float64. Above 2^53 not every integer is
      // representable, so a numeric RFID *may* have been rounded when the file
      // was written. String(n) recovers the double's exact digits, so values
      // that survive are fine; we can only flag the risk, never repair it.
      if (typeof raw.rfid === 'number') {
        if (!Number.isInteger(raw.rfid)) {
          throw new Error(`row ${rowNumber}: rfid ${raw.rfid} is not a whole number`);
        }
        if (!Number.isSafeInteger(raw.rfid)) {
          lossyRfid.push(`row ${rowNumber} (item_id ${item_id}) -> ${BigInt(raw.rfid)}`);
        }
      }

      rows.push({
        row: rowNumber,
        item_id,
        rfid: asText(raw.rfid),
        tareWeight: asText(raw.tareWeight),
        hydrostaticTestDate: asDate(raw.hydrostaticTestDate, 'hydrostaticTestDate', rowNumber),
        hydrostaticDueDate: asDate(raw.hydrostaticDueDate, 'hydrostaticDueDate', rowNumber),
        waterCapacity: asText(raw.waterCapacity),
        manufacturingDate: asDate(raw.manufacturingDate, 'manufacturingDate', rowNumber),
        status: asStatus(raw.status, rowNumber),
      });
    } catch (err) {
      errors.push(err instanceof Error ? err.message : String(err));
    }
  }

  if (lossyRfid.length) {
    const shown = lossyRfid.slice(0, 5).join('\n    ');
    const more = lossyRfid.length > 5 ? `\n    ... and ${lossyRfid.length - 5} more` : '';
    console.warn(
      `\nWarning: ${lossyRfid.length} RFID cell(s) are numeric and exceed 2^53, so Excel cannot\n` +
        `guarantee every digit. The values below are what the file actually contains -- spot-check\n` +
        `a few, and format the rfid column as Text in Excel to remove the ambiguity.\n    ` +
        shown +
        more,
    );
  }

  assertUnique(rows, 'item_id', errors);
  assertUnique(rows, 'rfid', errors);

  if (errors.length) {
    throw new Error(`sheet "${sheet.name}" has ${errors.length} problem(s):\n  - ${errors.join('\n  - ')}`);
  }
  return rows;
}

function assertUnique(rows: SheetRow[], field: 'item_id' | 'rfid', errors: string[]): void {
  const seen = new Map<string, number>();
  for (const row of rows) {
    const value = row[field];
    if (value === null) continue; // rfid is nullable and DB-unique only on non-nulls
    const first = seen.get(value);
    if (first !== undefined) {
      errors.push(`duplicate ${field} ${JSON.stringify(value)} on rows ${first} and ${row.row}`);
    } else {
      seen.set(value, row.row);
    }
  }
}

function confirm(question: string): Promise<boolean> {
  const rl = readline.createInterface({ input: process.stdin, output: process.stdout });
  return new Promise((resolve) =>
    rl.question(question, (answer) => {
      rl.close();
      resolve(answer.trim().toLowerCase() === 'yes');
    }),
  );
}

async function main() {
  const args = process.argv.slice(2);
  const file = args.find((a) => !a.startsWith('--'));
  const apply = args.includes('--apply');
  const assumeYes = args.includes('--yes');
  const sheetName = args.find((a) => a.startsWith('--sheet='))?.split('=').slice(1).join('=');

  if (!file) {
    throw new Error(
      'usage: ts-node --transpile-only scripts/import-cylinders.ts <file.xlsx> [--apply] [--yes] [--sheet=Name]',
    );
  }

  const creator = await prisma.users.findUnique({
    where: { id: CREATED_BY },
    select: { id: true, email: true },
  });
  if (!creator) {
    // `created_by` has no FK, so nothing else would catch a bad id.
    throw new Error(`created_by user ${CREATED_BY} does not exist in this database`);
  }

  const rows = await readSheet(path.resolve(file), sheetName);
  if (!rows.length) {
    throw new Error('sheet has no data rows; refusing to delete every cylinder');
  }

  const existing = await prisma.cylinders.findMany({ select: { id: true, item_id: true, rfid: true } });
  const existingByItemId = new Map(existing.map((c) => [c.item_id, c]));
  const sheetItemIds = new Set(rows.map((r) => r.item_id));

  const toCreate = rows.filter((r) => !existingByItemId.has(r.item_id));
  const toUpdate = rows.filter((r) => existingByItemId.has(r.item_id));
  const toDelete = existing.filter((c) => !sheetItemIds.has(c.item_id));
  const deleteIds = toDelete.map((c) => c.id);

  // `rfid` is unique. If the sheet moves an rfid from one surviving cylinder to
  // another, a row-by-row update transiently has it on two rows and the
  // constraint fires. Clearing the changed ones up front sidesteps the ordering.
  const rfidMoved = toUpdate
    .filter((r) => existingByItemId.get(r.item_id)!.rfid !== r.rfid)
    .map((r) => existingByItemId.get(r.item_id)!.id);

  const [logs, maintenance, assignments] = await Promise.all([
    prisma.cylinder_logs.count({ where: { cylinderId: { in: deleteIds } } }),
    prisma.maintenance_history.count({ where: { cylinderId: { in: deleteIds } } }),
    prisma.client_cylinders.count({ where: { cylinderId: { in: deleteIds } } }),
  ]);

  console.log(`\nSheet rows:        ${rows.length}`);
  console.log(`Cylinders in DB:   ${existing.length}`);
  console.log(`created_by:        ${creator.id} (${creator.email})\n`);
  console.log(`  create  ${toCreate.length}`);
  console.log(`  update  ${toUpdate.length}  (all sheet columns overwritten)`);
  console.log(`  delete  ${toDelete.length}`);
  if (deleteIds.length) {
    console.log(`\n  Deleting cascades to ${logs} cylinder_logs, ${maintenance} maintenance_history,`);
    console.log(`  and ${assignments} client_cylinders row(s). This cannot be undone.`);
  }
  console.log(`\nAfter import the DB will hold ${rows.length} cylinders.\n`);

  if (!apply) {
    console.log('Dry run — nothing was changed. Re-run with --apply to execute.\n');
    return;
  }

  if (!assumeYes) {
    if (!process.stdin.isTTY) {
      throw new Error('--apply needs an interactive terminal to confirm the deletions, or pass --yes');
    }
    const ok = await confirm(`Permanently delete ${toDelete.length} cylinder(s) and their history? Type "yes": `);
    if (!ok) {
      console.log('Aborted.\n');
      return;
    }
  }

  await prisma.$transaction(
    async (tx) => {
      // Delete first: a new row may reuse the RFID of a cylinder being removed,
      // and `rfid` is unique.
      if (deleteIds.length) {
        await tx.cylinders.deleteMany({ where: { id: { in: deleteIds } } });
      }

      if (rfidMoved.length) {
        await tx.cylinders.updateMany({ where: { id: { in: rfidMoved } }, data: { rfid: null } });
      }

      // One statement per row: `data` differs per cylinder, so updateMany can't do it.
      for (const { row: _row, item_id, ...cylinder } of toUpdate) {
        await tx.cylinders.update({
          where: { id: existingByItemId.get(item_id)!.id },
          data: { ...cylinder, created_by: CREATED_BY },
        });
      }

      if (toCreate.length) {
        await tx.cylinders.createMany({
          data: toCreate.map(({ row: _row, ...cylinder }) => ({ ...cylinder, created_by: CREATED_BY })),
        });
      }
    },
    { timeout: 120_000 },
  );

  const total = await prisma.cylinders.count();
  console.log(`\nDone. Created ${toCreate.length}, updated ${toUpdate.length}, deleted ${toDelete.length}.`);
  console.log(`Cylinders in DB: ${total}\n`);
}

main()
  .catch((err) => {
    console.error(`\n${err instanceof Error ? err.message : String(err)}\n`);
    process.exitCode = 1;
  })
  .finally(() => prisma.$disconnect());