'use server'

import { and, asc, desc, ilike, or } from 'drizzle-orm';
import db  from '@/db/db';
import { filterColumns } from "@/lib/filter-columns";


import { 
  esgEvaluations, 
  esgEvaluationItems, 
  esgAnswerOptions,
  NewEsgEvaluation,
  NewEsgEvaluationItem,
  NewEsgAnswerOption,
  EsgEvaluationWithItems,
  esgEvaluationsView
} from '@/db/schema';
import { eq } from 'drizzle-orm';
import { GetEsgEvaluationsSchema } from './validation';
import { countEsgEvaluations, getEsgEvaluationWithDetails, selectEsgEvaluations } from './repository';

// ============ 조회 함수들 ============

export async function getEsgEvaluations(input: GetEsgEvaluationsSchema) {
  try {
    const offset = (input.page - 1) * input.perPage;
    
    // 고급 필터링
    const advancedWhere = filterColumns({
      table: esgEvaluationsView,
      filters: input.filters,
      joinOperator: input.joinOperator,
    });

    // 전역 검색
    let globalWhere;
    if (input.search) {
      const s = `%${input.search}%`;
      globalWhere = or(
        ilike(esgEvaluationsView.serialNumber, s),
        ilike(esgEvaluationsView.category, s),
        ilike(esgEvaluationsView.inspectionItem, s)
      );
    }

    const finalWhere = and(advancedWhere, globalWhere);

    // 정렬
    const orderBy = input.sort.length > 0
      ? input.sort.map((item) => {
          return item.desc
            ? desc(esgEvaluationsView[item.id])
            : asc(esgEvaluationsView[item.id]);
        })
      : [desc(esgEvaluationsView.createdAt)];

    // 데이터 조회
    const { data, total } = await db.transaction(async (tx) => {
      const data = await selectEsgEvaluations(tx, {
        where: finalWhere,
        orderBy,
        offset,
        limit: input.perPage,
      });

      const total = await countEsgEvaluations(tx, finalWhere);
      return { data, total };
    });

    const pageCount = Math.ceil(total / input.perPage);
    return { data, pageCount };
  } catch (err) {
    console.error('Error in getEsgEvaluations:', err);
    return { data: [], pageCount: 0 };
  }
}

// 단일 평가표 상세 조회 (평가항목과 답변 옵션 포함)
export async function getEsgEvaluationDetails(id: number) {
  try {
    return await db.transaction(async (tx) => {
      return await getEsgEvaluationWithDetails(tx, id);
    });
  } catch (err) {
    console.error('Error in getEsgEvaluationDetails:', err);
    return null;
  }
}

// ============ 생성 함수들 ============

export async function createEsgEvaluation(data: NewEsgEvaluation) {
  try {
    return await db.transaction(async (tx) => {
      const [result] = await tx
        .insert(esgEvaluations)
        .values(data)
        .returning();
      return result;
    });
  } catch (err) {
    console.error('Error creating ESG evaluation:', err);
    throw new Error('Failed to create ESG evaluation');
  }
}

export async function createEsgEvaluationWithItems(
  evaluationData: NewEsgEvaluation,
  items: Array<{
    evaluationItem: string;
    orderIndex?: number;
    answerOptions: Array<{
      answerText: string;
      score: number;
      orderIndex?: number;
    }>;
  }>
) {
  try {
    return await db.transaction(async (tx) => {
      // 1. 평가표 생성
      const [evaluation] = await tx
        .insert(esgEvaluations)
        .values(evaluationData)
        .returning();

      // 2. 평가항목들 생성
      for (let i = 0; i < items.length; i++) {
        const item = items[i];
        const [evaluationItem] = await tx
          .insert(esgEvaluationItems)
          .values({
            esgEvaluationId: evaluation.id,
            evaluationItem: item.evaluationItem,
            orderIndex: item.orderIndex ?? i,
          })
          .returning();

        // 3. 답변 옵션들 생성
        if (item.answerOptions.length > 0) {
          await tx.insert(esgAnswerOptions).values(
            item.answerOptions.map((option, optionIndex) => ({
              esgEvaluationItemId: evaluationItem.id,
              answerText: option.answerText,
              score: option.score.toString(),
              orderIndex: option.orderIndex ?? optionIndex,
            }))
          );
        }
      }

      return evaluation;
    });
  } catch (err) {
    console.error('Error creating ESG evaluation with items:', err);
    throw new Error('Failed to create ESG evaluation with items');
  }
}

