-- ============================================================================
-- SENDGRID MARKETING ORCHESTRATION - SCHEMA DATABASE
-- ============================================================================

-- Base de datos
CREATE DATABASE IF NOT EXISTS sendgrid_marketing;
USE sendgrid_marketing;

-- ============================================================================
-- TABLA 1: CONTACTOS (sincronizados desde SendGrid)
-- ============================================================================
CREATE TABLE IF NOT EXISTS contacts (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    unsubscribed TINYINT(1) DEFAULT 0 COMMENT '1=desuscrito, 0=suscrito',
    status ENUM('active', 'inactive', 'bounced') DEFAULT 'active',
    list_ids JSON COMMENT 'IDs de listas en SendGrid',
    custom_fields JSON COMMENT 'Campos personalizados',
    created_at TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    synced_from_sendgrid TINYINT(1) DEFAULT 1,
    
    INDEX idx_email (email),
    INDEX idx_unsubscribed (unsubscribed),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- TABLA 2: LISTAS DE MARKETING (creadas desde PHP)
-- ============================================================================
CREATE TABLE IF NOT EXISTS marketing_lists (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    sendgrid_list_id VARCHAR(255) NOT NULL UNIQUE COMMENT 'ID de lista en SendGrid',
    list_name VARCHAR(255) NOT NULL,
    domain VARCHAR(100) COMMENT 'Dominio de email (@gmail.com, @hotmail.com, etc)',
    contact_count INT DEFAULT 0,
    emails JSON COMMENT 'Emails de contactos en esta lista',
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_sendgrid_list_id (sendgrid_list_id),
    INDEX idx_domain (domain),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- TABLA 3: CAMPAÑAS DE MARKETING
-- ============================================================================
CREATE TABLE IF NOT EXISTS marketing_campaigns (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    sendgrid_campaign_id VARCHAR(255) NOT NULL UNIQUE COMMENT 'ID de campaña en SendGrid',
    list_id VARCHAR(255) NOT NULL COMMENT 'Referencia a marketing_lists.sendgrid_list_id',
    design_id VARCHAR(255) NOT NULL COMMENT 'ID del design duplicado en SendGrid',
    subject VARCHAR(255),
    send_at DATETIME NOT NULL COMMENT 'Fecha/hora de envío programada',
    contact_count INT DEFAULT 0,
    status ENUM('draft', 'scheduled', 'sent', 'paused', 'cancelled') DEFAULT 'draft',
    sent_at DATETIME COMMENT 'Fecha/hora de envío real',
    opens INT DEFAULT 0,
    clicks INT DEFAULT 0,
    bounces INT DEFAULT 0,
    unsubscribes INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_sendgrid_campaign_id (sendgrid_campaign_id),
    INDEX idx_status (status),
    INDEX idx_send_at (send_at),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- TABLA 4: EVENTOS DE SENDGRID (webhooks)
-- ============================================================================
CREATE TABLE IF NOT EXISTS sendgrid_events (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    campaign_id VARCHAR(255),
    email VARCHAR(255),
    event_type VARCHAR(50) COMMENT 'open, click, bounce, unsubscribe, delivered, dropped',
    event_data JSON,
    timestamp DATETIME,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_campaign_id (campaign_id),
    INDEX idx_email (email),
    INDEX idx_event_type (event_type),
    INDEX idx_timestamp (timestamp)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- TABLA 5: DESIGNS (registro de designs duplicados)
-- ============================================================================
CREATE TABLE IF NOT EXISTS designs (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    sendgrid_design_id VARCHAR(255) NOT NULL UNIQUE,
    prototype_design_id VARCHAR(255) COMMENT 'ID del design prototipo original',
    design_name VARCHAR(255),
    html_content LONGTEXT,
    subject VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_sendgrid_design_id (sendgrid_design_id),
    INDEX idx_prototype_design_id (prototype_design_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- TABLA 6: LOGS DE EJECUCIÓN
-- ============================================================================
CREATE TABLE IF NOT EXISTS execution_logs (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    process_name VARCHAR(255),
    process_type VARCHAR(50) COMMENT 'sync, segment, campaign_create, send',
    status ENUM('started', 'completed', 'failed') DEFAULT 'started',
    total_processed INT DEFAULT 0,
    successful INT DEFAULT 0,
    failed INT DEFAULT 0,
    error_message TEXT,
    started_at DATETIME,
    completed_at DATETIME,
    duration_seconds INT,
    metadata JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_process_name (process_name),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- TABLA 7: CONFIGURACIÓN Y ESTADO
-- ============================================================================
CREATE TABLE IF NOT EXISTS system_config (
    id INT AUTO_INCREMENT PRIMARY KEY,
    config_key VARCHAR(255) UNIQUE,
    config_value LONGTEXT,
    description TEXT,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_config_key (config_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Inserciones iniciales de configuración
INSERT IGNORE INTO system_config (config_key, config_value, description) VALUES 
('prototype_design_id', '', 'ID del design prototipo en SendGrid'),
('last_contact_sync', '0', 'Timestamp del último sync de contactos'),
('last_campaign_run', '0', 'Timestamp de la última ejecución de campañas'),
('sendgrid_api_valid', '0', 'Validación de API Key (0=no validado, 1=válido)');

-- ============================================================================
-- VISTAS ÚTILES
-- ============================================================================

-- Vista: Resumen de campañas próximas
CREATE OR REPLACE VIEW upcoming_campaigns AS
SELECT 
    mc.id,
    mc.sendgrid_campaign_id,
    mc.subject,
    ml.list_name,
    ml.domain,
    mc.contact_count,
    mc.send_at,
    TIMESTAMPDIFF(MINUTE, NOW(), mc.send_at) as minutes_until_send,
    mc.status
FROM marketing_campaigns mc
JOIN marketing_lists ml ON mc.list_id = ml.sendgrid_list_id
WHERE mc.status = 'scheduled' AND mc.send_at > NOW()
ORDER BY mc.send_at ASC;

-- Vista: Estadísticas por dominio
CREATE OR REPLACE VIEW stats_by_domain AS
SELECT 
    ml.domain,
    COUNT(DISTINCT mc.id) as total_campaigns,
    SUM(mc.contact_count) as total_contacts,
    SUM(mc.opens) as total_opens,
    SUM(mc.clicks) as total_clicks,
    SUM(mc.bounces) as total_bounces,
    COUNT(CASE WHEN mc.status = 'sent' THEN 1 END) as sent_campaigns
FROM marketing_lists ml
LEFT JOIN marketing_campaigns mc ON ml.sendgrid_list_id = mc.list_id
GROUP BY ml.domain
ORDER BY total_contacts DESC;

-- Vista: Contactos por estado
CREATE OR REPLACE VIEW contacts_summary AS
SELECT 
    COUNT(*) as total,
    SUM(CASE WHEN unsubscribed = 0 THEN 1 ELSE 0 END) as active,
    SUM(CASE WHEN unsubscribed = 1 THEN 1 ELSE 0 END) as unsubscribed,
    SUM(CASE WHEN status = 'bounced' THEN 1 ELSE 0 END) as bounced
FROM contacts;

-- ============================================================================
-- COMENTARIOS DE TABLAS
-- ============================================================================

ALTER TABLE contacts COMMENT = 'Contactos sincronizados desde SendGrid';
ALTER TABLE marketing_lists COMMENT = 'Listas creadas respetando límites (1000 max, 100 por dominio)';
ALTER TABLE marketing_campaigns COMMENT = 'Campañas programadas automáticamente';
ALTER TABLE sendgrid_events COMMENT = 'Eventos de SendGrid recibidos vía webhooks';
ALTER TABLE designs COMMENT = 'Designs duplicados del prototipo';
ALTER TABLE execution_logs COMMENT = 'Log de todas las ejecuciones del sistema';
ALTER TABLE system_config COMMENT = 'Configuración del sistema y estado actual';

-- ============================================================================
-- PERMISOS (ajustar según tu usuario)
-- ============================================================================
-- GRANT ALL PRIVILEGES ON sendgrid_marketing.* TO 'usuario_mysql'@'localhost';
-- FLUSH PRIVILEGES;