import { ReportFrequency, Role } from '@prisma/client';
import { db, prismaUnscoped } from '../../lib/prisma';
import { getTenantIdOrThrow, runWithTenantContext } from '../../lib/tenantContext';
import { BadRequestError, ForbiddenError, NotFoundError } from '../../lib/errors';
import { effectivePermissions, type Permission } from '../../lib/permissions';
import { isDateOnly, writeXlsx, type Cell, type Sheet } from '../../lib/xlsx';
import { sendEmail } from '../../lib/email';
import { tenantHasFeature } from '../../middleware/requireFeature';
import { REPORT_GROUPS, allReports, groupOf, mayRun, reportByKey, type ReportDef } from './reports.registry';
import './reports.employee';

/**
 * Excel reports and their email timetable (Sprint 0, TEPL). The catalogue is
 * reports.registry; this file builds a workbook, keeps the schedules, and
 * sends what is due on the hourly tick.
 */

const IST_OFFSET_MS = 330 * 60_000;

/** The days a ranged report opens on, on screen. */
function defaultRange(def: ReportDef, now: Date) {
  if (!def.ranged) return null;
  const to = istDay(now);
  const shift = (days: number) => new Date(Date.parse(`${to}T00:00:00.000Z`) + days * 86_400_000).toISOString().slice(0, 10);
  const d = def.defaultDays ?? 30;
  if (d === 'month') return { from: `${to.slice(0, 7)}-01`, to };
  // A week here starts on Monday.
  if (d === 'week') return { from: shift(-((new Date(`${to}T00:00:00.000Z`).getUTCDay() + 6) % 7)), to };
  return { from: shift(-(d - 1)), to };
}

/** The reports this person may run: their permission, and the report's module switched on. Grouped, in the page's order. */
export async function catalogue(perms: Permission[], now = new Date()) {
  const tenantId = getTenantIdOrThrow();
  const out: { key: string; title: string; description: string; group: string; ranged: boolean; defaultRange: { from: string; to: string } | null }[] = [];
  for (const r of allReports()) {
    if (!mayRun(r, perms)) continue;
    if (r.feature && !(await tenantHasFeature(tenantId, r.feature))) continue;
    out.push({ key: r.key, title: r.title, description: r.description, group: groupOf(r.key), ranged: !!r.ranged, defaultRange: defaultRange(r, now) });
  }
  const rank = (group: string) => {
    const i = REPORT_GROUPS.findIndex((g) => g.name === group);
    return i === -1 ? REPORT_GROUPS.length : i;
  };
  return out.sort((a, b) => rank(a.group) - rank(b.group));
}

function istDay(d: Date) {
  return new Date(d.getTime() + IST_OFFSET_MS).toISOString().slice(0, 10);
}

interface RunOptions {
  range?: { from: string; to: string };
  scheduled?: boolean;
}

/** Everything a report's download and its screen share: who may run it, the checks, the build. */
async function runReport(key: string, perms: Permission[] | null, propertyId: string | undefined, now: Date, { range, scheduled }: RunOptions) {
  const def = reportByKey(key);
  if (!def) throw new NotFoundError('No such report');
  if (range) {
    if (!def.ranged) throw new BadRequestError('This report does not take a date range');
    const days = (Date.parse(`${range.to}T00:00:00.000Z`) - Date.parse(`${range.from}T00:00:00.000Z`)) / 86_400_000;
    if (!(days >= 0) || days > 91) throw new BadRequestError('Up to three months at a time, the start on or before the end');
  }
  if (perms && !mayRun(def, perms)) throw new ForbiddenError(`Requires permission: ${def.permission}`);
  if (def.feature && !(await tenantHasFeature(getTenantIdOrThrow(), def.feature))) throw new NotFoundError('No such report');
  let propertyName: string | null = null;
  if (propertyId) {
    const p = await db.property.findFirst({ where: { id: propertyId }, select: { name: true } });
    if (!p) throw new NotFoundError('Property not found');
    propertyName = p.name;
  }
  const sheets = await def.build({ propertyId, now, range, scheduled });
  return { def, sheets, propertyName };
}

