import {
  users,
  teams,
  players,
  coaches,
  teamCoaches,
  organizations,
  userClubRoles,
  passwordResetTokens,
  subscriptionPlans,
  modules,
  organizationModulePermissions,
  membershipFees,
  paymentActivities,
  paymentNotifications,
  automaticPaymentSchedules,
  memberStatusHistory,
  brandingSettings,

  attendanceSessions,
  attendanceRecords,
  attendanceStats,
  attendanceNotifications,
  tournaments,
  matches,
  leagues,
  leagueTeams,
  venues,
  referees,
  fixtures,
  matchEvents,
  matchStatistics,
  playerTransfers,
  cmsPages,
  cmsContentBlocks,
  cmsFeatures,
  cmsStats,
  cmsTestimonials,
  notifications,
  auditLogs,
  userPreferences,
  type User,
  type UpsertUser,
  type InsertTeam,
  type Team,
  type InsertPlayer,
  type Player,
  type InsertCoach,
  type Coach,
  type InsertTeamCoach,
  type TeamCoach,
  type Organization,
  type MembershipFee,
  type InsertMembershipFee,
  type PaymentActivity,
  type InsertPaymentActivity,
  type PaymentNotification,
  type InsertPaymentNotification,
  type AutomaticPaymentSchedule,
  type InsertAutomaticPaymentSchedule,
  type MemberStatusHistory,
  type InsertMemberStatusHistory,

  type AttendanceSession,
  type InsertAttendanceSession,
  type AttendanceRecord,
  type InsertAttendanceRecord,
  type AttendanceStats,
  type AttendanceNotification,
  type Tournament,
  type InsertTournament,
  type Match,
  type InsertMatch,
  type InsertAttendanceNotification,
  type League,
  type InsertLeague,
  type LeagueTeam,
  type InsertLeagueTeam,
  type Venue,
  type InsertVenue,
  type Referee,
  type InsertReferee,
  type Fixture,
  type InsertFixture,
  type MatchEvent,
  type InsertMatchEvent,
  type MatchStatistics,
  type InsertMatchStatistics,
  type PlayerTransfer,
  type InsertPlayerTransfer,
  type CmsPage,
  type InsertCmsPage,
  type CmsContentBlock,
  type InsertCmsContentBlock,
  type CmsFeature,
  type InsertCmsFeature,
  type CmsStat,
  type InsertCmsStat,
  type CmsTestimonial,
  type InsertCmsTestimonial,
  type SubscriptionPlan,
  type InsertSubscriptionPlan,
  type Notification,
  type InsertNotification,
  type AuditLog,
  type InsertAuditLog,
  type Module,
  type InsertModule,
} from "@shared/schema";
import { db } from "./db";
import { eq, and, gte, lte, desc, asc, sql } from "drizzle-orm";
import bcrypt from "bcrypt";

// Interface for storage operations
export interface IStorage {
  // Theme management
  getThemes(): Promise<any[]>;
  createTheme(theme: any): Promise<any>;
  updateTheme(id: number, theme: any): Promise<any>;
  deleteTheme(id: number): Promise<void>;
  
  // Payment gateway management
  getPaymentGateways(): Promise<any[]>;
  getPaymentGateway(id: number): Promise<any>;
  createPaymentGateway(gateway: any): Promise<any>;
  updatePaymentGateway(id: number, gateway: any): Promise<any>;
  
  // Data export/import
  getBiometricData(): Promise<any[]>;
  bulkImportPlayers(data: any[], mode: string): Promise<any>;
  bulkImportTeams(data: any[], mode: string): Promise<any>;
  bulkImportCoaches(data: any[], mode: string): Promise<any>;
  // User operations
  getUser(id: string): Promise<User | undefined>;
  upsertUser(user: UpsertUser): Promise<User>;
  getUserByUsername(username: string): Promise<User | undefined>;
  getUserByEmail(email: string): Promise<User | undefined>;
  getUserByVerificationToken(token: string): Promise<User | undefined>;
  createUser(insertUser: any): Promise<User>;
  createLocalUser(userData: { firstName: string; lastName: string; email: string; password: string; sport: string }): Promise<User>;
  updateUser(id: string, userData: Partial<User>): Promise<User>;
  getAllUsers(): Promise<User[]>;

  // Team operations
  createTeam(team: InsertTeam): Promise<Team>;
  getTeams(): Promise<Team[]>;
  getTeam(id: number): Promise<Team | undefined>;
  updateTeam(id: number, team: Partial<InsertTeam>): Promise<Team>;
  deleteTeam(id: number): Promise<void>;

  // Player operations
  createPlayer(player: InsertPlayer): Promise<Player>;
  getPlayers(): Promise<(Player & { teamName?: string })[]>;
  getPlayer(id: number): Promise<Player | undefined>;
  getPlayersByTeam(teamId: number): Promise<Player[]>;
  updatePlayer(id: number, player: Partial<InsertPlayer>): Promise<Player>;
  deletePlayer(id: number): Promise<void>;

  // Coach operations
  createCoach(coach: InsertCoach): Promise<Coach>;
  getCoaches(): Promise<Coach[]>;
  getCoach(id: number): Promise<Coach | undefined>;
  updateCoach(id: number, coach: Partial<InsertCoach>): Promise<Coach>;
  deleteCoach(id: number): Promise<void>;

  // Team-Coach operations
  assignCoachToTeam(assignment: InsertTeamCoach): Promise<TeamCoach>;
  getTeamCoaches(teamId: number): Promise<TeamCoach[]>;
  removeCoachFromTeam(teamId: number, coachId: number): Promise<void>;

  // Membership fee operations
  createMembershipFee(fee: InsertMembershipFee): Promise<MembershipFee>;
  getMembershipFees(organizationId?: number): Promise<MembershipFee[]>;
  getMembershipFee(id: number): Promise<MembershipFee | undefined>;
  updateMembershipFee(id: number, fee: Partial<InsertMembershipFee>): Promise<MembershipFee>;
  getOverdueFees(): Promise<MembershipFee[]>;
  getUserMembershipFees(userId: string): Promise<MembershipFee[]>;

  // Payment activity operations
  createPaymentActivity(activity: InsertPaymentActivity): Promise<PaymentActivity>;
  getPaymentActivities(membershipFeeId?: number): Promise<PaymentActivity[]>;
  updatePaymentActivity(id: number, activity: Partial<InsertPaymentActivity>): Promise<PaymentActivity>;
  getPaymentHistory(userId: string): Promise<PaymentActivity[]>;

  // Payment notification operations
  createPaymentNotification(notification: InsertPaymentNotification): Promise<PaymentNotification>;
  getPendingNotifications(): Promise<PaymentNotification[]>;
  updateNotificationStatus(id: number, status: string): Promise<void>;
  schedulePaymentReminder(membershipFeeId: number, scheduledFor: Date): Promise<PaymentNotification>;

  // Automatic payment schedule operations
  createAutomaticPaymentSchedule(schedule: InsertAutomaticPaymentSchedule): Promise<AutomaticPaymentSchedule>;
  getActivePaymentSchedules(): Promise<AutomaticPaymentSchedule[]>;
  updatePaymentSchedule(id: number, schedule: Partial<InsertAutomaticPaymentSchedule>): Promise<AutomaticPaymentSchedule>;
  suspendPaymentSchedule(id: number): Promise<void>;

  // Member status operations
  createMemberStatusChange(history: InsertMemberStatusHistory): Promise<MemberStatusHistory>;
  getMemberStatusHistory(userId: string): Promise<MemberStatusHistory[]>;
  suspendMemberForNonPayment(userId: string, organizationId: number, membershipFeeId: number): Promise<void>;
  reactivateMember(userId: string, organizationId: number): Promise<void>;



