use sea_orm_migration::prelude::*;
use sea_orm_migration::sea_orm::ConnectionTrait;

#[derive(DeriveMigrationName)]
pub struct Migration;

#[async_trait::async_trait]
impl MigrationTrait for Migration {
    async fn up(&self, manager: &SchemaManager) -> Result<(), DbErr> {
        let backend = manager.get_database_backend();
        let conn = manager.get_connection();

        let sql = match backend {
            sea_orm::DatabaseBackend::Postgres => POSTGRES_UP,
            sea_orm::DatabaseBackend::Sqlite => SQLITE_UP,
            sea_orm::DatabaseBackend::MySql => {
                return Err(DbErr::Migration("MySQL not supported for mini-chat".into()));
            }
        };

        conn.execute_unprepared(sql).await?;
        Ok(())
    }

    async fn down(&self, manager: &SchemaManager) -> Result<(), DbErr> {
        let conn = manager.get_connection();
        conn.execute_unprepared(DOWN).await?;
        Ok(())
    }
}

const DOWN: &str = r"
DROP TABLE IF EXISTS message_reactions;
DROP TABLE IF EXISTS quota_usage;
DROP TABLE IF EXISTS chat_vector_stores;
DROP TABLE IF EXISTS thread_summaries;
DROP TABLE IF EXISTS message_attachments;
DROP TABLE IF EXISTS attachments;
DROP TABLE IF EXISTS chat_turns;
DROP TABLE IF EXISTS messages;
DROP TABLE IF EXISTS chats;
";

const POSTGRES_UP: &str = r"
-- 1. chats
CREATE TABLE IF NOT EXISTS chats (
    id          UUID PRIMARY KEY NOT NULL,
    tenant_id   UUID NOT NULL,
    user_id     UUID NOT NULL,
    model       VARCHAR(1024) NOT NULL,
    title       VARCHAR(255),
    is_temporary BOOLEAN NOT NULL DEFAULT FALSE,
    created_at  TIMESTAMPTZ NOT NULL,
    updated_at  TIMESTAMPTZ NOT NULL,
    deleted_at  TIMESTAMPTZ
);
CREATE INDEX IF NOT EXISTS idx_chats_tenant_user_updated
    ON chats (tenant_id, user_id, updated_at DESC)
    WHERE deleted_at IS NULL;

-- 2. messages
CREATE TABLE IF NOT EXISTS messages (
    id                  UUID PRIMARY KEY NOT NULL,
    tenant_id           UUID NOT NULL,
    chat_id             UUID NOT NULL REFERENCES chats(id) ON DELETE CASCADE,
    request_id          UUID,
    role                VARCHAR(16) NOT NULL,
    content             TEXT NOT NULL DEFAULT '',
    content_type        VARCHAR(32) NOT NULL DEFAULT 'text',
    token_estimate      INT NOT NULL DEFAULT 0 CHECK (token_estimate >= 0),
    provider_response_id VARCHAR(128),
    request_kind        VARCHAR(16),
    features_used       JSONB NOT NULL DEFAULT '[]',
    input_tokens        BIGINT NOT NULL DEFAULT 0 CHECK (input_tokens >= 0),
    output_tokens       BIGINT NOT NULL DEFAULT 0 CHECK (output_tokens >= 0),
    cache_read_input_tokens  BIGINT NOT NULL DEFAULT 0 CHECK (cache_read_input_tokens >= 0),
    cache_write_input_tokens BIGINT NOT NULL DEFAULT 0 CHECK (cache_write_input_tokens >= 0),
    reasoning_tokens         BIGINT NOT NULL DEFAULT 0 CHECK (reasoning_tokens >= 0),
    model               VARCHAR(1024),
    is_compressed       BOOLEAN NOT NULL DEFAULT FALSE,
    created_at          TIMESTAMPTZ NOT NULL,
    deleted_at          TIMESTAMPTZ
);
CREATE UNIQUE INDEX IF NOT EXISTS idx_messages_chat_request_role
    ON messages (chat_id, request_id, role)
    WHERE request_id IS NOT NULL AND deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_messages_chat_created
    ON messages (chat_id, created_at)
    WHERE deleted_at IS NULL;
