import crypto from 'node:crypto';
import { Router, type IRouter } from 'express';
import multer from 'multer';
import * as XLSX from 'xlsx';
import { pool } from '../data/db';
import { users, addAuditEvent, type LayerRow, type ColumnType } from '../data/store';
import { requireAuth, requireAdmin } from '../middlewares/auth';

type TransformStep = {
  id: string; sheetId: string; order: number; name: string; operation: string;
  config: Record<string, unknown>; enabled: boolean; createdAt: string; updatedAt: string;
};

const upload = multer({ storage: multer.memoryStorage(), limits: { fileSize: 50 * 1024 * 1024 } });
const router: IRouter = Router();
router.use('/analytics/workbooks', requireAuth);
router.use('/analytics/sheets', requireAuth);

function detectType(values: unknown[]): ColumnType {
  const present = values.filter((value) => value !== null && value !== undefined && value !== '');
  if (!present.length) return 'text';
  if (present.every((value) => typeof value === 'number' || (String(value).trim() !== '' && Number.isFinite(Number(value))))) return 'number';
  if (present.every((value) => value instanceof Date || /^\d{4}-\d{1,2}-\d{1,2}/.test(String(value)))) return 'date';
  return 'text';
}

function describeRows(rows: LayerRow[]) {
  const columns = [...new Set(rows.flatMap((row) => Object.keys(row)))];
  const columnTypes = Object.fromEntries(columns.map((column) => [column, detectType(rows.slice(0, 500).map((row) => row[column]))])) as Record<string, ColumnType>;
  return { columns, columnTypes };
}

function stepFromRow(row: Record<string, unknown>): TransformStep {
  return { id: String(row.id), sheetId: String(row.sheet_id), order: Number(row.step_order), name: String(row.name), operation: String(row.operation), config: (row.config ?? {}) as Record<string, unknown>, enabled: Boolean(row.enabled), createdAt: String(row.created_at), updatedAt: String(row.updated_at) };
}

function convertValue(value: unknown, type: ColumnType): string | number | null {
  if (value === null || value === undefined || value === '') return null;
  if (type === 'number') { const number = Number(value); return Number.isFinite(number) ? number : null; }
  if (type === 'date') { const date = new Date(String(value)); return Number.isNaN(date.valueOf()) ? null : date.toISOString().slice(0, 10); }
  return String(value);
}

function applyStep(rows: LayerRow[], step: TransformStep): LayerRow[] {
  if (!step.enabled) return rows;
  const config = step.config;
  switch (step.operation) {
    case 'removeColumns': {
      const removed = new Set(Array.isArray(config.columns) ? config.columns.map(String) : []);
      return rows.map((row) => Object.fromEntries(Object.entries(row).filter(([column]) => !removed.has(column))) as LayerRow);
    }
    case 'selectColumns': {
      const selected = new Set(Array.isArray(config.columns) ? config.columns.map(String) : []);
      return rows.map((row) => Object.fromEntries(Object.entries(row).filter(([column]) => selected.has(column))) as LayerRow);
    }
    case 'renameColumn': {
      const from = String(config.from ?? ''); const to = String(config.to ?? '').trim();
      if (!from || !to) return rows;
      return rows.map((row) => { const next = { ...row }; if (from in next) { next[to] = next[from]; delete next[from]; } return next; });
    }
    case 'changeType': {
      const column = String(config.column ?? ''); const type = String(config.type ?? 'text') as ColumnType;
      return rows.map((row) => ({ ...row, [column]: convertValue(row[column], type) }));
    }
    case 'replaceValue': {
      const column = String(config.column ?? ''); const find = String(config.find ?? ''); const replacement = config.replacement as string | number | null ?? '';
      return rows.map((row) => String(row[column] ?? '') === find ? { ...row, [column]: replacement } : row);
    }
    case 'trim': {
      const columns = Array.isArray(config.columns) ? config.columns.map(String) : [];
      return rows.map((row) => { const next = { ...row }; for (const column of columns) if (typeof next[column] === 'string') next[column] = next[column].trim(); return next; });
    }
    case 'fillEmpty': {
      const column = String(config.column ?? ''); const value = config.value as string | number | null ?? null;
      return rows.map((row) => row[column] === null || row[column] === undefined || row[column] === '' ? { ...row, [column]: value } : row);
    }
    case 'addColumn': {
      const column = String(config.column ?? '').trim(); const value = config.value as string | number | null ?? null;
      return column ? rows.map((row) => ({ ...row, [column]: value })) : rows;
    }
    case 'filterRows': {
      const column = String(config.column ?? ''); const operator = String(config.operator ?? 'eq'); const expected = String(config.value ?? '');
      return rows.filter((row) => { const actual = String(row[column] ?? ''); if (operator === 'neq') return actual !== expected; if (operator === 'contains') return actual.toLowerCase().includes(expected.toLowerCase()); if (operator === 'not_contains') return !actual.toLowerCase().includes(expected.toLowerCase()); if (operator === 'is_empty') return !actual.trim(); if (operator === 'is_not_empty') return Boolean(actual.trim()); return actual === expected; });
    }
    case 'removeDuplicates': {
      const columns = Array.isArray(config.columns) ? config.columns.map(String) : [];
      const seen = new Set<string>(); return rows.filter((row) => { const key = JSON.stringify(columns.length ? columns.map((column) => row[column]) : row); if (seen.has(key)) return false; seen.add(key); return true; });
    }
    default: return rows;
  }
}

