CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NOT NULL UNIQUE,
    first_name VARCHAR(255),
    username VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS channels (
    id INT AUTO_INCREMENT PRIMARY KEY,
    chat_id BIGINT NOT NULL UNIQUE,
    owner_id BIGINT NOT NULL,
    title VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP,
    INDEX (owner_id)
);

CREATE TABLE IF NOT EXISTS channel_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    chat_id BIGINT NOT NULL,
    setting_key VARCHAR(50) NOT NULL,
    setting_value TEXT,
    UNIQUE KEY unique_setting (chat_id, setting_key),
    FOREIGN KEY (chat_id) REFERENCES channels(chat_id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS join_requests (
    id INT AUTO_INCREMENT PRIMARY KEY,
    chat_id BIGINT NOT NULL,
    user_id BIGINT NOT NULL,
    status ENUM('pending', 'approved', 'declined', 'deferred') DEFAULT 'pending',
    process_after INT DEFAULT 0, -- Unix timestamp for deferred
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    approved_at TIMESTAMP NULL,
    INDEX (user_id, chat_id),
    INDEX (status, process_after)
);

CREATE TABLE IF NOT EXISTS global_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(50) NOT NULL UNIQUE,
    setting_value TEXT
);

CREATE TABLE IF NOT EXISTS broadcast_queue (
    id INT AUTO_INCREMENT PRIMARY KEY,
    target_type ENUM('all_users', 'all_channels', 'specific_channel') NOT NULL,
    target_id BIGINT NULL, -- chat_id if specific_channel
    message_text TEXT NULL,
    status ENUM('pending', 'processing', 'completed', 'failed') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    completed_at TIMESTAMP NULL
);
CREATE TABLE IF NOT EXISTS user_states (
    user_id BIGINT PRIMARY KEY,
    state VARCHAR(50),
    data TEXT, -- JSON or serialized data (e.g., target_chat_id)
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS channel_messages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    chat_id BIGINT NOT NULL,
    type ENUM('greeting', 'farewell') NOT NULL,
    language VARCHAR(10) DEFAULT 'all',
    content_type ENUM('text', 'photo', 'video', 'animation', 'document') DEFAULT 'text',
    file_id TEXT,
    text_content TEXT,
    buttons JSON,
    defer_seconds INT DEFAULT 0,
    delete_seconds INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (chat_id) REFERENCES channels(chat_id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS scheduled_actions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    chat_id BIGINT NOT NULL,
    message_template_id INT,
    action_type ENUM('send', 'delete') NOT NULL,
    process_at INT NOT NULL,
    sent_message_id INT,
    payload TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    KEY (process_at),
    FOREIGN KEY (message_template_id) REFERENCES channel_messages(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS greeting_locks (
    chat_id BIGINT NOT NULL,
    user_id BIGINT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (chat_id, user_id)
);