import { z } from 'zod';
import { BugApp, Role, type Prisma } from '@prisma/client';
import { prismaUnscoped as db } from '../../lib/prisma';
import { ForbiddenError } from '../../lib/errors';
import { writeXlsx, type Cell, type Sheet } from '../../lib/xlsx';
import type { BugActor } from './bug-access';
import { BUG_APPS } from './bug-modules.service';
import { TEST_LIMITS } from './bug-testing.service';

/**
 * Test checklists to and from a sheet (docs/bug-tracker-plan.md, after BT6).
 *
 * One row per item: Checklist, App, Description, Item, How to test it, Module.
 * Rows with the same Checklist name (whatever the capitals) are one checklist,
 * in the order they come. App and Description are read from the first row of
 * a checklist that has them. The export writes the same columns, so a file
 * can go out, be edited, and come back.
 *
 * The browser reads the file (Excel or CSV) and sends its rows. An import is
 * looked at first (`dryRun`): what would be added, replaced or skipped, and
 * which rows are wrong. Nothing is written until it is sent again without
 * `dryRun`, and then in one transaction: all of it, or none.
 *
 * A checklist that already exists is skipped, unless the import says to
 * replace its items. Runs already started keep their own copy of the items.
 */

export const IMPORT_MAX_ROWS = 5_000;
export const COLUMNS = ['Checklist', 'App', 'Description', 'Item', 'How to test it', 'Module'] as const;

/** One row as the browser read it. Every cell is text; `row` is its line in the file, for the messages. */
const cell = (max: number) => z.string().max(max * 2).optional().default('');
export const importSchema = z
  .object({
    rows: z
      .array(
        z
          .object({
            row: z.number().int().min(1).max(1_000_000),
            checklist: cell(TEST_LIMITS.name.max),
            app: cell(40),
            description: cell(TEST_LIMITS.description),
            item: cell(TEST_LIMITS.title.max),
            detail: cell(TEST_LIMITS.detail),
            module: cell(80),
          })
          .strict(),
      )
      .min(1, 'The file has no rows below its header')
      .max(IMPORT_MAX_ROWS, `Import at most ${IMPORT_MAX_ROWS.toLocaleString('en-IN')} rows at a time`),
    /** What to do with a checklist that already exists: leave it, or replace its items. */
    existing: z.enum(['skip', 'replace']).default('skip'),
    /** Only say what would happen. */
    dryRun: z.boolean().default(true),
  })
  .strict();
export type ImportInput = z.infer<typeof importSchema>;

const clean = (v: string) => v.replace(/[\u0000-\u0008\u000b\u000c\u000e-\u001f\u007f]/g, '').trim();
const oneLine = (v: string) => clean(v).replace(/\s*[\r\n]+\s*/g, ' ');
const block = (v: string) => clean(v.replace(/\r\n?/g, '\n')).replace(/\n{3,}/g, '\n\n');
const key = (name: string) => name.toLowerCase();

function appFrom(value: string): BugApp | null | undefined {
  const v = value.trim().toLowerCase();
  if (!v || v === 'any' || v === 'any app') return null;
  const hit = BUG_APPS.find((a) => a.key.toLowerCase() === v || a.label.toLowerCase() === v || a.label.toLowerCase().replace(/ app$/, '') === v);
  return hit ? (hit.key as BugApp) : undefined;
}

interface Planned {
  name: string;
  description: string | null;
  app: BugApp | null;
  items: { title: string; detail: string | null; moduleId: string | null }[];
  /** The checklist with this name, if there is one. */
  existingId: string | null;
  existingItems: number;
  archived: boolean;
  action: 'create' | 'replace' | 'skip';
}

export interface ImportOutcome {
  dryRun: boolean;
  checklists: { name: string; action: 'create' | 'replace' | 'skip'; items: number; existingItems: number; archived: boolean; app: BugApp | null }[];
  errors: { row: number; message: string }[];
  warnings: { row: number; message: string }[];
  totals: { created: number; replaced: number; skipped: number; items: number };
}

