path: root/db/schema/vendorData.ts

import {
    pgTable,
    text,
    varchar,
    timestamp,
    integer, 
    unique, 
    serial, 
    jsonb, 
    uniqueIndex,
    primaryKey,
    foreignKey,
    pgView,
    boolean
} from "drizzle-orm/pg-core"
import { relations, and, eq, sql} from "drizzle-orm";

import { contractItems } from "./contract"
import { projects } from "./projects" // projects 테이블 임포트 가정

// 기존 테이블들은 그대로 유지
export const forms = pgTable("forms", {
    id: integer("id").primaryKey().generatedAlwaysAsIdentity(),
    contractItemId: integer("contract_item_id")
        .notNull()
        .references(() => contractItems.id, { onDelete: "cascade" }),
    formCode: varchar("form_code", { length: 100 }).notNull(),
    formName: varchar("form_name", { length: 255 }).notNull(),
    // source: varchar("source", { length: 255 }),
    // 새로 추가된 칼럼: eng와 im
    eng: boolean("eng").default(false).notNull(),
    im: boolean("im").default(false).notNull(),
    createdAt: timestamp("created_at").defaultNow().notNull(),
    updatedAt: timestamp("updated_at").defaultNow().notNull(),
}, (table) => {
    return {
        contractItemFormCodeUnique: uniqueIndex("contract_item_form_code_unique").on(
            table.contractItemId,
            table.formCode
        ),
    }
})

// formMetas에 projectId 추가
export const formMetas = pgTable("form_metas", {
    id: serial("id").primaryKey(),
    projectId: integer("project_id")
        .notNull()
        .references(() => projects.id, { onDelete: "cascade" }),
    formCode: varchar("form_code", { length: 50 }).notNull(),
    formName: varchar("form_name", { length: 255 }).notNull(),
    columns: jsonb("columns").notNull(),
    createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
    updatedAt: timestamp("updated_at", { withTimezone: true }).defaultNow().notNull(),
}, (table) => {
    return {
        // formCode는 프로젝트 내에서만 유니크하면 됨
        formCodeProjectUnique: unique("form_code_project_unique").on(
            table.projectId,
            table.formCode
        )
    }
})

export const formEntries = pgTable("form_entries", {
    id: serial("id").primaryKey(),
    formCode: varchar("form_code", { length: 50 }).notNull(),
    data: jsonb("data").notNull(),
    contractItemId: integer("contract_item_id")
        .notNull()
        .references(() => contractItems.id, { onDelete: "cascade" }),
    createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
    updatedAt: timestamp("updated_at", { withTimezone: true }).defaultNow().notNull(),
})

export const tags = pgTable("tags", {
    id: integer("id").primaryKey().generatedAlwaysAsIdentity(),
    contractItemId: integer("contract_item_id")
      .notNull()
      .references(() => contractItems.id, { onDelete: "cascade" }),
    formId: integer("form_id")
      .references(() => forms.id, { onDelete: "set null" }),
    tagNo: varchar("tag_no", { length: 100 }).notNull(),
    tagType: varchar("tag_type", { length: 50 }).notNull(),
    class: varchar("class", { length: 100 }).notNull(),
    description: text("description"),
    createdAt: timestamp("created_at").defaultNow().notNull(),
    updatedAt: timestamp("updated_at").defaultNow().notNull(),
  }, (table) => {
    return {
      contractItemTagNoUnique: unique("contract_item_tag_no_unique").on(table.contractItemId, table.tagNo),
    };
  });
  
// tagTypes에 projectId 추가 및 복합 기본키 생성
export const tagTypes = pgTable("tag_types", {
    code: varchar("code", { length: 50 }).notNull(),
    projectId: integer("project_id")
        .notNull()
        .references(() => projects.id, { onDelete: "cascade" }),
    description: text("description").notNull(),
    createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
    updatedAt: timestamp("updated_at", { withTimezone: true }).defaultNow().notNull(),
}, (table) => {
    return {
        // code는 더 이상 단독 PK가 아니고, projectId와 함께 복합 PK 구성
        pk: primaryKey({ columns: [table.code, table.projectId] })
    }
})

