-- Minimal relational schema for Telegram uploader bot core
-- Users (bot users)
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    telegram_id BIGINT NOT NULL UNIQUE,
    mode VARCHAR(16) NOT NULL DEFAULT 'free' -- free|premium
);

-- Admins (bot admins/owners)
CREATE TABLE admins (
    id INT AUTO_INCREMENT PRIMARY KEY,
    telegram_id BIGINT NOT NULL UNIQUE,
    is_owner TINYINT(1) NOT NULL DEFAULT 0
);

-- Folders for organizing media by admin
CREATE TABLE folders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    owner_admin_id INT NOT NULL,
    name VARCHAR(255) NOT NULL
);

-- Media items uploaded by admins
CREATE TABLE media (
    id INT AUTO_INCREMENT PRIMARY KEY,
    folder_id INT NOT NULL,
    owner_admin_id INT NOT NULL,
    telegram_file_id VARCHAR(255) NOT NULL,
    type VARCHAR(32) NOT NULL, -- photo|video|document|...
    uuid VARCHAR(36) NULL UNIQUE, -- UUID v4 for secure links
    thumbnail_file_id VARCHAR(255) NULL,
    thumbnail_enabled TINYINT(1) NOT NULL DEFAULT 1,
    caption TEXT NULL,
    auto_delete_seconds INT NULL,
    link_in_caption_enabled TINYINT(1) NOT NULL DEFAULT 1
);

-- Payments for upgrading users or buying quotas
CREATE TABLE payments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    gateway VARCHAR(32) NOT NULL, -- telegram_stars|nextpay|zarinpal
    amount INT NOT NULL,
    status VARCHAR(16) NOT NULL DEFAULT 'pending', -- pending|success|failed
    external_id VARCHAR(191) NULL,
    payload TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Ads configuration
CREATE TABLE ads (
    id INT AUTO_INCREMENT PRIMARY KEY,
    owner_admin_id INT NOT NULL,
    text TEXT NOT NULL,
    banner_url VARCHAR(255) NULL,
    frequency INT NOT NULL DEFAULT 1, -- show ad every N messages
    enabled TINYINT(1) NOT NULL DEFAULT 1
);

-- Analytics events (downloads, clicks, joins, payments, ...)
CREATE TABLE analytics_events (
    id INT AUTO_INCREMENT PRIMARY KEY,
    type VARCHAR(32) NOT NULL, -- download|click|join_converted|payment
    user_id INT NULL,
    media_id INT NULL,
    payment_id INT NULL,
    occurred_at DATETIME NOT NULL
);

-- Logs table for comprehensive logging system
CREATE TABLE logs (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    level VARCHAR(16) NOT NULL, -- DEBUG|INFO|WARNING|ERROR|CRITICAL
    message TEXT NOT NULL,
    context JSON NULL, -- Additional context data
    file VARCHAR(255) NULL, -- File where log was created
    line INT NULL, -- Line number where log was created
    telegram_id BIGINT NULL, -- Related Telegram user ID
    user_id INT NULL, -- Related user ID from users table
    admin_id INT NULL, -- Related admin ID from admins table
    media_id INT NULL, -- Related media ID
    exception_type VARCHAR(255) NULL, -- Exception class name if applicable
    exception_message TEXT NULL, -- Exception message
    exception_trace TEXT NULL, -- Exception stack trace
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_level (level),
    INDEX idx_created_at (created_at),
    INDEX idx_telegram_id (telegram_id),
    INDEX idx_user_id (user_id),
    INDEX idx_level_created (level, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


