import db from "@/db/db";
import {
  vendorRegularRegistrations,
  vendors,
  vendorAttachments,
  vendorInvestigationAttachments,
  basicContract,
  basicContractTemplates,
  vendorPQSubmissions,
  vendorInvestigations,
  vendorBusinessContacts,
  vendorAdditionalInfo,
} from "@/db/schema";
import { eq, desc, and, sql, inArray } from "drizzle-orm";
import type { VendorRegularRegistration } from "@/config/vendorRegularRegistrationsColumnsConfig";

export async function getVendorRegularRegistrations(
): Promise<VendorRegularRegistration[]> {
  try {
    // DB 레코드 기준으로 정규업체등록 데이터를 가져옴
    const registrations = await db
      .select({
        // 정규업체등록 정보
        id: vendorRegularRegistrations.id,
        vendorId: vendorRegularRegistrations.vendorId,
        status: vendorRegularRegistrations.status,
        potentialCode: vendorRegularRegistrations.potentialCode,
        majorItems: vendorRegularRegistrations.majorItems,
        registrationRequestDate: vendorRegularRegistrations.registrationRequestDate,
        assignedDepartment: vendorRegularRegistrations.assignedDepartment,
        assignedUser: vendorRegularRegistrations.assignedUser,
        remarks: vendorRegularRegistrations.remarks,
        // 새로 추가된 필드들
        safetyQualificationContent: vendorRegularRegistrations.safetyQualificationContent,
        gtcSkipped: vendorRegularRegistrations.gtcSkipped,
        // 벤더 기본 정보
        businessNumber: vendors.taxId,
        companyName: vendors.vendorName,
        establishmentDate: vendors.createdAt,
        representative: vendors.representativeName,
        // 국가 정보 추가
        country: vendors.country,
      })
      .from(vendorRegularRegistrations)
      .innerJoin(vendors, eq(vendorRegularRegistrations.vendorId, vendors.id))
      .orderBy(desc(vendorRegularRegistrations.createdAt));

    // 벤더 ID 배열 생성
    const vendorIds = registrations.map(r => r.vendorId);

    // 벤더 첨부파일 정보 조회 - 벤더별로 그룹화
    const vendorAttachmentsList = vendorIds.length > 0 ? await db
      .select()
      .from(vendorAttachments)
      .where(inArray(vendorAttachments.vendorId, vendorIds)) : [];

    // 실사 첨부파일 정보 조회 - 실사 ID를 통해 벤더 ID 매핑
    const investigationAttachmentsList = vendorIds.length > 0 ? await db
      .select({
        vendorId: vendorInvestigations.vendorId,
        attachmentId: vendorInvestigationAttachments.id,
        fileName: vendorInvestigationAttachments.fileName,
        attachmentType: vendorInvestigationAttachments.attachmentType,
        createdAt: vendorInvestigationAttachments.createdAt,
      })
      .from(vendorInvestigationAttachments)
      .innerJoin(vendorInvestigations, eq(vendorInvestigationAttachments.investigationId, vendorInvestigations.id))
      .where(inArray(vendorInvestigations.vendorId, vendorIds)) : [];

    // 기본 계약 정보 조회 (템플릿별로 가장 최신 것만)
    const basicContractsList = vendorIds.length > 0 ? await db
      .select({
        vendorId: basicContract.vendorId,
        templateId: basicContract.templateId,
        status: basicContract.status,
        templateName: basicContractTemplates.templateName,
        createdAt: basicContract.createdAt,
        filePath: basicContract.filePath,
        fileName: basicContract.fileName,
      })
      .from(basicContract)
      .leftJoin(basicContractTemplates, eq(basicContract.templateId, basicContractTemplates.id))
      .where(inArray(basicContract.vendorId, vendorIds))
      .orderBy(desc(basicContract.createdAt)) : [];

    // 추가정보 입력 상태 조회 (업무담당자 정보)
    const businessContactsList = vendorIds.length > 0 ? await db
      .select({
        vendorId: vendorBusinessContacts.vendorId,
        contactType: vendorBusinessContacts.contactType,
      })
      .from(vendorBusinessContacts)
      .where(inArray(vendorBusinessContacts.vendorId, vendorIds)) : [];

    // 추가정보 테이블 조회
    const additionalInfoList = vendorIds.length > 0 ? await db
      .select({
        vendorId: vendorAdditionalInfo.vendorId,
      })
      .from(vendorAdditionalInfo)
      .where(inArray(vendorAdditionalInfo.vendorId, vendorIds)) : [];

    // 각 등록 레코드별로 데이터를 매핑하여 결과 반환
    return registrations.map((registration) => {
      // 벤더별 첨부파일 필터링
      const vendorFiles = vendorAttachmentsList.filter(att => att.vendorId === registration.vendorId);
      const investigationFiles = investigationAttachmentsList.filter(att => att.vendorId === registration.vendorId);
      const allVendorContracts = basicContractsList.filter(contract => contract.vendorId === registration.vendorId);
      const vendorContacts = businessContactsList.filter(contact => contact.vendorId === registration.vendorId);
      const vendorAdditionalInfoData = additionalInfoList.filter(info => info.vendorId === registration.vendorId);
      
      // 기술자료 동의서, 비밀유지계약서 제외 필터링
      const filteredContracts = allVendorContracts.filter(contract => {
        const templateName = contract.templateName?.toLowerCase() || '';
        return !templateName.includes('기술자료') && !templateName.includes('비밀유지');
      });

      // 템플릿명 기준으로 가장 최신 계약만 유지 (중복 제거)
      const vendorContracts = filteredContracts.reduce((acc, contract) => {
        const existing = acc.find(c => c.templateName === contract.templateName);
        if (!existing || (contract.createdAt && existing.createdAt && contract.createdAt > existing.createdAt)) {
          // 기존에 같은 템플릿명이 없거나, 더 최신인 경우 추가/교체
          return acc.filter(c => c.templateName !== contract.templateName).concat(contract);
        }
        return acc;
      }, [] as typeof filteredContracts);
      
      // 문서 제출 현황 - 국가별 요구사항 적용
      const isForeign = registration.country !== 'KR';
      const documentSubmissionsStatus = {
        businessRegistration: vendorFiles.some(f => f.attachmentType === "BUSINESS_REGISTRATION"),
        creditEvaluation: vendorFiles.some(f => f.attachmentType === "CREDIT_REPORT"),
        bankCopy: isForeign ? vendorFiles.some(f => f.attachmentType === "BANK_ACCOUNT_COPY") : true, // 내자는 통장사본 불필요
        auditResult: investigationFiles.length > 0, // 실사 첨부파일이 하나라도 있으면 true
      };

      // 문서별 파일 정보 (다운로드용)
      const documentFiles = {
        businessRegistration: vendorFiles.filter(f => f.attachmentType === "BUSINESS_REGISTRATION"),
        creditEvaluation: vendorFiles.filter(f => f.attachmentType === "CREDIT_REPORT"),
        bankCopy: vendorFiles.filter(f => f.attachmentType === "BANK_ACCOUNT_COPY"),
        auditResult: investigationFiles,
      };

      // 계약 동의 현황 - 실제 기본 계약 데이터 기반으로 단순화
      const contractAgreementsStatus = {
        cp: vendorContracts.some(c => c.status === "COMPLETED") ? "completed" : "not_submitted",
        gtc: registration.gtcSkipped ? "completed" : (vendorContracts.some(c => c.templateName?.includes("GTC") && c.status === "COMPLETED") ? "completed" : "not_submitted"),
        standardSubcontract: vendorContracts.some(c => c.templateName?.includes("표준하도급") && c.status === "COMPLETED") ? "completed" : "not_submitted",
        safetyHealth: vendorContracts.some(c => c.templateName?.includes("안전보건") && c.status === "COMPLETED") ? "completed" : "not_submitted",
        ethics: vendorContracts.some(c => c.templateName?.includes("윤리") && c.status === "COMPLETED") ? "completed" : "not_submitted",
        domesticCredit: vendorContracts.some(c => c.templateName?.includes("내국신용장") && c.status === "COMPLETED") ? "completed" : "not_submitted",
      };

      // 추가정보 입력 완료 여부 - 5개 필수 업무담당자 타입 + 추가정보 테이블 모두 입력되었는지 확인
      const requiredContactTypes = ["sales", "design", "delivery", "quality", "tax_invoice"];
      const contactsCompleted = requiredContactTypes.every(type => 
        vendorContacts.some(contact => contact.contactType === type)
      );
      const additionalInfoTableCompleted = vendorAdditionalInfoData.length > 0;
      const additionalInfoCompleted = contactsCompleted && additionalInfoTableCompleted;

      // 모든 조건 충족 여부 확인
      const allDocumentsSubmitted = Object.values(documentSubmissionsStatus).every(status => status === true);
      const allContractsCompleted = vendorContracts.length > 0 && vendorContracts.every(c => c.status === "COMPLETED");
      const safetyQualificationCompleted = !!registration.safetyQualificationContent;
      
      // 모든 조건이 충족되면 status를 "approval_ready"(조건충족)로 자동 변경
      const shouldUpdateStatus = allDocumentsSubmitted && allContractsCompleted && safetyQualificationCompleted && additionalInfoCompleted;
      
      // 현재 상태가 조건충족이 아닌데 모든 조건이 충족되면 상태 업데이트
      // 단, 이미 registration_requested 상태라면 자동 업데이트하지 않음
      if (shouldUpdateStatus && registration.status !== "approval_ready" && registration.status !== "registration_requested") {
        // 비동기 업데이트 (백그라운드에서 실행)
        updateVendorRegularRegistration(registration.id, {
          status: "approval_ready"
        }).catch(error => {
          console.error(`상태 자동 업데이트 실패 (벤더 ID: ${registration.vendorId}):`, error);
        });
      }

      return {
        id: registration.id,
        vendorId: registration.vendorId,
        status: registration.status || "audit_pass",
        potentialCode: registration.potentialCode,
        businessNumber: registration.businessNumber || "",
        companyName: registration.companyName || "",
        majorItems: registration.majorItems,
        establishmentDate: registration.establishmentDate?.toISOString() || null,
        representative: registration.representative,
        country: registration.country,
        documentSubmissions: documentSubmissionsStatus,
        documentFiles: documentFiles, // 파일 정보 추가
        contractAgreements: contractAgreementsStatus,
        // 새로 추가된 필드들
        safetyQualificationContent: registration.safetyQualificationContent,
        gtcSkipped: registration.gtcSkipped || false,
        additionalInfo: additionalInfoCompleted,
        // 기본계약 정보
        basicContracts: vendorContracts.map((contract: any) => ({
          templateId: contract.templateId,
          templateName: contract.templateName,
          status: contract.status,
          createdAt: contract.createdAt,
          filePath: contract.filePath,
          fileName: contract.fileName,
        })),
        registrationRequestDate: registration.registrationRequestDate || null,
        assignedDepartment: registration.assignedDepartment,
        assignedUser: registration.assignedUser,
        remarks: registration.remarks,
      };
    });
  } catch (error) {
    console.error("Error fetching vendor regular registrations:", error);
    throw new Error("정규업체 등록 목록을 가져오는 중 오류가 발생했습니다.");
  }
}