  // Attendance operations
  createAttendanceSession(session: InsertAttendanceSession): Promise<AttendanceSession>;
  getAttendanceSessions(organizationId?: number, teamId?: number): Promise<AttendanceSession[]>;
  getAttendanceSession(id: number): Promise<AttendanceSession | undefined>;
  updateAttendanceSession(id: number, session: Partial<InsertAttendanceSession>): Promise<AttendanceSession>;
  deleteAttendanceSession(id: number): Promise<void>;
  
  createAttendanceRecord(record: InsertAttendanceRecord): Promise<AttendanceRecord>;
  getAttendanceRecords(sessionId: number): Promise<AttendanceRecord[]>;
  getPlayerAttendanceRecords(playerId: number): Promise<AttendanceRecord[]>;
  updateAttendanceRecord(id: number, record: Partial<InsertAttendanceRecord>): Promise<AttendanceRecord>;
  bulkCreateAttendanceRecords(records: InsertAttendanceRecord[]): Promise<AttendanceRecord[]>;
  
  getAttendanceStats(playerId: number): Promise<AttendanceStats | undefined>;
  updateAttendanceStats(playerId: number): Promise<AttendanceStats>;
  
  createAttendanceNotification(notification: InsertAttendanceNotification): Promise<AttendanceNotification>;
  getPendingAttendanceNotifications(): Promise<AttendanceNotification[]>;
  updateAttendanceNotificationStatus(id: number, status: string): Promise<void>;

  // Tournament operations
  getTournaments(): Promise<Tournament[]>;
  getTournament(id: number): Promise<Tournament | undefined>;
  createTournament(tournament: InsertTournament): Promise<Tournament>;
  updateTournament(id: number, tournament: Partial<InsertTournament>): Promise<Tournament>;
  deleteTournament(id: number): Promise<void>;

  // Match operations
  getMatches(): Promise<Match[]>;
  getMatchesByTournament(tournamentId: number): Promise<Match[]>;
  createMatch(match: InsertMatch): Promise<Match>;
  updateMatch(id: number, match: Partial<InsertMatch>): Promise<Match>;
  deleteMatch(id: number): Promise<void>;

  // CMS operations
  // Pages
  getCmsPages(): Promise<CmsPage[]>;
  getCmsPage(id: number): Promise<CmsPage | undefined>;
  getCmsPageBySlug(slug: string): Promise<CmsPage | undefined>;
  createCmsPage(page: InsertCmsPage): Promise<CmsPage>;
  updateCmsPage(id: number, page: Partial<InsertCmsPage>): Promise<CmsPage>;
  deleteCmsPage(id: number): Promise<void>;

  // Content Blocks
  getCmsContentBlocks(pageId: number): Promise<CmsContentBlock[]>;
  getCmsContentBlock(id: number): Promise<CmsContentBlock | undefined>;
  createCmsContentBlock(block: InsertCmsContentBlock): Promise<CmsContentBlock>;
  updateCmsContentBlock(id: number, block: Partial<InsertCmsContentBlock>): Promise<CmsContentBlock>;
  deleteCmsContentBlock(id: number): Promise<void>;

  // Features
  getCmsFeatures(): Promise<CmsFeature[]>;
  getCmsFeature(id: number): Promise<CmsFeature | undefined>;
  createCmsFeature(feature: InsertCmsFeature): Promise<CmsFeature>;
  updateCmsFeature(id: number, feature: Partial<InsertCmsFeature>): Promise<CmsFeature>;
  deleteCmsFeature(id: number): Promise<void>;

  // Stats
  getCmsStats(): Promise<CmsStat[]>;
  getCmsStat(id: number): Promise<CmsStat | undefined>;
  createCmsStat(stat: InsertCmsStat): Promise<CmsStat>;
  updateCmsStat(id: number, stat: Partial<InsertCmsStat>): Promise<CmsStat>;
  deleteCmsStat(id: number): Promise<void>;

  // Testimonials
  getCmsTestimonials(): Promise<CmsTestimonial[]>;
  getCmsTestimonial(id: number): Promise<CmsTestimonial | undefined>;
  createCmsTestimonial(testimonial: InsertCmsTestimonial): Promise<CmsTestimonial>;
  updateCmsTestimonial(id: number, testimonial: Partial<InsertCmsTestimonial>): Promise<CmsTestimonial>;
  deleteCmsTestimonial(id: number): Promise<void>;

  // Super Admin operations
  getAllOrganizations(): Promise<Organization[]>;
  getAllModules(): Promise<any[]>;
  getAllSubscriptionPlans(): Promise<any[]>;
  getOrganizationModulePermissions(orgId: number): Promise<any[]>;
  updateOrganizationSubscription(orgId: number, data: any): Promise<void>;
  updateOrganizationModulePermission(orgId: number, moduleId: number, enabled: boolean): Promise<void>;
  createModule(moduleData: InsertModule): Promise<Module>;
  getModules(): Promise<Module[]>;
  getModule(id: number): Promise<Module | undefined>;
  updateModule(id: number, moduleData: Partial<InsertModule>): Promise<Module>;
  createOrganization(organization: any): Promise<Organization>;

  // Subscription Plan operations
  getSubscriptionPlans(): Promise<SubscriptionPlan[]>;
  getActiveSubscriptionPlans(): Promise<SubscriptionPlan[]>;
  getSubscriptionPlan(id: number): Promise<SubscriptionPlan | undefined>;
  createSubscriptionPlan(plan: InsertSubscriptionPlan): Promise<SubscriptionPlan>;
  updateSubscriptionPlan(id: number, plan: Partial<InsertSubscriptionPlan>): Promise<SubscriptionPlan>;
  deleteSubscriptionPlan(id: number): Promise<void>;

  // Extended subscription operations
  getSubscriptionStats(): Promise<any>;
  getSubscriptions(): Promise<any[]>;
  getOverdueSubscriptions(): Promise<any[]>;
  getExpiringSubscriptions(days: number): Promise<any[]>;
  cancelSubscription(id: number, reason?: string): Promise<any>;
  suspendSubscription(id: number, reason?: string): Promise<any>;
  reactivateSubscription(id: number): Promise<any>;
  getPayments(): Promise<any[]>;

  // Branding settings operations
  getBrandingSettings(organizationId: number): Promise<any | undefined>;
  upsertBrandingSettings(organizationId: number, settings: any): Promise<any>;

  // Notification operations
  createNotification(notification: InsertNotification): Promise<Notification>;
  getNotifications(userId: string, limit?: number): Promise<Notification[]>;
  getUnreadNotifications(userId: string): Promise<Notification[]>;
  markNotificationAsRead(id: number): Promise<void>;
  markAllNotificationsAsRead(userId: string): Promise<void>;
  deleteNotification(id: number): Promise<void>;

  // Audit log operations
  createAuditLog(log: InsertAuditLog): Promise<AuditLog>;
  getAuditLogs(filters?: {
    userId?: string;
    organizationId?: number;
    resource?: string;
    action?: string;
    startDate?: Date;
    endDate?: Date;
    limit?: number;
  }): Promise<AuditLog[]>;
  getAuditLog(id: number): Promise<AuditLog | undefined>;

  // User preferences operations
  getUserPreferences(userId: string): Promise<any>;
  updateUserPreferences(userId: string, preferences: any): Promise<any>;
  verifyPassword(userId: string, password: string): Promise<boolean>;

  // League operations
  createLeague(league: InsertLeague): Promise<League>;
  getLeagues(organizationId?: number): Promise<League[]>;
  getLeague(id: number): Promise<League | undefined>;
  updateLeague(id: number, league: Partial<InsertLeague>): Promise<League>;
  deleteLeague(id: number): Promise<void>;
  getLeagueStandings(leagueId: number): Promise<any[]>;

  // League Team operations
  addTeamToLeague(leagueTeam: InsertLeagueTeam): Promise<LeagueTeam>;
  removeTeamFromLeague(leagueId: number, teamId: number): Promise<void>;
  updateLeagueTeamStats(leagueId: number, teamId: number, stats: Partial<InsertLeagueTeam>): Promise<LeagueTeam>;

