const { pool } = require("../../config/db");

/* ===========================
   ✅ CREATE PAYROLL
=========================== */

const createPayroll = async (req, res) => {
  try {
    console.log("=== CREATE PAYROLL DEBUG ===");
    console.log("req.body:", req.body);

    let {
      employee,
      payGrade,
      effectiveDate,
      basicSalary,
      hra,
      specialAllowance,
      conveyanceAllowance,
      medicalAllowance,
      otherAllowance,
      month,
      year,
      orgId
    } = req.body;

    // Validate required fields
    if (!employee || !payGrade || !effectiveDate) {
      return res.status(400).json({
        success: false,
        message: "Employee, Pay Grade, and Effective Date are required",
      });
    }

    // Check if employee exists
    const employeeResult = await pool.query(
      `SELECT employee_id, employee_name, designation 
       FROM employees 
       WHERE employee_name = $1 OR employee_id = $1
       AND is_deleted = false`,
      [employee]
    );

    let employeeId = null;
    let employeeName = employee;
    let designation = null;

    if (employeeResult.rows.length > 0) {
      employeeId = employeeResult.rows[0].employee_id;
      employeeName = employeeResult.rows[0].employee_name;
      designation = employeeResult.rows[0].designation;
    }

    // Set month and year from effective date if not provided
    if (!month && effectiveDate) {
      const date = new Date(effectiveDate);
      month = date.toLocaleString('default', { month: 'long' });
      year = date.getFullYear();
    }

    const result = await pool.query(
      `
      INSERT INTO employee_payroll (
        employee_id,
        employee_name,
        designation,
        pay_grade,
        effective_date,
        basic_salary,
        hra,
        special_allowance,
        conveyance_allowance,
        medical_allowance,
        other_allowance,
        month,
        year,
        status,
        generated_date,
        created_by,
        orgId
      )
      VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, NOW(), $15, $16)
      RETURNING *
      `,
      [
        employeeId,
        employeeName,
        designation,
        payGrade,
        effectiveDate,
        parseFloat(basicSalary) || 0,
        parseFloat(hra) || 0,
        parseFloat(specialAllowance) || 0,
        parseFloat(conveyanceAllowance) || 0,
        parseFloat(medicalAllowance) || 0,
        parseFloat(otherAllowance) || 0,
        month,
        year,
        'Generated',
        req.user?.id || req.user?.username || 'system',
        orgId || req.user?.orgId || null
      ]
    );

    res.status(201).json({
      success: true,
      message: "Payroll created successfully",
      data: result.rows[0],
    });
  } catch (error) {
    console.error("CREATE PAYROLL ERROR:", error);
    res.status(500).json({
      success: false,
      message: error.message,
    });
  }
};

/* ===========================
   ✅ GET ALL PAYROLLS
=========================== */

const getPayrolls = async (req, res) => {
  try {
    const { page = 1, limit = 10, month, year, status } = req.query;
    const offset = (page - 1) * limit;
    
    let query = `
      SELECT 
        p.*,
        p.gross_salary as calculated_gross
      FROM employee_payroll p
      WHERE p.is_deleted = false
    `;
    
    const queryParams = [];
    let paramIndex = 1;
    
    if (month) {
      queryParams.push(month);
      query += ` AND p.month = $${paramIndex++}`;
    }
    
    if (year) {
      queryParams.push(year);
      query += ` AND p.year = $${paramIndex++}`;
    }
    
    if (status) {
      queryParams.push(status);
      query += ` AND p.status = $${paramIndex++}`;
    }
    
    query += ` ORDER BY p.created_at DESC`;
    
    // Add pagination
    queryParams.push(limit);
    query += ` LIMIT $${paramIndex++}`;
    queryParams.push(offset);
    query += ` OFFSET $${paramIndex++}`;
    
    const result = await pool.query(query, queryParams);
    
    // Get total count
    let countQuery = `SELECT COUNT(*) FROM employee_payroll WHERE is_deleted = false`;
    const countParams = [];
    let countIndex = 1;
    
    if (month) {
      countParams.push(month);
      countQuery += ` AND month = $${countIndex++}`;
    }
    
    if (year) {
      countParams.push(year);
      countQuery += ` AND year = $${countIndex++}`;
    }
    
    if (status) {
      countParams.push(status);
      countQuery += ` AND status = $${countIndex++}`;
    }
    
    const countResult = await pool.query(countQuery, countParams);
    const totalRecords = parseInt(countResult.rows[0].count);
    
    res.json({
      success: true,
      data: result.rows,
      totalRecords: totalRecords,
      currentPage: parseInt(page),
      totalPages: Math.ceil(totalRecords / limit),
    });
  } catch (err) {
    console.error("FETCH PAYROLLS ERROR:", err);
    res.status(500).json({
      success: false,
      message: err.message,
    });
  }
};

