client/src/lib/projectUtils.ts

/**
 * Project utilities for parsing and processing Excel data
 * Three-section data model support
 */

import * as XLSX from 'xlsx';
import { ProjectData, ProjectStats, DEFAULT_COLUMN_MAPPING } from '@/types/project';

/**
 * Set nested property value using dot notation path
 */
function setNestedValue(obj: any, path: string, value: any) {
  const keys = path.split('.');
  let current = obj;
  
  for (let i = 0; i < keys.length - 1; i++) {
    const key = keys[i];
    if (!current[key]) {
      current[key] = {};
    }
    current = current[key];
  }
  
  current[keys[keys.length - 1]] = value;
}

/**
 * Get nested property value using dot notation path
 */
function getNestedValue(obj: any, path: string): any {
  const keys = path.split('.');
  let current = obj;
  
  for (const key of keys) {
    if (current === undefined || current === null) {
      return undefined;
    }
    current = current[key];
  }
  
  return current;
}

/**
 * Parse Excel serial date number to ISO date string
 */
function parseExcelDate(value: any): string {
  if (!value) return '';
  
  // If it's a number, treat as Excel serial date
  if (typeof value === 'number') {
    try {
      const date = XLSX.SSF.parse_date_code(value);
      return `${date.y}-${String(date.m).padStart(2, '0')}-${String(date.d).padStart(2, '0')}`;
    } catch {
      return String(value);
    }
  }
  
  // If it's already a string, try to parse as date
  if (typeof value === 'string') {
    const dateTest = new Date(value);
    if (!isNaN(dateTest.getTime())) {
      return dateTest.toISOString().split('T')[0];
    }
  }
  
  return String(value);
}

/**
 * Format date value for display in "MM月DD日" format
 * Handles Excel serial numbers, text strings, and empty values
 * 
 * @param value - Raw value from Excel cell (number, string, or undefined)
 * @returns Formatted date string like "12月24日" or "暂无日期"
 */
export function formatExcelDate(value: any): string {
  // Handle empty/undefined first
  if (!value || value === '') {
    return '暂无日期';
  }
  
  // PRIORITY 1: If it's already a clean string like "12月24日", just return it
  if (typeof value === 'string' && value.includes('月')) {
    return value;
  }
  
  // PRIORITY 2: If it's a number (e.g., 45325), convert it strictly
  if (!isNaN(value) && typeof value === 'number') {
    try {
      // Excel serial date formula: (serial - 25569) * 86400 * 1000
      const date = new Date((value - 25569) * 86400 * 1000);
      const month = date.getMonth() + 1;
      const day = date.getDate();
      return `${month}月${day}日`;
    } catch {
      return String(value);
    }
  }
  
  // PRIORITY 3: Try to parse as ISO date (YYYY-MM-DD)
  if (typeof value === 'string') {
    const dateMatch = value.match(/(\d{4})-(\d{1,2})-(\d{1,2})/);
    if (dateMatch) {
      const month = parseInt(dateMatch[2]);
      const day = parseInt(dateMatch[3]);
      return `${month}月${day}日`;
    }
  }
  
  // Fallback: return as string
  return String(value);
}

/**
 * Parse Excel file and extract project data
 */