  // Venue operations
  createVenue(venue: InsertVenue): Promise<Venue>;
  getVenues(organizationId?: number): Promise<Venue[]>;
  getVenue(id: number): Promise<Venue | undefined>;
  updateVenue(id: number, venue: Partial<InsertVenue>): Promise<Venue>;
  deleteVenue(id: number): Promise<void>;

  // Referee operations
  createReferee(referee: InsertReferee): Promise<Referee>;
  getReferees(organizationId?: number): Promise<Referee[]>;
  getReferee(id: number): Promise<Referee | undefined>;
  updateReferee(id: number, referee: Partial<InsertReferee>): Promise<Referee>;
  deleteReferee(id: number): Promise<void>;

  // Fixture operations
  createFixture(fixture: InsertFixture): Promise<Fixture>;
  getFixtures(filters?: { leagueId?: number; tournamentId?: number; status?: string }): Promise<Fixture[]>;
  getFixture(id: number): Promise<Fixture | undefined>;
  updateFixture(id: number, fixture: Partial<InsertFixture>): Promise<Fixture>;
  deleteFixture(id: number): Promise<void>;
  getUpcomingFixtures(limit?: number): Promise<Fixture[]>;
  getLiveFixtures(): Promise<Fixture[]>;

  // Match Event operations
  createMatchEvent(event: InsertMatchEvent): Promise<MatchEvent>;
  getMatchEvents(fixtureId: number): Promise<MatchEvent[]>;
  deleteMatchEvent(id: number): Promise<void>;

  // Match Statistics operations
  createMatchStatistics(stats: InsertMatchStatistics): Promise<MatchStatistics>;
  getMatchStatistics(fixtureId: number): Promise<MatchStatistics[]>;
  updateMatchStatistics(id: number, stats: Partial<InsertMatchStatistics>): Promise<MatchStatistics>;

  // Player Transfer operations
  createPlayerTransfer(transfer: InsertPlayerTransfer): Promise<PlayerTransfer>;
  getPlayerTransfers(filters?: { status?: string; playerId?: number }): Promise<PlayerTransfer[]>;
  getPlayerTransfer(id: number): Promise<PlayerTransfer | undefined>;
  updatePlayerTransfer(id: number, transfer: Partial<InsertPlayerTransfer>): Promise<PlayerTransfer>;
  approvePlayerTransfer(id: number, approvedBy: string): Promise<PlayerTransfer>;
  rejectPlayerTransfer(id: number, rejectionReason: string): Promise<PlayerTransfer>;
  completePlayerTransfer(id: number): Promise<PlayerTransfer>;
}

export class DatabaseStorage implements IStorage {
  // User operations
  async getUser(id: string): Promise<User | undefined> {
    const [user] = await db.select().from(users).where(eq(users.id, id));
    return user;
  }

  async upsertUser(userData: UpsertUser): Promise<User> {
    const [user] = await db
      .insert(users)
      .values(userData)
      .onConflictDoUpdate({
        target: users.id,
        set: {
          ...userData,
          updatedAt: new Date(),
        },
      })
      .returning();
    return user;
  }

  async getUserByUsername(username: string): Promise<User | undefined> {
    const [user] = await db.select().from(users).where(eq(users.username, username));
    return user;
  }

  async getUserByEmail(email: string): Promise<User | undefined> {
    const [user] = await db.select().from(users).where(eq(users.email, email));
    return user;
  }

  async getUserByVerificationToken(token: string): Promise<User | undefined> {
    const [user] = await db.select().from(users).where(eq(users.verificationToken, token));
    return user;
  }

  async createUser(insertUser: any): Promise<User> {
    const [user] = await db
      .insert(users)
      .values(insertUser)
      .returning();
    return user;
  }

  async createLocalUser(userData: { firstName: string; lastName: string; email: string; password: string; sport: string }): Promise<User> {
    const crypto = await import('crypto');
    const { generateUsername } = await import('./password');
    
    const username = generateUsername(userData.firstName, userData.lastName);
    const insertData = {
      id: crypto.randomUUID(),
      firstName: userData.firstName,
      lastName: userData.lastName,
      email: userData.email,
      username,
      password: userData.password,
      sport: userData.sport,
      authType: "local" as const,
      isActive: true,
      status: "active" as const,
      role: "user" as const,
      emailVerified: false,
    };

    const [user] = await db
      .insert(users)
      .values(insertData)
      .returning();
    return user;
  }

  async updateUser(id: string, userData: Partial<User>): Promise<User> {
    const [user] = await db
      .update(users)
      .set({ ...userData, updatedAt: new Date() })
      .where(eq(users.id, id))
      .returning();
    return user;
  }

  async getAllUsers(): Promise<User[]> {
    return await db.select().from(users);
  }

  // Team operations
  async createTeam(team: InsertTeam): Promise<Team> {
    const [newTeam] = await db
      .insert(teams)
      .values(team)
      .returning();
    return newTeam;
  }

  async getTeams(): Promise<Team[]> {
    return await db.select().from(teams);
  }

  async getTeam(id: number): Promise<Team | undefined> {
    const [team] = await db.select().from(teams).where(eq(teams.id, id));
    return team;
  }

  async updateTeam(id: number, team: Partial<InsertTeam>): Promise<Team> {
    const [updatedTeam] = await db
      .update(teams)
      .set({ ...team, updatedAt: new Date() })
      .where(eq(teams.id, id))
      .returning();
    return updatedTeam;
  }

  async deleteTeam(id: number): Promise<void> {
    await db.delete(teams).where(eq(teams.id, id));
  }

  // Player operations
  async createPlayer(player: InsertPlayer): Promise<Player> {
    const [newPlayer] = await db
      .insert(players)
      .values(player)
      .returning();
    return newPlayer;
  }

  async getPlayers(): Promise<(Player & { teamName?: string })[]> {
    const result = await db
      .select()
      .from(players)
      .leftJoin(teams, eq(players.teamId, teams.id))
      .orderBy(desc(players.createdAt));
    
    return result.map(row => ({
      ...row.players,
      teamName: row.teams?.name || undefined
    }));
  }

  async getPlayer(id: number): Promise<Player | undefined> {
    const [player] = await db.select().from(players).where(eq(players.id, id));
    return player;
  }

  async getPlayersByTeam(teamId: number): Promise<Player[]> {
    return await db.select().from(players).where(eq(players.teamId, teamId));
  }

  async updatePlayer(id: number, player: Partial<InsertPlayer>): Promise<Player> {
    const [updatedPlayer] = await db
      .update(players)
      .set({ ...player, updatedAt: new Date() })
      .where(eq(players.id, id))
      .returning();
    return updatedPlayer;
  }

  async deletePlayer(id: number): Promise<void> {
    await db.delete(players).where(eq(players.id, id));
  }

  // Coach operations
  async createCoach(coach: InsertCoach): Promise<Coach> {
    const [newCoach] = await db
      .insert(coaches)
      .values(coach)
      .returning();
    return newCoach;
  }

  async getCoaches(): Promise<Coach[]> {
    return await db.select().from(coaches);
  }

  async getCoach(id: number): Promise<Coach | undefined> {
    const [coach] = await db.select().from(coaches).where(eq(coaches.id, id));
    return coach;
  }

  async updateCoach(id: number, coach: Partial<InsertCoach>): Promise<Coach> {
    const [updatedCoach] = await db
      .update(coaches)
      .set({ ...coach, updatedAt: new Date() })
      .where(eq(coaches.id, id))
      .returning();
    return updatedCoach;
  }

  async deleteCoach(id: number): Promise<void> {
    await db.delete(coaches).where(eq(coaches.id, id));
  }

  // Team-Coach operations
  async assignCoachToTeam(assignment: InsertTeamCoach): Promise<TeamCoach> {
    const [teamCoach] = await db
      .insert(teamCoaches)
      .values(assignment)
      .returning();
    return teamCoach;
  }

