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"),
  contactEmail: varchar("contact_email"),
  contactPhone: varchar("contact_phone"),
  address: text("address"),
  description: text("description"),
  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"),
  adminUserId: varchar("admin_user_id"),
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow(),
});

// Branding Settings - organization-specific branding configuration
export const brandingSettings = pgTable("branding_settings", {
  id: serial("id").primaryKey(),
  organizationId: integer("organization_id").references(() => organizations.id).notNull().unique(),
  primaryColor: varchar("primary_color", { length: 7 }).default("#3b82f6"),
  secondaryColor: varchar("secondary_color", { length: 7 }).default("#64748b"),
  accentColor: varchar("accent_color", { length: 7 }).default("#10b981"),
  logoUrl: varchar("logo_url"),
  faviconUrl: varchar("favicon_url"),
  websiteUrl: varchar("website_url"),
  customCss: text("custom_css"),
  customJs: text("custom_js"),
  socialMedia: jsonb("social_media").$type<{
    facebook?: string;
    twitter?: string;
    instagram?: string;
    linkedin?: string;
    youtube?: string;
  }>(),
  seoSettings: jsonb("seo_settings").$type<{
    metaTitle?: string;
    metaDescription?: string;
    metaKeywords?: string;
    ogImage?: string;
  }>(),
  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"),
  sport: varchar("sport", { length: 50 }).notNull(), // Primary sport for the user
  status: varchar("status", { length: 20 }).default("active").notNull(),
  emailVerified: boolean("email_verified").default(false),
  verificationToken: varchar("verification_token"),
  verificationExpiresAt: timestamp("verification_expires_at"),
  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"),
  placeOfBirth: varchar("place_of_birth", { length: 255 }),
  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(),
});

// Relations
export const organizationsRelations = relations(organizations, ({ many }) => ({
  users: many(users),
  teams: many(teams),
  membershipFees: many(membershipFees),
  paymentActivities: many(paymentActivities),
  paymentNotifications: many(paymentNotifications),
  automaticPaymentSchedules: many(automaticPaymentSchedules),
  memberStatusHistory: many(memberStatusHistory),
}));

export const usersRelations = relations(users, ({ one, many }) => ({
  organization: one(organizations, {
    fields: [users.organizationId],
    references: [organizations.id],
  }),
  clubRoles: many(userClubRoles),
  membershipFees: many(membershipFees),
  paymentActivities: many(paymentActivities),
  paymentNotifications: many(paymentNotifications),
  automaticPaymentSchedules: many(automaticPaymentSchedules),
  memberStatusHistory: many(memberStatusHistory),
}));

export const membershipFeesRelations = relations(membershipFees, ({ one, many }) => ({
  organization: one(organizations, {
    fields: [membershipFees.organizationId],
    references: [organizations.id],
  }),
  user: one(users, {
    fields: [membershipFees.userId],
    references: [users.id],
  }),
  paymentActivities: many(paymentActivities),
  paymentNotifications: many(paymentNotifications),
  automaticPaymentSchedules: many(automaticPaymentSchedules),
  memberStatusHistory: many(memberStatusHistory),
}));

export const paymentActivitiesRelations = relations(paymentActivities, ({ one }) => ({
  membershipFee: one(membershipFees, {
    fields: [paymentActivities.membershipFeeId],
    references: [membershipFees.id],
  }),
  organization: one(organizations, {
    fields: [paymentActivities.organizationId],
    references: [organizations.id],
  }),
  user: one(users, {
    fields: [paymentActivities.userId],
    references: [users.id],
  }),
}));

export const paymentNotificationsRelations = relations(paymentNotifications, ({ one }) => ({
  membershipFee: one(membershipFees, {
    fields: [paymentNotifications.membershipFeeId],
    references: [membershipFees.id],
  }),
  user: one(users, {
    fields: [paymentNotifications.userId],
    references: [users.id],
  }),
  organization: one(organizations, {
    fields: [paymentNotifications.organizationId],
    references: [organizations.id],
  }),
}));

export const automaticPaymentSchedulesRelations = relations(automaticPaymentSchedules, ({ one }) => ({
  organization: one(organizations, {
    fields: [automaticPaymentSchedules.organizationId],
    references: [organizations.id],
  }),
  user: one(users, {
    fields: [automaticPaymentSchedules.userId],
    references: [users.id],
  }),
  membershipFee: one(membershipFees, {
    fields: [automaticPaymentSchedules.membershipFeeId],
    references: [membershipFees.id],
  }),
}));