// tagSubfields에 projectId 추가 및 FK/유니크 제약 업데이트
export const tagSubfields = pgTable("tag_subfields", {
    id: serial("id").primaryKey(),
    projectId: integer("project_id")
        .notNull()
        .references(() => projects.id, { onDelete: "cascade" }),
    tagTypeCode: varchar("tag_type_code", { length: 50 }).notNull(),
    attributesId: varchar("attributes_id", { length: 50 }).notNull(),
    attributesDescription: text("attributes_description").notNull(),
    expression: text("expression"),
    delimiter: varchar("delimiter", { length: 10 }),
    sortOrder: integer("sort_order").default(0).notNull(),
    createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
    updatedAt: timestamp("updated_at", { withTimezone: true }).defaultNow().notNull(),
}, (table) => {
    return {
        // 유니크 제약은 이제 projectId를 포함
        uniqTagTypeAttribute: unique("uniq_tag_type_attribute").on(
            table.projectId,
            table.tagTypeCode,
            table.attributesId
        ),
        // attributesId와 projectId 조합 내에서 유니크(0429 db push 관련 수정)
        uniqAttributeIdProject: unique("uniq_attribute_id_project").on(
            table.attributesId,
            table.projectId
        ),
        // tagTypes 참조를 위한 복합 FK (tagTypeCode, projectId)
        // tagTypeRef: foreignKey({
        //     columns: [table.tagTypeCode, table.projectId],
        //     foreignColumns: [tagTypes.code, tagTypes.projectId]
        // }).onDelete("cascade")
    };
});

// tagSubfieldOptions에 projectId 추가 및 FK/유니크 제약 업데이트
export const tagSubfieldOptions = pgTable("tag_subfield_options", {
    id: serial("id").primaryKey(),
    projectId: integer("project_id")
        .notNull()
        .references(() => projects.id, { onDelete: "cascade" }),
    attributesId: varchar("attributes_id", { length: 50 }).notNull(),
    code: varchar("code", { length: 50 }).notNull(),
    label: text("label").notNull(),
    createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
    updatedAt: timestamp("updated_at", { withTimezone: true }).defaultNow().notNull(),
}, (table) => {
    return {
        // 코드는 projectId + attributesId 조합 내에서 유니크해야 함
        uniqAttributeProjectCode: unique("uniq_attribute_project_code").on(
            table.projectId,
            table.attributesId,
            table.code
        ),
        // tagSubfields 참조를 위한 복합 FK
        // attributesId만으로는 더 이상 유니크하지 않으므로 projectId도 함께 참조
        // attributesRef: foreignKey({
        //     columns: [table.attributesId, table.projectId],
        //     // tagSubfields의 attributesId + projectId 참조
        //     foreignColumns: [tagSubfields.attributesId, tagSubfields.projectId]
        // }).onDelete("cascade")
        // 0429 db push 관련 수정
    };
})

// tagClasses에 projectId 추가 및 FK 업데이트
export const tagClasses = pgTable("tag_classes", {
    id: integer("id").primaryKey().generatedAlwaysAsIdentity(),
    projectId: integer("project_id")
        .notNull()
        .references(() => projects.id, { onDelete: "cascade" }),
    code: varchar("code", { length: 100 }).notNull(),
    label: text("label").notNull(),
    tagTypeCode: varchar("tag_type_code", { length: 50 }).notNull(),
    createdAt: timestamp("created_at").defaultNow().notNull(),
    updatedAt: timestamp("updated_at").defaultNow().notNull(),
}, (table) => {
    return {
        // 코드는 프로젝트 내에서 유니크해야 함
        uniqCodeInProject: unique("uniq_code_in_project").on(
            table.projectId,
            table.code
        ),
        // tagTypes 참조를 위한 복합 FK
        tagTypeRef: foreignKey({
            columns: [table.tagTypeCode, table.projectId],
            foreignColumns: [tagTypes.code, tagTypes.projectId]
        }).onDelete("cascade")
    };
})

// tagTypeClassFormMappings에 projectId 추가
export const tagTypeClassFormMappings = pgTable("tag_type_class_form_mappings", {
    id: serial("id").primaryKey(),
    projectId: integer("project_id").notNull(),  // Remove the reference here
    tagTypeLabel: varchar("tag_type_label", { length: 255 }).notNull(),
    classLabel: varchar("class_label", { length: 255 }).notNull(),
    formCode: varchar("form_code", { length: 50 }).notNull(),
    formName: varchar("form_name", { length: 255 }).notNull(),
    ep: varchar("ep", { length: 255 }),
    remark: varchar("remark", { length: 255 }),
    createdAt: timestamp("created_at", { withTimezone: true }).defaultNow().notNull(),
    updatedAt: timestamp("updated_at", { withTimezone: true }).defaultNow().notNull(),
}, (table) => {
    return {
        uniqMappingInProject: unique("uniq_mapping_in_project").on(
            table.projectId,
            table.tagTypeLabel,
            table.classLabel,
            table.formCode
        )
    };
});