/* ===========================
   ✅ GET PAYROLL SUMMARY BY MONTH
=========================== */

const getPayrollSummary = async (req, res) => {
  try {
    const { year } = req.query;
    const currentYear = year || new Date().getFullYear();
    
    const result = await pool.query(
      `
      SELECT 
        month,
        COUNT(*) as employees,
        COALESCE(SUM(gross_salary), 0) as total_paid,
        'Generated' as status
      FROM employee_payroll
      WHERE year = $1 AND is_deleted = false
      GROUP BY month
      ORDER BY 
        CASE month
          WHEN 'January' THEN 1
          WHEN 'February' THEN 2
          WHEN 'March' THEN 3
          WHEN 'April' THEN 4
          WHEN 'May' THEN 5
          WHEN 'June' THEN 6
          WHEN 'July' THEN 7
          WHEN 'August' THEN 8
          WHEN 'September' THEN 9
          WHEN 'October' THEN 10
          WHEN 'November' THEN 11
          WHEN 'December' THEN 12
        END
      `,
      [currentYear]
    );
    
    res.json({
      success: true,
      data: result.rows,
      year: currentYear,
    });
  } catch (err) {
    console.error("GET PAYROLL SUMMARY ERROR:", err);
    res.status(500).json({
      success: false,
      message: err.message,
    });
  }
};

/* ===========================
   ✅ GET PAYROLLS BY MONTH (Simple - no group)
=========================== */

const getPayrollsByMonth = async (req, res) => {
  try {
    const { month, year } = req.query;
    
    let query = `
      SELECT * FROM employee_payroll
      WHERE is_deleted = false
    `;
    
    const queryParams = [];
    let paramIndex = 1;
    
    if (month) {
      queryParams.push(month);
      query += ` AND month = $${paramIndex++}`;
    }
    
    if (year) {
      queryParams.push(year);
      query += ` AND year = $${paramIndex++}`;
    }
    
    query += ` ORDER BY employee_name`;
    
    const result = await pool.query(query, queryParams);
    
    res.json({
      success: true,
      data: result.rows,
      totalRecords: result.rows.length,
    });
  } catch (err) {
    console.error("GET PAYROLLS BY MONTH ERROR:", err);
    res.status(500).json({
      success: false,
      message: err.message,
    });
  }
};

/* ===========================
   ✅ GET PAYROLL WITH BENEFITS (JOIN WITH employee_benefits)
=========================== */

const getPayrollWithBenefits = async (req, res) => {
  try {
    const { id } = req.params;
    
    const result = await pool.query(
      `
      SELECT 
        p.*,
        eb.housing_allowance_hra as benefit_hra,
        eb.transport_allowance,
        eb.meal_allowance,
        eb.travel_allowance,
        eb.mobile_internet_reimbursement,
        eb.special_allowance as benefit_special_allowance,
        eb.health_insurance_applicable,
        eb.provident_fund_applicable,
        eb.pension_scheme_applicable,
        eb.employee_contribution_percent,
        eb.employer_contribution_percent,
        eb.annual_leave_balance,
        eb.sick_leave_balance,
        eb.casual_leave_balance,
        eb.bonus_eligibility,
        eb.bonus_percentage_amount
      FROM employee_payroll p
      LEFT JOIN employee_benefits eb 
        ON p.employee_id = eb.employee_id 
        AND eb.is_deleted = false
      WHERE p.id = $1 AND p.is_deleted = false
      `,
      [id]
    );
    
    if (!result.rows.length) {
      return res.status(404).json({
        success: false,
        message: "Payroll record not found",
      });
    }
    
    res.status(200).json({
      success: true,
      data: result.rows[0],
    });
  } catch (err) {
    console.error("GET PAYROLL WITH BENEFITS ERROR:", err);
    res.status(500).json({
      success: false,
      message: err.message,
    });
  }
};

/* ===========================
   ✅ GET PAYROLLS BY MONTH WITH BENEFITS
=========================== */

