import { pgTable, pgView, serial, varchar, text, timestamp, boolean, integer, numeric, date, uniqueIndex, unique, decimal } from "drizzle-orm/pg-core";
import { eq , sql, relations} from "drizzle-orm";
import { vendors } from "./vendors";
import { users } from "./users";
import { contracts } from "./contract";

// 평가 대상 메인 테이블
export const evaluationTargets = pgTable("evaluation_targets", {
  id: serial("id").primaryKey(),
  
  // 평가년도 (예: 2024)
  evaluationYear: integer("evaluation_year").notNull(),
  
  // 구분 (해양/조선)
  division: varchar("division", { 
    length: 20,
    enum: ["SHIP", "PLANT"] 
  }).notNull(),
  
  // 벤더 정보
  vendorId: integer("vendor_id").references(() => vendors.id).notNull(),
  vendorCode: varchar("vendor_code", { length: 100 }).notNull(),
  vendorName: varchar("vendor_name", { length: 255 }).notNull(),
  
  // 내외자 구분
  domesticForeign: varchar("domestic_foreign", { 
    length: 20,
    enum: ["DOMESTIC", "FOREIGN"] 
  }).notNull(),
  
  // 자재구분
  materialType: varchar("material_type", { 
    length: 30,
    enum: ["EQUIPMENT", "BULK", "EQUIPMENT_BULK"] 
  }).notNull(),
  
  // 상태
  status: varchar("status", { 
    length: 30,
    enum: ["PENDING", "CONFIRMED", "EXCLUDED"] 
  }).notNull().default("PENDING"),
  
  // 관리자 의견
  adminComment: text("admin_comment"),
  adminUserId: integer("admin_user_id").references(() => users.id),
  
  // 종합 담당자 의견 (각 담당자들의 의견을 모아서 표시)
  consolidatedComment: text("consolidated_comment"),
  
  // 의견 일치 여부 (null: 아직 검토중, true: 일치, false: 불일치)
  consensusStatus: boolean("consensus_status"),
  
  // 최종 확정일
  confirmedAt: timestamp("confirmed_at"),
  confirmedBy: integer("confirmed_by").references(() => users.id),

  ldClaimCount: integer("ld_claim_count").default(0),
  ldClaimAmount: decimal("ld_claim_amount", { precision: 15, scale: 2 }).default("0"),
  ldClaimCurrency: varchar("ld_claim_currency", { 
    length: 10,
    enum: ["KRW", "USD", "EUR", "JPY"]
  }).default("KRW"),
  
  
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
});

// 평가 대상별 담당자 지정 테이블
export const evaluationTargetReviewers = pgTable("evaluation_target_reviewers", {
  id: serial("id").primaryKey(),
  
  evaluationTargetId: integer("evaluation_target_id")
    .references(() => evaluationTargets.id, { onDelete: "cascade" })
    .notNull(),
  

  departmentCode: varchar("department_code", { length: 50 }).notNull(),
  departmentNameFrom: varchar("department_name_from", { length: 50 }),
  
  reviewerUserId: integer("reviewer_user_id")
    .references(() => users.id)
    .notNull(),
  
  // 지정일
  assignedAt: timestamp("assigned_at").defaultNow().notNull(),
  assignedBy: integer("assigned_by").references(() => users.id).notNull(),
  
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
}, (table) => ({
  // 같은 평가대상에 같은 부서는 한 명의 담당자만
  uniqueTargetDepartment: unique("unique_target_department")
    .on(table.evaluationTargetId, table.departmentCode),
}));

// 각 담당자의 평가 대상 검토 결과 테이블
export const evaluationTargetReviews = pgTable("evaluation_target_reviews", {
  id: serial("id").primaryKey(),
  
  evaluationTargetId: integer("evaluation_target_id")
    .references(() => evaluationTargets.id, { onDelete: "cascade" })
    .notNull(),
  
  reviewerUserId: integer("reviewer_user_id")
    .references(() => users.id)
    .notNull(),
  
  // 부서 코드 (API에서 부서 정보 조회용)
  departmentCode: varchar("department_code", { length: 50 }).notNull(),
  
  // 평가 대상 동의 여부
  isApproved: boolean("is_approved"),
  
  // 검토 의견
  reviewComment: text("review_comment"),
  
  // 검토일
  reviewedAt: timestamp("reviewed_at"),
  
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
}, (table) => ({
  // 같은 평가대상에 같은 담당자는 한 번만 검토
  uniqueTargetReviewer: unique("unique_target_reviewer")
    .on(table.evaluationTargetId, table.reviewerUserId),
}));