export const memberStatusHistoryRelations = relations(memberStatusHistory, ({ one }) => ({
  user: one(users, {
    fields: [memberStatusHistory.userId],
    references: [users.id],
  }),
  organization: one(organizations, {
    fields: [memberStatusHistory.organizationId],
    references: [organizations.id],
  }),
  membershipFee: one(membershipFees, {
    fields: [memberStatusHistory.membershipFeeId],
    references: [membershipFees.id],
  }),
  changedByUser: one(users, {
    fields: [memberStatusHistory.changedBy],
    references: [users.id],
  }),
}));

export const teamsRelations = relations(teams, ({ one, many }) => ({
  organization: one(organizations, {
    fields: [teams.organizationId],
    references: [organizations.id],
  }),
  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],
  }),
}));

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

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

// Type exports
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>;

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 Organization = typeof organizations.$inferSelect;
export type UpsertUser = typeof users.$inferInsert;
export type User = typeof users.$inferSelect;



// Attendance sessions - training sessions, matches, meetings
export const attendanceSessions = pgTable("attendance_sessions", {
  id: serial("id").primaryKey(),
  title: varchar("title", { length: 255 }).notNull(),
  description: text("description"),
  type: varchar("type", { length: 50 }).notNull(), // "training", "match", "meeting", "event"
  date: date("date").notNull(),
  startTime: varchar("start_time", { length: 10 }).notNull(), // "14:30"
  endTime: varchar("end_time", { length: 10 }).notNull(),
  location: varchar("location", { length: 255 }),
  teamId: integer("team_id").references(() => teams.id),
  coachId: integer("coach_id").references(() => coaches.id),
  organizationId: integer("organization_id").references(() => organizations.id).notNull(),
  isActive: boolean("is_active").default(true).notNull(),
  attendanceRequired: boolean("attendance_required").default(true).notNull(),
  createdBy: varchar("created_by").references(() => users.id).notNull(),
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow(),
});

// Individual attendance records
export const attendanceRecords = pgTable("attendance_records", {
  id: serial("id").primaryKey(),
  sessionId: integer("session_id").references(() => attendanceSessions.id).notNull(),
  playerId: integer("player_id").references(() => players.id).notNull(),
  status: varchar("status", { length: 20 }).notNull(), // "present", "absent", "late", "excused"
  arrivalTime: varchar("arrival_time", { length: 10 }), // "14:35" - actual arrival time
  notes: text("notes"), // Reason for absence, lateness, etc.
  markedBy: varchar("marked_by").references(() => users.id).notNull(),
  markedAt: timestamp("marked_at").defaultNow(),
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow(),
});

// Attendance statistics view
export const attendanceStats = pgTable("attendance_stats", {
  id: serial("id").primaryKey(),
  playerId: integer("player_id").references(() => players.id).notNull(),
  totalSessions: integer("total_sessions").default(0).notNull(),
  attendedSessions: integer("attended_sessions").default(0).notNull(),
  absences: integer("absences").default(0).notNull(),
  lateCount: integer("late_count").default(0).notNull(),
  attendanceRate: integer("attendance_rate").default(0).notNull(), // Percentage
  lastUpdated: timestamp("last_updated").defaultNow(),
});

// Attendance notifications for parents/guardians
export const attendanceNotifications = pgTable("attendance_notifications", {
  id: serial("id").primaryKey(),
  sessionId: integer("session_id").references(() => attendanceSessions.id).notNull(),
  playerId: integer("player_id").references(() => players.id).notNull(),
  parentEmail: varchar("parent_email", { length: 255 }),
  parentPhone: varchar("parent_phone", { length: 20 }),
  notificationType: varchar("notification_type", { length: 50 }).notNull(), // "absence", "late", "reminder"
  status: varchar("status", { length: 20 }).default("pending").notNull(), // "pending", "sent", "failed"
  sentAt: timestamp("sent_at"),
  createdAt: timestamp("created_at").defaultNow(),
});

// Schema definitions for attendance
export const insertAttendanceSessionSchema = createInsertSchema(attendanceSessions).omit({
  id: true,
  createdAt: true,
  updatedAt: true,
});

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

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

