SELECT 1; /* MIGRACION 015: Migrar transacciones existentes a tabla ventas | Fecha: 2026-02-02 | Plan: TABLA_VENTAS | Agrupa por (business_id, cliente, DATE(fecha_transaccion)), asigna id_venta a txs y comprobantes | Idempotente: NOT EXISTS + WHERE id_venta IS NULL */

INSERT INTO ventas (business_id, sucursal, id_cliente, cliente, numero_telefono, empleado, empleado_login_id, monto_total, tipo_pago, efectivo, fecha_venta, created_by_login_id, soft_delete, soft_delete_date)
SELECT
  sub.business_id,
  sub.sucursal,
  CASE WHEN EXISTS (SELECT 1 FROM clientes c WHERE c.id_cliente = sub.id_cliente) THEN sub.id_cliente ELSE NULL END,
  sub.cliente,
  sub.numero_telefono,
  sub.empleado,
  sub.empleado_login_id,
  sub.monto_total,
  sub.tipo_pago,
  sub.efectivo,
  sub.fecha_venta,
  sub.created_by_login_id,
  sub.is_all_soft_deleted,
  sub.soft_delete_date
FROM (
  SELECT
    t.business_id,
    (SELECT s.sucursal FROM transacciones s WHERE s.id_transaccion = MIN(t.id_transaccion)) AS sucursal,
    (array_agg(t.id_cliente ORDER BY t.id_transaccion))[1] AS id_cliente,
    COALESCE(NULLIF(t.cliente, ''), NULL) AS cliente,
    (array_agg(t.numero_telefono ORDER BY t.id_transaccion))[1] AS numero_telefono,
    (array_agg(t.empleado ORDER BY t.id_transaccion))[1] AS empleado,
    (array_agg(t.empleado_login_id ORDER BY t.id_transaccion))[1] AS empleado_login_id,
    COALESCE(SUM(t.cobrado), 0) AS monto_total,
    (array_agg(t.tipo_pago ORDER BY t.id_transaccion))[1] AS tipo_pago,
    (array_agg(t.efectivo ORDER BY t.id_transaccion))[1] AS efectivo,
    MIN(t.fecha_transaccion) AS fecha_venta,
    COALESCE((array_agg(t.empleado_login_id ORDER BY t.id_transaccion))[1], 9) AS created_by_login_id,
    bool_and(t.soft_delete) AS is_all_soft_deleted,
    CASE WHEN bool_and(t.soft_delete) THEN MAX(t.soft_delete_date) ELSE NULL END AS soft_delete_date
  FROM transacciones t
  GROUP BY t.business_id, COALESCE(NULLIF(t.cliente, ''), NULL), DATE(t.fecha_transaccion)
) sub
WHERE NOT EXISTS (
  SELECT 1 FROM ventas v
  WHERE v.business_id = sub.business_id
    AND v.cliente IS NOT DISTINCT FROM sub.cliente
    AND DATE(v.fecha_venta) = DATE(sub.fecha_venta)
);

UPDATE transacciones t
SET id_venta = v.id_venta
FROM ventas v
WHERE t.id_venta IS NULL
  AND v.business_id = t.business_id
  AND v.cliente IS NOT DISTINCT FROM COALESCE(NULLIF(t.cliente, ''), NULL)
  AND DATE(v.fecha_venta) = DATE(t.fecha_transaccion);

UPDATE comprobantes_transacciones c
SET id_venta = t.id_venta
FROM transacciones t
WHERE c.id_transaccion = t.id_transaccion
  AND t.id_venta IS NOT NULL
  AND c.id_venta IS NULL