CREATE UNIQUE INDEX IF NOT EXISTS idx_messages_id_chat_id
    ON messages (id, chat_id);

-- 3. chat_turns
CREATE TABLE IF NOT EXISTS chat_turns (
    id                          UUID PRIMARY KEY NOT NULL,
    tenant_id                   UUID NOT NULL,
    chat_id                     UUID NOT NULL REFERENCES chats(id) ON DELETE CASCADE,
    request_id                  UUID NOT NULL,
    requester_type              VARCHAR(16) NOT NULL,
    requester_user_id           UUID,
    state                       VARCHAR(16) NOT NULL,
    provider_name               VARCHAR(128),
    provider_response_id        VARCHAR(128),
    assistant_message_id        UUID REFERENCES messages(id) ON DELETE SET NULL,
    error_code                  VARCHAR(64),
    error_detail                TEXT,
    reserve_tokens              BIGINT,
    max_output_tokens_applied   INT,
    reserved_credits_micro      BIGINT,
    policy_version_applied      BIGINT,
    effective_model             VARCHAR(1024),
    minimal_generation_floor_applied INT,
    deleted_at                  TIMESTAMPTZ,
    replaced_by_request_id      UUID,
    started_at                  TIMESTAMPTZ NOT NULL,
    completed_at                TIMESTAMPTZ,
    updated_at                  TIMESTAMPTZ NOT NULL,
    UNIQUE (chat_id, request_id),
    CHECK (requester_type IN ('user', 'system')),
    CHECK (state IN ('running', 'completed', 'failed', 'cancelled'))
);
CREATE UNIQUE INDEX IF NOT EXISTS idx_chat_turns_running
    ON chat_turns (chat_id)
    WHERE state = 'running' AND deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_chat_turns_chat_started
    ON chat_turns (chat_id, started_at DESC)
    WHERE deleted_at IS NULL;

-- 4. attachments
CREATE TABLE IF NOT EXISTS attachments (
    id                      UUID PRIMARY KEY NOT NULL,
    tenant_id               UUID NOT NULL,
    chat_id                 UUID NOT NULL REFERENCES chats(id) ON DELETE CASCADE,
    uploaded_by_user_id     UUID NOT NULL,
    filename                VARCHAR(255) NOT NULL,
    content_type            VARCHAR(128) NOT NULL,
    size_bytes              BIGINT NOT NULL CHECK (size_bytes >= 0),
    storage_backend         VARCHAR(32) NOT NULL DEFAULT 'azure',
    provider_file_id        VARCHAR(128),
    status                  VARCHAR(16) NOT NULL,
    error_code              VARCHAR(64),
    attachment_kind         VARCHAR(16) NOT NULL,
    doc_summary             TEXT,
    img_thumbnail           BYTEA,
    img_thumbnail_width     INT CHECK (img_thumbnail_width >= 0),
    img_thumbnail_height    INT CHECK (img_thumbnail_height >= 0),
    summary_model           VARCHAR(1024),
    summary_updated_at      TIMESTAMPTZ,
    cleanup_status          VARCHAR(16),
    cleanup_attempts        INT NOT NULL DEFAULT 0 CHECK (cleanup_attempts >= 0),
    last_cleanup_error      TEXT,
    cleanup_updated_at      TIMESTAMPTZ,
    created_at              TIMESTAMPTZ NOT NULL,
    updated_at              TIMESTAMPTZ NOT NULL DEFAULT now(),
    for_file_search         BOOLEAN NOT NULL DEFAULT false,
    for_code_interpreter    BOOLEAN NOT NULL DEFAULT false,
    deleted_at              TIMESTAMPTZ,
    CHECK (attachment_kind IN ('document', 'image')),
    CHECK (status IN ('pending', 'uploaded', 'ready', 'failed'))
);
CREATE INDEX IF NOT EXISTS idx_attachments_tenant_chat
    ON attachments (tenant_id, chat_id)
    WHERE deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_attachments_cleanup
    ON attachments (cleanup_status)
    WHERE cleanup_status IS NOT NULL AND deleted_at IS NULL;
