-- ICYLAB OS v9.0.6
-- Conciliacion segura DTE emitido -> cotizacion -> oportunidad -> cobranza.
-- Caso verificado: FACTO document_id 877 / folio 260 / COT-2026-1493.
-- Esta migracion NO emite DTE, NO consume folios y NO crea pagos.

CREATE TABLE IF NOT EXISTS `billing_link_audit_logs` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `environment` enum('sandbox','production') NOT NULL,
  `facto_document_id` varchar(100) NOT NULL,
  `quote_id` int(11) DEFAULT NULL,
  `opportunity_id` int(11) DEFAULT NULL,
  `action_key` varchar(80) NOT NULL,
  `strategy` varchar(100) NOT NULL,
  `before_json` longtext DEFAULT NULL,
  `after_json` longtext DEFAULT NULL,
  `actor_user_id` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_billing_link_document` (`environment`,`facto_document_id`),
  KEY `idx_billing_link_quote` (`quote_id`),
  KEY `idx_billing_link_opportunity` (`opportunity_id`),
  KEY `idx_billing_link_action` (`action_key`,`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

START TRANSACTION;

DROP TEMPORARY TABLE IF EXISTS `tmp_icylab_v906_verified_link`;
CREATE TEMPORARY TABLE `tmp_icylab_v906_verified_link` AS
SELECT
    d.id AS local_document_id,
    d.environment,
    d.document_id,
    d.document_number,
    d.issue_date,
    d.document_type_id,
    d.crm_customer_id AS document_customer_id,
    d.finance_center_id,
    d.currency_code,
    d.exchange_rate_value,
    d.exchange_rate_date,
    d.exchange_rate_source,
    d.exchange_rate_status,
    d.receiver_country_id,
    d.sale_modality,
    d.sale_clause,
    d.pdf_file_path,
    d.xml_file_path,
    d.raw_payload,
    d.net_amount,
    d.taxes_amount,
    d.total_amount,
    q.id AS quote_id,
    q.quote_number,
    q.opportunity_id,
    q.customer_id,
    bc.id AS collection_id,
    bc.due_date,
    bc.payment_terms
FROM facto_documents d
INNER JOIN crm_quotes q ON q.id=306 AND q.quote_number='COT-2026-1493'
INNER JOIN crm_customers c ON c.id=q.customer_id
INNER JOIN crm_opportunities o ON o.id=q.opportunity_id
INNER JOIN billing_collections bc
    ON bc.environment=d.environment
   AND bc.direction='receivable'
   AND BINARY bc.facto_document_id=BINARY d.document_id
WHERE d.environment='production'
  AND BINARY d.document_id=BINARY '877'
  AND d.document_number='260'
  AND d.received_issued_flag=1
  AND d.document_status IN (1,2,3)
  AND d.document_type_id=2
  AND COALESCE(d.payment_status,'')<>'anulado'
  AND q.customer_id=70
  AND q.opportunity_id=722
  AND o.customer_id=q.customer_id
  AND q.deleted_at IS NULL
  AND o.deleted_at IS NULL
  AND q.status IN ('Aceptada','Facturada','Esperando pago','Pagada')
  AND (q.facto_document_id IS NULL OR q.facto_document_id='' OR BINARY q.facto_document_id=BINARY d.document_id)
  AND (d.crm_quote_id IS NULL OR d.crm_quote_id=q.id)
  AND (d.crm_opportunity_id IS NULL OR d.crm_opportunity_id=q.opportunity_id)
  AND (o.facto_document_id IS NULL OR o.facto_document_id='' OR BINARY o.facto_document_id=BINARY d.document_id)
  AND (bc.quote_id IS NULL OR bc.quote_id=q.id)
  AND (bc.opportunity_id IS NULL OR bc.opportunity_id=q.opportunity_id)
  AND REPLACE(REPLACE(REPLACE(UPPER(d.receiver_tax_id_code),'.',''),'-',''),' ','')=
      REPLACE(REPLACE(REPLACE(UPPER(c.customer_tax_id),'.',''),'-',''),' ','')
  AND d.issue_date>=DATE(q.created_at)
  AND ABS(COALESCE(d.net_amount_clp,d.net_amount)-((q.subtotal)-COALESCE(q.discount_amount,0)))<=1
  AND ABS(COALESCE(d.taxes_amount_clp,d.taxes_amount)-COALESCE(q.iva,0))<=1
  AND ABS(COALESCE(d.total_amount_clp,d.total_amount)-q.total)<=1
  AND ABS(bc.total_amount-d.total_amount)<=1
  AND ABS(COALESCE(bc.paid_amount,0))<=1
  AND ABS(bc.balance_amount-bc.total_amount)<=1
  AND bc.collection_status IN ('por_cobrar','atrasado')
  AND NOT EXISTS (
      SELECT 1 FROM billing_payments bp
      WHERE bp.environment=d.environment
        AND bp.direction='received'
        AND BINARY bp.facto_document_id=BINARY d.document_id
        AND COALESCE(bp.is_voided,0)=0
  )
  AND NOT EXISTS (
      SELECT 1 FROM crm_documents cd
      WHERE BINARY cd.facto_document_id=BINARY d.document_id
        AND ((cd.quote_id IS NOT NULL AND cd.quote_id<>q.id)
          OR (cd.opportunity_id IS NOT NULL AND cd.opportunity_id<>q.opportunity_id))
  );

