import {
  pgTable,
  uuid,
  varchar,
  text,
  time,
  date,
  numeric,
  timestamp,
  boolean,
  integer,
  pgEnum,
} from 'drizzle-orm/pg-core'
import { users } from './users.js'
import { accounts, members } from './accounts.js'

// --- Enums ---

export const lessonFormatEnum = pgEnum('lesson_format', ['private', 'group'])
export const billingUnitEnum = pgEnum('billing_unit', ['day', 'week', 'month', 'quarter', 'year'])

// --- Tables ---

export const instructors = pgTable('instructor', {
  id: uuid('id').primaryKey().defaultRandom(),
  userId: uuid('user_id').references(() => users.id),
  displayName: varchar('display_name', { length: 255 }).notNull(),
  bio: text('bio'),
  instruments: text('instruments').array(),
  isActive: boolean('is_active').notNull().default(true),
  createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
})

export const lessonTypes = pgTable('lesson_type', {
  id: uuid('id').primaryKey().defaultRandom(),
  name: varchar('name', { length: 255 }).notNull(),
  instrument: varchar('instrument', { length: 100 }),
  durationMinutes: integer('duration_minutes').notNull(),
  lessonFormat: lessonFormatEnum('lesson_format').notNull().default('private'),
  rateWeekly: numeric('rate_weekly', { precision: 10, scale: 2 }),
  rateMonthly: numeric('rate_monthly', { precision: 10, scale: 2 }),
  rateQuarterly: numeric('rate_quarterly', { precision: 10, scale: 2 }),
  isActive: boolean('is_active').notNull().default(true),
  createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
})

export const scheduleSlots = pgTable('schedule_slot', {
  id: uuid('id').primaryKey().defaultRandom(),
  instructorId: uuid('instructor_id')
    .notNull()
    .references(() => instructors.id),
  lessonTypeId: uuid('lesson_type_id')
    .notNull()
    .references(() => lessonTypes.id),
  dayOfWeek: integer('day_of_week').notNull(), // 0=Sunday, 6=Saturday
  startTime: time('start_time').notNull(),
  room: varchar('room', { length: 100 }),
  maxStudents: integer('max_students').notNull().default(1),
  rateWeekly: numeric('rate_weekly', { precision: 10, scale: 2 }),
  rateMonthly: numeric('rate_monthly', { precision: 10, scale: 2 }),
  rateQuarterly: numeric('rate_quarterly', { precision: 10, scale: 2 }),
  isActive: boolean('is_active').notNull().default(true),
  createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
})

export const enrollmentStatusEnum = pgEnum('enrollment_status', [
  'active',
  'paused',
  'cancelled',
  'completed',
])

export const enrollments = pgTable('enrollment', {
  id: uuid('id').primaryKey().defaultRandom(),
  memberId: uuid('member_id')
    .notNull()
    .references(() => members.id),
  accountId: uuid('account_id')
    .notNull()
    .references(() => accounts.id),
  scheduleSlotId: uuid('schedule_slot_id')
    .notNull()
    .references(() => scheduleSlots.id),
  instructorId: uuid('instructor_id')
    .notNull()
    .references(() => instructors.id),
  status: enrollmentStatusEnum('status').notNull().default('active'),
  startDate: date('start_date').notNull(),
  endDate: date('end_date'),
  rate: numeric('rate', { precision: 10, scale: 2 }),
  billingInterval: integer('billing_interval'),
  billingUnit: billingUnitEnum('billing_unit'),
  makeupCredits: integer('makeup_credits').notNull().default(0),
  notes: text('notes'),
  createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
})

export const lessonSessionStatusEnum = pgEnum('lesson_session_status', [
  'scheduled',
  'attended',
  'missed',
  'makeup',
  'cancelled',
])

export const lessonSessions = pgTable('lesson_session', {
  id: uuid('id').primaryKey().defaultRandom(),
  enrollmentId: uuid('enrollment_id')
    .notNull()
    .references(() => enrollments.id),
  scheduledDate: date('scheduled_date').notNull(),
  scheduledTime: time('scheduled_time').notNull(),
  actualStartTime: time('actual_start_time'),
  actualEndTime: time('actual_end_time'),
  status: lessonSessionStatusEnum('status').notNull().default('scheduled'),
  instructorNotes: text('instructor_notes'),
  memberNotes: text('member_notes'),
  homeworkAssigned: text('homework_assigned'),
  nextLessonGoals: text('next_lesson_goals'),
  topicsCovered: text('topics_covered').array(),
  makeupForSessionId: uuid('makeup_for_session_id'),
  substituteInstructorId: uuid('substitute_instructor_id').references(() => instructors.id),
  notesCompletedAt: timestamp('notes_completed_at', { withTimezone: true }),
  createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
})

