src/db.ts

import postgres from 'postgres'

const sql = postgres(process.env.DATABASE_URL!, {
  max: 10,
  idle_timeout: 20,
  connect_timeout: 10,
  transform: {
    undefined: null,
  },
})

export { sql }

export async function initializeDatabase() {
  try {
    await sql`
      CREATE TABLE IF NOT EXISTS users (
        id VARCHAR(50) PRIMARY KEY,
        github_id INTEGER UNIQUE NOT NULL,
        username VARCHAR(100) NOT NULL,
        email VARCHAR(255) UNIQUE,
        avatar_url TEXT,
        plan VARCHAR(20) DEFAULT 'free',
        created_at TIMESTAMP DEFAULT NOW(),
        updated_at TIMESTAMP DEFAULT NOW()
      )
    `

    await sql`
      CREATE TABLE IF NOT EXISTS user_events (
        id SERIAL PRIMARY KEY,
        event_type VARCHAR(50) NOT NULL,
        user_id VARCHAR(100),
        session_id VARCHAR(100) NOT NULL,
        page_url TEXT,
        referrer TEXT,
        user_agent TEXT,
        ip_hash VARCHAR(64),
        metadata JSONB,
        created_at TIMESTAMP DEFAULT NOW()
      )
    `

    await sql`
      CREATE TABLE IF NOT EXISTS revenue_events (
        id SERIAL PRIMARY KEY,
        event_type VARCHAR(50) NOT NULL,
        amount DECIMAL(10, 2) NOT NULL,
        currency VARCHAR(3) DEFAULT 'USD',
        customer_id VARCHAR(100),
        github_id INTEGER,
        subscription_tier VARCHAR(20),
        metadata JSONB,
        created_at TIMESTAMP DEFAULT NOW()
      )
    `

    await sql`
      CREATE TABLE IF NOT EXISTS affiliate_users (
        id VARCHAR(100) PRIMARY KEY,
        github_id INTEGER UNIQUE NOT NULL,
        username VARCHAR(100) NOT NULL,
        affiliate_code VARCHAR(50) UNIQUE NOT NULL,
        referral_count INTEGER DEFAULT 0,
        paid_referral_count INTEGER DEFAULT 0,
        tier VARCHAR(20) DEFAULT 'free',
        created_at TIMESTAMP DEFAULT NOW()
      )
    `

    await sql`
      CREATE TABLE IF NOT EXISTS referrals (
        id VARCHAR(100) PRIMARY KEY,
        affiliate_id VARCHAR(100) NOT NULL REFERENCES affiliate_users(id),
        referred_github_id INTEGER UNIQUE NOT NULL,
        referred_username VARCHAR(100) NOT NULL,
        referred_ip_hash VARCHAR(64) NOT NULL,
        has_purchased BOOLEAN DEFAULT FALSE,
        created_at TIMESTAMP DEFAULT NOW()
      )
    `

    await sql`
      CREATE TABLE IF NOT EXISTS affiliate_events (
        id SERIAL PRIMARY KEY,
        event_type VARCHAR(50) NOT NULL,
        affiliate_id VARCHAR(100) NOT NULL,
        affiliate_code VARCHAR(50),
        referrer_id VARCHAR(100),
        commission_amount DECIMAL(10, 2) DEFAULT 0,
        conversion_status VARCHAR(20) DEFAULT 'pending',
        metadata JSONB,
        created_at TIMESTAMP DEFAULT NOW()
      )
    `

    await sql`
      CREATE TABLE IF NOT EXISTS dashboard_metrics (
        id SERIAL PRIMARY KEY,
        metric_type VARCHAR(50) NOT NULL,
        metric_name VARCHAR(100) NOT NULL,
        metric_value DECIMAL(15, 2) NOT NULL,
        metadata JSONB,
        recorded_at TIMESTAMP DEFAULT NOW(),
        UNIQUE(metric_type, metric_name, recorded_at)
      )
    `

    await sql`
      CREATE TABLE IF NOT EXISTS daily_aggregates (
        id SERIAL PRIMARY KEY,
        date DATE NOT NULL,
        metric_category VARCHAR(50) NOT NULL,
        metric_name VARCHAR(100) NOT NULL,
        total_value DECIMAL(15, 2) NOT NULL,
        count INTEGER DEFAULT 1,
        metadata JSONB,
        created_at TIMESTAMP DEFAULT NOW(),
        UNIQUE(date, metric_category, metric_name)
      )
    `

    console.log('Backend database initialized successfully')
  } catch (error) {
    console.error('Failed to initialize backend database:', error)
  }
}