-- Tabela para administradores
CREATE TABLE IF NOT EXISTS admins (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT NOT NULL UNIQUE,
    pin TEXT NOT NULL,
    password TEXT NOT NULL
);

-- Tabela para planos de assinatura
CREATE TABLE IF NOT EXISTS plans (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL UNIQUE,
    duration_value INTEGER NOT NULL, -- Ex: 1, 30, 365
    duration_unit TEXT NOT NULL,    -- Ex: 'minutes', 'hours', 'days', 'months', 'years'
    price_client REAL NOT NULL,     -- Preço para clientes finais
    price_reseller REAL NOT NULL    -- Preço para revendedores
);

-- Tabela para associar múltiplos grupos a um plano
CREATE TABLE IF NOT EXISTS plan_groups (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    plan_id INTEGER NOT NULL,
    group_id TEXT NOT NULL,
    FOREIGN KEY (plan_id) REFERENCES plans(id)
);

-- Tabela para usuários do Telegram (clientes)
CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    telegram_id INTEGER NOT NULL UNIQUE,
    username TEXT,
    first_name TEXT,
    last_name TEXT,
    balance REAL DEFAULT 0.0
);

-- Tabela para revendedores
CREATE TABLE IF NOT EXISTS resellers (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    telegram_id INTEGER NOT NULL UNIQUE,
    username TEXT,
    first_name TEXT,
    last_name TEXT,
    balance REAL DEFAULT 0.0
);

-- Tabela para gift codes
CREATE TABLE IF NOT EXISTS gift_codes (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    code TEXT NOT NULL UNIQUE,
    value REAL NOT NULL,
    created_by INTEGER, -- ID do admin que criou
    redeemed_by_user INTEGER, -- ID do usuário que resgatou (se for cliente)
    redeemed_by_reseller INTEGER, -- ID do revendedor que resgatou (se for revendedor)
    redeemed_at DATETIME,
    is_used BOOLEAN DEFAULT FALSE,
    FOREIGN KEY (created_by) REFERENCES admins(id),
    FOREIGN KEY (redeemed_by_user) REFERENCES users(id),
    FOREIGN KEY (redeemed_by_reseller) REFERENCES resellers(id)
);

-- Tabela para assinaturas ativas
CREATE TABLE IF NOT EXISTS subscriptions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    plan_id INTEGER NOT NULL,
    start_date DATETIME NOT NULL,
    end_date DATETIME NOT NULL,
    status TEXT NOT NULL, -- 'active', 'expired', 'cancelled'
    group_id TEXT, -- ID do grupo/canal do Telegram
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (plan_id) REFERENCES plans(id)
);

-- Tabela para grupos/canais gerenciados pelo bot
CREATE TABLE IF NOT EXISTS managed_chats (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    chat_id TEXT NOT NULL UNIQUE, -- ID do grupo/canal
    chat_title TEXT,
    chat_type TEXT -- 'group', 'channel'
);

-- Tabela para gerenciar o estado da conversa dos usuários
CREATE TABLE IF NOT EXISTS user_states (
    telegram_id INTEGER PRIMARY KEY,
    state TEXT,
    data TEXT
);

-- Tabela para pagamentos TON
CREATE TABLE IF NOT EXISTS ton_payments (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    address TEXT NOT NULL,
    amount REAL NOT NULL,
    comment TEXT NOT NULL,
    status TEXT DEFAULT 'pending', -- 'pending', 'completed'
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);