import { db } from './db';
import { sql } from 'drizzle-orm';

export async function createMtnMobileDailyUsageTable(): Promise<void> {
  try {
    console.log('Creating MTN Mobile Daily Usage table...');
    
    // Create the table
    await db.execute(sql`
      CREATE TABLE IF NOT EXISTS mtn_mobile_daily_usage (
        id SERIAL PRIMARY KEY,
        username VARCHAR(255) NOT NULL,
        year INTEGER NOT NULL,
        month INTEGER NOT NULL,
        day INTEGER NOT NULL,
        date DATE NOT NULL,
        usage_bytes BIGINT NOT NULL DEFAULT 0,
        usage_gb VARCHAR(20) NOT NULL DEFAULT '0.000',
        entries INTEGER NOT NULL DEFAULT 0,
        success BOOLEAN NOT NULL DEFAULT true,
        error TEXT,
        last_synced_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
      )
    `);

    // Create unique constraint
    await db.execute(sql`
      CREATE UNIQUE INDEX IF NOT EXISTS unique_usage_per_day 
      ON mtn_mobile_daily_usage (username, year, month, day)
    `);

    // Create indexes for faster queries
    await db.execute(sql`
      CREATE INDEX IF NOT EXISTS mtn_mobile_daily_usage_username_idx 
      ON mtn_mobile_daily_usage (username)
    `);

    await db.execute(sql`
      CREATE INDEX IF NOT EXISTS mtn_mobile_daily_usage_date_idx 
      ON mtn_mobile_daily_usage (year, month, day)
    `);

    console.log('MTN Mobile Daily Usage table created successfully');
  } catch (error) {
    console.error('Error creating MTN Mobile Daily Usage table:', error);
    throw error;
  }
}