// 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").references(() => tournaments.id).notNull(),
  round: varchar("round", { length: 100 }).notNull(),
  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 }),
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow(),
});

// Leagues - Comprehensive league management
export const leagues = pgTable("leagues", {
  id: serial("id").primaryKey(),
  name: varchar("name", { length: 255 }).notNull(),
  sport: varchar("sport", { length: 100 }).notNull(),
  season: varchar("season", { length: 50 }).notNull(), // "2024-2025", "Spring 2025"
  division: varchar("division", { length: 100 }), // "Premier", "Division 1", "U18"
  organizationId: integer("organization_id").references(() => organizations.id).notNull(),
  description: text("description"),
  logoUrl: varchar("logo_url"),
  status: varchar("status", { length: 20 }).default("active").notNull(), // "active", "completed", "upcoming", "cancelled"
  startDate: date("start_date").notNull(),
  endDate: date("end_date").notNull(),
  maxTeams: integer("max_teams"),
  registrationDeadline: date("registration_deadline"),
  rules: text("rules"),
  pointsForWin: integer("points_for_win").default(3),
  pointsForDraw: integer("points_for_draw").default(1),
  pointsForLoss: integer("points_for_loss").default(0),
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow(),
});

// League Teams - Teams participating in a league
export const leagueTeams = pgTable("league_teams", {
  id: serial("id").primaryKey(),
  leagueId: integer("league_id").references(() => leagues.id).notNull(),
  teamId: integer("team_id").references(() => teams.id).notNull(),
  points: integer("points").default(0).notNull(),
  played: integer("played").default(0).notNull(),
  won: integer("won").default(0).notNull(),
  drawn: integer("drawn").default(0).notNull(),
  lost: integer("lost").default(0).notNull(),
  goalsFor: integer("goals_for").default(0).notNull(),
  goalsAgainst: integer("goals_against").default(0).notNull(),
  goalDifference: integer("goal_difference").default(0).notNull(),
  joinedAt: timestamp("joined_at").defaultNow(),
});

// Venues - Comprehensive venue management
export const venues = pgTable("venues", {
  id: serial("id").primaryKey(),
  name: varchar("name", { length: 255 }).notNull(),
  address: text("address").notNull(),
  city: varchar("city", { length: 100 }).notNull(),
  state: varchar("state", { length: 100 }),
  country: varchar("country", { length: 100 }).notNull(),
  postalCode: varchar("postal_code", { length: 20 }),
  latitude: varchar("latitude", { length: 50 }),
  longitude: varchar("longitude", { length: 50 }),
  capacity: integer("capacity"),
  surfaceType: varchar("surface_type", { length: 50 }), // "grass", "artificial turf", "indoor court"
  facilities: jsonb("facilities").$type<string[]>(), // ["parking", "lighting", "changing rooms", "medical room"]
  imageUrl: varchar("image_url"),
  contactName: varchar("contact_name", { length: 255 }),
  contactPhone: varchar("contact_phone", { length: 20 }),
  contactEmail: varchar("contact_email", { length: 255 }),
  bookingCost: integer("booking_cost"), // in cents per hour
  availability: jsonb("availability"), // Weekly availability schedule
  status: varchar("status", { length: 20 }).default("active").notNull(),
  organizationId: integer("organization_id").references(() => organizations.id).notNull(),
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow(),
});

// Referees - Referee management
export const referees = pgTable("referees", {
  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"),
  licenseNumber: varchar("license_number", { length: 100 }),
  licenseLevel: varchar("license_level", { length: 50 }), // "International", "National", "Regional", "Local"
  experience: integer("experience"), // years
  sport: varchar("sport", { length: 100 }).notNull(),
  status: varchar("status", { length: 20 }).default("active").notNull(),
  organizationId: integer("organization_id").references(() => organizations.id).notNull(),
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow(),
});

