-- ============================================================================
-- ACTUALIZACIÓN SCHEMA: SOPORTE MULTI-EMPRESA
-- ============================================================================
-- Ejecutar estos comandos ADEMÁS del 05-database-schema.sql

-- Agregar columna company_name a tablas existentes

-- 1. Actualizar tabla CONTACTS
ALTER TABLE contacts ADD COLUMN company_name VARCHAR(100) DEFAULT 'LINDERLAKE' AFTER email;
CREATE INDEX idx_company_name ON contacts(company_name);
CREATE INDEX idx_company_status ON contacts(company_name, unsubscribed, status);

-- 2. Actualizar tabla MARKETING_LISTS
ALTER TABLE marketing_lists ADD COLUMN company_name VARCHAR(100) DEFAULT 'LINDERLAKE' AFTER list_name;
CREATE INDEX idx_list_company ON marketing_lists(company_name);
CREATE INDEX idx_list_company_name ON marketing_lists(company_name, list_name);

-- 3. Actualizar tabla MARKETING_CAMPAIGNS
ALTER TABLE marketing_campaigns ADD COLUMN company_name VARCHAR(100) DEFAULT 'LINDERLAKE' AFTER subject;
CREATE INDEX idx_campaign_company ON marketing_campaigns(company_name);
CREATE INDEX idx_campaign_company_status ON marketing_campaigns(company_name, status);