CREATE UNIQUE INDEX IF NOT EXISTS idx_attachments_id_chat_id
    ON attachments (id, chat_id);

-- 4a. message_attachments
CREATE TABLE IF NOT EXISTS message_attachments (
    tenant_id       UUID NOT NULL,
    chat_id         UUID NOT NULL,
    message_id      UUID NOT NULL,
    attachment_id   UUID NOT NULL,
    created_at      TIMESTAMPTZ NOT NULL,
    PRIMARY KEY (chat_id, message_id, attachment_id),
    FOREIGN KEY (message_id, chat_id) REFERENCES messages(id, chat_id) ON DELETE CASCADE,
    FOREIGN KEY (attachment_id, chat_id) REFERENCES attachments(id, chat_id) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS idx_message_attachments_tenant_chat
    ON message_attachments (tenant_id, chat_id);
CREATE INDEX IF NOT EXISTS idx_message_attachments_attachment_chat
    ON message_attachments (attachment_id, chat_id);

-- 5. thread_summaries
CREATE TABLE IF NOT EXISTS thread_summaries (
    id                  UUID PRIMARY KEY NOT NULL,
    tenant_id           UUID NOT NULL,
    chat_id             UUID NOT NULL REFERENCES chats(id) ON DELETE CASCADE,
    summary_text        TEXT NOT NULL,
    summarized_up_to    UUID NOT NULL,
    token_estimate      INT NOT NULL DEFAULT 0 CHECK (token_estimate >= 0),
    created_at          TIMESTAMPTZ NOT NULL,
    updated_at          TIMESTAMPTZ NOT NULL,
    UNIQUE (chat_id)
);

-- 6. chat_vector_stores
CREATE TABLE IF NOT EXISTS chat_vector_stores (
    id              UUID PRIMARY KEY NOT NULL,
    tenant_id       UUID NOT NULL,
    chat_id         UUID NOT NULL REFERENCES chats(id) ON DELETE CASCADE,
    vector_store_id VARCHAR(128),
    provider        VARCHAR(128) NOT NULL,
    file_count      INT NOT NULL DEFAULT 0 CHECK (file_count >= 0),
    created_at      TIMESTAMPTZ NOT NULL,
    UNIQUE (tenant_id, chat_id)
);

-- 7. quota_usage
CREATE TABLE IF NOT EXISTS quota_usage (
    id                      UUID PRIMARY KEY NOT NULL,
    tenant_id               UUID NOT NULL,
    user_id                 UUID NOT NULL,
    period_type             VARCHAR(16) NOT NULL,
    period_start            DATE NOT NULL,
    bucket                  VARCHAR(32) NOT NULL,
    spent_credits_micro     BIGINT NOT NULL DEFAULT 0 CHECK (spent_credits_micro >= 0),
    reserved_credits_micro  BIGINT NOT NULL DEFAULT 0 CHECK (reserved_credits_micro >= 0),
    calls                   INT NOT NULL DEFAULT 0 CHECK (calls >= 0),
    input_tokens            BIGINT NOT NULL DEFAULT 0 CHECK (input_tokens >= 0),
    output_tokens           BIGINT NOT NULL DEFAULT 0 CHECK (output_tokens >= 0),
    file_search_calls       INT NOT NULL DEFAULT 0 CHECK (file_search_calls >= 0),
    web_search_calls        INT NOT NULL DEFAULT 0 CHECK (web_search_calls >= 0),
    code_interpreter_calls  INT NOT NULL DEFAULT 0 CHECK (code_interpreter_calls >= 0),
    rag_retrieval_calls     INT NOT NULL DEFAULT 0 CHECK (rag_retrieval_calls >= 0),
    image_inputs            INT NOT NULL DEFAULT 0 CHECK (image_inputs >= 0),
    image_upload_bytes      BIGINT NOT NULL DEFAULT 0 CHECK (image_upload_bytes >= 0),
    updated_at              TIMESTAMPTZ NOT NULL,
    UNIQUE (tenant_id, user_id, period_type, period_start, bucket)
);

-- 8. message_reactions
CREATE TABLE IF NOT EXISTS message_reactions (
    id          UUID PRIMARY KEY NOT NULL,
    tenant_id   UUID NOT NULL,
    message_id  UUID NOT NULL REFERENCES messages(id) ON DELETE CASCADE,
    user_id     UUID NOT NULL,
    reaction    VARCHAR(16) NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL,
    UNIQUE (message_id, user_id),
    CHECK (reaction IN ('like', 'dislike'))
);
";

const SQLITE_UP: &str = r"
-- 1. chats
CREATE TABLE IF NOT EXISTS chats (
    id          TEXT PRIMARY KEY NOT NULL,
    tenant_id   TEXT NOT NULL,
    user_id     TEXT NOT NULL,
    model       TEXT NOT NULL,
    title       TEXT,
    is_temporary INTEGER NOT NULL DEFAULT 0,
    created_at  TEXT NOT NULL,
    updated_at  TEXT NOT NULL,
    deleted_at  TEXT
);
CREATE INDEX IF NOT EXISTS idx_chats_tenant_user_updated
    ON chats (tenant_id, user_id, updated_at DESC)
    WHERE deleted_at IS NULL;

-- 2. messages
CREATE TABLE IF NOT EXISTS messages (
    id                  TEXT PRIMARY KEY NOT NULL,
    tenant_id           TEXT NOT NULL,
    chat_id             TEXT NOT NULL REFERENCES chats(id) ON DELETE CASCADE,
    request_id          TEXT,
    role                TEXT NOT NULL,
    content             TEXT NOT NULL DEFAULT '',
    content_type        TEXT NOT NULL DEFAULT 'text',
    token_estimate      INTEGER NOT NULL DEFAULT 0 CHECK (token_estimate >= 0),
    provider_response_id TEXT,
    request_kind        TEXT,
    features_used       TEXT NOT NULL DEFAULT '[]',
    input_tokens        INTEGER NOT NULL DEFAULT 0 CHECK (input_tokens >= 0),
    output_tokens       INTEGER NOT NULL DEFAULT 0 CHECK (output_tokens >= 0),
    cache_read_input_tokens  INTEGER NOT NULL DEFAULT 0 CHECK (cache_read_input_tokens >= 0),
    cache_write_input_tokens INTEGER NOT NULL DEFAULT 0 CHECK (cache_write_input_tokens >= 0),
    reasoning_tokens         INTEGER NOT NULL DEFAULT 0 CHECK (reasoning_tokens >= 0),
    model               TEXT,
    is_compressed       INTEGER NOT NULL DEFAULT 0,
    created_at          TEXT NOT NULL,
    deleted_at          TEXT
);
CREATE UNIQUE INDEX IF NOT EXISTS idx_messages_chat_request_role
    ON messages (chat_id, request_id, role)
    WHERE request_id IS NOT NULL AND deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_messages_chat_created
    ON messages (chat_id, created_at)
    WHERE deleted_at IS NULL;
CREATE UNIQUE INDEX IF NOT EXISTS idx_messages_id_chat_id
    ON messages (id, chat_id);

-- 3. chat_turns
CREATE TABLE IF NOT EXISTS chat_turns (
    id                          TEXT PRIMARY KEY NOT NULL,
    tenant_id                   TEXT NOT NULL,
    chat_id                     TEXT NOT NULL REFERENCES chats(id) ON DELETE CASCADE,
    request_id                  TEXT NOT NULL,
    requester_type              TEXT NOT NULL,
    requester_user_id           TEXT,
    state                       TEXT NOT NULL,
    provider_name               TEXT,
    provider_response_id        TEXT,
    assistant_message_id        TEXT REFERENCES messages(id) ON DELETE SET NULL,
    error_code                  TEXT,
    error_detail                TEXT,
    reserve_tokens              INTEGER,
    max_output_tokens_applied   INTEGER,
    reserved_credits_micro      INTEGER,
    policy_version_applied      INTEGER,
    effective_model             TEXT,
    minimal_generation_floor_applied INTEGER,
    deleted_at                  TEXT,
    replaced_by_request_id      TEXT,
    started_at                  TEXT NOT NULL,
    completed_at                TEXT,
    updated_at                  TEXT NOT NULL,
    UNIQUE (chat_id, request_id),
    CHECK (requester_type IN ('user', 'system')),
    CHECK (state IN ('running', 'completed', 'failed', 'cancelled'))
);
CREATE UNIQUE INDEX IF NOT EXISTS idx_chat_turns_running
    ON chat_turns (chat_id)
    WHERE state = 'running' AND deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_chat_turns_chat_started
    ON chat_turns (chat_id, started_at DESC)
    WHERE deleted_at IS NULL;

-- 4. attachments
CREATE TABLE IF NOT EXISTS attachments (
    id                      TEXT PRIMARY KEY NOT NULL,
    tenant_id               TEXT NOT NULL,
    chat_id                 TEXT NOT NULL REFERENCES chats(id) ON DELETE CASCADE,
    uploaded_by_user_id     TEXT NOT NULL,
    filename                TEXT NOT NULL,
    content_type            TEXT NOT NULL,
    size_bytes              INTEGER NOT NULL CHECK (size_bytes >= 0),
    storage_backend         TEXT NOT NULL DEFAULT 'azure',
    provider_file_id        TEXT,
    status                  TEXT NOT NULL,
    error_code              TEXT,
    attachment_kind         TEXT NOT NULL,
    doc_summary             TEXT,
    img_thumbnail           BLOB,
    img_thumbnail_width     INTEGER CHECK (img_thumbnail_width >= 0),
    img_thumbnail_height    INTEGER CHECK (img_thumbnail_height >= 0),
    summary_model           TEXT,
    summary_updated_at      TEXT,
    cleanup_status          TEXT,
    cleanup_attempts        INTEGER NOT NULL DEFAULT 0 CHECK (cleanup_attempts >= 0),
    last_cleanup_error      TEXT,
    cleanup_updated_at      TEXT,
    created_at              TEXT NOT NULL,
    updated_at              TEXT NOT NULL DEFAULT (datetime('now')),
    for_file_search         INTEGER NOT NULL DEFAULT 0,
    for_code_interpreter    INTEGER NOT NULL DEFAULT 0,
    deleted_at              TEXT,
    CHECK (attachment_kind IN ('document', 'image')),
    CHECK (status IN ('pending', 'uploaded', 'ready', 'failed'))
);
CREATE INDEX IF NOT EXISTS idx_attachments_tenant_chat
    ON attachments (tenant_id, chat_id)
    WHERE deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_attachments_cleanup
    ON attachments (cleanup_status)
    WHERE cleanup_status IS NOT NULL AND deleted_at IS NULL;
CREATE UNIQUE INDEX IF NOT EXISTS idx_attachments_id_chat_id
    ON attachments (id, chat_id);

-- 4a. message_attachments
CREATE TABLE IF NOT EXISTS message_attachments (
    tenant_id       TEXT NOT NULL,
    chat_id         TEXT NOT NULL,
    message_id      TEXT NOT NULL,
    attachment_id   TEXT NOT NULL,
    created_at      TEXT NOT NULL,
    PRIMARY KEY (chat_id, message_id, attachment_id),
    FOREIGN KEY (message_id, chat_id) REFERENCES messages(id, chat_id) ON DELETE CASCADE,
    FOREIGN KEY (attachment_id, chat_id) REFERENCES attachments(id, chat_id) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS idx_message_attachments_tenant_chat
    ON message_attachments (tenant_id, chat_id);
CREATE INDEX IF NOT EXISTS idx_message_attachments_attachment_chat
    ON message_attachments (attachment_id, chat_id);

-- 5. thread_summaries
CREATE TABLE IF NOT EXISTS thread_summaries (
    id                  TEXT PRIMARY KEY NOT NULL,
    tenant_id           TEXT NOT NULL,
    chat_id             TEXT NOT NULL REFERENCES chats(id) ON DELETE CASCADE,
    summary_text        TEXT NOT NULL,
    summarized_up_to    TEXT NOT NULL,
    token_estimate      INTEGER NOT NULL DEFAULT 0 CHECK (token_estimate >= 0),
    created_at          TEXT NOT NULL,
    updated_at          TEXT NOT NULL,
    UNIQUE (chat_id)
);

-- 6. chat_vector_stores
CREATE TABLE IF NOT EXISTS chat_vector_stores (
    id              TEXT PRIMARY KEY NOT NULL,
    tenant_id       TEXT NOT NULL,
    chat_id         TEXT NOT NULL REFERENCES chats(id) ON DELETE CASCADE,
    vector_store_id TEXT,
    provider        TEXT NOT NULL,
    file_count      INTEGER NOT NULL DEFAULT 0 CHECK (file_count >= 0),
    created_at      TEXT NOT NULL,
    UNIQUE (tenant_id, chat_id)
);

-- 7. quota_usage
CREATE TABLE IF NOT EXISTS quota_usage (
    id                      TEXT PRIMARY KEY NOT NULL,
    tenant_id               TEXT NOT NULL,
    user_id                 TEXT NOT NULL,
    period_type             TEXT NOT NULL,
    period_start            TEXT NOT NULL,
    bucket                  TEXT NOT NULL,
    spent_credits_micro     INTEGER NOT NULL DEFAULT 0 CHECK (spent_credits_micro >= 0),
    reserved_credits_micro  INTEGER NOT NULL DEFAULT 0 CHECK (reserved_credits_micro >= 0),
    calls                   INTEGER NOT NULL DEFAULT 0 CHECK (calls >= 0),
    input_tokens            INTEGER NOT NULL DEFAULT 0 CHECK (input_tokens >= 0),
    output_tokens           INTEGER NOT NULL DEFAULT 0 CHECK (output_tokens >= 0),
    file_search_calls       INTEGER NOT NULL DEFAULT 0 CHECK (file_search_calls >= 0),
    web_search_calls        INTEGER NOT NULL DEFAULT 0 CHECK (web_search_calls >= 0),
    code_interpreter_calls  INTEGER NOT NULL DEFAULT 0 CHECK (code_interpreter_calls >= 0),
    rag_retrieval_calls     INTEGER NOT NULL DEFAULT 0 CHECK (rag_retrieval_calls >= 0),
    image_inputs            INTEGER NOT NULL DEFAULT 0 CHECK (image_inputs >= 0),
    image_upload_bytes      INTEGER NOT NULL DEFAULT 0 CHECK (image_upload_bytes >= 0),
    updated_at              TEXT NOT NULL,
    UNIQUE (tenant_id, user_id, period_type, period_start, bucket)
);

-- 8. message_reactions
CREATE TABLE IF NOT EXISTS message_reactions (
    id          TEXT PRIMARY KEY NOT NULL,
    tenant_id   TEXT NOT NULL,
    message_id  TEXT NOT NULL REFERENCES messages(id) ON DELETE CASCADE,
    user_id     TEXT NOT NULL,
    reaction    TEXT NOT NULL,
    created_at  TEXT NOT NULL,
    UNIQUE (message_id, user_id),
    CHECK (reaction IN ('like', 'dislike'))
);
";