// Fixtures - Enhanced match scheduling (extends matches table)
export const fixtures = pgTable("fixtures", {
  id: serial("id").primaryKey(),
  leagueId: integer("league_id").references(() => leagues.id),
  tournamentId: integer("tournament_id").references(() => tournaments.id),
  homeTeamId: integer("home_team_id").references(() => teams.id).notNull(),
  awayTeamId: integer("away_team_id").references(() => teams.id).notNull(),
  venueId: integer("venue_id").references(() => venues.id),
  refereeId: integer("referee_id").references(() => referees.id),
  scheduledDate: timestamp("scheduled_date").notNull(),
  kickoffTime: varchar("kickoff_time", { length: 10 }).notNull(), // "19:00"
  status: varchar("status", { length: 20 }).default("scheduled").notNull(), // "scheduled", "live", "completed", "postponed", "cancelled"
  homeScore: integer("home_score"),
  awayScore: integer("away_score"),
  halfTimeHomeScore: integer("half_time_home_score"),
  halfTimeAwayScore: integer("half_time_away_score"),
  attendance: integer("attendance"),
  weatherConditions: varchar("weather_conditions", { length: 100 }),
  matchOfficials: jsonb("match_officials").$type<{ assistant1?: string; assistant2?: string; fourthOfficial?: string }>(),
  notes: text("notes"),
  broadcastLink: varchar("broadcast_link", { length: 500 }),
  highlightsUrl: varchar("highlights_url", { length: 500 }),
  matchReportUrl: varchar("match_report_url", { length: 500 }),
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow(),
});

// Match Events - Live match updates (goals, cards, substitutions)
export const matchEvents = pgTable("match_events", {
  id: serial("id").primaryKey(),
  fixtureId: integer("fixture_id").references(() => fixtures.id).notNull(),
  eventType: varchar("event_type", { length: 50 }).notNull(), // "goal", "yellow_card", "red_card", "substitution", "penalty", "own_goal"
  teamId: integer("team_id").references(() => teams.id).notNull(),
  playerId: integer("player_id").references(() => players.id),
  minute: integer("minute").notNull(), // Match minute
  extraTime: integer("extra_time"), // Added time (e.g., 90+3)
  description: text("description"),
  assistPlayerId: integer("assist_player_id").references(() => players.id),
  subPlayerInId: integer("sub_player_in_id").references(() => players.id), // For substitutions
  subPlayerOutId: integer("sub_player_out_id").references(() => players.id), // For substitutions
  createdAt: timestamp("created_at").defaultNow(),
});

// Match Statistics - Detailed match stats
export const matchStatistics = pgTable("match_statistics", {
  id: serial("id").primaryKey(),
  fixtureId: integer("fixture_id").references(() => fixtures.id).notNull(),
  teamId: integer("team_id").references(() => teams.id).notNull(),
  possession: integer("possession"), // Percentage
  shots: integer("shots").default(0),
  shotsOnTarget: integer("shots_on_target").default(0),
  corners: integer("corners").default(0),
  fouls: integer("fouls").default(0),
  yellowCards: integer("yellow_cards").default(0),
  redCards: integer("red_cards").default(0),
  offsides: integer("offsides").default(0),
  passes: integer("passes").default(0),
  passAccuracy: integer("pass_accuracy"), // Percentage
  saves: integer("saves").default(0),
  tackles: integer("tackles").default(0),
  interceptions: integer("interceptions").default(0),
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow(),
});

// Player Transfers - Transfer management with approval workflow
export const playerTransfers = pgTable("player_transfers", {
  id: serial("id").primaryKey(),
  playerId: integer("player_id").references(() => players.id).notNull(),
  fromTeamId: integer("from_team_id").references(() => teams.id),
  toTeamId: integer("to_team_id").references(() => teams.id).notNull(),
  transferType: varchar("transfer_type", { length: 50 }).notNull(), // "permanent", "loan", "free_agent", "trial"
  transferFee: integer("transfer_fee"), // in cents
  currency: varchar("currency", { length: 3 }).default("USD"),
  status: varchar("status", { length: 20 }).default("pending").notNull(), // "pending", "approved", "rejected", "completed", "cancelled"
  requestedBy: varchar("requested_by").references(() => users.id).notNull(),
  requestDate: timestamp("request_date").defaultNow().notNull(),
  approvedBy: varchar("approved_by").references(() => users.id),
  approvalDate: timestamp("approval_date"),
  completionDate: timestamp("completion_date"),
  contractStartDate: date("contract_start_date"),
  contractEndDate: date("contract_end_date"),
  loanEndDate: date("loan_end_date"), // For loan transfers
  notes: text("notes"),
  rejectionReason: text("rejection_reason"),
  documents: jsonb("documents").$type<{ name: string; url: string }[]>(), // Contract documents
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow(),
});

