import { and, desc, eq } from "drizzle-orm";
import { getDb } from "./db.ts";
import {
  poolSharkTables,
  poolSharkMatches,
  poolSharkParticipants,
  poolSharkWaitingLists,
  poolSharkLeaderboards,
  poolSharkAchievements,
  wallets,
  transactions,
} from "../drizzle/schema.ts";
import type {
  PoolSharkTable,
  PoolSharkMatch,
  PoolSharkParticipant,
  PoolSharkLeaderboard,
  PoolSharkAchievement,
} from "../drizzle/schema.ts";

/**
 * Get all active Pool Shark tables
 */
export async function getPoolSharkTables(): Promise<PoolSharkTable[]> {
  const db = await getDb();
  if (!db) throw new Error("Database not available");
  
  return db
    .select()
    .from(poolSharkTables)
    .where(eq(poolSharkTables.status, 'active'))
    .orderBy(poolSharkTables.sortOrder) as any;
}

/**
 * Get a specific Pool Shark table with current match info
 */
export async function getPoolSharkTableWithMatch(tableId: number) {
  const db = await getDb();
  if (!db) throw new Error("Database not available");
  
  const table = await db
    .select()
    .from(poolSharkTables)
    .where(eq(poolSharkTables.id, tableId))
    .then((rows: any) => rows[0]) as PoolSharkTable | undefined;

  if (!table) return null;

  // Get current match (if any)
  const currentMatch = await db
    .select()
    .from(poolSharkMatches)
    .where(
      and(
        eq(poolSharkMatches.tableId, tableId),
        eq(poolSharkMatches.status, 'in_progress')
      )
    )
    .then((rows: any) => rows[0]) as PoolSharkMatch | undefined;

  // Get participants for current match
  let participants: PoolSharkParticipant[] = [];
  if (currentMatch) {
    participants = await db
      .select()
      .from(poolSharkParticipants)
      .where(eq(poolSharkParticipants.matchId, currentMatch.id)) as any;
  }

  // Get waiting list
  const waitingList = await db
    .select()
    .from(poolSharkWaitingLists)
    .where(eq(poolSharkWaitingLists.tableId, tableId))
    .orderBy(poolSharkWaitingLists.joinedAt) as any;

  return {
    table,
    currentMatch,
    participants,
    waitingList,
  };
}

/**
 * Join a Pool Shark table or add to waiting list
 */
export async function joinPoolSharkTable(userId: number, tableId: number) {
  const db = await getDb();
  if (!db) throw new Error("Database not available");
  
  const table = await db
    .select()
    .from(poolSharkTables)
    .where(eq(poolSharkTables.id, tableId))
    .then((rows: any) => rows[0]) as PoolSharkTable | undefined;

  if (!table) throw new Error('Table not found');

  // Check user balance
  const wallet = await db
    .select()
    .from(wallets)
    .where(eq(wallets.userId, userId))
    .then((rows: any) => rows[0]) as any;

  if (!wallet) throw new Error('Wallet not found');

  // Get current match
  const currentMatch = await db
    .select()
    .from(poolSharkMatches)
    .where(
      and(
        eq(poolSharkMatches.tableId, tableId),
        eq(poolSharkMatches.status, 'in_progress')
      )
    )
    .then((rows: any) => rows[0]) as PoolSharkMatch | undefined;

  if (currentMatch) {
    // Check if match is full
    const participantCount = await db
      .select()
      .from(poolSharkParticipants)
      .where(eq(poolSharkParticipants.matchId, currentMatch.id))
      .then((rows: any) => rows.length);

    if (participantCount >= table.maxPlayers) {
      // Add to waiting list
      const waitingListCount = await db
        .select()
        .from(poolSharkWaitingLists)
        .where(eq(poolSharkWaitingLists.tableId, tableId))
        .then((rows: any) => rows.length);

      await db.insert(poolSharkWaitingLists).values({
        tableId,
        userId,
        position: waitingListCount + 1,
      });

      return { status: 'waiting', matchId: null };
    } else {
      // Add to current match
      await db.insert(poolSharkParticipants).values({
        matchId: currentMatch.id,
        userId,
        entryFee: table.entryFee,
      });

      // Deduct entry fee from wallet
      const newBalance = Number(wallet.scBalance) - Number(table.entryFee);
      await db
        .update(wallets)
        .set({ scBalance: newBalance.toString() })
        .where(eq(wallets.userId, userId));

      // Record transaction
      await db.insert(transactions).values({
        userId,
        type: 'minigame_bet',
        currency: 'SC',
        amount: table.entryFee.toString(),
        balanceBefore: wallet.scBalance.toString(),
        balanceAfter: newBalance.toString(),
        referenceId: `pool_shark_match_${currentMatch.id}`,
        referenceType: 'pool_shark_match',
        description: `Pool Shark table entry fee: ${table.name}`,
      });

      return { status: 'joined', matchId: currentMatch.id };
    }
  } else {
    // Create new match
    const prizePool = Number(table.entryFee) * Number(table.prizeMultiplier);
    const matchResult = await db
      .insert(poolSharkMatches)
      .values({
        tableId,
        status: 'waiting',
        totalPrizePool: prizePool.toString(),
      });

    const matchId = (matchResult[0] as any) as number;
    const match = await db
      .select()
      .from(poolSharkMatches)
      .where(eq(poolSharkMatches.id, matchId))
      .then((rows: any) => rows[0]) as PoolSharkMatch | undefined;

    if (!match) throw new Error('Failed to create match');

    // Add user to match
    await db.insert(poolSharkParticipants).values({
      matchId: match.id,
      userId,
      entryFee: table.entryFee,
    });

    // Deduct entry fee from wallet
    const newBalance = Number(wallet.scBalance) - Number(table.entryFee);
    await db
      .update(wallets)
      .set({ scBalance: newBalance.toString() })
      .where(eq(wallets.userId, userId));

    // Record transaction
    await db.insert(transactions).values({
      userId,
      type: 'minigame_bet',
      currency: 'SC',
      amount: table.entryFee.toString(),
      balanceBefore: wallet.scBalance.toString(),
      balanceAfter: newBalance.toString(),
      referenceId: `pool_shark_match_${match.id}`,
      referenceType: 'pool_shark_match',
      description: `Pool Shark table entry fee: ${table.name}`,
    });

    return { status: 'joined', matchId: match.id };
  }
}

