// ./services/manso/src/index.mjs

import 'dotenv/config';// Variables de Entorno

import favicon from 'serve-favicon'; // Favicon
import express from 'express';
import expressLayouts from 'express-ejs-layouts';

import pool from '#db'; // Pool Singleton
import v1Router from '#v1Router'; // Rutas API v1
import expressPages from '#pages'; // Rutas "/", "/dashboard", ...


import path from 'path';
import { fileURLToPath } from 'url';
const __filename = fileURLToPath(import.meta.url);
const __dirname = path.dirname(__filename);



// ----------------------------------------------------------
// Variables del sistema
// ----------------------------------------------------------
const PORT = process.env.PORT ? Number(process.env.PORT) : 3000;
const url = v => !v ? "" : (v.startsWith("http") ? v : `/img/productos/${v}`);
const VALID_IDENT = /^[a-zA-Z_][a-zA-Z0-9_]*$/;
const q = (s) => `"${String(s).replace(/"/g, '""')}"`;  // Identificadores SQL -> comillas dobles y escape correcto



// ----------------------------------------------------------
// App + Motor de vistas EJS
// ----------------------------------------------------------
const app = express();
app.set('trust proxy', true);
app.set("views", path.join(__dirname, "views"));
app.set("view engine", "ejs");
app.set("layout", "layouts/main");

app.use(express.json());
app.use(express.json({ limit: '1mb' }));
app.use(express.static(path.join(__dirname, 'public'))); // Carga de archivos estaticos
app.use(expressLayouts);  // Carga los layouts que usara el renderizado
app.use(favicon(path.join(__dirname, 'public', 'favicon', 'favicon.ico'), {maxAge: '1y'}));
app.use(expressPages); // Renderizado trae las paginas desde ./services/manso/src/routes/routes.js



// ----------------------------------------------------------
// Uso de API v1
// ----------------------------------------------------------
app.use("/api/v1", v1Router);

// /api/rpc/get_producto/:id
// /api/v1/rpc/get_producto/:id -> /rpc/get_producto/:id


// ----------------------------------------------------------
// Seguridad: Tablas permitidas
// ----------------------------------------------------------
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'
];

function ensureTable(name) {
  const t = String(name || '').toLowerCase();
  if (!ALLOWED_TABLES.includes(t)) throw new Error('Tabla no permitida');
  return t;
}




// ----------------------------------------------------------
// Introspección de esquema
// ----------------------------------------------------------
async function loadColumns(client, table) {
  const sql = `
    SELECT
      c.column_name,
      c.data_type,
      c.is_nullable = 'YES' AS is_nullable,
      c.column_default,
      (SELECT EXISTS (
         SELECT 1 FROM pg_attribute a
         JOIN pg_class t ON t.oid = a.attrelid
         JOIN pg_index i ON i.indrelid = t.oid AND a.attnum = ANY(i.indkey)
         WHERE t.relname = $1 AND i.indisprimary AND a.attname = c.column_name
      )) AS is_primary,
      (SELECT a.attgenerated = 's' OR a.attidentity IN ('a','d')
       FROM pg_attribute a
       JOIN pg_class t ON t.oid = a.attrelid
       WHERE t.relname = $1 AND a.attname = c.column_name
      ) AS is_identity
    FROM information_schema.columns c
    WHERE c.table_schema='public' AND c.table_name=$1
    ORDER BY c.ordinal_position
  `;
  const { rows } = await client.query(sql, [table]);
  return rows;
}

async function loadForeignKeys(client, table) {
  const sql = `
    SELECT
      kcu.column_name,
      ccu.table_name AS foreign_table,
      ccu.column_name AS foreign_column
    FROM information_schema.table_constraints tc
    JOIN information_schema.key_column_usage kcu
      ON tc.constraint_name = kcu.constraint_name AND tc.table_schema = kcu.table_schema
    JOIN information_schema.constraint_column_usage ccu
      ON ccu.constraint_name = tc.constraint_name AND ccu.table_schema = tc.table_schema
    WHERE tc.table_schema='public' AND tc.table_name=$1 AND tc.constraint_type='FOREIGN KEY'
  `;
  const { rows } = await client.query(sql, [table]);
  const map = {};
  for (const r of rows) map[r.column_name] = { foreign_table: r.foreign_table, foreign_column: r.foreign_column };
  return map;
}