export function parseExcelFile(file: File): Promise<ProjectData[]> {
  return new Promise((resolve, reject) => {
    const reader = new FileReader();
    
    reader.onload = (e) => {
      try {
        const data = e.target?.result;
        const workbook = XLSX.read(data, { type: 'binary' });
        const firstSheet = workbook.Sheets[workbook.SheetNames[0]];
        const jsonData = XLSX.utils.sheet_to_json(firstSheet, { 
          header: 1,
          raw: false, // Force reading formatted strings instead of raw numbers
          dateNF: 'mm"月"dd"日"' // Hint for date format
        }) as any[][];
        
        // Find header rows (row 0 and 1 are merged headers, row 2 is the actual header)
        const headerRowIndex = 2;
        const headers = jsonData[headerRowIndex] as string[];
        
        // Parse data rows (starting from row 3)
        const projects: ProjectData[] = [];
        
        for (let i = headerRowIndex + 1; i < jsonData.length; i++) {
          const row = jsonData[i];
          
          // Skip empty rows or rows that are just instructions
          if (!row || !row[0] || String(row[0]).includes('列')) {
            continue;
          }
          
          // Initialize project with nested structure
          const project: any = {
            no: '',
            identity: {},
            milestones: {},
            details: {}
          };
          
          // First column (index 0) is NO., even though header is undefined/empty
          project.no = row[0] !== undefined && row[0] !== null ? String(row[0]) : '-';
          
          // Map other columns starting from index 1
          headers.forEach((header, index) => {
            if (index === 0) return; // Skip first column as we already handled it
            
            const mappedPath = DEFAULT_COLUMN_MAPPING[header];
            if (mappedPath) {
              const value = row[index];
              let processedValue = value !== undefined && value !== null ? String(value) : '-';
              
              // Special handling for date fields
              if (mappedPath.includes('milestones.') && 
                  (mappedPath.includes('projectStart') || mappedPath.includes('t1') || 
                   mappedPath.includes('glTime') || mappedPath.includes('vmp') || 
                   mappedPath.includes('mp') || mappedPath.includes('detailDate'))) {
                processedValue = parseExcelDate(value) || '-';
              }
              
              setNestedValue(project, mappedPath, processedValue);
            }
          });
          
          // Only add if it has a project name
          if (project.identity?.projectName && project.identity.projectName !== '-') {
            projects.push(project as ProjectData);
          }
        }
        
        resolve(projects);
      } catch (error) {
        reject(error);
      }
    };
    
    reader.onerror = () => reject(new Error('文件读取失败'));
    reader.readAsBinaryString(file);
  });
}

/**
 * Get risk level color based on risk level text
 */
export function getRiskColor(riskLevel: string): string {
  const level = riskLevel?.trim() || '';
  
  if (level === '高' || level.includes('高')) {
    return 'rgb(220, 38, 38)'; // Red for high risk
  } else if (level === '中' || level.includes('中')) {
    return 'rgb(234, 179, 8)'; // Yellow for medium risk
  } else {
    return 'rgb(34, 197, 94)'; // Green for low/normal risk
  }
}

/**
 * Get risk level badge variant
 */
export function getRiskBadgeClass(riskLevel: string): string {
  const level = riskLevel?.trim() || '';
  
  if (level === '高' || level.includes('高')) {
    return 'bg-red-100 text-red-700 border-red-300';
  } else if (level === '中' || level.includes('中')) {
    return 'bg-yellow-100 text-yellow-700 border-yellow-300';
  } else {
    return 'bg-green-100 text-green-700 border-green-300';
  }
}

/**
 * Get status badge class
 */
export function getStatusBadgeClass(status: string): string {
  const statusText = status?.trim() || '';
  
  if (statusText === '已完成' || statusText.includes('完成')) {
    return 'bg-green-100 text-green-700 border-green-300';
  } else if (statusText === '进行中' || statusText.includes('进行')) {
    return 'bg-yellow-100 text-yellow-700 border-yellow-300';
  } else if (statusText === '已超时' || statusText.includes('超时')) {
    return 'bg-red-100 text-red-700 border-red-300';
  } else {
    return 'bg-gray-100 text-gray-700 border-gray-300';
  }
}

/**
 * Calculate project statistics based EXCLUSIVELY on 当前节点 (Current Node)
 * SINGLE SOURCE OF TRUTH - Ignore all other columns for status determination
 * 
 * Strict Mapping:
 * - "已完成" (Completed) → Green Theme
 * - "已超时" (Overdue) → Red Theme
 * - "进行中" (Ongoing) → Blue Theme
 * - Anything else or empty → Do NOT count, treat as null
 */