const getPayrollsByMonthWithBenefits = async (req, res) => {
  try {
    const { month, year } = req.query;
    
    let query = `
      SELECT 
        p.*,
        p.gross_salary as total_pay,
        eb.housing_allowance_hra,
        eb.transport_allowance,
        eb.meal_allowance,
        eb.travel_allowance,
        eb.mobile_internet_reimbursement,
        eb.special_allowance as benefit_special_allowance,
        eb.bonus_eligibility,
        eb.bonus_percentage_amount,
        eb.health_insurance_applicable,
        eb.provident_fund_applicable
      FROM employee_payroll p
      LEFT JOIN employee_benefits eb 
        ON p.employee_id = eb.employee_id 
        AND eb.is_deleted = false
      WHERE p.is_deleted = false
    `;
    
    const queryParams = [];
    let paramIndex = 1;
    
    if (month) {
      queryParams.push(month);
      query += ` AND p.month = $${paramIndex++}`;
    }
    
    if (year) {
      queryParams.push(year);
      query += ` AND p.year = $${paramIndex++}`;
    }
    
    query += ` ORDER BY p.employee_name`;
    
    const result = await pool.query(query, queryParams);
    
    res.json({
      success: true,
      data: result.rows,
      totalRecords: result.rows.length,
    });
  } catch (err) {
    console.error("GET PAYROLLS BY MONTH WITH BENEFITS ERROR:", err);
    res.status(500).json({
      success: false,
      message: err.message,
    });
  }
};

/* ===========================
   ✅ GET PAYROLL BY ID
=========================== */

const getPayrollById = async (req, res) => {
  try {
    const { id } = req.params;
    
    const result = await pool.query(
      `
      SELECT * FROM employee_payroll
      WHERE id = $1 AND is_deleted = false
      `,
      [id]
    );
    
    if (!result.rows.length) {
      return res.status(404).json({
        success: false,
        message: "Payroll record not found",
      });
    }
    
    res.status(200).json({
      success: true,
      data: result.rows[0],
    });
  } catch (err) {
    console.error("GET PAYROLL BY ID ERROR:", err);
    res.status(500).json({
      success: false,
      message: "Fetch failed",
    });
  }
};

/* ===========================
   ✅ UPDATE PAYROLL
=========================== */

const updatePayroll = async (req, res) => {
  try {
    const { id } = req.params;
    const {
      employeeName,
      title,
      salary,
      overtimeHours,
      overtimePay,
      totalSalary,
      payDate,
      paymentType,
      status,
      basicSalary,
      hra,
      specialAllowance,
      conveyanceAllowance,
      medicalAllowance,
      otherAllowance
    } = req.body;
    
    // Check if payroll exists
    const existing = await pool.query(
      `SELECT * FROM employee_payroll WHERE id = $1 AND is_deleted = false`,
      [id]
    );
    
    if (!existing.rows.length) {
      return res.status(404).json({
        success: false,
        message: "Payroll record not found",
      });
    }
    
    const old = existing.rows[0];
    
    const result = await pool.query(
      `
      UPDATE employee_payroll
      SET
        employee_name = COALESCE($1, employee_name),
        title = COALESCE($2, title),
        salary = COALESCE($3, salary),
        overtime_hours = COALESCE($4, overtime_hours),
        overtime_pay = COALESCE($5, overtime_pay),
        total_salary = COALESCE($6, total_salary),
        pay_date = COALESCE($7, pay_date),
        payment_type = COALESCE($8, payment_type),
        status = COALESCE($9, status),
        basic_salary = COALESCE($10, basic_salary),
        hra = COALESCE($11, hra),
        special_allowance = COALESCE($12, special_allowance),
        conveyance_allowance = COALESCE($13, conveyance_allowance),
        medical_allowance = COALESCE($14, medical_allowance),
        other_allowance = COALESCE($15, other_allowance),
        updated_at = NOW(),
        updated_by = $16
      WHERE id = $17
      RETURNING *
      `,
      [
        employeeName || old.employee_name,
        title || old.title,
        salary || old.salary,
        overtimeHours !== undefined ? overtimeHours : old.overtime_hours,
        overtimePay !== undefined ? overtimePay : old.overtime_pay,
        totalSalary || old.total_salary,
        payDate || old.pay_date,
        paymentType || old.payment_type,
        status || old.status,
        basicSalary || old.basic_salary,
        hra || old.hra,
        specialAllowance || old.special_allowance,
        conveyanceAllowance || old.conveyance_allowance,
        medicalAllowance || old.medical_allowance,
        otherAllowance || old.other_allowance,
        req.user?.username || 'system',
        id
      ]
    );
    
    res.status(200).json({
      success: true,
      message: "Payroll updated successfully",
      data: result.rows[0],
    });
  } catch (error) {
    console.error("UPDATE PAYROLL ERROR:", error);
    res.status(500).json({
      success: false,
      message: error.message,
    });
  }
};

