-- ============================================================
-- Patupelo Bot - Schema (Fase 1)
-- ============================================================
-- Diseñado para que Fases 2/3/4 sumen columnas sin migraciones
-- destructivas. Charset utf8mb4 por nombres con tildes/eñes.
-- ============================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ------------------------------------------------------------
-- bot_conversations
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `bot_conversations` (
    `id`                   BIGINT UNSIGNED  NOT NULL AUTO_INCREMENT,
    `channel`              ENUM('web','whatsapp','telegram') NOT NULL DEFAULT 'web',
    `external_id`          VARCHAR(128)     NOT NULL COMMENT 'session_id web o número WA E.164',
    `contact_name`         VARCHAR(160)     NULL,
    `contact_phone`        VARCHAR(32)      NULL,
    `contact_email`        VARCHAR(190)     NULL,
    `salon_name`           VARCHAR(190)     NULL,
    `is_existing_customer` TINYINT(1)       NOT NULL DEFAULT 0,
    `status`               ENUM('active','escalated','closed') NOT NULL DEFAULT 'active',
    `assigned_operator_id` INT UNSIGNED     NULL COMMENT 'Fase 4',
    `lead_score`           SMALLINT         NOT NULL DEFAULT 0,
    `metadata_json`        JSON             NULL,
    `created_at`           DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `last_message_at`      DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_channel_external` (`channel`, `external_id`),
    KEY `idx_status_lastmsg` (`status`, `last_message_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- bot_messages
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `bot_messages` (
    `id`               BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `conversation_id`  BIGINT UNSIGNED NOT NULL,
    `direction`        ENUM('in','out') NOT NULL,
    `body`             TEXT             NOT NULL,
    `intent_detected`  VARCHAR(64)      NULL,
    `confidence`       DECIMAL(4,3)     NULL COMMENT '0.000 a 1.000',
    `payload_json`     JSON             NULL COMMENT 'productos, botones, attachments',
    `created_at`       DATETIME         NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_conv_created` (`conversation_id`, `created_at`),
    CONSTRAINT `fk_msg_conv`
        FOREIGN KEY (`conversation_id`) REFERENCES `bot_conversations`(`id`)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- bot_intents (catálogo editable, sin tocar código)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `bot_intents` (
    `id`                       INT UNSIGNED   NOT NULL AUTO_INCREMENT,
    `name`                     VARCHAR(64)    NOT NULL,
    `description`              VARCHAR(255)   NULL,
    `keywords`                 TEXT           NULL COMMENT 'lista separada por comas',
    `patterns_json`            JSON           NULL COMMENT 'array de regex',
    `response_template`        TEXT           NULL,
    `requires_product_lookup`  TINYINT(1)     NOT NULL DEFAULT 0,
    `escalate`                 TINYINT(1)     NOT NULL DEFAULT 0,
    `priority`                 SMALLINT       NOT NULL DEFAULT 100 COMMENT 'menor = mayor prioridad',
    `is_active`                TINYINT(1)     NOT NULL DEFAULT 1,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_name` (`name`),
    KEY `idx_priority_active` (`priority`, `is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- bot_escalations
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `bot_escalations` (
    `id`                BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `conversation_id`   BIGINT UNSIGNED NOT NULL,
    `reason`            VARCHAR(64)     NOT NULL,
    `summary`           TEXT            NULL,
    `status`            ENUM('pending','taken','resolved') NOT NULL DEFAULT 'pending',
    `taken_by`          INT UNSIGNED    NULL,
    `created_at`        DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `resolved_at`       DATETIME        NULL,
    PRIMARY KEY (`id`),
    KEY `idx_status_created` (`status`, `created_at`),
    CONSTRAINT `fk_esc_conv`
        FOREIGN KEY (`conversation_id`) REFERENCES `bot_conversations`(`id`)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- bot_leads
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `bot_leads` (
    `id`               BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `conversation_id`  BIGINT UNSIGNED NOT NULL,
    `salon_name`       VARCHAR(190)    NULL,
    `contact_name`     VARCHAR(160)    NULL,
    `phone`            VARCHAR(32)     NULL,
    `email`            VARCHAR(190)    NULL,
    `cif`              VARCHAR(20)     NULL,
    `city`             VARCHAR(120)    NULL,
    `interests`        TEXT            NULL,
    `notes`            TEXT            NULL,
    `status`           ENUM('new','contacted','qualified','converted','discarded') NOT NULL DEFAULT 'new',
    `created_at`       DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_status_created` (`status`, `created_at`),
    CONSTRAINT `fk_lead_conv`
        FOREIGN KEY (`conversation_id`) REFERENCES `bot_conversations`(`id`)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- bot_product_cache
-- En Fase 1 se llena con seeds. En Fase 3 lo refresca un cron
-- desde Woo (precio/desc) + Odoo (stock real).
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `bot_product_cache` (
    `product_id`        BIGINT UNSIGNED NOT NULL,
    `sku`               VARCHAR(64)     NULL,
    `name`              VARCHAR(255)    NOT NULL,
    `brand`             VARCHAR(120)    NULL,
    `category`          VARCHAR(120)    NULL,
    `price`             DECIMAL(10,2)   NULL,
    `price_pro`         DECIMAL(10,2)   NULL COMMENT 'precio mayorista profesional',
    `stock_qty`         INT             NULL,
    `stock_status`      ENUM('instock','outofstock','onbackorder') NOT NULL DEFAULT 'instock',
    `short_description` TEXT            NULL,
    `permalink`         VARCHAR(500)    NULL,
    `image_url`         VARCHAR(500)    NULL,
    `keywords`          TEXT            NULL COMMENT 'términos extra para búsqueda',
    `updated_at`        DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`product_id`),
    KEY `idx_sku` (`sku`),
    KEY `idx_brand` (`brand`),
    KEY `idx_category` (`category`),
    FULLTEXT KEY `ft_search` (`name`, `brand`, `short_description`, `keywords`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;