export function calculateStats(projects: ProjectData[]): ProjectStats {
  const stats: ProjectStats = {
    total: projects.length,
    highRisk: 0,  // Now represents "已超时" (Overdue) ONLY
    inProgress: 0,
    completed: 0
  };
  
  projects.forEach(project => {
    const currentNode = project.milestones?.currentNode?.trim() || ''; // No default, treat empty as null
    
    // Strict matching - only count exact matches
    if (currentNode === '已完成') {
      stats.completed++;
    } else if (currentNode === '已超时') {
      stats.highRisk++;
    } else if (currentNode === '进行中') {
      stats.inProgress++;
    }
    // Anything else: do NOT count in any category
  });
  
  return stats;
}

/**
 * Format date string for display
 */
export function formatDate(dateStr: string): string {
  if (!dateStr || dateStr === '-' || dateStr === 'N/A') {
    return '-';
  }
  
  try {
    // If already in YYYY-MM-DD format, return as is
    if (/^\d{4}-\d{2}-\d{2}$/.test(dateStr)) {
      return dateStr;
    }
    
    // Try to parse as date
    const date = new Date(dateStr);
    if (!isNaN(date.getTime())) {
      return date.toISOString().split('T')[0];
    }
    
    return dateStr;
  } catch {
    return dateStr;
  }
}

/**
 * Get display value with fallback
 */
export function getDisplayValue(value: string | undefined | null, fallback: string = '-'): string {
  if (!value || value === 'undefined' || value === 'null' || value === '-') {
    return fallback;
  }
  return value;
}

/**
 * Check if project is high risk (for red border highlighting)
 * @deprecated Use getCardVisualState instead
 */
export function isHighRisk(riskLevel: string): boolean {
  const level = riskLevel?.trim() || '';
  return level === '高' || level.includes('高');
}

/**
 * Get card visual state based EXCLUSIVELY on 当前节点 (Current Node)
 * SINGLE SOURCE OF TRUTH - Returns visual styling configuration
 * 
 * Strict Mapping:
 * - "已完成" → Green border + Light green background
 * - "已超时" → Red border + Light red background
 * - "进行中" → Blue border + White background
 * - Anything else → Neutral style, no badge
 */
export function getCardVisualState(currentNode: string | undefined) {
  const node = currentNode?.trim() || ''; // No default, treat empty as null
  
  // Case A: 已完成 (Completed) - Green Theme
  if (node === '已完成') {
    return {
      type: 'completed' as const,
      borderClass: 'border-2 border-emerald-500',
      bgClass: 'bg-emerald-50',
      badgeClass: 'bg-emerald-100 text-emerald-700 border-emerald-300',
      badgeLabel: '已完成',
      showBadge: true
    };
  }
  
  // Case B: 已超时 (Overdue) - Red Theme
  if (node === '已超时') {
    return {
      type: 'overdue' as const,
      borderClass: 'border-2 border-red-600',
      bgClass: 'bg-red-50',
      badgeClass: 'bg-red-100 text-red-700 border-red-300',
      badgeLabel: '已超时',
      showBadge: true
    };
  }
  
  // Case C: 进行中 (Ongoing) - Blue Theme
  if (node === '进行中') {
    return {
      type: 'ongoing' as const,
      borderClass: 'border-2 border-blue-500',
      bgClass: 'bg-white',
      badgeClass: 'bg-blue-100 text-blue-700 border-blue-300',
      badgeLabel: '进行中',
      showBadge: true
    };
  }
  
  // Default: Null/Unknown - Neutral style, no badge
  return {
    type: 'unknown' as const,
    borderClass: 'border border-slate-200',
    bgClass: 'bg-white',
    badgeClass: 'bg-slate-100 text-slate-500 border-slate-300',
    badgeLabel: 'N/A',
    showBadge: false
  };
}

