-- =============================================================================
-- 02 — CLIENTES: ficha completa + direcciones + scores + segmentación
-- =============================================================================

CREATE TABLE IF NOT EXISTS clientes (
    id                  INT AUTO_INCREMENT PRIMARY KEY,
    nombre              VARCHAR(120) NOT NULL,
    telefono_whatsapp   VARCHAR(25) UNIQUE NOT NULL,   -- +5491148999778
    whatsapp_verificado TINYINT DEFAULT 0,
    fecha_alta          TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    fecha_cumpleanos    DATE NULL,
    email               VARCHAR(150) NULL,
    notas_internas      TEXT,           -- "alergico mani", "ojo con la dirección 2", etc.
    bloqueado           TINYINT DEFAULT 0,    -- cliente problemático que no atender
    motivo_bloqueo      VARCHAR(255),
    eliminado           TINYINT DEFAULT 0,
    INDEX idx_telefono (telefono_whatsapp),
    INDEX idx_nombre (nombre)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Direcciones del cliente (un cliente puede tener varias)
CREATE TABLE IF NOT EXISTS clientes_direcciones (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    id_cliente      INT NOT NULL,
    alias           VARCHAR(50) NULL,        -- "Casa", "Oficina", "Casa de papá"
    direccion       VARCHAR(255) NOT NULL,
    detalle         VARCHAR(255) NULL,        -- "Piso 3 Dpto B"
    latitud         DECIMAL(10,7) NULL,
    longitud        DECIMAL(10,7) NULL,
    id_zona_delivery INT NULL,                -- referencia a tabla de zonas (ver pedidos.sql)
    es_principal    TINYINT DEFAULT 0,
    fecha_alta      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (id_cliente) REFERENCES clientes(id) ON DELETE CASCADE,
    INDEX (id_cliente)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Etiquetas (segmentos automáticos + manuales)
CREATE TABLE IF NOT EXISTS etiquetas (
    id     INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(40) UNIQUE NOT NULL,    -- "VIP", "frecuente", "vegano", "alergico_mani", etc.
    color  VARCHAR(20) NULL,
    automatica TINYINT DEFAULT 0           -- 1 = la asigna el sistema, 0 = manual
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS clientes_etiquetas (
    id_cliente   INT NOT NULL,
    id_etiqueta  INT NOT NULL,
    fecha        TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id_cliente, id_etiqueta),
    FOREIGN KEY (id_cliente)  REFERENCES clientes(id)  ON DELETE CASCADE,
    FOREIGN KEY (id_etiqueta) REFERENCES etiquetas(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Scores que se calculan periódicamente (cron diario)
CREATE TABLE IF NOT EXISTS clientes_scores (
    id_cliente     INT PRIMARY KEY,
    score_freq     TINYINT,   -- 0-100
    score_valor    TINYINT,
    score_lealtad  TINYINT,
    score_total    TINYINT,
    segmento       VARCHAR(40),    -- 'nuevo','ocasional','frecuente','vip','en_riesgo','perdido'
    pedidos_90d    INT,
    monto_90d      DECIMAL(12,2),
    dias_ultimo_pedido INT,
    updated_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (id_cliente) REFERENCES clientes(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Cache de preferencias del cliente (pizza más pedida, último gasto, etc.)
CREATE TABLE IF NOT EXISTS clientes_preferencias (
    id_cliente            INT PRIMARY KEY,
    producto_favorito_id  INT NULL,
    pizza_top_3_json      JSON NULL,        -- top 3 productos por frecuencia
    horario_pico          TIME NULL,         -- a qué hora suele pedir
    dia_pico              TINYINT NULL,      -- 0=domingo, 6=sabado
    ticket_promedio       DECIMAL(10,2),
    forma_pago_preferida  VARCHAR(40),
    direccion_principal_id INT NULL,
    updated_at            TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (id_cliente) REFERENCES clientes(id) ON DELETE CASCADE,
    FOREIGN KEY (producto_favorito_id) REFERENCES productos(id),
    FOREIGN KEY (direccion_principal_id) REFERENCES clientes_direcciones(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Notas manuales del restaurante sobre el cliente (libre)
CREATE TABLE IF NOT EXISTS clientes_notas (
    id           INT AUTO_INCREMENT PRIMARY KEY,
    id_cliente   INT NOT NULL,
    nota         TEXT NOT NULL,
    id_autor     INT NULL,           -- empleado que la escribió
    fecha        TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (id_cliente) REFERENCES clientes(id) ON DELETE CASCADE,
    INDEX (id_cliente, fecha)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
