import { Router } from 'express';
import { VLAN_ID } from '../config/env.js';
import { clearRequests, getRecentRequests, registerSse } from '../sse.js';
import {
  deleteUserFromDb,
  readUsersFromDb,
  upsertUserToDb,
  pool,
  disableGuestsFromYesterday,
  getActiveSessions,
  getSessionHistory,
  syncConnectedDevicesFromSessions,
  markStaleSessions,
  getSessionStats
} from '../services/db.js';
import { disconnectUserSessions } from '../services/radius.js';

const router = Router();

// Users
router.get('/users', async (_req, res) => {
  try {
    const items = await readUsersFromDb();
    res.json({ items });
  } catch (e) {
    console.error('GET /api/users error:', e?.message || e);
    res.status(500).json({ items: [], ok: false, error: 'db_error' });
  }
});

router.get('/users/:username', async (req, res) => {
  try {
    const uname = String(req.params.username);
    const items = await readUsersFromDb();
    const found = items.find(u => u.username === uname);
    if (!found) return res.status(404).json({ ok: false, error: 'not_found' });
    res.json({ ok: true, item: found });
  } catch (e) {
    console.error('GET /api/users/:username error:', e?.message || e);
    res.status(500).json({ ok: false, error: 'db_error' });
  }
});

router.post('/users', async (req, res) => {
  const { username, password, vlan, disabled, etiquetas } = req.body || {};
  if (!username || !password) return res.status(400).json({ ok: false, error: 'username and password required' });
  const user = { username: String(username), password: String(password), vlan: String(vlan || VLAN_ID), disabled: !!disabled };
  if (Array.isArray(etiquetas)) user.etiquetas = etiquetas.map(String).slice(0, 100);
  await upsertUserToDb(user);
  res.json({ ok: true });
});

router.patch('/users/:username', async (req, res) => {
  const uname = String(req.params.username);
  const { password, vlan, disabled, etiquetas } = req.body || {};
  const current = (await readUsersFromDb()).find(u => u.username === uname);
  if (!current) return res.status(404).json({ ok: false, error: 'not_found' });
  const next = {
    username: uname,
    password: password !== undefined ? String(password) : current.password,
    vlan: vlan !== undefined ? String(vlan) : current.vlan,
    disabled: disabled !== undefined ? !!disabled : current.disabled,
  };
  if (etiquetas !== undefined) next.etiquetas = Array.isArray(etiquetas) ? etiquetas.map(String).slice(0, 100) : current.etiquetas;
  await upsertUserToDb(next);
  if (disabled === true) {
    disconnectUserSessions(uname).catch(err => console.error('CoA disconnect error:', err));
  }
  res.json({ ok: true });
});

router.delete('/users/:username', async (req, res) => {
  const uname = String(req.params.username);
  await deleteUserFromDb(uname);
  res.json({ ok: true });
});

// Requests
router.get('/requests', (_req, res) => {
  res.json({ items: getRecentRequests() });
});

router.delete('/requests', (_req, res) => {
  clearRequests();
  res.json({ ok: true });
});

