"use server";

import db from "@/db/db";
import { sql, eq, or, and, count, sum, inArray } from "drizzle-orm";
import { getServerSession } from "next-auth/next";
import { authOptions } from "@/app/api/auth/[...nextauth]/route";
import { getTablesByDomain } from "@/config/dashboard-table";
import { TableConfig } from "@/types/dashboard";

export interface DashboardStats {
  tableName: string;
  displayName: string;
  total: number;
  pending: number;
  inProgress: number;
  completed: number;
}

export interface UserDashboardStats extends DashboardStats {
  myTotal: number;
  myPending: number;
  myInProgress: number;
  myCompleted: number;
}

export interface DashboardData {
  domain: string;
  teamStats: DashboardStats[];
  userStats: UserDashboardStats[];
  summary: {
    totalTasks: number;
    myTasks: number;
    teamPending: number;
    teamInProgress: number;
    teamCompleted: number;
    myPending: number;
    myInProgress: number;
    myCompleted: number;
  };
}

// 팀 대시보드 데이터 조회
export async function getTeamDashboardData(domain: string): Promise<DashboardStats[]> {
  try {
    const tables = getTablesByDomain(domain);
    
    if (tables.length === 0) {
      console.warn(`도메인 '${domain}'에 대한 테이블이 없습니다.`);
      return [];
    }

    // 병렬 처리로 성능 향상
    const results = await Promise.allSettled(
      tables.map(tableConfig => getTableStats(tableConfig))
    );

    // 성공한 결과만 반환, 실패한 것들은 로그 출력
    const successfulResults: DashboardStats[] = [];
    results.forEach((result, index) => {
      if (result.status === 'fulfilled') {
        successfulResults.push(result.value);
      } else {
        console.error(`테이블 ${tables[index].tableName} 통계 조회 실패:`, result.reason);
      }
    });

    console.log('📊 팀 대시보드 결과:', successfulResults);

    return successfulResults;
  } catch (error) {
    console.error("팀 대시보드 데이터 조회 실패:", error);
    throw new Error("팀 대시보드 데이터를 불러오는데 실패했습니다.");
  }
}

// 사용자 대시보드 데이터 조회
export async function getUserDashboardData(domain: string): Promise<UserDashboardStats[]> {
  try {
    // 현재 사용자 정보 가져오기
    const session = await getServerSession(authOptions);
    if (!session?.user?.id) {
      throw new Error("인증되지 않은 사용자입니다.");
    }

    const userId = session.user.id;
    const tables = getTablesByDomain(domain);
    
    if (tables.length === 0) {
      console.warn(`도메인 '${domain}'에 대한 테이블이 없습니다.`);
      return [];
    }

    console.log(`👤 사용자 ID: ${userId}`);

    // 병렬 처리로 성능 향상
    const results = await Promise.allSettled(
      tables.map(async (tableConfig) => {
        const [teamStats, userStats] = await Promise.all([
          getTableStats(tableConfig),
          getUserTableStats(tableConfig, userId)
        ]);

        return {
          ...teamStats,
          myTotal: userStats.total,
          myPending: userStats.pending,
          myInProgress: userStats.inProgress,
          myCompleted: userStats.completed
        } as UserDashboardStats;
      })
    );

    // 성공한 결과만 반환
    const successfulResults: UserDashboardStats[] = [];
    results.forEach((result, index) => {
      if (result.status === 'fulfilled') {
        successfulResults.push(result.value);
      } else {
        console.error(`테이블 ${tables[index].tableName} 사용자 통계 조회 실패:`, result.reason);
      }
    });

    return successfulResults;
  } catch (error) {
    console.error("사용자 대시보드 데이터 조회 실패:", error);
    throw new Error("사용자 대시보드 데이터를 불러오는데 실패했습니다.");
  }
}


export async function refreshDashboardData(department: string = "engineering") {
  try {
    return await getDashboardData(department);
  } catch (error) {
    console.error("Dashboard refresh error:", error);
    throw error;
  }
}