// Tournament and match schema definitions
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 insertLeagueSchema = createInsertSchema(leagues).omit({
  id: true,
  createdAt: true,
  updatedAt: true,
});

export const insertLeagueTeamSchema = createInsertSchema(leagueTeams).omit({
  id: true,
  joinedAt: true,
});

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

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

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

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

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

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

export type AttendanceSession = typeof attendanceSessions.$inferSelect;
export type InsertAttendanceSession = z.infer<typeof insertAttendanceSessionSchema>;
export type AttendanceRecord = typeof attendanceRecords.$inferSelect;
export type InsertAttendanceRecord = z.infer<typeof insertAttendanceRecordSchema>;
export type AttendanceStats = typeof attendanceStats.$inferSelect;
export type AttendanceNotification = typeof attendanceNotifications.$inferSelect;
export type InsertAttendanceNotification = z.infer<typeof insertAttendanceNotificationSchema>;

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 League = typeof leagues.$inferSelect;
export type InsertLeague = z.infer<typeof insertLeagueSchema>;
export type LeagueTeam = typeof leagueTeams.$inferSelect;
export type InsertLeagueTeam = z.infer<typeof insertLeagueTeamSchema>;

export type Venue = typeof venues.$inferSelect;
export type InsertVenue = z.infer<typeof insertVenueSchema>;

export type Referee = typeof referees.$inferSelect;
export type InsertReferee = z.infer<typeof insertRefereeSchema>;

export type Fixture = typeof fixtures.$inferSelect;
export type InsertFixture = z.infer<typeof insertFixtureSchema>;

export type MatchEvent = typeof matchEvents.$inferSelect;
export type InsertMatchEvent = z.infer<typeof insertMatchEventSchema>;

export type MatchStatistics = typeof matchStatistics.$inferSelect;
export type InsertMatchStatistics = z.infer<typeof insertMatchStatisticsSchema>;

export type PlayerTransfer = typeof playerTransfers.$inferSelect;
export type InsertPlayerTransfer = z.infer<typeof insertPlayerTransferSchema>;

// CMS Content Management Tables
export const cmsPages = pgTable("cms_pages", {
  id: serial("id").primaryKey(),
  slug: varchar("slug", { length: 255 }).notNull().unique(),
  title: varchar("title", { length: 255 }).notNull(),
  metaDescription: varchar("meta_description", { length: 500 }),
  status: varchar("status", { length: 20 }).default("published").notNull(), // "draft", "published", "archived"
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow(),
});

export const cmsContentBlocks = pgTable("cms_content_blocks", {
  id: serial("id").primaryKey(),
  pageId: integer("page_id").references(() => cmsPages.id).notNull(),
  blockKey: varchar("block_key", { length: 255 }).notNull(), // Unique identifier for the content block
  blockType: varchar("block_type", { length: 50 }).notNull(), // "hero", "features", "stats", "testimonials", etc.
  title: varchar("title", { length: 255 }),
  subtitle: varchar("subtitle", { length: 500 }),
  content: text("content"),
  imageUrl: varchar("image_url"),
  buttonText: varchar("button_text", { length: 100 }),
  buttonUrl: varchar("button_url", { length: 500 }),
  metadata: jsonb("metadata"), // For additional flexible data
  sortOrder: integer("sort_order").default(0),
  isActive: boolean("is_active").default(true),
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow(),
});

export const cmsFeatures = pgTable("cms_features", {
  id: serial("id").primaryKey(),
  icon: varchar("icon", { length: 100 }).notNull(),
  title: varchar("title", { length: 255 }).notNull(),
  description: text("description").notNull(),
  features: jsonb("features"), // Array of feature points
  color: varchar("color", { length: 50 }).default("from-blue-500 to-purple-600"),
  sortOrder: integer("sort_order").default(0),
  isActive: boolean("is_active").default(true),
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow(),
});

export const cmsStats = pgTable("cms_stats", {
  id: serial("id").primaryKey(),
  icon: varchar("icon", { length: 100 }).notNull(),
  statValue: varchar("stat_value", { length: 50 }).notNull(),
  label: varchar("label", { length: 255 }).notNull(),
  sortOrder: integer("sort_order").default(0),
  isActive: boolean("is_active").default(true),
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow(),
});

