CREATE TABLE IF NOT EXISTS users (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
 platform ENUM('telegram','rubika') NOT NULL,
 platform_user_id VARCHAR(191) NOT NULL,
 chat_id VARCHAR(191) NULL,
 username VARCHAR(191) NULL,
 first_name VARCHAR(191) NULL,
 last_name VARCHAR(191) NULL,
 student_code VARCHAR(100) NULL,
 class_name VARCHAR(100) NULL,
 grade VARCHAR(50) NULL,
 notes TEXT NULL,
 status TINYINT NOT NULL DEFAULT 1,
 created_at DATETIME NOT NULL,
 last_seen DATETIME NOT NULL,
 message_count INT UNSIGNED NOT NULL DEFAULT 0,
 PRIMARY KEY(id),
 UNIQUE KEY uq_platform_user(platform,platform_user_id),
 KEY idx_chat(platform,chat_id),
 KEY idx_class(class_name),
 KEY idx_last_seen(last_seen)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS conversations (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
 user_id BIGINT UNSIGNED NOT NULL,
 title VARCHAR(255) NULL,
 created_at DATETIME NOT NULL,
 updated_at DATETIME NOT NULL,
 PRIMARY KEY(id),
 KEY idx_user_updated(user_id,updated_at),
 CONSTRAINT fk_conv_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS messages (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
 conversation_id BIGINT UNSIGNED NOT NULL,
 user_id BIGINT UNSIGNED NOT NULL,
 platform ENUM('telegram','rubika') NOT NULL,
 external_message_id VARCHAR(191) NULL,
 role ENUM('user','assistant','system') NOT NULL,
 message_type ENUM('text','image','mixed') NOT NULL DEFAULT 'text',
 text_content MEDIUMTEXT NULL,
 image_path VARCHAR(500) NULL,
 ai_model VARCHAR(191) NULL,
 token_usage INT UNSIGNED NULL,
 latency_ms INT UNSIGNED NULL,
 created_at DATETIME NOT NULL,
 PRIMARY KEY(id),
 UNIQUE KEY uq_external(platform,external_message_id),
 KEY idx_conv_created(conversation_id,created_at),
 KEY idx_user_created(user_id,created_at),
 CONSTRAINT fk_msg_conv FOREIGN KEY(conversation_id) REFERENCES conversations(id) ON DELETE CASCADE,
 CONSTRAINT fk_msg_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS ai_requests (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
 user_id BIGINT UNSIGNED NOT NULL,
 conversation_id BIGINT UNSIGNED NOT NULL,
 provider VARCHAR(100) NOT NULL,
 model VARCHAR(191) NOT NULL,
 status ENUM('started','success','error') NOT NULL,
 latency_ms INT UNSIGNED NULL,
 error_message TEXT NULL,
 created_at DATETIME NOT NULL,
 PRIMARY KEY(id),
 KEY idx_created(created_at),
 KEY idx_user_created(user_id,created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS admin_users (
 id INT UNSIGNED NOT NULL AUTO_INCREMENT,
 username VARCHAR(100) NOT NULL UNIQUE,
 password_hash VARCHAR(255) NOT NULL,
 created_at DATETIME NOT NULL,
 last_login DATETIME NULL,
 PRIMARY KEY(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;