-- ICYLAB OS v8.5 - MOTOR DE REPOSICION PREDICTIVA Y LIMPIEZA DEL EMBUDO
-- Ejecutar una sola vez despues de respaldar la base de datos.
-- Compatible con MariaDB/MySQL que soporten ADD COLUMN IF NOT EXISTS.

-- -----------------------------------------------------------------------------
-- 1. Modelo de cliente: ciclo, ultima compra y clasificacion comercial
-- -----------------------------------------------------------------------------
ALTER TABLE crm_customers
    MODIFY COLUMN restock_days INT NULL DEFAULT NULL,
    ADD COLUMN IF NOT EXISTS restock_cycle_source ENUM('none','manual','facto_calculated','legacy') NOT NULL DEFAULT 'none' AFTER restock_days,
    ADD COLUMN IF NOT EXISTS restock_confidence DECIMAL(5,4) NULL AFTER restock_cycle_source,
    ADD COLUMN IF NOT EXISTS restock_observations INT NULL AFTER restock_confidence,
    ADD COLUMN IF NOT EXISTS last_purchase_date DATE NULL AFTER restock_observations,
    ADD COLUMN IF NOT EXISTS customer_lifecycle_status ENUM('unknown','active','at_risk','lost') NOT NULL DEFAULT 'unknown' AFTER last_purchase_date,
    ADD COLUMN IF NOT EXISTS customer_lifecycle_updated_at DATETIME NULL AFTER customer_lifecycle_status;

-- El valor heredado 30 no se considera evidencia suficiente. Los ciclos distintos
-- se preservan como manuales hasta que el usuario los recalcule desde FACTO.
UPDATE crm_customers
SET restock_cycle_source = CASE
        WHEN restock_days IS NULL OR restock_days <= 0 THEN 'none'
        WHEN restock_days = 30 THEN 'legacy'
        ELSE 'manual'
    END,
    restock_confidence = CASE
        WHEN restock_days IS NULL OR restock_days <= 0 THEN NULL
        WHEN restock_days = 30 THEN NULL
        ELSE 1.0000
    END
WHERE restock_cycle_source = 'none'
   OR restock_cycle_source IS NULL;

-- Si una contraparte marcada como proveedor tiene ventas emitidas, se normaliza como ambos.
UPDATE crm_customers c
SET c.counterparty_type = 'both',
    c.is_supplier = 1,
    c.updated_at = NOW()
WHERE c.deleted_at IS NULL
  AND c.counterparty_type = 'supplier'
  AND EXISTS (
      SELECT 1 FROM facto_documents fd
      WHERE fd.received_issued_flag = 1
        AND fd.document_status = 1
        AND COALESCE(fd.payment_status,'') <> 'anulado'
        AND COALESCE(fd.document_type_taxbureau,'') IN ('33','34','39','110')
        AND (
            fd.crm_customer_id = c.id
            OR REPLACE(REPLACE(REPLACE(UPPER(fd.receiver_tax_id_code),'.',''),'-',''),' ','') =
               REPLACE(REPLACE(REPLACE(UPPER(c.customer_tax_id),'.',''),'-',''),' ','')
        )
  );

-- Los proveedores puros no participan del motor comercial de reposicion.
UPDATE crm_customers c
SET c.restock_days = NULL,
    c.next_restock_date = NULL,
    c.restock_cycle_source = 'none',
    c.restock_confidence = NULL,
    c.restock_observations = NULL,
    c.last_purchase_date = NULL,
    c.customer_lifecycle_status = 'unknown',
    c.customer_lifecycle_updated_at = NOW()
WHERE c.deleted_at IS NULL
  AND (
      c.counterparty_type = 'supplier'
      OR (
          c.is_supplier = 1
          AND c.counterparty_type <> 'both'
          AND NOT EXISTS (
              SELECT 1 FROM facto_documents fd
              WHERE fd.received_issued_flag = 1
                AND fd.document_status = 1
                AND COALESCE(fd.payment_status,'') <> 'anulado'
                AND COALESCE(fd.document_type_taxbureau,'') IN ('33','34','39','110')
                AND (
                    fd.crm_customer_id = c.id
                    OR REPLACE(REPLACE(REPLACE(UPPER(fd.receiver_tax_id_code),'.',''),'-',''),' ','') =
                       REPLACE(REPLACE(REPLACE(UPPER(c.customer_tax_id),'.',''),'-',''),' ','')
                )
          )
      )
  );

-- -----------------------------------------------------------------------------
-- 2. Pronosticos separados del kanban
-- -----------------------------------------------------------------------------
ALTER TABLE crm_opportunities
    ADD COLUMN IF NOT EXISTS restock_forecast_id BIGINT UNSIGNED NULL AFTER archive_hold_reason;

