-- Esquema SaaS Pro do SorteioBot Telegram

CREATE TABLE IF NOT EXISTS admins (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    telegram_id TEXT UNIQUE NOT NULL,
    username TEXT,
    role TEXT DEFAULT 'admin', -- 'superadmin' ou 'admin'
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS chats (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    chat_id TEXT UNIQUE NOT NULL,
    title TEXT NOT NULL,
    type TEXT NOT NULL, -- 'group', 'supergroup', 'channel', 'private'
    enabled INTEGER DEFAULT 0, -- 0 = Desativado, 1 = Ativado pelo Admin
    is_global INTEGER DEFAULT 0, -- 0 = Individual, 1 = Global
    added_by TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS moderators (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id TEXT NOT NULL,
    chat_id TEXT NOT NULL,
    status TEXT DEFAULT 'pending', -- 'pending', 'active', 'rejected'
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(user_id, chat_id)
);

CREATE TABLE IF NOT EXISTS giveaways (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    chat_id TEXT NOT NULL,
    creator_id TEXT NOT NULL,
    title TEXT NOT NULL,
    prize TEXT NOT NULL,
    image_url TEXT,
    winners_count INTEGER DEFAULT 1,
    required_channel TEXT, -- Canal obrigatório para participar (opcional)
    status TEXT DEFAULT 'active', -- 'active', 'ended'
    message_id TEXT,
    ends_at DATETIME, -- Data/hora de término automático
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS participants (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    giveaway_id INTEGER NOT NULL,
    user_id TEXT NOT NULL,
    username TEXT,
    first_name TEXT,
    referred_by TEXT, -- ID de quem indicou
    tickets INTEGER DEFAULT 1, -- Bilhetes acumulados (ex: por indicação)
    joined_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(giveaway_id, user_id)
);

CREATE TABLE IF NOT EXISTS winners (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    giveaway_id INTEGER NOT NULL,
    user_id TEXT NOT NULL,
    username TEXT,
    first_name TEXT,
    prize_rank INTEGER NOT NULL,
    redemption_info TEXT,
    drawn_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS donations (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id TEXT,
    username TEXT,
    amount REAL,
    payment_id TEXT,
    message TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS audit_logs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id TEXT,
    action TEXT,
    details TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);