-- ============================================
-- Script de Setup: Sistema de Controle de Etiquetas
-- Previne collision de etiquetas entre marketplaces
-- Data: 2025-12-02
-- ============================================

-- Tabela de controle de ownership de etiquetas
CREATE TABLE IF NOT EXISTS drophub_global.etiquetas_control (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_sn VARCHAR(100) NOT NULL COMMENT 'OrderSN/OrderID da venda',
    integracao VARCHAR(50) NOT NULL COMMENT 'Marketplace: Mercado Livre, Shopee, etc',
    arquivo_etiqueta VARCHAR(255) NOT NULL COMMENT 'Nome do arquivo: e_[order_sn].jpg',
    etiqueta_url TEXT COMMENT 'URL completa da etiqueta',
    usuario_id INT NOT NULL COMMENT 'ID do usuário dono da venda',
    whitelabel VARCHAR(50) NOT NULL COMMENT 'Nome do whitelabel',
    ip_origem VARCHAR(45) COMMENT 'IP que criou a etiqueta',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- Índices e constraints
    UNIQUE KEY unique_etiqueta (order_sn, whitelabel, integracao),
    INDEX idx_order (order_sn),
    INDEX idx_integracao (integracao),
    INDEX idx_usuario (usuario_id),
    INDEX idx_whitelabel (whitelabel),
    INDEX idx_arquivo (arquivo_etiqueta),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Controla ownership de etiquetas por marketplace';

-- Tabela de registro de tentativas de collision
CREATE TABLE IF NOT EXISTS drophub_global.etiquetas_collisions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_sn VARCHAR(100) NOT NULL COMMENT 'OrderSN que tentou salvar',
    integracao_tentativa VARCHAR(50) NOT NULL COMMENT 'Marketplace que tentou salvar',
    integracao_original VARCHAR(50) COMMENT 'Marketplace que já possui a etiqueta',
    arquivo_etiqueta VARCHAR(255) COMMENT 'Nome do arquivo em conflito',
    etiqueta_url_tentativa TEXT COMMENT 'URL que tentou salvar',
    etiqueta_url_original TEXT COMMENT 'URL que já existe',
    usuario_id INT NOT NULL COMMENT 'ID do usuário da tentativa',
    whitelabel VARCHAR(50) NOT NULL COMMENT 'Nome do whitelabel',
    ip_origem VARCHAR(45) COMMENT 'IP da tentativa',
    motivo_rejeicao VARCHAR(255) COMMENT 'Motivo da rejeição',
    data_tentativa TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    -- Índices
    INDEX idx_order (order_sn),
    INDEX idx_integracao_tentativa (integracao_tentativa),
    INDEX idx_usuario (usuario_id),
    INDEX idx_whitelabel (whitelabel),
    INDEX idx_data (data_tentativa)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Registra tentativas de collision de etiquetas para análise';

-- View para análise rápida de collisions
CREATE OR REPLACE VIEW drophub_global.v_etiquetas_collisions_resumo AS
SELECT 
    DATE(data_tentativa) as data,
    whitelabel,
    integracao_tentativa,
    COUNT(*) as total_collisions,
    COUNT(DISTINCT order_sn) as vendas_afetadas,
    COUNT(DISTINCT usuario_id) as usuarios_afetados
FROM drophub_global.etiquetas_collisions
GROUP BY DATE(data_tentativa), whitelabel, integracao_tentativa
ORDER BY data DESC, total_collisions DESC;

-- View para identificar collisions atuais no sistema
CREATE OR REPLACE VIEW drophub_global.v_etiquetas_possiveis_collisions AS
SELECT 
    mv1.saleTitle as order_sn_ml,
    mv1.integracao as integracao_ml,
    mv1.etiqueta_ml as etiqueta_ml_url,
    mv1.usuario_id as usuario_id_ml,
    mv2.saleTitle as order_sn_shopee,
    mv2.integracao as integracao_shopee,
    mv2.etiqueta_ml as etiqueta_shopee_url,
    mv2.usuario_id as usuario_id_shopee,
    mv1.vendido_dia as data_venda_ml,
    mv2.vendido_dia as data_venda_shopee
FROM drophub_global.minhas_vendas mv1
JOIN drophub_global.minhas_vendas mv2 
    ON mv1.etiqueta_ml = mv2.etiqueta_ml
    AND mv1.integracao != mv2.integracao
WHERE mv1.etiqueta_ml IS NOT NULL
  AND mv1.etiqueta_ml != ''
ORDER BY mv1.vendido_dia DESC;

-- Procedure para detectar e reportar collisions existentes
DELIMITER $$

CREATE PROCEDURE IF NOT EXISTS drophub_global.sp_detectar_collisions_etiquetas(
    IN p_whitelabel VARCHAR(50)
)
BEGIN
    -- Detectar vendas diferentes com mesma etiqueta
    SELECT 
        mv1.id as venda_1_id,
        mv1.saleTitle as venda_1_order,
        mv1.integracao as venda_1_marketplace,
        mv1.usuario_id as venda_1_usuario,
        mv2.id as venda_2_id,
        mv2.saleTitle as venda_2_order,
        mv2.integracao as venda_2_marketplace,
        mv2.usuario_id as venda_2_usuario,
        mv1.etiqueta_ml as etiqueta_duplicada,
        CASE 
            WHEN mv1.vendido_dia < mv2.vendido_dia THEN 'venda_1_mais_antiga'
            ELSE 'venda_2_mais_antiga'
        END as provavel_dona
    FROM drophub_global.minhas_vendas mv1
    INNER JOIN drophub_global.minhas_vendas mv2 
        ON mv1.etiqueta_ml = mv2.etiqueta_ml
        AND mv1.id != mv2.id
        AND mv1.saleTitle != mv2.saleTitle
    WHERE mv1.etiqueta_ml IS NOT NULL 
      AND mv1.etiqueta_ml != ''
      AND (p_whitelabel IS NULL OR mv1.database_name = p_whitelabel)
    ORDER BY mv1.vendido_dia DESC;
END$$

DELIMITER ;

-- ============================================
-- Queries úteis para diagnóstico
-- ============================================

-- 1. Verificar collisions atuais
-- SELECT * FROM drophub_global.v_etiquetas_possiveis_collisions;

-- 2. Ver resumo de collisions por dia
-- SELECT * FROM drophub_global.v_etiquetas_collisions_resumo WHERE data >= CURDATE() - INTERVAL 7 DAY;

-- 3. Detectar collisions para um whitelabel específico
-- CALL drophub_global.sp_detectar_collisions_etiquetas('homolog');

-- 4. Ver últimas tentativas de collision
-- SELECT * FROM drophub_global.etiquetas_collisions ORDER BY data_tentativa DESC LIMIT 50;

-- 5. Verificar ownership de uma etiqueta específica
-- SELECT * FROM drophub_global.etiquetas_control WHERE order_sn = '251125MGMNDA6Q';

-- ============================================
-- Script concluído
-- ============================================
