import { pgTable, varchar, text, integer, numeric, timestamp, boolean, pgEnum, bigint, date, index, pgSchema } from 'drizzle-orm/pg-core';
import { relations } from 'drizzle-orm';

// 定义 library schema
export const librarySchema = pgSchema('library');

// 枚举定义 - 使用 library schema
export const genderEnum = librarySchema.enum('gender_enum', ['M', 'F']);
export const accountStatusEnum = librarySchema.enum('account_status_enum', ['active', 'frozen', 'reported']);
export const bookStatusEnum = librarySchema.enum('book_status_enum', ['normal', 'damaged', 'removed']);
export const borrowStatusEnum = librarySchema.enum('borrow_status_enum', ['borrowed', 'returned', 'overdue']);
export const reservationStatusEnum = librarySchema.enum('reservation_status_enum', ['waiting', 'available', 'expired', 'cancelled']);
export const fineStatusEnum = librarySchema.enum('fine_status_enum', ['unpaid', 'paid']);

// 系统设置表
export const systemSettings = librarySchema.table('system_settings', {
  settingKey: varchar('setting_key', { length: 50 }).primaryKey(),
  settingValue: text('setting_value').notNull(),
  description: text('description'),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
});

// 管理员表
export const admins = librarySchema.table('admins', {
  adminId: bigint('admin_id', { mode: 'number' }).primaryKey().generatedByDefaultAsIdentity(),
  empNo: varchar('emp_no', { length: 20 }).unique().notNull(),
  name: varchar('name', { length: 50 }).notNull(),
  position: varchar('position', { length: 50 }),
  phone: varchar('phone', { length: 20 }),
  email: varchar('email', { length: 100 }),
  passwordHash: varchar('password_hash', { length: 255 }),
  privilegeLv: integer('privilege_lv').default(2).notNull(),
  lastLogin: timestamp('last_login'),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
});

// 图书表
export const books = librarySchema.table('books', {
  bookId: bigint('book_id', { mode: 'number' }).primaryKey().generatedByDefaultAsIdentity(),
  isbn: varchar('isbn', { length: 20 }).unique().notNull(),
  title: varchar('title', { length: 200 }).notNull(),
  authors: text('authors').array().notNull(),
  publisher: varchar('publisher', { length: 100 }),
  publishDate: date('publish_date'),
  price: numeric('price', { precision: 10, scale: 2 }),
  classificationNo: varchar('classification_no', { length: 50 }),
  location: varchar('location', { length: 100 }),
  totalCopies: integer('total_copies').default(1).notNull(),
  availableCopies: integer('available_copies').default(1).notNull(),
  status: bookStatusEnum('status').default('normal').notNull(),
  description: text('description'),
  coverUrl: varchar('cover_url', { length: 500 }),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
}, (table) => ({
  titleIdx: index('idx_books_title').on(table.title),
  authorsIdx: index('idx_books_authors').on(table.authors),
  isbnIdx: index('idx_books_isbn').on(table.isbn),
}));

// 学生表
export const students = librarySchema.table('students', {
  studentId: bigint('student_id', { mode: 'number' }).primaryKey().generatedByDefaultAsIdentity(),
  stuNo: varchar('stu_no', { length: 20 }).unique().notNull(),
  name: varchar('name', { length: 50 }).notNull(),
  gender: genderEnum('gender'),
  department: varchar('department', { length: 100 }),
  major: varchar('major', { length: 100 }),
  grade: varchar('grade', { length: 10 }),
  class: varchar('class', { length: 20 }),
  phone: varchar('phone', { length: 20 }),
  email: varchar('email', { length: 100 }),
  passwordHash: varchar('password_hash', { length: 255 }),
  maxBorrow: integer('max_borrow').default(10).notNull(),
  currentBorrow: integer('current_borrow').default(0).notNull(),
  accountStatus: accountStatusEnum('account_status').default('active').notNull(),
  enrollmentDate: date('enrollment_date'),
  lastLogin: timestamp('last_login'),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
}, (table) => ({
  stuNoIdx: index('idx_students_stu_no').on(table.stuNo),
  nameIdx: index('idx_students_name').on(table.name),
  deptIdx: index('idx_students_department').on(table.department),
}));

// 借阅记录表
export const borrowRecords = librarySchema.table('borrow_records', {
  borrowId: bigint('borrow_id', { mode: 'number' }).primaryKey().generatedByDefaultAsIdentity(),
  bookId: bigint('book_id', { mode: 'number' }).references(() => books.bookId, { onDelete: 'cascade' }).notNull(),
  studentId: bigint('student_id', { mode: 'number' }).references(() => students.studentId, { onDelete: 'cascade' }).notNull(),
  borrowDate: date('borrow_date').defaultNow().notNull(),
  dueDate: date('due_date').notNull(),
  returnDate: date('return_date'),
  renewTimes: integer('renew_times').default(0).notNull(),
  status: borrowStatusEnum('status').default('borrowed').notNull(),
  fineAmount: numeric('fine_amount', { precision: 10, scale: 2 }).default('0').notNull(),
  notes: text('notes'),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
}, (table) => ({
  bookIdIdx: index('idx_borrow_records_book_id').on(table.bookId),
  studentIdIdx: index('idx_borrow_records_student_id').on(table.studentId),
  statusIdx: index('idx_borrow_records_status').on(table.status),
  dueDateIdx: index('idx_borrow_records_due_date').on(table.dueDate),
}));

