import { pgTable, serial, varchar, text, timestamp, integer, boolean, pgView } from "drizzle-orm/pg-core";
import { sql } from "drizzle-orm";
import { itemShipbuilding } from "./items";
import { itemOffshoreTop } from "./items";
import { itemOffshoreHull } from "./items";

// 벤더 타입 enum 정의
export const VENDOR_TYPES = ["조선", "해양TOP", "해양HULL"] as const;
export type TechVendorType = typeof VENDOR_TYPES[number];

// 기술영업 벤더 테이블
export const techVendors = pgTable("tech_vendors", {
  id: serial("id").primaryKey(),
  vendorName: varchar("vendor_name", { length: 255 }).notNull(),
  vendorCode: varchar("vendor_code", { length: 100 }),
  taxId: varchar("tax_id", { length: 100 }),
  country: varchar("country", { length: 100 }),
  countryEng: varchar("country_eng", { length: 100 }),
  countryFab: varchar("country_fab", { length: 100 }),
  agentName: varchar("agent_name", { length: 255 }),
  agentPhone: varchar("agent_phone", { length: 50 }),
  agentEmail: varchar("agent_email", { length: 255 }),
  address: varchar("address", { length: 255 }),
  phone: varchar("phone", { length: 50 }),
  email: varchar("email", { length: 255 }),
  website: varchar("website", { length: 255 }),
  // 벤더 타입을 다중 선택 가능하도록 text 타입으로 변경 (JSON 배열 저장)
  techVendorType: text("tech_vendor_type").notNull(),
  // 상태 필드 - 더 많은 상태 추가
  status: varchar("status", { 
    length: 30,
    enum: [
      "ACTIVE", 
      "INACTIVE", 
      "BLACKLISTED",
      "PENDING_INVITE",
      "INVITED",
      "QUOTE_COMPARISON"
    ] 
  }).default("ACTIVE").notNull(),
  // 견적비교용 벤더 여부
  isQuoteComparison: boolean("is_quote_comparison").default(false).notNull(),
  // 대표자 정보
  representativeName: varchar("representative_name", { length: 255 }),
  representativeEmail: varchar("representative_email", { length: 255 }),
  representativePhone: varchar("representative_phone", { length: 50 }),
  representativeBirth: varchar("representative_birth", { length: 20 }),
  // 아이템 정보 (legacy 지원)
  items: text("items"),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
});
export const techVendorContacts = pgTable("tech_vendor_contacts", {
  id: serial("id").primaryKey(),
  vendorId: integer("vendor_id").notNull().references(() => techVendors.id),
  contactName: varchar("contact_name", { length: 255 }).notNull(),
  contactPosition: varchar("contact_position", { length: 100 }),
  contactTitle: varchar("contact_title", { length: 100 }),
  contactEmail: varchar("contact_email", { length: 255 }).notNull(),
  contactPhone: varchar("contact_phone", { length: 50 }),
  contactCountry: varchar("contact_country", { length: 100 }),
  isPrimary: boolean("is_primary").default(false).notNull(),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
});
export const techVendorPossibleItems = pgTable("tech_vendor_possible_items", {
  id: serial("id").primaryKey(),
  
  // 벤더 정보
  vendorId: integer("vendor_id").notNull().references(() => techVendors.id),
  shipbuildingItemId: integer("shipbuilding_item_id").references(() => itemShipbuilding.id),
  offshoreTopItemId: integer("offshore_top_item_id").references(() => itemOffshoreTop.id),
  offshoreHullItemId: integer("offshore_hull_item_id").references(() => itemOffshoreHull.id),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
});
export const techVendorAttachments = pgTable("tech_vendor_attachments", {
  id: serial("id").primaryKey(),
  vendorId: integer("vendor_id").references(() => techVendors.id),
  fileName: varchar("file_name", { length: 255 }).notNull(),
  filePath: varchar("file_path", { length: 1024 }).notNull(),
  attachmentType: varchar("attachment_type", {
    length: 50,
  }).default("GENERAL"),

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

// 벤더 상세 정보 뷰 (연락처 정보 포함)
export const techVendorDetailView = pgView("tech_vendor_detail_view").as((qb) => {
  return qb
    .select({
      // 협력업체 기본 정보
      id: techVendors.id,
      vendorName: techVendors.vendorName,
      vendorCode: techVendors.vendorCode,
      taxId: techVendors.taxId,
      address: techVendors.address,
      country: techVendors.country,
      countryEng: techVendors.countryEng,
      countryFab: techVendors.countryFab,
      agentName: techVendors.agentName,
      agentPhone: techVendors.agentPhone,
      agentEmail: techVendors.agentEmail,
      phone: techVendors.phone,
      email: techVendors.email,
      website: techVendors.website,
      status: techVendors.status,
      techVendorType: techVendors.techVendorType,
      representativeName: techVendors.representativeName,
      representativeEmail: techVendors.representativeEmail,
      representativePhone: techVendors.representativePhone,
      representativeBirth: techVendors.representativeBirth,
      createdAt: techVendors.createdAt,
      updatedAt: techVendors.updatedAt,
      
      // 연락처 정보 JSON
      contacts: sql<string>`
        (SELECT COALESCE(
          json_agg(
            json_build_object(
              'id', c.id,
              'contactName', c.contact_name,
              'contactPosition', c.contact_position,
              'contactTitle', c.contact_title,
              'contactEmail', c.contact_email,
              'contactPhone', c.contact_phone,
              'isPrimary', c.is_primary
            )
          ),
          '[]'::json
        )
        FROM tech_vendor_contacts c
        WHERE c.vendor_id = tech_vendors.id)
      `.as("contacts"),
      // 첨부파일 정보 (수정된 버전)
      attachments: sql<string>`
      (SELECT COALESCE(
        json_agg(
          json_build_object(
            'id', a.id,
            'fileName', a.file_name,
            'filePath', a.file_path,
            'attachmentType', a.attachment_type,
            'createdAt', a.created_at
          )
          ORDER BY a.attachment_type, a.created_at DESC
        ),
        '[]'::json
      )
      FROM tech_vendor_attachments a
      WHERE a.vendor_id = tech_vendors.id)
    `.as("attachments"),
      // 첨부파일 수 (수정된 버전)
      attachmentCount: sql<number>`
      (SELECT COUNT(*)
      FROM tech_vendor_attachments a
      WHERE a.vendor_id = tech_vendors.id)
    `.as("attachment_count"),
      // 연락처 수
      contactCount: sql<number>`
        (SELECT COUNT(*) 
        FROM vendor_contacts c
        WHERE c.vendor_id = tech_vendors.id)
      `.as("contact_count"),
      
      // 아이템 수
      itemCount: sql<number>`
        (SELECT COUNT(*) 
        FROM tech_vendor_possible_items i
        WHERE i.vendor_id = tech_vendors.id)
      `.as("item_count")
    })
    .from(techVendors);
});


export type TechVendor = typeof techVendors.$inferSelect
export type TechVendorContact = typeof techVendorContacts.$inferSelect
export type TechVendorItem = typeof techVendorPossibleItems.$inferSelect
export type TechVendorAttach = typeof techVendorAttachments.$inferSelect
export type TechVendorDetailView = typeof techVendorDetailView.$inferSelect
export type TechVendorWithAttachments = TechVendor & {
  hasAttachments?: boolean;
  attachmentsList?: TechVendorAttach[];
  workTypes?: string;
}