import { getDb } from "./db.ts";
import { guilds, guildMembers, playerStats, referrals, userVipStatus, vipTiers, tournaments, tournamentParticipants } from "../drizzle/schema.ts";
import { eq, and, desc, gte, lte } from "drizzle-orm";

// ─── GUILD MANAGEMENT ─────────────────────────────────────────────────────────

export async function createGuild(leaderId: number, name: string, description?: string, logoUrl?: string) {
  const db = await getDb();
  if (!db) throw new Error("Database unavailable");

  const result = await db.insert(guilds).values({
    leaderId,
    name,
    description,
    logoUrl,
    memberCount: 1,
  });

  // Get the inserted guild ID
  const insertedGuild = await db.select().from(guilds).where(eq(guilds.name, name)).limit(1);
  const guildId = insertedGuild[0]?.id || 0;

  // Add leader as member
  await db.insert(guildMembers).values({
    guildId,
    userId: leaderId,
    role: "leader",
  });

  return { id: guildId, name, leaderId };
}

export async function getGuildById(guildId: number) {
  const db = await getDb();
  if (!db) throw new Error("Database unavailable");

  const guild = await db.select().from(guilds).where(eq(guilds.id, guildId)).limit(1);
  return guild[0] || null;
}

export async function getGuildMembers(guildId: number) {
  const db = await getDb();
  if (!db) throw new Error("Database unavailable");

  return await db.select().from(guildMembers).where(eq(guildMembers.guildId, guildId));
}

export async function joinGuild(guildId: number, userId: number) {
  const db = await getDb();
  if (!db) throw new Error("Database unavailable");

  // Check if already a member
  const existing = await db
    .select()
    .from(guildMembers)
    .where(and(eq(guildMembers.guildId, guildId), eq(guildMembers.userId, userId)))
    .limit(1);

  if (existing.length > 0) {
    throw new Error("User is already a member of this guild");
  }

  // Add member
  await db.insert(guildMembers).values({
    guildId,
    userId,
    role: "member",
  });

  // Increment member count
  const guild = await db.select().from(guilds).where(eq(guilds.id, guildId)).limit(1);
  const newCount = (guild[0]?.memberCount || 0) + 1;
  await db
    .update(guilds)
    .set({ memberCount: newCount })
    .where(eq(guilds.id, guildId));

  return { guildId, userId, role: "member" };
}

export async function leaveGuild(guildId: number, userId: number) {
  const db = await getDb();
  if (!db) throw new Error("Database unavailable");

  // Check if leader
  const member = await db
    .select()
    .from(guildMembers)
    .where(and(eq(guildMembers.guildId, guildId), eq(guildMembers.userId, userId)))
    .limit(1);

  if (member[0]?.role === "leader") {
    throw new Error("Leader cannot leave guild");
  }

  // Remove member
  await db
    .delete(guildMembers)
    .where(and(eq(guildMembers.guildId, guildId), eq(guildMembers.userId, userId)));

  // Decrement member count
  const guild = await db.select().from(guilds).where(eq(guilds.id, guildId)).limit(1);
  const newCount = Math.max(0, (guild[0]?.memberCount || 1) - 1);
  await db
    .update(guilds)
    .set({ memberCount: newCount })
    .where(eq(guilds.id, guildId));

  return { success: true };
}

// ─── PLAYER STATISTICS ────────────────────────────────────────────────────────

export async function getPlayerStats(userId: number) {
  const db = await getDb();
  if (!db) throw new Error("Database unavailable");

  let stats = await db.select().from(playerStats).where(eq(playerStats.userId, userId)).limit(1);

  if (!stats.length) {
    // Create initial stats
    const result = await db.insert(playerStats).values({ userId });
    stats = await db.select().from(playerStats).where(eq(playerStats.userId, userId)).limit(1);
  }

  return stats[0];
}