// ============ 수정 함수들 ============

export async function updateEsgEvaluation(
  id: number,
  data: Partial<NewEsgEvaluation>
) {
  try {
    return await db.transaction(async (tx) => {
      const [result] = await tx
        .update(esgEvaluations)
        .set({ ...data, updatedAt: new Date() })
        .where(eq(esgEvaluations.id, id))
        .returning();
      return result;
    });
  } catch (err) {
    console.error('Error updating ESG evaluation:', err);
    throw new Error('Failed to update ESG evaluation');
  }
}

export async function updateEsgEvaluationItem(
  id: number,
  data: Partial<NewEsgEvaluationItem>
) {
  try {
    return await db.transaction(async (tx) => {
      const [result] = await tx
        .update(esgEvaluationItems)
        .set({ ...data, updatedAt: new Date() })
        .where(eq(esgEvaluationItems.id, id))
        .returning();
      return result;
    });
  } catch (err) {
    console.error('Error updating ESG evaluation item:', err);
    throw new Error('Failed to update ESG evaluation item');
  }
}

export async function updateEsgAnswerOption(
  id: number,
  data: Partial<NewEsgAnswerOption>
) {
  try {
    return await db.transaction(async (tx) => {
      const [result] = await tx
        .update(esgAnswerOptions)
        .set({ ...data, updatedAt: new Date() })
        .where(eq(esgAnswerOptions.id, id))
        .returning();
      return result;
    });
  } catch (err) {
    console.error('Error updating ESG answer option:', err);
    throw new Error('Failed to update ESG answer option');
  }
}

// ============ 삭제 함수들 ============

export async function deleteEsgEvaluation(id: number) {
  try {
    return await db.transaction(async (tx) => {
      // Cascade delete가 설정되어 있어서 평가항목과 답변옵션들도 자동 삭제됨
      const [result] = await tx
        .delete(esgEvaluations)
        .where(eq(esgEvaluations.id, id))
        .returning();
      return result;
    });
  } catch (err) {
    console.error('Error deleting ESG evaluation:', err);
    throw new Error('Failed to delete ESG evaluation');
  }
}

export async function deleteEsgEvaluationItem(id: number) {
  try {
    return await db.transaction(async (tx) => {
      // Cascade delete가 설정되어 있어서 답변옵션들도 자동 삭제됨
      const [result] = await tx
        .delete(esgEvaluationItems)
        .where(eq(esgEvaluationItems.id, id))
        .returning();
      return result;
    });
  } catch (err) {
    console.error('Error deleting ESG evaluation item:', err);
    throw new Error('Failed to delete ESG evaluation item');
  }
}

export async function deleteEsgAnswerOption(id: number) {
  try {
    return await db.transaction(async (tx) => {
      const [result] = await tx
        .delete(esgAnswerOptions)
        .where(eq(esgAnswerOptions.id, id))
        .returning();
      return result;
    });
  } catch (err) {
    console.error('Error deleting ESG answer option:', err);
    throw new Error('Failed to delete ESG answer option');
  }
}

// ============ 소프트 삭제 함수들 ============

export async function softDeleteEsgEvaluation(id: number) {
  return updateEsgEvaluation(id, { isActive: false });
}

export async function softDeleteEsgEvaluationItem(id: number) {
  return updateEsgEvaluationItem(id, { isActive: false });
}

export async function softDeleteEsgAnswerOption(id: number) {
  return updateEsgAnswerOption(id, { isActive: false });
}