SET @v906_verified_count := (SELECT COUNT(*) FROM tmp_icylab_v906_verified_link);

-- Guard atomico: si al momento de importar no existe exactamente una
-- coincidencia, vacia el conjunto de trabajo y todas las escrituras siguientes
-- quedan sin filas objetivo. El SELECT final conserva el resultado BLOCKED.
DELETE FROM tmp_icylab_v906_verified_link WHERE @v906_verified_count<>1;

INSERT INTO billing_link_audit_logs
    (environment,facto_document_id,quote_id,opportunity_id,action_key,strategy,before_json,after_json,actor_user_id)
SELECT
    v.environment,
    v.document_id,
    v.quote_id,
    v.opportunity_id,
    'link_verified',
    'migration_exact_identity_v9_0_6',
    JSON_OBJECT(
        'document_quote_id',d.crm_quote_id,
        'document_opportunity_id',d.crm_opportunity_id,
        'quote_facto_document_id',q.facto_document_id,
        'quote_status',q.status,
        'opportunity_stage',o.stage,
        'opportunity_payment_status',o.payment_status,
        'collection_quote_id',bc.quote_id,
        'collection_opportunity_id',bc.opportunity_id
    ),
    JSON_OBJECT(
        'document_id',v.document_id,
        'quote_id',v.quote_id,
        'opportunity_id',v.opportunity_id,
        'payment_status','por_cobrar',
        'new_document_created',FALSE,
        'payment_created',FALSE
    ),
    1
FROM tmp_icylab_v906_verified_link v
INNER JOIN facto_documents d ON d.id=v.local_document_id
INNER JOIN crm_quotes q ON q.id=v.quote_id
INNER JOIN crm_opportunities o ON o.id=v.opportunity_id
INNER JOIN billing_collections bc ON bc.id=v.collection_id
WHERE NOT EXISTS (
    SELECT 1 FROM billing_link_audit_logs a
    WHERE a.environment=v.environment
      AND BINARY a.facto_document_id=BINARY v.document_id
      AND a.quote_id=v.quote_id
      AND a.action_key='link_verified'
      AND a.strategy='migration_exact_identity_v9_0_6'
);

UPDATE facto_documents d
INNER JOIN tmp_icylab_v906_verified_link v ON v.local_document_id=d.id
SET d.crm_customer_id=v.customer_id,
    d.crm_quote_id=v.quote_id,
    d.crm_opportunity_id=v.opportunity_id,
    d.link_status='manual_linked',
    d.link_confidence=100,
    d.linked_by_user_id=COALESCE(d.linked_by_user_id,1),
    d.linked_at=NOW(),
    d.received_issued_flag=1,
    d.payment_status='por_cobrar',
    d.payment_due_date=v.due_date,
    d.payment_paid_date=NULL,
    d.updated_at=NOW();

UPDATE billing_collections bc
INNER JOIN tmp_icylab_v906_verified_link v ON v.collection_id=bc.id
SET bc.quote_id=v.quote_id,
    bc.opportunity_id=v.opportunity_id,
    bc.customer_id=v.customer_id,
    bc.paid_amount=0,
    bc.balance_amount=bc.total_amount,
    bc.collection_status='por_cobrar',
    bc.last_payment_date=NULL,
    bc.updated_at=NOW();

UPDATE crm_quotes q
INNER JOIN tmp_icylab_v906_verified_link v ON v.quote_id=q.id
SET q.status='Esperando pago',
    q.invoice_issue_date=v.issue_date,
    q.invoice_number=v.document_number,
    q.facto_document_id=v.document_id,
    q.billing_document_type='invoice_electronic',
    q.billing_document_type_id=v.document_type_id,
    q.billing_environment=v.environment,
    q.billing_currency_code=COALESCE(v.currency_code,'CLP'),
    q.billing_exchange_rate_value=v.exchange_rate_value,
    q.billing_exchange_rate_date=v.exchange_rate_date,
    q.billing_exchange_rate_source=v.exchange_rate_source,
    q.billing_exchange_rate_status=v.exchange_rate_status,
    q.billing_receiver_country_id=v.receiver_country_id,
    q.billing_sale_modality=v.sale_modality,
    q.billing_sale_clause=v.sale_clause,
    q.payment_date=NULL,
    q.payment_status='por_cobrar',
    q.payment_terms_selected=COALESCE(v.payment_terms,'Contado'),
    q.payment_due_date=v.due_date,
    q.payment_paid_date=NULL,
    q.payment_amount=NULL,
    q.payment_notes='DTE existente conciliado por identidad exacta; sin pago registrado.',
    q.billing_match_status='matched',
    q.facto_linked_at=COALESCE(q.facto_linked_at,NOW()),
    q.emitted_by_user_id=COALESCE(q.emitted_by_user_id,1),
    q.emitted_at=COALESCE(q.emitted_at,CONCAT(v.issue_date,' 00:00:00')),
    q.finance_center_id=COALESCE(q.finance_center_id,v.finance_center_id);

