-- Schéma MySQL pour webhook_chat
-- Créer la base avant : CREATE DATABASE webhook_chat CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS messages (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    wa_message_id VARCHAR(255) NULL,
    direction VARCHAR(10) NOT NULL DEFAULT 'in',
    phone VARCHAR(32) NOT NULL,
    contact_name VARCHAR(255) NOT NULL DEFAULT '',
    message_type VARCHAR(32) NOT NULL DEFAULT 'text',
    body TEXT NOT NULL,
    reply_body TEXT NOT NULL DEFAULT '',
    matched_training_id INT UNSIGNED NULL,
    used_ai TINYINT(1) NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_messages_wa_id (wa_message_id),
    KEY idx_messages_phone (phone),
    KEY idx_messages_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS training (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    keywords TEXT NOT NULL,
    response TEXT NOT NULL,
    priority INT NOT NULL DEFAULT 0,
    enabled TINYINT(1) NOT NULL DEFAULT 1,
    hit_count INT NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS settings (
    `key` VARCHAR(191) NOT NULL PRIMARY KEY,
    value TEXT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS stats_daily (
    date DATE NOT NULL,
    metric VARCHAR(64) NOT NULL,
    value INT NOT NULL DEFAULT 0,
    PRIMARY KEY (date, metric)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS conversation_state (
    phone VARCHAR(32) NOT NULL PRIMARY KEY,
    human_mode TINYINT(1) NOT NULL DEFAULT 0,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;