// lib/project-doc-templates/service.ts
"use server";

import db from "@/db/db";
import { projectDocTemplates, projectDocTemplateUsage, projects, type NewProjectDocTemplate, type DocTemplateVariable } from "@/db/schema";
import { eq, and, desc, asc, isNull, sql, inArray, count, or, ilike } from "drizzle-orm";
import { revalidatePath, revalidateTag } from "next/cache";
import { GetDOCTemplatesSchema } from "./validations";
import { filterColumns } from "@/lib/filter-columns";
import { getServerSession } from "next-auth/next"
import { authOptions } from "@/app/api/auth/[...nextauth]/route"

// 기본 변수 정의
const DEFAULT_VARIABLES: DocTemplateVariable[] = [
  {
    name: "document_number",
    displayName: "문서번호",
    type: "text",
    required: true,
    description: "문서 고유 번호",
  },
  {
    name: "project_code",
    displayName: "프로젝트 코드",
    type: "text",
    required: true,
    description: "프로젝트 식별 코드",
  },
  {
    name: "project_name",
    displayName: "프로젝트명",
    type: "text",
    required: true,
    description: "프로젝트 이름",
  },
  {
    name: "created_date",
    displayName: "작성일",
    type: "date",
    required: false,
    defaultValue: "{{today}}",
    description: "문서 작성 날짜",
  },
  {
    name: "author_name",
    displayName: "작성자",
    type: "text",
    required: false,
    description: "문서 작성자 이름",
  },
  {
    name: "department",
    displayName: "부서명",
    type: "text",
    required: false,
    description: "작성 부서",
  },
];


export async function getProjectDocTemplates(
  input: GetDOCTemplatesSchema
) {
  try {
    const offset = (input.page - 1) * input.perPage;

    // 고급 필터 조건
    const advancedWhere = filterColumns({
      table: projectDocTemplates,
      filters: input.filters,
      joinOperator: input.joinOperator,
    });

    // 전역 검색 조건 (중복 제거됨)
    let globalWhere: SQL | undefined = undefined;
    if (input.search?.trim()) {
      const searchTerm = `%${input.search.trim()}%`;
      globalWhere = or(
        ilike(projectDocTemplates.templateName, searchTerm),
        ilike(projectDocTemplates.templateCode, searchTerm),
        ilike(projectDocTemplates.projectCode, searchTerm),
        ilike(projectDocTemplates.projectName, searchTerm),
        ilike(projectDocTemplates.documentType, searchTerm),
        ilike(projectDocTemplates.fileName, searchTerm), // 중복 제거
        ilike(projectDocTemplates.createdByName, searchTerm),
        ilike(projectDocTemplates.updatedByName, searchTerm),
        ilike(projectDocTemplates.status, searchTerm),
        ilike(projectDocTemplates.description, searchTerm) // 추가 고려
      );
    }

    // WHERE 조건 결합
    const whereCondition = and(advancedWhere, globalWhere,  eq(projectDocTemplates.isLatest,true));

    // 정렬 조건 (타입 안정성 개선)
    const orderBy = input.sort.length > 0
      ? input.sort.map((item) => {
          const column = projectDocTemplates[item.id as keyof typeof projectDocTemplates];
          if (!column) {
            console.warn(`Invalid sort column: ${item.id}`);
            return null;
          }
          return item.desc ? desc(column) : asc(column);
        }).filter(Boolean) as SQL[]
      : [desc(projectDocTemplates.createdAt)];

    // 데이터 조회 (프로젝트 정보 조인 추가 고려)
    const [data, totalCount] = await Promise.all([
      db
        .select({
          ...projectDocTemplates,
          // 프로젝트 정보가 필요한 경우
          // project: projects
        })
        .from(projectDocTemplates)
        // .leftJoin(projects, eq(projectDocTemplates.projectId, projects.id))
        .where(whereCondition)
        .orderBy(...orderBy)
        .limit(input.perPage)
        .offset(offset),
      db
        .select({ count: count() })
        .from(projectDocTemplates)
        .where(whereCondition)
        .then((res) => res[0]?.count ?? 0),
    ]);

    const pageCount = Math.ceil(totalCount / input.perPage);

    return {
      data,
      pageCount,
      totalCount,
    };
  } catch (error) {
    console.error("Failed to fetch project doc templates:", error);
    throw new Error("템플릿 목록을 불러오는데 실패했습니다.");
  }
}