export async function importChecklists(input: ImportInput, actor: BugActor): Promise<ImportOutcome> {
  if (actor.role !== Role.QA && actor.role !== Role.SUPER_ADMIN) throw new ForbiddenError('Only QA or the super admin can import checklists.');
  const errors: { row: number; message: string }[] = [];
  const warnings: { row: number; message: string }[] = [];

  const modules = await db.bugModule.findMany({ select: { id: true, name: true, archivedAt: true } });
  const moduleByName = new Map(modules.map((m) => [m.name.toLowerCase(), m]));
  const existing = await db.bugChecklist.findMany({ select: { id: true, name: true, archivedAt: true, _count: { select: { items: true } } } });
  const existingByName = new Map(existing.map((c) => [key(c.name), c]));

  const groups = new Map<string, Planned>();
  for (const r of input.rows) {
    const name = oneLine(r.checklist);
    const title = oneLine(r.item);
    // A blank line in the sheet is not an error, just nothing.
    if (!name && !title && !clean(r.detail) && !clean(r.description)) continue;
    if (name.length < TEST_LIMITS.name.min) {
      errors.push({ row: r.row, message: name ? `The checklist name must be at least ${TEST_LIMITS.name.min} characters.` : 'Say which checklist this item is on (the Checklist column).' });
      continue;
    }
    if (name.length > TEST_LIMITS.name.max) {
      errors.push({ row: r.row, message: `The checklist name must be at most ${TEST_LIMITS.name.max} characters.` });
      continue;
    }
    let g = groups.get(key(name));
    if (!g) {
      const found = existingByName.get(key(name)) ?? null;
      g = { name: found?.name ?? name, description: null, app: null, items: [], existingId: found?.id ?? null, existingItems: found?._count.items ?? 0, archived: !!found?.archivedAt, action: found ? (input.existing === 'replace' ? 'replace' : 'skip') : 'create' };
      groups.set(key(name), g);
    }

    const description = block(r.description);
    if (description) {
      if (description.length > TEST_LIMITS.description) errors.push({ row: r.row, message: `The description must be at most ${TEST_LIMITS.description.toLocaleString('en-IN')} characters.` });
      else if (!g.description) g.description = description;
    }
    if (clean(r.app)) {
      const app = appFrom(r.app);
      if (app === undefined) errors.push({ row: r.row, message: `“${oneLine(r.app)}” is not one of the apps: ${BUG_APPS.map((a) => a.label).join(', ')}, or leave it blank for any.` });
      else if (app && !g.app) g.app = app;
      else if (app && g.app && app !== g.app) warnings.push({ row: r.row, message: `${g.name} is already for the ${BUG_APPS.find((a) => a.key === g!.app)?.label}; this row’s app is ignored.` });
    }

    // A row that only describes its checklist (no item) is fine.
    if (!title) {
      if (clean(r.detail)) errors.push({ row: r.row, message: 'This row has steps but no item. Put what to test in the Item column.' });
      continue;
    }
    if (title.length < TEST_LIMITS.title.min || title.length > TEST_LIMITS.title.max) {
      errors.push({ row: r.row, message: `The item must be ${TEST_LIMITS.title.min} to ${TEST_LIMITS.title.max} characters.` });
      continue;
    }
    const detail = block(r.detail);
    if (detail.length > TEST_LIMITS.detail) {
      errors.push({ row: r.row, message: `How to test it must be at most ${TEST_LIMITS.detail.toLocaleString('en-IN')} characters.` });
      continue;
    }
    let moduleId: string | null = null;
    const moduleName = oneLine(r.module);
    if (moduleName) {
      const m = moduleByName.get(moduleName.toLowerCase());
      if (!m) warnings.push({ row: r.row, message: `There is no module called “${moduleName}”. The item is added without one.` });
      else if (m.archivedAt) warnings.push({ row: r.row, message: `The module “${m.name}” is archived. The item is added without one.` });
      else moduleId = m.id;
    }
    g.items.push({ title, detail: detail || null, moduleId });
  }

  for (const g of groups.values()) {
    if (g.items.length > TEST_LIMITS.items) errors.push({ row: 0, message: `${g.name} has ${g.items.length} items; a checklist can have at most ${TEST_LIMITS.items}. Split it into two.` });
    if (!g.items.length && g.action !== 'skip') warnings.push({ row: 0, message: `${g.name} has no items in the file, so it is left out.` });
  }
  const planned = [...groups.values()].filter((g) => g.items.length || g.action === 'skip');
  const outcome = (dryRun: boolean): ImportOutcome => ({
    dryRun,
    checklists: planned.map((g) => ({ name: g.name, action: g.action, items: g.items.length, existingItems: g.existingItems, archived: g.archived, app: g.app })),
    errors,
    warnings,
    totals: {
      created: planned.filter((g) => g.action === 'create').length,
      replaced: planned.filter((g) => g.action === 'replace').length,
      skipped: planned.filter((g) => g.action === 'skip').length,
      items: planned.filter((g) => g.action !== 'skip').reduce((n, g) => n + g.items.length, 0),
    },
  });

  // A file with mistakes is not half-imported: fix the rows and send it again.
  if (input.dryRun || errors.length) return outcome(true);

  await db.$transaction(
    async (tx: Prisma.TransactionClient) => {
      for (const g of planned) {
        if (g.action === 'skip') continue;
        let id = g.existingId;
        if (g.action === 'create') {
          id = (await tx.bugChecklist.create({ data: { name: g.name, description: g.description, app: g.app, createdById: actor.id } })).id;
        } else {
          await tx.bugChecklist.update({ where: { id: id! }, data: { ...(g.description ? { description: g.description } : {}), ...(g.app ? { app: g.app } : {}) } });
          await tx.bugChecklistItem.deleteMany({ where: { checklistId: id! } });
        }
        await tx.bugChecklistItem.createMany({ data: g.items.map((i, sortOrder) => ({ checklistId: id!, title: i.title, detail: i.detail, moduleId: i.moduleId, sortOrder })) });
      }
    },
    { timeout: 60_000 },
  );
  return outcome(false);
}

