CREATE TABLE IF NOT EXISTS users (
    user_id          INTEGER PRIMARY KEY,
    username         TEXT,
    first_name       TEXT,
    last_name        TEXT,
    balance          REAL NOT NULL DEFAULT 0,
    total_deposits   REAL NOT NULL DEFAULT 0,
    total_purchases  REAL NOT NULL DEFAULT 0,
    referral_earnings REAL NOT NULL DEFAULT 0,
    referred_by      INTEGER,
    is_active        INTEGER NOT NULL DEFAULT 0,  -- becomes 1 after first deposit OR purchase
    is_banned        INTEGER NOT NULL DEFAULT 0,
    created_at       TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS categories (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    name        TEXT NOT NULL UNIQUE,
    created_at  TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS products (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    category_id     INTEGER REFERENCES categories(id) ON DELETE SET NULL,
    name            TEXT NOT NULL,
    description     TEXT,
    price           REAL NOT NULL,
    stock           INTEGER NOT NULL DEFAULT 0,
    image_file_id   TEXT,
    product_type    TEXT,             -- text|credentials|license|link|file
    delivery_content TEXT,            -- text/links/keys/credentials, or telegram file_id
    delivery_file_id TEXT,            -- optional uploaded file
    is_active       INTEGER NOT NULL DEFAULT 1,
    created_at      TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS orders (
    id           INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id      INTEGER NOT NULL REFERENCES users(user_id),
    product_id   INTEGER NOT NULL REFERENCES products(id),
    product_name TEXT NOT NULL,
    amount       REAL NOT NULL,
    delivery_status TEXT NOT NULL DEFAULT 'delivered',
    delivery_content TEXT,
    created_at   TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS deposits (
    id           INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id      INTEGER NOT NULL REFERENCES users(user_id),
    method       TEXT NOT NULL,
    amount       REAL NOT NULL,
    tx_reference TEXT,              -- txid or photo file_id
    proof_type   TEXT,              -- 'text' or 'photo'
    status       TEXT NOT NULL DEFAULT 'pending',  -- pending|approved|rejected
    admin_note   TEXT,
    created_at   TEXT NOT NULL DEFAULT (datetime('now')),
    reviewed_at  TEXT
);
CREATE UNIQUE INDEX IF NOT EXISTS idx_deposits_dup
    ON deposits(user_id, method, tx_reference)
    WHERE tx_reference IS NOT NULL;

CREATE TABLE IF NOT EXISTS referral_rewards (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    referrer_id INTEGER NOT NULL REFERENCES users(user_id),
    referred_id INTEGER NOT NULL REFERENCES users(user_id),
    reward_type TEXT NOT NULL,            -- 'active_batch' | 'purchase'
    amount      REAL NOT NULL,
    created_at  TEXT NOT NULL DEFAULT (datetime('now')),
    UNIQUE(referrer_id, referred_id, reward_type)
);

CREATE TABLE IF NOT EXISTS support_messages (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id     INTEGER NOT NULL REFERENCES users(user_id),
    message     TEXT NOT NULL,
    admin_reply TEXT,
    status      TEXT NOT NULL DEFAULT 'open', -- open|replied|closed
    created_at  TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS announcements (
    id         INTEGER PRIMARY KEY AUTOINCREMENT,
    title      TEXT NOT NULL,
    body       TEXT NOT NULL,
    created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS broadcasts (
    id         INTEGER PRIMARY KEY AUTOINCREMENT,
    admin_id   INTEGER NOT NULL,
    payload    TEXT NOT NULL,
    sent       INTEGER NOT NULL DEFAULT 0,
    delivered  INTEGER NOT NULL DEFAULT 0,
    failed     INTEGER NOT NULL DEFAULT 0,
    created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE TABLE IF NOT EXISTS settings (
    key   TEXT PRIMARY KEY,
    value TEXT
);

CREATE TABLE IF NOT EXISTS logs (
    id         INTEGER PRIMARY KEY AUTOINCREMENT,
    level      TEXT NOT NULL,
    event      TEXT NOT NULL,
    data       TEXT,
    created_at TEXT NOT NULL DEFAULT (datetime('now'))
);