CREATE TABLE IF NOT EXISTS crm_restock_forecasts (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    customer_id INT NOT NULL,
    source_opportunity_id INT NULL,
    source_quote_id INT NULL,
    source_document_id INT NULL,
    source_facto_document_id VARCHAR(100) NOT NULL,
    last_purchase_date DATE NOT NULL,
    restock_days INT NOT NULL,
    projected_restock_date DATE NOT NULL,
    activation_date DATE NOT NULL,
    expected_value DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    owner_user_id INT NULL,
    owner_name VARCHAR(255) NULL,
    confidence_score DECIMAL(5,4) NULL,
    purchase_observations INT NOT NULL DEFAULT 1,
    status ENUM('pending','ready','converted','cancelled','expired','superseded') NOT NULL DEFAULT 'pending',
    automation_key CHAR(64) NOT NULL,
    source_event VARCHAR(80) NOT NULL DEFAULT 'system',
    opportunity_id INT NULL,
    converted_at DATETIME NULL,
    cancelled_at DATETIME NULL,
    notes TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_restock_forecast_automation (automation_key),
    KEY idx_restock_forecast_due (status, activation_date, projected_restock_date),
    KEY idx_restock_forecast_customer (customer_id, last_purchase_date),
    KEY idx_restock_forecast_opportunity (opportunity_id),
    KEY idx_restock_forecast_source_doc (source_facto_document_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET @db_name := DATABASE();
SET @idx_exists := (SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema=@db_name AND table_name='crm_opportunities' AND index_name='idx_crm_opportunities_restock_forecast');
SET @sql := IF(@idx_exists=0,'ALTER TABLE crm_opportunities ADD INDEX idx_crm_opportunities_restock_forecast (restock_forecast_id,source,archived_at,deleted_at)','SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @idx_exists := (SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema=@db_name AND table_name='crm_customers' AND index_name='idx_crm_customers_lifecycle');
SET @sql := IF(@idx_exists=0,'ALTER TABLE crm_customers ADD INDEX idx_crm_customers_lifecycle (customer_lifecycle_status,last_purchase_date)','SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @idx_exists := (SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema=@db_name AND table_name='crm_restock_forecasts' AND index_name='idx_restock_forecast_owner_due');
SET @sql := IF(@idx_exists=0,'ALTER TABLE crm_restock_forecasts ADD INDEX idx_restock_forecast_owner_due (owner_user_id,status,activation_date)','SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- -----------------------------------------------------------------------------
-- 3. Parametros versionados del motor BI
-- -----------------------------------------------------------------------------
INSERT INTO dashboard_settings
    (setting_key, setting_value, status, approved_by_user_id, approved_at, updated_by_user_id)
VALUES
    ('restock_recent_purchase_days', '90', 'approved', 1, NOW(), 1),
    ('restock_activation_lead_days', '14', 'approved', 1, NOW(), 1),
    ('restock_opportunity_grace_days', '30', 'approved', 1, NOW(), 1),
    ('customer_lost_min_days', '180', 'approved', 1, NOW(), 1),
    ('customer_lost_max_days', '365', 'approved', 1, NOW(), 1),
    ('customer_lost_cycle_multiplier', '2.0', 'approved', 1, NOW(), 1)
ON DUPLICATE KEY UPDATE setting_key=VALUES(setting_key);

-- -----------------------------------------------------------------------------
-- 4. Snapshot inicial de ultima compra y clasificacion
-- -----------------------------------------------------------------------------
UPDATE crm_customers c
LEFT JOIN (
    SELECT
        COALESCE(fd.crm_customer_id, c2.id) AS customer_id,
        MAX(fd.issue_date) AS last_purchase_date
    FROM facto_documents fd
    LEFT JOIN crm_customers c2
      ON c2.deleted_at IS NULL
     AND REPLACE(REPLACE(REPLACE(UPPER(c2.customer_tax_id),'.',''),'-',''),' ','') =
         REPLACE(REPLACE(REPLACE(UPPER(fd.receiver_tax_id_code),'.',''),'-',''),' ','')
    WHERE fd.received_issued_flag = 1
      AND fd.document_status = 1
      AND COALESCE(fd.payment_status,'') <> 'anulado'
      AND COALESCE(fd.document_type_taxbureau,'') IN ('33','34','39','110')
    GROUP BY COALESCE(fd.crm_customer_id, c2.id)
) p ON p.customer_id = c.id
SET c.last_purchase_date = p.last_purchase_date,
    c.next_restock_date = CASE
        WHEN p.last_purchase_date IS NOT NULL AND c.restock_days > 0 AND c.restock_cycle_source <> 'legacy'
        THEN DATE_ADD(p.last_purchase_date, INTERVAL c.restock_days DAY)
        ELSE NULL
    END,
    c.customer_lifecycle_status = CASE
        WHEN c.counterparty_type='supplier' THEN 'unknown'
        WHEN p.last_purchase_date IS NULL THEN 'unknown'
        WHEN DATEDIFF(CURDATE(),p.last_purchase_date) <= 90 THEN 'active'
        WHEN DATEDIFF(CURDATE(),p.last_purchase_date) <= LEAST(365,GREATEST(180,CEIL(COALESCE(NULLIF(c.restock_days,0),90)*2))) THEN 'at_risk'
        ELSE 'lost'
    END,
    c.customer_lifecycle_updated_at = NOW()
WHERE c.deleted_at IS NULL;

-- -----------------------------------------------------------------------------
-- 5. Limpieza segura del lote incorrecto generado por force_sync_facto.php
--    IDs 443-645, creados el 15-07-2026 a las 17:35:22/23.
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS crm_opportunities_backup_restock_v85 LIKE crm_opportunities;

INSERT IGNORE INTO crm_opportunities_backup_restock_v85
SELECT o.*
FROM crm_opportunities o
WHERE o.id BETWEEN 443 AND 645
  AND o.title LIKE 'Reposición Programada - %'
  AND o.owner_name = 'Sistema'
  AND COALESCE(o.expected_value,0) = 0
  AND o.created_at >= '2026-07-15 17:35:22'
  AND o.created_at <  '2026-07-15 17:35:24'
  AND NOT EXISTS (SELECT 1 FROM crm_quotes q WHERE q.opportunity_id=o.id)
  AND NOT EXISTS (SELECT 1 FROM crm_documents d WHERE d.opportunity_id=o.id)
  AND NOT EXISTS (SELECT 1 FROM crm_activities a WHERE a.opportunity_id=o.id)
  AND NOT EXISTS (SELECT 1 FROM crm_events e WHERE e.opportunity_id=o.id)
  AND NOT EXISTS (SELECT 1 FROM crm_opportunity_stage_history h WHERE h.opportunity_id=o.id)
  AND NOT EXISTS (SELECT 1 FROM crm_opportunity_lifecycle_history l WHERE l.opportunity_id=o.id);

UPDATE crm_opportunities o
SET o.deleted_at = COALESCE(o.deleted_at,NOW()),
    o.updated_at = NOW()
WHERE o.id BETWEEN 443 AND 645
  AND o.title LIKE 'Reposición Programada - %'
  AND o.owner_name = 'Sistema'
  AND COALESCE(o.expected_value,0) = 0
  AND o.created_at >= '2026-07-15 17:35:22'
  AND o.created_at <  '2026-07-15 17:35:24'
  AND NOT EXISTS (SELECT 1 FROM crm_quotes q WHERE q.opportunity_id=o.id)
  AND NOT EXISTS (SELECT 1 FROM crm_documents d WHERE d.opportunity_id=o.id)
  AND NOT EXISTS (SELECT 1 FROM crm_activities a WHERE a.opportunity_id=o.id)
  AND NOT EXISTS (SELECT 1 FROM crm_events e WHERE e.opportunity_id=o.id)
  AND NOT EXISTS (SELECT 1 FROM crm_opportunity_stage_history h WHERE h.opportunity_id=o.id)
  AND NOT EXISTS (SELECT 1 FROM crm_opportunity_lifecycle_history l WHERE l.opportunity_id=o.id);

-- -----------------------------------------------------------------------------
-- 6. Verificacion de salida
-- -----------------------------------------------------------------------------
SELECT
    (SELECT COUNT(*) FROM crm_opportunities_backup_restock_v85) AS backup_rows,
    (SELECT COUNT(*) FROM crm_opportunities WHERE id BETWEEN 443 AND 645 AND deleted_at IS NULL AND title LIKE 'Reposición Programada - %') AS remaining_bad_batch,
    (SELECT COUNT(*) FROM crm_restock_forecasts) AS forecast_rows,
    (SELECT COUNT(*) FROM crm_customers WHERE deleted_at IS NULL AND customer_lifecycle_status='active') AS active_customers,
    (SELECT COUNT(*) FROM crm_customers WHERE deleted_at IS NULL AND customer_lifecycle_status='at_risk') AS at_risk_customers,
    (SELECT COUNT(*) FROM crm_customers WHERE deleted_at IS NULL AND customer_lifecycle_status='lost') AS lost_customers,
    (SELECT COUNT(*) FROM crm_customers WHERE deleted_at IS NULL AND counterparty_type='supplier' AND restock_days IS NOT NULL) AS supplier_cycles_remaining;