INSERT INTO crm_opportunity_stage_history
    (opportunity_id,from_stage,to_stage,changed_by_user_id,changed_by_name,change_source,changed_at)
SELECT v.opportunity_id,o.stage,'facturada',1,'Migracion facturacion v9.0.6','system',NOW()
FROM tmp_icylab_v906_verified_link v
INNER JOIN crm_opportunities o ON o.id=v.opportunity_id
WHERE o.stage<>'facturada'
  AND NOT EXISTS (
      SELECT 1 FROM crm_opportunity_stage_history h
      WHERE h.opportunity_id=v.opportunity_id
        AND h.to_stage='facturada'
        AND h.changed_by_name='Migracion facturacion v9.0.6'
  );

UPDATE crm_opportunities o
INNER JOIN tmp_icylab_v906_verified_link v ON v.opportunity_id=o.id
SET o.stage='facturada',
    o.payment_status='por_cobrar',
    o.payment_due_date=v.due_date,
    o.payment_paid_date=NULL,
    o.facto_document_id=v.document_id,
    o.finance_center_id=COALESCE(o.finance_center_id,v.finance_center_id),
    o.expected_value=v.total_amount,
    o.probability=100,
    o.commercial_closed_at=COALESCE(o.commercial_closed_at,NOW()),
    o.closed_at=COALESCE(o.closed_at,NOW()),
    o.closure_outcome='invoiced',
    o.closure_reason=NULL,
    o.updated_at=NOW();

INSERT INTO crm_documents
    (opportunity_id,customer_id,quote_id,document_type,document_name,document_number,document_date,net_amount,total_amount,file_path,facto_document_id,link_status,finance_center_id,notes)
SELECT
    v.opportunity_id,
    v.customer_id,
    v.quote_id,
    'factura',
    CONCAT('Factura FACTO ',v.document_number),
    v.document_number,
    v.issue_date,
    v.net_amount,
    v.total_amount,
    v.pdf_file_path,
    v.document_id,
    'auto',
    v.finance_center_id,
    CONCAT('DTE emitido conciliado con ',v.quote_number,' por identidad exacta. Sin pago registrado.')
FROM tmp_icylab_v906_verified_link v
WHERE NOT EXISTS (
    SELECT 1 FROM crm_documents cd
    WHERE BINARY cd.facto_document_id=BINARY v.document_id
);

UPDATE crm_documents cd
INNER JOIN tmp_icylab_v906_verified_link v ON BINARY cd.facto_document_id=BINARY v.document_id
SET cd.opportunity_id=COALESCE(cd.opportunity_id,v.opportunity_id),
    cd.customer_id=COALESCE(cd.customer_id,v.customer_id),
    cd.quote_id=COALESCE(cd.quote_id,v.quote_id),
    cd.document_number=COALESCE(cd.document_number,v.document_number),
    cd.document_date=COALESCE(cd.document_date,v.issue_date),
    cd.file_path=COALESCE(cd.file_path,v.pdf_file_path),
    cd.link_status='auto',
    cd.finance_center_id=COALESCE(cd.finance_center_id,v.finance_center_id);

DROP TEMPORARY TABLE IF EXISTS `tmp_icylab_v906_lines`;
CREATE TEMPORARY TABLE `tmp_icylab_v906_lines` AS
SELECT
    base.*,
    CASE
        WHEN ABS(base.stored_tax_amount)>0.009 THEN base.stored_tax_amount
        WHEN base.tax_treatment='afecto' THEN ROUND(base.line_net_amount*base.tax_rate/100,2)
        ELSE 0
    END AS line_tax_amount,
    CASE
        WHEN ABS(base.stored_gross_amount)>0.009 THEN base.stored_gross_amount
        WHEN base.tax_treatment='afecto' THEN base.line_net_amount+ROUND(base.line_net_amount*base.tax_rate/100,2)
        ELSE base.line_net_amount
    END AS line_total_amount
