storage.ts

import { db } from "./db";
import {
  admins, sports, teams, matches, cricketStats, pointsTable, gcPoints,
  type Admin, type InsertAdmin,
  type Sport, type InsertSport,
  type Team, type InsertTeam,
  type Match, type InsertMatch,
  type CricketStat, type InsertCricketStat,
  type PointsEntry, type InsertPointsEntry,
  type GCPointsEntry, type InsertGCPointsEntry
} from "@shared/schema";
import { eq, desc, asc, and } from "drizzle-orm";

export interface IStorage {
  // Admin
  getAdminByUsername(username: string): Promise<Admin | undefined>;
  createAdmin(admin: InsertAdmin): Promise<Admin>;

  // Sports
  getSports(): Promise<Sport[]>;
  getSport(id: number): Promise<Sport | undefined>;
  createSport(sport: InsertSport): Promise<Sport>;

  // Matches
  getMatches(sportId?: number, status?: string): Promise<Match[]>;
  getMatch(id: number): Promise<Match | undefined>;
  createMatch(match: InsertMatch): Promise<Match>;
  updateMatch(id: number, match: Partial<InsertMatch>): Promise<Match>;
  deleteMatch(id: number): Promise<void>;

  // Teams
  getTeams(sportId?: number): Promise<Team[]>;
  createTeam(team: InsertTeam): Promise<Team>;
  updateTeam(id: number, updates: Partial<InsertTeam>): Promise<Team>;
  deleteTeam(id: number): Promise<void>;

  // Cricket Stats
  getCricketStats(): Promise<CricketStat[]>;
  updateCricketStat(stat: InsertCricketStat): Promise<CricketStat>;

  // Points Table
  getPointsTable(sportId: number): Promise<PointsEntry[]>;
  updatePointsEntry(entry: InsertPointsEntry): Promise<PointsEntry>;

  // GC Points
  getGCPoints(): Promise<GCPointsEntry[]>;
  createGCPoints(entry: InsertGCPointsEntry): Promise<GCPointsEntry>;
  updateGCPoints(id: number, entry: Partial<InsertGCPointsEntry>): Promise<GCPointsEntry>;
  deleteGCPoints(id: number): Promise<void>;
}

export class DatabaseStorage implements IStorage {
  // Admin
  async getAdminByUsername(username: string): Promise<Admin | undefined> {
    const [admin] = await db.select().from(admins).where(eq(admins.username, username));
    return admin;
  }
  async createAdmin(admin: InsertAdmin): Promise<Admin> {
    const [newAdmin] = await db.insert(admins).values(admin).returning();
    return newAdmin;
  }

  // Sports
  async getSports(): Promise<Sport[]> {
    return await db.select().from(sports);
  }
  async getSport(id: number): Promise<Sport | undefined> {
    const [sport] = await db.select().from(sports).where(eq(sports.id, id));
    return sport;
  }
  async createSport(sport: InsertSport): Promise<Sport> {
    const [newSport] = await db.insert(sports).values(sport).returning();
    return newSport;
  }

  // Matches
  async getMatches(sportId?: number, status?: string): Promise<Match[]> {
    let query = db.select().from(matches);
    const filters = [];
    if (sportId) filters.push(eq(matches.sportId, sportId));
    if (status) filters.push(eq(matches.status, status));

    if (filters.length > 0) {
      // @ts-ignore - straightforward stacking of where clauses
      filters.forEach(filter => query.where(filter));
    }

    return await query.orderBy(asc(matches.date));
  }
  async getMatch(id: number): Promise<Match | undefined> {
    const [match] = await db.select().from(matches).where(eq(matches.id, id));
    return match;
  }
  async createMatch(match: InsertMatch): Promise<Match> {
    const [newMatch] = await db.insert(matches).values(match as any).returning();
    return newMatch;
  }
  async updateMatch(id: number, matchUpdates: Partial<InsertMatch>): Promise<Match> {
    const [updated] = await db.update(matches).set(matchUpdates as any).where(eq(matches.id, id)).returning();

    // Automatically update points table when a match is completed
    if (matchUpdates.status === 'completed' && updated) {
      await this.calculatePointsForSport(updated.sportId);
    }

    // Update player stats for Orange/Purple caps if cricket match
    if (updated && updated.details && updated.details.innings) {
      const sport = await this.getSport(updated.sportId);
      if (sport?.name.toLowerCase() === 'cricket') {
        for (const inning of updated.details.innings) {
          const battingStats = inning.battingStats || [];
          const bowlingStats = inning.bowlingStats || [];

          for (const stat of battingStats) {
            await this.updateCricketStat({
              playerName: stat.playerName,
              teamName: inning.team,
              runs: stat.runs,
              wickets: 0,
              matchesPlayed: 1
            });
          }

          for (const stat of bowlingStats) {
            await this.updateCricketStat({
              playerName: stat.playerName,
              teamName: inning.team,
              runs: 0,
              wickets: stat.wickets,
              matchesPlayed: 1
            });
          }
        }
      }
    }

    return updated;
  }

