import { pgTable, pgView, text, timestamp, integer, varchar, boolean } from 'drizzle-orm/pg-core';
import { vendors } from './vendors';
import { eq, sql } from "drizzle-orm";
import { users } from './users';

export const basicContractTemplates = pgTable('basic_contract_templates', {
  id: integer("id").primaryKey().generatedAlwaysAsIdentity(),
  templateCode: varchar("template_code", { length: 50 }).notNull().unique(), // 템플릿 코드
  templateName: text('template_name').notNull(),
  revision: integer('revision').notNull().default(1), // 리비전
  status: text('status').notNull().default('ACTIVE'), // ACTIVE, DISPOSED
  fileName: varchar("file_name", { length: 255 }).notNull(),
  filePath: varchar("file_path", { length: 1024 }).notNull(),
  validityPeriod: integer('validity_period'), // 계약 유효기간(개월)
  
  // 법무검토 관련
  legalReviewRequired: boolean('legal_review_required').notNull().default(false), // 법무검토 필요 여부
  
  // 적용 범위 (각 사업부별)
  shipBuildingApplicable: boolean('shipbuilding_applicable').notNull().default(false), // 조선해양
  windApplicable: boolean('wind_applicable').notNull().default(false), // 풍력
  pcApplicable: boolean('pc_applicable').notNull().default(false), // PC
  nbApplicable: boolean('nb_applicable').notNull().default(false), // NB
  rcApplicable: boolean('rc_applicable').notNull().default(false), // RC
  gyApplicable: boolean('gy_applicable').notNull().default(false), // GY
  sysApplicable: boolean('sys_applicable').notNull().default(false), // S&Sys
  infraApplicable: boolean('infra_applicable').notNull().default(false), // Infra
  
  // 감사 정보
  createdAt: timestamp('created_at').defaultNow(),
  createdBy: integer('created_by').references(() => users.id), // 생성자
  updatedAt: timestamp('updated_at').defaultNow(),
  updatedBy: integer('updated_by').references(() => users.id), // 수정자
  disposedAt: timestamp('disposed_at'), // 폐기일자
  restoredAt: timestamp('restored_at'), // 복구일자
});


export const basicContract = pgTable('basic_contract', {
  id: integer("id").primaryKey().generatedAlwaysAsIdentity(),
  templateId: integer('template_id').references(() => basicContractTemplates.id),
  vendorId: integer('vendor_id').references(() => vendors.id),
  requestedBy: integer('requested_by').references(() => users.id),
  status: text('status').notNull().default('PENDING'), // PENDING, COMPLETED, REJECTED
  fileName: varchar("file_name", { length: 255 }).notNull(),
  filePath: varchar("file_path", { length: 1024 }).notNull(),
  createdAt: timestamp('created_at').defaultNow(),
  updatedAt: timestamp('updated_at').defaultNow(),
  completedAt: timestamp('completed_at'), // 계약 체결 완료 날짜 - 새로 추가할 필드

});


// 기본 계약 요청 뷰
export const basicContractView = pgView('basic_contract_view').as((qb) => {
  return qb
    .select({
      // 기본 계약 정보
      id: sql<number>`${basicContract.id}`.as('id'),
      templateId: sql<number | null>`${basicContract.templateId}`.as('template_id'),
      vendorId: sql<number | null>`${basicContract.vendorId}`.as('vendor_id'),
      requestedBy: sql<number | null>`${basicContract.requestedBy}`.as('requested_by'),
      status: sql<string>`${basicContract.status}`.as('basic_contract_status'),
      createdAt: sql<Date>`${basicContract.createdAt}`.as('created_at'),
      updatedAt: sql<Date>`${basicContract.updatedAt}`.as('updated_at'),
      completedAt: sql<Date | null>`${basicContract.completedAt}`.as('completed_at'),
      
      // 벤더 정보
      vendorCode: sql<string | null>`${vendors.vendorCode}`.as('vendor_code'),
      vendorEmail: sql<string | null>`${vendors.email}`.as('vendor_email'),
      vendorName: sql<string | null>`${vendors.vendorName}`.as('vendor_name'),
      
      // 요청자 정보
      requestedByName: sql<string | null>`${users.name}`.as('requested_by_name'),
      
      // 템플릿 정보 (확장된 필드들)
      templateCode: sql<string | null>`${basicContractTemplates.templateCode}`.as('template_code'),
      templateName: sql<string | null>`${basicContractTemplates.templateName}`.as('template_name'),
      templateRevision: sql<number | null>`${basicContractTemplates.revision}`.as('template_revision'),
      templateStatus: sql<string | null>`${basicContractTemplates.status}`.as('template_status'),
      validityPeriod: sql<number | null>`${basicContractTemplates.validityPeriod}`.as('validity_period'),
      legalReviewRequired: sql<boolean | null>`${basicContractTemplates.legalReviewRequired}`.as('legal_review_required'),
      
      // 적용 범위 정보
      shipBuildingApplicable: sql<boolean | null>`${basicContractTemplates.shipBuildingApplicable}`.as('shipbuilding_applicable'),
      windApplicable: sql<boolean | null>`${basicContractTemplates.windApplicable}`.as('wind_applicable'),
      pcApplicable: sql<boolean | null>`${basicContractTemplates.pcApplicable}`.as('pc_applicable'),
      nbApplicable: sql<boolean | null>`${basicContractTemplates.nbApplicable}`.as('nb_applicable'),
      rcApplicable: sql<boolean | null>`${basicContractTemplates.rcApplicable}`.as('rc_applicable'),
      gyApplicable: sql<boolean | null>`${basicContractTemplates.gyApplicable}`.as('gy_applicable'),
      sysApplicable: sql<boolean | null>`${basicContractTemplates.sysApplicable}`.as('sys_applicable'),
      infraApplicable: sql<boolean | null>`${basicContractTemplates.infraApplicable}`.as('infra_applicable'),
      
      // 템플릿 파일 정보
      templateFilePath: sql<string | null>`${basicContractTemplates.filePath}`.as('template_file_path'),
      templateFileName: sql<string | null>`${basicContractTemplates.fileName}`.as('template_file_name'),
      
      // 서명된 계약서 파일 정보
      signedFilePath: sql<string | null>`${basicContract.filePath}`.as('signed_file_path'),
      signedFileName: sql<string | null>`${basicContract.fileName}`.as('signed_file_name'),
      
      // 템플릿 감사 정보
      templateCreatedAt: sql<Date | null>`${basicContractTemplates.createdAt}`.as('template_created_at'),
      templateCreatedBy: sql<number | null>`${basicContractTemplates.createdBy}`.as('template_created_by'),
      templateUpdatedAt: sql<Date | null>`${basicContractTemplates.updatedAt}`.as('template_updated_at'),
      templateUpdatedBy: sql<number | null>`${basicContractTemplates.updatedBy}`.as('template_updated_by'),
      templateDisposedAt: sql<Date | null>`${basicContractTemplates.disposedAt}`.as('template_disposed_at'),
      templateRestoredAt: sql<Date | null>`${basicContractTemplates.restoredAt}`.as('template_restored_at'),
    })
    .from(basicContract)
    .leftJoin(vendors, eq(basicContract.vendorId, vendors.id))
    .leftJoin(users, eq(basicContract.requestedBy, users.id))
    .leftJoin(basicContractTemplates, eq(basicContract.templateId, basicContractTemplates.id));
});

// 타입 정의
export type BasicContractTemplate = typeof basicContractTemplates.$inferSelect;
export type BasicContract = typeof basicContract.$inferSelect;
export type BasicContractView = typeof basicContractView.$inferSelect;