"use server";

import db from "@/db/db";
import { sql } from "drizzle-orm";
import { getServerSession } from "next-auth/next";
import { authOptions } from "@/app/api/auth/[...nextauth]/route";
import { getPartnerTablesByDomain } from "@/config/partners-dashboard-table";
import { TableConfig } from "@/types/dashboard";

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

export interface PartnersUserDashboardStats extends PartnersDashboardStats {
  myTotal: number;
  myPending: number;
  myInProgress: number;
  myCompleted: number;
}

export interface PartnersDashboardData {
  domain: string;
  companyId: string;
  teamStats: PartnersDashboardStats[];
  userStats: PartnersUserDashboardStats[];
  summary: {
    totalTasks: number;
    myTasks: number;
    teamPending: number;
    teamInProgress: number;
    teamCompleted: number;
    myPending: number;
    myInProgress: number;
    myCompleted: number;
  };
}

// Partners 팀 대시보드 데이터 조회 (회사 필터링 포함)
export async function getPartnersTeamDashboardData(domain: string): Promise<PartnersDashboardStats[]> {
  try {
    const session = await getServerSession(authOptions);
    if (!session?.user?.companyId) {
      throw new Error("회사 정보가 없습니다.");
    }

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

    console.log(`👥 회사 ID: ${companyId}로 파트너 데이터 조회`);

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

    // 성공한 결과만 반환
    const successfulResults: PartnersDashboardStats[] = [];
    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("파트너 팀 대시보드 데이터를 불러오는데 실패했습니다.");
  }
}

// Partners 사용자 대시보드 데이터 조회
export async function getPartnersUserDashboardData(domain: string): Promise<PartnersUserDashboardStats[]> {
  try {
    const session = await getServerSession(authOptions);
    if (!session?.user?.id || !session?.user?.companyId) {
      throw new Error("사용자 또는 회사 정보가 없습니다.");
    }

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


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

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

    // 성공한 결과만 반환
    const successfulResults: PartnersUserDashboardStats[] = [];
    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("파트너 사용자 대시보드 데이터를 불러오는데 실패했습니다.");
  }
}