  async getTeamCoaches(teamId: number): Promise<TeamCoach[]> {
    return await db.select().from(teamCoaches).where(eq(teamCoaches.teamId, teamId));
  }

  async getCoachTeams(coachId: number): Promise<any[]> {
    const assignments = await db
      .select({
        id: teamCoaches.id,
        teamId: teamCoaches.teamId,
        coachId: teamCoaches.coachId,
        role: teamCoaches.role,
        startDate: teamCoaches.startDate,
        endDate: teamCoaches.endDate,
        teamName: teams.name,
        teamSport: teams.sport,
        teamLogoUrl: teams.logoUrl,
      })
      .from(teamCoaches)
      .innerJoin(teams, eq(teamCoaches.teamId, teams.id))
      .where(eq(teamCoaches.coachId, coachId));
    
    return assignments;
  }

  async removeCoachFromTeam(teamId: number, coachId: number): Promise<void> {
    await db.delete(teamCoaches).where(
      and(eq(teamCoaches.teamId, teamId), eq(teamCoaches.coachId, coachId))
    );
  }

  // Membership fee operations
  async createMembershipFee(fee: InsertMembershipFee): Promise<MembershipFee> {
    const [newFee] = await db
      .insert(membershipFees)
      .values(fee)
      .returning();
    return newFee;
  }

  async getMembershipFees(organizationId?: number): Promise<MembershipFee[]> {
    if (organizationId) {
      return await db.select().from(membershipFees)
        .where(eq(membershipFees.organizationId, organizationId))
        .orderBy(desc(membershipFees.dueDate));
    }
    return await db.select().from(membershipFees).orderBy(desc(membershipFees.dueDate));
  }

  async getMembershipFee(id: number): Promise<MembershipFee | undefined> {
    const [fee] = await db.select().from(membershipFees).where(eq(membershipFees.id, id));
    return fee;
  }

  async updateMembershipFee(id: number, fee: Partial<InsertMembershipFee>): Promise<MembershipFee> {
    const [updatedFee] = await db
      .update(membershipFees)
      .set({ ...fee, updatedAt: new Date() })
      .where(eq(membershipFees.id, id))
      .returning();
    return updatedFee;
  }

  async getOverdueFees(): Promise<MembershipFee[]> {
    return await db.select().from(membershipFees)
      .where(
        and(
          eq(membershipFees.status, 'pending'),
          lte(membershipFees.dueDate, new Date())
        )
      )
      .orderBy(asc(membershipFees.dueDate));
  }

  async getUserMembershipFees(userId: string): Promise<MembershipFee[]> {
    return await db.select().from(membershipFees)
      .where(eq(membershipFees.userId, userId))
      .orderBy(desc(membershipFees.dueDate));
  }

  // Payment activity operations
  async createPaymentActivity(activity: InsertPaymentActivity): Promise<PaymentActivity> {
    const [newActivity] = await db
      .insert(paymentActivities)
      .values(activity)
      .returning();
    return newActivity;
  }

  async getPaymentActivities(membershipFeeId?: number): Promise<PaymentActivity[]> {
    if (membershipFeeId) {
      return await db.select().from(paymentActivities)
        .where(eq(paymentActivities.membershipFeeId, membershipFeeId))
        .orderBy(desc(paymentActivities.createdAt));
    }
    return await db.select().from(paymentActivities).orderBy(desc(paymentActivities.createdAt));
  }

  async updatePaymentActivity(id: number, activity: Partial<InsertPaymentActivity>): Promise<PaymentActivity> {
    const [updatedActivity] = await db
      .update(paymentActivities)
      .set({ ...activity, updatedAt: new Date() })
      .where(eq(paymentActivities.id, id))
      .returning();
    return updatedActivity;
  }

  async getPaymentHistory(userId: string): Promise<PaymentActivity[]> {
    return await db.select().from(paymentActivities)
      .where(eq(paymentActivities.userId, userId))
      .orderBy(desc(paymentActivities.createdAt));
  }

  // Payment notification operations
  async createPaymentNotification(notification: InsertPaymentNotification): Promise<PaymentNotification> {
    const [newNotification] = await db
      .insert(paymentNotifications)
      .values(notification)
      .returning();
    return newNotification;
  }

  async getPendingNotifications(): Promise<PaymentNotification[]> {
    return await db.select().from(paymentNotifications)
      .where(
        and(
          eq(paymentNotifications.status, 'pending'),
          lte(paymentNotifications.scheduledFor, new Date())
        )
      )
      .orderBy(asc(paymentNotifications.scheduledFor));
  }

  async updateNotificationStatus(id: number, status: string): Promise<void> {
    await db
      .update(paymentNotifications)
      .set({ 
        status,
        sentAt: status === 'sent' ? new Date() : undefined,
        updatedAt: new Date()
      })
      .where(eq(paymentNotifications.id, id));
  }

  async schedulePaymentReminder(membershipFeeId: number, scheduledFor: Date): Promise<PaymentNotification> {
    const fee = await this.getMembershipFee(membershipFeeId);
    if (!fee) throw new Error('Membership fee not found');

    const notification: InsertPaymentNotification = {
      membershipFeeId,
      userId: fee.userId!,
      organizationId: fee.organizationId,
      notificationType: 'reminder',
      method: 'email',
      scheduledFor,
      subject: 'Payment Reminder',
      message: `Your ${fee.feeType} payment of $${(fee.amount / 100).toFixed(2)} is due on ${fee.dueDate.toDateString()}`,
      emailAddress: '', // Will be populated from user data
    };

    return await this.createPaymentNotification(notification);
  }

  // Automatic payment schedule operations
  async createAutomaticPaymentSchedule(schedule: InsertAutomaticPaymentSchedule): Promise<AutomaticPaymentSchedule> {
    const [newSchedule] = await db
      .insert(automaticPaymentSchedules)
      .values(schedule)
      .returning();
    return newSchedule;
  }

  async getActivePaymentSchedules(): Promise<AutomaticPaymentSchedule[]> {
    return await db.select().from(automaticPaymentSchedules)
      .where(
        and(
          eq(automaticPaymentSchedules.isActive, true),
          lte(automaticPaymentSchedules.nextPaymentDate, new Date())
        )
      )
      .orderBy(asc(automaticPaymentSchedules.nextPaymentDate));
  }

  async updatePaymentSchedule(id: number, schedule: Partial<InsertAutomaticPaymentSchedule>): Promise<AutomaticPaymentSchedule> {
    const [updatedSchedule] = await db
      .update(automaticPaymentSchedules)
      .set({ ...schedule, updatedAt: new Date() })
      .where(eq(automaticPaymentSchedules.id, id))
      .returning();
    return updatedSchedule;
  }

  async suspendPaymentSchedule(id: number): Promise<void> {
    await db
      .update(automaticPaymentSchedules)
      .set({ 
        isActive: false,
        suspendedAt: new Date(),
        updatedAt: new Date()
      })
      .where(eq(automaticPaymentSchedules.id, id));
  }

  // Member status operations
  async createMemberStatusChange(history: InsertMemberStatusHistory): Promise<MemberStatusHistory> {
    const [newHistory] = await db
      .insert(memberStatusHistory)
      .values(history)
      .returning();
    return newHistory;
  }

  async getMemberStatusHistory(userId: string): Promise<MemberStatusHistory[]> {
    return await db.select().from(memberStatusHistory)
      .where(eq(memberStatusHistory.userId, userId))
      .orderBy(desc(memberStatusHistory.effectiveDate));
  }

  async suspendMemberForNonPayment(userId: string, organizationId: number, membershipFeeId: number): Promise<void> {
    // Update user status to suspended
    await this.updateUser(userId, { status: 'suspended' });

    // Create status history record
    await this.createMemberStatusChange({
      userId,
      organizationId,
      previousStatus: 'active',
      newStatus: 'suspended',
      reason: 'payment_overdue',
      membershipFeeId,
      automaticChange: true,
      notes: 'Member suspended automatically due to overdue payment',
    });
  }

