// packages/api/v1/routes/routes.js

import { Router } from 'express';
import { withTenantClient, tquery } from '../repositories/db.mjs'
import { listarComandas, detalleComanda, actualizarComanda, eliminarComanda } from './handlers/comandasHand.mjs';
import { loadColumns, loadForeignKeys, loadPrimaryKey, pickLabelColumn } from './utils/schemaInspector.mjs';

const router = Router();

const ALLOWED_TABLES = [
  'roles', 'usuarios', 'usua_roles',
  'categorias', 'productos',
  'clientes', 'mesas',
  'comandas', 'deta_comandas',
  'proveedores', 'compras', 'deta_comp_producto',
  'mate_primas', 'deta_comp_materias',
  'prov_producto', 'prov_mate_prima',
  'receta_producto', 'asistencia_resumen_diario',
  'asistencia_intervalo', 'asistencia_detalle',
  'vw_compras'
];

const VALID_IDENT = /^[a-z_][a-z0-9_]*$/i;
const q = (ident) => `"${String(ident).replace(/"/g, '""')}"`;

function ensureTable(name) {
  if (!VALID_IDENT.test(name)) throw new Error('Identificador inválido');
  if (!ALLOWED_TABLES.includes(name)) throw new Error('Tabla no permitida');
  return name;
}

// ==========================================================
// Rutas de API v1
// ==========================================================

// ----------------------------------------------------------
// API Tablas
/*router.route('/tables').get( async (_req, res) => {
  res.json(ALLOWED_TABLES);
});*/

// GET /api/schema/:table → columnas + foreign keys
/*router.get('/schema/:table', async (req, res) => {
  try {
    const table = ensureTable(req.params.table);
    const client = await poolTenants.getClient();
    try {
      const columns = await loadColumns(client, table);
      const fks = await loadForeignKeys(client, table);
      const enriched = columns.map(c => ({ ...c, foreign: fks[c.column_name] || null }));
      res.json({ table, columns: enriched });
    } finally {
      client.release();
    }
  } catch (e) {
    res.status(400).json({ error: e.message });
  }
});*/

// GET /api/options/:table/:column → opciones FK
/*router.get('/options/:table/:column', async (req, res) => {
  try {
    const table = ensureTable(req.params.table);
    const column = req.params.column;
    if (!VALID_IDENT.test(column)) throw new Error('Columna inválida');

    const client = await poolTenants.getClient();
    try {
      const fks = await loadForeignKeys(client, table);
      const fk = fks[column];
      if (!fk) return res.json([]);

      const refTable = fk.foreign_table;
      const refId = fk.foreign_column;
      const labelCol = await pickLabelColumn(client, refTable);

      const sql = `SELECT ${q(refId)} AS id, ${q(labelCol)} AS label
                   FROM ${q(refTable)} ORDER BY ${q(labelCol)} LIMIT 1000`;
      const result = await client.query(sql);
      res.json(result.rows);
    } finally {
      client.release();
    }
  } catch (e) {
    res.status(400).json({ error: e.message });
  }
});*/

// GET /api/table/:table → preview de datos
/*router.get('/table/:table', async (req, res) => {
  try {
    const table = ensureTable(req.params.table);
    const limit = Math.min(parseInt(req.query.limit || '100', 10), 1000);
    await withTenantClient(req, async (client) => {
      const pks = await loadPrimaryKey(client, table);
      const orderBy = pks.length ? `ORDER BY ${pks.map(q).join(', ')} DESC` : '';
      const sql = `SELECT * FROM ${q(table)} ${orderBy} LIMIT ${limit}`;
      const { rows } = await client.query(sql);
      res.json(rows);
    });
  } catch (e) {
    res.status(400).json({ error: e.message, code: e.code, detail: e.detail });
  }
});*/

// POST /api/table/:table → insertar fila
/*router.post('/table/:table', async (req, res) => {
  const table = ensureTable(req.params.table);
  const payload = req.body || {};
  try {
    const client = await poolTenants.getClient();
    try {
      const columns = await loadColumns(client, table);
      const insertable = columns.filter(c =>
        !c.is_primary &&
        !c.is_identity &&
        !(c.column_default || '').startsWith('nextval(')
      );
      const allowedCols = new Set(insertable.map(c => c.column_name));

      const cols = [];
      const vals = [];
      const params = [];
      let idx = 1;
      for (const [k, v] of Object.entries(payload)) {
        if (!allowedCols.has(k)) continue;
        if (!VALID_IDENT.test(k)) continue;
        cols.push(q(k));
        vals.push(`$${idx++}`);
        params.push(v);
      }

      let rows;
      if (!cols.length) {
        ({ rows } = await client.query(
          `INSERT INTO ${q(table)} DEFAULT VALUES RETURNING *`
        ));
      } else {
        ({ rows } = await client.query(
          `INSERT INTO ${q(table)} (${cols.join(', ')})
           VALUES (${vals.join(', ')}) RETURNING *`,
          params
        ));
      }
      res.status(201).json({ inserted: rows[0] });
    } catch (e) {
      if (e.code === '23503') return res.status(400).json({ error: 'Violación de clave foránea', detail: e.detail });
      if (e.code === '23505') return res.status(400).json({ error: 'Violación de unicidad', detail: e.detail });
      if (e.code === '23514') return res.status(400).json({ error: 'Violación de CHECK', detail: e.detail });
      if (e.code === '23502') return res.status(400).json({ error: 'Campo NOT NULL faltante', detail: e.detail });
      throw e;
    } finally {
      client.release();
    }
  } catch (e) {
    res.status(400).json({ error: e.message });
  }
});
*/











