FROM (
    SELECT
        v.environment,
        v.document_id,
        v.quote_id,
        v.finance_center_id AS document_finance_center_id,
        v.net_amount AS document_net_amount,
        v.taxes_amount AS document_tax_amount,
        v.total_amount AS document_total_amount,
        v.quote_number,
        qi.id AS source_line_id,
        qi.product_id,
        qi.name,
        qi.quantity,
        qi.unit,
        qi.price_unit,
        qi.discount_percent,
        qi.discount_amount,
        qi.tax_treatment,
        COALESCE(NULLIF(qi.tax_rate,0),19) AS tax_rate,
        qi.finance_center_id,
        COALESCE(NULLIF(qi.net_amount,0),NULLIF(qi.total_item,0),ROUND((qi.quantity*qi.price_unit)-COALESCE(qi.discount_amount,0),2)) AS line_net_amount,
        qi.tax_amount AS stored_tax_amount,
        qi.gross_amount AS stored_gross_amount
    FROM tmp_icylab_v906_verified_link v
    INNER JOIN crm_quote_items qi ON qi.quote_id=v.quote_id
) base;

INSERT INTO billing_document_lines
    (environment,facto_document_id,quote_id,source_line_id,product_id,sku,description,quantity,unit_measure,unit_price,discount_percent,discount_amount,net_amount,tax_amount,total_amount,tax_treatment,finance_center_id,raw_payload)
SELECT
    v.environment,
    v.document_id,
    v.quote_id,
    qi.source_line_id,
    CASE WHEN qi.product_id REGEXP '^[0-9]+$' THEN CAST(qi.product_id AS UNSIGNED) ELSE NULL END,
    p.sku,
    qi.name,
    qi.quantity,
    qi.unit,
    qi.price_unit,
    qi.discount_percent,
    qi.discount_amount,
    qi.line_net_amount,
    qi.line_tax_amount,
    qi.line_total_amount,
    qi.tax_treatment,
    COALESCE(qi.finance_center_id,v.finance_center_id),
    JSON_OBJECT('source','crm_quote_item','source_item_id',qi.source_line_id,'quote_number',v.quote_number,'amounts_reconstructed',TRUE)
FROM tmp_icylab_v906_verified_link v
INNER JOIN tmp_icylab_v906_lines qi ON qi.environment=v.environment AND BINARY qi.document_id=BINARY v.document_id AND qi.quote_id=v.quote_id
LEFT JOIN products p ON p.id=CASE WHEN qi.product_id REGEXP '^[0-9]+$' THEN CAST(qi.product_id AS UNSIGNED) ELSE NULL END
INNER JOIN (
    SELECT environment,document_id,quote_id,SUM(line_net_amount) AS net_amount,SUM(line_tax_amount) AS tax_amount,SUM(line_total_amount) AS total_amount
    FROM tmp_icylab_v906_lines GROUP BY environment,document_id,quote_id
) sums ON sums.environment=v.environment AND BINARY sums.document_id=BINARY v.document_id AND sums.quote_id=v.quote_id
WHERE ABS(sums.net_amount-v.net_amount)<=1
  AND ABS(sums.tax_amount-v.taxes_amount)<=1
  AND ABS(sums.total_amount-v.total_amount)<=1
  AND NOT EXISTS (
      SELECT 1 FROM billing_document_lines l
      WHERE l.environment=v.environment
        AND BINARY l.facto_document_id=BINARY v.document_id
  );

UPDATE billing_issue_logs l
INNER JOIN tmp_icylab_v906_verified_link v ON l.id=77 AND l.quote_id=v.quote_id AND l.environment=v.environment
SET l.opportunity_id=v.opportunity_id,
    l.customer_id=v.customer_id,
    l.document_type_key='invoice_electronic',
    l.document_type_id=v.document_type_id,
    l.mode='issue',
    l.status='success',
    l.response_payload=v.raw_payload,
    l.http_code=200,
    l.facto_document_id=v.document_id,
    l.document_number=v.document_number,
    l.pdf_file_path=v.pdf_file_path,
    l.xml_file_path=v.xml_file_path,
    l.error_code='RECOVERED_EXISTING_DTE',
    l.error_message='Documento existente verificado y vinculado sin nueva emision ni registro de pago.',
    l.updated_at=NOW();

COMMIT;

SELECT
    CASE WHEN @v906_verified_count=1 THEN 'PASS' ELSE 'BLOCKED' END AS migration_result,
    @v906_verified_count AS verified_candidates,
    CASE WHEN @v906_verified_count=1
         THEN 'DTE 877 vinculado a COT-2026-1493 y oportunidad 722; CxC por cobrar; sin pago nuevo.'
         ELSE 'No se aplicaron cambios al caso: la identidad o el estado financiero no coinciden exactamente.'
    END AS detail;

DROP TEMPORARY TABLE IF EXISTS `tmp_icylab_v906_verified_link`;
DROP TEMPORARY TABLE IF EXISTS `tmp_icylab_v906_lines`;