  async reactivateMember(userId: string, organizationId: number): Promise<void> {
    // Update user status to active
    await this.updateUser(userId, { status: 'active' });

    // Create status history record
    await this.createMemberStatusChange({
      userId,
      organizationId,
      previousStatus: 'suspended',
      newStatus: 'active',
      reason: 'payment_received',
      automaticChange: true,
      notes: 'Member reactivated automatically after payment received',
    });
  }



  // Attendance operations
  async createAttendanceSession(session: InsertAttendanceSession): Promise<AttendanceSession> {
    const [created] = await db
      .insert(attendanceSessions)
      .values(session)
      .returning();
    return created;
  }

  async getAttendanceSessions(organizationId?: number, teamId?: number): Promise<AttendanceSession[]> {
    const baseQuery = db.select().from(attendanceSessions);
    
    if (organizationId && teamId) {
      return await baseQuery
        .where(and(eq(attendanceSessions.organizationId, organizationId), eq(attendanceSessions.teamId, teamId)))
        .orderBy(attendanceSessions.date, attendanceSessions.startTime);
    }
    
    if (organizationId) {
      return await baseQuery
        .where(eq(attendanceSessions.organizationId, organizationId))
        .orderBy(attendanceSessions.date, attendanceSessions.startTime);
    }
    
    if (teamId) {
      return await baseQuery
        .where(eq(attendanceSessions.teamId, teamId))
        .orderBy(attendanceSessions.date, attendanceSessions.startTime);
    }
    
    return await baseQuery.orderBy(attendanceSessions.date, attendanceSessions.startTime);
  }

  async getAttendanceSession(id: number): Promise<AttendanceSession | undefined> {
    const [session] = await db
      .select()
      .from(attendanceSessions)
      .where(eq(attendanceSessions.id, id));
    return session;
  }

  async updateAttendanceSession(id: number, session: Partial<InsertAttendanceSession>): Promise<AttendanceSession> {
    const [updated] = await db
      .update(attendanceSessions)
      .set({
        ...session,
        updatedAt: new Date(),
      })
      .where(eq(attendanceSessions.id, id))
      .returning();
    return updated;
  }

  async deleteAttendanceSession(id: number): Promise<void> {
    await db.delete(attendanceSessions).where(eq(attendanceSessions.id, id));
  }

  async createAttendanceRecord(record: InsertAttendanceRecord): Promise<AttendanceRecord> {
    const [created] = await db
      .insert(attendanceRecords)
      .values(record)
      .returning();
    return created;
  }

  async getAttendanceRecords(sessionId: number): Promise<AttendanceRecord[]> {
    return await db
      .select()
      .from(attendanceRecords)
      .where(eq(attendanceRecords.sessionId, sessionId))
      .orderBy(attendanceRecords.markedAt);
  }

  async getPlayerAttendanceRecords(playerId: number): Promise<AttendanceRecord[]> {
    return await db
      .select()
      .from(attendanceRecords)
      .where(eq(attendanceRecords.playerId, playerId))
      .orderBy(attendanceRecords.markedAt);
  }

  async updateAttendanceRecord(id: number, record: Partial<InsertAttendanceRecord>): Promise<AttendanceRecord> {
    const [updated] = await db
      .update(attendanceRecords)
      .set({
        ...record,
        updatedAt: new Date(),
      })
      .where(eq(attendanceRecords.id, id))
      .returning();
    return updated;
  }

  async bulkCreateAttendanceRecords(records: InsertAttendanceRecord[]): Promise<AttendanceRecord[]> {
    return await db
      .insert(attendanceRecords)
      .values(records)
      .returning();
  }

  async getAttendanceStats(playerId: number): Promise<AttendanceStats | undefined> {
    const [stats] = await db
      .select()
      .from(attendanceStats)
      .where(eq(attendanceStats.playerId, playerId));
    return stats;
  }

  async updateAttendanceStats(playerId: number): Promise<AttendanceStats> {
    // Calculate stats from attendance records
    const records = await this.getPlayerAttendanceRecords(playerId);
    const totalSessions = records.length;
    const attendedSessions = records.filter(r => r.status === 'present').length;
    const absences = records.filter(r => r.status === 'absent').length;
    const lateCount = records.filter(r => r.status === 'late').length;
    const attendanceRate = totalSessions > 0 ? Math.round((attendedSessions / totalSessions) * 100) : 0;

    const [updated] = await db
      .insert(attendanceStats)
      .values({
        playerId,
        totalSessions,
        attendedSessions,
        absences,
        lateCount,
        attendanceRate,
        lastUpdated: new Date(),
      })
      .onConflictDoUpdate({
        target: attendanceStats.playerId,
        set: {
          totalSessions,
          attendedSessions,
          absences,
          lateCount,
          attendanceRate,
          lastUpdated: new Date(),
        },
      })
      .returning();
    return updated;
  }

  async createAttendanceNotification(notification: InsertAttendanceNotification): Promise<AttendanceNotification> {
    const [created] = await db
      .insert(attendanceNotifications)
      .values(notification)
      .returning();
    return created;
  }

  async getPendingAttendanceNotifications(): Promise<AttendanceNotification[]> {
    return await db
      .select()
      .from(attendanceNotifications)
      .where(eq(attendanceNotifications.status, 'pending'))
      .orderBy(attendanceNotifications.createdAt);
  }

  async updateAttendanceNotificationStatus(id: number, status: string): Promise<void> {
    await db
      .update(attendanceNotifications)
      .set({ 
        status, 
        sentAt: status === 'sent' ? new Date() : undefined 
      })
      .where(eq(attendanceNotifications.id, id));
  }

  // Tournament operations
  async getTournaments(): Promise<Tournament[]> {
    return await db.select().from(tournaments).orderBy(desc(tournaments.createdAt));
  }

  async getTournament(id: number): Promise<Tournament | undefined> {
    const [tournament] = await db.select().from(tournaments).where(eq(tournaments.id, id));
    return tournament;
  }

  async createTournament(tournament: InsertTournament): Promise<Tournament> {
    const [newTournament] = await db.insert(tournaments).values(tournament).returning();
    return newTournament;
  }

  async updateTournament(id: number, tournament: Partial<InsertTournament>): Promise<Tournament> {
    const [updatedTournament] = await db
      .update(tournaments)
      .set({ ...tournament, updatedAt: new Date() })
      .where(eq(tournaments.id, id))
      .returning();
    return updatedTournament;
  }

  async deleteTournament(id: number): Promise<void> {
    await db.delete(tournaments).where(eq(tournaments.id, id));
  }

  // Match operations
  async getMatches(): Promise<Match[]> {
    return await db.select().from(matches).orderBy(desc(matches.createdAt));
  }

  async getMatchesByTournament(tournamentId: number): Promise<Match[]> {
    return await db
      .select()
      .from(matches)
      .where(eq(matches.tournamentId, tournamentId))
      .orderBy(matches.scheduledTime);
  }

  async createMatch(match: InsertMatch): Promise<Match> {
    const [newMatch] = await db.insert(matches).values(match).returning();
    return newMatch;
  }

  async updateMatch(id: number, match: Partial<InsertMatch>): Promise<Match> {
    const [updatedMatch] = await db
      .update(matches)
      .set({ ...match, updatedAt: new Date() })
      .where(eq(matches.id, id))
      .returning();
    return updatedMatch;
  }

  async deleteMatch(id: number): Promise<void> {
    await db.delete(matches).where(eq(matches.id, id));
  }

  // Super Admin operations implementation
  async getAllOrganizations(): Promise<Organization[]> {
    return await db.select().from(organizations);
  }

