path: root/db/schema/avl/avl.ts

import { pgTable, boolean, integer, timestamp, varchar, decimal, json, pgView, uniqueIndex } from "drizzle-orm/pg-core";
import { createInsertSchema, createSelectSchema } from "drizzle-zod";
import { sql } from "drizzle-orm";

// AVL 리스트 테이블 (프로젝트 AVL 및 선종별 표준 AVL 리스트)
export const avlList = pgTable("avl_list", {
  // 기본 식별자
  id: integer("id").primaryKey().generatedAlwaysAsIdentity(),

  // 구분자
  isTemplate: boolean("is_template").default(false), // false: 프로젝트 AVL, true: 표준 AVL

  // 기본 정보
  constructionSector: varchar("construction_sector", { length: 10 }).notNull(), // 조선/해양
  projectCode: varchar("project_code", { length: 50 }), // 프로젝트코드 (프로젝트 AVL일 경우)
  shipType: varchar("ship_type", { length: 50 }), // 선종
  avlKind: varchar("avl_kind", { length: 50 }), // AVL 종류
  htDivision: varchar("ht_division", { length: 10 }), // H=Hull, T=Top, 공통
  rev: integer("rev").default(1), // 리비전 정보

  // 히스토리 관리
  vendorInfoSnapshot: json("vendor_info_snapshot"), // AVL 생성 시점의 vendorInfo 현황 스냅샷
  
  // 타임스탬프
  createdAt: timestamp("created_at").defaultNow(), // 등재일
  createdBy: varchar("created_by", { length: 50 }), // 등재자
  updatedAt: timestamp("updated_at").defaultNow(), // 최종변경일
  updatedBy: varchar("updated_by", { length: 50 }), // 최종변경자
});