export async function updateEsgEvaluationWithItems(
  id: number,
  evaluationData: {
    serialNumber: string;
    category: string;
    inspectionItem: string;
  },
  items: Array<{
    evaluationItem: string;
    evaluationItemDescription: string;
    answerOptions: Array<{
      answerText: string;
      score: number;
    }>;
  }>
) {
  try {
    return await db.transaction(async (tx) => {
      // 1. 기본 정보 수정
      const [updatedEvaluation] = await tx
        .update(esgEvaluations)
        .set({
          ...evaluationData,
          updatedAt: new Date(),
        })
        .where(eq(esgEvaluations.id, id))
        .returning();

      // 2. 기존 평가항목들과 답변 옵션들 모두 삭제 (cascade delete로 답변옵션도 함께 삭제됨)
      await tx
        .delete(esgEvaluationItems)
        .where(eq(esgEvaluationItems.esgEvaluationId, id));

      // 3. 새로운 평가항목들과 답변 옵션들 생성
      for (let i = 0; i < items.length; i++) {
        const item = items[i];
        
        const [evaluationItem] = await tx
          .insert(esgEvaluationItems)
          .values({
            esgEvaluationId: id,
            evaluationItem: item.evaluationItem,
            orderIndex: i,
          })
          .returning();

        // 답변 옵션들 생성
        if (item.answerOptions.length > 0) {
          await tx.insert(esgAnswerOptions).values(
            item.answerOptions.map((option, optionIndex) => ({
              esgEvaluationItemId: evaluationItem.id,
              answerText: option.answerText,
              score: option.score.toString(),
              orderIndex: optionIndex,
            }))
          );
        }
      }

      return updatedEvaluation;
    });
  } catch (err) {
    console.error('Error updating ESG evaluation with items:', err);
    
    // 시리얼 번호 중복 에러 처리
    if (err instanceof Error && err.message.includes('unique')) {
      throw new Error('이미 존재하는 시리얼번호입니다.');
    }
    
    throw new Error('평가표 수정에 실패했습니다.');
  }
}

// ============ 소프트 삭제 버전 (데이터 보존) ============

export async function updateEsgEvaluationWithItemsSoft(
  id: number,
  evaluationData: {
    serialNumber: string;
    category: string;
    inspectionItem: string;
  },
  items: Array<{
    evaluationItem: string;
    answerOptions: Array<{
      answerText: string;
      score: number;
    }>;
  }>
) {
  try {
    return await db.transaction(async (tx) => {
      // 1. 기본 정보 수정
      const [updatedEvaluation] = await tx
        .update(esgEvaluations)
        .set({
          ...evaluationData,
          updatedAt: new Date(),
        })
        .where(eq(esgEvaluations.id, id))
        .returning();

      // 2. 기존 평가항목들 소프트 삭제
      await tx
        .update(esgEvaluationItems)
        .set({ isActive: false, updatedAt: new Date() })
        .where(eq(esgEvaluationItems.esgEvaluationId, id));

      // 기존 답변 옵션들도 소프트 삭제
      const existingItems = await tx
        .select({ id: esgEvaluationItems.id })
        .from(esgEvaluationItems)
        .where(eq(esgEvaluationItems.esgEvaluationId, id));

      for (const item of existingItems) {
        await tx
          .update(esgAnswerOptions)
          .set({ isActive: false, updatedAt: new Date() })
          .where(eq(esgAnswerOptions.esgEvaluationItemId, item.id));
      }

      // 3. 새로운 평가항목들과 답변 옵션들 생성
      for (let i = 0; i < items.length; i++) {
        const item = items[i];
        
        const [evaluationItem] = await tx
          .insert(esgEvaluationItems)
          .values({
            esgEvaluationId: id,
            evaluationItem: item.evaluationItem,
            orderIndex: i,
          })
          .returning();

        // 답변 옵션들 생성
        if (item.answerOptions.length > 0) {
          await tx.insert(esgAnswerOptions).values(
            item.answerOptions.map((option, optionIndex) => ({
              esgEvaluationItemId: evaluationItem.id,
              answerText: option.answerText,
              score: option.score.toString(),
              orderIndex: optionIndex,
            }))
          );
        }
      }

      return updatedEvaluation;
    });
  } catch (err) {
    console.error('Error updating ESG evaluation with items (soft):', err);
    
    if (err instanceof Error && err.message.includes('unique')) {
      throw new Error('이미 존재하는 시리얼번호입니다.');
    }
    
    throw new Error('평가표 수정에 실패했습니다.');
  }
}

// ============ 생성 함수 개선 (에러 처리 추가) ============

