// packages/api/v1/repositories/comandasRepo.mjs

import { withTenantClient } from './db.mjs';
import { loadColumns, loadPrimaryKey } from '../routes/utils/schemaInspector.mjs';

const TABLE = 'comandas';
const VALID_IDENT = /^[a-z_][a-z0-9_]*$/i;

export async function listComandas({ schema, abierta, limit }) {
  return withTenantClient(schema, async (db) => {
    const max = Math.min(parseInt(limit || 200, 10), 1000);
    const { rows } = await db.query(
      `SELECT * FROM public.f_comandas_resumen($1, $2)`,
      [abierta, max]
    );
    return rows;
  });
}

export async function getDetalleItems({ schema, id }) {
  return withTenantClient(schema, async (db) => {
    const { rows } = await db.query(
      `SELECT id_det_comanda, id_producto, producto_nombre,
              cantidad, pre_unitario, subtotal, observaciones
         FROM public.v_comandas_detalle_items
        WHERE id_comanda = $1::int
        ORDER BY id_det_comanda`,
      [id]
    );
    return rows;
  });
}

export async function abrirComanda({ schema, id }) {
  return withTenantClient(schema, async (db) => {
    const st = await db.query(`SELECT eliminada FROM public.${q(TABLE)} WHERE id_comanda = $1`, [id]);
    if (!st.rowCount) return null;
    if (st.rows[0].eliminada === true) {
      const err = new Error('Comanda eliminada. Debe restaurarse antes de abrir.');
      err.http = { status: 409 };
      throw err;
    }
    const { rows } = await db.query(`SELECT public.f_abrir_comanda($1) AS data`, [id]);
    return rows[0]?.data || null;
  });
}

export async function cerrarComanda({ schema, id }) {
  return withTenantClient(schema, async (db) => {
    const { rows } = await db.query(`SELECT public.f_cerrar_comanda($1) AS data`, [id]);
    return rows[0]?.data || null;
  });
}

export async function restaurarComanda({ schema, id }) {
  return withTenantClient(schema, async (db) => {
    const { rows } = await db.query(`SELECT public.f_restaurar_comanda($1) AS data`, [id]);
    return rows[0]?.data || null;
  });
}

export async function eliminarComanda({ schema, id }) {
  return withTenantClient(schema, async (db) => {
    const { rows } = await db.query(`SELECT public.f_eliminar_comanda($1) AS data`, [id]);
    return rows[0]?.data || null;
  });
}

export async function patchComanda({ schema, id, payload }) {
  return withTenantClient(schema, async (db) => {
    const columns = await loadColumns(db, TABLE);
    const updatable = new Set(
      columns
        .filter(c =>
          !c.is_primary &&
          !c.is_identity &&
          !(String(c.column_default || '').startsWith('nextval('))
        )
        .map(c => c.column_name)
    );

    const sets = [];
    const params = [];
    let idx = 1;
    for (const [k, v] of Object.entries(payload || {})) {
      if (!VALID_IDENT.test(k)) continue;
      if (!updatable.has(k)) continue;
      sets.push(`${q(k)} = $${idx++}`);
      params.push(v);
    }
    if (!sets.length) return { error: 'Nada para actualizar' };

    const pks = await loadPrimaryKey(db, TABLE);
    if (pks.length !== 1) {
      const err = new Error('PK compuesta no soportada');
      err.http = { status: 400 };
      throw err;
    }
    params.push(id);

    const { rows } = await db.query(
      `UPDATE ${q(TABLE)} SET ${sets.join(', ')} WHERE ${q(pks[0])} = $${idx} RETURNING *`,
      params
    );
    return rows[0] || null;
  });
}

function q(ident) {
  return `"${String(ident).replace(/"/g, '""')}"`;
}