import db from "@/db/db";
import { esgAnswerOptions, esgEvaluationItems, esgEvaluations, esgEvaluationsView, projects } from "@/db/schema";
import { Item, items } from "@/db/schema/items";
import { formListsView, tagTypeClassFormMappings } from "@/db/schema/vendorData";
import {
    eq,
    inArray,
    not,
    asc,
    desc,
    and,
    ilike,
    gte,
    lte,
    count,
    gt,
} from "drizzle-orm";
import { PgTransaction } from "drizzle-orm/pg-core";
// import { DatabaseConnection } from '@/types/database';


export async function selectEsgEvaluations(
    tx: PgTransaction<any, any, any>,
    params: {
        where?: any;
        orderBy?: (ReturnType<typeof asc> | ReturnType<typeof desc>)[];
        offset?: number;
        limit?: number;
    }
) {

    const { where, orderBy, offset = 0, limit = 10 } = params;

    return await tx
        .select()
        .from(esgEvaluationsView)
        .where(where)
        .orderBy(...(orderBy ?? [asc(esgEvaluationsView.createdAt)]))
        .offset(offset ?? 0)
        .limit(limit ?? 10);
}

export async function countEsgEvaluations(
    tx: PgTransaction<any, any, any>,
    where?: any
) {
    const result = await tx
        .select({ count: count() })
        .from(esgEvaluationsView)
        .where(where);

    return result[0]?.count ?? 0;
}

// 상세 데이터 조회 (평가항목과 답변 옵션 포함)
export async function getEsgEvaluationWithDetails(
    tx: PgTransaction<any, any, any>,
    id: number
) {
    // 메인 평가표 정보
    const evaluation = await tx
        .select()
        .from(esgEvaluations)
        .where(eq(esgEvaluations.id, id))
        .limit(1);

    if (!evaluation[0]) return null;

    // 평가항목들과 답변 옵션들
    const items = await tx
        .select({
            // 평가항목 필드들
            itemId: esgEvaluationItems.id,
            evaluationItem: esgEvaluationItems.evaluationItem,
            evaluationItemDescription: esgEvaluationItems.evaluationItemDescription,
            itemOrderIndex: esgEvaluationItems.orderIndex,
            itemIsActive: esgEvaluationItems.isActive,
            itemCreatedAt: esgEvaluationItems.createdAt,
            itemUpdatedAt: esgEvaluationItems.updatedAt,
            // 답변 옵션 필드들
            optionId: esgAnswerOptions.id,
            answerText: esgAnswerOptions.answerText,
            score: esgAnswerOptions.score,
            optionOrderIndex: esgAnswerOptions.orderIndex,
            optionIsActive: esgAnswerOptions.isActive,
            optionCreatedAt: esgAnswerOptions.createdAt,
            optionUpdatedAt: esgAnswerOptions.updatedAt,
        })
        .from(esgEvaluationItems)
        .leftJoin(esgAnswerOptions, eq(esgEvaluationItems.id, esgAnswerOptions.esgEvaluationItemId))
        .where(eq(esgEvaluationItems.esgEvaluationId, id))
        .orderBy(
            asc(esgEvaluationItems.orderIndex),
            asc(esgAnswerOptions.orderIndex)
        );



    // 데이터 구조화
    const itemsMap = new Map();

    items.forEach((row) => {
        if (!itemsMap.has(row.itemId)) {
            itemsMap.set(row.itemId, {
                id: row.itemId,
                evaluationItem: row.evaluationItem,
                evaluationItemDescription: row.evaluationItemDescription,
                orderIndex: row.itemOrderIndex,
                isActive: row.itemIsActive,
                createdAt: row.itemCreatedAt,
                updatedAt: row.itemUpdatedAt,
                answerOptions: [],
            });
        }

        if (row.optionId) {
            itemsMap.get(row.itemId).answerOptions.push({
                id: row.optionId,
                answerText: row.answerText,
                score: row.score,
                orderIndex: row.optionOrderIndex,
                isActive: row.optionIsActive,
                createdAt: row.optionCreatedAt,
                updatedAt: row.optionUpdatedAt,
            });
        }
    });

    return {
        ...evaluation[0],
        evaluationItems: Array.from(itemsMap.values()),
    };
}