-- ============================================================
-- BD Top Viral Link Hub - Refactored Database Schema
-- ============================================================

-- Batches (file collections for sharing)
CREATE TABLE IF NOT EXISTS fb_batches (
    batch_code VARCHAR(32) PRIMARY KEY,
    uploaded_by BIGINT NOT NULL,
    uploaded_by_username VARCHAR(255),
    media_group_id VARCHAR(64),
    notified TINYINT(1) DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_media_group (media_group_id),
    INDEX idx_uploaded_by (uploaded_by)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Files in each batch
CREATE TABLE IF NOT EXISTS fb_batch_files (
    id INT AUTO_INCREMENT PRIMARY KEY,
    batch_code VARCHAR(32) NOT NULL,
    file_id VARCHAR(255) NOT NULL,
    file_unique_id VARCHAR(255),
    file_type ENUM('photo','video') NOT NULL,
    caption TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_batch_code (batch_code),
    FOREIGN KEY (batch_code) REFERENCES fb_batches(batch_code) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Users
CREATE TABLE IF NOT EXISTS fb_users (
    id BIGINT PRIMARY KEY,
    username VARCHAR(255),
    first_name VARCHAR(255),
    banned TINYINT(1) DEFAULT 0,
    joined_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_banned (banned)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Global settings (always id=1)
CREATE TABLE IF NOT EXISTS fb_settings (
    id INT PRIMARY KEY DEFAULT 1,
    forward_enabled TINYINT(1) DEFAULT 1,
    download_enabled TINYINT(1) DEFAULT 1,
    auto_delete_minutes INT DEFAULT 0,
    force_sub_channel VARCHAR(255),
    video_upload_restricted TINYINT(1) DEFAULT 0,
    file_btn1_text VARCHAR(64),
    file_btn1_url VARCHAR(512),
    file_btn2_text VARCHAR(64),
    file_btn2_url VARCHAR(512)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- User multi-step conversation states
CREATE TABLE IF NOT EXISTS fb_user_states (
    user_id BIGINT PRIMARY KEY,
    state VARCHAR(50) NOT NULL,
    temp_data JSON,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_state (state)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Scheduled auto-deletions
CREATE TABLE IF NOT EXISTS fb_scheduled_deletions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    chat_id BIGINT NOT NULL,
    message_id BIGINT NOT NULL,
    delete_at DATETIME NOT NULL,
    INDEX idx_delete_at (delete_at),
    UNIQUE KEY uniq_msg (chat_id, message_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Video upload permissions
CREATE TABLE IF NOT EXISTS fb_video_permissions (
    user_id BIGINT PRIMARY KEY,
    granted_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Insert default settings
INSERT INTO fb_settings (id, forward_enabled, download_enabled) VALUES (1, 1, 1) ON DUPLICATE KEY UPDATE id=id;