-- ModerBot Database Schema

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

-- Users table (Telegram users)
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    telegram_id BIGINT UNIQUE NOT NULL,
    username VARCHAR(255),
    first_name VARCHAR(255),
    last_name VARCHAR(255),
    is_admin BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Messages table (moderated messages)
CREATE TABLE messages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    telegram_message_id BIGINT NOT NULL,
    chat_id BIGINT NOT NULL,
    user_id INT,
    message_type ENUM('text', 'photo', 'video', 'document', 'other') NOT NULL,
    content TEXT,
    file_id VARCHAR(255),
    moderation_status ENUM('pending', 'approved', 'rejected') DEFAULT 'pending',
    moderation_reason TEXT,
    moderated_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    moderated_at TIMESTAMP NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (moderated_by) REFERENCES users(id) ON DELETE SET NULL
);

-- Moderation logs
CREATE TABLE moderation_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    message_id INT,
    moderator_id INT,
    action ENUM('approve', 'reject', 'delete') NOT NULL,
    reason TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (message_id) REFERENCES messages(id) ON DELETE CASCADE,
    FOREIGN KEY (moderator_id) REFERENCES users(id) ON DELETE SET NULL
);

-- Settings table
CREATE TABLE settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(255) UNIQUE NOT NULL,
    setting_value TEXT,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Insert default settings
INSERT INTO settings (setting_key, setting_value) VALUES
('auto_moderation', 'true'),
('require_admin_approval', 'false'),
('max_message_length', '1000'),
('forbidden_words', 'reklama,reklam,sotaman,sotib olaman,soqish,soq,jinsi,porno,xxx'),
('allowed_game', 'PUBG');

-- Insert default admin user (replace with actual admin telegram_id)
-- INSERT INTO users (telegram_id, username, first_name, is_admin) VALUES (YOUR_ADMIN_TELEGRAM_ID, 'admin', 'Admin', TRUE); 