-- Games Club feedback campaign database
-- Run this once: mysql -u root -p < sql/schema.sql

CREATE DATABASE IF NOT EXISTS gc_feedback
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

USE gc_feedback;

CREATE TABLE IF NOT EXISTS admins (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(64) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- In-progress Telegram conversations
CREATE TABLE IF NOT EXISTS bot_sessions (
    telegram_user_id BIGINT NOT NULL PRIMARY KEY,
    chat_id BIGINT NOT NULL,
    step VARCHAR(32) NOT NULL,
    data JSON NULL,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Finished reports from the Telegram bot
CREATE TABLE IF NOT EXISTS submissions (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    telegram_user_id BIGINT NOT NULL,
    telegram_chat_id BIGINT NOT NULL,
    app_uid VARCHAR(64) NOT NULL,
    phone VARCHAR(32) NOT NULL,
    category VARCHAR(32) NOT NULL,
    message TEXT NOT NULL,
    media_file_id VARCHAR(255) NOT NULL,
    media_type ENUM('photo', 'video') NOT NULL,
    status ENUM('pending', 'valid', 'invalid') NOT NULL DEFAULT 'pending',
    admin_note VARCHAR(500) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_status (status),
    INDEX idx_category (category),
    INDEX idx_created_at (created_at),
    INDEX idx_telegram_user (telegram_user_id)
) ENGINE=InnoDB;