-- 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 'none',
    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);

-- Blog posts (SEO content engine)
CREATE TABLE IF NOT EXISTS sc_blog_posts (
    id BIGSERIAL PRIMARY KEY,
    slug VARCHAR(500) UNIQUE NOT NULL,
    title VARCHAR(500) NOT NULL,
    meta_description VARCHAR(320) NOT NULL,
    h1 VARCHAR(500),
    content TEXT NOT NULL,
    excerpt TEXT,
    quick_facts JSONB,
    faq_schema JSONB,
    data_tables JSONB,
    sport VARCHAR(50),
    content_type VARCHAR(50) NOT NULL,
    league VARCHAR(50),
    game_date DATE,
    featured_image VARCHAR(500),
    og_image VARCHAR(500),
    schema_markup JSONB,
    status VARCHAR(20) NOT NULL DEFAULT 'draft',
    published_at TIMESTAMPTZ,
    page_views INTEGER NOT NULL DEFAULT 0,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS sc_blog_posts_slug_idx ON sc_blog_posts(slug);
CREATE INDEX IF NOT EXISTS sc_blog_posts_status_idx ON sc_blog_posts(status);
CREATE INDEX IF NOT EXISTS sc_blog_posts_sport_idx ON sc_blog_posts(sport);
CREATE INDEX IF NOT EXISTS sc_blog_posts_content_type_idx ON sc_blog_posts(content_type);
CREATE INDEX IF NOT EXISTS sc_blog_posts_published_at_idx ON sc_blog_posts(published_at);
CREATE INDEX IF NOT EXISTS sc_blog_posts_game_date_idx ON sc_blog_posts(game_date);

-- SEO agent job tracking
CREATE TABLE IF NOT EXISTS sc_seo_agent_jobs (
    id BIGSERIAL PRIMARY KEY,
    agent_type VARCHAR(100) NOT NULL,
    job_type VARCHAR(100) NOT NULL,
    config JSONB,
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    result JSONB,
    error TEXT,
    started_at TIMESTAMPTZ,
    completed_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