export const tagTypeClassFormMappingsRelations = relations(tagTypeClassFormMappings, ({ one }) => ({
    project: one(projects, {
        fields: [tagTypeClassFormMappings.projectId],
        references: [projects.id],
    }),
}));

// view_tag_subfields에도 projectId 추가
export const viewTagSubfields = pgView("view_tag_subfields").as((qb) => {
    return qb
      .select({

        id: sql<number>`${tagSubfields.id}`.as("id"),
        // projectId: tagSubfields.projectId,
        tagTypeCode: tagSubfields.tagTypeCode,
        tagTypeDescription: tagTypes.description,
        attributesId: tagSubfields.attributesId,
        attributesDescription: tagSubfields.attributesDescription,
        expression: tagSubfields.expression,
        delimiter: tagSubfields.delimiter,
        sortOrder: tagSubfields.sortOrder,
        createdAt: tagSubfields.createdAt,
        updatedAt: tagSubfields.updatedAt,
        // 프로젝트 관련 정보 추가
        projectId: sql<number>`${projects.id}`.as("project_id"),  // Explicitly alias projects.id
        projectCode: projects.code,
        projectName: projects.name
      })
      .from(tagSubfields)
      .innerJoin(
        tagTypes,
        and(
          eq(tagSubfields.tagTypeCode, tagTypes.code),
          eq(tagSubfields.projectId, tagTypes.projectId)
        )
      )
      .innerJoin(
        projects,
        eq(tagSubfields.projectId, projects.id)
      )
  });

// 타입 정의 업데이트
export type Tag = typeof tags.$inferSelect
export type Form = typeof forms.$inferSelect
export type NewTag = typeof tags.$inferInsert
export type TagTypeClassFormMappings = typeof tagTypeClassFormMappings.$inferSelect
export type TagSubfields = typeof tagSubfields.$inferSelect
export type TagSubfieldOption = typeof tagSubfieldOptions.$inferSelect
export type TagClasses = typeof tagClasses.$inferSelect
export type ViewTagSubfields = typeof viewTagSubfields.$inferSelect

export const vendorDataReportTemps = pgTable("vendor_data_report_temps", {
    id: serial("id").primaryKey(),
    contractItemId: integer("contract_item_id")
      .notNull()
      .references(() => contractItems.id, { onDelete: "cascade" }),
    formId: integer("form_id")
      .notNull()
      .references(() => forms.id, { onDelete: "cascade" }),
    fileName: varchar("file_name", { length: 255 }).notNull(),
    filePath: varchar("file_path", { length: 1024 }).notNull(),
    createdAt: timestamp("created_at", { withTimezone: true })
    .defaultNow()
    .notNull(),
    updatedAt: timestamp("updated_at", { withTimezone: true })
    .defaultNow()
    .notNull(),
});
  
export type VendorDataReportTemps = typeof vendorDataReportTemps.$inferSelect;


export const formListsView = pgView("form_lists_view").as((qb) => {
    return qb
      .select({
        // Primary identifiers
        id: sql<number>`${tagTypeClassFormMappings.id}`.as("id"),
        projectId: sql<number>`${tagTypeClassFormMappings.projectId}`.as("project_id"),
        
        // Project information
        projectCode: sql<string>`${projects.code}`.as("project_code"),
        projectName: sql<string>`${projects.name}`.as("project_name"),
        
        // Form information
        tagTypeLabel: sql<string>`${tagTypeClassFormMappings.tagTypeLabel}`.as("tag_type_label"),
        classLabel: sql<string>`${tagTypeClassFormMappings.classLabel}`.as("class_label"),
        formCode: sql<string>`${tagTypeClassFormMappings.formCode}`.as("form_code"),
        formName: sql<string>`${tagTypeClassFormMappings.formName}`.as("form_name"),
        
        // Additional fields
        ep: sql<string | null>`${tagTypeClassFormMappings.ep}`.as("ep"),
        remark: sql<string | null>`${tagTypeClassFormMappings.remark}`.as("remark"),
        
        // Timestamps
        createdAt: sql<Date>`${tagTypeClassFormMappings.createdAt}`.as("created_at"),
        updatedAt: sql<Date>`${tagTypeClassFormMappings.updatedAt}`.as("updated_at"),
      })
      .from(tagTypeClassFormMappings)
      .innerJoin(
        projects, 
        eq(tagTypeClassFormMappings.projectId, projects.id)
      );
  });
  
  
  export type FormListsView = typeof formListsView.$inferSelect;