import {
  users,
  organizations,
  modules,
  subscriptionPlans,
  organizationModulePermissions,
  teams,
  players,
  coaches,
  teamCoaches,
  tournaments,
  matches,
  activities,
  activityAttendance,
  departments,
  rosters,
  rosterAssignments,
  rosterSubstitutes,
  passwordResetTokens,

  dashboardWidgets,
  widgetTemplates,
  clubNews,
  newsletters,
  emailLogs,
  clubBanners,
  clubTerms,
  memberTermsAgreements,
  communicationPreferences,
  newsletterTemplates,
  newsComments,
  clubEvents,
  eventAttendees,
  notificationSettings,
  communicationAnalytics,
  membershipFees,
  paymentActivities,
  paymentNotifications,
  automaticPaymentSchedules,
  memberStatusHistory,
  paymentActivities,
  paymentNotificationSettings,
  coachingFeedbackReactions,
  playerFeedbackLogs,
  feedbackSessions,
  userClubRoles,
  type User,
  type UpsertUser,
  type Team,
  type InsertTeam,
  type Player,
  type InsertPlayer,
  type Coach,
  type InsertCoach,
  type TeamCoach,
  type InsertTeamCoach,
  type Tournament,
  type InsertTournament,
  type Match,
  type InsertMatch,
  type Activity,
  type InsertActivity,
  type ActivityAttendance,
  type InsertActivityAttendance,
  type Department,
  type InsertDepartment,
  type Roster,
  type InsertRoster,
  type RosterAssignment,
  type InsertRosterAssignment,
  type RosterSubstitute,
  type InsertRosterSubstitute,

  type DashboardWidget,
  type InsertDashboardWidget,
  type WidgetTemplate,
  type InsertWidgetTemplate,
  type ClubNews,
  type InsertClubNews,
  type Newsletter,
  type InsertNewsletter,
  type EmailLog,
  type ClubBanner,
  type InsertClubBanner,
  type ClubTerms,
  type InsertClubTerms,
  type MemberTermsAgreement,
  type InsertMemberTermsAgreement,
  type CommunicationPreferences,
  type InsertCommunicationPreferences,
  type NewsletterTemplate,
  type InsertNewsletterTemplate,
  type NewsComment,
  type InsertNewsComment,
  type ClubEvent,
  type InsertClubEvent,
  type EventAttendee,
  type InsertEventAttendee,
  type NotificationSettings,
  type InsertNotificationSettings,
  type CommunicationAnalytics,
  type InsertCommunicationAnalytics,
  type AchievementCategory,
  type InsertAchievementCategory,
  type Achievement,
  type InsertAchievement,
  type UserAchievement,
  type InsertUserAchievement,
  type UserPoints,
  type InsertUserPoints,
  type PointTransaction,
  type InsertPointTransaction,
  type Leaderboard,
  type InsertLeaderboard,
  type LeaderboardEntry,
  type InsertLeaderboardEntry,
  type ChallengeType,
  type InsertChallengeType,
  type Challenge,
  type InsertChallenge,
  type ChallengeParticipant,
  type InsertChallengeParticipant,
  type Badge,
  type InsertBadge,
  type UserBadge,
  type InsertUserBadge,
  type Milestone,
  type InsertMilestone,
  type UserMilestone,
  type InsertUserMilestone,
  type MembershipPlan,
  type InsertMembershipPlan,
  type Subscription,
  type InsertSubscription,
  type Payment,
  type InsertPayment,
  type PaymentReminder,
  type InsertPaymentReminder,
  type SubscriptionEvent,
  type InsertSubscriptionEvent,
  type PaymentActivity,
  type InsertPaymentActivity,
  type PaymentNotificationSettings,
  type InsertPaymentNotificationSettings,
  type CoachingFeedbackReaction,
  type InsertCoachingFeedbackReaction,
  type PlayerFeedbackLog,
  type InsertPlayerFeedbackLog,
  type FeedbackSession,
  type PasswordResetToken,
  type InsertPasswordResetToken,
  type InsertFeedbackSession,
  type UserClubRole,
  type InsertUserClubRole,
} from "@shared/schema";
import { db } from "./db";
import { eq, desc, and, count, sql, or, isNull, gt, lte, gte, lt } from "drizzle-orm";

export interface IStorage {
  // User operations (supporting both Replit Auth and local auth)
  getUser(id: string): Promise<User | undefined>;
  upsertUser(user: UpsertUser): Promise<User>;
  getUserByEmail(email: string): Promise<User | undefined>;
  createLocalUser(user: { firstName: string; lastName: string; email: string; password: string }): Promise<User>;
  updateLocalUser(id: string, user: { firstName?: string; lastName?: string; email?: string }): Promise<User>;
  updateUserPassword(userId: string, hashedPassword: string): Promise<User>;
  updateUser(id: string, user: Partial<User>): Promise<User>;

  // User club role operations
  getUserClubRoles(userId: string): Promise<UserClubRole[]>;
  getUserClubRole(roleId: number): Promise<UserClubRole | undefined>;
  createUserClubRole(role: InsertUserClubRole): Promise<UserClubRole>;
  updateUserClubRole(roleId: number, role: Partial<InsertUserClubRole>): Promise<UserClubRole>;
  deleteUserClubRole(roleId: number): Promise<void>;

  // Password reset operations
  createPasswordResetToken(token: InsertPasswordResetToken): Promise<PasswordResetToken>;
  getPasswordResetToken(token: string): Promise<PasswordResetToken | undefined>;
  markPasswordResetTokenAsUsed(tokenId: number): Promise<void>;
  deleteExpiredPasswordResetTokens(): Promise<void>;

  // Dashboard widget operations
  getUserDashboardWidgets(userId: string): Promise<DashboardWidget[]>;
  getDashboardWidget(id: number): Promise<DashboardWidget | undefined>;
  createDashboardWidget(widget: InsertDashboardWidget): Promise<DashboardWidget>;
  updateDashboardWidget(id: number, widget: Partial<InsertDashboardWidget>): Promise<DashboardWidget>;
  deleteDashboardWidget(id: number): Promise<void>;
  updateWidgetPosition(id: number, position: any): Promise<DashboardWidget>;
  
  // Widget template operations
  getWidgetTemplates(): Promise<WidgetTemplate[]>;
  getWidgetTemplatesByCategory(category: string): Promise<WidgetTemplate[]>;
  getWidgetTemplate(id: number): Promise<WidgetTemplate | undefined>;
  createWidgetTemplate(template: InsertWidgetTemplate): Promise<WidgetTemplate>;
  updateWidgetTemplate(id: number, template: Partial<InsertWidgetTemplate>): Promise<WidgetTemplate>;
  deleteWidgetTemplate(id: number): Promise<void>;
  
  // Team operations
  getTeams(): Promise<Team[]>;
  getTeam(id: number): Promise<Team | undefined>;
  createTeam(team: InsertTeam): Promise<Team>;
  updateTeam(id: number, team: Partial<InsertTeam>): Promise<Team>;
  deleteTeam(id: number): Promise<void>;
  
  // Player operations
  getPlayers(): Promise<(Player & { teamName?: string })[]>;
  getPlayer(id: number): Promise<Player | undefined>;
  createPlayer(player: InsertPlayer): Promise<Player>;
  updatePlayer(id: number, player: Partial<InsertPlayer>): Promise<Player>;
  deletePlayer(id: number): Promise<void>;
  getPlayersByTeam(teamId: number): Promise<Player[]>;
  
  // Coach operations
  getCoaches(): Promise<Coach[]>;
  getCoach(id: number): Promise<Coach | undefined>;
  getCoachByEmail(email: string): Promise<Coach | undefined>;
  createCoach(coach: InsertCoach): Promise<Coach>;
  updateCoach(id: number, coach: Partial<InsertCoach>): Promise<Coach>;
  deleteCoach(id: number): Promise<void>;
  
  // Team-Coach operations
  assignCoachToTeam(teamCoach: InsertTeamCoach): Promise<TeamCoach>;
  removeCoachFromTeam(teamId: number, coachId: number): Promise<void>;
  getTeamCoaches(teamId: number): Promise<(TeamCoach & { coach: Coach })[]>;
  
  // 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>;
  
  // Analytics operations
  getDashboardStats(): Promise<{
    totalTeams: number;
    totalPlayers: number;
    activePlayers: number;
    totalCoaches: number;
    playersByTeam: { teamName: string; playerCount: number }[];
    playersByGender: { male: number; female: number };
    playersByStatus: { active: number; inactive: number; suspended: number };
    playersByAge: { ageRange: string; count: number }[];
  }>;

  // Calendar/Activity operations
  getActivities(): Promise<Activity[]>;
  getActivity(id: number): Promise<Activity | undefined>;
  createActivity(activity: InsertActivity): Promise<Activity>;
  updateActivity(id: number, activity: Partial<InsertActivity>): Promise<Activity>;
  deleteActivity(id: number): Promise<void>;
  copyActivity(id: number, newDate: Date): Promise<Activity>;
  
  // Activity Attendance operations
  getActivityAttendance(activityId: number): Promise<(ActivityAttendance & { player?: Player; coach?: Coach })[]>;
  updateAttendance(attendanceId: number, updates: Partial<InsertActivityAttendance>): Promise<ActivityAttendance>;
  createActivityAttendance(attendance: InsertActivityAttendance): Promise<ActivityAttendance>;
  
  // Department operations
  getDepartments(): Promise<Department[]>;
  getDepartment(id: number): Promise<Department | undefined>;
  createDepartment(department: InsertDepartment): Promise<Department>;
  updateDepartment(id: number, department: Partial<InsertDepartment>): Promise<Department>;
  deleteDepartment(id: number): Promise<void>;

  // Roster operations
  getRosters(): Promise<Roster[]>;
  getRoster(id: number): Promise<Roster | undefined>;
  getRostersByTeam(teamId: number): Promise<Roster[]>;
  createRoster(roster: InsertRoster): Promise<Roster>;
  updateRoster(id: number, roster: Partial<InsertRoster>): Promise<Roster>;
  deleteRoster(id: number): Promise<void>;
  
  // Roster assignment operations
  getRosterAssignments(rosterId: number): Promise<(RosterAssignment & { player: Player })[]>;
  createRosterAssignment(assignment: InsertRosterAssignment): Promise<RosterAssignment>;
  updateRosterAssignment(id: number, assignment: Partial<InsertRosterAssignment>): Promise<RosterAssignment>;
  deleteRosterAssignment(id: number): Promise<void>;
  
  // Roster substitute operations
  getRosterSubstitutes(rosterId: number): Promise<(RosterSubstitute & { player: Player })[]>;
  createRosterSubstitute(substitute: InsertRosterSubstitute): Promise<RosterSubstitute>;
  updateRosterSubstitute(id: number, substitute: Partial<InsertRosterSubstitute>): Promise<RosterSubstitute>;
  deleteRosterSubstitute(id: number): Promise<void>;

  // Club News operations
  getClubNews(): Promise<ClubNews[]>;
  getClubNewsById(id: number): Promise<ClubNews | undefined>;
  getPublishedClubNews(): Promise<ClubNews[]>;
  createClubNews(news: InsertClubNews): Promise<ClubNews>;
  updateClubNews(id: number, news: Partial<InsertClubNews>): Promise<ClubNews>;
  deleteClubNews(id: number): Promise<void>;
  publishClubNews(id: number): Promise<ClubNews>;
  incrementNewsViewCount(id: number): Promise<void>;

  // Newsletter operations
  getNewsletters(): Promise<Newsletter[]>;
  getNewsletterById(id: number): Promise<Newsletter | undefined>;
  createNewsletter(newsletter: InsertNewsletter): Promise<Newsletter>;
  updateNewsletter(id: number, newsletter: Partial<InsertNewsletter>): Promise<Newsletter>;
  deleteNewsletter(id: number): Promise<void>;
  scheduleNewsletter(id: number, scheduledAt: Date): Promise<Newsletter>;
  sendNewsletter(id: number): Promise<Newsletter>;
  getNewsletterStats(id: number): Promise<{
    totalRecipients: number;
    deliveredCount: number;
    openCount: number;
    clickCount: number;
    bounceCount: number;
  }>;

  // Club Banner operations
  getClubBanners(): Promise<ClubBanner[]>;
  getActiveClubBanners(position?: string): Promise<ClubBanner[]>;
  getClubBannerById(id: number): Promise<ClubBanner | undefined>;
  createClubBanner(banner: InsertClubBanner): Promise<ClubBanner>;
  updateClubBanner(id: number, banner: Partial<InsertClubBanner>): Promise<ClubBanner>;
  deleteClubBanner(id: number): Promise<void>;
  incrementBannerClick(id: number): Promise<void>;
  incrementBannerView(id: number): Promise<void>;

  // Club Terms operations
  getClubTerms(): Promise<ClubTerms[]>;
  getActiveClubTerms(): Promise<ClubTerms[]>;
  getMandatoryClubTerms(): Promise<ClubTerms[]>;
  getClubTermsById(id: number): Promise<ClubTerms | undefined>;
  createClubTerms(terms: InsertClubTerms): Promise<ClubTerms>;
  updateClubTerms(id: number, terms: Partial<InsertClubTerms>): Promise<ClubTerms>;
  deleteClubTerms(id: number): Promise<void>;

  // Member Terms Agreement operations
  getMemberTermsAgreements(userId: string): Promise<MemberTermsAgreement[]>;
  getMemberTermsAgreement(userId: string, termsId: number): Promise<MemberTermsAgreement | undefined>;
  createMemberTermsAgreement(agreement: InsertMemberTermsAgreement): Promise<MemberTermsAgreement>;
  revokeMemberTermsAgreement(id: number, reason: string): Promise<MemberTermsAgreement>;
  checkMemberCompliance(userId: string): Promise<{
    hasAgreedToAll: boolean;
    pendingTerms: ClubTerms[];
    expiredAgreements: MemberTermsAgreement[];
  }>;

  // Communication Preferences operations
  getCommunicationPreferences(userId: string): Promise<CommunicationPreferences | undefined>;
  createCommunicationPreferences(preferences: InsertCommunicationPreferences): Promise<CommunicationPreferences>;
  updateCommunicationPreferences(userId: string, preferences: Partial<InsertCommunicationPreferences>): Promise<CommunicationPreferences>;