export async function updatePlayerStats(userId: number, updates: Partial<typeof playerStats.$inferInsert>) {
  const db = await getDb();
  if (!db) throw new Error("Database unavailable");

  // Ensure stats exist
  await getPlayerStats(userId);

  return await db.update(playerStats).set(updates).where(eq(playerStats.userId, userId));
}

export async function recordGameResult(
  userId: number,
  gameType: "poker" | "bingo" | "poolShark" | "sportsbook",
  won: boolean,
  betAmount: number,
  winAmount: number = 0
) {
  const db = await getDb();
  if (!db) throw new Error("Database unavailable");

  const stats = await getPlayerStats(userId);
  const updates: Partial<typeof playerStats.$inferInsert> = {};

  if (gameType === "poker") {
    updates.pokerTotalBet = (stats.pokerTotalBet as any) + betAmount;
    if (won) {
      updates.pokerWins = stats.pokerWins + 1;
      updates.pokerTotalWon = (stats.pokerTotalWon as any) + winAmount;
    } else {
      updates.pokerLosses = stats.pokerLosses + 1;
    }
  } else if (gameType === "bingo") {
    updates.bingoTotalBet = (stats.bingoTotalBet as any) + betAmount;
    if (won) {
      updates.bingoWins = stats.bingoWins + 1;
      updates.bingoTotalWon = (stats.bingoTotalWon as any) + winAmount;
    }
  } else if (gameType === "poolShark") {
    updates.poolSharkTotalBet = (stats.poolSharkTotalBet as any) + betAmount;
    if (won) {
      updates.poolSharkWins = stats.poolSharkWins + 1;
      updates.poolSharkTotalWon = (stats.poolSharkTotalWon as any) + winAmount;
    }
  } else if (gameType === "sportsbook") {
    updates.sportsbookTotalBet = (stats.sportsbookTotalBet as any) + betAmount;
    if (won) {
      updates.sportsbookWins = stats.sportsbookWins + 1;
      updates.sportsbookTotalWon = (stats.sportsbookTotalWon as any) + winAmount;
    }
  }

  return await updatePlayerStats(userId, updates);
}

// ─── VIP TIER MANAGEMENT ──────────────────────────────────────────────────────

export async function getUserVipStatus(userId: number) {
  const db = await getDb();
  if (!db) throw new Error("Database unavailable");

  let status = await db.select().from(userVipStatus).where(eq(userVipStatus.userId, userId)).limit(1);

  if (!status.length) {
    // Create initial VIP status
    await db.insert(userVipStatus).values({ userId });
    status = await db.select().from(userVipStatus).where(eq(userVipStatus.userId, userId)).limit(1);
  }

  return status[0];
}

export async function getVipTierByWinnings(lifetimeWinnings: number) {
  const db = await getDb();
  if (!db) throw new Error("Database unavailable");

  const tiers = await db
    .select()
    .from(vipTiers)
    .where(gte(vipTiers.minLifetimeWinnings, lifetimeWinnings.toString()))
    .orderBy(desc(vipTiers.minLifetimeWinnings))
    .limit(1);

  return tiers[0] || null;
}

export async function updateVipTier(userId: number, lifetimeWinnings: number) {
  const db = await getDb();
  if (!db) throw new Error("Database unavailable");

  const tier = await getVipTierByWinnings(lifetimeWinnings);
  if (!tier) return null;

  await db
    .update(userVipStatus)
    .set({ currentVipTierId: tier.id, lifetimeWinnings: lifetimeWinnings.toString() })
    .where(eq(userVipStatus.userId, userId));

  return tier;
}

// ─── REFERRAL SYSTEM ──────────────────────────────────────────────────────────

export async function createReferralCode(referrerId: number, refereeId: number) {
  const db = await getDb();
  if (!db) throw new Error("Database unavailable");

  const code = `REF-${referrerId}-${refereeId}-${Date.now()}`;
  const link = `https://coinkrazy.manus.space/join?ref=${code}`;

  await db.insert(referrals).values({
    referrerId,
    refereeId,
    referralCode: code,
    referralLink: link,
    source: "other",
    status: "pending",
  });

  // Get the inserted referral
  const inserted = await db.select().from(referrals).where(eq(referrals.referralCode, code)).limit(1);
  return { id: inserted[0]?.id || 0, code, link };
}