  async getAllModules(): Promise<any[]> {
    // Return mock modules for now since we don't have a modules table
    return [
      { id: 1, name: "player_management", displayName: "Player Management", description: "Manage players and rosters", category: "core", requiredTier: "basic" },
      { id: 2, name: "team_management", displayName: "Team Management", description: "Manage teams and coaches", category: "core", requiredTier: "basic" },
      { id: 3, name: "analytics", displayName: "Analytics", description: "Performance analytics and reporting", category: "analytics", requiredTier: "premium" },
      { id: 4, name: "video_highlights", displayName: "Video Highlights", description: "AI-powered video highlight generation", category: "advanced", requiredTier: "premium" },
      { id: 5, name: "biometric_scanning", displayName: "Biometric Scanning", description: "Facial recognition and biometric data", category: "security", requiredTier: "premium" },
      { id: 6, name: "smart_onboarding", displayName: "Smart Onboarding", description: "AI-powered onboarding recommendations", category: "ai", requiredTier: "premium" }
    ];
  }

  async getAllSubscriptionPlans(): Promise<any[]> {
    return await db.select().from(subscriptionPlans);
  }

  // Subscription Plan operations implementation
  async getSubscriptionPlans(): Promise<SubscriptionPlan[]> {
    return await db.select().from(subscriptionPlans).orderBy(subscriptionPlans.price);
  }

  async getActiveSubscriptionPlans(): Promise<SubscriptionPlan[]> {
    return await db.select().from(subscriptionPlans)
      .where(eq(subscriptionPlans.isActive, true))
      .orderBy(subscriptionPlans.price);
  }

  async getSubscriptionPlan(id: number): Promise<SubscriptionPlan | undefined> {
    const [plan] = await db.select().from(subscriptionPlans).where(eq(subscriptionPlans.id, id));
    return plan;
  }

  async createSubscriptionPlan(plan: InsertSubscriptionPlan): Promise<SubscriptionPlan> {
    const [newPlan] = await db.insert(subscriptionPlans).values(plan).returning();
    return newPlan;
  }

  async updateSubscriptionPlan(id: number, plan: Partial<InsertSubscriptionPlan>): Promise<SubscriptionPlan> {
    const [updatedPlan] = await db
      .update(subscriptionPlans)
      .set({ ...plan, updatedAt: new Date() })
      .where(eq(subscriptionPlans.id, id))
      .returning();
    return updatedPlan;
  }

  async deleteSubscriptionPlan(id: number): Promise<void> {
    await db.delete(subscriptionPlans).where(eq(subscriptionPlans.id, id));
  }

  // Extended subscription operations implementation
  async getSubscriptionStats(): Promise<any> {
    // Return comprehensive stats
    const totalPlans = await db.select({ count: sql<number>`count(*)` }).from(subscriptionPlans);
    const activePlans = await db.select({ count: sql<number>`count(*)` }).from(subscriptionPlans).where(eq(subscriptionPlans.isActive, true));
    const totalOrgs = await db.select({ count: sql<number>`count(*)` }).from(organizations);
    const activeOrgs = await db.select({ count: sql<number>`count(*)` }).from(organizations).where(eq(organizations.subscriptionStatus, 'active'));
    
    return {
      totalPlans: totalPlans[0]?.count || 0,
      activePlans: activePlans[0]?.count || 0,
      inactivePlans: (totalPlans[0]?.count || 0) - (activePlans[0]?.count || 0),
      totalSubscriptions: totalOrgs[0]?.count || 0,
      activeSubscriptions: activeOrgs[0]?.count || 0,
      monthlyRevenue: 0,
      totalRevenue: 0,
      churnRate: 0,
      avgSubscriptionValue: 0,
      cancelledSubscriptions: 0,
      trialSubscriptions: 0,
    };
  }

  async getSubscriptions(): Promise<any[]> {
    // Return organizations with subscription info
    return await db.select({
      id: organizations.id,
      name: organizations.name,
      subscriptionTier: organizations.subscriptionTier,
      subscriptionStatus: organizations.subscriptionStatus,
      createdAt: organizations.createdAt,
    }).from(organizations).where(sql`subscription_tier IS NOT NULL`);
  }

  async getOverdueSubscriptions(): Promise<any[]> {
    // Return organizations with overdue subscriptions
    return await db.select({
      id: organizations.id,
      name: organizations.name,
      subscriptionTier: organizations.subscriptionTier,
      subscriptionStatus: organizations.subscriptionStatus,
      createdAt: organizations.createdAt,
    }).from(organizations).where(eq(organizations.subscriptionStatus, 'overdue'));
  }

  async getExpiringSubscriptions(days: number): Promise<any[]> {
    // Return empty array for now since we don't have subscription_expires_at column
    return [];
  }

  async cancelSubscription(id: number, reason?: string): Promise<any> {
    const [updatedOrg] = await db
      .update(organizations)
      .set({ 
        subscriptionStatus: 'cancelled',
        updatedAt: new Date()
      })
      .where(eq(organizations.id, id))
      .returning();
    return updatedOrg;
  }

  async suspendSubscription(id: number, reason?: string): Promise<any> {
    const [updatedOrg] = await db
      .update(organizations)
      .set({ 
        subscriptionStatus: 'suspended',
        updatedAt: new Date()
      })
      .where(eq(organizations.id, id))
      .returning();
    return updatedOrg;
  }

  async reactivateSubscription(id: number): Promise<any> {
    const [updatedOrg] = await db
      .update(organizations)
      .set({ 
        subscriptionStatus: 'active',
        updatedAt: new Date()
      })
      .where(eq(organizations.id, id))
      .returning();
    return updatedOrg;
  }

  async getPayments(): Promise<any[]> {
    // Return basic payment data structure for now since paymentActivities table has different columns
    return [];
  }

  async getOrganizationModulePermissions(orgId: number): Promise<any[]> {
    return await db.select().from(organizationModulePermissions).where(eq(organizationModulePermissions.organizationId, orgId));
  }

  async updateOrganizationSubscription(orgId: number, data: any): Promise<void> {
    await db.update(organizations)
      .set({ 
        subscriptionTier: data.subscriptionTier, 
        subscriptionStatus: data.subscriptionStatus,
        updatedAt: new Date()
      })
      .where(eq(organizations.id, orgId));
  }

  async updateOrganizationModulePermission(orgId: number, moduleId: number, enabled: boolean): Promise<void> {
    const existing = await db.select()
      .from(organizationModulePermissions)
      .where(and(
        eq(organizationModulePermissions.organizationId, orgId),
        eq(organizationModulePermissions.moduleId, moduleId)
      ));

    if (existing.length > 0) {
      await db.update(organizationModulePermissions)
        .set({ isEnabled: enabled, updatedAt: new Date() })
        .where(and(
          eq(organizationModulePermissions.organizationId, orgId),
          eq(organizationModulePermissions.moduleId, moduleId)
        ));
    } else {
      await db.insert(organizationModulePermissions).values({
        organizationId: orgId,
        moduleId: moduleId,
        isEnabled: enabled,
        enabledBy: 'superadmin',
        createdAt: new Date(),
        updatedAt: new Date()
      });
    }
  }

  async createModule(moduleData: InsertModule): Promise<Module> {
    const [module] = await db.insert(modules).values({
      ...moduleData,
      createdAt: new Date(),
      updatedAt: new Date()
    }).returning();
    return module;
  }
  
  async getModules(): Promise<Module[]> {
    return await db.select().from(modules).orderBy(modules.category, modules.displayName);
  }
  
  async getModule(id: number): Promise<Module | undefined> {
    const [module] = await db.select().from(modules).where(eq(modules.id, id));
    return module;
  }
  
  async updateModule(id: number, moduleData: Partial<InsertModule>): Promise<Module> {
    const [updated] = await db.update(modules)
      .set({ ...moduleData, updatedAt: new Date() })
      .where(eq(modules.id, id))
      .returning();
    return updated;
  }

  async createOrganization(organizationData: any): Promise<Organization> {
    const [organization] = await db.insert(organizations).values({
      ...organizationData,
      createdAt: new Date(),
      updatedAt: new Date()
    }).returning();
    return organization;
  }