// 预约记录表
export const reservations = librarySchema.table('reservations', {
  reservationId: bigint('reservation_id', { mode: 'number' }).primaryKey().generatedByDefaultAsIdentity(),
  bookId: bigint('book_id', { mode: 'number' }).references(() => books.bookId, { onDelete: 'cascade' }).notNull(),
  studentId: bigint('student_id', { mode: 'number' }).references(() => students.studentId, { onDelete: 'cascade' }).notNull(),
  reserveDate: timestamp('reserve_date').defaultNow().notNull(),
  expiryDate: timestamp('expiry_date'),
  status: reservationStatusEnum('status').default('waiting').notNull(),
  notified: boolean('notified').default(false).notNull(),
  notes: text('notes'),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
}, (table) => ({
  bookIdIdx: index('idx_reservations_book_id').on(table.bookId),
  studentIdIdx: index('idx_reservations_student_id').on(table.studentId),
  statusIdx: index('idx_reservations_status').on(table.status),
}));

// 罚款记录表
export const fines = librarySchema.table('fines', {
  fineId: bigint('fine_id', { mode: 'number' }).primaryKey().generatedByDefaultAsIdentity(),
  studentId: bigint('student_id', { mode: 'number' }).references(() => students.studentId, { onDelete: 'cascade' }).notNull(),
  borrowId: bigint('borrow_id', { mode: 'number' }).references(() => borrowRecords.borrowId, { onDelete: 'cascade' }),
  fineType: varchar('fine_type', { length: 50 }).default('overdue').notNull(),
  amount: numeric('amount', { precision: 10, scale: 2 }).notNull(),
  reason: text('reason'),
  status: fineStatusEnum('status').default('unpaid').notNull(),
  paymentDate: timestamp('payment_date'),
  paymentMethod: varchar('payment_method', { length: 50 }),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
}, (table) => ({
  studentIdIdx: index('idx_fines_student_id').on(table.studentId),
  statusIdx: index('idx_fines_status').on(table.status),
}));

// 评价记录表
export const reviews = librarySchema.table('reviews', {
  reviewId: bigint('review_id', { mode: 'number' }).primaryKey().generatedByDefaultAsIdentity(),
  bookId: bigint('book_id', { mode: 'number' }).references(() => books.bookId, { onDelete: 'cascade' }).notNull(),
  studentId: bigint('student_id', { mode: 'number' }).references(() => students.studentId, { onDelete: 'cascade' }).notNull(),
  rating: integer('rating').notNull(),
  comment: text('comment'),
  reviewTime: timestamp('review_time').defaultNow().notNull(),
}, (table) => ({
  bookIdIdx: index('idx_reviews_book_id').on(table.bookId),
  studentIdIdx: index('idx_reviews_student_id').on(table.studentId),
}));

// 关系定义
export const booksRelations = relations(books, ({ many }) => ({
  borrowRecords: many(borrowRecords),
  reservations: many(reservations),
  reviews: many(reviews),
}));

export const studentsRelations = relations(students, ({ many }) => ({
  borrowRecords: many(borrowRecords),
  reservations: many(reservations),
  fines: many(fines),
  reviews: many(reviews),
}));

export const borrowRecordsRelations = relations(borrowRecords, ({ one, many }) => ({
  book: one(books, {
    fields: [borrowRecords.bookId],
    references: [books.bookId],
  }),
  student: one(students, {
    fields: [borrowRecords.studentId],
    references: [students.studentId],
  }),
  fines: many(fines),
}));

export const reservationsRelations = relations(reservations, ({ one }) => ({
  book: one(books, {
    fields: [reservations.bookId],
    references: [books.bookId],
  }),
  student: one(students, {
    fields: [reservations.studentId],
    references: [students.studentId],
  }),
}));

export const finesRelations = relations(fines, ({ one }) => ({
  student: one(students, {
    fields: [fines.studentId],
    references: [students.studentId],
  }),
  borrowRecord: one(borrowRecords, {
    fields: [fines.borrowId],
    references: [borrowRecords.borrowId],
  }),
}));

export const reviewsRelations = relations(reviews, ({ one }) => ({
  book: one(books, {
    fields: [reviews.bookId],
    references: [books.bookId],
  }),
  student: one(students, {
    fields: [reviews.studentId],
    references: [students.studentId],
  }),
}));
