import {
  pgTable,
  text,
  varchar,
  timestamp,
  jsonb,
  index,
  serial,
  integer,
  date,
  boolean,
} from "drizzle-orm/pg-core";
import { relations } from "drizzle-orm";
import { createInsertSchema } from "drizzle-zod";
import { z } from "zod";

// Session storage table for Replit Auth
export const sessions = pgTable(
  "sessions",
  {
    sid: varchar("sid").primaryKey(),
    sess: jsonb("sess").notNull(),
    expire: timestamp("expire").notNull(),
  },
  (table) => [index("IDX_session_expire").on(table.expire)],
);

// Organizations/Associations - primary subscription holders
export const organizations = pgTable("organizations", {
  id: serial("id").primaryKey(),
  name: varchar("name", { length: 255 }).notNull(),
  type: varchar("type", { length: 50 }).notNull(), // "team", "association", "club", "federation"
  sport: varchar("sport", { length: 100 }).notNull(),
  logoUrl: varchar("logo_url"),
  website: varchar("website"),
  email: varchar("email"),
  phone: varchar("phone"),
  address: text("address"),
  description: text("description"),
  foundedYear: integer("founded_year"),
  status: varchar("status", { length: 20 }).default("active").notNull(),
  subscriptionTier: varchar("subscription_tier", { length: 50 }).default("regular").notNull(),
  subscriptionStatus: varchar("subscription_status", { length: 20 }).default("inactive"),
  subscriptionStartDate: timestamp("subscription_start_date"),
  subscriptionEndDate: timestamp("subscription_end_date"),
  stripeCustomerId: varchar("stripe_customer_id"),
  stripeSubscriptionId: varchar("stripe_subscription_id"),
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow(),
});

// User storage table supporting both Replit Auth and local authentication
export const users = pgTable("users", {
  id: varchar("id").primaryKey().notNull(),
  email: varchar("email").unique(),
  firstName: varchar("first_name"),
  lastName: varchar("last_name"),
  profileImageUrl: varchar("profile_image_url"),
  username: varchar("username").unique(), // For local authentication usernames
  password: varchar("password"), // For local authentication
  authType: varchar("auth_type").default("local").notNull(), // 'replit' or 'local'
  role: varchar("role", { length: 50 }).default("user").notNull(), // "superadmin", "org_admin", "coach", "user"
  organizationId: integer("organization_id").references(() => organizations.id),
  isActive: boolean("is_active").default(true).notNull(),
  stripeCustomerId: varchar("stripe_customer_id"),
  stripeSubscriptionId: varchar("stripe_subscription_id"),
  language: varchar("language", { length: 10 }).default("en").notNull(),
  timezone: varchar("timezone", { length: 50 }).default("UTC"),
  status: varchar("status", { length: 20 }).default("active").notNull(),
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow(),
});

// User Club Roles - allows one profile to have multiple roles across different clubs
export const userClubRoles = pgTable("user_club_roles", {
  id: serial("id").primaryKey(),
  userId: varchar("user_id").references(() => users.id).notNull(),
  organizationId: integer("organization_id").references(() => organizations.id),
  teamId: integer("team_id").references(() => teams.id),
  role: varchar("role", { length: 50 }).notNull(), // "admin", "coach", "player", "manager", "viewer"
  permissions: jsonb("permissions"), // Custom permissions for this role
  isActive: boolean("is_active").default(true).notNull(),
  startDate: date("start_date").notNull(),
  endDate: date("end_date"),
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow(),
});

// Password reset tokens table
export const passwordResetTokens = pgTable("password_reset_tokens", {
  id: serial("id").primaryKey(),
  userId: varchar("user_id").references(() => users.id).notNull(),
  token: varchar("token", { length: 255 }).notNull().unique(),
  expiresAt: timestamp("expires_at").notNull(),
  isUsed: boolean("is_used").default(false).notNull(),
  createdAt: timestamp("created_at").defaultNow(),
});



// Teams table - now belongs to organizations
export const teams = pgTable("teams", {
  id: serial("id").primaryKey(),
  name: varchar("name", { length: 255 }).notNull(),
  sport: varchar("sport", { length: 100 }).notNull(),
  logoUrl: varchar("logo_url"),
  status: varchar("status", { length: 20 }).notNull().default("active"),
  description: text("description"),
  league: varchar("league", { length: 255 }),
  homeVenue: varchar("home_venue", { length: 255 }),
  email: varchar("email", { length: 255 }),
  phone: varchar("phone", { length: 20 }),
  address: text("address"),
  foundedYear: integer("founded_year"),
  organizationId: integer("organization_id").references(() => organizations.id).notNull(),
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow(),
});

// Application modules that can be enabled/disabled
export const modules = pgTable("modules", {
  id: serial("id").primaryKey(),
  name: varchar("name", { length: 100 }).notNull().unique(),
  displayName: varchar("display_name", { length: 150 }).notNull(),
  description: text("description"),
  category: varchar("category", { length: 50 }).notNull(), // "core", "analytics", "communication", "advanced"
  isActive: boolean("is_active").default(true).notNull(),
  requiredTier: varchar("required_tier", { length: 50 }).default("regular").notNull(), // "regular", "premium"
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow(),
});

// Organization-specific module permissions (superadmin can enable/disable per org)
export const organizationModulePermissions = pgTable("organization_module_permissions", {
  id: serial("id").primaryKey(),
  organizationId: integer("organization_id").references(() => organizations.id).notNull(),
  moduleId: integer("module_id").references(() => modules.id).notNull(),
  isEnabled: boolean("is_enabled").default(true).notNull(),
  enabledBy: varchar("enabled_by").references(() => users.id), // Who enabled this module
  enabledAt: timestamp("enabled_at").defaultNow(),
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow(),
});

// Subscription plans
export const subscriptionPlans = pgTable("subscription_plans", {
  id: serial("id").primaryKey(),
  name: varchar("name", { length: 100 }).notNull(),
  tier: varchar("tier", { length: 50 }).notNull(), // "regular", "premium"
  price: integer("price").notNull(), // in cents
  currency: varchar("currency", { length: 3 }).default("USD").notNull(),
  interval: varchar("interval", { length: 20 }).notNull(), // "month", "year"
  stripePriceId: varchar("stripe_price_id"),
  features: jsonb("features"), // Array of included features
  maxTeams: integer("max_teams"),
  maxUsers: integer("max_users"),
  isActive: boolean("is_active").default(true).notNull(),
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow(),
});

// Membership fees and subscription tracking
export const membershipFees = pgTable("membership_fees", {
  id: serial("id").primaryKey(),
  organizationId: integer("organization_id").references(() => organizations.id).notNull(),
  userId: varchar("user_id").references(() => users.id),
  feeType: varchar("fee_type", { length: 50 }).notNull(), // "subscription", "membership", "registration", "tournament"
  amount: integer("amount").notNull(), // in cents
  currency: varchar("currency", { length: 3 }).default("USD").notNull(),
  dueDate: timestamp("due_date").notNull(),
  description: text("description"),
  status: varchar("status", { length: 20 }).default("pending").notNull(), // "pending", "paid", "overdue", "cancelled"
  paymentMethod: varchar("payment_method", { length: 50 }), // "stripe", "cash", "bank_transfer"
  recurringInterval: varchar("recurring_interval", { length: 20 }), // "month", "year", "quarter", null for one-time
  nextDueDate: timestamp("next_due_date"),
  gracePeriodDays: integer("grace_period_days").default(7),
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow(),
});

// Payment activities and transactions
export const paymentActivities = pgTable("payment_activities", {
  id: serial("id").primaryKey(),
  membershipFeeId: integer("membership_fee_id").references(() => membershipFees.id).notNull(),
  organizationId: integer("organization_id").references(() => organizations.id).notNull(),
  userId: varchar("user_id").references(() => users.id),
  amount: integer("amount").notNull(), // in cents
  currency: varchar("currency", { length: 3 }).default("USD").notNull(),
  paymentMethod: varchar("payment_method", { length: 50 }).notNull(),
  status: varchar("status", { length: 20 }).notNull(), // "pending", "processing", "completed", "failed", "refunded"
  transactionId: varchar("transaction_id"), // Stripe payment intent ID or similar
  stripeCustomerId: varchar("stripe_customer_id"),
  stripePaymentIntentId: varchar("stripe_payment_intent_id"),
  failureReason: text("failure_reason"),
  processedAt: timestamp("processed_at"),
  refundedAt: timestamp("refunded_at"),
  refundAmount: integer("refund_amount"),
  metadata: jsonb("metadata"), // Additional payment data
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow(),
});

