// services/deductionEngine.js - CORRECTED VERSION
const { pool } = require("../../config/db");

class DeductionEngine {
  constructor(orgId, month, year) {
    this.orgId = orgId;
    this.month = month;
    this.year = year;
    this.rulesCache = null;
  }

  /**
   * Get all applicable deduction rules for an employee
   * Handles missing columns gracefully
   */
  async getApplicableRules(employeeId, departmentId) {
    if (!this.rulesCache) {
      // Check what columns exist in deduction_rules
      const columnsResult = await pool.query(`
        SELECT column_name 
        FROM information_schema.columns 
        WHERE table_name = 'deduction_rules' 
          AND column_name IN ('department_id', 'applies_to', 'priority')
      `);
      const existingColumns = columnsResult.rows.map(r => r.column_name);
      
      // Build query based on existing columns
      let selectFields = '*';
      let orderBy = 'rule_name';
      
      if (existingColumns.includes('priority')) {
        orderBy = 'priority ASC, rule_name';
      }
      
      const rulesResult = await pool.query(
        `SELECT ${selectFields} FROM deduction_rules 
         WHERE (org_id = $1 OR org_id IS NULL) 
           AND is_active = true 
           AND is_deleted = false
         ORDER BY ${orderBy}`,
        [this.orgId]
      );
      this.rulesCache = rulesResult.rows;
    }

    // Filter rules based on available columns
    return this.rulesCache.filter(rule => {
      // If no department filtering columns exist, return all rules
      if (rule.department_id === undefined || rule.department_id === null) {
        return true;
      }
      
      // Apply department filtering if columns exist
      if (rule.applies_to === 'all' || !rule.applies_to) return true;
      if (rule.applies_to === 'department' && rule.department_id === departmentId) return true;
      if (rule.applies_to === 'specific' && rule.employee_id === employeeId) return true;
      
      return false;
    });
  }

  /**
   * Calculate attendance summary from buffer (Single Source of Truth)
   */
  async getAttendanceSummary(employeeId) {
    const result = await pool.query(
      `SELECT 
         COUNT(*) AS total_days,
         SUM(CASE WHEN is_present THEN 1 ELSE 0 END) AS present_days,
         SUM(CASE WHEN is_absent THEN 1 ELSE 0 END) AS absent_days,
         SUM(CASE WHEN is_late THEN 1 ELSE 0 END) AS late_days,
         SUM(CASE WHEN is_holiday THEN 1 ELSE 0 END) AS holiday_days,
         SUM(CASE WHEN is_leave THEN 1 ELSE 0 END) AS leave_days,
         SUM(CASE WHEN attendance_status = 'Half Day' THEN 1 ELSE 0 END) AS half_days,
         SUM(CASE WHEN attendance_status = 'Overtime' THEN 1 ELSE 0 END) AS overtime_days,
         COALESCE(SUM(total_hours), 0) AS total_hours,
         COALESCE(SUM(overtime_hours), 0) AS total_overtime_hours,
         COALESCE(SUM(late_minutes), 0) AS total_late_minutes
       FROM attendance_buffer
       WHERE employee_id = $1 
         AND month_year = $2
         AND is_deleted = false`,
      [employeeId, `${this.year}-${String(this.month).padStart(2, '0')}`]
    );
    
    // Return default values if no rows
    if (!result.rows[0]) {
      return {
        total_days: 0,
        present_days: 0,
        absent_days: 0,
        late_days: 0,
        holiday_days: 0,
        leave_days: 0,
        half_days: 0,
        overtime_days: 0,
        total_hours: 0,
        total_overtime_hours: 0,
        total_late_minutes: 0
      };
    }
    
    return result.rows[0];
  }

  /**
   * Calculate salary components (single source)
   */
  calculateSalaryComponents(monthlySalary) {
    return {
      basic: parseFloat((monthlySalary * 0.4).toFixed(2)),
      hra: parseFloat((monthlySalary * 0.2).toFixed(2)),
      conveyance: parseFloat((monthlySalary * 0.05).toFixed(2)),
      special_allowance: parseFloat((monthlySalary * 0.35).toFixed(2)),
      monthly_gross: parseFloat(monthlySalary.toFixed(2))
    };
  }

  /**
   * Apply a single deduction rule
   */
  applyRule(rule, baseValue, context) {
    const { monthlySalary, perDaySalary, perHourSalary, perMinuteSalary, attendance } = context;
    
    // Safely get rule values with defaults
    const ruleName = rule.rule_name || '';
    const calcType = rule.calculation_type || 'percentage';
    const percentage = parseFloat(rule.percentage) || 0;
    const fixedAmount = parseFloat(rule.fixed_amount) || 0;
    const thresholdMinutes = parseInt(rule.threshold_minutes) || 0;
    const maxPerDay = parseFloat(rule.max_per_day) || 50;
    const maxAmount = parseFloat(rule.max_amount) || 0;
    
    switch (ruleName) {
      case 'Professional Tax':
        if (monthlySalary > (thresholdMinutes || 20000)) {
          return calcType === 'fixed' 
            ? fixedAmount
            : monthlySalary * (percentage / 100);
        }
        return 0;

      case 'Provident Fund':
        const pfAmount = monthlySalary * (percentage / 100);
        return maxAmount > 0 ? Math.min(pfAmount, maxAmount) : pfAmount;

      case 'TDS':
        const annualIncome = monthlySalary * 12;
        if (annualIncome <= 250000) return 0;
        if (annualIncome <= 500000) return ((annualIncome - 250000) * 0.05) / 12;
        if (annualIncome <= 1000000) return (12500 + (annualIncome - 500000) * 0.2) / 12;
        return (112500 + (annualIncome - 1000000) * 0.3) / 12;

      case 'Late Deduction':
        const lateMinutes = attendance.total_late_minutes || 0;
        if (lateMinutes > thresholdMinutes) {
          const deductable = lateMinutes - thresholdMinutes;
          const amount = deductable * perMinuteSalary * (percentage / 100);
          const maxAmountLimit = perDaySalary * (maxPerDay / 100);
          return Math.min(amount, maxAmountLimit);
        }
        return 0;

      case 'Absent Deduction':
        return (attendance.absent_days || 0) * perDaySalary * (percentage / 100);

      case 'Half Day Deduction':
        return (attendance.half_days || 0) * perDaySalary * (percentage / 100);

      case 'Loss of Pay':
        const workingDays = (attendance.total_days || 0) - (attendance.holiday_days || 0);
        const paidDays = (attendance.present_days || 0) + 
                        ((attendance.half_days || 0) * 0.5) + 
                        (attendance.leave_days || 0);
        const lopDays = Math.max(0, workingDays - paidDays);
        return lopDays * perDaySalary * (percentage / 100);

      default:
        return 0;
    }
  }