export const cmsTestimonials = pgTable("cms_testimonials", {
  id: serial("id").primaryKey(),
  name: varchar("name", { length: 255 }).notNull(),
  position: varchar("position", { length: 255 }),
  company: varchar("company", { length: 255 }),
  content: text("content").notNull(),
  rating: integer("rating").default(5),
  avatarUrl: varchar("avatar_url"),
  isActive: boolean("is_active").default(true),
  sortOrder: integer("sort_order").default(0),
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow(),
});

// CMS Relations
export const cmsPageRelations = relations(cmsPages, ({ many }) => ({
  contentBlocks: many(cmsContentBlocks),
}));

export const cmsContentBlockRelations = relations(cmsContentBlocks, ({ one }) => ({
  page: one(cmsPages, {
    fields: [cmsContentBlocks.pageId],
    references: [cmsPages.id],
  }),
}));

// CMS Schema Types
export const insertCmsPageSchema = createInsertSchema(cmsPages, {
  title: z.string().min(1, "Title is required"),
  slug: z.string().min(1, "Slug is required"),
});

export const insertCmsContentBlockSchema = createInsertSchema(cmsContentBlocks, {
  blockKey: z.string().min(1, "Block key is required"),
  blockType: z.string().min(1, "Block type is required"),
});

export const insertCmsFeatureSchema = createInsertSchema(cmsFeatures, {
  title: z.string().min(1, "Title is required"),
  description: z.string().min(1, "Description is required"),
  icon: z.string().min(1, "Icon is required"),
});

export const insertCmsStatSchema = createInsertSchema(cmsStats, {
  statValue: z.string().min(1, "Stat value is required"),
  label: z.string().min(1, "Label is required"),
  icon: z.string().min(1, "Icon is required"),
});

export const insertCmsTestimonialSchema = createInsertSchema(cmsTestimonials, {
  name: z.string().min(1, "Name is required"),
  content: z.string().min(1, "Content is required"),
  rating: z.number().min(1).max(5),
});

export const insertSubscriptionPlanSchema = createInsertSchema(subscriptionPlans, {
  name: z.string().min(1, "Name is required"),
  tier: z.string().min(1, "Tier is required"),
  price: z.number().min(0, "Price must be non-negative"),
  interval: z.string().min(1, "Interval is required"),
}).omit({ id: true, createdAt: true, updatedAt: true });

export type CmsPage = typeof cmsPages.$inferSelect;
export type InsertCmsPage = z.infer<typeof insertCmsPageSchema>;
export type CmsContentBlock = typeof cmsContentBlocks.$inferSelect;
export type InsertCmsContentBlock = z.infer<typeof insertCmsContentBlockSchema>;
export type CmsFeature = typeof cmsFeatures.$inferSelect;
export type InsertCmsFeature = z.infer<typeof insertCmsFeatureSchema>;
export type CmsStat = typeof cmsStats.$inferSelect;
export type InsertCmsStat = z.infer<typeof insertCmsStatSchema>;
export type CmsTestimonial = typeof cmsTestimonials.$inferSelect;
export type InsertCmsTestimonial = z.infer<typeof insertCmsTestimonialSchema>;
export type SubscriptionPlan = typeof subscriptionPlans.$inferSelect;
export type InsertSubscriptionPlan = z.infer<typeof insertSubscriptionPlanSchema>;

export const insertBrandingSettingsSchema = createInsertSchema(brandingSettings).omit({
  id: true,
  createdAt: true,
  updatedAt: true,
});
export type BrandingSettings = typeof brandingSettings.$inferSelect;
export type InsertBrandingSettings = z.infer<typeof insertBrandingSettingsSchema>;

// In-app Notifications
export const notifications = pgTable("notifications", {
  id: serial("id").primaryKey(),
  userId: varchar("user_id").references(() => users.id).notNull(),
  organizationId: integer("organization_id").references(() => organizations.id),
  type: varchar("type", { length: 50 }).notNull(), // "info", "success", "warning", "error", "match", "payment", "attendance"
  title: varchar("title", { length: 255 }).notNull(),
  message: text("message").notNull(),
  link: varchar("link", { length: 500 }), // Optional link to related resource
  isRead: boolean("is_read").default(false).notNull(),
  readAt: timestamp("read_at"),
  metadata: jsonb("metadata"), // Additional notification data
  createdAt: timestamp("created_at").defaultNow(),
});

