-- ============================================
-- Script de Setup: Prevenção de Duplicação de Vendas
-- Garante que cada saleTitle + usuario_id seja único
-- Data: 2025-12-02
-- ============================================

USE drophub_global;

-- ============================================
-- CAMADA 3: Adicionar UNIQUE INDEX
-- ============================================

-- Verificar se já existe o índice
SELECT 'Verificando índices existentes...' as status;

-- Remover duplicatas ANTES de criar o índice único
SELECT 'Removendo duplicatas existentes...' as status;

-- Identificar duplicatas (manter a mais antiga)
CREATE TEMPORARY TABLE IF NOT EXISTS temp_duplicatas AS
SELECT 
    MIN(id) as id_manter,
    saleTitle,
    usuario_id,
    COUNT(*) as total_duplicatas
FROM minhas_vendas
GROUP BY saleTitle, usuario_id
HAVING COUNT(*) > 1;

SELECT CONCAT('Encontradas ', COUNT(*), ' vendas com duplicatas') as status
FROM temp_duplicatas;

-- Deletar duplicatas (mantém apenas a primeira ocorrência)
DELETE mv FROM minhas_vendas mv
INNER JOIN temp_duplicatas td 
    ON mv.saleTitle = td.saleTitle 
    AND mv.usuario_id = td.usuario_id
WHERE mv.id > td.id_manter;

SELECT 'Duplicatas removidas!' as status;

-- Limpar tabela temporária
DROP TEMPORARY TABLE IF EXISTS temp_duplicatas;

-- ============================================
-- Criar UNIQUE INDEX para prevenir futuras duplicatas
-- ============================================

-- Verificar se índice já existe
SET @index_exists = (
    SELECT COUNT(*)
    FROM INFORMATION_SCHEMA.STATISTICS
    WHERE TABLE_SCHEMA = 'drophub_global'
    AND TABLE_NAME = 'minhas_vendas'
    AND INDEX_NAME = 'unique_sale_per_user'
);

-- Criar índice apenas se não existir
SET @sql = IF(
    @index_exists = 0,
    'ALTER TABLE minhas_vendas ADD UNIQUE INDEX unique_sale_per_user (saleTitle, usuario_id)',
    'SELECT "Índice unique_sale_per_user já existe" as status'
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SELECT 'UNIQUE INDEX criado/verificado com sucesso!' as status;

-- ============================================
-- Criar índices adicionais para performance
-- ============================================

-- Índice para busca rápida por saleTitle
SET @index_exists = (
    SELECT COUNT(*)
    FROM INFORMATION_SCHEMA.STATISTICS
    WHERE TABLE_SCHEMA = 'drophub_global'
    AND TABLE_NAME = 'minhas_vendas'
    AND INDEX_NAME = 'idx_saleTitle'
);

SET @sql = IF(
    @index_exists = 0,
    'CREATE INDEX idx_saleTitle ON minhas_vendas(saleTitle)',
    'SELECT "Índice idx_saleTitle já existe" as status'
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Índice para busca por usuário e data
SET @index_exists = (
    SELECT COUNT(*)
    FROM INFORMATION_SCHEMA.STATISTICS
    WHERE TABLE_SCHEMA = 'drophub_global'
    AND TABLE_NAME = 'minhas_vendas'
    AND INDEX_NAME = 'idx_usuario_data'
);

SET @sql = IF(
    @index_exists = 0,
    'CREATE INDEX idx_usuario_data ON minhas_vendas(usuario_id, vendido_dia)',
    'SELECT "Índice idx_usuario_data já existe" as status'
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SELECT 'Índices de performance criados!' as status;

-- ============================================
-- View para monitorar tentativas de duplicação
-- ============================================

CREATE OR REPLACE VIEW v_vendas_duplicadas_historico AS
SELECT 
    DATE(created_at) as data,
    integracao,
    COUNT(*) as total_tentativas,
    COUNT(DISTINCT usuario_id) as usuarios_afetados
FROM minhas_vendas
WHERE created_at IS NOT NULL
GROUP BY DATE(created_at), integracao
ORDER BY data DESC;

SELECT 'View de monitoramento criada!' as status;

-- ============================================
-- Procedure para detectar e reportar duplicatas
-- ============================================

DELIMITER $$

DROP PROCEDURE IF EXISTS sp_verificar_duplicatas_vendas$$

CREATE PROCEDURE sp_verificar_duplicatas_vendas(
    IN p_usuario_id INT
)
BEGIN
    -- Buscar vendas duplicadas
    SELECT 
        mv.saleTitle,
        mv.integracao,
        mv.usuario_id,
        COUNT(*) as total_duplicatas,
        MIN(mv.id) as primeiro_id,
        MAX(mv.id) as ultimo_id,
        MIN(mv.vendido_dia) as primeira_data,
        MAX(mv.vendido_dia) as ultima_data
    FROM minhas_vendas mv
    WHERE (p_usuario_id IS NULL OR mv.usuario_id = p_usuario_id)
    GROUP BY mv.saleTitle, mv.integracao, mv.usuario_id
    HAVING COUNT(*) > 1
    ORDER BY total_duplicatas DESC, mv.vendido_dia DESC
    LIMIT 100;
END$$

DELIMITER ;

SELECT 'Procedure de verificação criada!' as status;

-- ============================================
-- Teste final
-- ============================================

SELECT 'Verificando configuração final...' as status;

-- Verificar índices criados
SELECT 
    INDEX_NAME,
    COLUMN_NAME,
    NON_UNIQUE
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'drophub_global'
  AND TABLE_NAME = 'minhas_vendas'
  AND INDEX_NAME IN ('unique_sale_per_user', 'idx_saleTitle', 'idx_usuario_data')
ORDER BY INDEX_NAME, SEQ_IN_INDEX;

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

-- 1. Verificar se existem duplicatas atualmente
-- SELECT saleTitle, usuario_id, COUNT(*) as total 
-- FROM minhas_vendas 
-- GROUP BY saleTitle, usuario_id 
-- HAVING COUNT(*) > 1;

-- 2. Ver duplicatas de um usuário específico
-- CALL sp_verificar_duplicatas_vendas(123);

-- 3. Ver todas as duplicatas
-- CALL sp_verificar_duplicatas_vendas(NULL);

-- 4. Remover duplicatas manualmente (manter a primeira)
-- DELETE mv FROM minhas_vendas mv
-- INNER JOIN (
--     SELECT saleTitle, usuario_id, MIN(id) as keep_id
--     FROM minhas_vendas
--     GROUP BY saleTitle, usuario_id
--     HAVING COUNT(*) > 1
-- ) td ON mv.saleTitle = td.saleTitle 
--     AND mv.usuario_id = td.usuario_id
--     AND mv.id != td.keep_id;

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

SELECT '✅ Setup de prevenção de duplicação concluído com sucesso!' as status;
