/* IMPORT */
import {
  decimal,
  integer,
  pgTable,
  pgView,
  serial,
  text,
  timestamp,
  varchar
} from 'drizzle-orm/pg-core';
import { eq, relations, sql } from 'drizzle-orm';
import { users } from './users';

// ----------------------------------------------------------------------------------------------------

/* CONSTANTS */
const REG_EVAL_CRITERIA_CATEGORY = [
  { label: 'CS', value: 'customer-service' },
  { label: '관리자', value: 'administrator' },
  { label: '구매', value: 'procurement' },
  { label: '설계', value: 'design' },
  { label: '조달', value: 'sourcing' },
  { label: '품질', value: 'quality' },
];
const REG_EVAL_CRITERIA_ITEM = [
  { label: '가점항목', value: 'customer-service' },
  { label: '납기', value: 'delivery' },
  { label: '경영현황', value: 'management-status' },
  { label: '감점항목', value: 'penalty-item' },
  { label: '구매', value: 'procurement' },
  { label: '품질', value: 'quality' },
];
const REG_EVAL_CRITERIA_CATEGORY2 = [
  { label: '공정', value: 'processScore' },
  { label: '가격', value: 'priceScore' },
  { label: '납기', value: 'deliveryScore' },
  { label: '자율평가', value: 'selfEvaluationScore' },
];

const REG_EVAL_CRITERIA_CATEGORY_ENUM = REG_EVAL_CRITERIA_CATEGORY.map(c => c.value) as [string, ...string[]];
const REG_EVAL_CRITERIA_CATEGORY2_ENUM = REG_EVAL_CRITERIA_CATEGORY2.map(c => c.value) as [string, ...string[]];
const REG_EVAL_CRITERIA_ITEM_ENUM = REG_EVAL_CRITERIA_ITEM.map(c => c.value) as [string, ...string[]];

// ----------------------------------------------------------------------------------------------------

/* TABLE SCHEMATA */
const regEvalCriteria = pgTable('reg_eval_criteria', {
  id: serial('id').primaryKey(),
  category: varchar('category', { enum: REG_EVAL_CRITERIA_CATEGORY_ENUM, length: 32 }).default('quality').notNull(),
  category2: varchar('category2', { enum: REG_EVAL_CRITERIA_CATEGORY2_ENUM, length: 32 }).default('processScore').notNull(),
  item: varchar('item', { enum: REG_EVAL_CRITERIA_ITEM_ENUM, length: 32 }).default('quality').notNull(),
  classification: varchar('classification', { length: 255 }).notNull(),
  range: varchar('range', { length: 255 }),
  remarks: text('remarks'),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
  createdBy: integer("created_by")
    .notNull()
    .references(() => users.id, { onDelete: "set null" }),

  updatedBy: integer("updated_by")
    .notNull()
    .references(() => users.id, { onDelete: "set null" }),

});
const regEvalCriteriaDetails = pgTable('reg_eval_criteria_details', {
  id: serial('id').primaryKey(),
  criteriaId: integer('criteria_id')
    .notNull()
    .references(() => regEvalCriteria.id, { onDelete: 'cascade' }),
  detail: text('detail').notNull(),
  orderIndex: integer('order_index').default(0).notNull(),
  scoreEquipShip: decimal('score_equip_ship', { precision: 5, scale: 2 }),
  scoreEquipMarine: decimal('score_equip_marine', { precision: 5, scale: 2 }),
  scoreBulkShip: decimal('score_bulk_ship', { precision: 5, scale: 2 }),
  scoreBulkMarine: decimal('score_bulk_marine', { precision: 5, scale: 2 }),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
});

// ----------------------------------------------------------------------------------------------------

/* VIEWS */
const regEvalCriteriaView = pgView('reg_eval_criteria_view').as((qb) =>
  qb
    .select({
      id: regEvalCriteriaDetails.id,
      criteriaId: regEvalCriteriaDetails.criteriaId,
      category: regEvalCriteria.category,
      scoreCategory: regEvalCriteria.category2,
      item: regEvalCriteria.item,
      classification: regEvalCriteria.classification,
      range: regEvalCriteria.range,
      detail: regEvalCriteriaDetails.detail,
      orderIndex: regEvalCriteriaDetails.orderIndex,
      scoreEquipShip: regEvalCriteriaDetails.scoreEquipShip,
      scoreEquipMarine: regEvalCriteriaDetails.scoreEquipMarine,
      scoreBulkShip: regEvalCriteriaDetails.scoreBulkShip,
      scoreBulkMarine: regEvalCriteriaDetails.scoreBulkMarine,
      remarks: regEvalCriteria.remarks,
    })
    .from(regEvalCriteria)
    .leftJoin(regEvalCriteriaDetails, eq(regEvalCriteria.id, regEvalCriteriaDetails.criteriaId))
    .orderBy(regEvalCriteria.id, regEvalCriteriaDetails.orderIndex)
);

// ----------------------------------------------------------------------------------------------------

/* RELATIONS */
const regEvalCriteriaRelations = relations(regEvalCriteria, ({ many }) => ({
  details: many(regEvalCriteriaDetails),
}));
const regEvalCriteriaDetailsRelations = relations(regEvalCriteriaDetails, ({ one }) => ({
  criteria: one(regEvalCriteria, {
    fields: [regEvalCriteriaDetails.criteriaId],
    references: [regEvalCriteria.id],
  }),
}));

// ----------------------------------------------------------------------------------------------------

/* TYPES */
type RegEvalCriteria = typeof regEvalCriteria.$inferSelect;
type NewRegEvalCriteria = typeof regEvalCriteria.$inferInsert;
type RegEvalCriteriaDetails = typeof regEvalCriteriaDetails.$inferSelect;
type NewRegEvalCriteriaDetails = typeof regEvalCriteriaDetails.$inferInsert;
type RegEvalCriteriaWithDetails = RegEvalCriteria & { criteriaDetails: RegEvalCriteriaDetails[] };
type RegEvalCriteriaView = typeof regEvalCriteriaView.$inferSelect;

// ----------------------------------------------------------------------------------------------------

/* Export */
export {
  REG_EVAL_CRITERIA_CATEGORY,
  REG_EVAL_CRITERIA_CATEGORY_ENUM,
  REG_EVAL_CRITERIA_CATEGORY2,
  REG_EVAL_CRITERIA_CATEGORY2_ENUM,
  REG_EVAL_CRITERIA_ITEM,
  REG_EVAL_CRITERIA_ITEM_ENUM,
  regEvalCriteria,
  regEvalCriteriaDetails,
  regEvalCriteriaDetailsRelations,
  regEvalCriteriaRelations,
  regEvalCriteriaView,
  type NewRegEvalCriteria,
  type NewRegEvalCriteriaDetails,
  type RegEvalCriteriaView,
  type RegEvalCriteriaWithDetails,
  type RegEvalCriteria,
  type RegEvalCriteriaDetails,
};