// AVL별 자재그룹 취급사 정보 테이블
export const avlVendorInfo = pgTable("avl_vendor_info", {
  // 기본 식별자
  id: integer("id").primaryKey().generatedAlwaysAsIdentity(),

  isTemplate: boolean("is_template").default(false), // false: 프로젝트 AVL의 레코드임, true: 표준 AVL의 레코드임

  // 표준 AVL용 필드들 (isTemplate=true일 경우)
  constructionSector: varchar("construction_sector", { length: 10 }), // 공사부문 (표준 AVL일 경우)
  shipType: varchar("ship_type", { length: 50 }), // 선종 (표준 AVL일 경우)
  avlKind: varchar("avl_kind", { length: 50 }), // AVL 종류 (표준 AVL일 경우)
  htDivision: varchar("ht_division", { length: 10 }), // H/T 구분 (표준 AVL일 경우)

  projectCode: varchar("project_code", { length: 50 }), // 프로젝트코드 (프로젝트 AVL일 경우)

  // AVL 리스트와의 관계 (자식이 먼저 생기므로.. 제약조건 없는 외래키)
  avlListId: integer("avl_list_id"),

  // 제안방향
  ownerSuggestion: boolean("owner_suggestion").default(false), // 선주 제안사인 경우
  shiSuggestion: boolean("shi_suggestion").default(false), // SHI 제안사인 경우

  // 설계 정보
  equipBulkDivision: varchar("equip_bulk_division", { length: 1 }), // E 또는 B
  disciplineCode: varchar("discipline_code", { length: 10 }), // 설계공종코드
  disciplineName: varchar("discipline_name", { length: 50 }), // 설계공종명

  // 자재 정보
  materialNameCustomerSide: varchar("material_name_customer_side", { length: 100 }), // 고객사 AVL 자재명

  // 패키지 정보
  packageCode: varchar("package_code", { length: 50 }), // 패키지 코드
  packageName: varchar("package_name", { length: 100 }), // 패키지 명

  // 자재그룹 정보
  materialGroupCode: varchar("material_group_code", { length: 50 }), // 자재그룹 코드
  materialGroupName: varchar("material_group_name", { length: 100 }), // 자재그룹 명

  // 협력업체 정보
  vendorId: integer("vendor_id"), // 협력업체 ID (nullable - 없는 경우도 있음)
  vendorName: varchar("vendor_name", { length: 100 }), // 협력업체 명
  vendorCode: varchar("vendor_code", { length: 50 }), // 협력업체 코드

  // AVL 정보
  avlVendorName: varchar("avl_vendor_name", { length: 100 }), // AVL 등재업체명
  tier: varchar("tier", { length: 20 }), // 등급

  // FA 정보
  faTarget: boolean("fa_target").default(false), // FA 대상
  faStatus: varchar("fa_status", { length: 50 }), // FA 현황

  // Agent 정보
  isAgent: boolean("is_agent").default(false), // Agent 여부

  // 계약 서명주체
  contractSignerId: integer("contract_signer_id"), // 계약서명주체 ID (nullable)
  contractSignerName: varchar("contract_signer_name", { length: 100 }), // 계약서명주체 명
  contractSignerCode: varchar("contract_signer_code", { length: 50 }), // 계약서명주체 코드

  // 위치 정보
  headquarterLocation: varchar("headquarter_location", { length: 50 }), // 본사 위치 (국가)
  manufacturingLocation: varchar("manufacturing_location", { length: 50 }), // 제작/선적지 (국가)

  // SHI Qualification
  hasAvl: boolean("has_avl").default(false), // AVL
  isBlacklist: boolean("is_blacklist").default(false), // Blacklist
  isBcc: boolean("is_bcc").default(false), // BCC

  // 기술영업 견적결과
  techQuoteNumber: varchar("tech_quote_number", { length: 50 }), // 기술영업 견적번호
  quoteCode: varchar("quote_code", { length: 50 }), // 견적서 Code
  quoteVendorId: integer("quote_vendor_id"), // 견적 협력업체 ID (nullable)
  quoteVendorName: varchar("quote_vendor_name", { length: 100 }), // 견적 협력업체 명
  quoteVendorCode: varchar("quote_vendor_code", { length: 50 }), // 견적 협력업체 코드
  quoteCountry: varchar("quote_country", { length: 50 }), // 국가
  quoteTotalAmount: decimal("quote_total_amount", { precision: 15, scale: 2 }), // 총 금액
  quoteReceivedDate: varchar("quote_received_date", { length: 10 }), // 견적접수일 (YYYY-MM-DD)

  // 업체 실적 현황
  recentQuoteDate: varchar("recent_quote_date", { length: 10 }), // 최근견적일 (YYYY-MM-DD)
  recentQuoteNumber: varchar("recent_quote_number", { length: 50 }), // 최근견적번호
  recentOrderDate: varchar("recent_order_date", { length: 10 }), // 최근발주일 (YYYY-MM-DD)
  recentOrderNumber: varchar("recent_order_number", { length: 50 }), // 최근발주번호

  // 기타
  remark: varchar("remark", { length: 1000 }), // 비고

  // 타임스탬프
  createdAt: timestamp("created_at").defaultNow(),
  updatedAt: timestamp("updated_at").defaultNow(),
}, (table) => ({
  // 표준 AVL용 unique 제약조건 (isTemplate = true)
  // 표준AVL용 필드들 + 자재그룹코드 + 협력업체명이 unique해야 함
  uniqueStandardAvl: uniqueIndex("unique_standard_avl_material_vendor")
    .on(
      table.constructionSector,
      table.shipType,
      table.avlKind,
      table.htDivision,
      table.materialGroupCode,
      table.vendorName
    )
    .where(sql`${table.isTemplate} = true`),
  
  // 프로젝트 AVL용 unique 제약조건 (isTemplate = false)
  // 같은 projectCode 내에서 자재그룹코드 + 협력업체명이 unique해야 함
  uniqueProjectAvl: uniqueIndex("unique_project_avl_material_vendor")
    .on(
      table.projectCode,
      table.htDivision,
      table.materialGroupCode,
      table.vendorName
    )
    .where(sql`${table.isTemplate} = false`),
}));

// Zod 스키마 생성
export const insertAvlListSchema = createInsertSchema(avlList);
export const selectAvlListSchema = createSelectSchema(avlList);
export const insertAvlVendorInfoSchema = createInsertSchema(avlVendorInfo);
export const selectAvlVendorInfoSchema = createSelectSchema(avlVendorInfo);