  async calculatePointsForSport(sportId: number) {
    const sportMatches = await db.select().from(matches).where(and(eq(matches.sportId, sportId), eq(matches.status, 'completed')));
    const sportTeams = await db.select().from(teams).where(eq(teams.sportId, sportId));

    // Reset points for this sport
    await db.delete(pointsTable).where(eq(pointsTable.sportId, sportId));

    const tableData: Record<string, { won: number, matches: number, points: number }> = {};

    for (const team of sportTeams) {
      tableData[team.name] = { won: 0, matches: 0, points: 0 };
    }

    for (const match of sportMatches) {
      if (tableData[match.teamA]) {
        tableData[match.teamA].matches++;
      }
      if (tableData[match.teamB]) {
        tableData[match.teamB].matches++;
      }

      if (match.winner && tableData[match.winner]) {
        tableData[match.winner].won++;
        tableData[match.winner].points += 2;
      }
    }

    for (const [teamName, data] of Object.entries(tableData)) {
      await db.insert(pointsTable).values({
        sportId,
        teamName,
        matches: data.matches,
        won: data.won,
        lost: data.matches - data.won,
        points: data.points,
        nrr: "0.000"
      });
    }
  }
  async deleteMatch(id: number): Promise<void> {
    await db.delete(matches).where(eq(matches.id, id));
  }

  // Teams
  async getTeams(sportId?: number): Promise<Team[]> {
    if (sportId) {
      return await db.select().from(teams).where(eq(teams.sportId, sportId));
    }
    return await db.select().from(teams);
  }
  async createTeam(team: InsertTeam): Promise<Team> {
    const [newTeam] = await db.insert(teams).values(team as any).returning();
    return newTeam;
  }
  async updateTeam(id: number, updates: Partial<InsertTeam>): Promise<Team> {
    const [updated] = await db.update(teams).set(updates as any).where(eq(teams.id, id)).returning();
    return updated;
  }
  async deleteTeam(id: number): Promise<void> {
    await db.delete(teams).where(eq(teams.id, id));
  }

  // Cricket Stats
  async getCricketStats(): Promise<CricketStat[]> {
    return await db.select().from(cricketStats).orderBy(desc(cricketStats.runs));
  }
  async updateCricketStat(stat: InsertCricketStat): Promise<CricketStat> {
    // Upsert logic could be here, but for simplicity we'll just insert or update based on player/team combo
    const existing = await db.select().from(cricketStats)
      .where(eq(cricketStats.playerName, stat.playerName));

    if (existing.length > 0) {
      const [updated] = await db.update(cricketStats)
        .set(stat)
        .where(eq(cricketStats.id, existing[0].id))
        .returning();
      return updated;
    }
    const [created] = await db.insert(cricketStats).values(stat).returning();
    return created;
  }

  // Points Table
  async getPointsTable(sportId: number): Promise<PointsEntry[]> {
    return await db.select().from(pointsTable)
      .where(eq(pointsTable.sportId, sportId))
      .orderBy(desc(pointsTable.points));
  }
  async updatePointsEntry(entry: InsertPointsEntry): Promise<PointsEntry> {
    // Simple insert for now, real app would update existing row
    const [newEntry] = await db.insert(pointsTable).values(entry).returning();
    return newEntry;
  }

  // GC Points
  async getGCPoints(): Promise<GCPointsEntry[]> {
    return await db.select().from(gcPoints).orderBy(asc(gcPoints.rank));
  }
  async createGCPoints(entry: InsertGCPointsEntry): Promise<GCPointsEntry> {
    const [newEntry] = await db.insert(gcPoints).values(entry).returning();
    return newEntry;
  }
  async updateGCPoints(id: number, entry: Partial<InsertGCPointsEntry>): Promise<GCPointsEntry> {
    const [updated] = await db.update(gcPoints).set(entry).where(eq(gcPoints.id, id)).returning();
    return updated;
  }
  async deleteGCPoints(id: number): Promise<void> {
    await db.delete(gcPoints).where(eq(gcPoints.id, id));
  }
}

export const storage = new DatabaseStorage();