/* ===========================
   ✅ UPDATE PAYROLL STATUS
=========================== */

const updatePayrollStatus = async (req, res) => {
  try {
    const { id } = req.params;
    const { status, paidDate } = req.body;
    
    const result = await pool.query(
      `
      UPDATE employee_payroll
      SET 
        status = $1,
        paid_date = COALESCE($2, paid_date, CASE WHEN $1 = 'Paid' THEN CURRENT_DATE ELSE paid_date END),
        updated_at = NOW()
      WHERE id = $3 AND is_deleted = false
      RETURNING *
      `,
      [status, paidDate, id]
    );
    
    if (!result.rows.length) {
      return res.status(404).json({
        success: false,
        message: "Payroll record not found",
      });
    }
    
    res.json({
      success: true,
      message: `Payroll status updated to ${status}`,
      data: result.rows[0],
    });
  } catch (err) {
    console.error("UPDATE PAYROLL STATUS ERROR:", err);
    res.status(500).json({
      success: false,
      message: err.message,
    });
  }
};

/* ===========================
   ✅ DELETE PAYROLL (Soft Delete)
=========================== */

const deletePayroll = async (req, res) => {
  try {
    const { id } = req.params;
    
    const result = await pool.query(
      `
      UPDATE employee_payroll
      SET is_deleted = true,
          updated_at = NOW(),
          updated_by = $1
      WHERE id = $2 AND is_deleted = false
      RETURNING id
      `,
      [req.user?.username || 'system', id]
    );
    
    if (!result.rows.length) {
      return res.status(404).json({
        success: false,
        message: "Payroll record not found",
      });
    }
    
    res.json({
      success: true,
      message: "Payroll deleted successfully",
    });
  } catch (err) {
    console.error("DELETE PAYROLL ERROR:", err);
    res.status(500).json({
      success: false,
      message: "Delete failed",
    });
  }
};

/* ===========================
   ✅ GET MONTHLY SUMMARY FOR DASHBOARD
=========================== */

const getMonthlySummary = async (req, res) => {
  try {
    const { year } = req.query;
    const currentYear = year || new Date().getFullYear();
    
    const result = await pool.query(
      `
      SELECT 
        month,
        year,
        COUNT(*) as total_records,
        COUNT(DISTINCT employee_id) as total_employees,
        COALESCE(SUM(gross_salary), 0) as total_payroll_amount,
        COUNT(CASE WHEN status = 'Paid' THEN 1 END) as paid_count,
        COUNT(CASE WHEN status = 'Pending' THEN 1 END) as pending_count,
        COUNT(CASE WHEN status = 'Generated' THEN 1 END) as generated_count
      FROM employee_payroll
      WHERE year = $1 AND is_deleted = false
      GROUP BY month, year
      ORDER BY 
        CASE month
          WHEN 'January' THEN 1
          WHEN 'February' THEN 2
          WHEN 'March' THEN 3
          WHEN 'April' THEN 4
          WHEN 'May' THEN 5
          WHEN 'June' THEN 6
          WHEN 'July' THEN 7
          WHEN 'August' THEN 8
          WHEN 'September' THEN 9
          WHEN 'October' THEN 10
          WHEN 'November' THEN 11
          WHEN 'December' THEN 12
        END
      `,
      [currentYear]
    );
    
    res.json({
      success: true,
      data: result.rows,
      year: currentYear,
    });
  } catch (err) {
    console.error("GET MONTHLY SUMMARY ERROR:", err);
    res.status(500).json({
      success: false,
      message: err.message,
    });
  }
};

module.exports = {
  createPayroll,
  getPayrolls,
  getPayrollSummary,
  getPayrollById,
  getPayrollsByMonth,  // Added this (replaces getPayrollsByGroupAndMonth)
  updatePayroll,
  updatePayrollStatus,
  deletePayroll,
  getMonthlySummary,
  getPayrollWithBenefits,
  getPayrollsByMonthWithBenefits,
};