import { pgSchema, varchar, timestamp, jsonb, text, index } from "drizzle-orm/pg-core";

export const knoxSchema = pgSchema("knox");

export const employee = knoxSchema.table("employee", {
  epId: varchar("ep_id", { length: 25 }).primaryKey(),
  employeeNumber: varchar("employee_number", { length: 20 }),
  userId: varchar("user_id", { length: 50 }),
  fullName: varchar("full_name", { length: 100 }),
  givenName: varchar("given_name", { length: 100 }),
  sirName: varchar("sir_name", { length: 50 }),
  companyCode: varchar("company_code", { length: 10 }),
  companyName: varchar("company_name", { length: 100 }),
  departmentCode: varchar("department_code", { length: 50 }),
  departmentName: varchar("department_name", { length: 255 }),
  titleCode: varchar("title_code", { length: 20 }),
  titleName: varchar("title_name", { length: 100 }),
  emailAddress: varchar("email_address", { length: 255 }),
  mobile: varchar("mobile", { length: 50 }),
  employeeStatus: varchar("employee_status", { length: 2 }),
  employeeType: varchar("employee_type", { length: 2 }),
  accountStatus: varchar("account_status", { length: 2 }),
  securityLevel: varchar("security_level", { length: 2 }),
  preferredLanguage: varchar("preferred_language", { length: 5 }),
  description: text("description"),
  raw: jsonb("raw").notNull(),
  createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
  updatedAt: timestamp("updated_at", { withTimezone: true }).defaultNow().notNull(),
  enCompanyName: varchar("en_company_name", { length: 100 }),
  enDepartmentName: varchar("en_department_name", { length: 255 }),
  enDiscription: varchar("en_discription", { length: 255 }),
  enFullName: varchar("en_full_name", { length: 100 }),
  enGivenName: varchar("en_given_name", { length: 100 }),
  enGradeName: varchar("en_grade_name", { length: 100 }),
  enSirName: varchar("en_sir_name", { length: 50 }),
  enTitleName: varchar("en_title_name", { length: 100 }),
  gradeName: varchar("grade_name", { length: 100 }),
  gradeTitleIndiCode: varchar("grade_title_indi_code", { length: 2 }),
  jobName: varchar("job_name", { length: 100 }),
  realNameYn: varchar("real_name_yn", { length: 2 }),
  serverLocation: varchar("server_location", { length: 2 }),
  titleSortOrder: varchar("title_sort_order", { length: 10 }),
}, (table) => {
  return {
    companyDepartmentIdx: index("knox_employee_company_department_idx").on(table.companyCode, table.departmentCode),
    employeeNumberIdx: index("knox_employee_number_idx").on(table.employeeNumber),
    userIdIdx: index("knox_employee_user_id_idx").on(table.userId),
    emailIdx: index("knox_employee_email_idx").on(table.emailAddress),
  };
});

export type KnoxEmployee = typeof employee.$inferSelect;
export type NewKnoxEmployee = typeof employee.$inferInsert;