// Payment notifications and reminders
export const paymentNotifications = pgTable("payment_notifications", {
  id: serial("id").primaryKey(),
  membershipFeeId: integer("membership_fee_id").references(() => membershipFees.id).notNull(),
  userId: varchar("user_id").references(() => users.id).notNull(),
  organizationId: integer("organization_id").references(() => organizations.id).notNull(),
  notificationType: varchar("notification_type", { length: 50 }).notNull(), // "reminder", "overdue", "final_notice", "suspension_warning"
  method: varchar("method", { length: 20 }).notNull(), // "email", "sms", "in_app"
  status: varchar("status", { length: 20 }).default("pending").notNull(), // "pending", "sent", "delivered", "failed"
  scheduledFor: timestamp("scheduled_for").notNull(),
  sentAt: timestamp("sent_at"),
  subject: varchar("subject", { length: 255 }),
  message: text("message"),
  emailAddress: varchar("email_address"),
  phoneNumber: varchar("phone_number"),
  failureReason: text("failure_reason"),
  metadata: jsonb("metadata"),
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow(),
});

// Automatic payment schedules
export const automaticPaymentSchedules = pgTable("automatic_payment_schedules", {
  id: serial("id").primaryKey(),
  organizationId: integer("organization_id").references(() => organizations.id).notNull(),
  userId: varchar("user_id").references(() => users.id).notNull(),
  membershipFeeId: integer("membership_fee_id").references(() => membershipFees.id).notNull(),
  stripeCustomerId: varchar("stripe_customer_id").notNull(),
  stripeSubscriptionId: varchar("stripe_subscription_id"),
  stripePriceId: varchar("stripe_price_id"),
  isActive: boolean("is_active").default(true).notNull(),
  nextPaymentDate: timestamp("next_payment_date").notNull(),
  amount: integer("amount").notNull(), // in cents
  currency: varchar("currency", { length: 3 }).default("USD").notNull(),
  interval: varchar("interval", { length: 20 }).notNull(), // "month", "year", "quarter"
  failedAttempts: integer("failed_attempts").default(0),
  maxRetryAttempts: integer("max_retry_attempts").default(3),
  lastPaymentDate: timestamp("last_payment_date"),
  lastFailureDate: timestamp("last_failure_date"),
  lastFailureReason: text("last_failure_reason"),
  suspendedAt: timestamp("suspended_at"),
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow(),
});

// Member status tracking for payment-related suspensions
export const memberStatusHistory = pgTable("member_status_history", {
  id: serial("id").primaryKey(),
  userId: varchar("user_id").references(() => users.id).notNull(),
  organizationId: integer("organization_id").references(() => organizations.id).notNull(),
  previousStatus: varchar("previous_status", { length: 20 }).notNull(),
  newStatus: varchar("new_status", { length: 20 }).notNull(),
  reason: varchar("reason", { length: 100 }).notNull(), // "payment_overdue", "payment_received", "manual_update"
  membershipFeeId: integer("membership_fee_id").references(() => membershipFees.id),
  changedBy: varchar("changed_by").references(() => users.id),
  automaticChange: boolean("automatic_change").default(false).notNull(),
  notes: text("notes"),
  effectiveDate: timestamp("effective_date").defaultNow().notNull(),
  createdAt: timestamp("created_at").defaultNow(),
});

// Coaches table
export const coaches = pgTable("coaches", {
  id: serial("id").primaryKey(),
  firstName: varchar("first_name", { length: 100 }).notNull(),
  lastName: varchar("last_name", { length: 100 }).notNull(),
  email: varchar("email", { length: 255 }).unique().notNull(),
  phone: varchar("phone", { length: 20 }),
  profileImageUrl: varchar("profile_image_url"),
  experience: integer("experience"),
  specialization: varchar("specialization", { length: 100 }),
  status: varchar("status", { length: 20 }).notNull().default("active"),
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow(),
});

// Players table
export const players = pgTable("players", {
  id: serial("id").primaryKey(),
  firstName: varchar("first_name", { length: 100 }).notNull(),
  lastName: varchar("last_name", { length: 100 }).notNull(),
  email: varchar("email", { length: 255 }).unique(),
  phone: varchar("phone", { length: 20 }),
  profileImageUrl: varchar("profile_image_url"),
  dateOfBirth: date("date_of_birth"),
  gender: varchar("gender", { length: 10 }).notNull(),
  position: varchar("position", { length: 100 }),
  jerseyNumber: integer("jersey_number"),
  height: integer("height"), // in cm
  weight: integer("weight"), // in kg
  status: varchar("status", { length: 20 }).notNull().default("active"),
  teamId: integer("team_id").references(() => teams.id),
  emergencyContact: varchar("emergency_contact", { length: 255 }),
  medicalNotes: text("medical_notes"),
  
  // Extended profile fields
  nationality: varchar("nationality", { length: 100 }),
  preferredFoot: varchar("preferred_foot", { length: 10 }), // left, right, both
  previousClubs: jsonb("previous_clubs"), // Array of club names and dates
  dietaryRequirements: text("dietary_requirements"),
  address: text("address"),
  occupation: varchar("occupation", { length: 100 }),
  experienceLevel: varchar("experience_level", { length: 50 }), // beginner, intermediate, advanced, professional
  availability: jsonb("availability"), // Weekly availability schedule
  joinedDate: date("joined_date"),
  contractExpiryDate: date("contract_expiry_date"),
  parentGuardianName: varchar("parent_guardian_name", { length: 255 }), // For youth players
  parentGuardianPhone: varchar("parent_guardian_phone", { length: 20 }),
  schoolName: varchar("school_name", { length: 255 }), // For youth/student players
  emergencyContactRelation: varchar("emergency_contact_relation", { length: 50 }),
  bloodType: varchar("blood_type", { length: 5 }),
  
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow(),
});

// Team-Coach relationship table
export const teamCoaches = pgTable("team_coaches", {
  id: serial("id").primaryKey(),
  teamId: integer("team_id").references(() => teams.id).notNull(),
  coachId: integer("coach_id").references(() => coaches.id).notNull(),
  role: varchar("role", { length: 50 }).notNull().default("head_coach"),
  startDate: date("start_date").notNull(),
  endDate: date("end_date"),
  createdAt: timestamp("created_at").defaultNow(),
});

// Tournament table
export const tournaments = pgTable("tournaments", {
  id: serial("id").primaryKey(),
  name: varchar("name", { length: 255 }).notNull(),
  description: text("description"),
  startDate: timestamp("start_date").notNull(),
  endDate: timestamp("end_date").notNull(),
  location: varchar("location", { length: 255 }),
  status: varchar("status", { length: 50 }).default("upcoming"),
  format: varchar("format", { length: 50 }).default("single-elimination"),
  maxTeams: integer("max_teams").default(16),
  registeredTeams: integer("registered_teams").default(0),
  prize: varchar("prize", { length: 255 }),
  entryFee: integer("entry_fee").default(0),
  organizer: varchar("organizer", { length: 255 }),
  rugbyFormat: varchar("rugby_format", { length: 50 }).default("15s"),
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow(),
});

// Match table
export const matches = pgTable("matches", {
  id: serial("id").primaryKey(),
  tournamentId: integer("tournament_id").notNull().references(() => tournaments.id),
  round: varchar("round", { length: 100 }),
  team1Id: integer("team1_id").references(() => teams.id),
  team2Id: integer("team2_id").references(() => teams.id),
  team1Score: integer("team1_score"),
  team2Score: integer("team2_score"),
  status: varchar("status", { length: 50 }).default("scheduled"),
  scheduledTime: timestamp("scheduled_time"),
  venue: varchar("venue", { length: 255 }),
  referee: varchar("referee", { length: 255 }),
  notes: text("notes"),
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow(),
});