  // Enhanced Communication Features
  getNewsletterTemplates(): Promise<NewsletterTemplate[]>;
  createNewsletterTemplate(template: InsertNewsletterTemplate): Promise<NewsletterTemplate>;
  updateNewsletterTemplate(id: number, template: Partial<InsertNewsletterTemplate>): Promise<NewsletterTemplate>;
  deleteNewsletterTemplate(id: number): Promise<void>;
  
  getNewsComments(newsId: number): Promise<NewsComment[]>;
  createNewsComment(comment: InsertNewsComment): Promise<NewsComment>;
  approveNewsComment(id: number): Promise<NewsComment>;
  deleteNewsComment(id: number): Promise<void>;
  
  getClubEvents(): Promise<ClubEvent[]>;
  getClubEventById(id: number): Promise<ClubEvent | undefined>;
  createClubEvent(event: InsertClubEvent): Promise<ClubEvent>;
  updateClubEvent(id: number, event: Partial<InsertClubEvent>): Promise<ClubEvent>;
  deleteClubEvent(id: number): Promise<void>;
  
  getEventAttendees(eventId: number): Promise<EventAttendee[]>;
  registerForEvent(attendee: InsertEventAttendee): Promise<EventAttendee>;
  unregisterFromEvent(eventId: number, userId: string): Promise<void>;
  
  getNotificationSettings(userId: string): Promise<NotificationSettings | undefined>;
  createNotificationSettings(settings: InsertNotificationSettings): Promise<NotificationSettings>;
  updateNotificationSettings(userId: string, settings: Partial<InsertNotificationSettings>): Promise<NotificationSettings>;
  
  logCommunicationAnalytics(analytics: InsertCommunicationAnalytics): Promise<CommunicationAnalytics>;
  getCommunicationAnalytics(contentType: string, contentId: number): Promise<CommunicationAnalytics[]>;

  // Gamification System Operations
  // Achievement Categories
  getAchievementCategories(): Promise<AchievementCategory[]>;
  createAchievementCategory(category: InsertAchievementCategory): Promise<AchievementCategory>;
  updateAchievementCategory(id: number, category: Partial<InsertAchievementCategory>): Promise<AchievementCategory>;
  deleteAchievementCategory(id: number): Promise<void>;

  // Achievements
  getAchievements(): Promise<Achievement[]>;
  getAchievementsByCategory(categoryId: number): Promise<Achievement[]>;
  getAchievementsByRole(role: string): Promise<Achievement[]>;
  createAchievement(achievement: InsertAchievement): Promise<Achievement>;
  updateAchievement(id: number, achievement: Partial<InsertAchievement>): Promise<Achievement>;
  deleteAchievement(id: number): Promise<void>;

  // User Achievements
  getUserAchievements(userId: string): Promise<UserAchievement[]>;
  getUserAchievement(userId: string, achievementId: number): Promise<UserAchievement | undefined>;
  createUserAchievement(userAchievement: InsertUserAchievement): Promise<UserAchievement>;
  updateUserAchievement(id: number, progress: Partial<InsertUserAchievement>): Promise<UserAchievement>;
  completeUserAchievement(userId: string, achievementId: number): Promise<UserAchievement>;

  // User Points
  getUserPoints(userId: string): Promise<UserPoints | undefined>;
  createUserPoints(userPoints: InsertUserPoints): Promise<UserPoints>;
  updateUserPoints(userId: string, points: Partial<InsertUserPoints>): Promise<UserPoints>;
  addPointsToUser(userId: string, points: number, reason: string, sourceType: string, sourceId?: number): Promise<UserPoints>;
  
  // Point Transactions
  getPointTransactions(userId: string): Promise<PointTransaction[]>;
  createPointTransaction(transaction: InsertPointTransaction): Promise<PointTransaction>;

  // Leaderboards
  getLeaderboards(): Promise<Leaderboard[]>;
  getLeaderboard(id: number): Promise<Leaderboard | undefined>;
  createLeaderboard(leaderboard: InsertLeaderboard): Promise<Leaderboard>;
  updateLeaderboard(id: number, leaderboard: Partial<InsertLeaderboard>): Promise<Leaderboard>;
  deleteLeaderboard(id: number): Promise<void>;
  
  // Leaderboard Entries
  getLeaderboardEntries(leaderboardId: number): Promise<LeaderboardEntry[]>;
  createLeaderboardEntry(entry: InsertLeaderboardEntry): Promise<LeaderboardEntry>;
  updateLeaderboardEntry(id: number, entry: Partial<InsertLeaderboardEntry>): Promise<LeaderboardEntry>;
  refreshLeaderboard(leaderboardId: number): Promise<void>;

  // Challenge Types
  getChallengeTypes(): Promise<ChallengeType[]>;
  createChallengeType(type: InsertChallengeType): Promise<ChallengeType>;
  updateChallengeType(id: number, type: Partial<InsertChallengeType>): Promise<ChallengeType>;
  deleteChallengeType(id: number): Promise<void>;

  // Challenges
  getChallenges(): Promise<Challenge[]>;
  getActiveChallengess(): Promise<Challenge[]>;
  getChallenge(id: number): Promise<Challenge | undefined>;
  createChallenge(challenge: InsertChallenge): Promise<Challenge>;
  updateChallenge(id: number, challenge: Partial<InsertChallenge>): Promise<Challenge>;
  deleteChallenge(id: number): Promise<void>;

  // Challenge Participants
  getChallengeParticipants(challengeId: number): Promise<ChallengeParticipant[]>;
  getUserChallengeParticipation(userId: string): Promise<ChallengeParticipant[]>;
  joinChallenge(participant: InsertChallengeParticipant): Promise<ChallengeParticipant>;
  updateChallengeProgress(id: number, progress: Partial<InsertChallengeParticipant>): Promise<ChallengeParticipant>;
  leaveChallenge(challengeId: number, userId: string): Promise<void>;

  // Badges
  getBadges(): Promise<Badge[]>;
  createBadge(badge: InsertBadge): Promise<Badge>;
  updateBadge(id: number, badge: Partial<InsertBadge>): Promise<Badge>;
  deleteBadge(id: number): Promise<void>;

  // User Badges
  getUserBadges(userId: string): Promise<UserBadge[]>;
  awardBadge(userBadge: InsertUserBadge): Promise<UserBadge>;
  revokeBadge(userId: string, badgeId: number): Promise<void>;

  // Milestones
  getMilestones(): Promise<Milestone[]>;
  getMilestonesByRole(role: string): Promise<Milestone[]>;
  createMilestone(milestone: InsertMilestone): Promise<Milestone>;
  updateMilestone(id: number, milestone: Partial<InsertMilestone>): Promise<Milestone>;
  deleteMilestone(id: number): Promise<void>;

  // User Milestones
  getUserMilestones(userId: string): Promise<UserMilestone[]>;
  getUserMilestone(userId: string, milestoneId: number): Promise<UserMilestone | undefined>;
  createUserMilestone(userMilestone: InsertUserMilestone): Promise<UserMilestone>;
  updateUserMilestone(id: number, progress: Partial<InsertUserMilestone>): Promise<UserMilestone>;
  checkMilestoneProgress(userId: string, milestoneType: string, currentValue: number): Promise<void>;

  // Subscription Management
  
  // Membership Plans
  getMembershipPlans(): Promise<MembershipPlan[]>;
  getActiveMembershipPlans(): Promise<MembershipPlan[]>;
  getMembershipPlan(id: number): Promise<MembershipPlan | undefined>;
  createMembershipPlan(plan: InsertMembershipPlan): Promise<MembershipPlan>;
  updateMembershipPlan(id: number, plan: Partial<InsertMembershipPlan>): Promise<MembershipPlan>;
  deleteMembershipPlan(id: number): Promise<void>;

  // Subscriptions
  getSubscriptions(): Promise<(Subscription & { plan: MembershipPlan; user: User })[]>;
  getUserSubscription(userId: string): Promise<(Subscription & { plan: MembershipPlan }) | undefined>;
  getSubscription(id: number): Promise<(Subscription & { plan: MembershipPlan; user: User }) | undefined>;
  createSubscription(subscription: InsertSubscription): Promise<Subscription>;
  updateSubscription(id: number, subscription: Partial<InsertSubscription>): Promise<Subscription>;
  cancelSubscription(id: number, reason?: string): Promise<Subscription>;
  suspendSubscription(id: number, reason: string): Promise<Subscription>;
  reactivateSubscription(id: number): Promise<Subscription>;
  getExpiringSubscriptions(days: number): Promise<(Subscription & { plan: MembershipPlan; user: User })[]>;
  getOverdueSubscriptions(): Promise<(Subscription & { plan: MembershipPlan; user: User })[]>;

  // Payments
  getPayments(): Promise<(Payment & { subscription: Subscription; user: User })[]>;
  getUserPayments(userId: string): Promise<(Payment & { subscription: Subscription })[]>;
  getSubscriptionPayments(subscriptionId: number): Promise<Payment[]>;
  getPayment(id: number): Promise<(Payment & { subscription: Subscription; user: User }) | undefined>;
  createPayment(payment: InsertPayment): Promise<Payment>;
  updatePayment(id: number, payment: Partial<InsertPayment>): Promise<Payment>;
  markPaymentCompleted(id: number, paidAt: Date, stripeData?: any): Promise<Payment>;
  markPaymentFailed(id: number, reason: string): Promise<Payment>;
  processRefund(id: number, amount: number, reason: string): Promise<Payment>;

  // Payment Activities
  getPaymentActivities(): Promise<(PaymentActivity & { subscription: Subscription; user: User })[]>;
  getSubscriptionPaymentActivities(subscriptionId: number): Promise<PaymentActivity[]>;
  createPaymentActivity(activity: InsertPaymentActivity): Promise<PaymentActivity>;
  updatePaymentActivity(id: number, activity: Partial<InsertPaymentActivity>): Promise<PaymentActivity>;
  scheduleAutomaticPayment(subscriptionId: number, amount: number, scheduledDate: Date): Promise<PaymentActivity>;
  processScheduledPayments(): Promise<PaymentActivity[]>;

  // Payment Reminders
  getPaymentReminders(): Promise<(PaymentReminder & { subscription: Subscription; user: User })[]>;
  getUserPaymentReminders(userId: string): Promise<(PaymentReminder & { subscription: Subscription })[]>;
  createPaymentReminder(reminder: InsertPaymentReminder): Promise<PaymentReminder>;
  markReminderSent(id: number): Promise<PaymentReminder>;
  markReminderFailed(id: number): Promise<PaymentReminder>;
  schedulePaymentReminders(subscriptionId: number): Promise<PaymentReminder[]>;
  getPendingReminders(): Promise<(PaymentReminder & { subscription: Subscription; user: User })[]>;

  // Subscription Events
  getSubscriptionEvents(subscriptionId: number): Promise<SubscriptionEvent[]>;
  createSubscriptionEvent(event: InsertSubscriptionEvent): Promise<SubscriptionEvent>;
  logSubscriptionEvent(subscriptionId: number, eventType: string, description: string, eventData?: any): Promise<SubscriptionEvent>;

  // Payment Notification Settings
  getUserPaymentSettings(userId: string): Promise<PaymentNotificationSettings | undefined>;
  createPaymentSettings(settings: InsertPaymentNotificationSettings): Promise<PaymentNotificationSettings>;
  updatePaymentSettings(userId: string, settings: Partial<InsertPaymentNotificationSettings>): Promise<PaymentNotificationSettings>;

  // Quick Reaction Coaching Feedback System
  getCoachingFeedbackReactions(): Promise<CoachingFeedbackReaction[]>;
  getActiveCoachingFeedbackReactions(): Promise<CoachingFeedbackReaction[]>;
  createCoachingFeedbackReaction(reaction: InsertCoachingFeedbackReaction): Promise<CoachingFeedbackReaction>;
  updateCoachingFeedbackReaction(id: number, reaction: Partial<InsertCoachingFeedbackReaction>): Promise<CoachingFeedbackReaction>;
  deleteCoachingFeedbackReaction(id: number): Promise<void>;
  
  getPlayerFeedbackLogs(playerId: number): Promise<(PlayerFeedbackLog & { reaction: CoachingFeedbackReaction; coach: User })[]>;
  getCoachFeedbackLogs(coachId: string): Promise<(PlayerFeedbackLog & { reaction: CoachingFeedbackReaction; player: Player })[]>;
  getSessionFeedbackLogs(sessionId: number, sessionType: string): Promise<(PlayerFeedbackLog & { reaction: CoachingFeedbackReaction; player: Player; coach: User })[]>;
  createPlayerFeedbackLog(log: InsertPlayerFeedbackLog): Promise<PlayerFeedbackLog>;
  getFeedbackLogsByDateRange(startDate: Date, endDate: Date): Promise<(PlayerFeedbackLog & { reaction: CoachingFeedbackReaction; player: Player; coach: User })[]>;
  
  getFeedbackSessions(): Promise<(FeedbackSession & { team?: Team; coach: User })[]>;
  getActiveFeedbackSessions(coachId?: string): Promise<(FeedbackSession & { team?: Team; coach: User })[]>;
  getFeedbackSession(id: number): Promise<(FeedbackSession & { team?: Team; coach: User }) | undefined>;
  createFeedbackSession(session: InsertFeedbackSession): Promise<FeedbackSession>;
  updateFeedbackSession(id: number, session: Partial<InsertFeedbackSession>): Promise<FeedbackSession>;
  endFeedbackSession(id: number): Promise<FeedbackSession>;
  getFeedbackSessionStats(sessionId: number): Promise<{
    totalFeedbacks: number;
    feedbacksByCategory: { category: string; count: number }[];
    feedbacksByPlayer: { playerId: number; playerName: string; count: number }[];
    sessionDuration: number | null;
  }>;

  // Subscription Analytics
  getSubscriptionStats(): Promise<{
    totalSubscriptions: number;
    activeSubscriptions: number;
    trialSubscriptions: number;
    cancelledSubscriptions: number;
    suspendedSubscriptions: number;
    monthlyRevenue: number;
    totalRevenue: number;
    churnRate: number;
    avgSubscriptionValue: number;
  }>;