// 템플릿 생성
export async function createProjectDocTemplate(data: {
  templateName: string;
  templateCode?: string;
  description?: string;
  projectId?: number;
  templateType: "PROJECT" | "COMPANY_WIDE";
  documentType: string;
  filePath: string;
  fileName: string;
  fileSize?: number;
  mimeType?: string;
  variables?: DocTemplateVariable[];
  isPublic?: boolean;
  requiresApproval?: boolean;
  createdBy?: string;
}) {
  try {
    // 템플릿 코드 자동 생성 (없을 경우)
    const templateCode = data.templateCode || `TPL_${Date.now()}`;
    const session = await getServerSession(authOptions)
    if (!session?.user?.id) {
      throw new Error("인증이 필요합니다.")
    }


    // 프로젝트 정보 조회 (projectId가 있는 경우)
    let projectInfo = null;
    if (data.projectId) {
      projectInfo = await db
        .select()
        .from(projects)
        .where(eq(projects.id, data.projectId))
        .then((res) => res[0]);
    }

    // 변수 정보 설정 (기본 변수 + 사용자 정의 변수)
    const variables = [...DEFAULT_VARIABLES, ...(data.variables || [])];
    const requiredVariables = variables
      .filter((v) => v.required)
      .map((v) => v.name);

    const newTemplate: NewProjectDocTemplate = {
      templateName: data.templateName,
      templateCode,
      description: data.description,
      projectId: data.projectId,
      projectCode: projectInfo?.code,
      projectName: projectInfo?.name,
      templateType: data.templateType,
      documentType: data.documentType,
      filePath: data.filePath,
      fileName: data.fileName,
      fileSize: data.fileSize,
      mimeType: data.mimeType,
      variables,
      requiredVariables,
      isPublic: data.isPublic || false,
      requiresApproval: data.requiresApproval || false,
      status: "ACTIVE",
      createdBy: Number(session.user.id),
      createdByName: Number(session.user.name),
    };

    const [template] = await db
      .insert(projectDocTemplates)
      .values(newTemplate)
      .returning();

    revalidateTag("project-doc-templates");
    revalidatePath("/project-doc-templates");

    return { success: true, data: template };
  } catch (error) {
    console.error("Failed to create template:", error);
    return { success: false, error: "템플릿 생성에 실패했습니다." };
  }
}

// 템플릿 상세 조회
export async function getProjectDocTemplateById(templateId: number) {
  try {
    const template = await db
      .select({
        template: projectDocTemplates,
        project: projects,
      })
      .from(projectDocTemplates)
      .leftJoin(projects, eq(projectDocTemplates.projectId, projects.id))
      .where(eq(projectDocTemplates.id, templateId))
      .then((res) => res[0]);

    if (!template) {
      throw new Error("템플릿을 찾을 수 없습니다.");
    }

    // 버전 히스토리 조회
    const versionHistory = await db
      .select()
      .from(projectDocTemplates)
      .where(
        and(
          eq(projectDocTemplates.templateCode, template.template.templateCode),
          isNull(projectDocTemplates.deletedAt)
        )
      )
      .orderBy(desc(projectDocTemplates.version));

    // 사용 이력 조회 (최근 10건)
    const usageHistory = await db
      .select()
      .from(projectDocTemplateUsage)
      .where(eq(projectDocTemplateUsage.templateId, templateId))
      .orderBy(desc(projectDocTemplateUsage.usedAt))
      .limit(10);

    return {
      ...template.template,
      project: template.project,
      versionHistory,
      usageHistory,
    };
  } catch (error) {
    console.error("Failed to fetch template details:", error);
    throw new Error("템플릿 상세 정보를 불러오는데 실패했습니다.");
  }
}

// 템플릿 업데이트
export async function updateProjectDocTemplate(
  templateId: number,
  data: Partial<{
    templateName: string;
    description: string;
    documentType: string;
    variables: TemplateVariable[];
    isPublic: boolean;
    requiresApproval: boolean;
    status: string;
    updatedBy: string;
  }>
) {
  try {
    // 변수 정보 업데이트 시 requiredVariables도 함께 업데이트
    const updateData: any = { ...data, updatedAt: new Date() };
    
    if (data.variables) {
      updateData.variables = data.variables;
      updateData.requiredVariables = data.variables
        .filter((v) => v.required)
        .map((v) => v.name);
    }

    const [updated] = await db
      .update(projectDocTemplates)
      .set(updateData)
      .where(eq(projectDocTemplates.id, templateId))
      .returning();

    revalidateTag("project-doc-templates");
    revalidatePath("/project-doc-templates");

    return { success: true, data: updated };
  } catch (error) {
    console.error("Failed to update template:", error);
    return { success: false, error: "템플릿 업데이트에 실패했습니다." };
  }
}