/**
 * Complete a Pool Shark match and award prizes
 */
export async function completePoolSharkMatch(
  matchId: number,
  winnerId: number,
  winnerBreak: number = 0
) {
  const db = await getDb();
  if (!db) throw new Error("Database not available");
  
  const match = await db
    .select()
    .from(poolSharkMatches)
    .where(eq(poolSharkMatches.id, matchId))
    .then((rows: any) => rows[0]) as PoolSharkMatch | undefined;

  if (!match) throw new Error('Match not found');

  // Get all participants
  const participants = await db
    .select()
    .from(poolSharkParticipants)
    .where(eq(poolSharkParticipants.matchId, matchId)) as any;

  if (participants.length === 0) throw new Error('No participants found');

  // Award prize to winner (100% of pool for 1v1)
  const winner = participants.find((p: any) => p.userId === winnerId);
  if (!winner) throw new Error('Winner not found in match');

  const prizeAmount = Number(match.totalPrizePool);

  // Update winner's wallet
  const winnerWallet = await db
    .select()
    .from(wallets)
    .where(eq(wallets.userId, winnerId))
    .then((rows: any) => rows[0]) as any;

  if (winnerWallet) {
    const newBalance = Number(winnerWallet.scBalance) + prizeAmount;
    await db
      .update(wallets)
      .set({ scBalance: newBalance.toString() })
      .where(eq(wallets.userId, winnerId));

    // Record transaction
    await db.insert(transactions).values({
      userId: winnerId,
      type: 'minigame_win',
      currency: 'SC',
      amount: prizeAmount.toString(),
      balanceBefore: winnerWallet.scBalance.toString(),
      balanceAfter: newBalance.toString(),
      referenceId: `pool_shark_match_${matchId}`,
      referenceType: 'pool_shark_match',
      description: `Pool Shark match win: ${prizeAmount} SC`,
    });
  }

  // Update match status
  await db
    .update(poolSharkMatches)
    .set({
      status: 'completed',
      winnerId,
      winnerBreak,
      completedAt: new Date(),
    })
    .where(eq(poolSharkMatches.id, matchId));

  // Update participant positions
  await db
    .update(poolSharkParticipants)
    .set({ position: 1, prizeWon: prizeAmount.toString() })
    .where(eq(poolSharkParticipants.userId, winnerId));

  // Update leaderboards
  await updatePoolSharkLeaderboards(winnerId, participants);

  // Check for achievements
  await checkPoolSharkAchievements(winnerId);
}

/**
 * Update Pool Shark leaderboards
 */