router.get('/requests.csv', (_req, res) => {
  const items = getRecentRequests();
  const cols = ['ts','type','user','nas','calling','called','decision','vlan','bw_down','bw_up'];
  const lines = [cols.join(',')];
  for (const ev of items) {
    const attrs = ev.attrs || {};
    const row = [
      ev.ts || '',
      ev.type || '',
      attrs['User-Name'] || attrs['User-Name*0'] || '',
      attrs['NAS-IP-Address'] || attrs['NAS-Identifier'] || '',
      attrs['Calling-Station-Id'] || '',
      attrs['Called-Station-Id'] || '',
      ev.decision || '',
      ev.vlan || '',
      (ev.bandwidth && ev.bandwidth.down) || '',
      (ev.bandwidth && ev.bandwidth.up) || ''
    ];
    const esc = (v) => String(v).includes(',') || String(v).includes('"') || String(v).includes('\n')
      ? '"' + String(v).replace(/"/g, '""') + '"'
      : String(v);
    lines.push(row.map(esc).join(','));
  }
  const csv = lines.join('\n');
  const ts = new Date().toISOString().replace(/[:T]/g, '-').split('.')[0];
  res.setHeader('Content-Type', 'text/csv; charset=utf-8');
  res.setHeader('Content-Disposition', `attachment; filename="radius-events-${ts}.csv"`);
  res.send(csv);
});

// Export CSV: users, devices, vlans
router.get('/users.csv', async (_req, res) => {
  const items = await readUsersFromDb();
  const cols = ['username','password','vlan','disabled','etiquetas','created_at','updated_at','habilitado_since'];
  const esc = (v) => {
    const s = v == null ? '' : Array.isArray(v) ? v.join(';') : String(v);
    return /[",\n]/.test(s) ? '"' + s.replace(/"/g, '""') + '"' : s;
  };
  const lines = [cols.join(',')];
  for (const u of items) {
    lines.push([u.username,u.password,u.vlan,u.disabled ? 'true':'false',u.etiquetas||[],u.created_at||'',u.updated_at||'',u.habilitado_since||''].map(esc).join(','));
  }
  res.setHeader('Content-Type','text/csv; charset=utf-8');
  res.setHeader('Content-Disposition','attachment; filename="users.csv"');
  res.send(lines.join('\n'));
});

router.get('/devices.csv', async (_req, res) => {
  const r = await pool.query('SELECT id,mac,nombre,descripcion,vendor,first_seen,last_seen FROM dispositivos ORDER BY id ASC');
  const cols = ['id','mac','nombre','descripcion','vendor','first_seen','last_seen'];
  const esc = (v) => {
    const s = v == null ? '' : String(v);
    return /[",\n]/.test(s) ? '"' + s.replace(/"/g, '""') + '"' : s;
  };
  const lines = [cols.join(',')];
  for (const d of r.rows) lines.push(cols.map(c => esc(d[c])).join(','));
  res.setHeader('Content-Type','text/csv; charset=utf-8');
  res.setHeader('Content-Disposition','attachment; filename="devices.csv"');
  res.send(lines.join('\n'));
});

router.get('/vlans.csv', async (_req, res) => {
  const r = await pool.query('SELECT id,nombre,descripcion FROM vlans ORDER BY id ASC');
  const cols = ['id','nombre','descripcion'];
  const esc = (v) => {
    const s = v == null ? '' : String(v);
    return /[",\n]/.test(s) ? '"' + s.replace(/"/g, '""') + '"' : s;
  };
  const lines = [cols.join(',')];
  for (const d of r.rows) lines.push(cols.map(c => esc(d[c])).join(','));
  res.setHeader('Content-Type','text/csv; charset=utf-8');
  res.setHeader('Content-Disposition','attachment; filename="vlans.csv"');
  res.send(lines.join('\n'));
});

// Import CSV: expect JSON { csv: string }
function parseCsv(str){
  const rows=[]; let i=0, cur='', row=[], inq=false; for(; i<str.length; i++){ const ch=str[i]; if(ch==='"'){ if(inq && str[i+1]==='"'){ cur+='"'; i++; } else { inq=!inq; } } else if(ch===',' && !inq){ row.push(cur); cur=''; } else if((ch==='\n' || ch==='\r') && !inq){ if(ch==='\r' && str[i+1]==='\n') i++; row.push(cur); rows.push(row); row=[]; cur=''; } else { cur+=ch; } } if(cur.length||row.length) { row.push(cur); rows.push(row); } return rows; }

router.post('/users/import', async (req, res) => {
  try {
    const csv = String((req.body && req.body.csv) || '');
    const rows = parseCsv(csv).filter(r => r.length>1);
    if (rows.length === 0) return res.json({ ok: true, count: 0 });
    const header = rows[0].map(s=>s.trim().toLowerCase());
    const idx = (k) => header.indexOf(k);
    let count=0;
    for (let r of rows.slice(1)) {
      const user = {
        username: r[idx('username')]?.trim(),
        password: r[idx('password')] ?? '',
        vlan: r[idx('vlan')] ?? '',
        disabled: /^(true|1|yes)$/i.test(r[idx('disabled')] || ''),
      };
      const tags = r[idx('etiquetas')] || '';
      if (tags) user.etiquetas = String(tags).split(/[;,|]/).map(s=>s.trim()).filter(Boolean).slice(0,100);
      if (user.username && user.password) { await upsertUserToDb(user); count++; }
    }
    res.json({ ok: true, count });
  } catch (e) {
    console.error('POST /api/users/import error:', e?.message || e);
    res.status(500).json({ ok: false, error: 'import_error' });
  }
});

router.post('/devices/import', async (req, res) => {
  try {
    const csv = String((req.body && req.body.csv) || '');
    const rows = parseCsv(csv).filter(r => r.length>1);
    if (rows.length === 0) return res.json({ ok: true, count: 0 });
    const header = rows[0].map(s=>s.trim().toLowerCase());
    const idx = (k) => header.indexOf(k);
    let count=0;
    for (let r of rows.slice(1)) {
      const mac = r[idx('mac')]?.trim();
      if (!mac) continue;
      const nombre = r[idx('nombre')] || null;
      const descripcion = r[idx('descripcion')] || null;
      const vendor = r[idx('vendor')] || null;
      await pool.query(
        `INSERT INTO dispositivos (mac,nombre,descripcion,vendor) VALUES ($1,$2,$3,$4)
         ON CONFLICT (mac) DO UPDATE SET nombre=EXCLUDED.nombre, descripcion=EXCLUDED.descripcion, vendor=EXCLUDED.vendor, last_seen=NOW()`,
        [mac, nombre, descripcion, vendor]
      );
      count++;
    }
    res.json({ ok: true, count });
  } catch (e) {
    console.error('POST /api/devices/import error:', e?.message || e);
    res.status(500).json({ ok: false, error: 'import_error' });
  }
});

router.post('/vlans/import', async (req, res) => {
  try {
    const csv = String((req.body && req.body.csv) || '');
    const rows = parseCsv(csv).filter(r => r.length>1);
    if (rows.length === 0) return res.json({ ok: true, count: 0 });
    const header = rows[0].map(s=>s.trim().toLowerCase());
    const idx = (k) => header.indexOf(k);
    let count=0;
    for (let r of rows.slice(1)) {
      const id = parseInt(r[idx('id')]||'',10);
      if (!Number.isInteger(id)) continue;
      const nombre = r[idx('nombre')] || null;
      const descripcion = r[idx('descripcion')] || null;
      await pool.query(
        `INSERT INTO vlans (id,nombre,descripcion) VALUES ($1,$2,$3)
         ON CONFLICT (id) DO UPDATE SET nombre=EXCLUDED.nombre, descripcion=EXCLUDED.descripcion`,
        [id, nombre, descripcion]
      );
      count++;
    }
    res.json({ ok: true, count });
  } catch (e) {
    console.error('POST /api/vlans/import error:', e?.message || e);
    res.status(500).json({ ok: false, error: 'import_error' });
  }
});

// SSE events
router.get('/events', (req, res) => {
  registerSse(req, res, {});
});

// Raw DB views (read-only)
router.get('/db/tables', async (_req, res) => {
  try {
    const { rows } = await pool.query(
      "SELECT table_name FROM information_schema.tables WHERE table_schema='public' AND table_type='BASE TABLE' ORDER BY table_name ASC"
    );
    res.json({ items: rows.map(r => r.table_name) });
  } catch (e) {
    res.status(500).json({ ok: false, error: 'db_error' });
  }
});

router.get('/db/table/:name', async (req, res) => {
  const t = String(req.params.name);
  try {
    // validate table name exists and is public
    const { rows } = await pool.query(
      "SELECT table_name FROM information_schema.tables WHERE table_schema='public' AND table_type='BASE TABLE' AND table_name = $1",
      [t]
    );
    if (rows.length === 0) return res.status(404).json({ ok: false, error: 'not_found' });
    const limit = Math.max(1, Math.min(1000, parseInt(String(req.query.limit || '100'), 10) || 100));
    const offset = Math.max(0, parseInt(String(req.query.offset || '0'), 10) || 0);
    const totalRes = await pool.query(`SELECT COUNT(*)::int AS n FROM "${t}"`);
    const total = totalRes.rows[0]?.n || 0;
    const result = await pool.query(`SELECT * FROM "${t}" OFFSET $1 LIMIT $2`, [offset, limit]);
    res.json({ ok: true, columns: result.fields.map(f => f.name), rows: result.rows, total, limit, offset });
  } catch (e) {
    res.status(500).json({ ok: false, error: 'db_error' });
  }
});

// VLANs
router.post('/vlans', async (req, res) => {
  try {
    const { id, nombre, descripcion } = req.body || {};
    const vid = Number(id);
    if (!Number.isInteger(vid) || vid <= 0) {
      return res.status(400).json({ ok: false, error: 'invalid_vlan_id' });
    }
    if (!nombre || String(nombre).trim() === '') {
      return res.status(400).json({ ok: false, error: 'nombre_required' });
    }
    await pool.query(
      'INSERT INTO vlans (id, nombre, descripcion) VALUES ($1, $2, $3)',
      [vid, String(nombre), descripcion != null ? String(descripcion) : null]
    );
    res.json({ ok: true });
  } catch (e) {
    if (e && e.code === '23505') {
      return res.status(409).json({ ok: false, error: 'vlan_exists' });
    }
    console.error('POST /api/vlans error:', e?.message || e);
    res.status(500).json({ ok: false, error: 'db_error' });
  }
});

// Devices list (read-only)
router.get('/devices', async (_req, res) => {
  try {
    const result = await pool.query('SELECT id, mac, nombre, vendor, descripcion, first_seen, last_seen FROM dispositivos ORDER BY last_seen DESC NULLS LAST, first_seen DESC');
    res.json({ items: result.rows });
  } catch (e) {
    console.error('GET /api/devices error:', e?.message || e);
    res.status(500).json({ ok: false, error: 'db_error', items: [] });
  }
});

router.patch('/devices/:id', async (req, res) => {
  try {
    const id = parseInt(String(req.params.id), 10);
    if (!Number.isInteger(id) || id <= 0) return res.status(400).json({ ok: false, error: 'invalid_id' });
    const { nombre, descripcion } = req.body || {};
    const { rowCount } = await pool.query('UPDATE dispositivos SET nombre = $2, descripcion = $3, last_seen = NOW() WHERE id = $1', [id, nombre != null ? String(nombre) : null, descripcion != null ? String(descripcion) : null]);
    if (rowCount === 0) return res.status(404).json({ ok: false, error: 'not_found' });
    res.json({ ok: true });
  } catch (e) {
    console.error('PATCH /api/devices/:id error:', e?.message || e);
    res.status(500).json({ ok: false, error: 'db_error' });
  }
});

router.post('/users/:username/disconnect', async (req, res) => {
  try {
    const uname = String(req.params.username);
    await disconnectUserSessions(uname);
    res.json({ ok: true });
  } catch (e) {
    console.error('POST /api/users/:username/disconnect error:', e?.message || e);
    res.status(500).json({ ok: false, error: 'disconnect_error' });
  }
});

import { disconnectByMac } from '../services/radius.js';
router.post('/devices/:id/disconnect', async (req, res) => {
  try {
    const id = parseInt(String(req.params.id), 10);
    if (!Number.isInteger(id) || id <= 0) return res.status(400).json({ ok: false, error: 'invalid_id' });
    const r = await pool.query('SELECT mac FROM dispositivos WHERE id = $1', [id]);
    if (r.rows.length === 0) return res.status(404).json({ ok: false, error: 'not_found' });
    await disconnectByMac(r.rows[0].mac);
    res.json({ ok: true });
  } catch (e) {
    console.error('POST /api/devices/:id/disconnect error:', e?.message || e);
    res.status(500).json({ ok: false, error: 'disconnect_error' });
  }
});

// Manual trigger: disable invited users from yesterday (America/Tegucigalpa day)
router.post('/guests/disable-yesterday', async (_req, res) => {
  try {
    const { count } = await disableGuestsFromYesterday();
    res.json({ ok: true, count });
  } catch (e) {
    console.error('POST /api/guests/disable-yesterday error:', e?.message || e);
    res.status(500).json({ ok: false, error: 'db_error' });
  }
});

// ==================== SESSION ENDPOINTS ====================

// GET /api/sessions - Active sessions
router.get('/sessions', async (req, res) => {
  try {
    // Por defecto devuelve todas las sesiones recientes (últimas 24h o las últimas 200)
    const sessions = await getSessionHistory({ limit: 200 });
    const stats = await getSessionStats();
    res.json({ items: sessions, count: sessions.length, stats });
  } catch (e) {
    console.error('GET /api/sessions error:', e?.message || e);
    res.status(500).json({ ok: false, error: 'db_error' });
  }
});

// GET /api/sessions/history - Session history with filters
router.get('/sessions/history', async (req, res) => {
  try {
    const { username, dispositivo_id, mac, status, limit, offset, desde, hasta } = req.query;
    const sessions = await getSessionHistory({
      username: username || undefined,
      dispositivoId: dispositivo_id ? parseInt(dispositivo_id, 10) : undefined,
      mac: mac || undefined,
      status: status || undefined,
      limit: Math.min(parseInt(limit || '50', 10), 500),
      offset: parseInt(offset || '0', 10),
      desde: desde || undefined,
      hasta: hasta || undefined
    });
    res.json({ items: sessions });
  } catch (e) {
    console.error('GET /api/sessions/history error:', e?.message || e);
    res.status(500).json({ ok: false, error: 'db_error' });
  }
});

// GET /api/users/:username/sessions - Sessions for a specific user
router.get('/users/:username/sessions', async (req, res) => {
  try {
    const username = String(req.params.username);
    const sessions = await getSessionHistory({ username, limit: 100 });
    const active = sessions.filter(s => s.status === 'active');
    res.json({
      active,
      recent: sessions.slice(0, 20),
      activeCount: active.length,
      totalCount: sessions.length
    });
  } catch (e) {
    console.error('GET /api/users/:username/sessions error:', e?.message || e);
    res.status(500).json({ ok: false, error: 'db_error' });
  }
});

// GET /api/devices/:id/sessions - Session history for a specific device
router.get('/devices/:id/sessions', async (req, res) => {
  try {
    const id = parseInt(String(req.params.id), 10);
    if (!Number.isInteger(id) || id <= 0) {
      return res.status(400).json({ ok: false, error: 'invalid_id' });
    }
    const sessions = await getSessionHistory({ dispositivoId: id, limit: 100 });
    const active = sessions.filter(s => s.status === 'active');
    res.json({
      active,
      history: sessions,
      activeCount: active.length,
      totalCount: sessions.length
    });
  } catch (e) {
    console.error('GET /api/devices/:id/sessions error:', e?.message || e);
    res.status(500).json({ ok: false, error: 'db_error' });
  }
});

// POST /api/sessions/sync - Force sync of sessions
router.post('/sessions/sync', async (_req, res) => {
  try {
    await syncConnectedDevicesFromSessions();
    const staleResult = await markStaleSessions(10);
    res.json({ ok: true, staleMarked: staleResult.count });
  } catch (e) {
    console.error('POST /api/sessions/sync error:', e?.message || e);
    res.status(500).json({ ok: false, error: 'sync_error' });
  }
});

// GET /api/sessions.csv - Export session history as CSV
router.get('/sessions.csv', async (req, res) => {
  try {
    const { desde, hasta, status } = req.query;
    const sessions = await getSessionHistory({
      limit: 10000,
      desde: desde || undefined,
      hasta: hasta || undefined,
      status: status || undefined
    });
    const cols = ['session_id','username','mac','device_name','nas_ip','nas_id','started_at','ended_at','status','stop_reason','session_time','bytes_in','bytes_out'];
    const esc = (v) => {
      const s = v == null ? '' : String(v);
      return /[",\n]/.test(s) ? '"' + s.replace(/"/g, '""') + '"' : s;
    };
    const lines = [cols.join(',')];
    for (const s of sessions) {
      lines.push([
        s.session_id,
        s.username,
        s.mac || s.calling_station_id,
        s.device_name || '',
        s.nas_ip || '',
        s.nas_id || '',
        s.started_at || '',
        s.ended_at || '',
        s.status,
        s.stop_reason || '',
        s.session_time || 0,
        s.bytes_in || 0,
        s.bytes_out || 0
      ].map(esc).join(','));
    }
    const ts = new Date().toISOString().replace(/[:T]/g, '-').split('.')[0];
    res.setHeader('Content-Type', 'text/csv; charset=utf-8');
    res.setHeader('Content-Disposition', `attachment; filename="sessions-${ts}.csv"`);
    res.send(lines.join('\n'));
  } catch (e) {
    console.error('GET /api/sessions.csv error:', e?.message || e);
    res.status(500).json({ ok: false, error: 'export_error' });
  }
});

export default router;
