// db/schema/vendors.ts
import { pgTable, serial, varchar, text, timestamp, boolean, integer ,pgView} from "drizzle-orm/pg-core";
import { items } from "./items";
import {  eq} from "drizzle-orm";

export const vendors = pgTable("vendors", {
  id: serial("id").primaryKey(),
  vendorName: varchar("vendor_name", { length: 255 }).notNull(),
  vendorCode: varchar("vendor_code", { length: 100 }),
  taxId: varchar("tax_id", { length: 100 }).notNull(),
  address: text("address"),
  country: varchar("country", { length: 100 }),
  phone: varchar("phone", { length: 50 }),
  email: varchar("email", { length: 255 }),
  website: varchar("website", { length: 255 }),
  status: varchar("status", {
    length: 30,
    enum: [
      "PENDING_REVIEW",   // 가입 신청 중 (초기 신청)
      "IN_REVIEW",        // 심사 중
      "REJECTED",         // 심사 거부됨
      "IN_PQ",            // PQ 진행 중
      "PQ_SUBMITTED",        // PQ 제출
      "PQ_FAILED",        // PQ 실패
      "APPROVED",         // PQ 통과, 승인됨
      "ACTIVE",           // 활성 상태 (실제 거래 중)
      "INACTIVE",         // 비활성 상태 (일시적)
      "BLACKLISTED",      // 거래 금지 상태
    ]
  })
    .notNull()
    .default("PENDING_REVIEW"),

  representativeName: varchar("representative_name", { length: 255 }),
  representativeBirth: varchar("representative_birth", { length: 20 }),
  representativeEmail: varchar("representative_email", { length: 255 }),
  representativePhone: varchar("representative_phone", { length: 50 }),
  corporateRegistrationNumber: varchar("corporate_registration_number", {
    length: 100,
  }),

  creditAgency: varchar("credit_agency", { length: 50 }),
  creditRating: varchar("credit_rating", { length: 50 }),
  cashFlowRating: varchar("cash_flow_rating", { length: 50 }),

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

export const vendorContacts = pgTable("vendor_contacts", {
  id: serial("id").primaryKey(),
  vendorId: integer("vendor_id").notNull().references(() => vendors.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 vendorPossibleItems = pgTable("vendor_possible_items", {
  id: serial("id").primaryKey(),
  vendorId: integer("vendor_id").notNull().references(() => vendors.id),
  // itemId: integer("item_id"), // 별도 item 테이블 연동시
  itemCode: varchar("item_code", { length: 100 })
    .notNull()
    .references(() => items.itemCode, { onDelete: "cascade" }),
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
});

export const vendorItemsView = pgView("vendor_items_view").as((qb) => {
  return qb
    .select({
      // vendorPossibleItems의 "id" -> "vendorItemId"
      vendorItemId: vendorPossibleItems.id,
      vendorId: vendorPossibleItems.vendorId,

      // items의 "id" -> "itemId"
      // itemId: items.id,
      itemName: items.itemName,
      itemCode: items.itemCode,
      description: items.description,

      createdAt: vendorPossibleItems.createdAt,
      updatedAt: vendorPossibleItems.updatedAt,
    })
    .from(vendorPossibleItems)
    .leftJoin(items, eq(vendorPossibleItems.itemCode, items.itemCode))
})

export const vendorAttachments = pgTable("vendor_attachments", {
  id: serial("id").primaryKey(),
  vendorId: integer("vendor_id").references(() => vendors.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 type Vendor = typeof vendors.$inferSelect
export type VendorContact = typeof vendorContacts.$inferSelect
export type VendorItem = typeof vendorPossibleItems.$inferSelect
export type VendorAttach = typeof vendorAttachments.$inferSelect

export type VendorWithAttachments = Vendor & {
  hasAttachments?: boolean;
  attachmentsList?: VendorAttach[];
}

export type VendorItemsView = typeof vendorItemsView.$inferSelect