export async function updatePoolSharkLeaderboards(
  winnerId: number,
  participants: any[]
) {
  const db = await getDb();
  if (!db) throw new Error("Database not available");
  
  for (const participant of participants) {
    const isWinner = participant.userId === winnerId;
    const leaderboard = await db
      .select()
      .from(poolSharkLeaderboards)
      .where(eq(poolSharkLeaderboards.userId, participant.userId))
      .then((rows: any) => rows[0]) as PoolSharkLeaderboard | undefined;

    if (leaderboard) {
      const totalMatches = leaderboard.totalMatches + 1;
      const totalWins = leaderboard.totalWins + (isWinner ? 1 : 0);
      const totalLosses = leaderboard.totalLosses + (isWinner ? 0 : 1);
      const winRate = (totalWins / totalMatches) * 100;
      const totalPrizeWon = isWinner
        ? Number(leaderboard.totalPrizeWon) + (participant.prizeWon || 0)
        : Number(leaderboard.totalPrizeWon);

      await db
        .update(poolSharkLeaderboards)
        .set({
          totalMatches,
          totalWins,
          totalLosses,
          winRate: winRate.toString(),
          totalPrizeWon: totalPrizeWon.toString(),
          highestBreak: Math.max(leaderboard.highestBreak, participant.highestBreak || 0),
          totalBallsPocketed: leaderboard.totalBallsPocketed + (participant.ballsPocketed || 0),
        })
        .where(eq(poolSharkLeaderboards.userId, participant.userId));
    } else {
      const totalMatches = 1;
      const totalWins = isWinner ? 1 : 0;
      const totalLosses = isWinner ? 0 : 1;
      const winRate = isWinner ? 100 : 0;
      const totalPrizeWon = isWinner ? participant.prizeWon || 0 : 0;

      await db.insert(poolSharkLeaderboards).values({
        userId: participant.userId,
        totalMatches,
        totalWins,
        totalLosses,
        winRate: winRate.toString(),
        totalPrizeWon: totalPrizeWon.toString(),
        highestBreak: participant.highestBreak || 0,
        totalBallsPocketed: participant.ballsPocketed || 0,
        rank: 0,
      });
    }
  }

  // Recalculate ranks
  const allLeaderboards = await db
    .select()
    .from(poolSharkLeaderboards)
    .orderBy(desc(poolSharkLeaderboards.totalWins)) as any;

  for (let i = 0; i < allLeaderboards.length; i++) {
    await db
      .update(poolSharkLeaderboards)
      .set({ rank: i + 1 })
      .where(eq(poolSharkLeaderboards.id, allLeaderboards[i].id));
  }
}

/**
 * Check and award Pool Shark achievements
 */
export async function checkPoolSharkAchievements(userId: number) {
  const db = await getDb();
  if (!db) throw new Error("Database not available");
  
  const leaderboard = await db
    .select()
    .from(poolSharkLeaderboards)
    .where(eq(poolSharkLeaderboards.userId, userId))
    .then((rows: any) => rows[0]) as PoolSharkLeaderboard | undefined;

  if (!leaderboard) return;

  const achievements = [
    {
      type: 'first_win',
      condition: leaderboard.totalWins >= 1,
      title: 'First Victory',
      description: 'Win your first Pool Shark match',
    },
    {
      type: 'five_wins',
      condition: leaderboard.totalWins >= 5,
      title: 'Five-Time Winner',
      description: 'Win 5 Pool Shark matches',
    },
    {
      type: 'ten_wins',
      condition: leaderboard.totalWins >= 10,
      title: 'Veteran Player',
      description: 'Win 10 Pool Shark matches',
    },
    {
      type: 'fifty_wins',
      condition: leaderboard.totalWins >= 50,
      title: 'Pool Shark',
      description: 'Win 50 Pool Shark matches',
    },
    {
      type: 'hundred_wins',
      condition: leaderboard.totalWins >= 100,
      title: 'Hustler',
      description: 'Win 100 Pool Shark matches',
    },
    {
      type: 'pool_shark',
      condition: Number(leaderboard.winRate) >= 70 && leaderboard.totalMatches >= 20,
      title: 'Pool Shark Legend',
      description: 'Achieve 70% win rate in 20+ matches',
    },
    {
      type: 'high_accuracy',
      condition: Number(leaderboard.averageAccuracy) >= 80,
      title: 'Precision Master',
      description: 'Achieve 80% shot accuracy',
    },
  ];

  for (const achievement of achievements) {
    if (achievement.condition) {
      const existing = await db
        .select()
        .from(poolSharkAchievements)
        .where(
          and(
            eq(poolSharkAchievements.userId, userId),
            eq(poolSharkAchievements.achievementType, achievement.type as any)
          )
        )
        .then((rows: any) => rows[0]) as PoolSharkAchievement | undefined;

      if (!existing) {
        await db.insert(poolSharkAchievements).values({
          userId,
          achievementType: achievement.type as 'first_win' | 'five_wins' | 'ten_wins' | 'fifty_wins' | 'hundred_wins' | 'pool_shark' | 'hustler' | 'legend' | 'perfect_break' | 'high_accuracy' | 'winning_streak' | 'prize_master',
          title: achievement.title,
          description: achievement.description,
        });
      }
    }
  }
}

/**
 * Get Pool Shark leaderboard
 */
export async function getPoolSharkLeaderboard(limit: number = 100, offset: number = 0): Promise<PoolSharkLeaderboard[]> {
  const db = await getDb();
  if (!db) throw new Error("Database not available");
  
  return db
    .select()
    .from(poolSharkLeaderboards)
    .orderBy(desc(poolSharkLeaderboards.totalWins))
    .limit(limit)
    .offset(offset) as any;
}

/**
 * Get user's Pool Shark stats
 */
export async function getUserPoolSharkStats(userId: number) {
  const db = await getDb();
  if (!db) throw new Error("Database not available");
  
  const leaderboard = await db
    .select()
    .from(poolSharkLeaderboards)
    .where(eq(poolSharkLeaderboards.userId, userId))
    .then((rows: any) => rows[0]) as PoolSharkLeaderboard | undefined;

  const achievements = await db
    .select()
    .from(poolSharkAchievements)
    .where(eq(poolSharkAchievements.userId, userId)) as any;

  return { leaderboard, achievements };
}