export const instructorBlockedDates = pgTable('instructor_blocked_date', {
  id: uuid('id').primaryKey().defaultRandom(),
  instructorId: uuid('instructor_id')
    .notNull()
    .references(() => instructors.id),
  startDate: date('start_date').notNull(),
  endDate: date('end_date').notNull(),
  reason: varchar('reason', { length: 255 }),
  createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
})

export const storeClosures = pgTable('store_closure', {
  id: uuid('id').primaryKey().defaultRandom(),
  name: varchar('name', { length: 255 }).notNull(),
  startDate: date('start_date').notNull(),
  endDate: date('end_date').notNull(),
  createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
})

export const gradingScales = pgTable('grading_scale', {
  id: uuid('id').primaryKey().defaultRandom(),
  name: varchar('name', { length: 255 }).notNull(),
  description: text('description'),
  isDefault: boolean('is_default').notNull().default(false),
  createdBy: uuid('created_by').references(() => users.id),
  isActive: boolean('is_active').notNull().default(true),
  createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
})

export const gradingScaleLevels = pgTable('grading_scale_level', {
  id: uuid('id').primaryKey().defaultRandom(),
  gradingScaleId: uuid('grading_scale_id')
    .notNull()
    .references(() => gradingScales.id),
  value: varchar('value', { length: 50 }).notNull(),
  label: varchar('label', { length: 255 }).notNull(),
  numericValue: integer('numeric_value').notNull(),
  colorHex: varchar('color_hex', { length: 7 }),
  sortOrder: integer('sort_order').notNull(),
})

export const lessonPlanItemStatusEnum = pgEnum('lesson_plan_item_status', [
  'not_started',
  'in_progress',
  'mastered',
  'skipped',
])

export const memberLessonPlans = pgTable('member_lesson_plan', {
  id: uuid('id').primaryKey().defaultRandom(),
  memberId: uuid('member_id')
    .notNull()
    .references(() => members.id),
  enrollmentId: uuid('enrollment_id')
    .notNull()
    .references(() => enrollments.id),
  createdBy: uuid('created_by').references(() => users.id),
  title: varchar('title', { length: 255 }).notNull(),
  description: text('description'),
  isActive: boolean('is_active').notNull().default(true),
  startedDate: date('started_date'),
  completedDate: date('completed_date'),
  createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
})

export const lessonPlanSections = pgTable('lesson_plan_section', {
  id: uuid('id').primaryKey().defaultRandom(),
  lessonPlanId: uuid('lesson_plan_id')
    .notNull()
    .references(() => memberLessonPlans.id),
  title: varchar('title', { length: 255 }).notNull(),
  description: text('description'),
  sortOrder: integer('sort_order').notNull(),
  createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
})

export const lessonPlanItems = pgTable('lesson_plan_item', {
  id: uuid('id').primaryKey().defaultRandom(),
  sectionId: uuid('section_id')
    .notNull()
    .references(() => lessonPlanSections.id),
  title: varchar('title', { length: 255 }).notNull(),
  description: text('description'),
  status: lessonPlanItemStatusEnum('status').notNull().default('not_started'),
  gradingScaleId: uuid('grading_scale_id').references(() => gradingScales.id),
  currentGradeValue: varchar('current_grade_value', { length: 50 }),
  targetGradeValue: varchar('target_grade_value', { length: 50 }),
  startedDate: date('started_date'),
  masteredDate: date('mastered_date'),
  notes: text('notes'),
  sortOrder: integer('sort_order').notNull(),
  createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
})

// --- Lesson Plan Templates ---

export const skillLevelEnum = pgEnum('skill_level', ['beginner', 'intermediate', 'advanced', 'all_levels'])

export const lessonPlanTemplates = pgTable('lesson_plan_template', {
  id: uuid('id').primaryKey().defaultRandom(),
  name: varchar('name', { length: 255 }).notNull(),
  description: text('description'),
  instrument: varchar('instrument', { length: 100 }),
  skillLevel: skillLevelEnum('skill_level').notNull().default('all_levels'),
  createdBy: uuid('created_by').references(() => users.id),
  isActive: boolean('is_active').notNull().default(true),
  createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
})