// Calendar Activities table
export const activities = pgTable("activities", {
  id: serial("id").primaryKey(),
  title: varchar("title", { length: 255 }).notNull(),
  description: text("description"),
  activityType: varchar("activity_type", { length: 50 }).notNull(), // training, match, meeting, social
  startDateTime: timestamp("start_date_time").notNull(),
  endDateTime: timestamp("end_date_time").notNull(),
  location: varchar("location", { length: 255 }),
  
  // Attendance tracking
  isRecurring: boolean("is_recurring").notNull().default(false),
  recurringPattern: jsonb("recurring_pattern"), // frequency, days, end date
  maxAttendees: integer("max_attendees"),
  
  // Targeting
  clubWide: boolean("club_wide").notNull().default(false), // For entire club
  teamIds: jsonb("team_ids"), // Array of team IDs
  departmentIds: jsonb("department_ids"), // Array of department IDs
  
  // Additional details
  equipment: jsonb("equipment"), // Required equipment list
  instructions: text("instructions"),
  isCompulsory: boolean("is_compulsory").notNull().default(false),
  weather: varchar("weather", { length: 50 }), // indoor, outdoor, weather-dependent
  cost: integer("cost").default(0), // Activity cost in cents
  
  createdBy: varchar("created_by").references(() => users.id),
  status: varchar("status", { length: 20 }).default("scheduled"), // scheduled, completed, cancelled
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow(),
});

// Activity Attendance table
export const activityAttendance = pgTable("activity_attendance", {
  id: serial("id").primaryKey(),
  activityId: integer("activity_id").references(() => activities.id).notNull(),
  playerId: integer("player_id").references(() => players.id),
  coachId: integer("coach_id").references(() => coaches.id),
  
  // Attendance status
  status: varchar("status", { length: 20 }).default("invited"), // invited, confirmed, attended, absent, late
  responseDate: timestamp("response_date"),
  attendanceDate: timestamp("attendance_date"),
  
  // Absence management
  isExcused: boolean("is_excused").notNull().default(false),
  absenceReason: text("absence_reason"),
  notificationSent: boolean("notification_sent").notNull().default(false),
  
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow(),
});

// Departments table (for organizing teams)
export const departments = pgTable("departments", {
  id: serial("id").primaryKey(),
  name: varchar("name", { length: 255 }).notNull(),
  description: text("description"),
  headCoachId: integer("head_coach_id").references(() => coaches.id),
  ageGroup: varchar("age_group", { length: 50 }), // u16, u18, senior, etc.
  division: varchar("division", { length: 50 }), // first team, reserves, development
  status: varchar("status", { length: 20 }).default("active"),
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow(),
});

// Injury tracking table
export const injuries = pgTable("injuries", {
  id: serial("id").primaryKey(),
  playerId: integer("player_id").references(() => players.id).notNull(),
  injuryType: varchar("injury_type", { length: 100 }).notNull(),
  bodyPart: varchar("body_part", { length: 100 }).notNull(),
  severity: varchar("severity", { length: 50 }).notNull(), // mild, moderate, severe
  description: text("description"),
  injuryDate: timestamp("injury_date").notNull(),
  expectedReturnDate: timestamp("expected_return_date"),
  actualReturnDate: timestamp("actual_return_date"),
  treatmentPlan: text("treatment_plan"),
  status: varchar("status", { length: 50 }).notNull().default("active"), // active, recovering, cleared
  medicalStaff: varchar("medical_staff", { length: 255 }),
  followUpNotes: text("follow_up_notes"),
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow(),
});

// Training sessions table
export const trainingSessions = pgTable("training_sessions", {
  id: serial("id").primaryKey(),
  title: varchar("title", { length: 255 }).notNull(),
  description: text("description"),
  sessionType: varchar("session_type", { length: 50 }).notNull(), // skills, fitness, match-prep, recovery
  intensity: varchar("intensity", { length: 20 }).notNull(), // low, medium, high
  duration: integer("duration"), // minutes
  location: varchar("location", { length: 255 }),
  coachId: integer("coach_id").references(() => coaches.id),
  teamIds: jsonb("team_ids"), // Array of team IDs
  scheduledDate: timestamp("scheduled_date").notNull(),
  objectives: text("objectives"),
  equipmentNeeded: jsonb("equipment_needed"),
  weatherRequirements: varchar("weather_requirements", { length: 50 }),
  maxParticipants: integer("max_participants"),
  status: varchar("status", { length: 20 }).notNull().default("scheduled"),
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow(),
});

// Training attendance table
export const trainingAttendance = pgTable("training_attendance", {
  id: serial("id").primaryKey(),
  sessionId: integer("session_id").references(() => trainingSessions.id).notNull(),
  playerId: integer("player_id").references(() => players.id).notNull(),
  status: varchar("status", { length: 20 }).notNull().default("scheduled"), // scheduled, attended, absent, excused
  arrivalTime: timestamp("arrival_time"),
  performanceRating: integer("performance_rating"), // 1-10 scale
  notes: text("notes"),
  injuryReported: boolean("injury_reported").notNull().default(false),
  fatigueLevels: jsonb("fatigue_levels"), // pre/post session ratings
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow(),
});

// Fitness assessments table
export const fitnessAssessments = pgTable("fitness_assessments", {
  id: serial("id").primaryKey(),
  playerId: integer("player_id").references(() => players.id).notNull(),
  assessmentDate: timestamp("assessment_date").notNull(),
  assessmentType: varchar("assessment_type", { length: 50 }).notNull(), // pre-season, mid-season, post-season
  
  // Physical measurements
  vo2Max: integer("vo2_max"), // ml/kg/min
  bodyFatPercentage: integer("body_fat_percentage"), // decimal stored as integer (e.g., 125 = 12.5%)
  muscleMass: integer("muscle_mass"), // kg
  flexibility: integer("flexibility"), // score out of 100
  
  // Performance tests
  sprintTime40m: integer("sprint_time_40m"), // milliseconds
  sprintTime100m: integer("sprint_time_100m"), // milliseconds
  benchPress: integer("bench_press"), // kg
  squat: integer("squat"), // kg
  verticalJump: integer("vertical_jump"), // cm
  agilityCourse: integer("agility_course"), // seconds * 100
  
  // Overall scores
  enduranceScore: integer("endurance_score"), // 1-100
  strengthScore: integer("strength_score"), // 1-100
  speedScore: integer("speed_score"), // 1-100
  agilityScore: integer("agility_score"), // 1-100
  overallFitnessScore: integer("overall_fitness_score"), // 1-100
  
  notes: text("notes"),
  assessedBy: varchar("assessed_by", { length: 255 }),
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow(),
});

// Roster management tables
export const rosters = pgTable("rosters", {
  id: serial("id").primaryKey(),
  name: varchar("name", { length: 255 }).notNull(),
  teamId: integer("team_id").references(() => teams.id).notNull(),
  formation: varchar("formation", { length: 50 }).notNull(), // 15s-standard, 7s-standard, etc.
  matchDate: timestamp("match_date"),
  opposition: varchar("opposition", { length: 255 }),
  notes: text("notes"),
  isActive: boolean("is_active").notNull().default(false),
  createdBy: varchar("created_by").references(() => users.id),
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow(),
});

export const rosterAssignments = pgTable("roster_assignments", {
  id: serial("id").primaryKey(),
  rosterId: integer("roster_id").references(() => rosters.id).notNull(),
  playerId: integer("player_id").references(() => players.id).notNull(),
  positionId: varchar("position_id", { length: 10 }).notNull(), // 1, 2, 3... 15 for rugby positions
  isStarter: boolean("is_starter").notNull().default(true),
  orderIndex: integer("order_index").notNull().default(0),
  createdAt: timestamp("created_at").defaultNow(),
});

export const rosterSubstitutes = pgTable("roster_substitutes", {
  id: serial("id").primaryKey(),
  rosterId: integer("roster_id").references(() => rosters.id).notNull(),
  playerId: integer("player_id").references(() => players.id).notNull(),
  orderIndex: integer("order_index").notNull().default(0),
  createdAt: timestamp("created_at").defaultNow(),
});

// Relations
export const teamsRelations = relations(teams, ({ many }) => ({
  players: many(players),
  teamCoaches: many(teamCoaches),
}));

