-- =============================================================================
-- 06 — CRM ENGAGEMENT: campañas, segmentos, eventos, recordatorios
-- =============================================================================

-- Segmentos de clientes (reglas dinámicas)
CREATE TABLE IF NOT EXISTS segmentos (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    nombre          VARCHAR(80) NOT NULL,
    descripcion     TEXT,
    -- Reglas en JSON: {"min_pedidos_90d":6, "max_dias_ultimo_pedido":30, ...}
    reglas_json     JSON NOT NULL,
    color           VARCHAR(20),
    es_sistema      TINYINT DEFAULT 0,        -- 1 si lo crea el sistema (nuevo, vip, etc.)
    fecha_alta      TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO segmentos (nombre, descripcion, reglas_json, es_sistema, color) VALUES
    ('Nuevo',       '0 o 1 pedido total',                                       '{"max_pedidos_total":1}',                          1, '#3b82f6'),
    ('Ocasional',   '2-5 pedidos en últimos 90 días',                           '{"min_pedidos_90d":2,"max_pedidos_90d":5}',        1, '#a78bfa'),
    ('Frecuente',   '6+ pedidos en últimos 90 días',                            '{"min_pedidos_90d":6}',                           1, '#10b981'),
    ('VIP',         'Top 10% por gasto o frecuencia',                           '{"top_percent_gasto_90d":10}',                    1, '#f59e0b'),
    ('En riesgo',   'Era frecuente pero hace 30+ días sin pedir',               '{"was_frecuente":true,"min_dias_ultimo_pedido":30}', 1, '#ef4444'),
    ('Perdido',     '90+ días sin pedir',                                       '{"min_dias_ultimo_pedido":90}',                   1, '#6b7280');

-- Asignación de cliente a segmentos (un cliente puede estar en varios)
CREATE TABLE IF NOT EXISTS clientes_segmentos (
    id_cliente      INT NOT NULL,
    id_segmento     INT NOT NULL,
    fecha_entrada   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id_cliente, id_segmento),
    FOREIGN KEY (id_cliente)  REFERENCES clientes(id)  ON DELETE CASCADE,
    FOREIGN KEY (id_segmento) REFERENCES segmentos(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Campañas WhatsApp masivas (templates aprobados por Meta)
CREATE TABLE IF NOT EXISTS campanas (
    id                  INT AUTO_INCREMENT PRIMARY KEY,
    nombre              VARCHAR(120) NOT NULL,
    descripcion         TEXT,
    id_segmento         INT NULL,                -- a quién va
    template_sid        VARCHAR(40),             -- template Twilio aprobado
    template_friendly_name VARCHAR(80),
    variables_json      JSON,                    -- variables del template
    mensaje_preview     TEXT,                    -- para que el dueño vea cómo queda
    programada_para     DATETIME,
    estado              ENUM('borrador','programada','enviando','enviada','cancelada','pausada') DEFAULT 'borrador',
    enviados            INT DEFAULT 0,
    entregados          INT DEFAULT 0,
    leidos              INT DEFAULT 0,
    respondieron        INT DEFAULT 0,
    convirtieron        INT DEFAULT 0,           -- hicieron pedido en las 24h post envío
    monto_atribuido     DECIMAL(12,2) DEFAULT 0,  -- $ generado por la campaña
    fecha_alta          TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    fecha_iniciada      DATETIME NULL,
    fecha_finalizada    DATETIME NULL,
    id_autor            INT NULL,
    FOREIGN KEY (id_segmento) REFERENCES segmentos(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Envíos individuales de cada campaña (1 fila por cliente alcanzado)
CREATE TABLE IF NOT EXISTS campanas_envios (
    id               INT AUTO_INCREMENT PRIMARY KEY,
    id_campana       INT NOT NULL,
    id_cliente       INT NOT NULL,
    twilio_message_sid VARCHAR(40),
    status           VARCHAR(40) DEFAULT 'pendiente',  -- pendiente/enviado/entregado/leido/respondido/convertido/fallido
    error            TEXT,
    sent_at          DATETIME,
    delivered_at     DATETIME,
    read_at          DATETIME,
    replied_at       DATETIME,
    converted_at     DATETIME,
    monto_pedido     DECIMAL(10,2),
    FOREIGN KEY (id_campana) REFERENCES campanas(id) ON DELETE CASCADE,
    FOREIGN KEY (id_cliente) REFERENCES clientes(id) ON DELETE CASCADE,
    INDEX (id_campana, status),
    INDEX (id_cliente, sent_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Eventos del cliente (timeline de todo lo que pasa con él — útil para CRM)
CREATE TABLE IF NOT EXISTS eventos_cliente (
    id           INT AUTO_INCREMENT PRIMARY KEY,
    id_cliente   INT NOT NULL,
    tipo_evento  VARCHAR(40),
        -- 'primer_pedido', 'reorden', 'queja', 'campana_enviada', 'campana_convertida',
        -- 'cambio_segmento', 'cumpleanos', 'recordatorio_enviado', etc.
    payload_json JSON,
    monto        DECIMAL(10,2) NULL,
    fecha        TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (id_cliente) REFERENCES clientes(id) ON DELETE CASCADE,
    INDEX (id_cliente, fecha)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Recordatorios automáticos programados (ej. "feliz cumple", "te extrañamos", etc.)
CREATE TABLE IF NOT EXISTS recordatorios_reglas (
    id             INT AUTO_INCREMENT PRIMARY KEY,
    nombre         VARCHAR(120) NOT NULL,
    descripcion    TEXT,
    trigger_tipo   ENUM('cumpleanos','dias_sin_pedir','post_pedido','dia_semana','programado') NOT NULL,
    trigger_valor  VARCHAR(80),         -- ej. para 'dias_sin_pedir': '30'; para 'dia_semana': 'viernes 18:00'
    id_segmento    INT NULL,            -- a qué segmento aplica
    template_sid   VARCHAR(40),
    activo         TINYINT DEFAULT 1,
    ultimo_disparo DATETIME NULL,
    FOREIGN KEY (id_segmento) REFERENCES segmentos(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