// 관계 정의
export const evaluationTargetsRelations = relations(evaluationTargets, ({ one, many }) => ({
  vendor: one(vendors, {
    fields: [evaluationTargets.vendorId],
    references: [vendors.id],
  }),
  adminUser: one(users, {
    fields: [evaluationTargets.adminUserId],
    references: [users.id],
  }),
  confirmedByUser: one(users, {
    fields: [evaluationTargets.confirmedBy],
    references: [users.id],
  }),
  reviewers: many(evaluationTargetReviewers),
  reviews: many(evaluationTargetReviews),
}));

export const evaluationTargetReviewersRelations = relations(evaluationTargetReviewers, ({ one }) => ({
  evaluationTarget: one(evaluationTargets, {
    fields: [evaluationTargetReviewers.evaluationTargetId],
    references: [evaluationTargets.id],
  }),
  reviewer: one(users, {
    fields: [evaluationTargetReviewers.reviewerUserId],
    references: [users.id],
  }),
  assignedByUser: one(users, {
    fields: [evaluationTargetReviewers.assignedBy],
    references: [users.id],
  }),
}));

export const evaluationTargetReviewsRelations = relations(evaluationTargetReviews, ({ one }) => ({
  evaluationTarget: one(evaluationTargets, {
    fields: [evaluationTargetReviews.evaluationTargetId],
    references: [evaluationTargets.id],
  }),
  reviewer: one(users, {
    fields: [evaluationTargetReviews.reviewerUserId],
    references: [users.id],
  }),
}));

export const orderRecordsRelations = relations(contracts, ({ one }) => ({
  vendor: one(vendors, {
    fields: [contracts.vendorId],
    references: [vendors.id],
  }),
}));

// 평가 담당 부서 코드 상수 (조직 API와 매핑)
export const EVALUATION_DEPARTMENT_CODES = {
  ORDER_EVAL: "ORDER_EVAL",           // 발주 평가 담당
  PROCUREMENT_EVAL: "PROCUREMENT_EVAL", // 조달 평가 담당
  QUALITY_EVAL: "QUALITY_EVAL",       // 품질 평가 담당
  DESIGN_EVAL: "DESIGN_EVAL",         // 설계 평가 담당
  CS_EVAL: "CS_EVAL",                 // CS 평가 담당
} as const;

export type EvaluationDepartmentCode = keyof typeof EVALUATION_DEPARTMENT_CODES;

  // ============= TypeScript 타입 정의 =============
  
  export type EvaluationTargetStatus = "PENDING" | "CONFIRMED" | "EXCLUDED";
  export type Division = "OCEAN" | "SHIPYARD";
  export type MaterialType = "EQUIPMENT" | "BULK" | "EQUIPMENT_BULK";
  export type DomesticForeign = "DOMESTIC" | "FOREIGN";
  
  export interface EvaluationTargetCreate {
    evaluationYear: number;
    division: Division;
    vendorId: number;
    vendorCode: string;
    vendorName: string;
    domesticForeign: DomesticForeign;
    materialType: MaterialType;
    adminComment?: string;
    managerComment?: string;
  }
  
  export interface ReviewerAssignment {
    departmentId: number;
    reviewerUserId: number;
  }
  
  export interface EvaluationReview {
    evaluationTargetId: number;
    reviewerUserId: number;
    departmentId: number;
    isApproved: boolean;
    reviewComment?: string;
  }
  
  export interface EvaluationTargetWithReviews {
    id: number;
    evaluationYear: number;
    division: Division;
    vendorCode: string;
    vendorName: string;
    domesticForeign: DomesticForeign;
    materialType: MaterialType;
    status: EvaluationTargetStatus;
    consensusStatus: boolean | null;
    adminComment?: string;
    managerComment?: string;
    reviews: {
      departmentName: string;
      reviewerName: string;
      isApproved: boolean | null;
      reviewComment?: string;
      reviewedAt?: Date;
    }[];
  }


  

