import { storage } from "./storage";
import { db } from "./db";
import { eq, sql, gte, lte, and, desc } from "drizzle-orm";
import {
  matches,
  attendanceSessions,
  attendanceRecords,
  players,
  teams,
  paymentActivities,
  membershipFees,
} from "@shared/schema";

export interface FixturesReport {
  total: number;
  completed: number;
  upcoming: number;
  byMonth: Array<{ month: string; count: number }>;
  byTeam: Array<{ teamId: number; teamName: string; matches: number }>;
}

export interface AttendanceReport {
  totalSessions: number;
  totalAttendance: number;
  averageAttendanceRate: number;
  byMonth: Array<{ month: string; rate: number }>;
  topAttenders: Array<{ playerId: number; playerName: string; attendanceRate: number; sessionsAttended: number }>;
  absentees: Array<{ playerId: number; playerName: string; absences: number }>;
}

export interface RevenueReport {
  totalRevenue: number;
  byMonth: Array<{ month: string; amount: number }>;
  byType: Array<{ type: string; amount: number }>;
  outstandingPayments: number;
  paidCount: number;
  pendingCount: number;
}

export interface TopScorersReport {
  topPlayers: Array<{ 
    playerId: number; 
    playerName: string; 
    position: string;
    teamName: string;
    stats: {
      matches: number;
      attendanceRate: number;
    };
  }>;
}

export class ReportsService {
  async getFixturesReport(filters?: {
    organizationId?: number;
    teamId?: number;
    startDate?: Date;
    endDate?: Date;
  }): Promise<FixturesReport> {
    const conditions = [];
    
    if (filters?.teamId) {
      conditions.push(eq(matches.team1Id, filters.teamId));
    }
    if (filters?.startDate) {
      conditions.push(gte(matches.scheduledTime, filters.startDate));
    }
    if (filters?.endDate) {
      conditions.push(lte(matches.scheduledTime, filters.endDate));
    }

    const query = conditions.length > 0 
      ? db.select().from(matches).where(and(...conditions))
      : db.select().from(matches);

    const allMatches = await query;

    const completed = allMatches.filter(m => m.status === 'completed').length;
    const upcoming = allMatches.filter(m => m.status === 'scheduled').length;

    // Group by month
    const byMonth = allMatches.reduce((acc: any, match) => {
      if (match.scheduledTime) {
        const month = new Date(match.scheduledTime).toLocaleDateString('en-US', { year: 'numeric', month: 'short' });
        if (!acc[month]) acc[month] = 0;
        acc[month]++;
      }
      return acc;
    }, {});

    // Get team data
    const allTeams = await storage.getTeams();
    const teamMap = new Map(allTeams.map(t => [t.id, t.name]));

    const byTeam = allMatches.reduce((acc: any, match) => {
      if (match.team1Id) {
        const teamName = teamMap.get(match.team1Id) || 'Unknown';
        if (!acc[match.team1Id]) {
          acc[match.team1Id] = { teamId: match.team1Id, teamName, matches: 0 };
        }
        acc[match.team1Id].matches++;
      }
      return acc;
    }, {});

    return {
      total: allMatches.length,
      completed,
      upcoming,
      byMonth: Object.entries(byMonth).map(([month, count]) => ({ month, count: count as number })),
      byTeam: Object.values(byTeam),
    };
  }

  async getAttendanceReport(filters?: {
    organizationId?: number;
    teamId?: number;
    startDate?: Date;
    endDate?: Date;
  }): Promise<AttendanceReport> {
    const sessions = await storage.getAttendanceSessions(filters?.organizationId, filters?.teamId);
    
    let filteredSessions = sessions;
    if (filters?.startDate) {
      filteredSessions = filteredSessions.filter(s => new Date(s.date) >= filters.startDate!);
    }
    if (filters?.endDate) {
      filteredSessions = filteredSessions.filter(s => new Date(s.date) <= filters.endDate!);
    }

    // Get all attendance records for these sessions
    const allRecords = await Promise.all(
      filteredSessions.map(s => storage.getAttendanceRecords(s.id))
    );
    const flatRecords = allRecords.flat();

    const totalSessions = filteredSessions.length;
    const totalAttendance = flatRecords.filter(r => r.status === 'present').length;
    const averageAttendanceRate = flatRecords.length > 0 
      ? Math.round((totalAttendance / flatRecords.length) * 100) 
      : 0;

    // Group by month
    const byMonth = filteredSessions.reduce((acc: any, session) => {
      const month = new Date(session.date).toLocaleDateString('en-US', { year: 'numeric', month: 'short' });
      if (!acc[month]) acc[month] = { total: 0, present: 0 };
      
      const sessionRecords = flatRecords.filter(r => r.sessionId === session.id);
      acc[month].total += sessionRecords.length;
      acc[month].present += sessionRecords.filter(r => r.status === 'present').length;
      
      return acc;
    }, {});

    const monthlyRates = Object.entries(byMonth).map(([month, data]: [string, any]) => ({
      month,
      rate: data.total > 0 ? Math.round((data.present / data.total) * 100) : 0,
    }));

    // Get player statistics
    const allPlayers = await storage.getPlayers();
    const playerStats = allPlayers.map(player => {
      const playerRecords = flatRecords.filter(r => r.playerId === player.id);
      const attended = playerRecords.filter(r => r.status === 'present').length;
      const absences = playerRecords.filter(r => r.status === 'absent').length;
      const rate = playerRecords.length > 0 ? Math.round((attended / playerRecords.length) * 100) : 0;

      return {
        playerId: player.id,
        playerName: `${player.firstName} ${player.lastName}`,
        attendanceRate: rate,
        sessionsAttended: attended,
        absences,
      };
    });

    const topAttenders = playerStats
      .filter(p => p.sessionsAttended > 0)
      .sort((a, b) => b.attendanceRate - a.attendanceRate)
      .slice(0, 10);

    const absentees = playerStats
      .filter(p => p.absences > 2)
      .sort((a, b) => b.absences - a.absences)
      .slice(0, 10);

    return {
      totalSessions,
      totalAttendance,
      averageAttendanceRate,
      byMonth: monthlyRates,
      topAttenders,
      absentees,
    };
  }

