-- ICYLAB OS v8.6 - Tipos de cambio históricos BCCh vía Findic
-- Ejecutar una sola vez en la base de producción. Es idempotente.

SET @db_name := DATABASE();

CREATE TABLE IF NOT EXISTS finance_exchange_rates (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    currency_code VARCHAR(10) NOT NULL,
    indicator_code VARCHAR(40) NOT NULL,
    requested_date DATE DEFAULT NULL,
    rate_date DATE NOT NULL,
    rate_to_clp DECIMAL(18,6) NOT NULL,
    provider VARCHAR(40) NOT NULL DEFAULT 'FINDIC_BCCH',
    source_name VARCHAR(160) NOT NULL DEFAULT 'Banco Central de Chile vía Findic',
    source_url VARCHAR(500) DEFAULT NULL,
    quality_status ENUM('validated','provisional','manual','missing','failed') NOT NULL DEFAULT 'validated',
    response_hash CHAR(64) DEFAULT NULL,
    raw_payload LONGTEXT DEFAULT NULL,
    retrieved_at DATETIME NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_fx_currency_date_provider (currency_code, rate_date, provider),
    KEY idx_fx_requested_date (requested_date),
    KEY idx_fx_rate_date (rate_date),
    KEY idx_fx_status (quality_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DROP PROCEDURE IF EXISTS icylab_v86_add_column;
DELIMITER $$
CREATE PROCEDURE icylab_v86_add_column(
    IN p_table VARCHAR(64),
    IN p_column VARCHAR(64),
    IN p_definition TEXT
)
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = DATABASE()
          AND TABLE_NAME = p_table
          AND COLUMN_NAME = p_column
    ) THEN
        SET @ddl = CONCAT('ALTER TABLE `', p_table, '` ADD COLUMN `', p_column, '` ', p_definition);
        PREPARE stmt FROM @ddl;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END IF;
END$$
DELIMITER ;

CALL icylab_v86_add_column('facto_documents', 'exchange_rate_date', "DATE NULL AFTER exchange_rate_value");
CALL icylab_v86_add_column('facto_documents', 'exchange_rate_source', "VARCHAR(40) NULL AFTER exchange_rate_date");
CALL icylab_v86_add_column('facto_documents', 'exchange_rate_status', "VARCHAR(20) NULL AFTER exchange_rate_source");
CALL icylab_v86_add_column('facto_documents', 'net_amount_clp', "DECIMAL(18,2) NULL AFTER net_amount");
CALL icylab_v86_add_column('facto_documents', 'taxes_amount_clp', "DECIMAL(18,2) NULL AFTER taxes_amount");
CALL icylab_v86_add_column('facto_documents', 'total_amount_clp', "DECIMAL(18,2) NULL AFTER total_amount");

CALL icylab_v86_add_column('billing_collections', 'exchange_rate_date', "DATE NULL AFTER exchange_rate_value");
CALL icylab_v86_add_column('billing_collections', 'exchange_rate_source', "VARCHAR(40) NULL AFTER exchange_rate_date");
CALL icylab_v86_add_column('billing_collections', 'exchange_rate_status', "VARCHAR(20) NULL AFTER exchange_rate_source");

CALL icylab_v86_add_column('billing_payments', 'currency_code', "VARCHAR(10) NULL AFTER amount");
CALL icylab_v86_add_column('billing_payments', 'exchange_rate_value', "DECIMAL(18,6) NULL AFTER currency_code");
CALL icylab_v86_add_column('billing_payments', 'exchange_rate_date', "DATE NULL AFTER exchange_rate_value");
CALL icylab_v86_add_column('billing_payments', 'exchange_rate_source', "VARCHAR(40) NULL AFTER exchange_rate_date");
CALL icylab_v86_add_column('billing_payments', 'exchange_rate_status', "VARCHAR(20) NULL AFTER exchange_rate_source");
CALL icylab_v86_add_column('billing_payments', 'foreign_amount', "DECIMAL(18,4) NULL AFTER exchange_rate_status");

CALL icylab_v86_add_column('billing_export_operations', 'exchange_rate_date', "DATE NULL AFTER exchange_rate_value");
CALL icylab_v86_add_column('billing_export_operations', 'exchange_rate_source', "VARCHAR(40) NULL AFTER exchange_rate_date");
CALL icylab_v86_add_column('billing_export_operations', 'exchange_rate_status', "VARCHAR(20) NULL AFTER exchange_rate_source");

CALL icylab_v86_add_column('crm_quotes', 'billing_exchange_rate_date', "DATE NULL AFTER billing_exchange_rate_value");
CALL icylab_v86_add_column('crm_quotes', 'billing_exchange_rate_source', "VARCHAR(40) NULL AFTER billing_exchange_rate_date");
CALL icylab_v86_add_column('crm_quotes', 'billing_exchange_rate_status', "VARCHAR(20) NULL AFTER billing_exchange_rate_source");

DROP PROCEDURE IF EXISTS icylab_v86_add_column;

-- Completa el código ISO desde el catálogo FACTO antes de asumir CLP.
UPDATE facto_documents fd
LEFT JOIN billing_currencies cur
       ON cur.facto_currency_id = CAST(fd.currency_id AS UNSIGNED)
SET fd.currency_code = UPPER(cur.currency_code)
WHERE (fd.currency_code IS NULL OR fd.currency_code='')
  AND cur.currency_code IS NOT NULL
  AND cur.currency_code<>'';

-- Normalización inmediata de documentos CLP.
UPDATE facto_documents
SET currency_code = COALESCE(NULLIF(currency_code,''), 'CLP'),
    exchange_rate_value = 1.000000,
    exchange_rate_date = COALESCE(exchange_rate_date, issue_date),
    exchange_rate_source = COALESCE(NULLIF(exchange_rate_source,''), 'CLP'),
    exchange_rate_status = 'validated',
    net_amount_clp = net_amount,
    taxes_amount_clp = taxes_amount,
    total_amount_clp = total_amount
WHERE COALESCE(NULLIF(currency_code,''), 'CLP') = 'CLP';

-- Preserva tasas ya existentes en FACTO y calcula equivalentes CLP.
UPDATE facto_documents
SET exchange_rate_date = COALESCE(exchange_rate_date, issue_date),
    exchange_rate_source = COALESCE(NULLIF(exchange_rate_source,''), 'FACTO_DTE'),
    exchange_rate_status = COALESCE(NULLIF(exchange_rate_status,''), 'validated'),
    net_amount_clp = ROUND(net_amount * exchange_rate_value, 2),
    taxes_amount_clp = ROUND(taxes_amount * exchange_rate_value, 2),
    total_amount_clp = ROUND(total_amount * exchange_rate_value, 2)
WHERE UPPER(COALESCE(NULLIF(currency_code,''), 'CLP')) <> 'CLP'
  AND COALESCE(exchange_rate_value,0) > 0;

-- Sincroniza metadatos y equivalentes de cobranza cuando ya existe una tasa.
UPDATE billing_collections bc
INNER JOIN facto_documents fd
        ON BINARY fd.environment = BINARY bc.environment
       AND BINARY fd.document_id = BINARY bc.facto_document_id
SET bc.currency_code = COALESCE(NULLIF(fd.currency_code,''), bc.currency_code, 'CLP'),
    bc.exchange_rate_value = COALESCE(fd.exchange_rate_value, bc.exchange_rate_value, 1),
    bc.exchange_rate_date = COALESCE(fd.exchange_rate_date, bc.exchange_rate_date, fd.issue_date),
    bc.exchange_rate_source = COALESCE(NULLIF(fd.exchange_rate_source,''), bc.exchange_rate_source),
    bc.exchange_rate_status = COALESCE(NULLIF(fd.exchange_rate_status,''), bc.exchange_rate_status),
    bc.total_amount = COALESCE(fd.total_amount_clp, bc.total_amount),
    bc.foreign_total_amount = CASE
        WHEN UPPER(COALESCE(NULLIF(fd.currency_code,''),'CLP')) <> 'CLP' THEN fd.total_amount
        ELSE NULL
    END
WHERE bc.direction = 'receivable';

-- Normaliza pagos históricos de exportación. Antes de v8.6 los pagos de esas
-- CxC podían haberse almacenado en la moneda del documento dentro de amount.
DROP TEMPORARY TABLE IF EXISTS tmp_icylab_v86_fx_payments;
CREATE TEMPORARY TABLE tmp_icylab_v86_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_v86_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 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_v86_fx_payments;

-- Reconcilia saldos en CLP desde los pagos ya normalizados. Se calculan
-- todos los valores en una tabla temporal para evitar depender del orden de
-- evaluación de las asignaciones SET en MySQL/MariaDB.
DROP TEMPORARY TABLE IF EXISTS tmp_icylab_v86_collection_reconcile;
CREATE TEMPORARY TABLE tmp_icylab_v86_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.is_foreign=1 AND 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.is_foreign=1 AND 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,
        COALESCE(fd.total_amount_clp,bc.total_amount,0) AS total_clp,
        CASE
            WHEN pay.paid_clp IS NOT NULL THEN pay.paid_clp
            WHEN UPPER(COALESCE(NULLIF(fd.currency_code,''),'CLP')) <> 'CLP'
             AND COALESCE(fd.exchange_rate_value,0)>1
             AND COALESCE(bc.paid_amount,0) <= COALESCE(fd.total_amount,0) + GREATEST(0.05,COALESCE(fd.total_amount,0)*0.01)
            THEN ROUND(COALESCE(bc.paid_amount,0)*fd.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,
        CASE WHEN UPPER(COALESCE(NULLIF(fd.currency_code,''),'CLP')) <> 'CLP' THEN 1 ELSE 0 END AS is_foreign,
        COALESCE(fd.exchange_rate_value,bc.exchange_rate_value,0) AS exchange_rate_value,
        COALESCE(bc.foreign_total_amount,CASE WHEN UPPER(COALESCE(NULLIF(fd.currency_code,''),'CLP')) <> 'CLP' THEN fd.total_amount ELSE NULL END) AS foreign_total_amount,
        bc.collection_status AS existing_status,
        bc.due_date
    FROM billing_collections bc
    INNER JOIN facto_documents fd
            ON BINARY fd.environment = BINARY bc.environment
           AND BINARY fd.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'
) x;

UPDATE billing_collections bc
INNER JOIN tmp_icylab_v86_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_v86_collection_reconcile;

-- Índices de consulta. MariaDB no soporta CREATE INDEX IF NOT EXISTS en todas las versiones.
SET @idx_exists := (
    SELECT COUNT(*) FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='facto_documents' AND INDEX_NAME='idx_facto_fx_pending'
);
SET @ddl := IF(@idx_exists=0,
    'CREATE INDEX idx_facto_fx_pending ON facto_documents (received_issued_flag, document_status, currency_code, exchange_rate_status, issue_date)',
    'SELECT 1');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SELECT
    COUNT(*) AS foreign_documents_total,
    SUM(CASE WHEN COALESCE(exchange_rate_value,0)>0 AND total_amount_clp IS NOT NULL THEN 1 ELSE 0 END) AS foreign_documents_resolved,
    SUM(CASE WHEN COALESCE(exchange_rate_value,0)<=0 OR total_amount_clp IS NULL THEN 1 ELSE 0 END) AS foreign_documents_pending
FROM facto_documents
WHERE received_issued_flag=1
  AND document_status=1
  AND UPPER(COALESCE(NULLIF(currency_code,''),'CLP')) <> 'CLP';

SELECT
    COUNT(*) AS foreign_payment_rows,
    SUM(CASE WHEN foreign_amount IS NOT NULL AND COALESCE(exchange_rate_value,0)>0 THEN 1 ELSE 0 END) AS foreign_payment_rows_normalized
FROM billing_payments
WHERE direction='received'
  AND UPPER(COALESCE(NULLIF(currency_code,''),'CLP')) <> 'CLP';