function applySteps(rawRows: LayerRow[], steps: TransformStep[], stopAfterId?: string) {
  let rows = rawRows.map((row) => ({ ...row }));
  const snapshots: Array<{ stepId: string; rowCount: number; columns: string[] }> = [];
  for (const step of steps.sort((a, b) => a.order - b.order)) {
    rows = applyStep(rows, step);
    snapshots.push({ stepId: step.id, rowCount: rows.length, columns: describeRows(rows).columns });
    if (stopAfterId && step.id === stopAfterId) break;
  }
  return { rows, snapshots, ...describeRows(rows) };
}

async function loadSteps(sheetId: string): Promise<TransformStep[]> {
  const result = await pool.query('SELECT * FROM sc_transform_steps WHERE sheet_id=$1 ORDER BY step_order', [sheetId]);
  return result.rows.map(stepFromRow);
}

async function rematerialize(sheetId: string) {
  const result = await pool.query('SELECT raw_rows FROM sc_dataset_sheets WHERE id=$1', [sheetId]);
  if (!result.rows[0]) throw new Error('Worksheet not found');
  const transformed = applySteps(result.rows[0].raw_rows as LayerRow[], await loadSteps(sheetId));
  await pool.query('UPDATE sc_dataset_sheets SET clean_rows=$2, columns=$3, column_types=$4, row_count=$5, updated_at=$6 WHERE id=$1', [sheetId, JSON.stringify(transformed.rows), JSON.stringify(transformed.columns), JSON.stringify(transformed.columnTypes), transformed.rows.length, new Date().toISOString()]);
  return transformed;
}

router.get('/analytics/workbooks', async (_req, res) => {
  const result = await pool.query(`SELECT w.*, COALESCE(json_agg(json_build_object('id',s.id,'name',s.name,'index',s.sheet_index,'columns',s.columns,'columnTypes',s.column_types,'rowCount',s.row_count,'updatedAt',s.updated_at) ORDER BY s.sheet_index) FILTER (WHERE s.id IS NOT NULL), '[]') AS sheets FROM sc_workbooks w LEFT JOIN sc_dataset_sheets s ON s.workbook_id=w.id GROUP BY w.id ORDER BY w.updated_at DESC`);
  return res.json(result.rows.map((row) => ({ id: row.id, name: row.name, fileName: row.file_name, sourceType: row.source_type, createdAt: row.created_at, updatedAt: row.updated_at, sheets: row.sheets })));
});

router.delete('/analytics/workbooks/:id', requireAdmin, async (req, res) => {
  const result = await pool.query('DELETE FROM sc_workbooks WHERE id=$1 RETURNING name', [req.params.id]);
  if (!result.rows[0]) return res.status(404).json({ error: 'Workbook not found' });
  const actor = users.find((user) => user.id === req.session!.userId)?.name ?? 'Admin';
  await addAuditEvent('Deleted analytics workbook', String(result.rows[0].name), actor, undefined, { workbookId: String(req.params.id) });
  return res.json({ ok: true });
});