  /**
   * Calculate complete payroll for an employee (SINGLE SOURCE OF TRUTH)
   */
  async calculateEmployeePayroll(employeeId, departmentId, monthlySalary) {
    // 1. Get attendance from buffer
    const attendance = await this.getAttendanceSummary(employeeId);
    
    // 2. Calculate per-day/hour/minute rates
    const workingDays = Math.max(1, (attendance.total_days || 0) - (attendance.holiday_days || 0));
    const perDaySalary = monthlySalary / workingDays;
    const perHourSalary = perDaySalary / 8;
    const perMinuteSalary = perHourSalary / 60;
    
    // 3. Calculate prorated gross
    const paidDays = (attendance.present_days || 0) + 
                     ((attendance.half_days || 0) * 0.5) + 
                     (attendance.leave_days || 0);
    const proratedGross = (monthlySalary / workingDays) * paidDays;
    
    // 4. Calculate overtime payment
    const otHours = Math.min(attendance.total_overtime_hours || 0, 4);
    const otPayment = otHours > 0 ? otHours * perHourSalary * 1.5 : 0;
    const totalEarnings = proratedGross + otPayment;
    
    // 5. Get applicable rules
    const rules = await this.getApplicableRules(employeeId, departmentId);
    
    // 6. Apply all rules
    const context = {
      monthlySalary,
      perDaySalary,
      perHourSalary,
      perMinuteSalary,
      attendance,
      proratedGross,
      otPayment
    };
    
    const deductions = {};
    for (const rule of rules) {
      deductions[rule.rule_name] = this.applyRule(rule, null, context);
    }
    
    // 7. Get manual deductions
    const manualResult = await pool.query(
      `SELECT COALESCE(SUM(amount), 0) AS total 
       FROM deductions 
       WHERE employee_id = $1 AND month = $2 AND year = $3 AND is_manual = true AND is_deleted = false`,
      [employeeId, this.month, this.year]
    );
    const manualTotal = parseFloat(manualResult.rows[0]?.total || 0);
    
    // 8. Calculate totals
    const standardTotal = (deductions['Professional Tax'] || 0) + 
                          (deductions['Provident Fund'] || 0) + 
                          (deductions['TDS'] || 0);
    
    const attendanceTotal = (deductions['Late Deduction'] || 0) +
                            (deductions['Absent Deduction'] || 0) +
                            (deductions['Half Day Deduction'] || 0) +
                            (deductions['Loss of Pay'] || 0);
    
    const totalDeductions = standardTotal + attendanceTotal + manualTotal;
    const netSalary = Math.max(0, totalEarnings - totalDeductions);
    
    // 9. Return complete payroll object
    return {
      attendance: {
        total_days: attendance.total_days || 0,
        present_days: attendance.present_days || 0,
        absent_days: attendance.absent_days || 0,
        late_days: attendance.late_days || 0,
        half_days: attendance.half_days || 0,
        holiday_days: attendance.holiday_days || 0,
        leave_days: attendance.leave_days || 0,
        total_overtime_hours: attendance.total_overtime_hours || 0,
        total_late_minutes: attendance.total_late_minutes || 0
      },
      salary_components: this.calculateSalaryComponents(monthlySalary),
      prorated_gross: parseFloat(proratedGross.toFixed(2)),
      ot_payment: parseFloat(otPayment.toFixed(2)),
      total_earnings: parseFloat(totalEarnings.toFixed(2)),
      deductions: {
        standard: {
          professional_tax: parseFloat((deductions['Professional Tax'] || 0).toFixed(2)),
          pf_employee: parseFloat((deductions['Provident Fund'] || 0).toFixed(2)),
          tds: parseFloat((deductions['TDS'] || 0).toFixed(2)),
          total: parseFloat(standardTotal.toFixed(2))
        },
        attendance: {
          late_deduction: parseFloat((deductions['Late Deduction'] || 0).toFixed(2)),
          absent_deduction: parseFloat((deductions['Absent Deduction'] || 0).toFixed(2)),
          half_day_deduction: parseFloat((deductions['Half Day Deduction'] || 0).toFixed(2)),
          loss_of_pay_deduction: parseFloat((deductions['Loss of Pay'] || 0).toFixed(2)),
          total: parseFloat(attendanceTotal.toFixed(2))
        },
        manual_total: parseFloat(manualTotal.toFixed(2)),
        total: parseFloat(totalDeductions.toFixed(2))
      },
      net_salary: parseFloat(netSalary.toFixed(2)),
      working_days: workingDays,
      paid_days: parseFloat(paidDays.toFixed(2)),
      loss_of_pay_days: Math.max(0, workingDays - paidDays)
    };
  }
}

module.exports = DeductionEngine;