import crypto from 'node:crypto';
import { Router, type IRouter } from 'express';
import { pool } from '../data/db';
import { layers, users, addAuditEvent, type LayerRow } from '../data/store';
import { requireAuth, requireAdmin } from '../middlewares/auth';

type Aggregation = 'count' | 'countDistinct' | 'sum' | 'avg' | 'min' | 'max';

type ReportVisualInput = {
  pageId: string;
  title: string;
  visualType: 'column' | 'bar' | 'line' | 'area' | 'donut' | 'kpi' | 'table';
  layerId: string;
  categoryColumn?: string;
  valueColumn?: string;
  aggregation: Aggregation;
  seriesColumn?: string;
  color?: string;
  settings?: Record<string, unknown>;
  layout?: { x: number; y: number; w: number; h: number };
  visualOrder?: number;
  enabled?: boolean;
};

function mapPage(row: Record<string, unknown>) {
  return {
    id: row.id,
    name: row.name,
    order: row.page_order,
    enabled: row.enabled,
    createdAt: row.created_at,
    updatedAt: row.updated_at,
  };
}

function mapVisual(row: Record<string, unknown>) {
  return {
    id: row.id,
    pageId: row.page_id,
    title: row.title,
    visualType: row.visual_type,
    layerId: row.layer_id,
    categoryColumn: row.category_column ?? undefined,
    valueColumn: row.value_column ?? undefined,
    aggregation: row.aggregation,
    seriesColumn: row.series_column ?? undefined,
    color: row.color,
    settings: row.settings ?? {},
    layout: row.layout ?? { x: 0, y: 0, w: 6, h: 4 },
    order: row.visual_order,
    enabled: row.enabled,
    createdAt: row.created_at,
    updatedAt: row.updated_at,
  };
}

function aggregate(rows: LayerRow[], column: string | undefined, operation: Aggregation): number {
  if (operation === 'count') return rows.length;
  const values = rows
    .map((row) => column ? row[column] : null)
    .filter((value) => value !== null && value !== undefined && value !== '');
  if (operation === 'countDistinct') return new Set(values.map(String)).size;
  const numbers = values.map(Number).filter(Number.isFinite);
  if (numbers.length === 0) return 0;
  if (operation === 'sum') return numbers.reduce((sum, value) => sum + value, 0);
  if (operation === 'avg') return numbers.reduce((sum, value) => sum + value, 0) / numbers.length;
  if (operation === 'min') return Math.min(...numbers);
  return Math.max(...numbers);
}

const router: IRouter = Router();
router.use('/analytics', requireAuth);

router.get('/analytics/report', async (_req, res) => {
  const [pageResult, visualResult] = await Promise.all([
    pool.query('SELECT * FROM sc_report_pages WHERE enabled = true ORDER BY page_order, name'),
    pool.query('SELECT * FROM sc_report_visuals WHERE enabled = true ORDER BY page_id, visual_order, title'),
  ]);
  res.json({
    pages: pageResult.rows.map(mapPage),
    visuals: visualResult.rows.map(mapVisual),
  });
});

router.post('/analytics/pages', requireAdmin, async (req, res) => {
  const name = String(req.body?.name ?? '').trim();
  if (!name) return res.status(400).json({ error: 'Page name is required' });
  const id = crypto.randomUUID();
  const now = new Date().toISOString();
  const orderResult = await pool.query('SELECT COALESCE(MAX(page_order), 0) + 1 AS next_order FROM sc_report_pages');
  const result = await pool.query(
    `INSERT INTO sc_report_pages (id, name, page_order, enabled, created_at, updated_at)
     VALUES ($1,$2,$3,true,$4,$4) RETURNING *`,
    [id, name, Number(orderResult.rows[0].next_order), now],
  );
  const actor = users.find((user) => user.id === req.session!.userId)?.name ?? 'Admin';
  await addAuditEvent('Created report page', name, actor, undefined, { reportPageId: id });
  return res.status(201).json(mapPage(result.rows[0]));
});

router.patch('/analytics/pages/:id', requireAdmin, async (req, res) => {
  const current = await pool.query('SELECT * FROM sc_report_pages WHERE id = $1', [req.params.id]);
  if (!current.rows[0]) return res.status(404).json({ error: 'Report page not found' });
  const row = current.rows[0];
  const result = await pool.query(
    `UPDATE sc_report_pages SET name=$2, page_order=$3, enabled=$4, updated_at=$5
     WHERE id=$1 RETURNING *`,
    [req.params.id, String(req.body?.name ?? row.name).trim(), Number(req.body?.order ?? row.page_order), req.body?.enabled ?? row.enabled, new Date().toISOString()],
  );
  return res.json(mapPage(result.rows[0]));
});

router.delete('/analytics/pages/:id', requireAdmin, async (req, res) => {
  const result = await pool.query('DELETE FROM sc_report_pages WHERE id=$1 RETURNING name', [req.params.id]);
  if (!result.rows[0]) return res.status(404).json({ error: 'Report page not found' });
  return res.json({ ok: true });
});

