import { pgTable, text, timestamp, integer, boolean, uuid, varchar, date } from 'drizzle-orm/pg-core';
import { relations } from 'drizzle-orm';
import { eq, and, ne } from 'drizzle-orm';

// Re-export drizzle operators for convenience
export { eq, and, ne };

// Tabla de usuarios (administradores, docentes, estudiantes)
export const users = pgTable('users', {
  id: uuid('id').defaultRandom().primaryKey(),
  email: varchar('email', { length: 255 }).notNull().unique(),
  password: text('password').notNull(),
  firstName: varchar('first_name', { length: 100 }).notNull(),
  lastName: varchar('last_name', { length: 100 }).notNull(),
  cedula: varchar('cedula', { length: 20 }).notNull().unique(),
  phone: varchar('phone', { length: 20 }),
  role: varchar('role', { length: 20 }).notNull(), // 'admin', 'teacher', 'student'
  admissionNumber: varchar('admission_number', { length: 50 }), // Solo para estudiantes
  isActive: boolean('is_active').default(true),
  createdAt: timestamp('created_at').defaultNow(),
  updatedAt: timestamp('updated_at').defaultNow(),
});

// Tabla de periodos
export const periods = pgTable('periods', {
  id: uuid('id').defaultRandom().primaryKey(),
  name: varchar('name', { length: 100 }).notNull(),
  startDate: date('start_date').notNull(),
  endDate: date('end_date').notNull(),
  isActive: boolean('is_active').default(true),
  createdAt: timestamp('created_at').defaultNow(),
  updatedAt: timestamp('updated_at').defaultNow(),
});

// Tabla de parciales
export const partials = pgTable('partials', {
  id: uuid('id').defaultRandom().primaryKey(),
  name: varchar('name', { length: 100 }).notNull(),
  periodId: uuid('period_id').references(() => periods.id),
  startDate: date('start_date').notNull(),
  endDate: date('end_date').notNull(),
  isActive: boolean('is_active').default(true),
  createdAt: timestamp('created_at').defaultNow(),
  updatedAt: timestamp('updated_at').defaultNow(),
});

// Tabla de clases/materias
export const classes = pgTable('classes', {
  id: uuid('id').defaultRandom().primaryKey(),
  name: varchar('name', { length: 100 }).notNull(),
  code: varchar('code', { length: 20 }).notNull().unique(),
  credits: integer('credits').notNull(),
  description: text('description'),
  periodId: uuid('period_id').references(() => periods.id),
  isActive: boolean('is_active').default(true),
  createdAt: timestamp('created_at').defaultNow(),
  updatedAt: timestamp('updated_at').defaultNow(),
});

// Tabla de paralelos/secciones
export const sections = pgTable('sections', {
  id: uuid('id').defaultRandom().primaryKey(),
  name: varchar('name', { length: 50 }).notNull(),
  classId: uuid('class_id').references(() => classes.id),
  maxStudents: integer('max_students').notNull(),
  isActive: boolean('is_active').default(true),
  createdAt: timestamp('created_at').defaultNow(),
  updatedAt: timestamp('updated_at').defaultNow(),
});

// Tabla de asignaciones de docentes a clases/secciones
export const teacherAssignments = pgTable('teacher_assignments', {
  id: uuid('id').defaultRandom().primaryKey(),
  teacherId: uuid('teacher_id').references(() => users.id),
  classId: uuid('class_id').references(() => classes.id),
  sectionId: uuid('section_id').references(() => sections.id),
  isActive: boolean('is_active').default(true),
  createdAt: timestamp('created_at').defaultNow(),
});

// Tabla de inscripciones de estudiantes
export const studentEnrollments = pgTable('student_enrollments', {
  id: uuid('id').defaultRandom().primaryKey(),
  studentId: uuid('student_id').references(() => users.id),
  classId: uuid('class_id').references(() => classes.id),
  sectionId: uuid('section_id').references(() => sections.id),
  isActive: boolean('is_active').default(true),
  createdAt: timestamp('created_at').defaultNow(),
});

// Tabla de asistencia
export const attendance = pgTable('attendance', {
  id: uuid('id').defaultRandom().primaryKey(),
  studentId: uuid('student_id').references(() => users.id),
  classId: uuid('class_id').references(() => classes.id),
  sectionId: uuid('section_id').references(() => sections.id),
  teacherId: uuid('teacher_id').references(() => users.id),
  partialId: uuid('partial_id').references(() => partials.id),
  date: date('date').notNull(),
  status: varchar('status', { length: 20 }).notNull(), // 'present', 'absent', 'justified'
  reason: text('reason'), // Motivo de falta o justificación
  createdAt: timestamp('created_at').defaultNow(),
});

// Relaciones
export const usersRelations = relations(users, ({ many }) => ({
  teacherAssignments: many(teacherAssignments),
  studentEnrollments: many(studentEnrollments),
  attendanceAsStudent: many(attendance, { relationName: 'studentAttendance' }),
  attendanceAsTeacher: many(attendance, { relationName: 'teacherAttendance' }),
}));

export const periodsRelations = relations(periods, ({ many }) => ({
  partials: many(partials),
  sections: many(sections),
}));

export const partialsRelations = relations(partials, ({ one }) => ({
  period: one(periods, {
    fields: [partials.periodId],
    references: [periods.id],
  }),
}));

export const classesRelations = relations(classes, ({ one, many }) => ({
  period: one(periods, {
    fields: [classes.periodId],
    references: [periods.id],
  }),
  sections: many(sections),
  teacherAssignments: many(teacherAssignments),
  studentEnrollments: many(studentEnrollments),
  attendance: many(attendance),
}));

export const sectionsRelations = relations(sections, ({ one, many }) => ({
  class: one(classes, {
    fields: [sections.classId],
    references: [classes.id],
  }),
  teacherAssignments: many(teacherAssignments),
  studentEnrollments: many(studentEnrollments),
  attendance: many(attendance),
}));

export const teacherAssignmentsRelations = relations(teacherAssignments, ({ one }) => ({
  teacher: one(users, {
    fields: [teacherAssignments.teacherId],
    references: [users.id],
  }),
  class: one(classes, {
    fields: [teacherAssignments.classId],
    references: [classes.id],
  }),
  section: one(sections, {
    fields: [teacherAssignments.sectionId],
    references: [sections.id],
  }),
}));

export const studentEnrollmentsRelations = relations(studentEnrollments, ({ one }) => ({
  student: one(users, {
    fields: [studentEnrollments.studentId],
    references: [users.id],
  }),
  class: one(classes, {
    fields: [studentEnrollments.classId],
    references: [classes.id],
  }),
  section: one(sections, {
    fields: [studentEnrollments.sectionId],
    references: [sections.id],
  }),
}));

export const attendanceRelations = relations(attendance, ({ one }) => ({
  student: one(users, {
    fields: [attendance.studentId],
    references: [users.id],
    relationName: 'studentAttendance',
  }),
  teacher: one(users, {
    fields: [attendance.teacherId],
    references: [users.id],
    relationName: 'teacherAttendance',
  }),
  class: one(classes, {
    fields: [attendance.classId],
    references: [classes.id],
  }),
  section: one(sections, {
    fields: [attendance.sectionId],
    references: [sections.id],
  }),
  partial: one(partials, {
    fields: [attendance.partialId],
    references: [partials.id],
  }),
}));