export async function activateReferral(referralId: number) {
  const db = await getDb();
  if (!db) throw new Error("Database unavailable");

  return await db
    .update(referrals)
    .set({
      status: "completed",
      completedAt: new Date(),
      referrerRewardGiven: true,
      refereeRewardGiven: true,
    })
    .where(eq(referrals.id, referralId));
}

export async function getReferralsByReferrer(referrerId: number) {
  const db = await getDb();
  if (!db) throw new Error("Database unavailable");

  return await db.select().from(referrals).where(eq(referrals.referrerId, referrerId));
}

// ─── TOURNAMENT MANAGEMENT ────────────────────────────────────────────────────

export async function createTournament(
  name: string,
  gameType: string,
  startAt: Date,
  endAt: Date,
  maxParticipants: number,
  entryFeeGc: number,
  prizePool: number,
  description?: string
) {
  const db = await getDb();
  if (!db) throw new Error("Database unavailable");

  await db.insert(tournaments).values({
    name,
    startAt,
    endAt,
    maxParticipants,
    entryFeeGc,
    prizePool: prizePool.toString(),
    description,
    status: "upcoming",
    createdBy: 1,
  });

  // Get the inserted tournament
  const inserted = await db.select().from(tournaments).where(eq(tournaments.name, name)).orderBy(desc(tournaments.updatedAt)).limit(1);
  return { id: inserted[0]?.id || 0, name };
}

export async function getTournamentById(tournamentId: number) {
  const db = await getDb();
  if (!db) throw new Error("Database unavailable");

  const tournament = await db.select().from(tournaments).where(eq(tournaments.id, tournamentId)).limit(1);
  return tournament[0] || null;
}

export async function joinTournament(tournamentId: number, userId: number) {
  const db = await getDb();
  if (!db) throw new Error("Database unavailable");

  // Check tournament exists
  const tournament = await getTournamentById(tournamentId);
  if (!tournament) throw new Error("Tournament not found");

  // Add participant
  await db.insert(tournamentParticipants).values({
    tournamentId,
    userId,
  });

  // Increment participant count
  const currentCount = tournament.currentParticipants || 0;
  await db
    .update(tournaments)
    .set({ currentParticipants: currentCount + 1 })
    .where(eq(tournaments.id, tournamentId));

  return { tournamentId, userId };
}

export async function getTournamentParticipants(tournamentId: number) {
  const db = await getDb();
  if (!db) throw new Error("Database unavailable");

  return await db.select().from(tournamentParticipants).where(eq(tournamentParticipants.tournamentId, tournamentId));
}

export async function completeTournamentMatch(
  tournamentId: number,
  userId: number,
  finalRank: number,
  prizeWon: number
) {
  const db = await getDb();
  if (!db) throw new Error("Database unavailable");

  return await db
    .update(tournamentParticipants)
    .set({
      finalRank,
      prizeWon: prizeWon.toString(),
    })
    .where(and(eq(tournamentParticipants.tournamentId, tournamentId), eq(tournamentParticipants.userId, userId)));
}

export async function getActiveTournaments() {
  const db = await getDb();
  if (!db) throw new Error("Database unavailable");

  return await db
    .select()
    .from(tournaments)
    .where(eq(tournaments.status, "active"))
    .orderBy(desc(tournaments.startAt));
}

export async function getUpcomingTournaments(limit: number = 10) {
  const db = await getDb();
  if (!db) throw new Error("Database unavailable");

  return await db
    .select()
    .from(tournaments)
    .where(eq(tournaments.status, "upcoming"))
    .orderBy(tournaments.startAt)
    .limit(limit);
}