export type EvaluationTargets = typeof evaluationTargets.$inferSelect;
export type NewEvaluationTargets= typeof evaluationTargets.$inferInsert;


export const evaluationTargetsWithDepartments = pgView("evaluation_targets_with_departments").as((qb) => {
  return qb
    .select({
      // 기본 정보
      id: evaluationTargets.id,
      evaluationYear: evaluationTargets.evaluationYear,
      division: evaluationTargets.division,
      vendorCode: evaluationTargets.vendorCode,
      vendorName: evaluationTargets.vendorName,
      domesticForeign: evaluationTargets.domesticForeign,
      materialType: evaluationTargets.materialType,
      status: evaluationTargets.status,
      consensusStatus: evaluationTargets.consensusStatus,
      adminComment: evaluationTargets.adminComment,
      consolidatedComment: evaluationTargets.consolidatedComment,
      confirmedAt: evaluationTargets.confirmedAt,
      confirmedBy: evaluationTargets.confirmedBy,
      ldClaimCount: evaluationTargets.ldClaimCount,
      ldClaimAmount: evaluationTargets.ldClaimAmount,
      ldClaimCurrency: evaluationTargets.ldClaimCurrency,
      createdAt: evaluationTargets.createdAt,
      updatedAt: evaluationTargets.updatedAt,
      
      // 발주 평가 담당 (ORDER_EVAL)
      orderReviewerName: sql<string | null>`order_reviewer.name`.as('order_reviewer_name'),
      orderReviewerEmail: sql<string | null>`order_reviewer.email`.as('order_reviewer_email'),
      orderDepartmentName: sql<string | null>`order_etr.department_name_from`.as('order_department_name'),
      orderIsApproved: sql<boolean | null>`order_review.is_approved`.as('order_is_approved'),
      orderReviewedAt: sql<Date | null>`order_review.reviewed_at`.as('order_reviewed_at'),
      
      // 조달 평가 담당 (PROCUREMENT_EVAL)
      procurementReviewerName: sql<string | null>`procurement_reviewer.name`.as('procurement_reviewer_name'),
      procurementReviewerEmail: sql<string | null>`procurement_reviewer.email`.as('procurement_reviewer_email'),
      procurementDepartmentName: sql<string | null>`procurement_etr.department_name_from`.as('procurement_department_name'),
      procurementIsApproved: sql<boolean | null>`procurement_review.is_approved`.as('procurement_is_approved'),
      procurementReviewedAt: sql<Date | null>`procurement_review.reviewed_at`.as('procurement_reviewed_at'),
      
      // 품질 평가 담당 (QUALITY_EVAL)
      qualityReviewerName: sql<string | null>`quality_reviewer.name`.as('quality_reviewer_name'),
      qualityReviewerEmail: sql<string | null>`quality_reviewer.email`.as('quality_reviewer_email'),
      qualityDepartmentName: sql<string | null>`quality_etr.department_name_from`.as('quality_department_name'),
      qualityIsApproved: sql<boolean | null>`quality_review.is_approved`.as('quality_is_approved'),
      qualityReviewedAt: sql<Date | null>`quality_review.reviewed_at`.as('quality_reviewed_at'),
      
      // 설계 평가 담당 (DESIGN_EVAL)
      designReviewerName: sql<string | null>`design_reviewer.name`.as('design_reviewer_name'),
      designReviewerEmail: sql<string | null>`design_reviewer.email`.as('design_reviewer_email'),
      designDepartmentName: sql<string | null>`design_etr.department_name_from`.as('design_department_name'),
      designIsApproved: sql<boolean | null>`design_review.is_approved`.as('design_is_approved'),
      designReviewedAt: sql<Date | null>`design_review.reviewed_at`.as('design_reviewed_at'),
      
      // CS 평가 담당 (CS_EVAL)
      csReviewerName: sql<string | null>`cs_reviewer.name`.as('cs_reviewer_name'),
      csReviewerEmail: sql<string | null>`cs_reviewer.email`.as('cs_reviewer_email'),
      csDepartmentName: sql<string | null>`cs_etr.department_name_from`.as('cs_department_name'),
      csIsApproved: sql<boolean | null>`cs_review.is_approved`.as('cs_is_approved'),
      csReviewedAt: sql<Date | null>`cs_review.reviewed_at`.as('cs_reviewed_at'),
    })
    .from(evaluationTargets)
    
    // 발주 평가 담당자 JOIN
    .leftJoin(
      sql`evaluation_target_reviewers order_etr`,
      sql`${evaluationTargets.id} = order_etr.evaluation_target_id AND order_etr.department_code = 'ORDER_EVAL'`
    )
    .leftJoin(
      sql`users order_reviewer`, 
      sql`order_etr.reviewer_user_id = order_reviewer.id`
    )
    .leftJoin(
      sql`evaluation_target_reviews order_review`,
      sql`${evaluationTargets.id} = order_review.evaluation_target_id AND order_review.reviewer_user_id = order_reviewer.id`
    )
    
    // 조달 평가 담당자 JOIN
    .leftJoin(
      sql`evaluation_target_reviewers procurement_etr`,
      sql`${evaluationTargets.id} = procurement_etr.evaluation_target_id AND procurement_etr.department_code = 'PROCUREMENT_EVAL'`
    )
    .leftJoin(
      sql`users procurement_reviewer`, 
      sql`procurement_etr.reviewer_user_id = procurement_reviewer.id`
    )
    .leftJoin(
      sql`evaluation_target_reviews procurement_review`,
      sql`${evaluationTargets.id} = procurement_review.evaluation_target_id AND procurement_review.reviewer_user_id = procurement_reviewer.id`
    )
    
    // 품질 평가 담당자 JOIN
    .leftJoin(
      sql`evaluation_target_reviewers quality_etr`,
      sql`${evaluationTargets.id} = quality_etr.evaluation_target_id AND quality_etr.department_code = 'QUALITY_EVAL'`
    )
    .leftJoin(
      sql`users quality_reviewer`, 
      sql`quality_etr.reviewer_user_id = quality_reviewer.id`
    )
    .leftJoin(
      sql`evaluation_target_reviews quality_review`,
      sql`${evaluationTargets.id} = quality_review.evaluation_target_id AND quality_review.reviewer_user_id = quality_reviewer.id`
    )
    
    // 설계 평가 담당자 JOIN
    .leftJoin(
      sql`evaluation_target_reviewers design_etr`,
      sql`${evaluationTargets.id} = design_etr.evaluation_target_id AND design_etr.department_code = 'DESIGN_EVAL'`
    )
    .leftJoin(
      sql`users design_reviewer`, 
      sql`design_etr.reviewer_user_id = design_reviewer.id`
    )
    .leftJoin(
      sql`evaluation_target_reviews design_review`,
      sql`${evaluationTargets.id} = design_review.evaluation_target_id AND design_review.reviewer_user_id = design_reviewer.id`
    )
    
    // CS 평가 담당자 JOIN
    .leftJoin(
      sql`evaluation_target_reviewers cs_etr`,
      sql`${evaluationTargets.id} = cs_etr.evaluation_target_id AND cs_etr.department_code = 'CS_EVAL'`
    )
    .leftJoin(
      sql`users cs_reviewer`, 
      sql`cs_etr.reviewer_user_id = cs_reviewer.id`
    )
    .leftJoin(
      sql`evaluation_target_reviews cs_review`,
      sql`${evaluationTargets.id} = cs_review.evaluation_target_id AND cs_review.reviewer_user_id = cs_reviewer.id`
    );
});

// 타입 정의
export type EvaluationTargetWithDepartments = typeof evaluationTargetsWithDepartments.$inferSelect;