export const playersRelations = relations(players, ({ one }) => ({
  team: one(teams, {
    fields: [players.teamId],
    references: [teams.id],
  }),
}));

export const coachesRelations = relations(coaches, ({ many }) => ({
  teamCoaches: many(teamCoaches),
}));

export const teamCoachesRelations = relations(teamCoaches, ({ one }) => ({
  team: one(teams, {
    fields: [teamCoaches.teamId],
    references: [teams.id],
  }),
  coach: one(coaches, {
    fields: [teamCoaches.coachId],
    references: [coaches.id],
  }),
}));

export const tournamentsRelations = relations(tournaments, ({ many }) => ({
  matches: many(matches),
}));

export const matchesRelations = relations(matches, ({ one }) => ({
  tournament: one(tournaments, {
    fields: [matches.tournamentId],
    references: [tournaments.id],
  }),
  team1: one(teams, {
    fields: [matches.team1Id],
    references: [teams.id],
  }),
  team2: one(teams, {
    fields: [matches.team2Id],
    references: [teams.id],
  }),
}));

export const activitiesRelations = relations(activities, ({ one, many }) => ({
  creator: one(users, {
    fields: [activities.createdBy],
    references: [users.id],
  }),
  attendance: many(activityAttendance),
}));

export const activityAttendanceRelations = relations(activityAttendance, ({ one }) => ({
  activity: one(activities, {
    fields: [activityAttendance.activityId],
    references: [activities.id],
  }),
  player: one(players, {
    fields: [activityAttendance.playerId],
    references: [players.id],
  }),
  coach: one(coaches, {
    fields: [activityAttendance.coachId],
    references: [coaches.id],
  }),
}));

export const departmentsRelations = relations(departments, ({ one, many }) => ({
  headCoach: one(coaches, {
    fields: [departments.headCoachId],
    references: [coaches.id],
  }),
}));

// Insert schemas
export const insertTeamSchema = createInsertSchema(teams).omit({
  id: true,
  createdAt: true,
  updatedAt: true,
});

export const insertPlayerSchema = createInsertSchema(players).omit({
  id: true,
  createdAt: true,
  updatedAt: true,
});

export const insertCoachSchema = createInsertSchema(coaches).omit({
  id: true,
  createdAt: true,
  updatedAt: true,
});

export const insertTeamCoachSchema = createInsertSchema(teamCoaches).omit({
  id: true,
  createdAt: true,
});

export const insertTournamentSchema = createInsertSchema(tournaments).omit({
  id: true,
  createdAt: true,
  updatedAt: true,
});

export const insertMatchSchema = createInsertSchema(matches).omit({
  id: true,
  createdAt: true,
  updatedAt: true,
});

export const insertActivitySchema = createInsertSchema(activities).omit({
  id: true,
  createdAt: true,
  updatedAt: true,
});

export const insertActivityAttendanceSchema = createInsertSchema(activityAttendance).omit({
  id: true,
  createdAt: true,
  updatedAt: true,
});

export const insertDepartmentSchema = createInsertSchema(departments).omit({
  id: true,
  createdAt: true,
  updatedAt: true,
});

export const insertRosterSchema = createInsertSchema(rosters).omit({
  id: true,
  createdAt: true,
  updatedAt: true,
});

export const insertRosterAssignmentSchema = createInsertSchema(rosterAssignments).omit({
  id: true,
  createdAt: true,
});

export const insertRosterSubstituteSchema = createInsertSchema(rosterSubstitutes).omit({
  id: true,
  createdAt: true,
});

// Types
export type UpsertUser = typeof users.$inferInsert;
export type User = typeof users.$inferSelect;
export type Team = typeof teams.$inferSelect;
export type InsertTeam = z.infer<typeof insertTeamSchema>;
export type Player = typeof players.$inferSelect;
export type InsertPlayer = z.infer<typeof insertPlayerSchema>;
export type Coach = typeof coaches.$inferSelect;
export type InsertCoach = z.infer<typeof insertCoachSchema>;
export type TeamCoach = typeof teamCoaches.$inferSelect;
export type InsertTeamCoach = z.infer<typeof insertTeamCoachSchema>;
export type Tournament = typeof tournaments.$inferSelect;
export type InsertTournament = z.infer<typeof insertTournamentSchema>;
export type Match = typeof matches.$inferSelect;
export type InsertMatch = z.infer<typeof insertMatchSchema>;
export type Activity = typeof activities.$inferSelect;
export type InsertActivity = z.infer<typeof insertActivitySchema>;
export type ActivityAttendance = typeof activityAttendance.$inferSelect;
export type InsertActivityAttendance = z.infer<typeof insertActivityAttendanceSchema>;
export type Department = typeof departments.$inferSelect;
export type InsertDepartment = z.infer<typeof insertDepartmentSchema>;
export type Roster = typeof rosters.$inferSelect;
export type InsertRoster = z.infer<typeof insertRosterSchema>;
export type RosterAssignment = typeof rosterAssignments.$inferSelect;
export type InsertRosterAssignment = z.infer<typeof insertRosterAssignmentSchema>;
export type RosterSubstitute = typeof rosterSubstitutes.$inferSelect;
export type InsertRosterSubstitute = z.infer<typeof insertRosterSubstituteSchema>;

// Password reset token types
export type PasswordResetToken = typeof passwordResetTokens.$inferSelect;
export type InsertPasswordResetToken = typeof passwordResetTokens.$inferInsert;

// Dashboard widget configuration tables
export const dashboardWidgets = pgTable("dashboard_widgets", {
  id: serial("id").primaryKey(),
  userId: varchar("user_id").references(() => users.id).notNull(),
  widgetType: varchar("widget_type", { length: 50 }).notNull(), // team_stats, recent_activities, player_performance, upcoming_events, news_feed, weather
  title: varchar("title", { length: 255 }).notNull(),
  position: jsonb("position").notNull(), // {x: number, y: number, width: number, height: number}
  settings: jsonb("settings"), // Widget-specific configuration
  isVisible: boolean("is_visible").default(true).notNull(),
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow(),
});

export const widgetTemplates = pgTable("widget_templates", {
  id: serial("id").primaryKey(),
  name: varchar("name", { length: 100 }).notNull(),
  widgetType: varchar("widget_type", { length: 50 }).notNull(),
  defaultTitle: varchar("default_title", { length: 255 }).notNull(),
  description: text("description"),
  defaultPosition: jsonb("default_position").notNull(),
  defaultSettings: jsonb("default_settings"),
  category: varchar("category", { length: 50 }).notNull(), // analytics, communication, management, events
  iconName: varchar("icon_name", { length: 50 }).notNull(), // Lucide icon name
  isActive: boolean("is_active").default(true).notNull(),
  requiredRole: varchar("required_role", { length: 50 }).default("user"), // user, coach, admin
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow(),
});

// Club Communication tables
export const clubNews = pgTable("club_news", {
  id: serial("id").primaryKey(),
  title: varchar("title", { length: 255 }).notNull(),
  content: text("content").notNull(),
  excerpt: varchar("excerpt", { length: 500 }),
  category: varchar("category", { length: 100 }).notNull().default("general"), // general, match, training, social, announcement
  priority: varchar("priority", { length: 20 }).notNull().default("normal"), // low, normal, high, urgent
  status: varchar("status", { length: 20 }).notNull().default("draft"), // draft, published, archived
  featuredImageUrl: varchar("featured_image_url"),
  authorId: varchar("author_id").references(() => users.id).notNull(),
  publishedAt: timestamp("published_at"),
  expiresAt: timestamp("expires_at"),
  viewCount: integer("view_count").default(0),
  tags: jsonb("tags"), // Array of tags for categorization
  isSticky: boolean("is_sticky").default(false), // Pin to top
  allowComments: boolean("allow_comments").default(true),
  notifyMembers: boolean("notify_members").default(false), // Send notification to all members
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow(),
});