// 새 버전 생성
export async function createTemplateVersion(
  templateId: number,
  data: {
    filePath: string;
    fileName: string;
    fileSize?: number;
    mimeType?: string;
    variables?: DocTemplateVariable[];
    createdBy?: string;
  }
) {
  try {

    const session = await getServerSession(authOptions)
    if (!session?.user?.id) {
      throw new Error("인증이 필요합니다.")
    }


    // 기존 템플릿 조회
    const existingTemplate = await db
      .select()
      .from(projectDocTemplates)
      .where(eq(projectDocTemplates.id, templateId))
      .then((res) => res[0]);

    if (!existingTemplate) {
      throw new Error("템플릿을 찾을 수 없습니다.");
    }

    // 모든 버전의 isLatest를 false로 업데이트
    await db
      .update(projectDocTemplates)
      .set({ isLatest: false })
      .where(eq(projectDocTemplates.templateCode, existingTemplate.templateCode));

    // 새 버전 생성
    const newVersion = existingTemplate.version + 1;
    const variables = data.variables || existingTemplate.variables;

    const [newTemplate] = await db
      .insert(projectDocTemplates)
      .values({
        ...existingTemplate,
        id: undefined, // 새 ID 자동 생성
        version: newVersion,
        isLatest: true,
        parentTemplateId: templateId,
        filePath: data.filePath,
        fileName: data.fileName,
        fileSize: data.fileSize,
        mimeType: data.mimeType,
        variables,
        requiredVariables: variables
          .filter((v: DocTemplateVariable) => v.required)
          .map((v: DocTemplateVariable) => v.name),
        createdBy:Number(session.user.id),
        creaetedByName:session.user.name,
        updatedBy:Number(session.user.id),
        updatedByName:session.user.name,
        createdAt: new Date(),
        updatedAt: new Date(),
      })
      .returning();

    revalidateTag("project-doc-templates");
    revalidatePath("/project-doc-templates");

    return { success: true, data: newTemplate };
  } catch (error) {
    console.error("Failed to create template version:", error);
    return { success: false, error: "새 버전 생성에 실패했습니다." };
  }
}

// 템플릿 삭제 (soft delete)
export async function deleteProjectDocTemplate(templateId: number) {
  try {
    await db
      .update(projectDocTemplates)
      .set({ 
        deletedAt: new Date(),
        status: "ARCHIVED" 
      })
      .where(eq(projectDocTemplates.id, templateId));

    revalidateTag("project-doc-templates");
    revalidatePath("/project-doc-templates");

    return { success: true };
  } catch (error) {
    console.error("Failed to delete template:", error);
    return { success: false, error: "템플릿 삭제에 실패했습니다." };
  }
}

// 템플릿 파일 저장
export async function tlsaveTemplateFile(
  templateId: number,
  formData: FormData
) {
  try {
    const file = formData.get("file") as File;
    if (!file) {
      throw new Error("파일이 없습니다.");
    }

    // 파일 저장 로직 (실제 구현 필요)
    const fileName = file.name;
    const filePath = `/uploads/templates/${templateId}/${fileName}`;

    // 템플릿 파일 경로 업데이트
    await db
      .update(projectDocTemplates)
      .set({
        filePath,
        fileName,
        updatedAt: new Date(),
      })
      .where(eq(projectDocTemplates.id, templateId));

    revalidateTag("project-doc-templates");

    return { success: true, filePath };
  } catch (error) {
    console.error("Failed to save template file:", error);
    return { success: false, error: "파일 저장에 실패했습니다." };
  }
}

// 사용 가능한 프로젝트 목록 조회
export async function getAvailableProjects() {
  try {
    const projectList = await db
      .select()
      .from(projects)
      // .where(eq(projects.status, "ACTIVE"))
      .orderBy(projects.code);

    return projectList;
  } catch (error) {
    console.error("Failed to fetch projects:", error);
    return [];
  }
}

// 템플릿 사용 기록 생성
export async function recordTemplateUsage(
  templateId: number,
  data: {
    generatedDocumentId: string;
    generatedFilePath: string;
    generatedFileName: string;
    usedVariables: Record<string, any>;
    usedInProjectId?: number;
    usedInProjectCode?: string;
    usedBy: string;
    metadata?: any;
  }
) {
  try {
    const [usage] = await db
      .insert(projectDocTemplateUsage)
      .values({
        templateId,
        ...data,
      })
      .returning();

    return { success: true, data: usage };
  } catch (error) {
    console.error("Failed to record template usage:", error);
    return { success: false, error: "사용 기록 생성에 실패했습니다." };
  }
}