-- ICYLAB OS v8.6.2
-- Backfill inmediato de tasas históricas para exportaciones existentes y
-- endurecimiento del proveedor Findic. Ejecutar una sola vez después de v8.6/v8.6.1.

START TRANSACTION;

-- Tasas oficiales históricas de respaldo. Se registran como BCCH/SII y no se
-- atribuyen falsamente a una consulta remota de Findic. El servicio v8.6.2 las
-- acepta como caché validada y continúa usando Findic para nuevas fechas.
INSERT INTO finance_exchange_rates
(currency_code, indicator_code, requested_date, rate_date, rate_to_clp, provider,
 source_name, source_url, quality_status, response_hash, raw_payload, retrieved_at)
VALUES
('USD','dolar','2025-12-12','2025-12-12',916.900000,'BCCH_SII_SEED',
 'Banco Central de Chile / respaldo SII',
 'https://www.sii.cl/valores_y_fechas/dolar/dolar2025.htm',
 'validated',SHA2('USD|2025-12-12|916.90|BCCH_SII_SEED',256),
 '{"source":"BCCH/SII","currency":"USD","date":"2025-12-12","value":916.90,"reason":"production_backfill"}',NOW()),
('USD','dolar','2026-04-27','2026-04-27',895.070000,'BCCH_SII_SEED',
 'Banco Central de Chile / respaldo SII',
 'https://www.sii.cl/valores_y_fechas/dolar/dolar2026.htm',
 'validated',SHA2('USD|2026-04-27|895.07|BCCH_SII_SEED',256),
 '{"source":"BCCH/SII","currency":"USD","date":"2026-04-27","value":895.07,"reason":"production_backfill"}',NOW()),
('USD','dolar','2026-05-27','2026-05-27',894.670000,'BCCH_SII_SEED',
 'Banco Central de Chile / respaldo SII',
 'https://www.sii.cl/valores_y_fechas/dolar/dolar2026.htm',
 'validated',SHA2('USD|2026-05-27|894.67|BCCH_SII_SEED',256),
 '{"source":"BCCH/SII","currency":"USD","date":"2026-05-27","value":894.67,"reason":"production_backfill"}',NOW()),
('USD','dolar','2026-06-02','2026-06-02',892.910000,'BCCH_SII_SEED',
 'Banco Central de Chile / respaldo SII',
 'https://www.sii.cl/valores_y_fechas/dolar/dolar2026.htm',
 'validated',SHA2('USD|2026-06-02|892.91|BCCH_SII_SEED',256),
 '{"source":"BCCH/SII","currency":"USD","date":"2026-06-02","value":892.91,"reason":"production_backfill"}',NOW())
ON DUPLICATE KEY UPDATE
 requested_date=VALUES(requested_date),
 rate_to_clp=VALUES(rate_to_clp),
 source_name=VALUES(source_name),
 source_url=VALUES(source_url),
 quality_status='validated',
 response_hash=VALUES(response_hash),
 raw_payload=VALUES(raw_payload),
 retrieved_at=NOW(),
 updated_at=CURRENT_TIMESTAMP;

-- Aplica el caché validado a documentos emitidos en moneda extranjera.
UPDATE facto_documents d
INNER JOIN finance_exchange_rates fx
        ON fx.currency_code=UPPER(d.currency_code)
       AND fx.rate_date=d.issue_date
       AND fx.provider IN ('FINDIC_BCCH','BCCH_SII_SEED')
       AND fx.quality_status IN ('validated','provisional')
SET d.exchange_rate_value=fx.rate_to_clp,
    d.exchange_rate_date=fx.rate_date,
    d.exchange_rate_source=fx.provider,
    d.exchange_rate_status=fx.quality_status,
    d.net_amount_clp=ROUND(d.net_amount*fx.rate_to_clp,2),
    d.taxes_amount_clp=ROUND(d.taxes_amount*fx.rate_to_clp,2),
    d.total_amount_clp=ROUND(d.total_amount*fx.rate_to_clp,2),
    d.updated_at=CURRENT_TIMESTAMP
WHERE d.environment='production'
  AND d.received_issued_flag=1
  AND d.document_status=1
  AND CAST(COALESCE(d.document_type_taxbureau,0) AS UNSIGNED) IN (110,111,112)
  AND UPPER(COALESCE(NULLIF(d.currency_code,''),'CLP'))<>'CLP'
  AND (COALESCE(d.exchange_rate_value,0)<=0 OR d.total_amount_clp IS NULL);