export const newsletters = pgTable("newsletters", {
  id: serial("id").primaryKey(),
  title: varchar("title", { length: 255 }).notNull(),
  subject: varchar("subject", { length: 255 }).notNull(),
  content: text("content").notNull(),
  htmlContent: text("html_content"),
  recipientType: varchar("recipient_type", { length: 50 }).notNull().default("all"), // all, players, coaches, parents, specific_team
  recipientIds: jsonb("recipient_ids"), // Array of specific user/team IDs if recipientType is specific
  status: varchar("status", { length: 20 }).notNull().default("draft"), // draft, scheduled, sent, failed
  scheduledAt: timestamp("scheduled_at"),
  sentAt: timestamp("sent_at"),
  totalRecipients: integer("total_recipients").default(0),
  deliveredCount: integer("delivered_count").default(0),
  openCount: integer("open_count").default(0),
  clickCount: integer("click_count").default(0),
  bounceCount: integer("bounce_count").default(0),
  templateId: integer("template_id"),
  senderId: varchar("sender_id").references(() => users.id).notNull(),
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow(),
});

export const emailLogs = pgTable("email_logs", {
  id: serial("id").primaryKey(),
  newsletterId: integer("newsletter_id").references(() => newsletters.id),
  recipientEmail: varchar("recipient_email", { length: 255 }).notNull(),
  recipientId: varchar("recipient_id").references(() => users.id),
  status: varchar("status", { length: 50 }).notNull(), // sent, delivered, opened, clicked, bounced, failed
  errorMessage: text("error_message"),
  sentAt: timestamp("sent_at"),
  deliveredAt: timestamp("delivered_at"),
  openedAt: timestamp("opened_at"),
  clickedAt: timestamp("clicked_at"),
  bouncedAt: timestamp("bounced_at"),
  userAgent: text("user_agent"),
  ipAddress: varchar("ip_address", { length: 45 }),
  createdAt: timestamp("created_at").defaultNow(),
});

// Club Banners
export const clubBanners = pgTable("club_banners", {
  id: serial("id").primaryKey(),
  title: varchar("title", { length: 255 }).notNull(),
  description: text("description"),
  imageUrl: varchar("image_url").notNull(),
  linkUrl: varchar("link_url"),
  position: varchar("position", { length: 50 }).notNull().default("header"), // header, sidebar, footer, popup, homepage
  displayOrder: integer("display_order").default(0),
  startDate: timestamp("start_date"),
  endDate: timestamp("end_date"),
  isActive: boolean("is_active").default(true),
  targetAudience: varchar("target_audience", { length: 50 }).default("all"), // all, members, visitors, specific_role
  clickCount: integer("click_count").default(0),
  viewCount: integer("view_count").default(0),
  backgroundColor: varchar("background_color", { length: 7 }),
  textColor: varchar("text_color", { length: 7 }),
  borderStyle: varchar("border_style", { length: 100 }),
  animationType: varchar("animation_type", { length: 50 }), // none, fade, slide, bounce
  createdBy: varchar("created_by").references(() => users.id).notNull(),
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow(),
});

// Club Terms and Conditions
export const clubTerms = pgTable("club_terms", {
  id: serial("id").primaryKey(),
  title: varchar("title", { length: 255 }).notNull(),
  content: text("content").notNull(),
  version: varchar("version", { length: 20 }).notNull(),
  type: varchar("type", { length: 50 }).notNull().default("general"), // general, conduct, membership, safety, privacy
  category: varchar("category", { length: 100 }).notNull().default("terms"), // terms, conduct, expectations, rules
  isActive: boolean("is_active").default(true),
  isMandatory: boolean("is_mandatory").default(true),
  effectiveDate: timestamp("effective_date").notNull(),
  expiryDate: timestamp("expiry_date"),
  minimumAge: integer("minimum_age"), // Minimum age requirement for agreement
  parentalConsent: boolean("parental_consent").default(false), // Requires parental consent for minors
  digitalSignature: boolean("digital_signature").default(false), // Requires digital signature
  acknowledgmentText: text("acknowledgment_text"), // Custom text user must acknowledge
  createdBy: varchar("created_by").references(() => users.id).notNull(),
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow(),
});

export const memberTermsAgreements = pgTable("member_terms_agreements", {
  id: serial("id").primaryKey(),
  userId: varchar("user_id").references(() => users.id).notNull(),
  playerId: integer("player_id").references(() => players.id), // If agreement is for a player
  termsId: integer("terms_id").references(() => clubTerms.id).notNull(),
  agreedAt: timestamp("agreed_at").notNull(),
  ipAddress: varchar("ip_address", { length: 45 }),
  userAgent: text("user_agent"),
  signatureData: text("signature_data"), // Digital signature data if required
  witnessName: varchar("witness_name", { length: 255 }), // For parental consent
  witnessSignature: text("witness_signature"),
  isActive: boolean("is_active").default(true),
  revokedAt: timestamp("revoked_at"),
  revokedReason: text("revoked_reason"),
  createdAt: timestamp("created_at").defaultNow(),
});

// Communication preferences
export const communicationPreferences = pgTable("communication_preferences", {
  id: serial("id").primaryKey(),
  userId: varchar("user_id").references(() => users.id).notNull(),
  emailNewsletters: boolean("email_newsletters").default(true),
  emailMatchUpdates: boolean("email_match_updates").default(true),
  emailTrainingAlerts: boolean("email_training_alerts").default(true),
  emailSocialEvents: boolean("email_social_events").default(true),
  emailEmergencyOnly: boolean("email_emergency_only").default(false),
  smsNotifications: boolean("sms_notifications").default(false),
  pushNotifications: boolean("push_notifications").default(true),
  frequency: varchar("frequency", { length: 20 }).default("weekly"), // daily, weekly, monthly
  language: varchar("language", { length: 10 }).default("en"),
  timezone: varchar("timezone", { length: 50 }).default("UTC"),
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow(),
});

// Create insert schemas and types for the new tables
export const insertClubNewsSchema = createInsertSchema(clubNews).omit({
  id: true,
  createdAt: true,
  updatedAt: true,
});

export const insertNewsletterSchema = createInsertSchema(newsletters).omit({
  id: true,
  createdAt: true,
  updatedAt: true,
});

export const insertClubBannerSchema = createInsertSchema(clubBanners).omit({
  id: true,
  createdAt: true,
  updatedAt: true,
});

export const insertClubTermsSchema = createInsertSchema(clubTerms).omit({
  id: true,
  createdAt: true,
  updatedAt: true,
});

export const insertMemberTermsAgreementSchema = createInsertSchema(memberTermsAgreements).omit({
  id: true,
  createdAt: true,
});

export const insertCommunicationPreferencesSchema = createInsertSchema(communicationPreferences).omit({
  id: true,
  createdAt: true,
  updatedAt: true,
});

// Enhanced Communication Features
export const newsletterTemplates = pgTable("newsletter_templates", {
  id: serial("id").primaryKey(),
  name: varchar("name", { length: 255 }).notNull(),
  description: text("description"),
  subject: varchar("subject", { length: 255 }).notNull(),
  htmlContent: text("html_content").notNull(),
  textContent: text("text_content"),
  isDefault: boolean("is_default").default(false),
  category: varchar("category", { length: 100 }).default("general"), // general, match, training, event
  createdBy: varchar("created_by").references(() => users.id).notNull(),
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow(),
});

export const newsComments = pgTable("news_comments", {
  id: serial("id").primaryKey(),
  newsId: integer("news_id").references(() => clubNews.id, { onDelete: "cascade" }).notNull(),
  authorId: varchar("author_id").references(() => users.id).notNull(),
  content: text("content").notNull(),
  parentId: integer("parent_id"),
  isApproved: boolean("is_approved").default(false),
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow(),
});

export const clubEvents = pgTable("club_events", {
  id: serial("id").primaryKey(),
  title: varchar("title", { length: 255 }).notNull(),
  description: text("description"),
  eventType: varchar("event_type", { length: 50 }).notNull(), // match, training, social, meeting, tournament
  startDateTime: timestamp("start_date_time").notNull(),
  endDateTime: timestamp("end_date_time").notNull(),
  location: varchar("location", { length: 255 }),
  isAllDay: boolean("is_all_day").default(false),
  isRecurring: boolean("is_recurring").default(false),
  recurringPattern: jsonb("recurring_pattern"), // For recurring events
  maxAttendees: integer("max_attendees"),
  registrationRequired: boolean("registration_required").default(false),
  registrationDeadline: timestamp("registration_deadline"),
  cost: text("cost"),
  organizerId: varchar("organizer_id").references(() => users.id).notNull(),
  status: varchar("status", { length: 20 }).default("scheduled"), // scheduled, cancelled, completed
  visibility: varchar("visibility", { length: 20 }).default("members"), // public, members, team_specific
  teamId: integer("team_id").references(() => teams.id), // For team-specific events
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow(),
});