  async getRevenueReport(filters?: {
    organizationId?: number;
    startDate?: Date;
    endDate?: Date;
  }): Promise<RevenueReport> {
    let fees = await storage.getMembershipFees(filters?.organizationId);
    
    if (filters?.startDate) {
      fees = fees.filter(f => new Date(f.dueDate) >= filters.startDate!);
    }
    if (filters?.endDate) {
      fees = fees.filter(f => new Date(f.dueDate) <= filters.endDate!);
    }

    const paidFees = fees.filter(f => f.status === 'paid');
    const pendingFees = fees.filter(f => f.status === 'pending' || f.status === 'overdue');

    const totalRevenue = paidFees.reduce((sum, fee) => sum + (fee.amount || 0), 0);
    const outstandingPayments = pendingFees.reduce((sum, fee) => sum + (fee.amount || 0), 0);

    // Group by month
    const byMonth = paidFees.reduce((acc: any, fee) => {
      const month = new Date(fee.dueDate).toLocaleDateString('en-US', { year: 'numeric', month: 'short' });
      if (!acc[month]) acc[month] = 0;
      acc[month] += fee.amount || 0;
      return acc;
    }, {});

    // Group by type
    const byType = fees.reduce((acc: any, fee) => {
      const type = fee.feeType || 'other';
      if (!acc[type]) acc[type] = 0;
      if (fee.status === 'paid') {
        acc[type] += fee.amount || 0;
      }
      return acc;
    }, {});

    return {
      totalRevenue: totalRevenue / 100, // Convert from cents
      outstandingPayments: outstandingPayments / 100,
      paidCount: paidFees.length,
      pendingCount: pendingFees.length,
      byMonth: Object.entries(byMonth).map(([month, amount]) => ({ 
        month, 
        amount: (amount as number) / 100 
      })),
      byType: Object.entries(byType).map(([type, amount]) => ({ 
        type, 
        amount: (amount as number) / 100 
      })),
    };
  }

  async getTopScorersReport(filters?: {
    organizationId?: number;
    teamId?: number;
    limit?: number;
  }): Promise<TopScorersReport> {
    let allPlayers = await storage.getPlayers();
    
    if (filters?.teamId) {
      allPlayers = allPlayers.filter(p => p.teamId === filters.teamId);
    }

    const allTeams = await storage.getTeams();
    const teamMap = new Map(allTeams.map(t => [t.id, t.name]));

    // Get attendance stats for each player
    const playersWithStats = await Promise.all(
      allPlayers.map(async (player) => {
        const stats = await storage.getAttendanceStats(player.id);
        return {
          playerId: player.id,
          playerName: `${player.firstName} ${player.lastName}`,
          position: player.position || 'N/A',
          teamName: player.teamId ? (teamMap.get(player.teamId) || 'N/A') : 'N/A',
          stats: {
            matches: stats?.totalSessions || 0,
            attendanceRate: stats?.attendanceRate || 0,
          },
        };
      })
    );

    const topPlayers = playersWithStats
      .filter(p => p.stats.matches > 0)
      .sort((a, b) => {
        // Sort by attendance rate, then by matches played
        if (b.stats.attendanceRate !== a.stats.attendanceRate) {
          return b.stats.attendanceRate - a.stats.attendanceRate;
        }
        return b.stats.matches - a.stats.matches;
      })
      .slice(0, filters?.limit || 10);

    return {
      topPlayers,
    };
  }

  async exportReportToCSV(reportType: string, data: any): Promise<string> {
    let csv = '';

    switch (reportType) {
      case 'fixtures':
        csv = 'Month,Count\n';
        data.byMonth.forEach((row: any) => {
          csv += `${row.month},${row.count}\n`;
        });
        break;

      case 'attendance':
        csv = 'Player,Attendance Rate,Sessions Attended\n';
        data.topAttenders.forEach((row: any) => {
          csv += `${row.playerName},${row.attendanceRate}%,${row.sessionsAttended}\n`;
        });
        break;

      case 'revenue':
        csv = 'Month,Amount\n';
        data.byMonth.forEach((row: any) => {
          csv += `${row.month},$${row.amount.toFixed(2)}\n`;
        });
        break;

      case 'top-scorers':
        csv = 'Player,Position,Team,Matches,Attendance Rate\n';
        data.topPlayers.forEach((row: any) => {
          csv += `${row.playerName},${row.position},${row.teamName},${row.stats.matches},${row.stats.attendanceRate}%\n`;
        });
        break;
    }

    return csv;
  }
}

export const reportsService = new ReportsService();
