-- Detracciones por comprobante en tabla separada

CREATE TABLE IF NOT EXISTS facturacion_comprobante_detracciones (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    comprobante_id BIGINT UNSIGNED NOT NULL,
    catalogo_detraccion_id BIGINT UNSIGNED NULL,
    base DECIMAL(14,2) NOT NULL DEFAULT 0,
    monto DECIMAL(14,2) NOT NULL DEFAULT 0,
    created_by BIGINT UNSIGNED NULL,
    updated_by BIGINT UNSIGNED NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NULL,
    deleted_at DATETIME NULL,
    UNIQUE KEY uq_fact_det_comprobante (comprobante_id),
    KEY idx_fact_det_catalogo (catalogo_detraccion_id),
    CONSTRAINT fk_fact_det_comprobante FOREIGN KEY (comprobante_id) REFERENCES facturacion_comprobantes(id),
    CONSTRAINT fk_fact_det_catalogo FOREIGN KEY (catalogo_detraccion_id) REFERENCES catalogo_detracciones(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO facturacion_comprobante_detracciones
    (comprobante_id, catalogo_detraccion_id, base, monto, created_at)
SELECT
    c.id,
    c.detraccion_id,
    COALESCE(c.detraccion_base, c.total, 0),
    COALESCE(c.detraccion_monto, 0),
    NOW()
FROM facturacion_comprobantes c
LEFT JOIN facturacion_comprobante_detracciones fd ON fd.comprobante_id = c.id
WHERE fd.id IS NULL
  AND c.deleted_at IS NULL
  AND (c.detraccion_id IS NOT NULL OR COALESCE(c.detraccion_base, 0) <> 0 OR COALESCE(c.detraccion_monto, 0) <> 0);