/**
 * Parse FAI value to percentage format
 * Handles both decimal (0.9) and percentage (90%) inputs
 * Returns formatted string like "90%"
 */
export function parseFAIValue(value: string | undefined | null): string {
  if (!value || value === '-' || value === 'N/A') {
    return '-';
  }
  
  const strValue = String(value).trim();
  
  // If already has %, just return it
  if (strValue.includes('%')) {
    return strValue;
  }
  
  // Try to parse as number
  const numValue = parseFloat(strValue);
  if (isNaN(numValue)) {
    return strValue; // Return as-is if not a number
  }
  
  // If value is between 0 and 1, treat as decimal (e.g., 0.9 = 90%)
  if (numValue > 0 && numValue <= 1) {
    return `${Math.round(numValue * 100)}%`;
  }
  
  // If value is 1, treat as 100%
  if (numValue === 1) {
    return '100%';
  }
  
  // If value is greater than 1, assume it's already a percentage
  return `${Math.round(numValue)}%`;
}

/**
 * Get FAI color class based on value (traffic light system)
 * < 100% = Red (alert)
 * = 100% = Green (pass)
 */
export function getFAIColorClass(value: string | undefined | null): string {
  const parsed = parseFAIValue(value);
  
  if (parsed === '-' || parsed === 'N/A') {
    return 'text-gray-500';
  }
  
  // Extract numeric value from percentage string
  const numericMatch = parsed.match(/\d+/);
  if (!numericMatch) {
    return 'text-gray-500';
  }
  
  const numValue = parseInt(numericMatch[0], 10);
  
  if (numValue >= 100) {
    return 'text-emerald-600 font-bold'; // Green for 100%
  } else {
    return 'text-red-600 font-bold'; // Red for < 100%
  }
}

/**
 * Module 2 field definitions for completeness calculation
 */
const MODULE_2_FIELDS = [
  'milestones.projectStart',
  'milestones.t1',
  'milestones.glTime',
  'milestones.vmp',
  'milestones.mp',
  'milestones.currentStage',
  'milestones.trialCount',
  'milestones.toolingFAI',
  'milestones.partFAI',
  'milestones.t1SizeQualified',
] as const;

/**
 * Check if a field value is considered empty
 */
function isFieldEmpty(value: any): boolean {
  if (value === null || value === undefined) return true;
  if (typeof value === 'string') {
    const trimmed = value.trim();
    return trimmed === '' || trimmed === '-' || trimmed === 'N/A';
  }
  return false;
}

/**
 * Calculate Module 2 completeness percentage
 */
export function calculateCompleteness(project: ProjectData): number {
  const totalFields = MODULE_2_FIELDS.length;
  let filledFields = 0;

  for (const fieldPath of MODULE_2_FIELDS) {
    const value = getNestedValue(project, fieldPath);
    if (!isFieldEmpty(value)) {
      filledFields++;
    }
  }

  return Math.round((filledFields / totalFields) * 100);
}

/**
 * Get list of missing fields in Module 2
 */
export function getMissingFields(project: ProjectData): string[] {
  const fieldLabels: Record<string, string> = {
    'milestones.projectStart': '项目启动时间',
    'milestones.t1': 'T1时间',
    'milestones.glTime': 'G/L时间',
    'milestones.vmp': 'VMP时间',
    'milestones.mp': 'MP时间',
    'milestones.currentStage': '当前阶段',
    'milestones.trialCount': '试模次数',
    'milestones.toolingFAI': 'Tooling FAI',
    'milestones.partFAI': 'Part FAI',
    'milestones.t1SizeQualified': 'T1尺寸是否达标',
  };

  const missingFields: string[] = [];

  for (const fieldPath of MODULE_2_FIELDS) {
    const value = getNestedValue(project, fieldPath);
    if (isFieldEmpty(value)) {
      missingFields.push(fieldLabels[fieldPath] || fieldPath);
    }
  }

  return missingFields;
}