  // Automated Tasks
  processOverduePayments(): Promise<void>;
  sendPaymentReminders(): Promise<void>;
  suspendOverdueAccounts(): Promise<void>;
  processTrialExpirations(): Promise<void>;
}

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

  async createLocalUser(userData: { firstName: string; lastName: string; email: string; password: string }): Promise<User> {
    const id = crypto.randomUUID();
    const [user] = await db
      .insert(users)
      .values({
        id,
        firstName: userData.firstName,
        lastName: userData.lastName,
        email: userData.email,
        password: userData.password,
        authType: "local",
      })
      .returning();
    return user;
  }

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

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

  async createUserWithCustomPassword(userData: {
    firstName: string;
    lastName: string;
    email: string;
    password: string;
    role?: string;
    organizationId?: number;
    teamId?: number;
  }): Promise<User> {
    const bcrypt = await import('bcrypt');
    const hashedPassword = await bcrypt.hash(userData.password, 10);
    
    const { generateUsername } = await import('./password');
    const username = generateUsername(userData.firstName, userData.lastName);
    
    const [user] = await db
      .insert(users)
      .values({
        id: crypto.randomUUID(),
        firstName: userData.firstName,
        lastName: userData.lastName,
        email: userData.email,
        username,
        password: hashedPassword,
        role: userData.role || 'player',
        organizationId: userData.organizationId,
        teamId: userData.teamId,
        status: 'active',
        authType: 'local',
      })
      .returning();
    return user;
  }

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

  // User club role operations
  async getUserClubRoles(userId: string): Promise<UserClubRole[]> {
    return await db
      .select()
      .from(userClubRoles)
      .where(eq(userClubRoles.userId, userId))
      .orderBy(desc(userClubRoles.createdAt));
  }

  async getUserClubRole(roleId: number): Promise<UserClubRole | undefined> {
    const [role] = await db
      .select()
      .from(userClubRoles)
      .where(eq(userClubRoles.id, roleId));
    return role;
  }

  async createUserClubRole(roleData: InsertUserClubRole): Promise<UserClubRole> {
    const [newRole] = await db
      .insert(userClubRoles)
      .values(roleData)
      .returning();
    return newRole;
  }

  async updateUserClubRole(roleId: number, roleData: Partial<InsertUserClubRole>): Promise<UserClubRole> {
    const [updatedRole] = await db
      .update(userClubRoles)
      .set({ ...roleData, updatedAt: new Date() })
      .where(eq(userClubRoles.id, roleId))
      .returning();
    return updatedRole;
  }

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

  // Password reset operations
  async createPasswordResetToken(tokenData: InsertPasswordResetToken): Promise<PasswordResetToken> {
    const [token] = await db.insert(passwordResetTokens).values(tokenData).returning();
    return token;
  }

  async getPasswordResetToken(token: string): Promise<PasswordResetToken | undefined> {
    const [resetToken] = await db
      .select()
      .from(passwordResetTokens)
      .where(and(
        eq(passwordResetTokens.token, token),
        eq(passwordResetTokens.isUsed, false),
        gt(passwordResetTokens.expiresAt, new Date())
      ));
    return resetToken;
  }

  async markPasswordResetTokenAsUsed(tokenId: number): Promise<void> {
    await db
      .update(passwordResetTokens)
      .set({ isUsed: true })
      .where(eq(passwordResetTokens.id, tokenId));
  }

  async deleteExpiredPasswordResetTokens(): Promise<void> {
    await db
      .delete(passwordResetTokens)
      .where(or(
        eq(passwordResetTokens.isUsed, true),
        lt(passwordResetTokens.expiresAt, new Date())
      ));
  }

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

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

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

  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 getPlayers(): Promise<(Player & { teamName?: string })[]> {
    const result = await db
      .select({
        id: players.id,
        firstName: players.firstName,
        lastName: players.lastName,
        email: players.email,
        phone: players.phone,
        profileImageUrl: players.profileImageUrl,
        dateOfBirth: players.dateOfBirth,
        gender: players.gender,
        position: players.position,
        jerseyNumber: players.jerseyNumber,
        height: players.height,
        weight: players.weight,
        status: players.status,
        teamId: players.teamId,
        emergencyContact: players.emergencyContact,
        medicalNotes: players.medicalNotes,
        createdAt: players.createdAt,
        updatedAt: players.updatedAt,
        teamName: teams.name,
      })
      .from(players)
      .leftJoin(teams, eq(players.teamId, teams.id))
      .orderBy(desc(players.createdAt));
    
    return result.map(row => ({
      ...row,
      teamName: row.teamName || undefined
    }));
  }

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

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

  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));
  }

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

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

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

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

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

  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(teamCoach: InsertTeamCoach): Promise<TeamCoach> {
    const [newTeamCoach] = await db.insert(teamCoaches).values(teamCoach).returning();
    return newTeamCoach;
  }

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

  async getTeamCoaches(teamId: number): Promise<(TeamCoach & { coach: Coach })[]> {
    const result = await db
      .select({
        id: teamCoaches.id,
        teamId: teamCoaches.teamId,
        coachId: teamCoaches.coachId,
        role: teamCoaches.role,
        startDate: teamCoaches.startDate,
        endDate: teamCoaches.endDate,
        createdAt: teamCoaches.createdAt,
        coach: coaches,
      })
      .from(teamCoaches)
      .innerJoin(coaches, eq(teamCoaches.coachId, coaches.id))
      .where(eq(teamCoaches.teamId, teamId));
    
    return result;
  }

  // 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));
  }

  // Analytics operations
  async getDashboardStats() {
    // Get total counts
    const [teamCount] = await db.select({ count: count() }).from(teams);
    const [playerCount] = await db.select({ count: count() }).from(players);
    const [activePlayerCount] = await db
      .select({ count: count() })
      .from(players)
      .where(eq(players.status, "active"));
    const [coachCount] = await db.select({ count: count() }).from(coaches);

    // Get players by team
    const playersByTeam = await db
      .select({
        teamName: teams.name,
        playerCount: count(players.id),
      })
      .from(teams)
      .leftJoin(players, eq(teams.id, players.teamId))
      .groupBy(teams.id, teams.name)
      .orderBy(teams.name);

    // Get players by gender
    const genderStats = await db
      .select({
        gender: players.gender,
        count: count(),
      })
      .from(players)
      .groupBy(players.gender);

    const playersByGender = {
      male: genderStats.find(s => s.gender === "male")?.count || 0,
      female: genderStats.find(s => s.gender === "female")?.count || 0,
    };

    // Get players by status
    const statusStats = await db
      .select({
        status: players.status,
        count: count(),
      })
      .from(players)
      .groupBy(players.status);

    const playersByStatus = {
      active: statusStats.find(s => s.status === "active")?.count || 0,
      inactive: statusStats.find(s => s.status === "inactive")?.count || 0,
      suspended: statusStats.find(s => s.status === "suspended")?.count || 0,
    };

    // Get players by age (simplified - would need more complex calculation for real ages)
    const playersByAge = [
      { ageRange: "16-18", count: 0 },
      { ageRange: "19-21", count: 0 },
      { ageRange: "22-24", count: 0 },
      { ageRange: "25-27", count: 0 },
      { ageRange: "28-30", count: 0 },
      { ageRange: "31+", count: 0 },
    ];

    return {
      totalTeams: teamCount.count,
      totalPlayers: playerCount.count,
      activePlayers: activePlayerCount.count,
      totalCoaches: coachCount.count,
      playersByTeam: playersByTeam.map(p => ({
        teamName: p.teamName,
        playerCount: p.playerCount,
      })),
      playersByGender,
      playersByStatus,
      playersByAge,
    };
  }

  // Calendar/Activity operations
  async getActivities(): Promise<Activity[]> {
    return await db.select().from(activities).orderBy(desc(activities.startDateTime));
  }

  async getActivity(id: number): Promise<Activity | undefined> {
    const [activity] = await db.select().from(activities).where(eq(activities.id, id));
    return activity;
  }

  async createActivity(activity: InsertActivity): Promise<Activity> {
    const [newActivity] = await db
      .insert(activities)
      .values(activity)
      .returning();
    return newActivity;
  }

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

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

  async copyActivity(id: number, newDate: Date): Promise<Activity> {
    const originalActivity = await this.getActivity(id);
    if (!originalActivity) {
      throw new Error("Activity not found");
    }

    const timeDiff = new Date(originalActivity.endDateTime).getTime() - new Date(originalActivity.startDateTime).getTime();
    const newStartDateTime = newDate;
    const newEndDateTime = new Date(newDate.getTime() + timeDiff);

    const activityCopy = {
      ...originalActivity,
      id: undefined,
      title: `${originalActivity.title} (Copy)`,
      startDateTime: newStartDateTime,
      endDateTime: newEndDateTime,
      createdAt: undefined,
      updatedAt: undefined,
    };

    return await this.createActivity(activityCopy);
  }

  // Activity Attendance operations
  async getActivityAttendance(activityId: number): Promise<(ActivityAttendance & { player?: Player; coach?: Coach })[]> {
    const attendance = await db
      .select({
        attendance: activityAttendance,
        player: players,
        coach: coaches,
      })
      .from(activityAttendance)
      .leftJoin(players, eq(activityAttendance.playerId, players.id))
      .leftJoin(coaches, eq(activityAttendance.coachId, coaches.id))
      .where(eq(activityAttendance.activityId, activityId));

    return attendance.map(({ attendance, player, coach }) => ({
      ...attendance,
      player: player || undefined,
      coach: coach || undefined,
    }));
  }

  async updateAttendance(attendanceId: number, updates: Partial<InsertActivityAttendance>): Promise<ActivityAttendance> {
    const [updatedAttendance] = await db
      .update(activityAttendance)
      .set({ ...updates, updatedAt: new Date() })
      .where(eq(activityAttendance.id, attendanceId))
      .returning();
    return updatedAttendance;
  }

  async createActivityAttendance(attendance: InsertActivityAttendance): Promise<ActivityAttendance> {
    const [newAttendance] = await db
      .insert(activityAttendance)
      .values(attendance)
      .returning();
    return newAttendance;
  }

  // Department operations
  async getDepartments(): Promise<Department[]> {
    return await db.select().from(departments).orderBy(departments.name);
  }

  async getDepartment(id: number): Promise<Department | undefined> {
    const [department] = await db.select().from(departments).where(eq(departments.id, id));
    return department;
  }

  async createDepartment(department: InsertDepartment): Promise<Department> {
    const [newDepartment] = await db
      .insert(departments)
      .values(department)
      .returning();
    return newDepartment;
  }

  async updateDepartment(id: number, department: Partial<InsertDepartment>): Promise<Department> {
    const [updatedDepartment] = await db
      .update(departments)
      .set({ ...department, updatedAt: new Date() })
      .where(eq(departments.id, id))
      .returning();
    return updatedDepartment;
  }

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

  // Roster operations
  async getRosters(): Promise<Roster[]> {
    return await db.select().from(rosters);
  }

  async getRoster(id: number): Promise<Roster | undefined> {
    const [roster] = await db.select().from(rosters).where(eq(rosters.id, id));
    return roster;
  }

  async getRostersByTeam(teamId: number): Promise<Roster[]> {
    return await db.select().from(rosters).where(eq(rosters.teamId, teamId));
  }

  async createRoster(roster: InsertRoster): Promise<Roster> {
    const [newRoster] = await db.insert(rosters).values(roster).returning();
    return newRoster;
  }

  async updateRoster(id: number, roster: Partial<InsertRoster>): Promise<Roster> {
    const [updatedRoster] = await db
      .update(rosters)
      .set(roster)
      .where(eq(rosters.id, id))
      .returning();
    return updatedRoster;
  }

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

  // Roster assignment operations
  async getRosterAssignments(rosterId: number): Promise<(RosterAssignment & { player: Player })[]> {
    return await db
      .select({
        ...rosterAssignments,
        player: players,
      })
      .from(rosterAssignments)
      .leftJoin(players, eq(rosterAssignments.playerId, players.id))
      .where(eq(rosterAssignments.rosterId, rosterId));
  }

  async createRosterAssignment(assignment: InsertRosterAssignment): Promise<RosterAssignment> {
    const [newAssignment] = await db
      .insert(rosterAssignments)
      .values(assignment)
      .returning();
    return newAssignment;
  }

  async updateRosterAssignment(id: number, assignment: Partial<InsertRosterAssignment>): Promise<RosterAssignment> {
    const [updatedAssignment] = await db
      .update(rosterAssignments)
      .set(assignment)
      .where(eq(rosterAssignments.id, id))
      .returning();
    return updatedAssignment;
  }

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

  // Roster substitute operations
  async getRosterSubstitutes(rosterId: number): Promise<(RosterSubstitute & { player: Player })[]> {
    return await db
      .select({
        ...rosterSubstitutes,
        player: players,
      })
      .from(rosterSubstitutes)
      .leftJoin(players, eq(rosterSubstitutes.playerId, players.id))
      .where(eq(rosterSubstitutes.rosterId, rosterId));
  }

  async createRosterSubstitute(substitute: InsertRosterSubstitute): Promise<RosterSubstitute> {
    const [newSubstitute] = await db
      .insert(rosterSubstitutes)
      .values(substitute)
      .returning();
    return newSubstitute;
  }

  async updateRosterSubstitute(id: number, substitute: Partial<InsertRosterSubstitute>): Promise<RosterSubstitute> {
    const [updatedSubstitute] = await db
      .update(rosterSubstitutes)
      .set(substitute)
      .where(eq(rosterSubstitutes.id, id))
      .returning();
    return updatedSubstitute;
  }

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

  // Club News operations
  async getClubNews(): Promise<ClubNews[]> {
    return await db.select().from(clubNews).orderBy(desc(clubNews.createdAt));
  }

  async getClubNewsById(id: number): Promise<ClubNews | undefined> {
    const [news] = await db.select().from(clubNews).where(eq(clubNews.id, id));
    return news;
  }

  async getPublishedClubNews(): Promise<ClubNews[]> {
    return await db.select().from(clubNews)
      .where(and(
        eq(clubNews.status, 'published'),
        or(
          isNull(clubNews.expiresAt),
          gt(clubNews.expiresAt, new Date())
        )
      ))
      .orderBy(desc(clubNews.isSticky), desc(clubNews.publishedAt));
  }

  async createClubNews(news: InsertClubNews): Promise<ClubNews> {
    const [newNews] = await db.insert(clubNews).values(news).returning();
    return newNews;
  }

  async updateClubNews(id: number, news: Partial<InsertClubNews>): Promise<ClubNews> {
    const [updatedNews] = await db
      .update(clubNews)
      .set({ ...news, updatedAt: new Date() })
      .where(eq(clubNews.id, id))
      .returning();
    return updatedNews;
  }

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

  async publishClubNews(id: number): Promise<ClubNews> {
    const [publishedNews] = await db
      .update(clubNews)
      .set({ 
        status: 'published', 
        publishedAt: new Date(),
        updatedAt: new Date()
      })
      .where(eq(clubNews.id, id))
      .returning();
    return publishedNews;
  }

  async incrementNewsViewCount(id: number): Promise<void> {
    await db
      .update(clubNews)
      .set({ viewCount: sql`${clubNews.viewCount} + 1` })
      .where(eq(clubNews.id, id));
  }

  // Newsletter operations
  async getNewsletters(): Promise<Newsletter[]> {
    return await db.select().from(newsletters).orderBy(desc(newsletters.createdAt));
  }

  async getNewsletterById(id: number): Promise<Newsletter | undefined> {
    const [newsletter] = await db.select().from(newsletters).where(eq(newsletters.id, id));
    return newsletter;
  }

  async createNewsletter(newsletter: InsertNewsletter): Promise<Newsletter> {
    const [newNewsletter] = await db.insert(newsletters).values(newsletter).returning();
    return newNewsletter;
  }

  async updateNewsletter(id: number, newsletter: Partial<InsertNewsletter>): Promise<Newsletter> {
    const [updatedNewsletter] = await db
      .update(newsletters)
      .set({ ...newsletter, updatedAt: new Date() })
      .where(eq(newsletters.id, id))
      .returning();
    return updatedNewsletter;
  }

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

  async scheduleNewsletter(id: number, scheduledAt: Date): Promise<Newsletter> {
    const [scheduledNewsletter] = await db
      .update(newsletters)
      .set({ 
        status: 'scheduled', 
        scheduledAt,
        updatedAt: new Date()
      })
      .where(eq(newsletters.id, id))
      .returning();
    return scheduledNewsletter;
  }

  async sendNewsletter(id: number): Promise<Newsletter> {
    const [sentNewsletter] = await db
      .update(newsletters)
      .set({ 
        status: 'sent', 
        sentAt: new Date(),
        updatedAt: new Date()
      })
      .where(eq(newsletters.id, id))
      .returning();
    return sentNewsletter;
  }

  async getNewsletterStats(id: number): Promise<{
    totalRecipients: number;
    deliveredCount: number;
    openCount: number;
    clickCount: number;
    bounceCount: number;
  }> {
    const [newsletter] = await db.select().from(newsletters).where(eq(newsletters.id, id));
    return {
      totalRecipients: newsletter?.totalRecipients || 0,
      deliveredCount: newsletter?.deliveredCount || 0,
      openCount: newsletter?.openCount || 0,
      clickCount: newsletter?.clickCount || 0,
      bounceCount: newsletter?.bounceCount || 0,
    };
  }

  // Club Banner operations
  async getClubBanners(): Promise<ClubBanner[]> {
    return await db.select().from(clubBanners).orderBy(clubBanners.displayOrder, desc(clubBanners.createdAt));
  }

  async getActiveClubBanners(position?: string): Promise<ClubBanner[]> {
    const whereConditions = [
      eq(clubBanners.isActive, true),
      or(
        isNull(clubBanners.startDate),
        lte(clubBanners.startDate, new Date())
      ),
      or(
        isNull(clubBanners.endDate),
        gte(clubBanners.endDate, new Date())
      )
    ];

    if (position) {
      whereConditions.push(eq(clubBanners.position, position));
    }

    return await db.select().from(clubBanners)
      .where(and(...whereConditions))
      .orderBy(clubBanners.displayOrder, desc(clubBanners.createdAt));
  }

  async getClubBannerById(id: number): Promise<ClubBanner | undefined> {
    const [banner] = await db.select().from(clubBanners).where(eq(clubBanners.id, id));
    return banner;
  }

  async createClubBanner(banner: InsertClubBanner): Promise<ClubBanner> {
    const [newBanner] = await db.insert(clubBanners).values(banner).returning();
    return newBanner;
  }

  async updateClubBanner(id: number, banner: Partial<InsertClubBanner>): Promise<ClubBanner> {
    const [updatedBanner] = await db
      .update(clubBanners)
      .set({ ...banner, updatedAt: new Date() })
      .where(eq(clubBanners.id, id))
      .returning();
    return updatedBanner;
  }

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

  async incrementBannerClick(id: number): Promise<void> {
    await db
      .update(clubBanners)
      .set({ clickCount: sql`${clubBanners.clickCount} + 1` })
      .where(eq(clubBanners.id, id));
  }

  async incrementBannerView(id: number): Promise<void> {
    await db
      .update(clubBanners)
      .set({ viewCount: sql`${clubBanners.viewCount} + 1` })
      .where(eq(clubBanners.id, id));
  }

  // Club Terms operations
  async getClubTerms(): Promise<ClubTerms[]> {
    return await db.select().from(clubTerms).orderBy(desc(clubTerms.createdAt));
  }

  async getActiveClubTerms(): Promise<ClubTerms[]> {
    return await db.select().from(clubTerms)
      .where(and(
        eq(clubTerms.isActive, true),
        lte(clubTerms.effectiveDate, new Date()),
        or(
          isNull(clubTerms.expiryDate),
          gte(clubTerms.expiryDate, new Date())
        )
      ))
      .orderBy(clubTerms.type, desc(clubTerms.effectiveDate));
  }

  async getMandatoryClubTerms(): Promise<ClubTerms[]> {
    return await db.select().from(clubTerms)
      .where(and(
        eq(clubTerms.isActive, true),
        eq(clubTerms.isMandatory, true),
        lte(clubTerms.effectiveDate, new Date()),
        or(
          isNull(clubTerms.expiryDate),
          gte(clubTerms.expiryDate, new Date())
        )
      ))
      .orderBy(clubTerms.type, desc(clubTerms.effectiveDate));
  }

  async getClubTermsById(id: number): Promise<ClubTerms | undefined> {
    const [terms] = await db.select().from(clubTerms).where(eq(clubTerms.id, id));
    return terms;
  }

  async createClubTerms(terms: InsertClubTerms): Promise<ClubTerms> {
    const [newTerms] = await db.insert(clubTerms).values(terms).returning();
    return newTerms;
  }

  async updateClubTerms(id: number, terms: Partial<InsertClubTerms>): Promise<ClubTerms> {
    const [updatedTerms] = await db
      .update(clubTerms)
      .set({ ...terms, updatedAt: new Date() })
      .where(eq(clubTerms.id, id))
      .returning();
    return updatedTerms;
  }

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

  // Member Terms Agreement operations
  async getMemberTermsAgreements(userId: string): Promise<MemberTermsAgreement[]> {
    return await db.select().from(memberTermsAgreements)
      .where(and(
        eq(memberTermsAgreements.userId, userId),
        eq(memberTermsAgreements.isActive, true)
      ))
      .orderBy(desc(memberTermsAgreements.agreedAt));
  }

  async getMemberTermsAgreement(userId: string, termsId: number): Promise<MemberTermsAgreement | undefined> {
    const [agreement] = await db.select().from(memberTermsAgreements)
      .where(and(
        eq(memberTermsAgreements.userId, userId),
        eq(memberTermsAgreements.termsId, termsId),
        eq(memberTermsAgreements.isActive, true)
      ));
    return agreement;
  }

  async createMemberTermsAgreement(agreement: InsertMemberTermsAgreement): Promise<MemberTermsAgreement> {
    const [newAgreement] = await db.insert(memberTermsAgreements).values(agreement).returning();
    return newAgreement;
  }

  async revokeMemberTermsAgreement(id: number, reason: string): Promise<MemberTermsAgreement> {
    const [revokedAgreement] = await db
      .update(memberTermsAgreements)
      .set({ 
        isActive: false,
        revokedAt: new Date(),
        revokedReason: reason
      })
      .where(eq(memberTermsAgreements.id, id))
      .returning();
    return revokedAgreement;
  }

  async checkMemberCompliance(userId: string): Promise<{
    hasAgreedToAll: boolean;
    pendingTerms: ClubTerms[];
    expiredAgreements: MemberTermsAgreement[];
  }> {
    // Get all mandatory terms
    const mandatoryTerms = await this.getMandatoryClubTerms();
    
    // Get user's agreements
    const userAgreements = await this.getMemberTermsAgreements(userId);
    
    // Find pending terms (mandatory terms without agreements)
    const agreedTermsIds = new Set(userAgreements.map(a => a.termsId));
    const pendingTerms = mandatoryTerms.filter(term => !agreedTermsIds.has(term.id));
    
    // Check for expired agreements (where terms have been updated)
    const expiredAgreements = userAgreements.filter(agreement => {
      const term = mandatoryTerms.find(t => t.id === agreement.termsId);
      return term && term.updatedAt && agreement.agreedAt < term.updatedAt;
    });

    return {
      hasAgreedToAll: pendingTerms.length === 0 && expiredAgreements.length === 0,
      pendingTerms,
      expiredAgreements
    };
  }

  // Enhanced Communication Features
  
  // Newsletter Templates
  async getNewsletterTemplates(): Promise<NewsletterTemplate[]> {
    return await db.select().from(newsletterTemplates)
      .orderBy(desc(newsletterTemplates.createdAt));
  }

  async getNewsletterTemplateById(id: number): Promise<NewsletterTemplate | undefined> {
    const [template] = await db.select().from(newsletterTemplates).where(eq(newsletterTemplates.id, id));
    return template;
  }

  async createNewsletterTemplate(template: InsertNewsletterTemplate): Promise<NewsletterTemplate> {
    const [newTemplate] = await db.insert(newsletterTemplates).values(template).returning();
    return newTemplate;
  }

  async updateNewsletterTemplate(id: number, template: Partial<InsertNewsletterTemplate>): Promise<NewsletterTemplate> {
    const [updatedTemplate] = await db
      .update(newsletterTemplates)
      .set({ ...template, updatedAt: new Date() })
      .where(eq(newsletterTemplates.id, id))
      .returning();
    return updatedTemplate;
  }

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

  // News Comments
  async getNewsComments(newsId: number): Promise<NewsComment[]> {
    return await db.select().from(newsComments)
      .where(eq(newsComments.newsId, newsId))
      .orderBy(newsComments.createdAt);
  }

  async createNewsComment(comment: InsertNewsComment): Promise<NewsComment> {
    const [newComment] = await db.insert(newsComments).values(comment).returning();
    return newComment;
  }

  async updateNewsComment(id: number, comment: Partial<InsertNewsComment>): Promise<NewsComment> {
    const [updatedComment] = await db
      .update(newsComments)
      .set({ ...comment, updatedAt: new Date() })
      .where(eq(newsComments.id, id))
      .returning();
    return updatedComment;
  }

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

  async approveNewsComment(id: number): Promise<NewsComment> {
    const [approvedComment] = await db
      .update(newsComments)
      .set({ isApproved: true })
      .where(eq(newsComments.id, id))
      .returning();
    return approvedComment;
  }

  // Club Events
  async getClubEvents(): Promise<ClubEvent[]> {
    return await db.select().from(clubEvents)
      .orderBy(clubEvents.startDateTime);
  }

  async getClubEventById(id: number): Promise<ClubEvent | undefined> {
    const [event] = await db.select().from(clubEvents).where(eq(clubEvents.id, id));
    return event;
  }

  async getEventsByDateRange(startDate: Date, endDate: Date): Promise<ClubEvent[]> {
    return await db.select().from(clubEvents)
      .where(and(
        gte(clubEvents.startDateTime, startDate),
        lte(clubEvents.endDateTime, endDate)
      ))
      .orderBy(clubEvents.startDateTime);
  }

  async getEventsByType(eventType: string): Promise<ClubEvent[]> {
    return await db.select().from(clubEvents)
      .where(eq(clubEvents.eventType, eventType))
      .orderBy(clubEvents.startDateTime);
  }

  async createClubEvent(event: InsertClubEvent): Promise<ClubEvent> {
    const [newEvent] = await db.insert(clubEvents).values(event).returning();
    return newEvent;
  }

  async updateClubEvent(id: number, event: Partial<InsertClubEvent>): Promise<ClubEvent> {
    const [updatedEvent] = await db
      .update(clubEvents)
      .set({ ...event, updatedAt: new Date() })
      .where(eq(clubEvents.id, id))
      .returning();
    return updatedEvent;
  }

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

  // Event Attendees
  async getEventAttendees(eventId: number): Promise<EventAttendee[]> {
    return await db.select().from(eventAttendees)
      .where(eq(eventAttendees.eventId, eventId))
      .orderBy(eventAttendees.registeredAt);
  }

  async registerForEvent(attendee: InsertEventAttendee): Promise<EventAttendee> {
    const [newAttendee] = await db.insert(eventAttendees).values(attendee).returning();
    return newAttendee;
  }

  async updateEventAttendance(id: number, status: string, notes?: string): Promise<EventAttendee> {
    const [updatedAttendee] = await db
      .update(eventAttendees)
      .set({ status, notes })
      .where(eq(eventAttendees.id, id))
      .returning();
    return updatedAttendee;
  }

  async unregisterFromEvent(eventId: number, userId: string): Promise<void> {
    await db.delete(eventAttendees)
      .where(and(
        eq(eventAttendees.eventId, eventId),
        eq(eventAttendees.userId, userId)
      ));
  }

  // Notification Settings
  async getNotificationSettings(userId: string): Promise<NotificationSettings | undefined> {
    const [settings] = await db.select().from(notificationSettings)
      .where(eq(notificationSettings.userId, userId));
    return settings;
  }

  async createNotificationSettings(settings: InsertNotificationSettings): Promise<NotificationSettings> {
    const [newSettings] = await db.insert(notificationSettings).values(settings).returning();
    return newSettings;
  }

  async updateNotificationSettings(userId: string, settings: Partial<InsertNotificationSettings>): Promise<NotificationSettings> {
    const [updatedSettings] = await db
      .update(notificationSettings)
      .set({ ...settings, updatedAt: new Date() })
      .where(eq(notificationSettings.userId, userId))
      .returning();
    return updatedSettings;
  }

  // Communication Analytics
  async logCommunicationAnalytics(analytics: InsertCommunicationAnalytics): Promise<CommunicationAnalytics> {
    const [newAnalytics] = await db.insert(communicationAnalytics).values(analytics).returning();
    return newAnalytics;
  }

  async getCommunicationAnalytics(contentType: string, contentId: number): Promise<CommunicationAnalytics[]> {
    return await db.select().from(communicationAnalytics)
      .where(and(
        eq(communicationAnalytics.contentType, contentType),
        eq(communicationAnalytics.contentId, contentId)
      ))
      .orderBy(desc(communicationAnalytics.timestamp));
  }

  // Communication Preferences operations
  async getCommunicationPreferences(userId: string): Promise<CommunicationPreferences | undefined> {
    const [preferences] = await db.select().from(communicationPreferences)
      .where(eq(communicationPreferences.userId, userId));
    return preferences;
  }

  async createCommunicationPreferences(preferences: InsertCommunicationPreferences): Promise<CommunicationPreferences> {
    const [newPreferences] = await db.insert(communicationPreferences).values(preferences).returning();
    return newPreferences;
  }

  async updateCommunicationPreferences(userId: string, preferences: Partial<InsertCommunicationPreferences>): Promise<CommunicationPreferences> {
    const [updatedPreferences] = await db
      .update(communicationPreferences)
      .set({ ...preferences, updatedAt: new Date() })
      .where(eq(communicationPreferences.userId, userId))
      .returning();
    return updatedPreferences;
  }

  // Gamification System Implementation



  async createAchievement(achievement: InsertAchievement): Promise<Achievement> {
    const [newAchievement] = await db.insert(achievements).values(achievement).returning();
    return newAchievement;
  }

  async updateAchievement(id: number, achievement: Partial<InsertAchievement>): Promise<Achievement> {
    const [updatedAchievement] = await db
      .update(achievements)
      .set({ ...achievement, updatedAt: new Date() })
      .where(eq(achievements.id, id))
      .returning();
    return updatedAchievement;
  }

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

  // User Achievements
  async getUserAchievements(userId: string): Promise<UserAchievement[]> {
    return await db.select().from(userAchievements)
      .where(eq(userAchievements.userId, userId))
      .orderBy(desc(userAchievements.completedAt));
  }

  async getUserAchievement(userId: string, achievementId: number): Promise<UserAchievement | undefined> {
    const [userAchievement] = await db.select().from(userAchievements)
      .where(and(
        eq(userAchievements.userId, userId),
        eq(userAchievements.achievementId, achievementId)
      ));
    return userAchievement;
  }

  async createUserAchievement(userAchievement: InsertUserAchievement): Promise<UserAchievement> {
    const [newUserAchievement] = await db.insert(userAchievements).values(userAchievement).returning();
    return newUserAchievement;
  }

  async updateUserAchievement(id: number, progress: Partial<InsertUserAchievement>): Promise<UserAchievement> {
    const [updatedUserAchievement] = await db
      .update(userAchievements)
      .set(progress)
      .where(eq(userAchievements.id, id))
      .returning();
    return updatedUserAchievement;
  }

  async completeUserAchievement(userId: string, achievementId: number): Promise<UserAchievement> {
    const [completedAchievement] = await db
      .update(userAchievements)
      .set({ 
        isCompleted: true, 
        completedAt: new Date(),
        progress: 100
      })
      .where(and(
        eq(userAchievements.userId, userId),
        eq(userAchievements.achievementId, achievementId)
      ))
      .returning();
    return completedAchievement;
  }

  // User Points
  async getUserPoints(userId: string): Promise<UserPoints | undefined> {
    const [userPoints] = await db.select().from(userPoints)
      .where(eq(userPoints.userId, userId));
    return userPoints;
  }

  async createUserPoints(userPointsData: InsertUserPoints): Promise<UserPoints> {
    const [newUserPoints] = await db.insert(userPoints).values(userPointsData).returning();
    return newUserPoints;
  }

  async updateUserPoints(userId: string, points: Partial<InsertUserPoints>): Promise<UserPoints> {
    const [updatedUserPoints] = await db
      .update(userPoints)
      .set({ ...points, updatedAt: new Date() })
      .where(eq(userPoints.userId, userId))
      .returning();
    return updatedUserPoints;
  }

  async addPointsToUser(userId: string, points: number, reason: string, sourceType: string, sourceId?: number): Promise<UserPoints> {
    // Create point transaction
    await this.createPointTransaction({
      userId,
      points,
      reason,
      sourceType,
      sourceId
    });

    // Update or create user points
    const existingPoints = await this.getUserPoints(userId);
    if (existingPoints) {
      return await this.updateUserPoints(userId, {
        totalPoints: (existingPoints.totalPoints || 0) + points
      });
    } else {
      return await this.createUserPoints({
        userId,
        totalPoints: points
      });
    }
  }

  // Point Transactions
  async getPointTransactions(userId: string): Promise<PointTransaction[]> {
    return await db.select().from(pointTransactions)
      .where(eq(pointTransactions.userId, userId))
      .orderBy(desc(pointTransactions.createdAt));
  }

  async createPointTransaction(transaction: InsertPointTransaction): Promise<PointTransaction> {
    const [newTransaction] = await db.insert(pointTransactions).values(transaction).returning();
    return newTransaction;
  }

  // Leaderboards
  async getLeaderboards(): Promise<Leaderboard[]> {
    return await db.select().from(leaderboards)
      .where(eq(leaderboards.isActive, true))
      .orderBy(leaderboards.displayOrder);
  }

  async getLeaderboard(id: number): Promise<Leaderboard | undefined> {
    const [leaderboard] = await db.select().from(leaderboards).where(eq(leaderboards.id, id));
    return leaderboard;
  }

  async createLeaderboard(leaderboard: InsertLeaderboard): Promise<Leaderboard> {
    const [newLeaderboard] = await db.insert(leaderboards).values(leaderboard).returning();
    return newLeaderboard;
  }

  async updateLeaderboard(id: number, leaderboard: Partial<InsertLeaderboard>): Promise<Leaderboard> {
    const [updatedLeaderboard] = await db
      .update(leaderboards)
      .set({ ...leaderboard, updatedAt: new Date() })
      .where(eq(leaderboards.id, id))
      .returning();
    return updatedLeaderboard;
  }

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

  // Leaderboard Entries
  async getLeaderboardEntries(leaderboardId: number): Promise<LeaderboardEntry[]> {
    return await db.select().from(leaderboardEntries)
      .where(eq(leaderboardEntries.leaderboardId, leaderboardId))
      .orderBy(leaderboardEntries.rank);
  }

  async createLeaderboardEntry(entry: InsertLeaderboardEntry): Promise<LeaderboardEntry> {
    const [newEntry] = await db.insert(leaderboardEntries).values(entry).returning();
    return newEntry;
  }

  async updateLeaderboardEntry(id: number, entry: Partial<InsertLeaderboardEntry>): Promise<LeaderboardEntry> {
    const [updatedEntry] = await db
      .update(leaderboardEntries)
      .set({ ...entry, updatedAt: new Date() })
      .where(eq(leaderboardEntries.id, id))
      .returning();
    return updatedEntry;
  }

  async refreshLeaderboard(leaderboardId: number): Promise<void> {
    // This would implement the logic to refresh leaderboard rankings
    // For now, we'll just update the timestamp
    await db
      .update(leaderboards)
      .set({ updatedAt: new Date() })
      .where(eq(leaderboards.id, leaderboardId));
  }

  // Challenge Types
  async getChallengeTypes(): Promise<ChallengeType[]> {
    return await db.select().from(challengeTypes)
      .where(eq(challengeTypes.isActive, true))
      .orderBy(challengeTypes.name);
  }

  async createChallengeType(type: InsertChallengeType): Promise<ChallengeType> {
    const [newType] = await db.insert(challengeTypes).values(type).returning();
    return newType;
  }

  async updateChallengeType(id: number, type: Partial<InsertChallengeType>): Promise<ChallengeType> {
    const [updatedType] = await db
      .update(challengeTypes)
      .set({ ...type, updatedAt: new Date() })
      .where(eq(challengeTypes.id, id))
      .returning();
    return updatedType;
  }

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

  // Challenges
  async getChallenges(): Promise<Challenge[]> {
    return await db.select().from(challenges)
      .orderBy(desc(challenges.startDate));
  }

  async getActiveChallengess(): Promise<Challenge[]> {
    const now = new Date();
    return await db.select().from(challenges)
      .where(and(
        eq(challenges.isActive, true),
        lte(challenges.startDate, now),
        gte(challenges.endDate, now)
      ))
      .orderBy(challenges.endDate);
  }

  async getChallenge(id: number): Promise<Challenge | undefined> {
    const [challenge] = await db.select().from(challenges).where(eq(challenges.id, id));
    return challenge;
  }

  async createChallenge(challenge: InsertChallenge): Promise<Challenge> {
    const [newChallenge] = await db.insert(challenges).values(challenge).returning();
    return newChallenge;
  }

  async updateChallenge(id: number, challenge: Partial<InsertChallenge>): Promise<Challenge> {
    const [updatedChallenge] = await db
      .update(challenges)
      .set({ ...challenge, updatedAt: new Date() })
      .where(eq(challenges.id, id))
      .returning();
    return updatedChallenge;
  }

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

  // Challenge Participants
  async getChallengeParticipants(challengeId: number): Promise<ChallengeParticipant[]> {
    return await db.select().from(challengeParticipants)
      .where(eq(challengeParticipants.challengeId, challengeId))
      .orderBy(desc(challengeParticipants.progress));
  }

  async getUserChallengeParticipation(userId: string): Promise<ChallengeParticipant[]> {
    return await db.select().from(challengeParticipants)
      .where(eq(challengeParticipants.userId, userId))
      .orderBy(desc(challengeParticipants.joinedAt));
  }

  async joinChallenge(participant: InsertChallengeParticipant): Promise<ChallengeParticipant> {
    const [newParticipant] = await db.insert(challengeParticipants).values(participant).returning();
    return newParticipant;
  }

  async updateChallengeProgress(id: number, progress: Partial<InsertChallengeParticipant>): Promise<ChallengeParticipant> {
    const [updatedParticipant] = await db
      .update(challengeParticipants)
      .set({ ...progress, updatedAt: new Date() })
      .where(eq(challengeParticipants.id, id))
      .returning();
    return updatedParticipant;
  }

  async leaveChallenge(challengeId: number, userId: string): Promise<void> {
    await db.delete(challengeParticipants)
      .where(and(
        eq(challengeParticipants.challengeId, challengeId),
        eq(challengeParticipants.userId, userId)
      ));
  }

  // Badges
  async getBadges(): Promise<Badge[]> {
    return await db.select().from(badges)
      .where(eq(badges.isActive, true))
      .orderBy(badges.displayOrder);
  }

  async createBadge(badge: InsertBadge): Promise<Badge> {
    const [newBadge] = await db.insert(badges).values(badge).returning();
    return newBadge;
  }

  async updateBadge(id: number, badge: Partial<InsertBadge>): Promise<Badge> {
    const [updatedBadge] = await db
      .update(badges)
      .set({ ...badge, updatedAt: new Date() })
      .where(eq(badges.id, id))
      .returning();
    return updatedBadge;
  }

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

  // User Badges
  async getUserBadges(userId: string): Promise<UserBadge[]> {
    return await db.select().from(userBadges)
      .where(eq(userBadges.userId, userId))
      .orderBy(desc(userBadges.earnedAt));
  }

  async awardBadge(userBadge: InsertUserBadge): Promise<UserBadge> {
    const [newUserBadge] = await db.insert(userBadges).values(userBadge).returning();
    return newUserBadge;
  }

  async revokeBadge(userId: string, badgeId: number): Promise<void> {
    await db.delete(userBadges)
      .where(and(
        eq(userBadges.userId, userId),
        eq(userBadges.badgeId, badgeId)
      ));
  }

  // Milestones
  async getMilestones(): Promise<Milestone[]> {
    return await db.select().from(milestones)
      .where(eq(milestones.isActive, true))
      .orderBy(milestones.displayOrder);
  }

  async getMilestonesByRole(role: string): Promise<Milestone[]> {
    return await db.select().from(milestones)
      .where(and(
        eq(milestones.isActive, true),
        eq(milestones.targetRole, role)
      ))
      .orderBy(milestones.displayOrder);
  }

  async createMilestone(milestone: InsertMilestone): Promise<Milestone> {
    const [newMilestone] = await db.insert(milestones).values(milestone).returning();
    return newMilestone;
  }

  async updateMilestone(id: number, milestone: Partial<InsertMilestone>): Promise<Milestone> {
    const [updatedMilestone] = await db
      .update(milestones)
      .set({ ...milestone, updatedAt: new Date() })
      .where(eq(milestones.id, id))
      .returning();
    return updatedMilestone;
  }

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

  // User Milestones
  async getUserMilestones(userId: string): Promise<UserMilestone[]> {
    return await db.select().from(userMilestones)
      .where(eq(userMilestones.userId, userId))
      .orderBy(desc(userMilestones.achievedAt));
  }

  async getUserMilestone(userId: string, milestoneId: number): Promise<UserMilestone | undefined> {
    const [userMilestone] = await db.select().from(userMilestones)
      .where(and(
        eq(userMilestones.userId, userId),
        eq(userMilestones.milestoneId, milestoneId)
      ));
    return userMilestone;
  }

  async createUserMilestone(userMilestone: InsertUserMilestone): Promise<UserMilestone> {
    const [newUserMilestone] = await db.insert(userMilestones).values(userMilestone).returning();
    return newUserMilestone;
  }

  async updateUserMilestone(id: number, progress: Partial<InsertUserMilestone>): Promise<UserMilestone> {
    const [updatedUserMilestone] = await db
      .update(userMilestones)
      .set({ ...progress, updatedAt: new Date() })
      .where(eq(userMilestones.id, id))
      .returning();
    return updatedUserMilestone;
  }

  async checkMilestoneProgress(userId: string, milestoneType: string, currentValue: number): Promise<void> {
    // This would implement logic to check if user has reached any milestones
    // For now, we'll just log the check
    console.log(`Checking milestone progress for user ${userId}, type ${milestoneType}, value ${currentValue}`);
  }

  // Achievement System Implementation
  
  // Achievement Categories
  async getAchievementCategories(): Promise<AchievementCategory[]> {
    return await db.select().from(achievementCategories)
      .where(eq(achievementCategories.isActive, true));
  }

  async createAchievementCategory(category: InsertAchievementCategory): Promise<AchievementCategory> {
    const [newCategory] = await db.insert(achievementCategories).values(category).returning();
    return newCategory;
  }

  async updateAchievementCategory(id: number, category: Partial<InsertAchievementCategory>): Promise<AchievementCategory> {
    const [updatedCategory] = await db
      .update(achievementCategories)
      .set({ ...category, updatedAt: new Date() })
      .where(eq(achievementCategories.id, id))
      .returning();
    return updatedCategory;
  }

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

  // Achievements
  async getAchievements(): Promise<Achievement[]> {
    return await db.select().from(achievements)
      .where(eq(achievements.isActive, true))
      .orderBy(achievements.rarity);
  }

  async getAchievementsByCategory(categoryId: number): Promise<Achievement[]> {
    return await db.select().from(achievements)
      .where(and(
        eq(achievements.isActive, true),
        eq(achievements.categoryId, categoryId)
      ))
      .orderBy(achievements.difficulty, achievements.displayOrder);
  }

  async getAchievementsByRole(role: string): Promise<Achievement[]> {
    return await db.select().from(achievements)
      .where(and(
        eq(achievements.isActive, true),
        eq(achievements.targetRole, role)
      ))
      .orderBy(achievements.difficulty, achievements.displayOrder);
  }

  async createAchievement(achievement: InsertAchievement): Promise<Achievement> {
    const [newAchievement] = await db.insert(achievements).values(achievement).returning();
    return newAchievement;
  }

  async updateAchievement(id: number, achievement: Partial<InsertAchievement>): Promise<Achievement> {
    const [updatedAchievement] = await db
      .update(achievements)
      .set({ ...achievement, updatedAt: new Date() })
      .where(eq(achievements.id, id))
      .returning();
    return updatedAchievement;
  }

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

  // User Achievements
  async getUserAchievements(userId: string): Promise<UserAchievement[]> {
    return await db.select().from(userAchievements)
      .where(eq(userAchievements.userId, userId))
      .orderBy(desc(userAchievements.startedAt));
  }

  async getUserAchievement(userId: string, achievementId: number): Promise<UserAchievement | undefined> {
    const [userAchievement] = await db.select().from(userAchievements)
      .where(and(
        eq(userAchievements.userId, userId),
        eq(userAchievements.achievementId, achievementId)
      ));
    return userAchievement;
  }

  async createUserAchievement(userAchievement: InsertUserAchievement): Promise<UserAchievement> {
    const [newUserAchievement] = await db.insert(userAchievements).values(userAchievement).returning();
    return newUserAchievement;
  }

  async updateUserAchievement(id: number, progress: Partial<InsertUserAchievement>): Promise<UserAchievement> {
    const [updatedUserAchievement] = await db
      .update(userAchievements)
      .set({ ...progress, updatedAt: new Date() })
      .where(eq(userAchievements.id, id))
      .returning();
    return updatedUserAchievement;
  }

  async completeUserAchievement(userId: string, achievementId: number): Promise<UserAchievement> {
    const [completedAchievement] = await db
      .update(userAchievements)
      .set({ 
        isCompleted: true, 
        completedAt: new Date(),
        updatedAt: new Date()
      })
      .where(and(
        eq(userAchievements.userId, userId),
        eq(userAchievements.achievementId, achievementId)
      ))
      .returning();
    return completedAchievement;
  }

  // User Points
  async getUserPoints(userId: string): Promise<UserPoints | undefined> {
    const [userPoints] = await db.select().from(userPoints)
      .where(eq(userPoints.userId, userId));
    return userPoints;
  }

  async createUserPoints(userPointsData: InsertUserPoints): Promise<UserPoints> {
    const [newUserPoints] = await db.insert(userPoints).values(userPointsData).returning();
    return newUserPoints;
  }

  async updateUserPoints(userId: string, points: Partial<InsertUserPoints>): Promise<UserPoints> {
    const [updatedUserPoints] = await db
      .update(userPoints)
      .set({ ...points, updatedAt: new Date() })
      .where(eq(userPoints.userId, userId))
      .returning();
    return updatedUserPoints;
  }

  async addPointsToUser(userId: string, points: number, reason: string, sourceType: string, sourceId?: number): Promise<UserPoints> {
    // First, get or create user points record
    let userPointsRecord = await this.getUserPoints(userId);
    
    if (!userPointsRecord) {
      userPointsRecord = await this.createUserPoints({
        userId,
        totalPoints: 0,
        lifetimePoints: 0,
        currentLevel: 1,
        pointsToNextLevel: 100
      });
    }

    // Update user points
    const newTotalPoints = userPointsRecord.totalPoints + points;
    const newLifetimePoints = userPointsRecord.lifetimePoints + points;
    
    // Calculate new level (simple: every 100 points = 1 level)
    const newLevel = Math.floor(newTotalPoints / 100) + 1;
    const pointsToNextLevel = (newLevel * 100) - newTotalPoints;

    const updatedUserPoints = await this.updateUserPoints(userId, {
      totalPoints: newTotalPoints,
      lifetimePoints: newLifetimePoints,
      currentLevel: newLevel,
      pointsToNextLevel
    });

    // Create point transaction record
    await this.createPointTransaction({
      userId,
      points,
      reason,
      sourceType,
      sourceId,
      earnedAt: new Date()
    });

    return updatedUserPoints;
  }

  // Point Transactions
  async getPointTransactions(userId: string): Promise<PointTransaction[]> {
    return await db.select().from(pointTransactions)
      .where(eq(pointTransactions.userId, userId))
      .orderBy(desc(pointTransactions.earnedAt));
  }

  async createPointTransaction(transaction: InsertPointTransaction): Promise<PointTransaction> {
    const [newTransaction] = await db.insert(pointTransactions).values(transaction).returning();
    return newTransaction;
  }

  // Leaderboards
  async getLeaderboards(): Promise<Leaderboard[]> {
    return await db.select().from(leaderboards)
      .where(eq(leaderboards.isActive, true))
      .orderBy(leaderboards.displayOrder);
  }

  async getLeaderboard(id: number): Promise<Leaderboard | undefined> {
    const [leaderboard] = await db.select().from(leaderboards)
      .where(eq(leaderboards.id, id));
    return leaderboard;
  }

  async createLeaderboard(leaderboard: InsertLeaderboard): Promise<Leaderboard> {
    const [newLeaderboard] = await db.insert(leaderboards).values(leaderboard).returning();
    return newLeaderboard;
  }

  async updateLeaderboard(id: number, leaderboard: Partial<InsertLeaderboard>): Promise<Leaderboard> {
    const [updatedLeaderboard] = await db
      .update(leaderboards)
      .set({ ...leaderboard, updatedAt: new Date() })
      .where(eq(leaderboards.id, id))
      .returning();
    return updatedLeaderboard;
  }

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

  // Leaderboard Entries
  async getLeaderboardEntries(leaderboardId: number): Promise<LeaderboardEntry[]> {
    return await db.select().from(leaderboardEntries)
      .where(eq(leaderboardEntries.leaderboardId, leaderboardId))
      .orderBy(desc(leaderboardEntries.score));
  }

  async createLeaderboardEntry(entry: InsertLeaderboardEntry): Promise<LeaderboardEntry> {
    const [newEntry] = await db.insert(leaderboardEntries).values(entry).returning();
    return newEntry;
  }

  async updateLeaderboardEntry(id: number, entry: Partial<InsertLeaderboardEntry>): Promise<LeaderboardEntry> {
    const [updatedEntry] = await db
      .update(leaderboardEntries)
      .set({ ...entry, updatedAt: new Date() })
      .where(eq(leaderboardEntries.id, id))
      .returning();
    return updatedEntry;
  }

  async refreshLeaderboard(leaderboardId: number): Promise<void> {
    // Implementation would depend on the specific leaderboard type
    // For now, we'll just log the refresh
    console.log(`Refreshing leaderboard ${leaderboardId}`);
  }

  // Quick Reaction Coaching Feedback System Implementation
  async getCoachingFeedbackReactions(): Promise<CoachingFeedbackReaction[]> {
    return await db.select().from(coachingFeedbackReactions)
      .orderBy(coachingFeedbackReactions.sortOrder, coachingFeedbackReactions.category);
  }

  async getActiveCoachingFeedbackReactions(): Promise<CoachingFeedbackReaction[]> {
    return await db.select().from(coachingFeedbackReactions)
      .where(eq(coachingFeedbackReactions.isActive, true))
      .orderBy(coachingFeedbackReactions.sortOrder, coachingFeedbackReactions.category);
  }

  async createCoachingFeedbackReaction(reaction: InsertCoachingFeedbackReaction): Promise<CoachingFeedbackReaction> {
    const [newReaction] = await db.insert(coachingFeedbackReactions).values(reaction).returning();
    return newReaction;
  }

  async updateCoachingFeedbackReaction(id: number, reaction: Partial<InsertCoachingFeedbackReaction>): Promise<CoachingFeedbackReaction> {
    const [updatedReaction] = await db
      .update(coachingFeedbackReactions)
      .set(reaction)
      .where(eq(coachingFeedbackReactions.id, id))
      .returning();
    return updatedReaction;
  }

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

  async getPlayerFeedbackLogs(playerId: number): Promise<(PlayerFeedbackLog & { reaction: CoachingFeedbackReaction; coach: User })[]> {
    return await db.select({
      ...playerFeedbackLogs,
      reaction: coachingFeedbackReactions,
      coach: users,
    })
    .from(playerFeedbackLogs)
    .innerJoin(coachingFeedbackReactions, eq(playerFeedbackLogs.reactionId, coachingFeedbackReactions.id))
    .innerJoin(users, eq(playerFeedbackLogs.coachId, users.id))
    .where(eq(playerFeedbackLogs.playerId, playerId))
    .orderBy(desc(playerFeedbackLogs.timestamp)) as any;
  }

  async getCoachFeedbackLogs(coachId: string): Promise<(PlayerFeedbackLog & { reaction: CoachingFeedbackReaction; player: Player })[]> {
    return await db.select({
      ...playerFeedbackLogs,
      reaction: coachingFeedbackReactions,
      player: players,
    })
    .from(playerFeedbackLogs)
    .innerJoin(coachingFeedbackReactions, eq(playerFeedbackLogs.reactionId, coachingFeedbackReactions.id))
    .innerJoin(players, eq(playerFeedbackLogs.playerId, players.id))
    .where(eq(playerFeedbackLogs.coachId, coachId))
    .orderBy(desc(playerFeedbackLogs.timestamp)) as any;
  }

  async getSessionFeedbackLogs(sessionId: number, sessionType: string): Promise<(PlayerFeedbackLog & { reaction: CoachingFeedbackReaction; player: Player; coach: User })[]> {
    return await db.select({
      ...playerFeedbackLogs,
      reaction: coachingFeedbackReactions,
      player: players,
      coach: users,
    })
    .from(playerFeedbackLogs)
    .innerJoin(coachingFeedbackReactions, eq(playerFeedbackLogs.reactionId, coachingFeedbackReactions.id))
    .innerJoin(players, eq(playerFeedbackLogs.playerId, players.id))
    .innerJoin(users, eq(playerFeedbackLogs.coachId, users.id))
    .where(and(
      eq(playerFeedbackLogs.sessionId, sessionId),
      eq(playerFeedbackLogs.sessionType, sessionType)
    ))
    .orderBy(desc(playerFeedbackLogs.timestamp)) as any;
  }

  async createPlayerFeedbackLog(log: InsertPlayerFeedbackLog): Promise<PlayerFeedbackLog> {
    const [newLog] = await db.insert(playerFeedbackLogs).values(log).returning();
    return newLog;
  }

  async getFeedbackLogsByDateRange(startDate: Date, endDate: Date): Promise<(PlayerFeedbackLog & { reaction: CoachingFeedbackReaction; player: Player; coach: User })[]> {
    return await db.select({
      ...playerFeedbackLogs,
      reaction: coachingFeedbackReactions,
      player: players,
      coach: users,
    })
    .from(playerFeedbackLogs)
    .innerJoin(coachingFeedbackReactions, eq(playerFeedbackLogs.reactionId, coachingFeedbackReactions.id))
    .innerJoin(players, eq(playerFeedbackLogs.playerId, players.id))
    .innerJoin(users, eq(playerFeedbackLogs.coachId, users.id))
    .where(and(
      gte(playerFeedbackLogs.timestamp, startDate),
      lte(playerFeedbackLogs.timestamp, endDate)
    ))
    .orderBy(desc(playerFeedbackLogs.timestamp)) as any;
  }

  async getFeedbackSessions(): Promise<(FeedbackSession & { team?: Team; coach: User })[]> {
    return await db.select({
      ...feedbackSessions,
      team: teams,
      coach: users,
    })
    .from(feedbackSessions)
    .leftJoin(teams, eq(feedbackSessions.teamId, teams.id))
    .innerJoin(users, eq(feedbackSessions.coachId, users.id))
    .orderBy(desc(feedbackSessions.startTime)) as any;
  }

  async getActiveFeedbackSessions(coachId?: string): Promise<(FeedbackSession & { team?: Team; coach: User })[]> {
    const whereConditions = [eq(feedbackSessions.status, 'active')];
    if (coachId) {
      whereConditions.push(eq(feedbackSessions.coachId, coachId));
    }

    return await db.select({
      ...feedbackSessions,
      team: teams,
      coach: users,
    })
    .from(feedbackSessions)
    .leftJoin(teams, eq(feedbackSessions.teamId, teams.id))
    .innerJoin(users, eq(feedbackSessions.coachId, users.id))
    .where(and(...whereConditions))
    .orderBy(desc(feedbackSessions.startTime)) as any;
  }

  async getFeedbackSession(id: number): Promise<(FeedbackSession & { team?: Team; coach: User }) | undefined> {
    const [session] = await db.select({
      ...feedbackSessions,
      team: teams,
      coach: users,
    })
    .from(feedbackSessions)
    .leftJoin(teams, eq(feedbackSessions.teamId, teams.id))
    .innerJoin(users, eq(feedbackSessions.coachId, users.id))
    .where(eq(feedbackSessions.id, id)) as any;
    
    return session;
  }

  async createFeedbackSession(session: InsertFeedbackSession): Promise<FeedbackSession> {
    const [newSession] = await db.insert(feedbackSessions).values(session).returning();
    return newSession;
  }

  async updateFeedbackSession(id: number, session: Partial<InsertFeedbackSession>): Promise<FeedbackSession> {
    const [updatedSession] = await db
      .update(feedbackSessions)
      .set({ ...session, updatedAt: new Date() })
      .where(eq(feedbackSessions.id, id))
      .returning();
    return updatedSession;
  }

  async endFeedbackSession(id: number): Promise<FeedbackSession> {
    const [endedSession] = await db
      .update(feedbackSessions)
      .set({ 
        status: 'completed',
        endTime: new Date(),
        updatedAt: new Date()
      })
      .where(eq(feedbackSessions.id, id))
      .returning();
    return endedSession;
  }

  async getFeedbackSessionStats(sessionId: number): Promise<{
    totalFeedbacks: number;
    feedbacksByCategory: { category: string; count: number }[];
    feedbacksByPlayer: { playerId: number; playerName: string; count: number }[];
    sessionDuration: number | null;
  }> {
    // Get total feedback count
    const [totalResult] = await db.select({ count: count() })
      .from(playerFeedbackLogs)
      .where(eq(playerFeedbackLogs.sessionId, sessionId));

    // Get feedbacks by category
    const categoryStats = await db.select({
      category: coachingFeedbackReactions.category,
      count: count()
    })
    .from(playerFeedbackLogs)
    .innerJoin(coachingFeedbackReactions, eq(playerFeedbackLogs.reactionId, coachingFeedbackReactions.id))
    .where(eq(playerFeedbackLogs.sessionId, sessionId))
    .groupBy(coachingFeedbackReactions.category);

    // Get feedbacks by player
    const playerStats = await db.select({
      playerId: players.id,
      playerName: sql<string>`${players.firstName} || ' ' || ${players.lastName}`,
      count: count()
    })
    .from(playerFeedbackLogs)
    .innerJoin(players, eq(playerFeedbackLogs.playerId, players.id))
    .where(eq(playerFeedbackLogs.sessionId, sessionId))
    .groupBy(players.id, players.firstName, players.lastName);

    // Get session duration
    const [session] = await db.select({
      startTime: feedbackSessions.startTime,
      endTime: feedbackSessions.endTime
    })
    .from(feedbackSessions)
    .where(eq(feedbackSessions.id, sessionId));

    const sessionDuration = session?.endTime && session?.startTime
      ? Math.floor((session.endTime.getTime() - session.startTime.getTime()) / 1000 / 60) // minutes
      : null;

    return {
      totalFeedbacks: totalResult.count,
      feedbacksByCategory: categoryStats as any,
      feedbacksByPlayer: playerStats as any,
      sessionDuration
    };
  }

  // Subscription Management Implementation

  // Membership Plans
  async getMembershipPlans(): Promise<MembershipPlan[]> {
    return await db.select().from(membershipPlans)
      .orderBy(membershipPlans.price);
  }

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

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

  async createMembershipPlan(plan: InsertMembershipPlan): Promise<MembershipPlan> {
    const [newPlan] = await db.insert(membershipPlans).values(plan).returning();
    return newPlan;
  }

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

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

  // Subscriptions
  async getSubscriptions(): Promise<(Subscription & { plan: MembershipPlan; user: User })[]> {
    return await db.select()
      .from(subscriptions)
      .leftJoin(membershipPlans, eq(subscriptions.planId, membershipPlans.id))
      .leftJoin(users, eq(subscriptions.userId, users.id))
      .orderBy(desc(subscriptions.createdAt)) as any;
  }

  async getUserSubscription(userId: string): Promise<(Subscription & { plan: MembershipPlan }) | undefined> {
    const [subscription] = await db.select()
      .from(subscriptions)
      .leftJoin(membershipPlans, eq(subscriptions.planId, membershipPlans.id))
      .where(eq(subscriptions.userId, userId))
      .orderBy(desc(subscriptions.createdAt))
      .limit(1) as any;
    return subscription;
  }

  async getSubscription(id: number): Promise<(Subscription & { plan: MembershipPlan; user: User }) | undefined> {
    const [subscription] = await db.select()
      .from(subscriptions)
      .leftJoin(membershipPlans, eq(subscriptions.planId, membershipPlans.id))
      .leftJoin(users, eq(subscriptions.userId, users.id))
      .where(eq(subscriptions.id, id)) as any;
    return subscription;
  }

  async createSubscription(subscription: InsertSubscription): Promise<Subscription> {
    const [newSubscription] = await db.insert(subscriptions).values(subscription).returning();
    
    // Log subscription event
    await this.logSubscriptionEvent(
      newSubscription.id,
      "created",
      "Subscription created",
      { planId: subscription.planId, status: subscription.status }
    );
    
    return newSubscription;
  }

  async updateSubscription(id: number, subscription: Partial<InsertSubscription>): Promise<Subscription> {
    const [updatedSubscription] = await db.update(subscriptions)
      .set({ ...subscription, updatedAt: new Date() })
      .where(eq(subscriptions.id, id))
      .returning();
    
    // Log subscription event
    await this.logSubscriptionEvent(
      id,
      "updated",
      "Subscription updated",
      subscription
    );
    
    return updatedSubscription;
  }

  async cancelSubscription(id: number, reason?: string): Promise<Subscription> {
    const [cancelledSubscription] = await db.update(subscriptions)
      .set({
        status: "cancelled",
        cancelledAt: new Date(),
        cancelReason: reason,
        autoRenew: false,
        updatedAt: new Date(),
      })
      .where(eq(subscriptions.id, id))
      .returning();
    
    // Log subscription event
    await this.logSubscriptionEvent(
      id,
      "cancelled",
      `Subscription cancelled${reason ? `: ${reason}` : ""}`,
      { reason }
    );
    
    return cancelledSubscription;
  }

  async suspendSubscription(id: number, reason: string): Promise<Subscription> {
    const [suspendedSubscription] = await db.update(subscriptions)
      .set({
        status: "suspended",
        updatedAt: new Date(),
      })
      .where(eq(subscriptions.id, id))
      .returning();
    
    // Log subscription event
    await this.logSubscriptionEvent(
      id,
      "suspended",
      `Subscription suspended: ${reason}`,
      { reason }
    );
    
    return suspendedSubscription;
  }

  async reactivateSubscription(id: number): Promise<Subscription> {
    const [reactivatedSubscription] = await db.update(subscriptions)
      .set({
        status: "active",
        updatedAt: new Date(),
      })
      .where(eq(subscriptions.id, id))
      .returning();
    
    // Log subscription event
    await this.logSubscriptionEvent(
      id,
      "reactivated",
      "Subscription reactivated"
    );
    
    return reactivatedSubscription;
  }

  async getExpiringSubscriptions(days: number): Promise<(Subscription & { plan: MembershipPlan; user: User })[]> {
    const expirationDate = new Date();
    expirationDate.setDate(expirationDate.getDate() + days);
    
    return await db.select()
      .from(subscriptions)
      .leftJoin(membershipPlans, eq(subscriptions.planId, membershipPlans.id))
      .leftJoin(users, eq(subscriptions.userId, users.id))
      .where(and(
        eq(subscriptions.status, "active"),
        lte(subscriptions.nextBillingDate, expirationDate)
      )) as any;
  }

  async getOverdueSubscriptions(): Promise<(Subscription & { plan: MembershipPlan; user: User })[]> {
    const now = new Date();
    
    return await db.select()
      .from(subscriptions)
      .leftJoin(membershipPlans, eq(subscriptions.planId, membershipPlans.id))
      .leftJoin(users, eq(subscriptions.userId, users.id))
      .where(and(
        eq(subscriptions.status, "active"),
        lt(subscriptions.nextBillingDate, now)
      )) as any;
  }

  // Payments
  async getPayments(): Promise<(Payment & { subscription: Subscription; user: User })[]> {
    return await db.select()
      .from(payments)
      .leftJoin(subscriptions, eq(payments.subscriptionId, subscriptions.id))
      .leftJoin(users, eq(payments.userId, users.id))
      .orderBy(desc(payments.createdAt)) as any;
  }

  async getUserPayments(userId: string): Promise<(Payment & { subscription: Subscription })[]> {
    return await db.select()
      .from(payments)
      .leftJoin(subscriptions, eq(payments.subscriptionId, subscriptions.id))
      .where(eq(payments.userId, userId))
      .orderBy(desc(payments.createdAt)) as any;
  }

  async getSubscriptionPayments(subscriptionId: number): Promise<Payment[]> {
    return await db.select().from(payments)
      .where(eq(payments.subscriptionId, subscriptionId))
      .orderBy(desc(payments.createdAt));
  }

  async getPayment(id: number): Promise<(Payment & { subscription: Subscription; user: User }) | undefined> {
    const [payment] = await db.select()
      .from(payments)
      .leftJoin(subscriptions, eq(payments.subscriptionId, subscriptions.id))
      .leftJoin(users, eq(payments.userId, users.id))
      .where(eq(payments.id, id)) as any;
    return payment;
  }

  async createPayment(payment: InsertPayment): Promise<Payment> {
    const [newPayment] = await db.insert(payments).values(payment).returning();
    return newPayment;
  }

  async updatePayment(id: number, payment: Partial<InsertPayment>): Promise<Payment> {
    const [updatedPayment] = await db.update(payments)
      .set({ ...payment, updatedAt: new Date() })
      .where(eq(payments.id, id))
      .returning();
    return updatedPayment;
  }

  async markPaymentCompleted(id: number, paidAt: Date, stripeData?: any): Promise<Payment> {
    const [completedPayment] = await db.update(payments)
      .set({
        status: "completed",
        paidAt,
        metadata: stripeData,
        updatedAt: new Date(),
      })
      .where(eq(payments.id, id))
      .returning();
    return completedPayment;
  }

  async markPaymentFailed(id: number, reason: string): Promise<Payment> {
    const [failedPayment] = await db.update(payments)
      .set({
        status: "failed",
        failureReason: reason,
        updatedAt: new Date(),
      })
      .where(eq(payments.id, id))
      .returning();
    return failedPayment;
  }

  async processRefund(id: number, amount: number, reason: string): Promise<Payment> {
    const [refundedPayment] = await db.update(payments)
      .set({
        status: "refunded",
        refundAmount: amount,
        refundedAt: new Date(),
        failureReason: reason,
        updatedAt: new Date(),
      })
      .where(eq(payments.id, id))
      .returning();
    return refundedPayment;
  }

  // Payment Activities
  async getPaymentActivities(): Promise<(PaymentActivity & { subscription: Subscription; user: User })[]> {
    return await db.select()
      .from(paymentActivities)
      .leftJoin(subscriptions, eq(paymentActivities.subscriptionId, subscriptions.id))
      .leftJoin(users, eq(paymentActivities.userId, users.id))
      .orderBy(desc(paymentActivities.createdAt)) as any;
  }

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

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

  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 scheduleAutomaticPayment(subscriptionId: number, amount: number, scheduledDate: Date): Promise<PaymentActivity> {
    const subscription = await db.select().from(subscriptions).where(eq(subscriptions.id, subscriptionId)).limit(1);
    if (!subscription[0]) throw new Error("Subscription not found");

    const activity = await this.createPaymentActivity({
      subscriptionId,
      userId: subscription[0].userId,
      activityType: "auto_payment_attempt",
      status: "pending",
      amount,
      scheduledDate,
      metadata: { autoPayment: true },
    });

    return activity;
  }

  async processScheduledPayments(): Promise<PaymentActivity[]> {
    const now = new Date();
    const pendingActivities = await db.select()
      .from(paymentActivities)
      .where(and(
        eq(paymentActivities.status, "pending"),
        eq(paymentActivities.activityType, "auto_payment_attempt"),
        lte(paymentActivities.scheduledDate, now)
      ));

    const processedActivities = [];
    for (const activity of pendingActivities) {
      // Here you would integrate with payment processor (Stripe)
      // For now, we'll mark as completed
      const updated = await this.updatePaymentActivity(activity.id, {
        status: "completed",
        completedAt: new Date(),
      });
      processedActivities.push(updated);
    }

    return processedActivities;
  }

  // Payment Reminders
  async getPaymentReminders(): Promise<(PaymentReminder & { subscription: Subscription; user: User })[]> {
    return await db.select()
      .from(paymentReminders)
      .leftJoin(subscriptions, eq(paymentReminders.subscriptionId, subscriptions.id))
      .leftJoin(users, eq(paymentReminders.userId, users.id))
      .orderBy(desc(paymentReminders.createdAt)) as any;
  }

  async getUserPaymentReminders(userId: string): Promise<(PaymentReminder & { subscription: Subscription })[]> {
    return await db.select()
      .from(paymentReminders)
      .leftJoin(subscriptions, eq(paymentReminders.subscriptionId, subscriptions.id))
      .where(eq(paymentReminders.userId, userId))
      .orderBy(desc(paymentReminders.createdAt)) as any;
  }

  async createPaymentReminder(reminder: InsertPaymentReminder): Promise<PaymentReminder> {
    const [newReminder] = await db.insert(paymentReminders).values(reminder).returning();
    return newReminder;
  }

  async markReminderSent(id: number): Promise<PaymentReminder> {
    const [sentReminder] = await db.update(paymentReminders)
      .set({
        status: "sent",
        sentAt: new Date(),
      })
      .where(eq(paymentReminders.id, id))
      .returning();
    return sentReminder;
  }

  async markReminderFailed(id: number): Promise<PaymentReminder> {
    const [failedReminder] = await db.update(paymentReminders)
      .set({
        status: "failed",
        retryCount: sql`${paymentReminders.retryCount} + 1`,
      })
      .where(eq(paymentReminders.id, id))
      .returning();
    return failedReminder;
  }

  async schedulePaymentReminders(subscriptionId: number): Promise<PaymentReminder[]> {
    const subscription = await db.select().from(subscriptions).where(eq(subscriptions.id, subscriptionId)).limit(1);
    if (!subscription[0] || !subscription[0].nextBillingDate) return [];

    const settings = await this.getUserPaymentSettings(subscription[0].userId);
    const reminderDays = settings?.reminderDaysBefore || 3;

    const reminderDate = new Date(subscription[0].nextBillingDate);
    reminderDate.setDate(reminderDate.getDate() - reminderDays);

    const reminders = [];

    // Schedule email reminder
    if (settings?.emailNotifications !== false) {
      const emailReminder = await this.createPaymentReminder({
        subscriptionId,
        userId: subscription[0].userId,
        reminderType: "upcoming_payment",
        scheduledDate: reminderDate,
        notificationMethod: "email",
        content: {
          subject: "Payment Due Soon",
          message: `Your subscription payment of is due on ${subscription[0].nextBillingDate}`,
        },
      });
      reminders.push(emailReminder);
    }

    return reminders;
  }

  async getPendingReminders(): Promise<(PaymentReminder & { subscription: Subscription; user: User })[]> {
    const now = new Date();
    return await db.select()
      .from(paymentReminders)
      .leftJoin(subscriptions, eq(paymentReminders.subscriptionId, subscriptions.id))
      .leftJoin(users, eq(paymentReminders.userId, users.id))
      .where(and(
        eq(paymentReminders.status, "pending"),
        lte(paymentReminders.scheduledDate, now)
      )) as any;
  }

  // Subscription Events
  async getSubscriptionEvents(subscriptionId: number): Promise<SubscriptionEvent[]> {
    return await db.select().from(subscriptionEvents)
      .where(eq(subscriptionEvents.subscriptionId, subscriptionId))
      .orderBy(desc(subscriptionEvents.createdAt));
  }

  async createSubscriptionEvent(event: InsertSubscriptionEvent): Promise<SubscriptionEvent> {
    const [newEvent] = await db.insert(subscriptionEvents).values(event).returning();
    return newEvent;
  }

  async logSubscriptionEvent(subscriptionId: number, eventType: string, description: string, eventData?: any): Promise<SubscriptionEvent> {
    const subscription = await db.select().from(subscriptions).where(eq(subscriptions.id, subscriptionId)).limit(1);
    if (!subscription[0]) throw new Error("Subscription not found");

    return await this.createSubscriptionEvent({
      subscriptionId,
      userId: subscription[0].userId,
      eventType,
      description,
      eventData,
    });
  }

  // Payment Notification Settings
  async getUserPaymentSettings(userId: string): Promise<PaymentNotificationSettings | undefined> {
    const [settings] = await db.select().from(paymentNotificationSettings)
      .where(eq(paymentNotificationSettings.userId, userId));
    return settings;
  }

  async createPaymentSettings(settings: InsertPaymentNotificationSettings): Promise<PaymentNotificationSettings> {
    const [newSettings] = await db.insert(paymentNotificationSettings).values(settings).returning();
    return newSettings;
  }

  async updatePaymentSettings(userId: string, settings: Partial<InsertPaymentNotificationSettings>): Promise<PaymentNotificationSettings> {
    const [updatedSettings] = await db.update(paymentNotificationSettings)
      .set({ ...settings, updatedAt: new Date() })
      .where(eq(paymentNotificationSettings.userId, userId))
      .returning();
    return updatedSettings;
  }

  // Subscription Analytics
  async getSubscriptionStats(): Promise<{
    totalSubscriptions: number;
    activeSubscriptions: number;
    trialSubscriptions: number;
    cancelledSubscriptions: number;
    suspendedSubscriptions: number;
    monthlyRevenue: number;
    totalRevenue: number;
    churnRate: number;
    avgSubscriptionValue: number;
  }> {
    const [stats] = await db.select({
      totalSubscriptions: count(),
      activeSubscriptions: count(sql`CASE WHEN status = 'active' THEN 1 END`),
      trialSubscriptions: count(sql`CASE WHEN status = 'trial' THEN 1 END`),
      cancelledSubscriptions: count(sql`CASE WHEN status = 'cancelled' THEN 1 END`),
      suspendedSubscriptions: count(sql`CASE WHEN status = 'suspended' THEN 1 END`),
    }).from(subscriptions);

    const [revenueStats] = await db.select({
      totalRevenue: sql`COALESCE(SUM(amount), 0)`,
      monthlyRevenue: sql`COALESCE(SUM(CASE WHEN paid_at >= DATE_TRUNC('month', CURRENT_DATE) THEN amount ELSE 0 END), 0)`,
    }).from(payments).where(eq(payments.status, "completed"));

    return {
      ...stats,
      monthlyRevenue: Number(revenueStats.monthlyRevenue) / 100, // Convert from cents
      totalRevenue: Number(revenueStats.totalRevenue) / 100, // Convert from cents
      churnRate: stats.totalSubscriptions > 0 ? (stats.cancelledSubscriptions / stats.totalSubscriptions) * 100 : 0,
      avgSubscriptionValue: stats.activeSubscriptions > 0 ? Number(revenueStats.totalRevenue) / 100 / stats.activeSubscriptions : 0,
    };
  }

  // Automated Tasks
  async processOverduePayments(): Promise<void> {
    const overdueSubscriptions = await this.getOverdueSubscriptions();
    
    for (const subscription of overdueSubscriptions) {
      await this.scheduleAutomaticPayment(
        subscription.id,
        subscription.plan?.price || 0,
        new Date()
      );
    }
  }

  async sendPaymentReminders(): Promise<void> {
    const pendingReminders = await this.getPendingReminders();
    
    for (const reminder of pendingReminders) {
      try {
        // Here you would integrate with email/SMS service
        await this.markReminderSent(reminder.id);
      } catch (error) {
        await this.markReminderFailed(reminder.id);
      }
    }
  }

  async suspendOverdueAccounts(): Promise<void> {
    const overdueSubscriptions = await this.getOverdueSubscriptions();
    const gracePeriodDays = 7; // Grace period before suspension
    
    for (const subscription of overdueSubscriptions) {
      if (subscription.nextBillingDate) {
        const daysPastDue = Math.floor(
          (Date.now() - subscription.nextBillingDate.getTime()) / (1000 * 60 * 60 * 24)
        );
        
        if (daysPastDue >= gracePeriodDays) {
          await this.suspendSubscription(subscription.id, "Payment overdue");
        }
      }
    }
  }

  async processTrialExpirations(): Promise<void> {
    const expiringTrials = await db.select()
      .from(subscriptions)
      .where(and(
        eq(subscriptions.status, "trial"),
        lte(subscriptions.trialEndDate, new Date())
      ));
    
    for (const subscription of expiringTrials) {
      await this.updateSubscription(subscription.id, {
        status: "active",
      });
    }
  }

  // Superadmin methods for organization and module management
  async getAllOrganizations(): Promise<Organization[]> {
    return await db.select().from(organizations).orderBy(organizations.name);
  }

  async getAllModules(): Promise<Module[]> {
    return await db.select().from(modules).orderBy(modules.category, modules.displayName);
  }

  async getAllSubscriptionPlans(): Promise<SubscriptionPlan[]> {
    return await db.select().from(subscriptionPlans).orderBy(subscriptionPlans.tier, subscriptionPlans.price);
  }

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

  async updateOrganizationSubscription(orgId: number, updateData: any): Promise<Organization> {
    const [organization] = await db
      .update(organizations)
      .set({
        subscriptionTier: updateData.subscriptionTier,
        subscriptionStatus: updateData.subscriptionStatus,
        updatedAt: new Date(),
      })
      .where(eq(organizations.id, orgId))
      .returning();
    return organization;
  }

  async updateOrganizationModulePermission(
    orgId: number,
    moduleId: number,
    enabled: boolean,
    userId: string
  ): Promise<OrganizationModulePermission> {
    const [permission] = await db
      .insert(organizationModulePermissions)
      .values({
        organizationId: orgId,
        moduleId,
        isEnabled: enabled,
        enabledBy: userId,
      })
      .onConflictDoUpdate({
        target: [organizationModulePermissions.organizationId, organizationModulePermissions.moduleId],
        set: {
          isEnabled: enabled,
          enabledBy: userId,
          updatedAt: new Date(),
        },
      })
      .returning();
    return permission;
  }

  async createModule(moduleData: InsertModule): Promise<Module> {
    const [module] = await db
      .insert(modules)
      .values(moduleData)
      .returning();
    return module;
  }

  async createOrganization(orgData: InsertOrganization): Promise<Organization> {
    const [organization] = await db
      .insert(organizations)
      .values(orgData)
      .returning();
    return organization;
  }

  async checkUserModuleAccess(userId: string, moduleName: string): Promise<boolean> {
    // Get user's organization
    const [user] = await db
      .select({ organizationId: users.organizationId })
      .from(users)
      .where(eq(users.id, userId));

    if (!user?.organizationId) return false;

    // Get module info
    const [module] = await db
      .select()
      .from(modules)
      .where(eq(modules.name, moduleName));

    if (!module) return false;

    // Check organization's permission for this module
    const [permission] = await db
      .select()
      .from(organizationModulePermissions)
      .where(
        and(
          eq(organizationModulePermissions.organizationId, user.organizationId),
          eq(organizationModulePermissions.moduleId, module.id)
        )
      );

    return permission?.isEnabled || false;
  }

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

  async getTeamStaff(teamId: number): Promise<(TeamCoach & { coach: Coach })[]> {
    return await db
      .select()
      .from(teamCoaches)
      .leftJoin(coaches, eq(teamCoaches.coachId, coaches.id))
      .where(eq(teamCoaches.teamId, teamId)) as any;
  }

  async addStaffToTeam(teamId: number, staffData: InsertTeamCoach): Promise<TeamCoach> {
    const [assignment] = await db
      .insert(teamCoaches)
      .values({
        teamId,
        coachId: staffData.coachId,
        role: staffData.role,
        isActive: staffData.isActive ?? true,
      })
      .returning();
    return assignment;
  }

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

  async updateTeamStaff(teamId: number, coachId: number, updates: { role?: string; isActive?: boolean }): Promise<TeamCoach> {
    const [updated] = await db
      .update(teamCoaches)
      .set({
        ...updates,
        updatedAt: new Date(),
      })
      .where(and(eq(teamCoaches.teamId, teamId), eq(teamCoaches.coachId, coachId)))
      .returning();
    return updated;
  }

  // Admin user management operations
  async createUserWithPassword(userData: {
    firstName: string;
    lastName: string;
    email: string;
    username?: string;
    password?: string;
    role?: string;
    organizationId?: number;
    teamId?: number;
  }): Promise<{ user: User; password: string }> {
    const { generatePassword, generateUsername } = await import('./password');
    const bcrypt = await import('bcrypt');
    
    const generatedPassword = userData.password || generatePassword();
    const username = userData.username || generateUsername(userData.firstName, userData.lastName);
    const hashedPassword = await bcrypt.hash(generatedPassword, 10);
    
    const [user] = await db
      .insert(users)
      .values({
        id: crypto.randomUUID(),
        firstName: userData.firstName,
        lastName: userData.lastName,
        email: userData.email,
        username,
        password: hashedPassword,
        role: userData.role || 'player',
        language: 'en',
        timezone: 'UTC',
        authType: 'local',
        status: 'active'
      })
      .returning();
    
    return { user, password: generatedPassword };
  }

  async resetUserPassword(userId: string): Promise<string> {
    const { generatePassword, hashPassword } = await import('./password');
    
    const newPassword = generatePassword();
    const hashedPassword = await hashPassword(newPassword);
    
    await db
      .update(users)
      .set({ 
        password: hashedPassword,
        updatedAt: new Date()
      })
      .where(eq(users.id, userId));
    
    return newPassword;
  }

  async getAllUsersForAdmin(): Promise<User[]> {
    const result = await db
      .select()
      .from(users)
      .orderBy(users.createdAt);
    
    return result;
  }

  async updateUserStatus(userId: string, status: 'active' | 'inactive' | 'suspended'): Promise<User> {
    const [updated] = await db
      .update(users)
      .set({ 
        status,
        updatedAt: new Date()
      })
      .where(eq(users.id, userId))
      .returning();
    return updated;
  }

  async updateUser(userId: string, userData: {
    firstName?: string;
    lastName?: string;
    email?: string;
    role?: string;
    status?: string;
    language?: string;
    timezone?: string;
  }): Promise<User> {
    const updateData = {
      ...userData,
      updatedAt: new Date()
    };

    const [updated] = await db
      .update(users)
      .set(updateData)
      .where(eq(users.id, userId))
      .returning();
    
    return updated;
  }

  // Dashboard widget operations
  async getUserDashboardWidgets(userId: string): Promise<DashboardWidget[]> {
    return await db
      .select()
      .from(dashboardWidgets)
      .where(and(eq(dashboardWidgets.userId, userId), eq(dashboardWidgets.isVisible, true)))
      .orderBy(dashboardWidgets.createdAt);
  }

  async getDashboardWidget(id: number): Promise<DashboardWidget | undefined> {
    const [widget] = await db
      .select()
      .from(dashboardWidgets)
      .where(eq(dashboardWidgets.id, id));
    return widget;
  }

  async createDashboardWidget(widget: InsertDashboardWidget): Promise<DashboardWidget> {
    const [created] = await db
      .insert(dashboardWidgets)
      .values(widget)
      .returning();
    return created;
  }

  async updateDashboardWidget(id: number, widget: Partial<InsertDashboardWidget>): Promise<DashboardWidget> {
    const [updated] = await db
      .update(dashboardWidgets)
      .set({
        ...widget,
        updatedAt: new Date(),
      })
      .where(eq(dashboardWidgets.id, id))
      .returning();
    return updated;
  }

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

  async updateWidgetPosition(id: number, position: any): Promise<DashboardWidget> {
    const [updated] = await db
      .update(dashboardWidgets)
      .set({
        position,
        updatedAt: new Date(),
      })
      .where(eq(dashboardWidgets.id, id))
      .returning();
    return updated;
  }

  // Widget template operations
  async getWidgetTemplates(): Promise<WidgetTemplate[]> {
    return await db
      .select()
      .from(widgetTemplates)
      .where(eq(widgetTemplates.isActive, true))
      .orderBy(widgetTemplates.category, widgetTemplates.name);
  }

  async getWidgetTemplatesByCategory(category: string): Promise<WidgetTemplate[]> {
    return await db
      .select()
      .from(widgetTemplates)
      .where(and(eq(widgetTemplates.category, category), eq(widgetTemplates.isActive, true)))
      .orderBy(widgetTemplates.name);
  }

  async getWidgetTemplate(id: number): Promise<WidgetTemplate | undefined> {
    const [template] = await db
      .select()
      .from(widgetTemplates)
      .where(eq(widgetTemplates.id, id));
    return template;
  }

  async createWidgetTemplate(template: InsertWidgetTemplate): Promise<WidgetTemplate> {
    const [created] = await db
      .insert(widgetTemplates)
      .values(template)
      .returning();
    return created;
  }

  async updateWidgetTemplate(id: number, template: Partial<InsertWidgetTemplate>): Promise<WidgetTemplate> {
    const [updated] = await db
      .update(widgetTemplates)
      .set({
        ...template,
        updatedAt: new Date(),
      })
      .where(eq(widgetTemplates.id, id))
      .returning();
    return updated;
  }

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


}

export const storage = new DatabaseStorage();