-- 4. Crear nueva tabla: COMPANY_LIST_MAPPING
-- (Mapea qué listas pertenecen a qué empresa en SendGrid)
CREATE TABLE IF NOT EXISTS company_list_mapping (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    company VARCHAR(100) NOT NULL,
    list_id VARCHAR(255) NOT NULL UNIQUE,
    list_name VARCHAR(255),
    list_pattern VARCHAR(255) COMMENT 'Patrón usado para detectar la empresa (ej: BCS_%)',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_company (company),
    INDEX idx_list_id (list_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 5. Crear nueva tabla: COMPANY_CONFIG
-- (Configuración de cada empresa)
CREATE TABLE IF NOT EXISTS company_config (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    company_name VARCHAR(100) UNIQUE NOT NULL,
    display_name VARCHAR(255),
    unsubscribe_group_id VARCHAR(255),
    from_email VARCHAR(255),
    from_name VARCHAR(255),
    reply_to VARCHAR(255),
    active TINYINT(1) DEFAULT 1,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_company_name (company_name),
    INDEX idx_active (active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insertar configuración de empresas por defecto
INSERT IGNORE INTO company_config (company_name, display_name, from_email, from_name, active) VALUES
('LINDERLAKE', 'Linderlake Newsletter', 'newsletter@linderlake.com', 'Linderlake', 1),
('BCS', 'BCS Communications', 'communications@bcs.com', 'BCS', 1);

-- 6. Crear vista: CONTACTS_BY_COMPANY
-- (Para ver fácilmente contactos segmentados por empresa)
CREATE OR REPLACE VIEW contacts_by_company AS
SELECT 
    company_name,
    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
GROUP BY company_name;

-- 7. Crear vista: CAMPAIGNS_BY_COMPANY
-- (Para ver campañas por empresa)
CREATE OR REPLACE VIEW campaigns_by_company AS
SELECT 
    company_name,
    COUNT(*) as total_campaigns,
    SUM(CASE WHEN status = 'scheduled' THEN 1 ELSE 0 END) as scheduled,
    SUM(CASE WHEN status = 'sent' THEN 1 ELSE 0 END) as sent,
    SUM(CASE WHEN status = 'paused' THEN 1 ELSE 0 END) as paused,
    SUM(contact_count) as total_contacts
FROM marketing_campaigns
GROUP BY company_name;

-- 8. Crear vista: SAFETY_CHECK
-- (Validar que no hay duplicados entre empresas)
CREATE OR REPLACE VIEW safety_check_duplicates AS
SELECT 
    email,
    COUNT(*) as company_count,
    GROUP_CONCAT(DISTINCT company_name) as companies
FROM contacts
GROUP BY email
HAVING COUNT(*) > 1;

-- 9. Crear vista: CONTACTS_MULTI_COMPANY
-- (Detectar si un contacto está en múltiples empresas - RIESGO)
CREATE OR REPLACE VIEW contacts_multi_company AS
SELECT 
    c.email,
    c.company_name,
    COUNT(DISTINCT l.company_name) as other_companies,
    GROUP_CONCAT(DISTINCT l.company_name SEPARATOR ', ') as in_companies
FROM contacts c
LEFT JOIN marketing_lists l ON c.email IN (
    SELECT JSON_UNQUOTE(JSON_EXTRACT(emails, CONCAT('$[', i, ']')))
    FROM (
        SELECT 0 as i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
        UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
    ) numbers
    WHERE JSON_UNQUOTE(JSON_EXTRACT(emails, CONCAT('$[', i, ']'))) IS NOT NULL
)
WHERE l.company_name != c.company_name
GROUP BY c.email, c.company_name
HAVING COUNT(DISTINCT l.company_name) > 0;

-- ============================================================================
-- COMENTARIOS
-- ============================================================================

ALTER TABLE contacts COMMENT = 'Contactos sincronizados de SendGrid con empresa asignada';
ALTER TABLE marketing_lists COMMENT = 'Listas creadas en SendGrid segmentadas por empresa';
ALTER TABLE marketing_campaigns COMMENT = 'Campañas programadas con empresa asignada';
ALTER TABLE company_list_mapping COMMENT = 'Mapeo de listas SendGrid a empresas (para detectar automáticamente)';
ALTER TABLE company_config COMMENT = 'Configuración específica de cada empresa';

-- ============================================================================
-- ALERTAS IMPORTANTES
-- ============================================================================

/*
VALIDACIONES DE SEGURIDAD CLAVE:

1. Ver duplicados entre empresas:
   SELECT * FROM safety_check_duplicates;
   
2. Ver contactos que pueden estar en múltiples empresas:
   SELECT * FROM contacts_multi_company;
   
3. Contar contactos por empresa:
   SELECT * FROM contacts_by_company;
   
4. Ver campañas por empresa:
   SELECT * FROM campaigns_by_company;
   
5. Validar que NO hay campañas cruzadas:
   SELECT c.company_name, mc.company_name, COUNT(*)
   FROM marketing_campaigns mc
   JOIN contacts c ON mc.list_id IN (...)
   WHERE c.company_name != mc.company_name
   GROUP BY c.company_name, mc.company_name;
*/

-- ============================================================================
-- PERMISOS RECOMENDADOS
-- ============================================================================

/*
Si usas múltiples usuarios de MySQL, crea uno por empresa:

CREATE USER 'linderlake_user'@'localhost' IDENTIFIED BY 'strong_password';
GRANT SELECT, INSERT, UPDATE ON sendgrid_marketing.contacts TO 'linderlake_user'@'localhost'
  WHERE company_name = 'LINDERLAKE';
GRANT SELECT, INSERT, UPDATE ON sendgrid_marketing.marketing_lists TO 'linderlake_user'@'localhost'
  WHERE company_name = 'LINDERLAKE';
GRANT SELECT, INSERT, UPDATE ON sendgrid_marketing.marketing_campaigns TO 'linderlake_user'@'localhost'
  WHERE company_name = 'LINDERLAKE';
FLUSH PRIVILEGES;
*/

-- ============================================================================
-- DATOS DE EJEMPLO (OPCIONAL)
-- ============================================================================

-- Ver configuración de empresas
SELECT * FROM company_config WHERE active = 1;

-- Ver resumen de contactos por empresa
SELECT * FROM contacts_by_company;

-- Ver resumen de campañas por empresa
SELECT * FROM campaigns_by_company;