// ── Export ──────────────────────────────────────────────────────────────────

const HOW_TO: Cell[][] = [
  ['One row for each thing to test. Rows with the same Checklist name make one checklist, in the order they come.'],
  [''],
  ['Checklist', 'Required. The checklist’s name, 2 to 80 characters. Repeat it on every row of that checklist.'],
  ['App', `Optional: ${BUG_APPS.map((a) => a.label).join(', ')}. Blank means any app. Read from the first row that has one.`],
  ['Description', 'Optional: what the checklist is for. Read from the first row that has one.'],
  ['Item', 'Required. What to test, in one line (2 to 200 characters).'],
  ['How to test it', 'Optional: the steps and what should happen. Several lines are fine.'],
  ['Module', 'Optional: one of the bug tracker’s modules, by its name. Bugs reported from the item are filed there.'],
  [''],
  ['A checklist that already exists is left as it is, unless you choose to replace its items when importing. Runs already started keep the items they began with.'],
];

const appLabel = (app: BugApp | null) => (app ? (BUG_APPS.find((a) => a.key === app)?.label ?? app) : '');

function workbook(rows: Cell[][]): Buffer {
  const sheets: Sheet[] = [
    {
      name: 'Test cases',
      columns: [
        { header: COLUMNS[0], width: 34 },
        { header: COLUMNS[1], width: 13 },
        { header: COLUMNS[2], width: 40 },
        { header: COLUMNS[3], width: 60 },
        { header: COLUMNS[4], width: 60 },
        { header: COLUMNS[5], width: 24 },
      ],
      rows,
    },
    { name: 'How to fill it in', columns: [{ header: 'Column', width: 18 }, { header: 'What goes in it', width: 110 }], rows: HOW_TO },
  ];
  return writeXlsx(sheets);
}

/** Checklists as a sheet: all of them, or the ones named. The description goes on each checklist's first row. */
export async function exportChecklists(f: { ids?: string[]; includeArchived?: boolean }, now = new Date()) {
  const lists = await db.bugChecklist.findMany({
    where: { ...(f.ids?.length ? { id: { in: f.ids } } : {}), ...(f.includeArchived || f.ids?.length ? {} : { archivedAt: null }) },
    orderBy: { name: 'asc' },
    include: { items: { orderBy: { sortOrder: 'asc' } } },
    take: 500,
  });
  const modules = new Map((await db.bugModule.findMany({ select: { id: true, name: true } })).map((m) => [m.id, m.name]));
  const rows: Cell[][] = lists.flatMap((c) =>
    (c.items.length ? c.items : [null]).map((i, n) => [c.name, n === 0 ? appLabel(c.app) : '', n === 0 ? (c.description ?? '') : '', i?.title ?? '', i?.detail ?? '', i?.moduleId ? (modules.get(i.moduleId) ?? '') : '']),
  );
  const day = new Date(now.getTime() + 330 * 60_000).toISOString().slice(0, 10);
  const one = lists.length === 1 ? `-${lists[0].name.toLowerCase().replace(/[^a-z0-9]+/g, '-').replace(/^-|-$/g, '').slice(0, 40)}` : '';
  return { bytes: workbook(rows), filename: `hostelcare-test-cases${one}-${day}.xlsx`, checklists: lists.length, items: rows.length };
}

/** An example to fill in. */
export function templateWorkbook() {
  const rows: Cell[][] = [
    ['Smoke test', 'Web app', 'The ten-minute pass before every web release.', 'Sign in with an emailed code', '1. Open the sign-in page\n2. Enter email and password\n3. Enter the code from the email\n\nThe dashboard opens.', 'Sign-in and account'],
    ['Smoke test', '', '', 'Record a part payment on an invoice', '1. Open an invoice of ₹6,000\n2. Record ₹2,000\n\nThe balance shows ₹4,000.', 'Billing and payments'],
    ['Smoke test', '', '', 'Mess menu shows today', '', 'Mess'],
    ['Month-end billing', '', 'Run on the last working day of the month.', 'Raise the month’s invoices', '', 'Billing and payments'],
    ['Month-end billing', '', '', 'The late fee is charged once', '', 'Billing and payments'],
  ];
  return { bytes: workbook(rows), filename: 'hostelcare-test-cases-template.xlsx' };
}