// 전체 대시보드 데이터 조회 (팀 + 개인)
export async function getDashboardData(domain: string): Promise<DashboardData> {
  try {
    const session = await getServerSession(authOptions);
    if (!session?.user?.id) {
      throw new Error("인증되지 않은 사용자입니다.");
    }

    // 병렬 처리로 성능 향상
    const [teamStats, userStats] = await Promise.all([
      getTeamDashboardData(domain),
      getUserDashboardData(domain)
    ]);

    // 요약 통계 계산
    const summary = {
      totalTasks: teamStats.reduce((sum, stat) => sum + stat.total, 0),
      myTasks: userStats.reduce((sum, stat) => sum + stat.myTotal, 0),
      teamPending: teamStats.reduce((sum, stat) => sum + stat.pending, 0),
      teamInProgress: teamStats.reduce((sum, stat) => sum + stat.inProgress, 0),
      teamCompleted: teamStats.reduce((sum, stat) => sum + stat.completed, 0),
      myPending: userStats.reduce((sum, stat) => sum + stat.myPending, 0),
      myInProgress: userStats.reduce((sum, stat) => sum + stat.myInProgress, 0),
      myCompleted: userStats.reduce((sum, stat) => sum + stat.myCompleted, 0)
    };

    return {
      domain,
      teamStats,
      userStats,
      summary
    };
  } catch (error) {
    console.error("대시보드 데이터 조회 실패:", error);
    throw new Error("대시보드 데이터를 불러오는데 실패했습니다.");
  }
}

// 테이블별 전체 통계 조회 (완전히 수정된 버전)
async function getTableStats(config: TableConfig): Promise<DashboardStats> {
  try {
    console.log(`\n🔍 테이블 ${config.tableName} 통계 조회 시작`);
    
    // 1단계: 기본 총 개수 확인
    console.log("1단계: 총 개수 조회");
    const totalQuery = `SELECT COUNT(*)::INTEGER as total FROM "${config.tableName}"`;
    console.log("Total SQL:", totalQuery);
    
    const totalResult = await db.execute(sql.raw(totalQuery));
    console.log("Total 결과:", totalResult.rows[0]);
    
    // 2단계: 실제 상태값 확인
    console.log("2단계: 상태값 분포 확인");
    const statusQuery = `
      SELECT "${config.statusField}" as status, COUNT(*) as count
      FROM "${config.tableName}"
      WHERE "${config.statusField}" IS NOT NULL
      GROUP BY "${config.statusField}"
      ORDER BY count DESC
    `;
    console.log("Status SQL:", statusQuery);
    
    const statusResult = await db.execute(sql.raw(statusQuery));
    console.log("Status 결과:", statusResult.rows);
    
    // 3단계: 상태별 개수 조회 (개별 쿼리)
    console.log("3단계: 상태별 개수 조회");
    
    const pendingValues = Object.entries(config.statusMapping)
      .filter(([_, mapped]) => mapped === 'pending')
      .map(([original]) => original);
    
    const inProgressValues = Object.entries(config.statusMapping)
      .filter(([_, mapped]) => mapped === 'in_progress')
      .map(([original]) => original);
    
    const completedValues = Object.entries(config.statusMapping)
      .filter(([_, mapped]) => mapped === 'completed')
      .map(([original]) => original);
    
    console.log("매핑된 상태값:");
    console.log("- pending:", pendingValues);
    console.log("- inProgress:", inProgressValues);
    console.log("- completed:", completedValues);
    
    // 개별 쿼리로 정확한 개수 조회
    let pendingCount = 0;
    let inProgressCount = 0; 
    let completedCount = 0;
    
    // Pending 개수
    if (pendingValues.length > 0) {
      const pendingValuesList = pendingValues.map(v => `'${v.replace(/'/g, "''")}'`).join(',');
      const pendingQuery = `
        SELECT COUNT(*)::INTEGER as count
        FROM "${config.tableName}"
        WHERE "${config.statusField}" IN (${pendingValuesList})
      `;
      console.log("Pending SQL:", pendingQuery);
      
      const pendingResult = await db.execute(sql.raw(pendingQuery));
      pendingCount = parseInt(pendingResult.rows[0]?.count || '0');
      console.log("Pending 개수:", pendingCount);
    }
    
    // In Progress 개수
    if (inProgressValues.length > 0) {
      const inProgressValuesList = inProgressValues.map(v => `'${v.replace(/'/g, "''")}'`).join(',');
      const inProgressQuery = `
        SELECT COUNT(*)::INTEGER as count
        FROM "${config.tableName}"
        WHERE "${config.statusField}" IN (${inProgressValuesList})
      `;
      console.log("InProgress SQL:", inProgressQuery);
      
      const inProgressResult = await db.execute(sql.raw(inProgressQuery));
      inProgressCount = parseInt(inProgressResult.rows[0]?.count || '0');
      console.log("InProgress 개수:", inProgressCount);
    }
    
    // Completed 개수
    if (completedValues.length > 0) {
      const completedValuesList = completedValues.map(v => `'${v.replace(/'/g, "''")}'`).join(',');
      const completedQuery = `
        SELECT COUNT(*)::INTEGER as count
        FROM "${config.tableName}"
        WHERE "${config.statusField}" IN (${completedValuesList})
      `;
      console.log("Completed SQL:", completedQuery);
      
      const completedResult = await db.execute(sql.raw(completedQuery));
      completedCount = parseInt(completedResult.rows[0]?.count || '0');
      console.log("Completed 개수:", completedCount);
    }
    
    const stats = {
      tableName: config.tableName,
      displayName: config.displayName,
      total: parseInt(totalResult.rows[0]?.total || '0'),
      pending: pendingCount,
      inProgress: inProgressCount,
      completed: completedCount
    };

    console.log(`✅ ${config.tableName} 최종 통계:`, stats);
    return stats;
  } catch (error) {
    console.error(`❌ 테이블 ${config.tableName} 통계 조회 중 오류:`, error);
    // 에러 발생 시 빈 통계 반환
    return createEmptyStats(config);
  }
}