router.post('/analytics/workbooks/import', requireAdmin, upload.single('file'), async (req, res) => {
  if (!req.file?.buffer) return res.status(400).json({ error: 'Excel file is required' });
  let workbook: XLSX.WorkBook;
  try { workbook = XLSX.read(req.file.buffer, { type: 'buffer', cellDates: true }); }
  catch { return res.status(400).json({ error: 'Unable to read Excel workbook' }); }
  const requestedSheets = req.body?.sheets ? String(req.body.sheets).split(',').map((name) => name.trim()).filter(Boolean) : workbook.SheetNames;
  const selected = workbook.SheetNames.filter((name) => requestedSheets.includes(name));
  if (!selected.length) return res.status(400).json({ error: 'No worksheets selected' });
  const workbookId = crypto.randomUUID(); const now = new Date().toISOString(); const client = await pool.connect();
  try {
    await client.query('BEGIN');
    await client.query('INSERT INTO sc_workbooks (id,name,source_type,file_name,created_at,updated_at) VALUES ($1,$2,$3,$4,$5,$5)', [workbookId, String(req.body?.name ?? req.file.originalname).trim(), 'Excel', req.file.originalname, now]);
    for (const sheetName of selected) {
      const sheetId = crypto.randomUUID();
      const rows = XLSX.utils.sheet_to_json<LayerRow>(workbook.Sheets[sheetName], { defval: null, raw: false });
      const { columns, columnTypes } = describeRows(rows);
      await client.query('INSERT INTO sc_dataset_sheets (id,workbook_id,name,sheet_index,columns,column_types,raw_rows,clean_rows,row_count,updated_at) VALUES ($1,$2,$3,$4,$5,$6,$7,$7,$8,$9)', [sheetId, workbookId, sheetName, workbook.SheetNames.indexOf(sheetName), JSON.stringify(columns), JSON.stringify(columnTypes), JSON.stringify(rows), rows.length, now]);
    }
    await client.query('COMMIT');
  } catch (error) { await client.query('ROLLBACK'); throw error; } finally { client.release(); }
  const actor = users.find((user) => user.id === req.session!.userId)?.name ?? 'Admin';
  await addAuditEvent('Imported analytics workbook', req.file.originalname, actor, `Imported ${selected.length} worksheet(s)`, { workbookId, sheetCount: selected.length });
  return res.status(201).json({ id: workbookId, name: req.body?.name ?? req.file.originalname, sheets: selected });
});

router.get('/analytics/sheets/:id/preview', async (req, res) => {
  const sheet = await pool.query('SELECT * FROM sc_dataset_sheets WHERE id=$1', [req.params.id]);
  if (!sheet.rows[0]) return res.status(404).json({ error: 'Worksheet not found' });
  const steps = await loadSteps(String(req.params.id));
  const transformed = applySteps(sheet.rows[0].raw_rows as LayerRow[], steps, req.query.stepId ? String(req.query.stepId) : undefined);
  return res.json({ sheet: { id: sheet.rows[0].id, name: sheet.rows[0].name }, steps, rows: transformed.rows.slice(0, Math.max(1, Math.min(500, Number(req.query.limit ?? 100)))), rowCount: transformed.rows.length, columns: transformed.columns, columnTypes: transformed.columnTypes, snapshots: transformed.snapshots });
});

router.post('/analytics/sheets/:id/steps', requireAdmin, async (req, res) => {
  const id = crypto.randomUUID(); const now = new Date().toISOString();
  const orderResult = await pool.query('SELECT COALESCE(MAX(step_order),0)+1 AS next_order FROM sc_transform_steps WHERE sheet_id=$1', [req.params.id]);
  const result = await pool.query('INSERT INTO sc_transform_steps (id,sheet_id,step_order,name,operation,config,enabled,created_at,updated_at) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$8) RETURNING *', [id, req.params.id, Number(orderResult.rows[0].next_order), String(req.body?.name ?? req.body?.operation ?? 'Transformation'), String(req.body?.operation ?? ''), JSON.stringify(req.body?.config ?? {}), req.body?.enabled ?? true, now]);
  await rematerialize(String(req.params.id));
  return res.status(201).json(stepFromRow(result.rows[0]));
});

router.patch('/analytics/sheets/:sheetId/steps/:id', requireAdmin, async (req, res) => {
  const current = await pool.query('SELECT * FROM sc_transform_steps WHERE id=$1 AND sheet_id=$2', [req.params.id, req.params.sheetId]);
  if (!current.rows[0]) return res.status(404).json({ error: 'Transformation step not found' });
  const row = current.rows[0];
  const result = await pool.query('UPDATE sc_transform_steps SET step_order=$3,name=$4,operation=$5,config=$6,enabled=$7,updated_at=$8 WHERE id=$1 AND sheet_id=$2 RETURNING *', [req.params.id, req.params.sheetId, Number(req.body?.order ?? row.step_order), String(req.body?.name ?? row.name), String(req.body?.operation ?? row.operation), JSON.stringify(req.body?.config ?? row.config), req.body?.enabled ?? row.enabled, new Date().toISOString()]);
  await rematerialize(String(req.params.sheetId));
  return res.json(stepFromRow(result.rows[0]));
});

router.delete('/analytics/sheets/:sheetId/steps/:id', requireAdmin, async (req, res) => {
  const result = await pool.query('DELETE FROM sc_transform_steps WHERE id=$1 AND sheet_id=$2 RETURNING id', [req.params.id, req.params.sheetId]);
  if (!result.rows[0]) return res.status(404).json({ error: 'Transformation step not found' });
  await rematerialize(String(req.params.sheetId));
  return res.json({ ok: true });
});

export default router;