-- Users table
CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    telegram_id BIGINT UNIQUE NOT NULL,
    username VARCHAR(255),
    first_name VARCHAR(255),
    last_name VARCHAR(255),
    balance DECIMAL(15, 2) DEFAULT 0,
    role VARCHAR(20) DEFAULT 'user',
    is_banned BOOLEAN DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Orders table
CREATE TABLE IF NOT EXISTS orders (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    order_code VARCHAR(100) UNIQUE,
    user_id INTEGER NOT NULL,
    telegram_id BIGINT NOT NULL,
    product_id INTEGER NOT NULL,
    product_name VARCHAR(255),
    category_name VARCHAR(255),
    quantity INTEGER DEFAULT 1,
    price DECIMAL(15, 2),
    total_price DECIMAL(15, 2),
    status VARCHAR(50) DEFAULT 'completed',
    content TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Products cache table (optional, for caching API responses)
CREATE TABLE IF NOT EXISTS products_cache (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    product_id INTEGER UNIQUE NOT NULL,
    category_id INTEGER,
    category_name VARCHAR(255),
    product_name VARCHAR(255),
    price DECIMAL(15, 2),
    stock INTEGER,
    description TEXT,
    cached_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Config table (for storing bot settings)
CREATE TABLE IF NOT EXISTS config (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    config_key VARCHAR(100) UNIQUE NOT NULL,
    config_value TEXT,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Transactions table (for tracking deposits and purchases)
CREATE TABLE IF NOT EXISTS transactions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    telegram_id BIGINT NOT NULL,
    type VARCHAR(50) NOT NULL, -- 'deposit', 'purchase', 'refund'
    amount DECIMAL(15, 2) NOT NULL,
    description TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Create indexes for better performance
CREATE INDEX IF NOT EXISTS idx_users_telegram_id ON users(telegram_id);
CREATE INDEX IF NOT EXISTS idx_orders_user_id ON orders(user_id);
CREATE INDEX IF NOT EXISTS idx_orders_telegram_id ON orders(telegram_id);
CREATE INDEX IF NOT EXISTS idx_transactions_user_id ON transactions(user_id);
