-- Migration: 0002_create_ai_engine_tables
-- Phase 3 (AI Engine).

-- Lightweight state machine on the user row: lets the Telegram layer
-- know whether the user's next free-text message is a new chat
-- question or a ✅/❌ confirmation reply, without any in-memory
-- session (the bot is stateless per HTTP request).
ALTER TABLE users
    ADD COLUMN conversation_state VARCHAR(32) NOT NULL DEFAULT 'idle' AFTER is_profile_complete;

-- Model catalog. Model names are NEVER hardcoded in PHP — the
-- Decision Engine always reads the active model for a tier from
-- this table. credit_multiplier = how many wallet credits are
-- debited per underlying AI token consumed by that model (more
-- capable models cost more credits per token).
CREATE TABLE IF NOT EXISTS ai_models (
    id                  BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    provider            VARCHAR(32)  NOT NULL DEFAULT 'avalai',
    model_name          VARCHAR(128) NOT NULL,
    tier                ENUM('cheap', 'medium', 'powerful') NOT NULL,
    credit_multiplier   DECIMAL(6, 3) NOT NULL DEFAULT 1.000,
    is_active           TINYINT(1)   NOT NULL DEFAULT 1,
    created_at          DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,

    KEY idx_ai_models_tier_active (tier, is_active)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

-- Seed a sensible default per tier so the bot is usable immediately
-- after install; the Admin panel (Phase 5) lets these be changed
-- without touching code or redeploying.
INSERT INTO ai_models (provider, model_name, tier, credit_multiplier, is_active) VALUES
    ('avalai', 'gpt-4o-mini',    'cheap',    1.000, 1),
    ('avalai', 'gpt-4o',         'medium',   3.000, 1),
    ('avalai', 'gpt-4.1',        'powerful', 6.000, 1);

-- One row per user: token balance + today's free-quota usage.
CREATE TABLE IF NOT EXISTS credit_accounts (
    user_id                   BIGINT UNSIGNED PRIMARY KEY,
    balance_tokens            BIGINT UNSIGNED NOT NULL DEFAULT 0,
    free_requests_used_today  INT UNSIGNED NOT NULL DEFAULT 0,
    free_requests_date        DATE NULL,
    updated_at                DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    CONSTRAINT fk_credit_accounts_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

-- Long-term conversation memory (Memory Manager reads/writes here;
-- Context Manager decides how much of it enters a given prompt).
CREATE TABLE IF NOT EXISTS conversation_messages (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id     BIGINT UNSIGNED NOT NULL,
    role        ENUM('user', 'assistant', 'system') NOT NULL,
    content     LONGTEXT NOT NULL,
    tokens      INT UNSIGNED NULL,
    created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    KEY idx_conversation_messages_user_created (user_id, created_at),
    CONSTRAINT fk_conversation_messages_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

-- Doubles as (a) the pending-confirmation record the Telegram layer
-- checks against a ✅/❌ reply, and (b) the permanent operations log
-- required by the project spec (date, model, tokens consumed,
-- operation type, status) — a completed row IS the log entry, so
-- there is no separate table to keep in sync.
CREATE TABLE IF NOT EXISTS ai_operations (
    id                      BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id                 BIGINT UNSIGNED NOT NULL,
    ai_model_id             BIGINT UNSIGNED NOT NULL,
    operation_type          VARCHAR(32) NOT NULL,
    user_message            LONGTEXT NULL,
    estimated_input_tokens  INT UNSIGNED NOT NULL,
    estimated_output_tokens INT UNSIGNED NOT NULL,
    actual_input_tokens     INT UNSIGNED NULL,
    actual_output_tokens    INT UNSIGNED NULL,
    credits_charged         INT UNSIGNED NULL,
    used_free_quota         TINYINT(1)  NOT NULL DEFAULT 0,
    status                  ENUM('pending', 'completed', 'failed', 'cancelled') NOT NULL DEFAULT 'pending',
    created_at              DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    completed_at            DATETIME NULL,

    KEY idx_ai_operations_user_status (user_id, status),
    CONSTRAINT fk_ai_operations_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE,
    CONSTRAINT fk_ai_operations_model FOREIGN KEY (ai_model_id) REFERENCES ai_models (id)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;