/**
 * Game Availability Service
 * Manages provider status, availability, and game filtering
 */

import { db } from './db.ts';
import { casinoGames } from '../drizzle/schema.ts';
import { eq, sql } from 'drizzle-orm';

export interface ProviderStatus {
  name: string;
  status: 'active' | 'pending' | 'blocked';
  gameCount: number;
  totalPlays: number;
  avgRtp: number;
  lastUpdated: Date;
  reason?: string;
}

export interface GameAvailabilityStats {
  totalGames: number;
  activeProviders: number;
  blockedProviders: number;
  totalPlays: number;
  providers: ProviderStatus[];
}

const RESTRICTED_PROVIDERS = ['PG Soft', 'Yggdrasil', 'NextGen', 'AGS'];
const PENDING_PROVIDERS: string[] = [];

export async function getGameAvailabilityStats(): Promise<GameAvailabilityStats> {
  try {
    // Get all providers and their stats
    const providerStats = await db.execute(sql`
      SELECT 
        provider,
        COUNT(*) as gameCount,
        SUM(playCount) as totalPlays,
        AVG(rtp) as avgRtp
      FROM casinoGames
      GROUP BY provider
      ORDER BY gameCount DESC
    `);

    const providers: ProviderStatus[] = providerStats.map((row: any) => {
      let status: 'active' | 'pending' | 'blocked' = 'active';
      let reason: string | undefined;

      if (RESTRICTED_PROVIDERS.includes(row.provider)) {
        status = 'blocked';
        reason = 'Provider restricted from platform';
      } else if (PENDING_PROVIDERS.includes(row.provider)) {
        status = 'pending';
        reason = 'Awaiting approval';
      }

      return {
        name: row.provider,
        status,
        gameCount: row.gameCount || 0,
        totalPlays: row.totalPlays || 0,
        avgRtp: parseFloat(row.avgRtp || 0).toFixed(2),
        lastUpdated: new Date(),
        reason,
      };
    });

    const totalGames = providers.reduce((sum, p) => sum + p.gameCount, 0);
    const activeProviders = providers.filter(p => p.status === 'active').length;
    const blockedProviders = providers.filter(p => p.status === 'blocked').length;
    const totalPlays = providers.reduce((sum, p) => sum + p.totalPlays, 0);

    return {
      totalGames,
      activeProviders,
      blockedProviders,
      totalPlays,
      providers,
    };
  } catch (error) {
    console.error('Error fetching game availability stats:', error);
    throw error;
  }
}

export async function getProviderStatus(providerName: string): Promise<ProviderStatus | null> {
  try {
    const result = await db.execute(sql`
      SELECT 
        provider,
        COUNT(*) as gameCount,
        SUM(playCount) as totalPlays,
        AVG(rtp) as avgRtp
      FROM casinoGames
      WHERE provider = ?
      GROUP BY provider
    `, [providerName]);

    if (!result || result.length === 0) {
      return null;
    }

    const row = result[0];
    let status: 'active' | 'pending' | 'blocked' = 'active';
    let reason: string | undefined;

    if (RESTRICTED_PROVIDERS.includes(row.provider)) {
      status = 'blocked';
      reason = 'Provider restricted from platform';
    } else if (PENDING_PROVIDERS.includes(row.provider)) {
      status = 'pending';
      reason = 'Awaiting approval';
    }

    return {
      name: row.provider,
      status,
      gameCount: row.gameCount || 0,
      totalPlays: row.totalPlays || 0,
      avgRtp: parseFloat(row.avgRtp || 0).toFixed(2),
      lastUpdated: new Date(),
      reason,
    };
  } catch (error) {
    console.error('Error fetching provider status:', error);
    throw error;
  }
}

export async function getActiveProviders(): Promise<string[]> {
  try {
    const result = await db.execute(sql`
      SELECT DISTINCT provider
      FROM casinoGames
      WHERE isActive = 1 AND provider NOT IN (${RESTRICTED_PROVIDERS.map(() => '?').join(',')})
      ORDER BY provider
    `, RESTRICTED_PROVIDERS);

    return result.map((row: any) => row.provider);
  } catch (error) {
    console.error('Error fetching active providers:', error);
    throw error;
  }
}

export async function getBlockedProviders(): Promise<ProviderStatus[]> {
  try {
    const stats = await getGameAvailabilityStats();
    return stats.providers.filter(p => p.status === 'blocked');
  } catch (error) {
    console.error('Error fetching blocked providers:', error);
    throw error;
  }
}

export async function getPendingProviders(): Promise<ProviderStatus[]> {
  try {
    const stats = await getGameAvailabilityStats();
    return stats.providers.filter(p => p.status === 'pending');
  } catch (error) {
    console.error('Error fetching pending providers:', error);
    throw error;
  }
}

export async function getGamesByProvider(providerName: string, limit = 50, offset = 0) {
  try {
    return await db.query.casinoGames.findMany({
      where: eq(casinoGames.provider, providerName),
      limit,
      offset,
      orderBy: (games) => [sql`${games.playCount} DESC`],
    });
  } catch (error) {
    console.error('Error fetching games by provider:', error);
    throw error;
  }
}

export function isProviderBlocked(providerName: string): boolean {
  return RESTRICTED_PROVIDERS.includes(providerName);
}

export function isProviderPending(providerName: string): boolean {
  return PENDING_PROVIDERS.includes(providerName);
}

export function isProviderActive(providerName: string): boolean {
  return !isProviderBlocked(providerName) && !isProviderPending(providerName);
}