import {
    pgTable, serial, integer, varchar, text,
    timestamp, uniqueIndex
} from "drizzle-orm/pg-core";
import { vendors } from "./vendors";
import { projects } from "./projects";

export const pqCriterias = pgTable("pq_criterias", {
    id: serial("id").primaryKey(),
    code: varchar("code", { length: 50 }).notNull(),        // 예: "1-1"
    checkPoint: varchar("check_point", { length: 255 }).notNull(),
    description: text("description"),
    remarks: text("remarks"),

    // (선택) "GENERAL", "Quality Management System" 등 큰 분류
    groupName: varchar("group_name", { length: 255 }),
    createdAt: timestamp("created_at").defaultNow().notNull(),
    updatedAt: timestamp("updated_at").defaultNow().notNull(),
});

export const pqCriteriasExtension = pgTable("pq_criterias_extension", {
    id: serial("id").primaryKey(),
  
    // pq_criterias와 연결 (1:1 or 1:N 중 필요에 맞춰 사용)
    pqCriteriaId: integer("pq_criteria_id")
      .notNull()
      .references(() => pqCriterias.id, {
        onDelete: "cascade",
        onUpdate: "cascade",
      }),
  
    // projects 테이블에 대한 FK
    projectId: integer("project_id")
      .notNull()
      .references(() => projects.id, {
        onDelete: "cascade",
        onUpdate: "cascade",
      }),
  
    // 프로젝트별 PQ 시 필요한 추가 정보
    contractInfo: text("contract_info"),
    additionalRequirement: text("additional_requirement"),
  
    createdAt: timestamp("created_at").defaultNow().notNull(),
    updatedAt: timestamp("updated_at").defaultNow().notNull(),
  });



export const vendorCriteriaAttachments = pgTable("vendor_criteria_attachments", {
    id: serial("id").primaryKey(),
    vendorCriteriaAnswerId: integer("vendor_criteria_answer_id")
        .references(() => vendorPqCriteriaAnswers.id, { onDelete: "cascade" })
        .notNull(),

    fileName: varchar("file_name", { length: 255 }).notNull(),
    filePath: varchar("file_path", { length: 1024 }).notNull(),
    fileType: varchar("file_type", { length: 50 }),
    fileSize: integer("file_size"),

    createdAt: timestamp("created_at").defaultNow().notNull(),
    updatedAt: timestamp("updated_at").defaultNow().notNull(),
});

export const vendorPqReviewLogs = pgTable("vendor_pq_review_logs", {
    id: serial("id").primaryKey(),
  
    // Each log references a single vendorPqCriteriaAnswers row
    vendorPqCriteriaAnswerId: integer("vendor_pq_criteria_answer_id")
      .references(() => vendorPqCriteriaAnswers.id, { onDelete: "cascade" })
      .notNull(),
  
    // The reviewer's comment text
    reviewerComment: text("reviewer_comment").notNull(),
  
    // Optionally store the reviewer name or user ID, if you have it
    reviewerName: text("reviewer_name"),
  
    createdAt: timestamp("created_at").defaultNow().notNull(),
  })
export type PqCriterias = typeof pqCriterias.$inferSelect


// 벤더와 프로젝트 PQ 요청 연결 테이블
export const vendorProjectPQs = pgTable("vendor_project_pqs", {
  id: serial("id").primaryKey(),
  
  // vendors 테이블 FK
  vendorId: integer("vendor_id")
    .notNull()
    .references(() => vendors.id, {
      onDelete: "cascade",
      onUpdate: "cascade",
    }),
  
  // projects 테이블 FK
  projectId: integer("project_id")
    .notNull()
    .references(() => projects.id, {
      onDelete: "cascade",
      onUpdate: "cascade",
    }),
  
  // 상태: REQUESTED(요청됨), IN_PROGRESS(진행중), SUBMITTED(제출됨), APPROVED(승인됨), REJECTED(거부됨)
  status: varchar("status", { length: 20 }).notNull().default("REQUESTED"),
  
  // 메타데이터
  submittedAt: timestamp("submitted_at"),
  approvedAt: timestamp("approved_at"),
  rejectedAt: timestamp("rejected_at"),
  rejectReason: text("reject_reason"),
  
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
});

// 기존 vendorPqCriteriaAnswers 테이블에 projectId 필드 추가
export const vendorPqCriteriaAnswers = pgTable("vendor_pq_criteria_answers", {
  id: serial("id").primaryKey(),
  
  vendorId: integer("vendor_id")
    .notNull()
    .references(() => vendors.id, {
      onDelete: "cascade",
      onUpdate: "cascade",
    }),
  
  criteriaId: integer("criteria_id")
    .notNull()
    .references(() => pqCriterias.id, {
      onDelete: "cascade",
      onUpdate: "cascade",
    }),
  
  // 추가: 프로젝트 ID (null은 일반 PQ를 의미)
  projectId: integer("project_id")
    .references(() => projects.id, {
      onDelete: "cascade",
      onUpdate: "cascade",
    }),
  
  answer: text("answer"),
  
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
});