-- ============================================
-- Migration: Sistema de Captura de Leads
-- Version: 003
-- Date: 2026-01-28
-- Description: Tabelas para leads, formularios, status e webhooks
-- ============================================

-- ----------------------------
-- Tabela: lead_forms
-- Armazena metadados dos formularios de lead do Meta
-- ----------------------------
CREATE TABLE IF NOT EXISTS `lead_forms` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` INT UNSIGNED NOT NULL,
    `meta_form_id` VARCHAR(50) NOT NULL COMMENT 'ID do formulario no Meta',
    `page_id` VARCHAR(50) NOT NULL COMMENT 'ID da pagina do Facebook',
    `page_name` VARCHAR(255) NULL COMMENT 'Nome da pagina',
    `ad_account_id` VARCHAR(50) NULL COMMENT 'Conta de anuncios associada',
    `name` VARCHAR(255) NOT NULL COMMENT 'Nome do formulario',
    `status` ENUM('active', 'inactive', 'archived') NOT NULL DEFAULT 'active',
    `questions_json` JSON NULL COMMENT 'Estrutura das perguntas do formulario',
    `leads_count` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Contador de leads',
    `last_synced_at` DATETIME NULL COMMENT 'Ultima sincronizacao',
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_lead_forms_user_form` (`user_id`, `meta_form_id`),
    KEY `idx_lead_forms_page` (`page_id`),
    KEY `idx_lead_forms_status` (`status`),
    KEY `idx_lead_forms_user` (`user_id`),
    CONSTRAINT `fk_lead_forms_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- Tabela: lead_statuses
-- Status customizaveis por usuario
-- ----------------------------
CREATE TABLE IF NOT EXISTS `lead_statuses` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` INT UNSIGNED NOT NULL,
    `name` VARCHAR(100) NOT NULL COMMENT 'Nome do status',
    `slug` VARCHAR(100) NOT NULL COMMENT 'Slug para URLs',
    `color` VARCHAR(7) NOT NULL DEFAULT '#6366f1' COMMENT 'Cor em hexadecimal',
    `sort_order` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Ordem de exibicao',
    `is_default` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Status padrao para novos leads',
    `is_final` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Status final (convertido/descartado)',
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_lead_statuses_user_slug` (`user_id`, `slug`),
    KEY `idx_lead_statuses_order` (`user_id`, `sort_order`),
    CONSTRAINT `fk_lead_statuses_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- Tabela: leads
-- Armazenamento principal de leads
-- ----------------------------
CREATE TABLE IF NOT EXISTS `leads` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` INT UNSIGNED NOT NULL,
    `lead_form_id` INT UNSIGNED NULL,
    `status_id` INT UNSIGNED NULL,
    `meta_lead_id` VARCHAR(50) NOT NULL COMMENT 'ID do lead no Meta',
    `meta_form_id` VARCHAR(50) NOT NULL COMMENT 'ID do formulario no Meta',
    `page_id` VARCHAR(50) NOT NULL COMMENT 'ID da pagina',
    `ad_account_id` VARCHAR(50) NULL COMMENT 'Conta de anuncios',
    `campaign_id` VARCHAR(50) NULL COMMENT 'ID da campanha',
    `campaign_name` VARCHAR(255) NULL COMMENT 'Nome da campanha',
    `adset_id` VARCHAR(50) NULL COMMENT 'ID do conjunto de anuncios',
    `adset_name` VARCHAR(255) NULL COMMENT 'Nome do conjunto',
    `ad_id` VARCHAR(50) NULL COMMENT 'ID do anuncio',
    `ad_name` VARCHAR(255) NULL COMMENT 'Nome do anuncio',
    `full_name` VARCHAR(255) NULL COMMENT 'Nome completo do lead',
    `email` VARCHAR(255) NULL COMMENT 'Email do lead',
    `phone` VARCHAR(50) NULL COMMENT 'Telefone do lead',
    `field_data_json` JSON NOT NULL COMMENT 'Todos os campos do formulario',
    `platform` ENUM('facebook', 'instagram', 'messenger', 'audience_network') NOT NULL DEFAULT 'facebook',
    `is_organic` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Lead organico (sem anuncio)',
    `lead_created_at` DATETIME NOT NULL COMMENT 'Data de criacao no Meta',
    `notes` TEXT NULL COMMENT 'Notas sobre o lead',
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_leads_meta_lead` (`meta_lead_id`),
    KEY `idx_leads_user` (`user_id`),
    KEY `idx_leads_form` (`lead_form_id`),
    KEY `idx_leads_status` (`status_id`),
    KEY `idx_leads_email` (`email`),
    KEY `idx_leads_phone` (`phone`),
    KEY `idx_leads_campaign` (`campaign_id`),
    KEY `idx_leads_ad` (`ad_id`),
    KEY `idx_leads_created` (`lead_created_at`),
    KEY `idx_leads_user_date` (`user_id`, `lead_created_at`),
    KEY `idx_leads_user_status` (`user_id`, `status_id`),
    CONSTRAINT `fk_leads_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_leads_form` FOREIGN KEY (`lead_form_id`) REFERENCES `lead_forms` (`id`) ON DELETE SET NULL,
    CONSTRAINT `fk_leads_status` FOREIGN KEY (`status_id`) REFERENCES `lead_statuses` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- Tabela: lead_activities