export const eventAttendees = pgTable("event_attendees", {
  id: serial("id").primaryKey(),
  eventId: integer("event_id").references(() => clubEvents.id, { onDelete: "cascade" }).notNull(),
  userId: varchar("user_id").references(() => users.id).notNull(),
  status: varchar("status", { length: 20 }).default("registered"), // registered, attended, no_show, cancelled
  registeredAt: timestamp("registered_at").defaultNow(),
  notes: text("notes"),
  createdAt: timestamp("created_at").defaultNow(),
});

export const notificationSettings = pgTable("notification_settings", {
  id: serial("id").primaryKey(),
  userId: varchar("user_id").references(() => users.id).notNull(),
  emailNews: boolean("email_news").default(true),
  emailEvents: boolean("email_events").default(true),
  emailMatches: boolean("email_matches").default(true),
  emailNewsletters: boolean("email_newsletters").default(true),
  pushNotifications: boolean("push_notifications").default(true),
  smsNotifications: boolean("sms_notifications").default(false),
  frequency: varchar("frequency", { length: 20 }).default("immediate"), // immediate, daily, weekly
  quietHours: jsonb("quiet_hours"), // Time ranges to avoid notifications
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow(),
});

export const communicationAnalytics = pgTable("communication_analytics", {
  id: serial("id").primaryKey(),
  contentType: varchar("content_type", { length: 50 }).notNull(), // news, newsletter, banner, event
  contentId: integer("content_id").notNull(),
  userId: varchar("user_id").references(() => users.id),
  action: varchar("action", { length: 50 }).notNull(), // view, click, share, download, register
  sessionId: varchar("session_id"),
  userAgent: text("user_agent"),
  ipAddress: varchar("ip_address", { length: 45 }),
  referrer: text("referrer"),
  timestamp: timestamp("timestamp").defaultNow(),
});




// Create insert schemas for enhanced features
export const insertNewsletterTemplateSchema = createInsertSchema(newsletterTemplates).omit({
  id: true,
  createdAt: true,
  updatedAt: true,
});

export const insertNewsCommentSchema = createInsertSchema(newsComments).omit({
  id: true,
  createdAt: true,
  updatedAt: true,
});

export const insertClubEventSchema = createInsertSchema(clubEvents).omit({
  id: true,
  createdAt: true,
  updatedAt: true,
});

export const insertEventAttendeeSchema = createInsertSchema(eventAttendees).omit({
  id: true,
  createdAt: true,
});

export const insertNotificationSettingsSchema = createInsertSchema(notificationSettings).omit({
  id: true,
  createdAt: true,
  updatedAt: true,
});

export const insertCommunicationAnalyticsSchema = createInsertSchema(communicationAnalytics).omit({
  id: true,
});

// User Club Roles schema
export const insertUserClubRoleSchema = createInsertSchema(userClubRoles).omit({
  id: true,
  createdAt: true,
  updatedAt: true,
});

export type UserClubRole = typeof userClubRoles.$inferSelect;
export type InsertUserClubRole = typeof userClubRoles.$inferInsert;

// Type exports for the new tables
export type ClubNews = typeof clubNews.$inferSelect;
export type InsertClubNews = z.infer<typeof insertClubNewsSchema>;
export type Newsletter = typeof newsletters.$inferSelect;
export type InsertNewsletter = z.infer<typeof insertNewsletterSchema>;
export type EmailLog = typeof emailLogs.$inferSelect;
export type ClubBanner = typeof clubBanners.$inferSelect;
export type InsertClubBanner = z.infer<typeof insertClubBannerSchema>;
export type ClubTerms = typeof clubTerms.$inferSelect;
export type InsertClubTerms = z.infer<typeof insertClubTermsSchema>;
export type MemberTermsAgreement = typeof memberTermsAgreements.$inferSelect;
export type InsertMemberTermsAgreement = z.infer<typeof insertMemberTermsAgreementSchema>;
export type CommunicationPreferences = typeof communicationPreferences.$inferSelect;
export type InsertCommunicationPreferences = z.infer<typeof insertCommunicationPreferencesSchema>;

// Enhanced feature types
export type NewsletterTemplate = typeof newsletterTemplates.$inferSelect;
export type InsertNewsletterTemplate = z.infer<typeof insertNewsletterTemplateSchema>;
export type NewsComment = typeof newsComments.$inferSelect;
export type InsertNewsComment = z.infer<typeof insertNewsCommentSchema>;
export type ClubEvent = typeof clubEvents.$inferSelect;
export type InsertClubEvent = z.infer<typeof insertClubEventSchema>;
export type EventAttendee = typeof eventAttendees.$inferSelect;
export type InsertEventAttendee = z.infer<typeof insertEventAttendeeSchema>;
export type NotificationSettings = typeof notificationSettings.$inferSelect;
export type InsertNotificationSettings = z.infer<typeof insertNotificationSettingsSchema>;
export type CommunicationAnalytics = typeof communicationAnalytics.$inferSelect;
export type InsertCommunicationAnalytics = z.infer<typeof insertCommunicationAnalyticsSchema>;

// Gamified Achievement System
export const achievementCategories = pgTable("achievement_categories", {
  id: serial("id").primaryKey(),
  name: varchar("name", { length: 100 }).notNull().unique(),
  description: text("description"),
  icon: varchar("icon", { length: 50 }).default("trophy"),
  color: varchar("color", { length: 20 }).default("#FFD700"),
  isActive: boolean("is_active").default(true),
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow(),
});

export const achievements = pgTable("achievements", {
  id: serial("id").primaryKey(),
  categoryId: integer("category_id").references(() => achievementCategories.id).notNull(),
  name: varchar("name", { length: 255 }).notNull(),
  description: text("description").notNull(),
  type: varchar("type", { length: 50 }).notNull(), // milestone, streak, performance, participation, leadership
  criteria: jsonb("criteria").notNull(), // Flexible criteria definition
  points: integer("points").default(0),
  rarity: varchar("rarity", { length: 20 }).default("common"), // common, rare, epic, legendary
  badge: varchar("badge", { length: 255 }), // Badge image/icon identifier
  isRepeatable: boolean("is_repeatable").default(false),
  maxCount: integer("max_count"), // For repeatable achievements
  targetRole: varchar("target_role", { length: 20 }).default("both"), // player, coach, both
  isActive: boolean("is_active").default(true),
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow(),
});

export const userAchievements = pgTable("user_achievements", {
  id: serial("id").primaryKey(),
  userId: varchar("user_id").references(() => users.id).notNull(),
  achievementId: integer("achievement_id").references(() => achievements.id).notNull(),
  progress: integer("progress").default(0),
  maxProgress: integer("max_progress").notNull(),
  isCompleted: boolean("is_completed").default(false),
  completedAt: timestamp("completed_at"),
  count: integer("count").default(0), // For repeatable achievements
  metadata: jsonb("metadata"), // Additional data for achievement tracking
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow(),
});

export const userPoints = pgTable("user_points", {
  id: serial("id").primaryKey(),
  userId: varchar("user_id").references(() => users.id).notNull().unique(),
  totalPoints: integer("total_points").default(0),
  seasonPoints: integer("season_points").default(0),
  monthlyPoints: integer("monthly_points").default(0),
  weeklyPoints: integer("weekly_points").default(0),
  level: integer("level").default(1),
  experience: integer("experience").default(0),
  nextLevelExperience: integer("next_level_experience").default(100),
  rank: integer("rank"),
  seasonRank: integer("season_rank"),
  streakCount: integer("streak_count").default(0),
  longestStreak: integer("longest_streak").default(0),
  lastActivityDate: timestamp("last_activity_date"),
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow(),
});

export const pointTransactions = pgTable("point_transactions", {
  id: serial("id").primaryKey(),
  userId: varchar("user_id").references(() => users.id).notNull(),
  points: integer("points").notNull(),
  reason: varchar("reason", { length: 100 }).notNull(),
  description: text("description"),
  sourceType: varchar("source_type", { length: 50 }).notNull(), // achievement, activity, manual, bonus
  sourceId: integer("source_id"), // Reference to the source (achievement_id, activity_id, etc.)
  metadata: jsonb("metadata"),
  createdAt: timestamp("created_at").defaultNow(),
});

