"use server";

import { asc, desc, count, ilike, eq, and, inArray, or, type SQL } from "drizzle-orm";
import db from "@/db/db";
import { getErrorMessage } from "@/lib/handle-error";

// Drizzle 트랜잭션 타입 정의
type DatabaseTransaction = Parameters<Parameters<typeof db.transaction>[0]>[0];
import { generalContractTemplates, GeneralContractTemplate, type NewGeneralContractTemplate, users } from "@/db/schema";

// 계약 템플릿 조회 옵션
export interface SelectContractTemplatesOptions {
  where?: SQL;
  orderBy?: SQL[];
  limit?: number;
  offset?: number;
}

// 계약 템플릿 목록 조회
export async function selectContractTemplates(
  tx: DatabaseTransaction,
  options: SelectContractTemplatesOptions = {}
) {
  const { where, orderBy, limit, offset } = options;

  const query = tx
    .select()
    .from(generalContractTemplates)
    .$dynamic();

  if (where) {
    query.where(where);
  }

  if (orderBy) {
    query.orderBy(...orderBy);
  }

  if (limit !== undefined) {
    query.limit(limit);
  }

  if (offset !== undefined) {
    query.offset(offset);
  }

  return await query.execute();
}

// 계약 템플릿 조회 (사용자 정보 포함)
export async function selectContractTemplatesWithUsers(
  tx: DatabaseTransaction,
  options: SelectContractTemplatesOptions
) {
  const { where, orderBy, limit, offset } = options;

  const query = tx
    .select({
      // 템플릿 정보 (필수 컬럼만)
      id: generalContractTemplates.id,
      contractTemplateType: generalContractTemplates.contractTemplateType,
      contractTemplateName: generalContractTemplates.contractTemplateName,
      revision: generalContractTemplates.revision,
      status: generalContractTemplates.status,
      fileName: generalContractTemplates.fileName,
      filePath: generalContractTemplates.filePath,
      legalReviewRequired: generalContractTemplates.legalReviewRequired,
      createdAt: generalContractTemplates.createdAt,
      updatedAt: generalContractTemplates.updatedAt,
      updatedBy: generalContractTemplates.updatedBy,
      disposedAt: generalContractTemplates.disposedAt,
      // 사용자 정보
      updatedByName: users.name,
    })
    .from(generalContractTemplates)
    .leftJoin(users, eq(generalContractTemplates.updatedBy, users.id))
    .$dynamic();

  if (where) {
    query.where(where);
  }

  if (orderBy) {
    query.orderBy(...orderBy);
  }

  if (limit !== undefined) {
    query.limit(limit);
  }

  if (offset !== undefined) {
    query.offset(offset);
  }

  return await query.execute();
}

// 계약 템플릿 개수 조회
export async function countContractTemplates(
  tx: DatabaseTransaction,
  where?: SQL
) {
  const query = tx
    .select({ count: count() })
    .from(generalContractTemplates)
    .$dynamic();

  if (where) {
    query.where(where);
  }

  const result = await query.execute();
  return result[0]?.count ?? 0;
}

// 계약 템플릿 생성
export async function insertContractTemplate(
  tx: DatabaseTransaction,
  data: NewGeneralContractTemplate
) {
  return await tx
    .insert(generalContractTemplates)
    .values(data)
    .returning();
}

// ID로 계약 템플릿 조회
export async function getContractTemplateById(
  tx: DatabaseTransaction,
  id: number
) {
  const result = await tx
    .select()
    .from(generalContractTemplates)
    .where(eq(generalContractTemplates.id, id))
    .limit(1);

  return result[0] ?? null;
}

// 계약 템플릿 업데이트
export async function updateContractTemplate(
  tx: DatabaseTransaction,
  id: number,
  data: Partial<NewGeneralContractTemplate>,
  updatedBy?: number
) {
  return await tx
    .update(generalContractTemplates)
    .set({
      ...data,
      updatedAt: new Date(),
      updatedBy: updatedBy || null,
    })
    .where(eq(generalContractTemplates.id, id))
    .returning();
}

// 계약 템플릿 삭제 (복수)
export async function deleteContractTemplates(
  tx: DatabaseTransaction,
  ids: number[]
): Promise<{ data: GeneralContractTemplate[] | null; error: string | null }> {
  if (!ids || ids.length === 0) {
    return { data: [], error: null };
  }
  
  try {
    // 삭제될 템플릿 정보를 반환하기 위해 먼저 조회
    const templatesBeforeDelete = await tx
      .select()
      .from(generalContractTemplates)
      .where(inArray(generalContractTemplates.id, ids));
    
    // 삭제 실행
    const deletedTemplates = await tx
      .delete(generalContractTemplates)
      .where(inArray(generalContractTemplates.id, ids))
      .returning();
    
    return { data: deletedTemplates, error: null };
  } catch (error) {
    console.error("deleteContractTemplates 에러:", error);
    return { data: null, error: getErrorMessage(error) };
  }
}


// 모든 활성 템플릿 조회 (간단한 목록용)
export async function findAllContractTemplates(tx: DatabaseTransaction) {
  return await tx
    .select({
      id: generalContractTemplates.id,
      contractTemplateType: generalContractTemplates.contractTemplateType,
      contractTemplateName: generalContractTemplates.contractTemplateName,
      revision: generalContractTemplates.revision,
      status: generalContractTemplates.status,
    })
    .from(generalContractTemplates)
    .where(eq(generalContractTemplates.status, "ACTIVE"))
    .orderBy(
      asc(generalContractTemplates.contractTemplateType),
      asc(generalContractTemplates.contractTemplateName)
    );
}
// 상태별 템플릿 개수
export async function getContractTemplateStats(tx: DatabaseTransaction) {
  return await tx
    .select({
      status: generalContractTemplates.status,
      count: count(),
    })
    .from(generalContractTemplates)
    .groupBy(generalContractTemplates.status);
}

// 계약 종류별 템플릿 개수
export async function getContractTemplatesByType(tx: DatabaseTransaction) {
  return await tx
    .select({
      contractTemplateType: generalContractTemplates.contractTemplateType,
      count: count(),
    })
    .from(generalContractTemplates)
    .where(eq(generalContractTemplates.status, "ACTIVE"))
    .groupBy(generalContractTemplates.contractTemplateType)
    .orderBy(asc(generalContractTemplates.contractTemplateType));
}