export async function createEsgEvaluationWithItemsEnhanced(
  evaluationData: {
    serialNumber: string;
    category: string;
    inspectionItem: string;
  },
  items: Array<{
    evaluationItem: string;
    evaluationItemDescription: string;
    answerOptions: Array<{
      answerText: string;
      score: number;
    }>;
  }>
) {
  try {
    return await db.transaction(async (tx) => {
      // 1. 평가표 생성
      const [evaluation] = await tx
        .insert(esgEvaluations)
        .values(evaluationData)
        .returning();

      // 2. 평가항목들과 답변 옵션들 생성
      for (let i = 0; i < items.length; i++) {
        const item = items[i];
        
        const [evaluationItem] = await tx
          .insert(esgEvaluationItems)
          .values({
            esgEvaluationId: evaluation.id,
            evaluationItem: item.evaluationItem,
            evaluationItemDescription: item.evaluationItemDescription,
            orderIndex: i,
          })
          .returning();

        // 답변 옵션들 생성
        if (item.answerOptions.length > 0) {
          await tx.insert(esgAnswerOptions).values(
            item.answerOptions.map((option, optionIndex) => ({
              esgEvaluationItemId: evaluationItem.id,
              answerText: option.answerText,
              score: option.score.toString(),
              orderIndex: optionIndex,
            }))
          );
        }
      }

      return evaluation;
    });
  } catch (err) {
    console.error('Error creating ESG evaluation with items:', err);
    
    // 시리얼 번호 중복 에러 처리
    if (err instanceof Error && err.message.includes('unique')) {
      throw new Error('이미 존재하는 시리얼번호입니다.');
    }
    
    throw new Error('평가표 생성에 실패했습니다.');
  }
}

export async function deleteEsgEvaluationsBatch(ids: number[]) {
  try {
    if (ids.length === 0) {
      throw new Error('삭제할 평가표가 없습니다.');
    }

    return await db.transaction(async (tx) => {
      let deletedCount = 0;
      
      for (const id of ids) {
        try {
          // 각 평가표 삭제 (cascade delete로 관련 데이터도 함께 삭제됨)
          await tx
            .delete(esgEvaluations)
            .where(eq(esgEvaluations.id, id));
          
          deletedCount++;
        } catch (error) {
          console.error(`Error deleting evaluation ${id}:`, error);
          // 개별 삭제 실패는 로그만 남기고 계속 진행
        }
      }

      return {
        total: ids.length,
        deleted: deletedCount,
        failed: ids.length - deletedCount
      };
    });
  } catch (err) {
    console.error('Error in batch delete ESG evaluations:', err);
    throw new Error('평가표 일괄 삭제에 실패했습니다.');
  }
}

export async function softDeleteEsgEvaluationsBatch(ids: number[]) {
  try {
    if (ids.length === 0) {
      throw new Error('삭제할 평가표가 없습니다.');
    }

    return await db.transaction(async (tx) => {
      let deletedCount = 0;
      
      for (const id of ids) {
        try {
          // 평가표 소프트 삭제
          await tx
            .update(esgEvaluations)
            .set({ 
              isActive: false,
              updatedAt: new Date(),
            })
            .where(eq(esgEvaluations.id, id));

          // 관련 평가항목들 소프트 삭제
          await tx
            .update(esgEvaluationItems)
            .set({ isActive: false, updatedAt: new Date() })
            .where(eq(esgEvaluationItems.esgEvaluationId, id));

          // 관련 답변 옵션들 소프트 삭제
          const evaluationItems = await tx
            .select({ id: esgEvaluationItems.id })
            .from(esgEvaluationItems)
            .where(eq(esgEvaluationItems.esgEvaluationId, id));

          for (const item of evaluationItems) {
            await tx
              .update(esgAnswerOptions)
              .set({ isActive: false, updatedAt: new Date() })
              .where(eq(esgAnswerOptions.esgEvaluationItemId, item.id));
          }
          
          deletedCount++;
        } catch (error) {
          console.error(`Error soft deleting evaluation ${id}:`, error);
          // 개별 삭제 실패는 로그만 남기고 계속 진행
        }
      }

      return {
        total: ids.length,
        deleted: deletedCount,
        failed: ids.length - deletedCount
      };
    });
  } catch (err) {
    console.error('Error in batch soft delete ESG evaluations:', err);
    throw new Error('평가표 일괄 삭제에 실패했습니다.');
  }
}