/** Without the columns kept only for working out headline numbers. */
const visible = (sheets: Sheet[]): Sheet[] =>
  sheets.map((s) => {
    if (!s.columns.some((c) => c.hidden)) return s;
    const keep = s.columns.flatMap((c, i) => (c.hidden ? [] : [i]));
    return { ...s, columns: keep.map((i) => s.columns[i]), rows: s.rows.map((r) => keep.map((i) => r[i])) };
  });

/** Build one report as an .xlsx, inside the current tenant context. */
export async function buildReport(key: string, perms: Permission[] | null, propertyId: string | undefined, now = new Date(), range?: { from: string; to: string }, scheduled = false) {
  const { def, sheets, propertyName } = await runReport(key, perms, propertyId, now, { range, scheduled });
  // A ranged download names its days instead of "(30 days)".
  const title = range ? `${def.title.replace(/\s*\(.*\)$/, '')} ${range.from} to ${range.to}` : def.title;
  const filename = `${title}${propertyName ? ` - ${propertyName}` : ''}${range ? '' : ` ${istDay(now)}`}.xlsx`.replace(/[\\/:*?"<>|]/g, ' ');
  return { filename, title: def.title, propertyName, bytes: writeXlsx(visible(sheets)) };
}

/** Rows sent to the screen per sheet; the Excel file has them all. */
const VIEW_ROWS = 5000;

/** A date as text that reads and sorts as itself, India time: "2026-11-04" or "2026-11-04 14:30". */
function viewCell(c: Cell): string | number | null {
  if (c === null || c === undefined || c === '') return null;
  if (c instanceof Date) {
    if (Number.isNaN(c.getTime())) return null;
    if (isDateOnly(c)) return c.toISOString().slice(0, 10);
    return new Date(c.getTime() + IST_OFFSET_MS).toISOString().slice(0, 16).replace('T', ' ');
  }
  return c;
}

/** The same report for reading on screen (TEPL, 4 Nov 2026): the same checks and rows as the Excel file. */
export async function viewReport(key: string, perms: Permission[], propertyId: string | undefined, now = new Date(), range?: { from: string; to: string }) {
  const { def, sheets, propertyName } = await runReport(key, perms, propertyId, now, { range });
  return {
    key: def.key,
    title: def.title,
    description: def.description,
    propertyName,
    range: range ?? null,
    highlights: def.highlights?.(sheets) ?? [],
    sheets: visible(sheets).map((s) => ({
      name: s.name,
      columns: s.columns.map((c) => c.header),
      total: s.rows.length,
      rows: s.rows.slice(0, VIEW_ROWS).map((r) => r.map(viewCell)),
    })),
  };
}

export interface ReportPageQuery {
  /** Which sheet the page is of; the others come back without rows. */
  sheet: number;
  page: number;
  limit: number;
  /** Matched against each cell as sent and as the apps show it. */
  q?: string;
  /** A column of the sheet as sent (hidden columns left out), and its direction. */
  sort?: { col: number; dir: 1 | -1 };
}

type ViewCell = string | number | null;

const VIEW_DATE = /^\d{4}-\d{2}-\d{2}$/;
const VIEW_DATE_TIME = /^\d{4}-\d{2}-\d{2} \d{2}:\d{2}$/;

/** A cell as the apps show it (`showCell` in the web's ReportViewer), so a search finds what is on screen. */
export function shownCell(c: ViewCell): string {
  if (c === null) return '—';
  if (typeof c === 'number') return c.toLocaleString('en-IN');
  if (VIEW_DATE.test(c)) return new Date(`${c}T00:00:00.000Z`).toLocaleDateString('en-IN', { day: '2-digit', month: 'short', year: 'numeric', timeZone: 'UTC' });
  if (VIEW_DATE_TIME.test(c)) return new Date(`${c.replace(' ', 'T')}:00.000Z`).toLocaleString('en-IN', { day: '2-digit', month: 'short', year: 'numeric', hour: 'numeric', minute: '2-digit', timeZone: 'UTC' });
  return c;
}

/** "1 h 20 min" as minutes, so a duration column sorts by length. */
function minutesOf(c: string): number | null {
  const m = /^(?:(\d+) h)?\s*(?:(\d+) min)?$/.exec(c);
  return m && (m[1] || m[2]) ? Number(m[1] ?? 0) * 60 + Number(m[2] ?? 0) : null;
}

const collator = new Intl.Collator('en', { numeric: true, sensitivity: 'base' });

/** The web viewer's order: numbers by value, durations by length, the rest as words with numbers in them. */
function compareCells(a: string | number, b: string | number): number {
  if (typeof a === 'number' && typeof b === 'number') return a - b;
  const [x, y] = [String(a), String(b)];
  const [mx, my] = [minutesOf(x), minutesOf(y)];
  if (mx !== null && my !== null) return mx - my;
  return collator.compare(x, y);
}

/**
 * One page of a sheet, searched and sorted as the apps did it on the whole
 * sheet: a row matches when any cell, as sent or as shown, holds the words;
 * blank cells stay at the bottom whichever way it is sorted, and rows that
 * tie keep the report's order.
 */
export function pageOfRows(rows: ViewCell[][], q: string | undefined, sort: ReportPageQuery['sort'], page: number, limit: number) {
  const term = q?.trim().toLowerCase();
  let found = term ? rows.filter((r) => r.some((c) => c !== null && (String(c).toLowerCase().includes(term) || shownCell(c).toLowerCase().includes(term)))) : rows;
  if (sort) {
    const { col, dir } = sort;
    found = [...found].sort((a, b) => {
      const [x, y] = [a[col] ?? null, b[col] ?? null];
      if (x === null || y === null) return Number(x === null) - Number(y === null);
      return compareCells(x, y) * dir;
    });
  }
  const start = (page - 1) * limit;
  return { matched: found.length, rows: found.slice(start, start + limit), hasMore: start + limit < found.length };
}

/** A column of numbers lines up on the right: something in it, and nothing but numbers. */
function numericColumns(columns: number, rows: ViewCell[][]) {
  return Array.from({ length: columns }, (_, i) => rows.some((r) => r[i] !== null && r[i] !== undefined) && rows.every((r) => r[i] === null || r[i] === undefined || typeof r[i] === 'number'));
}

/**
 * The report on screen a page at a time (Sprint L4): the same checks, build and
 * headline numbers as `viewReport`, but only one page of one sheet is sent,
 * with how many rows match and which columns hold numbers. The other sheets
 * come with their names, columns and totals, and no rows. The Excel file is
 * still built whole.
 */
export async function viewReportPage(key: string, perms: Permission[], propertyId: string | undefined, now: Date, range: { from: string; to: string } | undefined, query: ReportPageQuery) {
  const { def, sheets, propertyName } = await runReport(key, perms, propertyId, now, { range });
  const shown = visible(sheets);
  const chosen = Math.min(Math.max(0, query.sheet), Math.max(0, shown.length - 1));
  return {
    key: def.key,
    title: def.title,
    description: def.description,
    propertyName,
    range: range ?? null,
    highlights: def.highlights?.(sheets) ?? [],
    sheet: chosen,
    sheets: shown.map((s, i) => {
      const rows = s.rows.map((r) => r.map(viewCell));
      const base = { name: s.name, columns: s.columns.map((c) => c.header), total: rows.length, numeric: numericColumns(s.columns.length, rows) };
      if (i !== chosen) return { ...base, rows: [] as ViewCell[][], matched: rows.length, page: 1, limit: query.limit, hasMore: false };
      const sort = query.sort && query.sort.col < s.columns.length ? query.sort : undefined;
      const p = pageOfRows(rows, query.q, sort, query.page, query.limit);
      return { ...base, rows: p.rows, matched: p.matched, page: query.page, limit: query.limit, hasMore: p.hasMore };
    }),
  };
}

// ── Schedules ───────────────────────────────────────────────────────────────

export interface ScheduleInput {
  reportKey: string;
  frequency: ReportFrequency;
  hour: number;
  weekday?: number | null;
  monthDay?: number | null;
  recipients: string[];
  propertyId?: string | null;
  isActive?: boolean;
}

function checkShape(input: { frequency: ReportFrequency; weekday?: number | null; monthDay?: number | null }) {
  if (input.frequency === ReportFrequency.WEEKLY && !input.weekday) throw new BadRequestError('Choose the day of the week');
  if (input.frequency === ReportFrequency.MONTHLY && !input.monthDay) throw new BadRequestError('Choose the day of the month');
}

export function listSchedules() {
  return db.reportSchedule.findMany({ orderBy: { createdAt: 'asc' }, take: 100 });
}

export async function createSchedule(input: ScheduleInput, perms: Permission[], actorId: string) {
  const def = reportByKey(input.reportKey);
  if (!def) throw new BadRequestError('No such report');
  if (!mayRun(def, perms)) throw new ForbiddenError(`Requires permission: ${def.permission}`);
  checkShape(input);
  if (input.propertyId && !(await db.property.findFirst({ where: { id: input.propertyId }, select: { id: true } }))) throw new NotFoundError('Property not found');
  if ((await db.reportSchedule.count()) >= 50) throw new BadRequestError('Up to 50 scheduled reports');
  return db.reportSchedule.create({
    data: {
      tenantId: getTenantIdOrThrow(),
      reportKey: input.reportKey,
      frequency: input.frequency,
      hour: input.hour,
      weekday: input.frequency === ReportFrequency.WEEKLY ? input.weekday : null,
      monthDay: input.frequency === ReportFrequency.MONTHLY ? input.monthDay : null,
      recipients: input.recipients,
      propertyId: input.propertyId ?? null,
      isActive: input.isActive ?? true,
      createdById: actorId,
    },
  });
}

export async function updateSchedule(id: string, input: Partial<ScheduleInput>, perms: Permission[]) {
  const existing = await db.reportSchedule.findFirst({ where: { id } });
  if (!existing) throw new NotFoundError('Schedule not found');
  const key = input.reportKey ?? existing.reportKey;
  const def = reportByKey(key);
  if (!def) throw new BadRequestError('No such report');
  if (!mayRun(def, perms)) throw new ForbiddenError(`Requires permission: ${def.permission}`);
  const merged = { frequency: input.frequency ?? existing.frequency, weekday: input.weekday !== undefined ? input.weekday : existing.weekday, monthDay: input.monthDay !== undefined ? input.monthDay : existing.monthDay };
  checkShape(merged);
  if (input.propertyId && !(await db.property.findFirst({ where: { id: input.propertyId }, select: { id: true } }))) throw new NotFoundError('Property not found');
  return db.reportSchedule.update({
    where: { id },
    data: {
      reportKey: key,
      frequency: merged.frequency,
      weekday: merged.frequency === ReportFrequency.WEEKLY ? merged.weekday : null,
      monthDay: merged.frequency === ReportFrequency.MONTHLY ? merged.monthDay : null,
      ...(input.hour !== undefined ? { hour: input.hour } : {}),
      ...(input.recipients ? { recipients: input.recipients } : {}),
      ...(input.propertyId !== undefined ? { propertyId: input.propertyId } : {}),
      ...(input.isActive !== undefined ? { isActive: input.isActive } : {}),
    },
  });
}

export async function deleteSchedule(id: string) {
  const existing = await db.reportSchedule.findFirst({ where: { id } });
  if (!existing) throw new NotFoundError('Schedule not found');
  await db.reportSchedule.delete({ where: { id } });
  return { ok: true };
}

/**
 * The period a schedule is due for at `now`, or null when it is not due:
 * the India-time day for daily and weekly, the month for monthly. Sending
 * waits for the schedule's hour; a server down at that hour sends later the
 * same day, and a day missed entirely is skipped rather than sent late.
 */
export function dueKey(s: { frequency: ReportFrequency; hour: number; weekday: number | null; monthDay: number | null }, now: Date): string | null {
  const ist = new Date(now.getTime() + IST_OFFSET_MS);
  if (ist.getUTCHours() < s.hour) return null;
  const day = ist.toISOString().slice(0, 10);
  if (s.frequency === ReportFrequency.DAILY) return day;
  if (s.frequency === ReportFrequency.WEEKLY) {
    const weekday = ((ist.getUTCDay() + 6) % 7) + 1; // 1 = Monday … 7 = Sunday
    return weekday === s.weekday ? day : null;
  }
  return ist.getUTCDate() === s.monthDay ? day.slice(0, 7) : null;
}

/** Build and email one schedule now. Each recipient gets their own message (the mail provider's limit). */
const PAUSED = 'Paused: the person who set this up can no longer run this report';

/**
 * Whoever set a schedule up must still be able to run the report when it is
 * sent (Sprint 7 hardening): a login removed, or one whose role lost the
 * permission, stops its schedules rather than keep mailing the data out.
 */
async function creatorPerms(s: { tenantId: string; createdById: string }) {
  const u = await prismaUnscoped.user.findFirst({ where: { id: s.createdById, tenantId: s.tenantId }, select: { role: true, customRole: { select: { permissions: true } } } });
  return u ? effectivePermissions(u.role, u.customRole?.permissions) : null;
}

async function sendSchedule(s: { id: string; tenantId: string; reportKey: string; propertyId: string | null; recipients: string[]; createdById: string }, now: Date): Promise<{ sent: number; failed: string[]; paused?: boolean }> {
  const perms = await creatorPerms(s);
  const def = reportByKey(s.reportKey);
  if (!perms || !def || !mayRun(def, perms)) {
    await prismaUnscoped.reportSchedule.update({ where: { id: s.id }, data: { isActive: false, lastStatus: PAUSED } });
    return { sent: 0, failed: [], paused: true };
  }
  return runWithTenantContext({ userId: s.createdById, role: Role.HOSTEL_ADMIN, tenantId: s.tenantId }, async () => {
    const report = await buildReport(s.reportKey, perms, s.propertyId ?? undefined, now, undefined, true);
    const tenant = await prismaUnscoped.tenant.findUnique({ where: { id: s.tenantId }, select: { name: true } });
    const subject = `${report.title}${report.propertyName ? ` · ${report.propertyName}` : ''} — ${istDay(now)}`;
    const text = `Your scheduled report from ${tenant?.name ?? 'HostelCare'} is attached: ${report.filename}.\n\nTo change or stop it, open Reports in HostelCare.`;
    let sent = 0;
    const failed: string[] = [];
    for (const to of s.recipients.slice(0, 10)) {
      const r = await sendEmail(to, subject, text, undefined, [{ filename: report.filename, content: report.bytes }]).catch(() => null);
      if (r && (r.sent || r.mode === 'log')) sent++;
      else failed.push(to);
    }
    return { sent, failed };
  });
}

/** Staff "send it now" — a test of the schedule, outside its timetable. */
export async function sendScheduleNow(id: string) {
  const s = await db.reportSchedule.findFirst({ where: { id } });
  if (!s) throw new NotFoundError('Schedule not found');
  const result = await sendSchedule(s, new Date());
  if (result.paused) throw new ForbiddenError(PAUSED);
  await db.reportSchedule.update({ where: { id }, data: { lastRunAt: new Date(), lastStatus: result.failed.length ? `Failed for ${result.failed.join(', ')}` : `Sent to ${result.sent}` } });
  return result;
}

/** The hourly tick: every active schedule that is due and not yet sent for its period. */
export async function runDueReportSchedules(now = new Date()): Promise<void> {
  const schedules = await prismaUnscoped.reportSchedule.findMany({ where: { isActive: true } });
  for (const s of schedules) {
    const key = dueKey(s, now);
    if (!key || s.lastRunKey === key) continue;
    if (!(await tenantHasFeature(s.tenantId, 'reports'))) continue;
    // Claim the period first, so two servers (or a slow tick) never send it twice.
    const claimed = await prismaUnscoped.reportSchedule.updateMany({
      where: { id: s.id, OR: [{ lastRunKey: null }, { lastRunKey: { not: key } }] },
      data: { lastRunKey: key, lastRunAt: now },
    });
    if (claimed.count === 0) continue;
    try {
      const result = await sendSchedule(s, now);
      if (result.paused) continue;
      await prismaUnscoped.reportSchedule.update({ where: { id: s.id }, data: { lastStatus: result.failed.length ? `Failed for ${result.failed.join(', ')}` : `Sent to ${result.sent}` } });
    } catch (err) {
      // eslint-disable-next-line no-console
      console.error(`[reports] schedule ${s.id} failed:`, err);
      await prismaUnscoped.reportSchedule.update({ where: { id: s.id }, data: { lastStatus: `Failed: ${String(err).slice(0, 200)}` } }).catch(() => {});
    }
  }
}