// 사용자별 테이블 통계 조회 (수정된 버전)
async function getUserTableStats(config: TableConfig, userId: string): Promise<DashboardStats> {
  try {
    // 사용자 필드가 없는 경우 빈 통계 반환
    if (!hasUserFields(config)) {
      console.log(`⚠️ 테이블 ${config.tableName}에 사용자 필드가 없습니다.`);
      return createEmptyStats(config);
    }

    console.log(`\n👤 사용자 ${userId}의 ${config.tableName} 통계 조회`);

    // 사용자 조건 생성
    const userConditions = [];
    if (config.userFields.creator) {
      userConditions.push(`"${config.userFields.creator}" = '${userId}'`);
    }
    if (config.userFields.updater) {
      userConditions.push(`"${config.userFields.updater}" = '${userId}'`);
    }
    if (config.userFields.assignee) {
      userConditions.push(`"${config.userFields.assignee}" = '${userId}'`);
    }
    
    if (userConditions.length === 0) {
      return createEmptyStats(config);
    }
    
    const userConditionStr = userConditions.join(' OR ');
    
    // 1. 사용자 총 개수
    const userTotalQuery = `
      SELECT COUNT(*)::INTEGER as total 
      FROM "${config.tableName}"
      WHERE ${userConditionStr}
    `;
    console.log("User Total SQL:", userTotalQuery);
    
    const userTotalResult = await db.execute(sql.raw(userTotalQuery));
    console.log("User Total 결과:", userTotalResult.rows[0]);
    
    // 2. 사용자 상태별 개수
    const pendingValues = Object.entries(config.statusMapping)
      .filter(([_, mapped]) => mapped === 'pending')
      .map(([original]) => original);
    
    const inProgressValues = Object.entries(config.statusMapping)
      .filter(([_, mapped]) => mapped === 'in_progress')
      .map(([original]) => original);
    
    const completedValues = Object.entries(config.statusMapping)
      .filter(([_, mapped]) => mapped === 'completed')
      .map(([original]) => original);
    
    let userPendingCount = 0;
    let userInProgressCount = 0; 
    let userCompletedCount = 0;
    
    // User Pending 개수
    if (pendingValues.length > 0) {
      const pendingValuesList = pendingValues.map(v => `'${v.replace(/'/g, "''")}'`).join(',');
      const userPendingQuery = `
        SELECT COUNT(*)::INTEGER as count
        FROM "${config.tableName}"
        WHERE (${userConditionStr}) AND "${config.statusField}" IN (${pendingValuesList})
      `;
      
      const userPendingResult = await db.execute(sql.raw(userPendingQuery));
      userPendingCount = parseInt(userPendingResult.rows[0]?.count || '0');
      console.log("User Pending 개수:", userPendingCount);
    }
    
    // User In Progress 개수
    if (inProgressValues.length > 0) {
      const inProgressValuesList = inProgressValues.map(v => `'${v.replace(/'/g, "''")}'`).join(',');
      const userInProgressQuery = `
        SELECT COUNT(*)::INTEGER as count
        FROM "${config.tableName}"
        WHERE (${userConditionStr}) AND "${config.statusField}" IN (${inProgressValuesList})
      `;
      
      const userInProgressResult = await db.execute(sql.raw(userInProgressQuery));
      userInProgressCount = parseInt(userInProgressResult.rows[0]?.count || '0');
      console.log("User InProgress 개수:", userInProgressCount);
    }
    
    // User Completed 개수
    if (completedValues.length > 0) {
      const completedValuesList = completedValues.map(v => `'${v.replace(/'/g, "''")}'`).join(',');
      const userCompletedQuery = `
        SELECT COUNT(*)::INTEGER as count
        FROM "${config.tableName}"
        WHERE (${userConditionStr}) AND "${config.statusField}" IN (${completedValuesList})
      `;
      
      const userCompletedResult = await db.execute(sql.raw(userCompletedQuery));
      userCompletedCount = parseInt(userCompletedResult.rows[0]?.count || '0');
      console.log("User Completed 개수:", userCompletedCount);
    }

    const stats = {
      tableName: config.tableName,
      displayName: config.displayName,
      total: parseInt(userTotalResult.rows[0]?.total || '0'),
      pending: userPendingCount,
      inProgress: userInProgressCount,
      completed: userCompletedCount
    };

    console.log(`✅ 사용자 ${config.tableName} 최종 통계:`, stats);
    return stats;
  } catch (error) {
    console.error(`❌ 테이블 ${config.tableName} 사용자 통계 조회 중 오류:`, error);
    return createEmptyStats(config);
  }
}

