path: root/db/schema/basicContractDocumnet.ts

import { pgTable, pgView, text, timestamp, integer, varchar, boolean , unique, date} from 'drizzle-orm/pg-core';
import { vendors } from './vendors';
import { eq, sql } from "drizzle-orm";
import { users } from './users';
import { biddingCompanies } from './bidding';
import { rfqLastVendorResponses } from './rfqVendor';
import { rfqLastDetails } from './rfqLast';
import { generalContracts } from './generalContract';
import { legalWorks } from './legal';

export const basicContractTemplates = pgTable('basic_contract_templates', {
  id: integer("id").primaryKey().generatedAlwaysAsIdentity(),
  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 }),
  filePath: varchar("file_path", { length: 1024 }),
  validityPeriod: integer('validity_period'), // 계약 유효기간(개월)
  
  // 법무검토 관련
  legalReviewRequired: boolean('legal_review_required').notNull().default(false), // 법무검토 필요 여부
  
  // 감사 정보
  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'), // 복구일자
}, (table) => ({
  // templateName과 revision 조합이 유니크해야 함
  templateNameRevisionUnique: unique("template_name_revision_unique").on(table.templateName, table.revision),
}));



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),
  //biddingCompanies fk 추가, nullable
  biddingCompanyId: integer('bidding_company_id').references(() => biddingCompanies.id),
  rfqCompanyId: integer('rfq_company_id').references(() => rfqLastDetails.id),
  generalContractId: integer('general_contract_id').references(() => generalContracts.id),
  requestedBy: integer('requested_by').references(() => users.id),
  
  // 상태값을 더 세분화
  status: text('status').notNull().default('PENDING'), 
  // PENDING: 초기 발송 상태
  // VENDOR_SIGNED: 협력업체 서명 완료
  // BUYER_SIGNED: 구매팀 서명 완료 (협력업체 서명 후)
  // LEGAL_REVIEW_REQUESTED: 법무검토 요청
  // LEGAL_REVIEW_COMPLETED: 법무검토 완료
  // COMPLETED: 모든 과정 완료
  // REJECTED: 거절됨
  
  fileName: varchar("file_name", { length: 255 }).notNull(),
  filePath: varchar("file_path", { length: 1024 }).notNull(),
  
  // 새로 추가되는 필드들
  deadline: date('deadline').notNull(), // 마감일 (요청일로부터 10일 후)
  vendorSignedAt: timestamp('vendor_signed_at'), // 협력업체 서명일
  buyerSignedAt: timestamp('buyer_signed_at'), // 구매팀 서명일
  negotiationCompletedAt: timestamp('negotiation_completed_at'), // GTC 협의 완료일
  legalReviewRequestedAt: timestamp('legal_review_requested_at'), // 법무검토 요청일
  legalReviewCompletedAt: timestamp('legal_review_completed_at'), // 법무검토 완료일
  legalReviewRegNo: varchar('legal_review_reg_no', { length: 100 }), // 법무 시스템 REG_NO
  legalReviewProgressStatus: varchar('legal_review_progress_status', { length: 255 }), // PRGS_STAT_DSC 값
  
  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'),

      //견적,입찰, 계약
      rfqCompanyId: sql<number | null>`${basicContract.rfqCompanyId}`.as('rfq_company_id'),
      biddingCompanyId: sql<number | null>`${basicContract.biddingCompanyId}`.as('bidding_company_id'),
      generalContractId: sql<number | null>`${basicContract.generalContractId}`.as('general_contract_id'),

      
      // 새로 추가된 필드들
      deadline: sql<string | null>`${basicContract.deadline}`.as('deadline'),
      vendorSignedAt: sql<Date | null>`${basicContract.vendorSignedAt}`.as('vendor_signed_at'),
      buyerSignedAt: sql<Date | null>`${basicContract.buyerSignedAt}`.as('buyer_signed_at'),
      negotiationCompletedAt: sql<Date | null>`${basicContract.negotiationCompletedAt}`.as('negotiation_completed_at'),
      legalReviewRequestedAt: sql<Date | null>`${basicContract.legalReviewRequestedAt}`.as('legal_review_requested_at'),
      legalReviewCompletedAt: sql<Date | null>`${basicContract.legalReviewCompletedAt}`.as('legal_review_completed_at'),
      legalReviewRegNo: sql<string | null>`${basicContract.legalReviewRegNo}`.as('legal_review_reg_no'),
      legalReviewProgressStatus: sql<string | null>`${basicContract.legalReviewProgressStatus}`.as('legal_review_progress_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'),
      
      // 템플릿 정보
      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'),

      // 법무검토 상태 (PRGS_STAT_DSC 동기화 값)
      legalReviewStatus: sql<string | null>`${basicContract.legalReviewProgressStatus}`.as('legal_review_status'),

      // 템플릿 파일 정보
      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;