async function loadPrimaryKey(client, table) {
  const sql = `
    SELECT a.attname AS column_name
    FROM pg_index i
    JOIN pg_attribute a ON a.attrelid = i.indrelid AND a.attnum = ANY(i.indkey)
    JOIN pg_class t ON t.oid = i.indrelid
    WHERE t.relname = $1 AND i.indisprimary
  `;
  const { rows } = await client.query(sql, [table]);
  return rows.map(r => r.column_name);
}

// label column for FK options
async function pickLabelColumn(client, refTable) {
  const preferred = ['nombre','raz_social','apodo','documento','correo','telefono'];
  const { rows } = await client.query(
    `SELECT column_name, data_type
     FROM information_schema.columns
     WHERE table_schema='public' AND table_name=$1
     ORDER BY ordinal_position`, [refTable]
  );
  for (const cand of preferred) {
    if (rows.find(r => r.column_name === cand)) return cand;
  }
  const textish = rows.find(r => /text|character varying|varchar/i.test(r.data_type));
  if (textish) return textish.column_name;
  return rows[0]?.column_name || 'id';
}

// ----------------------------------------------------------
// Middleware para datos globales
// ----------------------------------------------------------
app.use((req, res, next) => {
  res.locals.currentPath = req.path;
  res.locals.pageTitle = "SuiteCoffee"; 
  res.locals.pageId = "";               
  next();
});




// ----------------------------------------------------------
// API
// ----------------------------------------------------------
app.get('/api/tables', async (_req, res) => {
  res.json(ALLOWED_TABLES);
});

app.get('/api/schema/:table', async (req, res) => {
  try {
    const table = ensureTable(req.params.table);
    const client = await pool.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 });
  }
});

app.get('/api/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 pool.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 });
  }
});

app.get('/api/table/:table', async (req, res) => {
  try {
    const table = ensureTable(req.params.table);
    const limit = Math.min(parseInt(req.query.limit || '100', 10), 1000);
    const client = await pool.getClient();
    try {
      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 result = await client.query(sql);

      // Normalizar: siempre devolver objetos {col: valor}
      const colNames = result.fields.map(f => f.name);
      let rows = result.rows;
      if (rows.length && Array.isArray(rows[0])) {
        rows = rows.map(r => Object.fromEntries(r.map((v, i) => [colNames[i], v])));
      }
      res.json(rows);
    } finally { client.release(); }
  } catch (e) {
    res.status(400).json({ error: e.message, code: e.code, detail: e.detail });
  }
});

app.post('/api/table/:table', async (req, res) => {
  const table = ensureTable(req.params.table);
  const payload = req.body || {};
  try {
    const client = await pool.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);
      }

      if (!cols.length) {
        const { rows } = await client.query(`INSERT INTO ${q(table)} DEFAULT VALUES RETURNING *`);
        res.status(201).json({ inserted: rows[0] });
      } else {
        const { 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 });
  }
});


// ----------------------------------------------------------
// Verificación de conexión
// ----------------------------------------------------------
async function verificarConexion() {
  try {
    var client = await pool.getClient();
    let res = await client.query('SELECT NOW() AS hora');
    console.log(`\nConexión con la base de datos ${process.env.DB_NAME} fue exitosa.`);
    console.log('Fecha y hora actual de la base de datos:\n ->', res.rows[0].hora);
  } catch (error) {
    console.error('Error al conectar con la base de datos al iniciar: \n ->', error.message);
    console.error('Revisar credenciales y accesos de red.');
  } finally{
    client.release();
  }
}



// ----------------------------------------------------------
// Inicio del servidor
// ----------------------------------------------------------

app.listen(PORT, () => {
  console.log(`Servidor de aplicación escuchando en ${`http://localhost:${PORT}`}`);
  console.log(`Comprobando accesibilidad a la db ${process.env.DB_NAME} del host ${process.env.DB_HOST} ...`);
  verificarConexion();
});

// Healthcheck
app.get('/health', (_req, res) => res.status(200).json({ status: 'ok'}));