-- Propaga vínculo, moneda y tasa a las cuentas por cobrar.
UPDATE billing_collections bc
INNER JOIN facto_documents d
        ON BINARY d.environment=BINARY bc.environment
       AND BINARY d.document_id=BINARY bc.facto_document_id
SET bc.customer_id=COALESCE(bc.customer_id,d.crm_customer_id),
    bc.currency_code=d.currency_code,
    bc.currency_id=COALESCE(bc.currency_id,d.currency_id),
    bc.exchange_rate_value=d.exchange_rate_value,
    bc.exchange_rate_date=d.exchange_rate_date,
    bc.exchange_rate_source=d.exchange_rate_source,
    bc.exchange_rate_status=d.exchange_rate_status,
    bc.foreign_total_amount=d.total_amount,
    bc.total_amount=d.total_amount_clp,
    bc.updated_at=CURRENT_TIMESTAMP
WHERE bc.direction='receivable'
  AND d.environment='production'
  AND d.received_issued_flag=1
  AND d.document_status=1
  AND CAST(COALESCE(d.document_type_taxbureau,0) AS UNSIGNED) IN (110,111,112)
  AND UPPER(COALESCE(NULLIF(d.currency_code,''),'CLP'))<>'CLP'
  AND COALESCE(d.exchange_rate_value,0)>0;

-- Normaliza pagos históricos que originalmente fueron guardados en la moneda
-- del documento dentro de billing_payments.amount.
DROP TEMPORARY TABLE IF EXISTS tmp_icylab_v862_fx_payments;
CREATE TEMPORARY TABLE tmp_icylab_v862_fx_payments AS
SELECT
    p.id,
    p.amount AS old_amount,
    c.currency_code,
    c.exchange_rate_value,
    c.exchange_rate_date,
    c.exchange_rate_source,
    c.exchange_rate_status,
    c.foreign_total_amount,
    CASE
        WHEN COALESCE(c.exchange_rate_value,0)>1
         AND c.foreign_total_amount IS NOT NULL
         AND p.amount<=c.foreign_total_amount+GREATEST(0.05,c.foreign_total_amount*0.01)
        THEN 1 ELSE 0
    END AS amount_was_foreign
FROM billing_payments p
INNER JOIN billing_collections c ON c.id=p.collection_id
WHERE p.direction='received'
  AND UPPER(COALESCE(NULLIF(c.currency_code,''),'CLP'))<>'CLP'
  AND COALESCE(c.exchange_rate_value,0)>0;

UPDATE billing_payments p
INNER JOIN tmp_icylab_v862_fx_payments x ON x.id=p.id
SET p.currency_code=x.currency_code,
    p.exchange_rate_value=x.exchange_rate_value,
    p.exchange_rate_date=x.exchange_rate_date,
    p.exchange_rate_source=x.exchange_rate_source,
    p.exchange_rate_status=x.exchange_rate_status,
    p.foreign_amount=CASE
        WHEN x.amount_was_foreign=1 THEN x.old_amount
        ELSE COALESCE(p.foreign_amount,ROUND(x.old_amount/NULLIF(x.exchange_rate_value,0),4))
    END,
    p.amount=CASE
        WHEN x.amount_was_foreign=1 THEN ROUND(x.old_amount*x.exchange_rate_value,2)
        ELSE x.old_amount
    END;
DROP TEMPORARY TABLE IF EXISTS tmp_icylab_v862_fx_payments;

-- Reconcilia saldos en CLP. Es idempotente: solo multiplica paid_amount cuando
-- todavía conserva una magnitud compatible con la moneda extranjera.
DROP TEMPORARY TABLE IF EXISTS tmp_icylab_v862_collection_reconcile;
CREATE TEMPORARY TABLE tmp_icylab_v862_collection_reconcile AS
SELECT
    x.collection_id,
    x.total_clp,
    x.paid_clp,
    GREATEST(x.total_clp-x.paid_clp,0) AS balance_clp,
    x.last_payment_date,
    CASE WHEN x.exchange_rate_value>0 THEN ROUND(x.paid_clp/x.exchange_rate_value,4) ELSE NULL END AS foreign_paid_amount,
    CASE WHEN x.exchange_rate_value>0 THEN GREATEST(x.foreign_total_amount-ROUND(x.paid_clp/x.exchange_rate_value,4),0) ELSE NULL END AS foreign_balance_amount,
    CASE
        WHEN x.existing_status IN ('anulado','incobrable') THEN x.existing_status
        WHEN GREATEST(x.total_clp-x.paid_clp,0)<=0.5 THEN 'pagado'
        WHEN x.due_date IS NOT NULL AND x.due_date<CURRENT_DATE THEN 'atrasado'
        WHEN x.paid_clp>0.005 THEN 'parcial'
        ELSE 'por_cobrar'
    END AS new_status
