-- ICYLAB OS - P0 Quote Number Integrity
-- Objetivo:
-- 1) Reparar folios duplicados existentes en crm_quotes.
-- 2) Sincronizar crm_documents cuando el documento apunta a quote_view.php?id=ID.
-- 3) Inicializar quote_sequences con el mayor folio real por año.
-- 4) Bloquear nuevos duplicados con UNIQUE KEY.

CREATE TABLE IF NOT EXISTS `quote_sequences` (
  `year` int(11) NOT NULL,
  `last_number` int(11) NOT NULL,
  PRIMARY KEY (`year`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

START TRANSACTION;

CREATE TEMPORARY TABLE tmp_quote_duplicates_to_fix AS
SELECT
    x.id,
    x.quote_number AS old_quote_number,
    x.quote_year,
    ROW_NUMBER() OVER (PARTITION BY x.quote_year ORDER BY x.id) AS rn
FROM (
    SELECT
        q.id,
        q.quote_number,
        CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(q.quote_number, '-', 2), '-', -1) AS UNSIGNED) AS quote_year
    FROM crm_quotes q
    INNER JOIN (
        SELECT quote_number, MIN(id) AS keep_id
        FROM crm_quotes
        GROUP BY quote_number
        HAVING COUNT(*) > 1
    ) d ON d.quote_number = q.quote_number
    WHERE q.id <> d.keep_id
) x;

CREATE TEMPORARY TABLE tmp_quote_year_max AS
SELECT
    CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(quote_number, '-', 2), '-', -1) AS UNSIGNED) AS quote_year,
    COALESCE(MAX(CAST(SUBSTRING_INDEX(quote_number, '-', -1) AS UNSIGNED)), 1450) AS max_number
FROM crm_quotes
WHERE quote_number LIKE 'COT-%-%'
GROUP BY CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(quote_number, '-', 2), '-', -1) AS UNSIGNED);

ALTER TABLE tmp_quote_duplicates_to_fix
ADD COLUMN new_quote_number varchar(50) NULL;

UPDATE tmp_quote_duplicates_to_fix f
INNER JOIN tmp_quote_year_max ym ON ym.quote_year = f.quote_year
SET f.new_quote_number = CONCAT('COT-', f.quote_year, '-', LPAD(ym.max_number + f.rn, 4, '0'));

UPDATE crm_documents d
INNER JOIN tmp_quote_duplicates_to_fix f
    ON d.file_path = CONCAT('/modules/crm/quote_view.php?id=', f.id)
SET
    d.document_number = f.new_quote_number,
    d.document_name = CONCAT('Cotización ', f.new_quote_number)
WHERE f.new_quote_number IS NOT NULL;

UPDATE crm_quotes q
INNER JOIN tmp_quote_duplicates_to_fix f ON f.id = q.id
SET q.quote_number = f.new_quote_number
WHERE f.new_quote_number IS NOT NULL;

INSERT INTO quote_sequences (`year`, last_number)
SELECT
    quote_year,
    MAX(max_number) AS last_number
FROM (
    SELECT
        CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(quote_number, '-', 2), '-', -1) AS UNSIGNED) AS quote_year,
        CAST(SUBSTRING_INDEX(quote_number, '-', -1) AS UNSIGNED) AS max_number
    FROM crm_quotes
    WHERE quote_number LIKE 'COT-%-%'
) y
GROUP BY quote_year
ON DUPLICATE KEY UPDATE last_number = GREATEST(last_number, VALUES(last_number));

UPDATE crm_activities
SET activity_type = 'cotizacion'
WHERE (activity_type = '' OR activity_type IS NULL)
  AND title LIKE '%Cotización%';

UPDATE crm_activities
SET activity_type = 'sistema'
WHERE activity_type = '' OR activity_type IS NULL;

COMMIT;

DELIMITER //
CREATE PROCEDURE icylab_add_quote_unique_index_if_needed()
BEGIN
    IF NOT EXISTS (
        SELECT 1
        FROM INFORMATION_SCHEMA.STATISTICS
        WHERE TABLE_SCHEMA = DATABASE()
          AND TABLE_NAME = 'crm_quotes'
          AND INDEX_NAME = 'uq_crm_quotes_quote_number'
    ) THEN
        ALTER TABLE crm_quotes ADD UNIQUE KEY uq_crm_quotes_quote_number (quote_number);
    END IF;

    IF NOT EXISTS (
        SELECT 1
        FROM INFORMATION_SCHEMA.STATISTICS
        WHERE TABLE_SCHEMA = DATABASE()
          AND TABLE_NAME = 'crm_quotes'
          AND INDEX_NAME = 'idx_crm_quotes_customer_id'
    ) THEN
        ALTER TABLE crm_quotes ADD KEY idx_crm_quotes_customer_id (customer_id);
    END IF;
END//
DELIMITER ;

CALL icylab_add_quote_unique_index_if_needed();
DROP PROCEDURE icylab_add_quote_unique_index_if_needed;

-- Validacion posterior recomendada:
-- SELECT quote_number, COUNT(*) FROM crm_quotes GROUP BY quote_number HAVING COUNT(*) > 1;
-- SELECT * FROM quote_sequences ORDER BY year;
