-- =====================================================================
-- schema.sql — ruleaza o data in phpMyAdmin (cPanel), pe baza ta de date.
-- =====================================================================

-- Memoria conversatiei (separata per canal + utilizator)
CREATE TABLE IF NOT EXISTS messages (
    id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    channel    VARCHAR(20)               NOT NULL,   -- 'instagram' sau 'page'
    user_ref   VARCHAR(64)               NOT NULL,   -- IGSID / PSID
    role       ENUM('user','assistant')  NOT NULL,
    content    MEDIUMTEXT                NOT NULL,
    mid        VARCHAR(190)              NULL,        -- id mesaj Meta (dedupe)
    created_at TIMESTAMP                 DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_conv (channel, user_ref, id),
    UNIQUE KEY uniq_mid (mid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Starea de escaladare (cat timp tace botul pentru o conversatie)
CREATE TABLE IF NOT EXISTS handoffs (
    id        BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    channel   VARCHAR(20)  NOT NULL,
    user_ref  VARCHAR(64)  NOT NULL,
    until_ts  DATETIME     NOT NULL,
    UNIQUE KEY uniq_conv (channel, user_ref)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;