-- SportsClaw Database Schema

-- Users table
CREATE TABLE IF NOT EXISTS sc_users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    telegram_username VARCHAR(100),
    telegram_chat_id BIGINT,
    plan VARCHAR(20) DEFAULT 'free' CHECK (plan IN ('free', 'weekly', 'monthly')),
    stripe_customer_id VARCHAR(255),
    stripe_subscription_id VARCHAR(255),
    subscription_status VARCHAR(50) DEFAULT 'active',
    subscription_ends_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Bot tokens table (each user gets a unique bot or shared bot with user token)
CREATE TABLE IF NOT EXISTS sc_bot_tokens (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID REFERENCES sc_users(id) ON DELETE CASCADE,
    token VARCHAR(100) UNIQUE NOT NULL,
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Usage tracking
CREATE TABLE IF NOT EXISTS sc_usage (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID REFERENCES sc_users(id) ON DELETE CASCADE,
    query_type VARCHAR(50),
    query_text TEXT,
    response_time_ms INTEGER,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Daily usage summary (for quick limit checks)
CREATE TABLE IF NOT EXISTS sc_daily_usage (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID REFERENCES sc_users(id) ON DELETE CASCADE,
    date DATE NOT NULL,
    query_count INTEGER DEFAULT 0,
    UNIQUE(user_id, date)
);

-- Indexes
CREATE INDEX IF NOT EXISTS idx_users_email ON sc_users(email);
CREATE INDEX IF NOT EXISTS idx_users_telegram ON sc_users(telegram_chat_id);
CREATE INDEX IF NOT EXISTS idx_users_stripe ON sc_users(stripe_customer_id);
CREATE INDEX IF NOT EXISTS idx_usage_user_date ON sc_usage(user_id, created_at);
CREATE INDEX IF NOT EXISTS idx_daily_usage_lookup ON sc_daily_usage(user_id, date);
CREATE INDEX IF NOT EXISTS idx_bot_tokens_token ON sc_bot_tokens(token);
