-- 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 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 DEFAULT 'active',

    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,

    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,

    last_renewal DATETIME,

    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,
    chat_title TEXT NOT NULL,
    chat_type TEXT NOT NULL,
    invite_link TEXT,
    active INTEGER DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 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,
    status TEXT DEFAULT 'pending', -- 'pending', 'completed'
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);


versão atualizada 


Plano → Grupo

CREATE TABLE IF NOT EXISTS plan_chats (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    plan_id INTEGER NOT NULL,
    managed_chat_id INTEGER NOT NULL,

    FOREIGN KEY(plan_id)
        REFERENCES plans(id),

    FOREIGN KEY(managed_chat_id)
        REFERENCES managed_chats(id)
);

Links exclusivos

CREATE TABLE IF NOT EXISTS invite_links (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    subscription_id INTEGER NOT NULL,
    managed_chat_id INTEGER NOT NULL,
    telegram_user INTEGER NOT NULL,
    invite_link TEXT NOT NULL,
    telegram_invite_link_id TEXT,
    invite_name TEXT,
    expire_date DATETIME,
    member_limit INTEGER DEFAULT 1,
    used INTEGER DEFAULT 0,
    revoked INTEGER DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY(subscription_id)
        REFERENCES subscriptions(id),

    FOREIGN KEY(managed_chat_id)
        REFERENCES managed_chats(id)
);


Histórico de pagamentos

CREATE TABLE IF NOT EXISTS payments (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    gateway TEXT NOT NULL,
    payment_id TEXT UNIQUE,
    amount REAL NOT NULL,
    status TEXT DEFAULT 'pending',
    external_reference TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    paid_at DATETIME,

    FOREIGN KEY(user_id)
        REFERENCES users(id)
);

Histórico financeiro

CREATE TABLE IF NOT EXISTS balance_history (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    telegram_id INTEGER,
    type TEXT,
    amount REAL,
    description TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

Configurações

CREATE TABLE IF NOT EXISTS settings (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    config_key TEXT UNIQUE,
    config_value TEXT
);

Logs

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

Sessões do Admin

CREATE TABLE IF NOT EXISTS admin_sessions (
    telegram_id INTEGER PRIMARY KEY,
    logged INTEGER DEFAULT 0,
    login_at DATETIME
);

Convites enviados

CREATE TABLE invite_history (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    telegram_id INTEGER,
    managed_chat_id INTEGER,
    invite_link TEXT,
    joined INTEGER DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

Renovação

CREATE TABLE renewals (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    subscription_id INTEGER,
    old_end_date DATETIME,
    new_end_date DATETIME,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);


quem entrou nk grupo ou canal 

CREATE TABLE IF NOT EXISTS chat_members (

    id INTEGER PRIMARY KEY AUTOINCREMENT,

    telegram_id INTEGER NOT NULL,

    managed_chat_id INTEGER NOT NULL,

    joined_at DATETIME DEFAULT CURRENT_TIMESTAMP,

    left_at DATETIME,

    active INTEGER DEFAULT 1,

    FOREIGN KEY(managed_chat_id)
        REFERENCES managed_chats(id)

);


jobs

CREATE TABLE IF NOT EXISTS jobs (

    id INTEGER PRIMARY KEY AUTOINCREMENT,

    job_type TEXT,

    payload TEXT,

    status TEXT DEFAULT 'pending',

    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,

    executed_at DATETIME

);


notificações 

CREATE TABLE IF NOT EXISTS notifications (

    id INTEGER PRIMARY KEY AUTOINCREMENT,

    telegram_id INTEGER,

    title TEXT,

    message TEXT,

    sent INTEGER DEFAULT 0,

    created_at DATETIME DEFAULT CURRENT_TIMESTAMP

);


CREATE INDEX IF NOT EXISTS idx_users_telegram
ON users(telegram_id);

CREATE INDEX IF NOT EXISTS idx_resellers_telegram
ON resellers(telegram_id);

CREATE INDEX IF NOT EXISTS idx_subscriptions_user
ON subscriptions(user_id);

CREATE INDEX IF NOT EXISTS idx_subscriptions_status
ON subscriptions(status);

CREATE INDEX IF NOT EXISTS idx_payments_user
ON payments(user_id);

CREATE INDEX IF NOT EXISTS idx_invite_links_user
ON invite_links(telegram_user);

CREATE INDEX IF NOT EXISTS idx_plan_chats
ON plan_chats(plan_id);

CREATE INDEX IF NOT EXISTS idx_managed_chat
ON managed_chats(chat_id);

CREATE INDEX IF NOT EXISTS idx_gift_code
ON gift_codes(code);

CREATE INDEX IF NOT EXISTS idx_payment
ON payments(payment_id);

CREATE INDEX IF NOT EXISTS idx_invite_subscription
ON invite_links(subscription_id);

CREATE INDEX IF NOT EXISTS idx_chat_members
ON chat_members(telegram_id);

CREATE INDEX IF NOT EXISTS idx_jobs_status
ON jobs(status);

CREATE INDEX IF NOT EXISTS idx_notifications
ON notifications(sent);

CREATE INDEX IF NOT EXISTS idx_plan_chats_chat
ON plan_chats(managed_chat_id);

CREATE INDEX IF NOT EXISTS idx_chat_members_chat
ON chat_members(managed_chat_id);

CREATE INDEX IF NOT EXISTS idx_logs_telegram
ON logs(telegram_id);

CREATE INDEX IF NOT EXISTS idx_balance_history
ON balance_history(telegram_id);

CREATE INDEX IF NOT EXISTS idx_subscription_plan
ON subscriptions(plan_id);