  async getBrandingSettings(organizationId: number): Promise<any | undefined> {
    const [settings] = await db.select()
      .from(brandingSettings)
      .where(eq(brandingSettings.organizationId, organizationId));
    return settings;
  }

  async upsertBrandingSettings(organizationId: number, settingsData: any): Promise<any> {
    const existing = await this.getBrandingSettings(organizationId);
    
    if (existing) {
      const [updated] = await db.update(brandingSettings)
        .set({
          ...settingsData,
          updatedAt: new Date()
        })
        .where(eq(brandingSettings.organizationId, organizationId))
        .returning();
      return updated;
    } else {
      const [created] = await db.insert(brandingSettings)
        .values({
          ...settingsData,
          organizationId,
          createdAt: new Date(),
          updatedAt: new Date()
        })
        .returning();
      return created;
    }
  }

  // Notification operations
  async createNotification(notification: InsertNotification): Promise<Notification> {
    const [newNotification] = await db.insert(notifications).values(notification).returning();
    return newNotification;
  }

  async getNotifications(userId: string, limit: number = 50): Promise<Notification[]> {
    return await db.select()
      .from(notifications)
      .where(eq(notifications.userId, userId))
      .orderBy(desc(notifications.createdAt))
      .limit(limit);
  }

  async getUnreadNotifications(userId: string): Promise<Notification[]> {
    return await db.select()
      .from(notifications)
      .where(and(
        eq(notifications.userId, userId),
        eq(notifications.isRead, false)
      ))
      .orderBy(desc(notifications.createdAt));
  }

  async markNotificationAsRead(id: number): Promise<void> {
    await db.update(notifications)
      .set({ isRead: true, readAt: new Date() })
      .where(eq(notifications.id, id));
  }

  async markAllNotificationsAsRead(userId: string): Promise<void> {
    await db.update(notifications)
      .set({ isRead: true, readAt: new Date() })
      .where(and(
        eq(notifications.userId, userId),
        eq(notifications.isRead, false)
      ));
  }

  async deleteNotification(id: number): Promise<void> {
    await db.delete(notifications).where(eq(notifications.id, id));
  }

  // Audit log operations
  async createAuditLog(log: InsertAuditLog): Promise<AuditLog> {
    const [newLog] = await db.insert(auditLogs).values(log).returning();
    return newLog;
  }

  async getAuditLogs(filters?: {
    userId?: string;
    organizationId?: number;
    resource?: string;
    action?: string;
    startDate?: Date;
    endDate?: Date;
    limit?: number;
  }): Promise<AuditLog[]> {
    let query = db.select().from(auditLogs);

    const conditions = [];
    if (filters?.userId) {
      conditions.push(eq(auditLogs.userId, filters.userId));
    }
    if (filters?.organizationId) {
      conditions.push(eq(auditLogs.organizationId, filters.organizationId));
    }
    if (filters?.resource) {
      conditions.push(eq(auditLogs.resource, filters.resource));
    }
    if (filters?.action) {
      conditions.push(eq(auditLogs.action, filters.action));
    }
    if (filters?.startDate) {
      conditions.push(gte(auditLogs.createdAt, filters.startDate));
    }
    if (filters?.endDate) {
      conditions.push(lte(auditLogs.createdAt, filters.endDate));
    }

    if (conditions.length > 0) {
      query = query.where(and(...conditions)) as any;
    }

    const results = await query
      .orderBy(desc(auditLogs.createdAt))
      .limit(filters?.limit || 100);

    return results as AuditLog[];
  }

  async getAuditLog(id: number): Promise<AuditLog | undefined> {
    const [log] = await db.select().from(auditLogs).where(eq(auditLogs.id, id));
    return log;
  }

  // User preferences operations
  async getUserPreferences(userId: string): Promise<any> {
    const [prefs] = await db.select().from(userPreferences).where(eq(userPreferences.userId, userId));
    
    // If no preferences exist, create default ones
    if (!prefs) {
      const [newPrefs] = await db.insert(userPreferences)
        .values({ userId })
        .returning();
      return newPrefs;
    }
    
    return prefs;
  }

  async updateUserPreferences(userId: string, preferences: any): Promise<any> {
    // Check if preferences exist
    const existing = await this.getUserPreferences(userId);
    
    const [updated] = await db.update(userPreferences)
      .set({ ...preferences, updatedAt: new Date() })
      .where(eq(userPreferences.userId, userId))
      .returning();
    
    return updated;
  }

  async verifyPassword(userId: string, password: string): Promise<boolean> {
    const user = await this.getUser(userId);
    
    if (!user || !user.password) {
      return false;
    }
    
    return await bcrypt.compare(password, user.password);
  }

  // League operations
  async createLeague(league: InsertLeague): Promise<League> {
    const [newLeague] = await db.insert(leagues).values(league).returning();
    return newLeague;
  }

  async getLeagues(organizationId?: number): Promise<League[]> {
    if (organizationId) {
      return await db.select().from(leagues).where(eq(leagues.organizationId, organizationId)).orderBy(desc(leagues.createdAt));
    }
    return await db.select().from(leagues).orderBy(desc(leagues.createdAt));
  }

  async getLeague(id: number): Promise<League | undefined> {
    const [league] = await db.select().from(leagues).where(eq(leagues.id, id));
    return league;
  }

  async updateLeague(id: number, league: Partial<InsertLeague>): Promise<League> {
    // Whitelist mutable fields only - prevent accidental foreign key updates
    const { organizationId, ...safeUpdates } = league;
    const [updated] = await db.update(leagues)
      .set({ ...safeUpdates, updatedAt: new Date() })
      .where(eq(leagues.id, id))
      .returning();
    return updated;
  }

  async deleteLeague(id: number): Promise<void> {
    await db.delete(leagues).where(eq(leagues.id, id));
  }

  async getLeagueStandings(leagueId: number): Promise<any[]> {
    const standings = await db.select({
      team: teams,
      stats: leagueTeams,
    })
      .from(leagueTeams)
      .innerJoin(teams, eq(leagueTeams.teamId, teams.id))
      .where(eq(leagueTeams.leagueId, leagueId))
      .orderBy(desc(leagueTeams.points), desc(leagueTeams.goalDifference));
    
    return standings;
  }

  // League Team operations
  async addTeamToLeague(leagueTeam: InsertLeagueTeam): Promise<LeagueTeam> {
    const [newLeagueTeam] = await db.insert(leagueTeams).values(leagueTeam).returning();
    return newLeagueTeam;
  }

  async removeTeamFromLeague(leagueId: number, teamId: number): Promise<void> {
    await db.delete(leagueTeams)
      .where(and(
        eq(leagueTeams.leagueId, leagueId),
        eq(leagueTeams.teamId, teamId)
      ));
  }

  async updateLeagueTeamStats(leagueId: number, teamId: number, stats: Partial<InsertLeagueTeam>): Promise<LeagueTeam> {
    // Prevent updating foreign keys - only update stats fields
    const { leagueId: _, teamId: __, ...safeStats } = stats;
    const [updated] = await db.update(leagueTeams)
      .set(safeStats)
      .where(and(
        eq(leagueTeams.leagueId, leagueId),
        eq(leagueTeams.teamId, teamId)
      ))
      .returning();
    return updated;
  }

  // Venue operations
  async createVenue(venue: InsertVenue): Promise<Venue> {
    const [newVenue] = await db.insert(venues).values(venue).returning();
    return newVenue;
  }

  async getVenues(organizationId?: number): Promise<Venue[]> {
    if (organizationId) {
      return await db.select().from(venues).where(eq(venues.organizationId, organizationId)).orderBy(asc(venues.name));
    }
    return await db.select().from(venues).orderBy(asc(venues.name));
  }