-- Historico de acoes nos leads
-- ----------------------------
CREATE TABLE IF NOT EXISTS `lead_activities` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `lead_id` BIGINT UNSIGNED NOT NULL,
    `user_id` INT UNSIGNED NULL COMMENT 'Usuario que fez a acao (null = sistema)',
    `action` VARCHAR(50) NOT NULL COMMENT 'Tipo: status_change, note_added, exported, created',
    `old_value` VARCHAR(255) NULL COMMENT 'Valor anterior',
    `new_value` VARCHAR(255) NULL COMMENT 'Novo valor',
    `metadata_json` JSON NULL COMMENT 'Dados adicionais',
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_lead_activities_lead` (`lead_id`),
    KEY `idx_lead_activities_created` (`created_at`),
    KEY `idx_lead_activities_action` (`action`),
    CONSTRAINT `fk_lead_activities_lead` FOREIGN KEY (`lead_id`) REFERENCES `leads` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_lead_activities_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- Tabela: lead_sync_jobs
-- Jobs de sincronizacao (polling)
-- ----------------------------
CREATE TABLE IF NOT EXISTS `lead_sync_jobs` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` INT UNSIGNED NOT NULL,
    `ad_account_id` VARCHAR(50) NULL COMMENT 'Conta especifica ou NULL para todas',
    `form_id` VARCHAR(50) NULL COMMENT 'Formulario especifico ou NULL para todos',
    `sync_type` ENUM('manual', 'scheduled', 'historical') NOT NULL DEFAULT 'manual',
    `status` ENUM('pending', 'running', 'completed', 'failed') NOT NULL DEFAULT 'pending',
    `leads_found` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Leads encontrados',
    `leads_created` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Novos leads criados',
    `leads_updated` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Leads atualizados',
    `last_lead_time` DATETIME NULL COMMENT 'Timestamp do ultimo lead para cursor',
    `error_message` TEXT NULL COMMENT 'Mensagem de erro se falhou',
    `started_at` DATETIME NULL,
    `completed_at` DATETIME NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_lead_sync_jobs_user` (`user_id`),
    KEY `idx_lead_sync_jobs_status` (`status`),
    KEY `idx_lead_sync_jobs_type` (`sync_type`),
    CONSTRAINT `fk_lead_sync_jobs_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- Inserir status padrao para usuarios existentes
-- ----------------------------

-- Status: Novo (padrao)
INSERT INTO `lead_statuses` (`user_id`, `name`, `slug`, `color`, `sort_order`, `is_default`, `is_final`)
SELECT
    u.id,
    'Novo',
    'novo',
    '#3b82f6',
    1,
    1,
    0
FROM `users` u
WHERE NOT EXISTS (
    SELECT 1 FROM `lead_statuses` ls
    WHERE ls.user_id = u.id AND ls.slug = 'novo'
);

-- Status: Em Contato
INSERT INTO `lead_statuses` (`user_id`, `name`, `slug`, `color`, `sort_order`, `is_default`, `is_final`)
SELECT
    u.id,
    'Em Contato',
    'em-contato',
    '#f59e0b',
    2,
    0,
    0
FROM `users` u
WHERE NOT EXISTS (
    SELECT 1 FROM `lead_statuses` ls
    WHERE ls.user_id = u.id AND ls.slug = 'em-contato'
);

-- Status: Qualificado
INSERT INTO `lead_statuses` (`user_id`, `name`, `slug`, `color`, `sort_order`, `is_default`, `is_final`)
SELECT
    u.id,
    'Qualificado',
    'qualificado',
    '#8b5cf6',
    3,
    0,
    0
FROM `users` u
WHERE NOT EXISTS (
    SELECT 1 FROM `lead_statuses` ls
    WHERE ls.user_id = u.id AND ls.slug = 'qualificado'
);

-- Status: Convertido (final)
INSERT INTO `lead_statuses` (`user_id`, `name`, `slug`, `color`, `sort_order`, `is_default`, `is_final`)
SELECT
    u.id,
    'Convertido',
    'convertido',
    '#10b981',
    4,
    0,
    1
FROM `users` u
WHERE NOT EXISTS (
    SELECT 1 FROM `lead_statuses` ls
    WHERE ls.user_id = u.id AND ls.slug = 'convertido'
);

-- Status: Descartado (final)
INSERT INTO `lead_statuses` (`user_id`, `name`, `slug`, `color`, `sort_order`, `is_default`, `is_final`)
SELECT
    u.id,
    'Descartado',
    'descartado',
    '#ef4444',
    5,
    0,
    1
FROM `users` u
WHERE NOT EXISTS (
    SELECT 1 FROM `lead_statuses` ls
    WHERE ls.user_id = u.id AND ls.slug = 'descartado'
);

-- ----------------------------
-- Trigger para criar status padrao para novos usuarios
-- ----------------------------
DELIMITER //

DROP TRIGGER IF EXISTS `trg_user_default_lead_statuses`//

CREATE TRIGGER `trg_user_default_lead_statuses`
AFTER INSERT ON `users`
FOR EACH ROW
BEGIN
    INSERT INTO `lead_statuses` (`user_id`, `name`, `slug`, `color`, `sort_order`, `is_default`, `is_final`) VALUES
    (NEW.id, 'Novo', 'novo', '#3b82f6', 1, 1, 0),
    (NEW.id, 'Em Contato', 'em-contato', '#f59e0b', 2, 0, 0),
    (NEW.id, 'Qualificado', 'qualificado', '#8b5cf6', 3, 0, 0),
    (NEW.id, 'Convertido', 'convertido', '#10b981', 4, 0, 1),
    (NEW.id, 'Descartado', 'descartado', '#ef4444', 5, 0, 1);
END//

DELIMITER ;

-- ----------------------------
-- Fim da migration 003
-- ----------------------------