// AVL 리스트 집계 뷰 - 가장 마지막 revision별로 집계
export const avlListSummaryView = pgView("avl_list_summary_view", {
  id: integer("id"),
  isTemplate: boolean("is_template"),
  constructionSector: varchar("construction_sector", { length: 10 }),
  projectCode: varchar("project_code", { length: 50 }),
  shipType: varchar("ship_type", { length: 50 }),
  avlKind: varchar("avl_kind", { length: 50 }),
  htDivision: varchar("ht_division", { length: 10 }),
  rev: integer("rev"),
  createdAt: timestamp("created_at"),
  createdBy: varchar("created_by", { length: 50 }),
  updatedAt: timestamp("updated_at"),
  updatedBy: varchar("updated_by", { length: 50 }),
  pkgCount: integer("pkg_count"),
  materialGroupCount: integer("material_group_count"),
  vendorCount: integer("vendor_count"),
  tierCount: integer("tier_count"),
  ownerSuggestionCount: integer("owner_suggestion_count"),
  shiSuggestionCount: integer("shi_suggestion_count"),
}).as(sql`
  WITH latest_revisions AS (
    -- 표준 AVL의 경우: 공사부문, 선종, AVL종류, H/T 구분별 최신 revision 찾기
    SELECT 
      construction_sector,
      ship_type,
      avl_kind,
      ht_division,
      MAX(rev) as max_rev,
      'standard' as avl_type,
      NULL::varchar as project_code
    FROM avl_list 
    WHERE is_template = true
    GROUP BY construction_sector, ship_type, avl_kind, ht_division
    
    UNION ALL
    
    -- 프로젝트 AVL의 경우: 프로젝트 코드별 최신 revision 찾기
    SELECT 
      construction_sector,
      ship_type,
      avl_kind,
      ht_division,
      MAX(rev) as max_rev,
      'project' as avl_type,
      project_code
    FROM avl_list 
    WHERE is_template = false
    GROUP BY project_code, construction_sector, ship_type, avl_kind, ht_division
  ),
  latest_avl_lists AS (
    -- 최신 revision의 실제 AVL 리스트 가져오기
    SELECT 
      al.*,
      CASE 
        WHEN al.is_template = true THEN 'standard'
        ELSE 'project'
      END as avl_type
    FROM avl_list al
    INNER JOIN latest_revisions lr ON (
      (al.is_template = true AND lr.avl_type = 'standard' AND
       al.construction_sector = lr.construction_sector AND
       al.ship_type = lr.ship_type AND
       al.avl_kind = lr.avl_kind AND
       al.ht_division = lr.ht_division AND
       al.rev = lr.max_rev)
      OR
      (al.is_template = false AND lr.avl_type = 'project' AND
       al.project_code = lr.project_code AND
       al.rev = lr.max_rev)
    )
  )
  SELECT 
    al.id,
    al.is_template,
    al.construction_sector,
    al.project_code,
    al.ship_type,
    al.avl_kind,
    al.ht_division,
    al.rev,
    al.created_at,
    al.created_by,
    al.updated_at,
    al.updated_by,
    
    -- 집계 필드들
    COALESCE(COUNT(DISTINCT CASE WHEN avi.package_code IS NOT NULL THEN avi.package_code END), 0)::integer as pkg_count,
    COALESCE(COUNT(DISTINCT CASE WHEN avi.material_group_code IS NOT NULL THEN avi.material_group_code END), 0)::integer as material_group_count,
    COALESCE(COUNT(DISTINCT CASE WHEN avi.vendor_name IS NOT NULL THEN avi.vendor_name END), 0)::integer as vendor_count,
    COALESCE(COUNT(DISTINCT CASE WHEN avi.tier IS NOT NULL THEN avi.tier END), 0)::integer as tier_count,
    COALESCE(SUM(CASE WHEN avi.owner_suggestion = true THEN 1 ELSE 0 END), 0)::integer as owner_suggestion_count,
    COALESCE(SUM(CASE WHEN avi.shi_suggestion = true THEN 1 ELSE 0 END), 0)::integer as shi_suggestion_count
    
  FROM latest_avl_lists al
  LEFT JOIN avl_vendor_info avi ON al.id = avi.avl_list_id
  GROUP BY 
    al.id, al.is_template, al.construction_sector, al.project_code, 
    al.ship_type, al.avl_kind, al.ht_division, al.rev,
    al.created_at, al.created_by, al.updated_at, al.updated_by
  ORDER BY 
    al.is_template ASC, 
    al.construction_sector, 
    al.project_code, 
    al.ship_type, 
    al.avl_kind, 
    al.ht_division,
    al.rev DESC
`);

// 타입 추론
export type AvlList = typeof avlList.$inferSelect;
export type NewAvlList = typeof avlList.$inferInsert;
export type AvlVendorInfo = typeof avlVendorInfo.$inferSelect;
export type NewAvlVendorInfo = typeof avlVendorInfo.$inferInsert;
export type AvlListSummary = typeof avlListSummaryView.$inferSelect;