FROM (
    SELECT
        bc.id AS collection_id,
        d.total_amount_clp AS total_clp,
        CASE
            WHEN pay.paid_clp IS NOT NULL THEN pay.paid_clp
            WHEN COALESCE(bc.paid_amount,0)<=d.total_amount+GREATEST(0.05,d.total_amount*0.01)
            THEN ROUND(COALESCE(bc.paid_amount,0)*d.exchange_rate_value,2)
            ELSE COALESCE(bc.paid_amount,0)
        END AS paid_clp,
        COALESCE(pay.last_payment_date,bc.last_payment_date) AS last_payment_date,
        d.exchange_rate_value,
        d.total_amount AS foreign_total_amount,
        bc.collection_status AS existing_status,
        bc.due_date
    FROM billing_collections bc
    INNER JOIN facto_documents d
            ON BINARY d.environment=BINARY bc.environment
           AND BINARY d.document_id=BINARY bc.facto_document_id
    LEFT JOIN (
        SELECT collection_id,SUM(amount) AS paid_clp,MAX(payment_date) AS last_payment_date
        FROM billing_payments
        WHERE direction='received'
        GROUP BY collection_id
    ) pay ON pay.collection_id=bc.id
    WHERE bc.direction='receivable'
      AND d.environment='production'
      AND d.received_issued_flag=1
      AND d.document_status=1
      AND CAST(COALESCE(d.document_type_taxbureau,0) AS UNSIGNED) IN (110,111,112)
      AND UPPER(COALESCE(NULLIF(d.currency_code,''),'CLP'))<>'CLP'
      AND COALESCE(d.exchange_rate_value,0)>0
) x;

UPDATE billing_collections bc
INNER JOIN tmp_icylab_v862_collection_reconcile x ON x.collection_id=bc.id
SET bc.total_amount=x.total_clp,
    bc.paid_amount=x.paid_clp,
    bc.balance_amount=x.balance_clp,
    bc.last_payment_date=x.last_payment_date,
    bc.foreign_paid_amount=x.foreign_paid_amount,
    bc.foreign_balance_amount=x.foreign_balance_amount,
    bc.collection_status=x.new_status,
    bc.updated_at=CURRENT_TIMESTAMP;
DROP TEMPORARY TABLE IF EXISTS tmp_icylab_v862_collection_reconcile;

-- Propaga la tasa a cotizaciones asociadas cuando corresponda.
UPDATE crm_quotes q
INNER JOIN facto_documents d ON d.crm_quote_id=q.id
SET q.billing_currency_code=d.currency_code,
    q.billing_exchange_rate_value=d.exchange_rate_value,
    q.billing_exchange_rate_date=d.exchange_rate_date,
    q.billing_exchange_rate_source=d.exchange_rate_source,
    q.billing_exchange_rate_status=d.exchange_rate_status
WHERE d.environment='production'
  AND d.received_issued_flag=1
  AND CAST(COALESCE(d.document_type_taxbureau,0) AS UNSIGNED) IN (110,111,112)
  AND COALESCE(d.exchange_rate_value,0)>0;

COMMIT;

SELECT
    d.document_number,
    d.issue_date,
    d.receiver_legal_name,
    d.crm_customer_id,
    d.currency_code,
    d.exchange_rate_value,
    d.exchange_rate_date,
    d.exchange_rate_source,
    d.total_amount,
    d.total_amount_clp
FROM facto_documents d
WHERE d.environment='production'
  AND d.received_issued_flag=1
  AND d.document_status=1
  AND CAST(COALESCE(d.document_type_taxbureau,0) AS UNSIGNED) IN (110,111,112)
ORDER BY d.issue_date DESC,d.id DESC;

SELECT
    COUNT(*) AS export_documents_total,
    SUM(CASE WHEN UPPER(COALESCE(NULLIF(currency_code,''),'CLP'))='CLP' OR (COALESCE(exchange_rate_value,0)>0 AND total_amount_clp IS NOT NULL) THEN 1 ELSE 0 END) AS export_documents_fx_resolved,
    SUM(CASE WHEN UPPER(COALESCE(NULLIF(currency_code,''),'CLP'))<>'CLP' AND (COALESCE(exchange_rate_value,0)<=0 OR total_amount_clp IS NULL) THEN 1 ELSE 0 END) AS export_documents_fx_pending
FROM facto_documents
WHERE environment='production'
  AND received_issued_flag=1
  AND document_status=1
  AND CAST(COALESCE(document_type_taxbureau,0) AS UNSIGNED) IN (110,111,112);
