-- =============================================================================
-- ODONTOGENESIS — Migrar pagos históricos → sale_note_payments
-- Ejecutar DESPUÉS de odontogenesis_dentalbuysv_dh_update.sql
-- (cuando la tabla sale_note_payments ya exista).
--
-- Origen de los pagos procesados hasta ahora:
--   • Tabla `boxes` (campo sale_note_id) — caja / afectar_caja al facturar
--   • Tabla `sale_notes` marcadas paid=1 sin registro en caja
--
-- NO borra ni modifica `boxes`. Solo copia a sale_note_payments.
-- Idempotente: no duplica si ya existe un pago para esa factura.
-- =============================================================================

-- ---------------------------------------------------------------------------
-- 1) Desde CAJA (boxes): un pago por factura (el movimiento más reciente)
-- ---------------------------------------------------------------------------
INSERT INTO `sale_note_payments` (
  `sale_note_id`,
  `date_of_payment`,
  `payment_method_type_id`,
  `has_card`,
  `card_brand_id`,
  `reference`,
  `change`,
  `payment`
)
SELECT
  b.`sale_note_id`,
  COALESCE(b.`date`, sn.`date_of_issue`) AS `date_of_payment`,
  COALESCE(
    (
      SELECT pmt.`id`
      FROM `payment_method_types` pmt
      WHERE pmt.`id` = TRIM(b.`method`)
         OR LOWER(TRIM(pmt.`description`)) = LOWER(TRIM(b.`method`))
      LIMIT 1
    ),
    CASE
      WHEN LOWER(TRIM(b.`method`)) LIKE '%tarjeta%' OR LOWER(TRIM(b.`method`)) LIKE '%card%' THEN '03'
      WHEN LOWER(TRIM(b.`method`)) LIKE '%transfer%' THEN '04'
      ELSE '01'
    END
  ) AS `payment_method_type_id`,
  CASE
    WHEN LOWER(TRIM(b.`method`)) LIKE '%tarjeta%' OR LOWER(TRIM(b.`method`)) LIKE '%card%' THEN 1
    ELSE 0
  END AS `has_card`,
  NULL AS `card_brand_id`,
  NULL AS `reference`,
  NULL AS `change`,
  b.`amount` AS `payment`
FROM `boxes` b
INNER JOIN `sale_notes` sn ON sn.`id` = b.`sale_note_id`
INNER JOIN (
  SELECT `sale_note_id`, MAX(`id`) AS `box_id`
  FROM `boxes`
  WHERE `sale_note_id` IS NOT NULL
  GROUP BY `sale_note_id`
) latest ON latest.`box_id` = b.`id`
WHERE b.`sale_note_id` IS NOT NULL
  AND (b.`state` IS NULL OR b.`state` <> '0')
  AND sn.`state_type_id` <> '11'
  AND NOT EXISTS (
    SELECT 1
    FROM `sale_note_payments` snp
    WHERE snp.`sale_note_id` = b.`sale_note_id`
  );

-- ---------------------------------------------------------------------------
-- 2) Facturas pagadas (paid=1) sin movimiento en caja → Efectivo por total
-- ---------------------------------------------------------------------------
INSERT INTO `sale_note_payments` (
  `sale_note_id`,
  `date_of_payment`,
  `payment_method_type_id`,
  `has_card`,
  `card_brand_id`,
  `reference`,
  `change`,
  `payment`
)
SELECT
  sn.`id`,
  sn.`date_of_issue`,
  '01',
  0,
  NULL,
  NULL,
  NULL,
  sn.`total`
FROM `sale_notes` sn
WHERE sn.`paid` = 1
  AND sn.`state_type_id` <> '11'
  AND NOT EXISTS (
    SELECT 1 FROM `sale_note_payments` snp WHERE snp.`sale_note_id` = sn.`id`
  )
  AND NOT EXISTS (
    SELECT 1 FROM `boxes` b WHERE b.`sale_note_id` = sn.`id`
  );

-- ---------------------------------------------------------------------------
-- VERIFICACIÓN
-- ---------------------------------------------------------------------------
SELECT 'Facturas con movimiento en caja' AS concepto,
       COUNT(DISTINCT `sale_note_id`) AS cantidad
FROM `boxes`
WHERE `sale_note_id` IS NOT NULL;

SELECT 'Pagos migrados en sale_note_payments' AS concepto,
       COUNT(*) AS cantidad
FROM `sale_note_payments`;

SELECT 'Facturas pagadas sin fila en sale_note_payments' AS concepto,
       COUNT(*) AS cantidad
FROM `sale_notes` sn
WHERE sn.`paid` = 1
  AND sn.`state_type_id` <> '11'
  AND NOT EXISTS (
    SELECT 1 FROM `sale_note_payments` snp WHERE snp.`sale_note_id` = sn.`id`
  );