export async function createVendorRegularRegistration(data: {
  vendorId: number;
  status?: string;
  potentialCode?: string;
  majorItems?: string;
  assignedDepartment?: string;
  assignedDepartmentCode?: string;
  assignedUser?: string;
  assignedUserCode?: string;
  remarks?: string;
  safetyQualificationContent?: string;
  gtcSkipped?: boolean;
}) {
  try {
    const [registration] = await db
      .insert(vendorRegularRegistrations)
      .values({
        vendorId: data.vendorId,
        status: data.status || "audit_pass",
        potentialCode: data.potentialCode,
        majorItems: data.majorItems,
        assignedDepartment: data.assignedDepartment,
        assignedDepartmentCode: data.assignedDepartmentCode,
        assignedUser: data.assignedUser,
        assignedUserCode: data.assignedUserCode,
        remarks: data.remarks,
        safetyQualificationContent: data.safetyQualificationContent,
        gtcSkipped: data.gtcSkipped || false,
      })
      .returning();

    return registration;
  } catch (error) {
    console.error("Error creating vendor regular registration:", error);
    throw new Error("정규업체 등록을 생성하는 중 오류가 발생했습니다.");
  }
}

export async function updateVendorRegularRegistration(
  id: number,
  data: Partial<{
    status: string;
    potentialCode: string;
    majorItems: string;
    registrationRequestDate: string;
    assignedDepartment: string;
    assignedDepartmentCode: string;
    assignedUser: string;
    assignedUserCode: string;
    remarks: string;
    safetyQualificationContent: string;
    gtcSkipped: boolean;
  }>
) {
  try {
    const [registration] = await db
      .update(vendorRegularRegistrations)
      .set({
        ...data,
        updatedAt: new Date(),
      })
      .where(eq(vendorRegularRegistrations.id, id))
      .returning();

    return registration;
  } catch (error) {
    console.error("Error updating vendor regular registration:", error);
    throw new Error("정규업체 등록을 업데이트하는 중 오류가 발생했습니다.");
  }
}

export async function getVendorRegularRegistrationById(id: number) {
  try {
    const [registration] = await db
      .select()
      .from(vendorRegularRegistrations)
      .where(eq(vendorRegularRegistrations.id, id));

    return registration;
  } catch (error) {
    console.error("Error fetching vendor regular registration by id:", error);
    throw new Error("정규업체 등록 정보를 가져오는 중 오류가 발생했습니다.");
  }
}