// Audit Logs for tracking admin actions
export const auditLogs = pgTable("audit_logs", {
  id: serial("id").primaryKey(),
  userId: varchar("user_id").references(() => users.id).notNull(),
  organizationId: integer("organization_id").references(() => organizations.id),
  action: varchar("action", { length: 100 }).notNull(), // "create", "update", "delete", "login", "logout"
  resource: varchar("resource", { length: 100 }).notNull(), // "user", "team", "player", "payment", etc.
  resourceId: varchar("resource_id", { length: 255 }), // ID of the affected resource
  description: text("description").notNull(), // Human-readable description
  ipAddress: varchar("ip_address", { length: 45 }),
  userAgent: text("user_agent"),
  changes: jsonb("changes"), // Before/after state for updates
  metadata: jsonb("metadata"), // Additional contextual data
  createdAt: timestamp("created_at").defaultNow(),
});

// User Preferences for storing user-specific settings
export const userPreferences = pgTable("user_preferences", {
  id: serial("id").primaryKey(),
  userId: varchar("user_id").references(() => users.id).notNull().unique(),
  // Notification Preferences
  emailNotifications: boolean("email_notifications").default(true).notNull(),
  smsNotifications: boolean("sms_notifications").default(false).notNull(),
  pushNotifications: boolean("push_notifications").default(true).notNull(),
  notificationTypes: jsonb("notification_types").$type<{
    teamUpdates?: boolean;
    playerUpdates?: boolean;
    matchReminders?: boolean;
    paymentReminders?: boolean;
    systemAlerts?: boolean;
  }>().default({
    teamUpdates: true,
    playerUpdates: true,
    matchReminders: true,
    paymentReminders: true,
    systemAlerts: true,
  }),
  // Theme & Layout Preferences
  theme: varchar("theme", { length: 20 }).default("light").notNull(), // "light", "dark", "auto"
  sidebarCollapsed: boolean("sidebar_collapsed").default(false).notNull(),
  compactMode: boolean("compact_mode").default(false).notNull(),
  // Privacy & Security
  profileVisibility: varchar("profile_visibility", { length: 20 }).default("team").notNull(), // "public", "team", "private"
  showEmail: boolean("show_email").default(false).notNull(),
  showPhone: boolean("show_phone").default(false).notNull(),
  twoFactorEnabled: boolean("two_factor_enabled").default(false).notNull(),
  // Other preferences
  language: varchar("language", { length: 10 }).default("en").notNull(),
  timezone: varchar("timezone", { length: 50 }).default("UTC").notNull(),
  dateFormat: varchar("date_format", { length: 20 }).default("MM/DD/YYYY").notNull(),
  timeFormat: varchar("time_format", { length: 10 }).default("12h").notNull(), // "12h" or "24h"
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow(),
});

// Notification relations
export const notificationsRelations = relations(notifications, ({ one }) => ({
  user: one(users, {
    fields: [notifications.userId],
    references: [users.id],
  }),
  organization: one(organizations, {
    fields: [notifications.organizationId],
    references: [organizations.id],
  }),
}));

// Audit log relations
export const auditLogsRelations = relations(auditLogs, ({ one }) => ({
  user: one(users, {
    fields: [auditLogs.userId],
    references: [users.id],
  }),
  organization: one(organizations, {
    fields: [auditLogs.organizationId],
    references: [organizations.id],
  }),
}));

// Insert schemas
export const insertNotificationSchema = createInsertSchema(notifications).omit({
  id: true,
  createdAt: true,
});

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

export const insertUserPreferencesSchema = createInsertSchema(userPreferences).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,
});

// Type exports
export type Notification = typeof notifications.$inferSelect;
export type InsertNotification = z.infer<typeof insertNotificationSchema>;
export type AuditLog = typeof auditLogs.$inferSelect;
export type InsertAuditLog = z.infer<typeof insertAuditLogSchema>;
export type UserPreferences = typeof userPreferences.$inferSelect;
export type InsertUserPreferences = z.infer<typeof insertUserPreferencesSchema>;
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>;
