-- =============================================================
--  ESQUEMA DE BASE DE DATOS — WhatsApp Mascotas TV
--  Importar directamente en phpMyAdmin sobre la BD ya existente
--  Servidor: publicamascotas.mysql.db
--  BD: publicamascotas
-- =============================================================

-- (No incluir CREATE DATABASE ni USE en OVH compartido)

-- Contactos (espectadores que escriben)
CREATE TABLE IF NOT EXISTS contacts (
    id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    wa_id         VARCHAR(20)  NOT NULL UNIQUE,   -- número de WhatsApp e.g. 34612345678
    display_name  VARCHAR(120) NOT NULL DEFAULT '',
    profile_name  VARCHAR(120) NOT NULL DEFAULT '',
    first_seen    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_seen     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    notes         TEXT,
    INDEX idx_wa_id (wa_id)
) ENGINE=InnoDB;

-- Mensajes entrantes y salientes
CREATE TABLE IF NOT EXISTS messages (
    id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    wa_message_id VARCHAR(100) NOT NULL UNIQUE,
    contact_id    INT UNSIGNED NOT NULL,
    direction     ENUM('in','out') NOT NULL,
    type          ENUM('text','image','video','audio','document','sticker','location','unknown') NOT NULL DEFAULT 'text',
    body          TEXT,                           -- texto del mensaje
    media_id      VARCHAR(100),                   -- ID de media en Meta
    media_mime    VARCHAR(80),
    media_local   VARCHAR(255),                   -- ruta local tras descarga
    media_caption TEXT,
    status        ENUM('received','read','sent','delivered','failed') NOT NULL DEFAULT 'received',
    created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    read_at       DATETIME,
    FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE CASCADE,
    INDEX idx_contact   (contact_id),
    INDEX idx_created   (created_at),
    INDEX idx_direction (direction),
    INDEX idx_status    (status)
) ENGINE=InnoDB;

-- Log de webhooks recibidos (para depuración)
CREATE TABLE IF NOT EXISTS webhook_log (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    received_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    payload    MEDIUMTEXT NOT NULL,
    processed  TINYINT(1) NOT NULL DEFAULT 0
) ENGINE=InnoDB;