export const lessonPlanTemplateSections = pgTable('lesson_plan_template_section', {
  id: uuid('id').primaryKey().defaultRandom(),
  templateId: uuid('template_id')
    .notNull()
    .references(() => lessonPlanTemplates.id),
  title: varchar('title', { length: 255 }).notNull(),
  description: text('description'),
  sortOrder: integer('sort_order').notNull(),
  createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
})

export const lessonPlanTemplateItems = pgTable('lesson_plan_template_item', {
  id: uuid('id').primaryKey().defaultRandom(),
  sectionId: uuid('section_id')
    .notNull()
    .references(() => lessonPlanTemplateSections.id),
  title: varchar('title', { length: 255 }).notNull(),
  description: text('description'),
  gradingScaleId: uuid('grading_scale_id').references(() => gradingScales.id),
  targetGradeValue: varchar('target_grade_value', { length: 50 }),
  sortOrder: integer('sort_order').notNull(),
  createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
})

export const lessonPlanItemGradeHistory = pgTable('lesson_plan_item_grade_history', {
  id: uuid('id').primaryKey().defaultRandom(),
  lessonPlanItemId: uuid('lesson_plan_item_id')
    .notNull()
    .references(() => lessonPlanItems.id),
  gradingScaleId: uuid('grading_scale_id').references(() => gradingScales.id),
  gradeValue: varchar('grade_value', { length: 50 }).notNull(),
  gradedBy: uuid('graded_by').references(() => users.id),
  sessionId: uuid('session_id').references(() => lessonSessions.id),
  notes: text('notes'),
  createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
})

export const lessonSessionPlanItems = pgTable('lesson_session_plan_item', {
  id: uuid('id').primaryKey().defaultRandom(),
  sessionId: uuid('session_id')
    .notNull()
    .references(() => lessonSessions.id),
  lessonPlanItemId: uuid('lesson_plan_item_id')
    .notNull()
    .references(() => lessonPlanItems.id),
  createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
})

// --- Type exports ---

export type Instructor = typeof instructors.$inferSelect
export type InstructorInsert = typeof instructors.$inferInsert
export type LessonType = typeof lessonTypes.$inferSelect
export type LessonTypeInsert = typeof lessonTypes.$inferInsert
export type ScheduleSlot = typeof scheduleSlots.$inferSelect
export type ScheduleSlotInsert = typeof scheduleSlots.$inferInsert
export type Enrollment = typeof enrollments.$inferSelect
export type EnrollmentInsert = typeof enrollments.$inferInsert
export type LessonSession = typeof lessonSessions.$inferSelect
export type LessonSessionInsert = typeof lessonSessions.$inferInsert
export type InstructorBlockedDate = typeof instructorBlockedDates.$inferSelect
export type InstructorBlockedDateInsert = typeof instructorBlockedDates.$inferInsert
export type StoreClosure = typeof storeClosures.$inferSelect
export type StoreClosureInsert = typeof storeClosures.$inferInsert
export type GradingScale = typeof gradingScales.$inferSelect
export type GradingScaleInsert = typeof gradingScales.$inferInsert
export type GradingScaleLevel = typeof gradingScaleLevels.$inferSelect
export type GradingScaleLevelInsert = typeof gradingScaleLevels.$inferInsert
export type MemberLessonPlan = typeof memberLessonPlans.$inferSelect
export type MemberLessonPlanInsert = typeof memberLessonPlans.$inferInsert
export type LessonPlanSection = typeof lessonPlanSections.$inferSelect
export type LessonPlanSectionInsert = typeof lessonPlanSections.$inferInsert
export type LessonPlanItem = typeof lessonPlanItems.$inferSelect
export type LessonPlanItemInsert = typeof lessonPlanItems.$inferInsert
export type LessonPlanTemplate = typeof lessonPlanTemplates.$inferSelect
export type LessonPlanTemplateInsert = typeof lessonPlanTemplates.$inferInsert
export type LessonPlanTemplateSection = typeof lessonPlanTemplateSections.$inferSelect
export type LessonPlanTemplateSectionInsert = typeof lessonPlanTemplateSections.$inferInsert
export type LessonPlanTemplateItem = typeof lessonPlanTemplateItems.$inferSelect
export type LessonPlanTemplateItemInsert = typeof lessonPlanTemplateItems.$inferInsert
export type LessonPlanItemGradeHistory = typeof lessonPlanItemGradeHistory.$inferSelect
export type LessonPlanItemGradeHistoryInsert = typeof lessonPlanItemGradeHistory.$inferInsert
export type LessonSessionPlanItem = typeof lessonSessionPlanItems.$inferSelect
export type LessonSessionPlanItemInsert = typeof lessonSessionPlanItems.$inferInsert