export const leaderboards = pgTable("leaderboards", {
  id: serial("id").primaryKey(),
  name: varchar("name", { length: 100 }).notNull(),
  description: text("description"),
  type: varchar("type", { length: 50 }).notNull(), // points, achievements, performance, activity
  period: varchar("period", { length: 20 }).notNull(), // all_time, season, monthly, weekly
  criteria: jsonb("criteria").notNull(),
  isActive: boolean("is_active").default(true),
  resetSchedule: varchar("reset_schedule", { length: 50 }), // For periodic leaderboards
  lastReset: timestamp("last_reset"),
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow(),
});

export const leaderboardEntries = pgTable("leaderboard_entries", {
  id: serial("id").primaryKey(),
  leaderboardId: integer("leaderboard_id").references(() => leaderboards.id, { onDelete: "cascade" }).notNull(),
  userId: varchar("user_id").references(() => users.id).notNull(),
  score: integer("score").notNull(),
  rank: integer("rank").notNull(),
  metadata: jsonb("metadata"),
  periodStart: timestamp("period_start"),
  periodEnd: timestamp("period_end"),
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow(),
});

// Quick Reaction Coaching Feedback System
export const coachingFeedbackReactions = pgTable("coaching_feedback_reactions", {
  id: serial("id").primaryKey(),
  name: varchar("name", { length: 100 }).notNull(),
  emoji: varchar("emoji", { length: 10 }).notNull(),
  category: varchar("category", { length: 50 }).notNull(), // positive, negative, neutral, instruction
  color: varchar("color", { length: 20 }).default("#3B82F6"),
  isActive: boolean("is_active").default(true),
  sortOrder: integer("sort_order").default(0),
  createdAt: timestamp("created_at").defaultNow(),
});

export const playerFeedbackLogs = pgTable("player_feedback_logs", {
  id: serial("id").primaryKey(),
  playerId: integer("player_id").references(() => players.id).notNull(),
  coachId: varchar("coach_id").references(() => users.id).notNull(),
  reactionId: integer("reaction_id").references(() => coachingFeedbackReactions.id).notNull(),
  sessionType: varchar("session_type", { length: 50 }).notNull(), // training, match, drill
  sessionId: integer("session_id"), // Reference to training session or match
  context: text("context"), // Additional context or notes
  intensity: integer("intensity").default(1), // 1-5 scale for reaction intensity
  timestamp: timestamp("timestamp").defaultNow(),
  location: varchar("location", { length: 255 }), // Where the feedback was given
  tags: text("tags").array(), // Additional tags for categorization
  createdAt: timestamp("created_at").defaultNow(),
});

export const feedbackSessions = pgTable("feedback_sessions", {
  id: serial("id").primaryKey(),
  name: varchar("name", { length: 255 }).notNull(),
  type: varchar("type", { length: 50 }).notNull(), // training, match, individual
  teamId: integer("team_id").references(() => teams.id),
  coachId: varchar("coach_id").references(() => users.id).notNull(),
  startTime: timestamp("start_time").notNull(),
  endTime: timestamp("end_time"),
  status: varchar("status", { length: 20 }).default("active"), // active, completed, paused
  location: varchar("location", { length: 255 }),
  description: text("description"),
  totalFeedbacks: integer("total_feedbacks").default(0),
  participantCount: integer("participant_count").default(0),
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow(),
});

export const challengeTypes = pgTable("challenge_types", {
  id: serial("id").primaryKey(),
  name: varchar("name", { length: 100 }).notNull(),
  description: text("description"),
  icon: varchar("icon", { length: 50 }).default("target"),
  isActive: boolean("is_active").default(true),
  createdAt: timestamp("created_at").defaultNow(),
});

export const challenges = pgTable("challenges", {
  id: serial("id").primaryKey(),
  typeId: integer("type_id").references(() => challengeTypes.id).notNull(),
  name: varchar("name", { length: 255 }).notNull(),
  description: text("description").notNull(),
  criteria: jsonb("criteria").notNull(),
  rewards: jsonb("rewards").notNull(), // Points, badges, etc.
  startDate: timestamp("start_date").notNull(),
  endDate: timestamp("end_date").notNull(),
  maxParticipants: integer("max_participants"),
  targetRole: varchar("target_role", { length: 20 }).default("both"),
  difficulty: varchar("difficulty", { length: 20 }).default("medium"), // easy, medium, hard, expert
  isTeamChallenge: boolean("is_team_challenge").default(false),
  isActive: boolean("is_active").default(true),
  createdBy: varchar("created_by").references(() => users.id).notNull(),
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow(),
});