// Partners 전체 대시보드 데이터 조회
export async function getPartnersDashboardData(domain: string): Promise<PartnersDashboardData> {
  try {
    const session = await getServerSession(authOptions);
    if (!session?.user?.id || !session?.user?.companyId) {
      throw new Error("사용자 또는 회사 정보가 없습니다.");
    }

    const [teamStats, userStats] = await Promise.all([
      getPartnersTeamDashboardData(domain),
      getPartnersUserDashboardData(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,
      companyId: session.user.companyId,
      teamStats,
      userStats,
      summary
    };
  } catch (error) {
    console.error("파트너 대시보드 데이터 조회 실패:", error);
    throw new Error("파트너 대시보드 데이터를 불러오는데 실패했습니다.");
  }
}

// Partners 테이블별 전체 통계 조회 (회사 필터링 포함)
async function getPartnersTableStats(config: TableConfig, companyId: string): Promise<PartnersDashboardStats> {
  try {
    
    // 1단계: 회사별 총 개수 확인
    const totalQuery = `
      SELECT COUNT(*)::INTEGER as total 
      FROM "${config.tableName}" 
      WHERE "vendor_id" = '${companyId}'
    `;
    
    const totalResult = await db.execute(sql.raw(totalQuery));
    
    // 2단계: 회사별 상태값 분포 확인
    const statusQuery = `
      SELECT "${config.statusField}" as status, COUNT(*) as count
      FROM "${config.tableName}"
      WHERE "vendor_id" = '${companyId}' AND "${config.statusField}" IS NOT NULL
      GROUP BY "${config.statusField}"
      ORDER BY count DESC
    `;
    
    const statusResult = await db.execute(sql.raw(statusQuery));
    
    // 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);
    

    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 "vendor_id" = '${companyId}' AND "${config.statusField}" IN (${pendingValuesList})
      `;
      
      const pendingResult = await db.execute(sql.raw(pendingQuery));
      pendingCount = parseInt(pendingResult.rows[0]?.count || '0');
    }
    
    // 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 "vendor_id" = '${companyId}' AND "${config.statusField}" IN (${inProgressValuesList})
      `;
      
      const inProgressResult = await db.execute(sql.raw(inProgressQuery));
      inProgressCount = parseInt(inProgressResult.rows[0]?.count || '0');
    }
    
    // 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 "vendor_id" = '${companyId}' AND "${config.statusField}" IN (${completedValuesList})
      `;
      
      const completedResult = await db.execute(sql.raw(completedQuery));
      completedCount = parseInt(completedResult.rows[0]?.count || '0');
    }
    
    const stats = {
      tableName: config.tableName,
      displayName: config.displayName,
      total: parseInt(totalResult.rows[0]?.total || '0'),
      pending: pendingCount,
      inProgress: inProgressCount,
      completed: completedCount
    };

    return stats;
  } catch (error) {
    console.error(`❌ 파트너 테이블 ${config.tableName} 통계 조회 중 오류:`, error);
    return createEmptyPartnersStats(config);
  }
}

// Partners 사용자별 테이블 통계 조회 (회사 + 사용자 필터링)
async function getPartnersUserTableStats(config: TableConfig, companyId: string, userId: string): Promise<PartnersDashboardStats> {
  try {
    // 사용자 필드가 없는 경우 빈 통계 반환
    if (!hasUserFields(config)) {
      return createEmptyPartnersStats(config);
    }


    // 사용자 조건 생성 (회사 필터 포함)
    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 createEmptyPartnersStats(config);
    }
    
    const userConditionStr = userConditions.join(' OR ');
    
    // 1. 사용자 + 회사 총 개수
    const userTotalQuery = `
      SELECT COUNT(*)::INTEGER as total 
      FROM "${config.tableName}"
      WHERE "vendor_id" = '${companyId}' AND (${userConditionStr})
    `;
    
    const userTotalResult = await db.execute(sql.raw(userTotalQuery));
    
    // 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 + Company 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 "vendor_id" = '${companyId}' AND (${userConditionStr}) AND "${config.statusField}" IN (${pendingValuesList})
      `;
      
      const userPendingResult = await db.execute(sql.raw(userPendingQuery));
      userPendingCount = parseInt(userPendingResult.rows[0]?.count || '0');
    }
    
    // User + Company 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 "vendor_id" = '${companyId}' AND (${userConditionStr}) AND "${config.statusField}" IN (${inProgressValuesList})
      `;
      
      const userInProgressResult = await db.execute(sql.raw(userInProgressQuery));
      userInProgressCount = parseInt(userInProgressResult.rows[0]?.count || '0');
    }
    
    // User + Company 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 "vendor_id" = '${companyId}' AND (${userConditionStr}) AND "${config.statusField}" IN (${completedValuesList})
      `;
      
      const userCompletedResult = await db.execute(sql.raw(userCompletedQuery));
      userCompletedCount = parseInt(userCompletedResult.rows[0]?.count || '0');
    }

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

    return stats;
  } catch (error) {
    console.error(`❌ 파트너 테이블 ${config.tableName} 사용자 통계 조회 중 오류:`, error);
    return createEmptyPartnersStats(config);
  }
}

// 유틸리티 함수들
function createEmptyPartnersStats(config: TableConfig): PartnersDashboardStats {
  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);
}

// 디버깅 함수: Partners 전용
export async function simplePartnersTest(tableName: string, statusField: string, companyId: string) {
  try {
    
    // 1. 회사별 총 개수
    const totalQuery = `SELECT COUNT(*) as total FROM "${tableName}" WHERE "vendor_id" = '${companyId}'`;
    const totalResult = await db.execute(sql.raw(totalQuery));
    
    // 2. 회사별 상태 분포
    const statusQuery = `
      SELECT "${statusField}" as status, COUNT(*) as count
      FROM "${tableName}"
      WHERE "vendor_id" = '${companyId}'
      GROUP BY "${statusField}"
      ORDER BY count DESC
    `;
    const statusResult = await db.execute(sql.raw(statusQuery));
    
    return {
      total: totalResult.rows[0],
      statusDistribution: statusResult.rows
    };
  } catch (error) {
    console.error("파트너 간단한 테스트 실패:", error);
    return null;
  }
}