router.post('/analytics/visuals', requireAdmin, async (req, res) => {
  const body = req.body as Partial<ReportVisualInput>;
  if (!body.pageId || !body.title || !body.visualType || !body.layerId || !body.aggregation) {
    return res.status(400).json({ error: 'pageId, title, visualType, layerId and aggregation are required' });
  }
  const layer = layers.find((item) => item.id === body.layerId);
  if (!layer) return res.status(400).json({ error: 'Selected layer does not exist' });
  if (body.categoryColumn && !layer.columns.includes(body.categoryColumn)) return res.status(400).json({ error: 'Category column does not exist' });
  if (body.valueColumn && !layer.columns.includes(body.valueColumn)) return res.status(400).json({ error: 'Value column does not exist' });
  const id = crypto.randomUUID();
  const now = new Date().toISOString();
  const orderResult = await pool.query('SELECT COALESCE(MAX(visual_order), 0) + 1 AS next_order FROM sc_report_visuals WHERE page_id=$1', [body.pageId]);
  const result = await pool.query(
    `INSERT INTO sc_report_visuals
      (id,page_id,title,visual_type,layer_id,category_column,value_column,aggregation,series_column,color,settings,layout,visual_order,enabled,created_at,updated_at)
     VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$15) RETURNING *`,
    [id, body.pageId, body.title.trim(), body.visualType, body.layerId, body.categoryColumn || null,
      body.valueColumn || null, body.aggregation, body.seriesColumn || null, body.color ?? '#294b55',
      JSON.stringify(body.settings ?? {}), JSON.stringify(body.layout ?? { x: 0, y: 0, w: 6, h: 4 }),
      body.visualOrder ?? Number(orderResult.rows[0].next_order), body.enabled ?? true, now],
  );
  const actor = users.find((user) => user.id === req.session!.userId)?.name ?? 'Admin';
  await addAuditEvent('Created report visual', body.title, actor, undefined, { reportVisualId: id, reportPageId: body.pageId, layerId: body.layerId });
  return res.status(201).json(mapVisual(result.rows[0]));
});

router.patch('/analytics/visuals/:id', requireAdmin, async (req, res) => {
  const current = await pool.query('SELECT * FROM sc_report_visuals WHERE id=$1', [req.params.id]);
  if (!current.rows[0]) return res.status(404).json({ error: 'Report visual not found' });
  const row = current.rows[0];
  const body = req.body as Partial<ReportVisualInput>;
  const result = await pool.query(
    `UPDATE sc_report_visuals SET page_id=$2,title=$3,visual_type=$4,layer_id=$5,category_column=$6,
      value_column=$7,aggregation=$8,series_column=$9,color=$10,settings=$11,layout=$12,
      visual_order=$13,enabled=$14,updated_at=$15 WHERE id=$1 RETURNING *`,
    [req.params.id, body.pageId ?? row.page_id, body.title?.trim() ?? row.title,
      body.visualType ?? row.visual_type, body.layerId ?? row.layer_id,
      body.categoryColumn === undefined ? row.category_column : body.categoryColumn || null,
      body.valueColumn === undefined ? row.value_column : body.valueColumn || null,
      body.aggregation ?? row.aggregation,
      body.seriesColumn === undefined ? row.series_column : body.seriesColumn || null,
      body.color ?? row.color, JSON.stringify(body.settings ?? row.settings), JSON.stringify(body.layout ?? row.layout),
      body.visualOrder ?? row.visual_order, body.enabled ?? row.enabled, new Date().toISOString()],
  );
  return res.json(mapVisual(result.rows[0]));
});

router.delete('/analytics/visuals/:id', requireAdmin, async (req, res) => {
  const result = await pool.query('DELETE FROM sc_report_visuals WHERE id=$1 RETURNING title', [req.params.id]);
  if (!result.rows[0]) return res.status(404).json({ error: 'Report visual not found' });
  return res.json({ ok: true });
});

router.post('/analytics/query', async (req, res) => {
  const layerId = String(req.body?.layerId ?? '');
  const layer = layers.find((item) => item.id === layerId);
  if (!layer) return res.status(404).json({ error: 'Layer not found' });
  const categoryColumn = req.body?.categoryColumn ? String(req.body.categoryColumn) : undefined;
  const valueColumn = req.body?.valueColumn ? String(req.body.valueColumn) : undefined;
  const aggregation = String(req.body?.aggregation ?? 'count') as Aggregation;
  const filters = Array.isArray(req.body?.filters) ? req.body.filters as Array<{ column: string; values: string[] }> : [];
  const filteredRows = layer.rows.filter((row) => filters.every((filter) =>
    !filter.values?.length || filter.values.includes(String(row[filter.column] ?? '')),
  ));
  if (!categoryColumn) {
    return res.json({ rows: [{ category: 'Total', value: aggregate(filteredRows, valueColumn, aggregation) }], totalRows: filteredRows.length });
  }
  const groups = new Map<string, LayerRow[]>();
  for (const row of filteredRows) {
    const category = String(row[categoryColumn] ?? '').trim() || '(blank)';
    const group = groups.get(category) ?? [];
    group.push(row);
    groups.set(category, group);
  }
  const rows = [...groups.entries()]
    .map(([category, group]) => ({ category, value: aggregate(group, valueColumn, aggregation) }))
    .sort((a, b) => b.value - a.value)
    .slice(0, Math.max(1, Math.min(100, Number(req.body?.limit ?? 30))));
  return res.json({ rows, totalRows: filteredRows.length });
});

export default router;