// ----------------------------------------------------------
// API Productos
// ----------------------------------------------------------

// GET producto + receta
/*router.route('/rpc/get_producto/:id').get( async (req, res) => {
  const client = await poolTenants.getClient()
  const id = Number(req.params.id);
  const { rows } = await client.query('SELECT public.get_producto($1) AS data', [id]);
  res.json(rows[0]?.data || {});
  client.release();
});*/

// POST guardar producto + receta
/*router.route('/rpc/save_producto').post(async (req, res) => {
  try {
    // console.debug('receta payload:', req.body?.receta); // habilitalo si lo necesitás
    const client = await poolTenants.getClient()
    const q = 'SELECT public.save_producto($1,$2,$3,$4,$5,$6,$7::jsonb) AS id_producto';
    const { id_producto=null, nombre, img_producto=null, precio=0, activo=true, id_categoria=null, receta=[] } = req.body || {};
    const params = [id_producto, nombre, img_producto, precio, activo, id_categoria, JSON.stringify(receta||[])];
    const { rows } = await client.query(q, params);
    res.json(rows[0] || {});
    client.release();
  } catch(e) {
    console.error(e);
    res.status(500).json({ error: 'save_producto failed' });
  }
});*/



// ----------------------------------------------------------
// API Materias Primas
// ----------------------------------------------------------

