-- دیتابیس ربات جشنواره واک
-- MySQL 5.7+ / MariaDB

CREATE DATABASE IF NOT EXISTS vak_bot CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE vak_bot;

-- کاربران ثبت‌نام‌شده
CREATE TABLE IF NOT EXISTS users (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    telegram_id     BIGINT UNSIGNED NOT NULL UNIQUE,
    display_id      INT UNSIGNED NOT NULL UNIQUE COMMENT 'شناسه عددی: کودک از ۱۰۰۰۰، بزرگسال از ۲۰۰۰۰',
    full_name       VARCHAR(200) NOT NULL,
    phone           VARCHAR(20) NOT NULL,
    national_id     CHAR(10) NOT NULL,
    birth_date      DATE NOT NULL,
    city            VARCHAR(100) NOT NULL,
    age_group       ENUM('child', 'adult') NOT NULL COMMENT 'child=زیر ۱۶، adult=۱۶ و بالاتر',
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_telegram (telegram_id),
    INDEX idx_display (display_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- شمارنده شناسه عددی
CREATE TABLE IF NOT EXISTS id_counters (
    age_group   ENUM('child', 'adult') PRIMARY KEY,
    last_id     INT UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO id_counters (age_group, last_id) VALUES
    ('child', 9999),
    ('adult', 19999)
ON DUPLICATE KEY UPDATE last_id = last_id;

-- وضعیت مکالمه (state machine)
CREATE TABLE IF NOT EXISTS user_states (
    telegram_id BIGINT UNSIGNED PRIMARY KEY,
    state       VARCHAR(50) NOT NULL DEFAULT 'idle',
    data        JSON NULL,
    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 submissions (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id     INT UNSIGNED NOT NULL,
    category    VARCHAR(50) NOT NULL,
    file_id     VARCHAR(255) NOT NULL,
    file_type   ENUM('voice', 'video', 'video_note', 'audio', 'document') NOT NULL,
    file_unique_id VARCHAR(255) NULL,
    created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_user_category (user_id, category),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_category (category)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;