import { pgTable, serial, varchar, text, timestamp, integer, boolean, pgView } from "drizzle-orm/pg-core";
import { sql, eq } from "drizzle-orm";
import { items } 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"
    ] 
  }).default("ACTIVE").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 }),

  // 사업자등록번호
  corporateRegistrationNumber: varchar("corporate_registration_number", {
    length: 100,
  }),
  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 }),
  contactEmail: varchar("contact_email", { length: 255 }).notNull(),
  contactPhone: varchar("contact_phone", { length: 50 }),
  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),
  // itemId: integer("item_id"), // 별도 item 테이블 연동시
  itemCode: varchar("item_code", { length: 100 })
    .notNull()
    .references(() => items.itemCode, { onDelete: "cascade" }),
  itemName: varchar("item_name", { length: 255 }).notNull(),
  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 techVendorItemsView = pgView("tech_vendor_items_view").as((qb) => {
  return qb
    .select({
      vendorItemId: techVendorPossibleItems.id,
      vendorId: techVendorPossibleItems.vendorId,
      itemCode: items.itemCode,
      itemName: items.itemName,
      createdAt: techVendorPossibleItems.createdAt,
      updatedAt: techVendorPossibleItems.updatedAt,
    })
    .from(techVendorPossibleItems)
    .leftJoin(items, eq(techVendorPossibleItems.itemCode, items.itemCode));
});

// 벤더 상세 정보 뷰 (연락처 정보 포함)
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,
      corporateRegistrationNumber: techVendors.corporateRegistrationNumber,
      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,
              'contactEmail', c.contact_email,
              'contactPhone', c.contact_phone,
              'isPrimary', c.is_primary
            )
          ),
          '[]'::json
        )
        FROM 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"),
      
      // 가능 아이템 목록 JSON
      possibleItems: sql<string>`
        (SELECT COALESCE(
          json_agg(
            json_build_object(
              'itemCode', i.item_code,
              'itemName', it.item_name
            )
          ),
          '[]'::json
        )
        FROM tech_vendor_possible_items i
        LEFT JOIN items it ON i.item_code = it.item_code
        WHERE i.vendor_id = tech_vendors.id)
      `.as("possible_items"),
      
      // 아이템 수
      itemCount: sql<number>`
        (SELECT COUNT(*) 
        FROM tech_vendor_possible_items i
        WHERE i.vendor_id = tech_vendors.id)
      `.as("item_count")
    })
    .from(techVendors);
});


export const techVendorCandidates = pgTable("tech_vendor_candidates", {
  id: serial("id").primaryKey(),
  companyName: varchar("company_name", { length: 255 }).notNull(),
  contactEmail: varchar("contact_email", { length: 255 }),
  contactPhone: varchar("contact_phone", { length: 50 }),
  
  taxId: varchar("tax_id", { length: 100 }),
  address: text("address"),

  country: varchar("country", { length: 100 }),
  // 웹 크롤링 등으로 얻은 상태나 분류
  source: varchar("source", { length: 100 }),        // 수집 출처
  status: varchar("status", {
    length: 30,
    enum: [
      "COLLECTED",  // 단순 데이터 수집 완료
      "INVITED",    // 초청 메일 발송
      "DISCARDED",  // 불필요, 검토 후 배제됨
    ],
  })
    .notNull()
    .default("COLLECTED"),
  remark: text("remark"),
  items: text("items").notNull(),
  vendorId: integer("vendor_id")
    .references(() => techVendors.id, { onDelete: "cascade" }),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
});
export const techVendorCandidatesWithVendorInfo = pgView("tech_vendor_candidates_with_vendor_info").as((qb) => {
  return qb
    .select({
      // ----------------------------------------
      // 1) techVendorCandidates 기본 필드
      id: techVendorCandidates.id,
      companyName: techVendorCandidates.companyName,
      contactEmail: techVendorCandidates.contactEmail,
      contactPhone: techVendorCandidates.contactPhone,
      taxId: techVendorCandidates.taxId,
      address: techVendorCandidates.address,
      country: techVendorCandidates.country,
      source: techVendorCandidates.source,
      status: techVendorCandidates.status,
      items: techVendorCandidates.items,
      remark: techVendorCandidates.remark,
      createdAt: techVendorCandidates.createdAt,
      updatedAt: techVendorCandidates.updatedAt,

      // ----------------------------------------
      // 2) techVendors 조인해서 가져올 필드
      vendorName: techVendors.vendorName,
      vendorCode: techVendors.vendorCode,
      vendorCreatedAt: sql<Date>`${techVendors.createdAt}`.as("vendor_created_at"),

    })
    .from(techVendorCandidates)
    .leftJoin(techVendors, eq(techVendorCandidates.vendorId, techVendors.id));
});

export type TechVendorCandidatesWithVendorInfo = typeof techVendorCandidatesWithVendorInfo.$inferSelect;

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 TechVendorItemsView = typeof techVendorItemsView.$inferSelect
export type TechVendorDetailView = typeof techVendorDetailView.$inferSelect
export type TechVendorCandidate = typeof techVendorCandidates.$inferSelect
export type TechVendorWithAttachments = TechVendor & {
  hasAttachments?: boolean;
  attachmentsList?: TechVendorAttach[];
}