export const basicContractTemplateStatsView = pgView('basic_contract_template_stats_view').as((qb) => {
  return qb
    .select({
      // 템플릿 기본 정보
      templateId: sql<number>`${basicContractTemplates.id}`.as('template_id'),
      templateName: sql<string>`${basicContractTemplates.templateName}`.as('template_name'),
      revision: sql<number>`${basicContractTemplates.revision}`.as('revision'),
      legalReviewRequired: sql<boolean>`${basicContractTemplates.legalReviewRequired}`.as('legal_review_required'),
      validityPeriod: sql<number | null>`${basicContractTemplates.validityPeriod}`.as('validity_period'),
      templateCreatedAt: sql<Date>`${basicContractTemplates.createdAt}`.as('template_created_at'),
      
      // 통계 정보
      // 발송건수 (전체 basicContract 수)
      totalSentCount: sql<number>`COUNT(${basicContract.id})`.as('total_sent_count'),
      
      // 지연건수 (데드라인이 지났는데 완료되지 않은 것)
      overdueCount: sql<number>`
        COUNT(CASE 
          WHEN ${basicContract.deadline} < CURRENT_DATE 
               AND ${basicContract.status} NOT IN ('COMPLETED', 'REJECTED') 
          THEN 1 
        END)
      `.as('overdue_count'),
      
      // 미서명건수 (PENDING 상태)
      unsignedCount: sql<number>`
        COUNT(CASE 
          WHEN ${basicContract.status} = 'PENDING' 
          THEN 1 
        END)
      `.as('unsigned_count'),
      
      // 서명완료건수 (협력업체만 - VENDOR_SIGNED 이상)
      vendorSignedCount: sql<number>`
        COUNT(CASE 
          WHEN ${basicContract.status} IN ('VENDOR_SIGNED', 'BUYER_SIGNED', 'LEGAL_REVIEW_REQUESTED', 'LEGAL_REVIEW_COMPLETED', 'COMPLETED') 
          THEN 1 
        END)
      `.as('vendor_signed_count'),
      
      // 법무요청건수
      legalRequestCount: sql<number>`
        COUNT(CASE 
          WHEN ${basicContract.status} IN ('LEGAL_REVIEW_REQUESTED', 'LEGAL_REVIEW_COMPLETED') 
          THEN 1 
        END)
      `.as('legal_request_count'),
      
      // 법무완료건수
      legalCompletedCount: sql<number>`
        COUNT(CASE 
          WHEN ${basicContract.status} = 'LEGAL_REVIEW_COMPLETED' 
               OR (${basicContract.status} = 'COMPLETED' AND ${basicContract.legalReviewCompletedAt} IS NOT NULL)
          THEN 1 
        END)
      `.as('legal_completed_count'),
      
      // 계약완료건수
      contractCompletedCount: sql<number>`
        COUNT(CASE 
          WHEN ${basicContract.status} = 'COMPLETED' 
          THEN 1 
        END)
      `.as('contract_completed_count'),
      
      // 추가 유용한 통계
      // 구매팀 서명완료건수
      buyerSignedCount: sql<number>`
        COUNT(CASE 
          WHEN ${basicContract.status} IN ('BUYER_SIGNED', 'LEGAL_REVIEW_REQUESTED', 'LEGAL_REVIEW_COMPLETED', 'COMPLETED') 
          THEN 1 
        END)
      `.as('buyer_signed_count'),
      
      // 거절건수
      rejectedCount: sql<number>`
        COUNT(CASE 
          WHEN ${basicContract.status} = 'REJECTED' 
          THEN 1 
        END)
      `.as('rejected_count'),
      
      // 평균 처리시간 (완료된 건만)
      avgProcessingDays: sql<number>`
        AVG(CASE 
          WHEN ${basicContract.status} = 'COMPLETED' AND ${basicContract.completedAt} IS NOT NULL
          THEN EXTRACT(EPOCH FROM (${basicContract.completedAt} - ${basicContract.createdAt})) / 86400
        END)
      `.as('avg_processing_days'),
      
      // 최근 활동일
      lastActivityDate: sql<Date | null>`MAX(${basicContract.updatedAt})`.as('last_activity_date'),
    })
    .from(basicContractTemplates)
    .leftJoin(basicContract, eq(basicContractTemplates.id, basicContract.templateId))
    .where(eq(basicContractTemplates.status, 'ACTIVE'))
    .groupBy(
      basicContractTemplates.id,
      basicContractTemplates.templateName,
      basicContractTemplates.revision,
      basicContractTemplates.legalReviewRequired,
      basicContractTemplates.validityPeriod,
      basicContractTemplates.createdAt
    );
});

export type BasicContractTemplateStatsView = typeof basicContractTemplateStatsView.$inferSelect;


export const buyerSignatures = pgTable('buyer_signatures', {
  id: integer("id").primaryKey().generatedAlwaysAsIdentity(),
  name: varchar('name', { length: 255 }).notNull().default('삼성중공업'),
  imageUrl: text('image_url').notNull(),
  dataUrl: text('data_url'), // Base64 데이터
  mimeType: varchar('mime_type', { length: 100 }),
  fileSize: integer('file_size'),
  isActive: boolean('is_active').default(true),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  updatedAt: timestamp('updated_at').defaultNow().notNull(),
});

export type BuyerSignature = typeof buyerSignatures.$inferSelect;
export type NewBuyerSignature = typeof buyerSignatures.$inferInsert;