// 유틸리티 함수들
function createEmptyStats(config: TableConfig): DashboardStats {
  return {
    tableName: config.tableName,
    displayName: config.displayName,
    total: 0,
    pending: 0,
    inProgress: 0,
    completed: 0
  };
}

function hasUserFields(config: TableConfig): boolean {
  return !!(config.userFields.creator || config.userFields.updater || config.userFields.assignee);
}

// 디버깅 함수: 단순한 테스트
export async function simpleTest(tableName: string, statusField: string) {
  try {
    console.log(`\n🧪 ${tableName} 간단한 테스트:`);
    
    // 1. 총 개수
    const totalQuery = `SELECT COUNT(*) as total FROM "${tableName}"`;
    const totalResult = await db.execute(sql.raw(totalQuery));
    console.log("총 개수:", totalResult.rows[0]);
    
    // 2. 상태 분포
    const statusQuery = `
      SELECT "${statusField}" as status, COUNT(*) as count
      FROM "${tableName}"
      GROUP BY "${statusField}"
      ORDER BY count DESC
    `;
    const statusResult = await db.execute(sql.raw(statusQuery));
    console.log("상태 분포:", statusResult.rows);
    
    // 3. 특정 상태 테스트
    const draftQuery = `SELECT COUNT(*) as count FROM "${tableName}" WHERE "${statusField}" = 'DRAFT'`;
    const draftResult = await db.execute(sql.raw(draftQuery));
    console.log("DRAFT 개수:", draftResult.rows[0]);
    
    const docConfirmedQuery = `SELECT COUNT(*) as count FROM "${tableName}" WHERE "${statusField}" = 'Doc. Confirmed'`;
    const docConfirmedResult = await db.execute(sql.raw(docConfirmedQuery));
    console.log("Doc. Confirmed 개수:", docConfirmedResult.rows[0]);
    
    return {
      total: totalResult.rows[0],
      statusDistribution: statusResult.rows,
      draft: draftResult.rows[0],
      docConfirmed: docConfirmedResult.rows[0]
    };
  } catch (error) {
    console.error("간단한 테스트 실패:", error);
    return null;
  }
}