export const challengeParticipants = pgTable("challenge_participants", {
  id: serial("id").primaryKey(),
  challengeId: integer("challenge_id").references(() => challenges.id, { onDelete: "cascade" }).notNull(),
  userId: varchar("user_id").references(() => users.id).notNull(),
  teamId: integer("team_id").references(() => teams.id),
  progress: integer("progress").default(0),
  maxProgress: integer("max_progress").notNull(),
  isCompleted: boolean("is_completed").default(false),
  completedAt: timestamp("completed_at"),
  rank: integer("rank"),
  metadata: jsonb("metadata"),
  joinedAt: timestamp("joined_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow(),
});

export const badges = pgTable("badges", {
  id: serial("id").primaryKey(),
  name: varchar("name", { length: 100 }).notNull().unique(),
  description: text("description"),
  icon: varchar("icon", { length: 255 }),
  rarity: varchar("rarity", { length: 20 }).default("common"),
  category: varchar("category", { length: 50 }),
  isActive: boolean("is_active").default(true),
  createdAt: timestamp("created_at").defaultNow(),
});

export const userBadges = pgTable("user_badges", {
  id: serial("id").primaryKey(),
  userId: varchar("user_id").references(() => users.id).notNull(),
  badgeId: integer("badge_id").references(() => badges.id).notNull(),
  earnedAt: timestamp("earned_at").defaultNow(),
  sourceType: varchar("source_type", { length: 50 }), // achievement, challenge, manual
  sourceId: integer("source_id"),
});

export const milestones = pgTable("milestones", {
  id: serial("id").primaryKey(),
  name: varchar("name", { length: 255 }).notNull(),
  description: text("description"),
  type: varchar("type", { length: 50 }).notNull(), // games_played, goals_scored, training_sessions, etc.
  thresholds: jsonb("thresholds").notNull(), // Array of milestone values
  rewards: jsonb("rewards").notNull(),
  targetRole: varchar("target_role", { length: 20 }).default("both"),
  isActive: boolean("is_active").default(true),
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow(),
});

export const userMilestones = pgTable("user_milestones", {
  id: serial("id").primaryKey(),
  userId: varchar("user_id").references(() => users.id).notNull(),
  milestoneId: integer("milestone_id").references(() => milestones.id).notNull(),
  currentValue: integer("current_value").default(0),
  completedThresholds: jsonb("completed_thresholds").default([]),
  lastThresholdReached: integer("last_threshold_reached"),
  reachedAt: timestamp("reached_at"),
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow(),
});

// Create insert schemas for gamification tables
export const insertAchievementCategorySchema = createInsertSchema(achievementCategories).omit({
  id: true,
  createdAt: true,
  updatedAt: true,
});

export const insertAchievementSchema = createInsertSchema(achievements).omit({
  id: true,
  createdAt: true,
  updatedAt: true,
});

export const insertUserAchievementSchema = createInsertSchema(userAchievements).omit({
  id: true,
  createdAt: true,
  updatedAt: true,
});

export const insertUserPointsSchema = createInsertSchema(userPoints).omit({
  id: true,
  createdAt: true,
  updatedAt: true,
});

export const insertPointTransactionSchema = createInsertSchema(pointTransactions).omit({
  id: true,
  createdAt: true,
});

export const insertLeaderboardSchema = createInsertSchema(leaderboards).omit({
  id: true,
  createdAt: true,
  updatedAt: true,
});

export const insertLeaderboardEntrySchema = createInsertSchema(leaderboardEntries).omit({
  id: true,
  createdAt: true,
  updatedAt: true,
});

export const insertChallengeTypeSchema = createInsertSchema(challengeTypes).omit({
  id: true,
  createdAt: true,
});

export const insertChallengeSchema = createInsertSchema(challenges).omit({
  id: true,
  createdAt: true,
  updatedAt: true,
});

export const insertChallengeParticipantSchema = createInsertSchema(challengeParticipants).omit({
  id: true,
  joinedAt: true,
  updatedAt: true,
});

export const insertBadgeSchema = createInsertSchema(badges).omit({
  id: true,
  createdAt: true,
});

export const insertUserBadgeSchema = createInsertSchema(userBadges).omit({
  id: true,
  earnedAt: true,
});

export const insertMilestoneSchema = createInsertSchema(milestones).omit({
  id: true,
  createdAt: true,
  updatedAt: true,
});

export const insertUserMilestoneSchema = createInsertSchema(userMilestones).omit({
  id: true,
  createdAt: true,
  updatedAt: true,
});

// Insert schemas for payment system
export const insertMembershipFeeSchema = createInsertSchema(membershipFees).omit({
  id: true,
  createdAt: true,
  updatedAt: true,
});

export const insertPaymentActivitySchema = createInsertSchema(paymentActivities).omit({
  id: true,
  createdAt: true,
  updatedAt: true,
});

export const insertPaymentNotificationSchema = createInsertSchema(paymentNotifications).omit({
  id: true,
  createdAt: true,
  updatedAt: true,
});

export const insertAutomaticPaymentScheduleSchema = createInsertSchema(automaticPaymentSchedules).omit({
  id: true,
  createdAt: true,
  updatedAt: true,
});

export const insertMemberStatusHistorySchema = createInsertSchema(memberStatusHistory).omit({
  id: true,
  createdAt: true,
});

// Type exports for payment system
export type MembershipFee = typeof membershipFees.$inferSelect;
export type InsertMembershipFee = z.infer<typeof insertMembershipFeeSchema>;

export type PaymentActivity = typeof paymentActivities.$inferSelect;
export type InsertPaymentActivity = z.infer<typeof insertPaymentActivitySchema>;

export type PaymentNotification = typeof paymentNotifications.$inferSelect;
export type InsertPaymentNotification = z.infer<typeof insertPaymentNotificationSchema>;

export type AutomaticPaymentSchedule = typeof automaticPaymentSchedules.$inferSelect;
export type InsertAutomaticPaymentSchedule = z.infer<typeof insertAutomaticPaymentScheduleSchema>;

export type MemberStatusHistory = typeof memberStatusHistory.$inferSelect;
export type InsertMemberStatusHistory = z.infer<typeof insertMemberStatusHistorySchema>;

// Type exports for gamification system
export type AchievementCategory = typeof achievementCategories.$inferSelect;
export type InsertAchievementCategory = z.infer<typeof insertAchievementCategorySchema>;
export type Achievement = typeof achievements.$inferSelect;
export type InsertAchievement = z.infer<typeof insertAchievementSchema>;
export type UserAchievement = typeof userAchievements.$inferSelect;
export type InsertUserAchievement = z.infer<typeof insertUserAchievementSchema>;
export type UserPoints = typeof userPoints.$inferSelect;
export type InsertUserPoints = z.infer<typeof insertUserPointsSchema>;
export type PointTransaction = typeof pointTransactions.$inferSelect;
export type InsertPointTransaction = z.infer<typeof insertPointTransactionSchema>;
export type Leaderboard = typeof leaderboards.$inferSelect;
export type InsertLeaderboard = z.infer<typeof insertLeaderboardSchema>;
export type LeaderboardEntry = typeof leaderboardEntries.$inferSelect;
export type InsertLeaderboardEntry = z.infer<typeof insertLeaderboardEntrySchema>;
export type ChallengeType = typeof challengeTypes.$inferSelect;
export type InsertChallengeType = z.infer<typeof insertChallengeTypeSchema>;
export type Challenge = typeof challenges.$inferSelect;
export type InsertChallenge = z.infer<typeof insertChallengeSchema>;
export type ChallengeParticipant = typeof challengeParticipants.$inferSelect;
export type InsertChallengeParticipant = z.infer<typeof insertChallengeParticipantSchema>;
export type Badge = typeof badges.$inferSelect;
export type InsertBadge = z.infer<typeof insertBadgeSchema>;
export type UserBadge = typeof userBadges.$inferSelect;
export type InsertUserBadge = z.infer<typeof insertUserBadgeSchema>;
export type Milestone = typeof milestones.$inferSelect;
export type InsertMilestone = z.infer<typeof insertMilestoneSchema>;
export type UserMilestone = typeof userMilestones.$inferSelect;
export type InsertUserMilestone = z.infer<typeof insertUserMilestoneSchema>;

// Insert schemas for coaching feedback system
export const insertCoachingFeedbackReactionSchema = createInsertSchema(coachingFeedbackReactions).omit({
  id: true,
  createdAt: true,
});

export const insertPlayerFeedbackLogSchema = createInsertSchema(playerFeedbackLogs).omit({
  id: true,
  createdAt: true,
  timestamp: true,
});

export const insertFeedbackSessionSchema = createInsertSchema(feedbackSessions).omit({
  id: true,
  createdAt: true,
  updatedAt: true,
});

// Insert schemas for subscription system
export const insertOrganizationSchema = createInsertSchema(organizations).omit({
  id: true,
  createdAt: true,
  updatedAt: true,
});

export const insertModuleSchema = createInsertSchema(modules).omit({
  id: true,
  createdAt: true,
  updatedAt: true,
});

export const insertOrganizationModulePermissionSchema = createInsertSchema(organizationModulePermissions).omit({
  id: true,
  createdAt: true,
  updatedAt: true,
});

export const insertSubscriptionPlanSchema = createInsertSchema(subscriptionPlans).omit({
  id: true,
  createdAt: true,
  updatedAt: true,
});

// Type exports for subscription system
export type Organization = typeof organizations.$inferSelect;
export type InsertOrganization = z.infer<typeof insertOrganizationSchema>;
export type Module = typeof modules.$inferSelect;
export type InsertModule = z.infer<typeof insertModuleSchema>;
export type OrganizationModulePermission = typeof organizationModulePermissions.$inferSelect;
export type InsertOrganizationModulePermission = z.infer<typeof insertOrganizationModulePermissionSchema>;
export type SubscriptionPlan = typeof subscriptionPlans.$inferSelect;
export type InsertSubscriptionPlan = z.infer<typeof insertSubscriptionPlanSchema>;

// Type exports for coaching feedback system
export type CoachingFeedbackReaction = typeof coachingFeedbackReactions.$inferSelect;
export type InsertCoachingFeedbackReaction = z.infer<typeof insertCoachingFeedbackReactionSchema>;
export type PlayerFeedbackLog = typeof playerFeedbackLogs.$inferSelect;
export type InsertPlayerFeedbackLog = z.infer<typeof insertPlayerFeedbackLogSchema>;
export type FeedbackSession = typeof feedbackSessions.$inferSelect;
export type InsertFeedbackSession = z.infer<typeof insertFeedbackSessionSchema>;

// Insert schemas for dashboard widgets
export const insertDashboardWidgetSchema = createInsertSchema(dashboardWidgets).omit({
  id: true,
  createdAt: true,
  updatedAt: true,
});

export const insertWidgetTemplateSchema = createInsertSchema(widgetTemplates).omit({
  id: true,
  createdAt: true,
  updatedAt: true,
});



// Type exports for dashboard widgets
export type DashboardWidget = typeof dashboardWidgets.$inferSelect;
export type InsertDashboardWidget = z.infer<typeof insertDashboardWidgetSchema>;
export type WidgetTemplate = typeof widgetTemplates.$inferSelect;
export type InsertWidgetTemplate = z.infer<typeof insertWidgetTemplateSchema>;

// Grid layout types
export type GridPosition = {
  x: number;
  y: number;
  w: number;
  h: number;
};

export type GridConfig = {
  cols: number;
  rowHeight: number;
  breakpoints: Record<string, number>;
  margin: [number, number];
  containerPadding: [number, number];
};

export type WidgetSettings = {
  [key: string]: any;
};