// GET MP + proveedores
/*router.route('/rpc/get_materia/:id').get(async (req, res) => {
  const id = Number(req.params.id);
  try {
    const client = await poolTenants.getClient()
    const { rows } = await client.query('SELECT public.get_materia_prima($1) AS data', [id]);
    res.json(rows[0]?.data || {});
    client.release();
  } catch (e) {
    console.error(e);
    res.status(500).json({ error: 'get_materia failed' });
  }
});

// SAVE MP + proveedores (array)
router.route('/rpc/save_materia').post( async (req, res) => {
  const { id_mat_prima=null, nombre, unidad, activo=true, proveedores=[] } = req.body || {};
  try {
    const q = 'SELECT public.save_materia_prima($1,$2,$3,$4,$5::jsonb) AS id_mat_prima';
    const params = [id_mat_prima, nombre, unidad, activo, JSON.stringify(proveedores||[])];
    const { rows } = await poolTenants.query(q, params);
    res.json(rows[0] || {});
  } catch (e) {
    console.error(e);
    res.status(500).json({ error: 'save_materia failed' });
  }
});



// ----------------------------------------------------------
// API Usuarios y Asistencias
// ----------------------------------------------------------

// POST /api/rpc/find_usuarios_por_documentos  { docs: ["12345678","09123456", ...] }
router.route('/rpc/find_usuarios_por_documentos').post( async (req, res) => {
  try {
    const docs = Array.isArray(req.body?.docs) ? req.body.docs : [];
    const sql  = 'SELECT public.find_usuarios_por_documentos($1::jsonb) AS data';
    const { rows } = await poolTenants.query(sql, [JSON.stringify(docs)]);
    res.json(rows[0]?.data || {});
  } catch (e) {
    console.error(e);
    res.status(500).json({ error: 'find_usuarios_por_documentos failed' });
  }
});

// POST /api/rpc/import_asistencia  { registros: [...], origen?: "AGL_001.txt" }
router.route('/rpc/import_asistencia').post( async (req, res) => {
  try {
    const registros = Array.isArray(req.body?.registros) ? req.body.registros : [];
    const origen    = req.body?.origen || null;
    const sql = 'SELECT public.import_asistencia($1::jsonb,$2) AS data';
    const { rows } = await poolTenants.query(sql, [JSON.stringify(registros), origen]);
    res.json(rows[0]?.data || {});
  } catch (e) {
    console.error(e);
    res.status(500).json({ error: 'import_asistencia failed' });
  }
});

// Consultar datos de asistencia (raw + pares) para un usuario y rango
router.route('/rpc/asistencia_get').post( async (req, res) => {
  try {
    const { doc, desde, hasta } = req.body || {};
    const sql = 'SELECT public.asistencia_get($1::text,$2::date,$3::date) AS data';
    const { rows } = await poolTenants.query(sql, [doc, desde, hasta]);
    res.json(rows[0]?.data || {});
  } catch (e) {
    console.error(e); res.status(500).json({ error: 'asistencia_get failed' });
  }
});

// Editar un registro crudo y recalcular pares
router.route('/rpc/asistencia_update_raw').post( async (req, res) => {
  try {
    const { id_raw, fecha, hora, modo } = req.body || {};
    const sql = 'SELECT public.asistencia_update_raw($1::bigint,$2::date,$3::text,$4::text) AS data';
    const { rows } = await poolTenants.query(sql, [id_raw, fecha, hora, modo ?? null]);
    res.json(rows[0]?.data || {});
  } catch (e) {
    console.error(e); res.status(500).json({ error: 'asistencia_update_raw failed' });
  }
});

// Eliminar un registro crudo y recalcular pares
router.route('/rpc/asistencia_delete_raw').post( async (req, res) => {
  try {
    const { id_raw } = req.body || {};
    const sql = 'SELECT public.asistencia_delete_raw($1::bigint) AS data';
    const { rows } = await poolTenants.query(sql, [id_raw]);
    res.json(rows[0]?.data || {});
  } catch (e) {
    console.error(e); res.status(500).json({ error: 'asistencia_delete_raw failed' });
  }
});


// ----------------------------------------------------------
// API Reportes
// ----------------------------------------------------------

// POST /api/rpc/report_tickets { year }
router.route('/rpc/report_tickets').post( async (req, res) => {
  try {
    const y = parseInt(req.body?.year ?? req.query?.year, 10);
    const year = (Number.isFinite(y) && y >= 2000 && y <= 2100)
      ? y
      : (new Date()).getFullYear();

    const { rows } = await poolTenants.query(
      'SELECT public.report_tickets_year($1::int) AS j', [year]
    );
    res.json(rows[0].j);
  } catch (e) {
    console.error('report_tickets error:', e);
    res.status(500).json({
      error: 'report_tickets failed',
      message: e.message, detail: e.detail, where: e.where, code: e.code
    });
  }
});

// POST /api/rpc/report_asistencia { desde: 'YYYY-MM-DD', hasta: 'YYYY-MM-DD' }
router.route('/rpc/report_asistencia').post( async (req, res) => {
  try {
    let { desde, hasta } = req.body || {};
    // defaults si vienen vacíos/invalidos
    const re = /^\d{4}-\d{2}-\d{2}$/;
    if (!re.test(desde) || !re.test(hasta)) {
      const end   = new Date();
      const start = new Date(end); start.setDate(end.getDate()-30);
      desde = start.toISOString().slice(0,10);
      hasta = end.toISOString().slice(0,10);
    }

    const { rows } = await poolTenants.query(
      'SELECT public.report_asistencia($1::date,$2::date) AS j', [desde, hasta]
    );
    res.json(rows[0].j);
  } catch (e) {
    console.error('report_asistencia error:', e);
    res.status(500).json({
      error: 'report_asistencia failed',
      message: e.message, detail: e.detail, where: e.where, code: e.code
    });
  }
});

// ----------------------------------------------------------
// API Compras y Gastos
// ----------------------------------------------------------

// Guardar (insert/update)
router.route('/rpc/save_compra').post( async (req, res) => {
  try {
    const { id_compra, id_proveedor, fec_compra, detalles } = req.body || {};
    const sql  = 'SELECT * FROM public.save_compra($1::int,$2::int,$3::timestamptz,$4::jsonb)';
    const args = [id_compra ?? null, id_proveedor, fec_compra ? new Date(fec_compra) : null, JSON.stringify(detalles)];
    const { rows } = await poolTenants.query(sql, args);
    res.json(rows[0]); // { id_compra, total }
  } catch (e) {
    console.error('save_compra error:', e);
    res.status(500).json({ error: 'save_compra failed', message: e.message, detail: e.detail, where: e.where, code: e.code });
  }
});


// Obtener para editar
router.route('/rpc/get_compra').post( async (req, res) => {
  try {
    const { id_compra } = req.body || {};
    const sql = `SELECT public.get_compra($1::int) AS data`;
    const { rows } = await poolTenants.query(sql, [id_compra]);
    res.json(rows[0]?.data || {});
  } catch (e) {
    console.error(e); res.status(500).json({ error: 'get_compra failed' });
  }
});

// Eliminar
router.route('/rpc/delete_compra').post( async (req, res) => {
  try {
    const { id_compra } = req.body || {};
    await poolTenants.query(`SELECT public.delete_compra($1::int)`, [id_compra]);
    res.json({ ok: true });
  } catch (e) {
    console.error(e); res.status(500).json({ error: 'delete_compra failed' });
  }
});


// POST /api/rpc/report_gastos  { year: 2025 }
router.route('/rpc/report_gastos').post( async (req, res) => {
  try {
    const year = parseInt(req.body?.year ?? new Date().getFullYear(), 10);
    const { rows } = await poolTenants.query(
      'SELECT public.report_gastos($1::int) AS j', [year]
    );
    res.json(rows[0].j);
  } catch (e) {
    console.error('report_gastos error:', e);
    res.status(500).json({
      error: 'report_gastos failed',
      message: e.message, detail: e.detail, code: e.code
    });
  }
});*/


export default router;