  async getVenue(id: number): Promise<Venue | undefined> {
    const [venue] = await db.select().from(venues).where(eq(venues.id, id));
    return venue;
  }

  async updateVenue(id: number, venue: Partial<InsertVenue>): Promise<Venue> {
    // Prevent updating organizationId
    const { organizationId, ...safeUpdates } = venue;
    const [updated] = await db.update(venues)
      .set({ ...safeUpdates, updatedAt: new Date() })
      .where(eq(venues.id, id))
      .returning();
    return updated;
  }

  async deleteVenue(id: number): Promise<void> {
    await db.delete(venues).where(eq(venues.id, id));
  }

  // Referee operations
  async createReferee(referee: InsertReferee): Promise<Referee> {
    const [newReferee] = await db.insert(referees).values(referee).returning();
    return newReferee;
  }

  async getReferees(organizationId?: number): Promise<Referee[]> {
    if (organizationId) {
      return await db.select().from(referees).where(eq(referees.organizationId, organizationId)).orderBy(asc(referees.lastName));
    }
    return await db.select().from(referees).orderBy(asc(referees.lastName));
  }

  async getReferee(id: number): Promise<Referee | undefined> {
    const [referee] = await db.select().from(referees).where(eq(referees.id, id));
    return referee;
  }

  async updateReferee(id: number, referee: Partial<InsertReferee>): Promise<Referee> {
    // Prevent updating organizationId
    const { organizationId, ...safeUpdates } = referee;
    const [updated] = await db.update(referees)
      .set({ ...safeUpdates, updatedAt: new Date() })
      .where(eq(referees.id, id))
      .returning();
    return updated;
  }

  async deleteReferee(id: number): Promise<void> {
    await db.delete(referees).where(eq(referees.id, id));
  }

  // Fixture operations
  async createFixture(fixture: InsertFixture): Promise<Fixture> {
    const [newFixture] = await db.insert(fixtures).values(fixture).returning();
    return newFixture;
  }

  async getFixtures(filters?: { leagueId?: number; tournamentId?: number; status?: string }): Promise<Fixture[]> {
    let query = db.select().from(fixtures);
    
    const conditions = [];
    if (filters?.leagueId) {
      conditions.push(eq(fixtures.leagueId, filters.leagueId));
    }
    if (filters?.tournamentId) {
      conditions.push(eq(fixtures.tournamentId, filters.tournamentId));
    }
    if (filters?.status) {
      conditions.push(eq(fixtures.status, filters.status));
    }

    if (conditions.length > 0) {
      query = query.where(and(...conditions)) as any;
    }

    return await query.orderBy(asc(fixtures.scheduledDate));
  }

  async getFixture(id: number): Promise<Fixture | undefined> {
    const [fixture] = await db.select().from(fixtures).where(eq(fixtures.id, id));
    return fixture;
  }

  async updateFixture(id: number, fixture: Partial<InsertFixture>): Promise<Fixture> {
    const [updated] = await db.update(fixtures)
      .set({ ...fixture, updatedAt: new Date() })
      .where(eq(fixtures.id, id))
      .returning();
    return updated;
  }

  async deleteFixture(id: number): Promise<void> {
    await db.delete(fixtures).where(eq(fixtures.id, id));
  }

  async getUpcomingFixtures(limit: number = 10): Promise<Fixture[]> {
    return await db.select()
      .from(fixtures)
      .where(eq(fixtures.status, 'scheduled'))
      .orderBy(asc(fixtures.scheduledDate))
      .limit(limit);
  }

  async getLiveFixtures(): Promise<Fixture[]> {
    return await db.select()
      .from(fixtures)
      .where(eq(fixtures.status, 'live'))
      .orderBy(asc(fixtures.scheduledDate));
  }

  // Match Event operations
  async createMatchEvent(event: InsertMatchEvent): Promise<MatchEvent> {
    const [newEvent] = await db.insert(matchEvents).values(event).returning();
    return newEvent;
  }

  async getMatchEvents(fixtureId: number): Promise<MatchEvent[]> {
    return await db.select()
      .from(matchEvents)
      .where(eq(matchEvents.fixtureId, fixtureId))
      .orderBy(asc(matchEvents.minute), asc(matchEvents.extraTime));
  }

  async deleteMatchEvent(id: number): Promise<void> {
    await db.delete(matchEvents).where(eq(matchEvents.id, id));
  }

  // Match Statistics operations
  async createMatchStatistics(stats: InsertMatchStatistics): Promise<MatchStatistics> {
    const [newStats] = await db.insert(matchStatistics).values(stats).returning();
    return newStats;
  }

  async getMatchStatistics(fixtureId: number): Promise<MatchStatistics[]> {
    return await db.select()
      .from(matchStatistics)
      .where(eq(matchStatistics.fixtureId, fixtureId));
  }

  async updateMatchStatistics(id: number, stats: Partial<InsertMatchStatistics>): Promise<MatchStatistics> {
    const [updated] = await db.update(matchStatistics)
      .set({ ...stats, updatedAt: new Date() })
      .where(eq(matchStatistics.id, id))
      .returning();
    return updated;
  }

  // Player Transfer operations
  async createPlayerTransfer(transfer: InsertPlayerTransfer): Promise<PlayerTransfer> {
    const [newTransfer] = await db.insert(playerTransfers).values(transfer).returning();
    return newTransfer;
  }

  async getPlayerTransfers(filters?: { status?: string; playerId?: number }): Promise<PlayerTransfer[]> {
    let query = db.select().from(playerTransfers);
    
    const conditions = [];
    if (filters?.status) {
      conditions.push(eq(playerTransfers.status, filters.status));
    }
    if (filters?.playerId) {
      conditions.push(eq(playerTransfers.playerId, filters.playerId));
    }

    if (conditions.length > 0) {
      query = query.where(and(...conditions)) as any;
    }

    return await query.orderBy(desc(playerTransfers.createdAt));
  }

  async getPlayerTransfer(id: number): Promise<PlayerTransfer | undefined> {
    const [transfer] = await db.select().from(playerTransfers).where(eq(playerTransfers.id, id));
    return transfer;
  }

  async updatePlayerTransfer(id: number, transfer: Partial<InsertPlayerTransfer>): Promise<PlayerTransfer> {
    // Prevent updating immutable fields (player, teams, requester)
    const { playerId, fromTeamId, toTeamId, requestedBy, ...safeUpdates } = transfer;
    const [updated] = await db.update(playerTransfers)
      .set({ ...safeUpdates, updatedAt: new Date() })
      .where(eq(playerTransfers.id, id))
      .returning();
    return updated;
  }

  async approvePlayerTransfer(id: number, approvedBy: string): Promise<PlayerTransfer> {
    const [updated] = await db.update(playerTransfers)
      .set({ 
        status: 'approved',
        approvedBy,
        approvalDate: new Date(),
        updatedAt: new Date()
      })
      .where(eq(playerTransfers.id, id))
      .returning();
    return updated;
  }

  async rejectPlayerTransfer(id: number, rejectionReason: string): Promise<PlayerTransfer> {
    const [updated] = await db.update(playerTransfers)
      .set({ 
        status: 'rejected',
        rejectionReason,
        updatedAt: new Date()
      })
      .where(eq(playerTransfers.id, id))
      .returning();
    return updated;
  }

  async completePlayerTransfer(id: number): Promise<PlayerTransfer> {
    const transfer = await this.getPlayerTransfer(id);
    if (!transfer) {
      throw new Error("Transfer not found");
    }

    // Update player's team
    if (transfer.playerId && transfer.toTeamId) {
      await this.updatePlayer(transfer.playerId, { teamId: transfer.toTeamId });
    }

    // Update transfer status
    const [updated] = await db.update(playerTransfers)
      .set({ 
        status: 'completed',
        completionDate: new Date(),
        updatedAt: new Date()
      })
      .where(eq(playerTransfers.id, id))
      .returning();
    return updated;
  }
}

export const storage = new DatabaseStorage();