
const puppeteer = require("puppeteer");
const { pool } = require("../../config/db");
const fs = require('fs');
const path = require('path');

/* ===========================
   HELPER - Get logo as Base64
=========================== */
const getLogoBase64 = () => {
  try {
    const possiblePaths = [
      path.join(__dirname, '../../../public/logo.png'),
      path.join(__dirname, '../../public/logo.png'),
      path.join(__dirname, '../public/logo.png'),
      path.join(process.cwd(), 'public/logo.png'),
      path.join(process.cwd(), 'logo.png'),
      path.join(__dirname, '../../../public/logo.jpg'),
      path.join(__dirname, '../../public/logo.jpg'),
      path.join(__dirname, '../public/logo.jpg'),
      path.join(process.cwd(), 'public/logo.jpg'),
      path.join(process.cwd(), 'logo.jpg')
    ];
    
    let logoPath = null;
    for (const p of possiblePaths) {
      if (fs.existsSync(p)) {
        logoPath = p;
        break;
      }
    }
    
    if (logoPath && fs.existsSync(logoPath)) {
      const logoBuffer = fs.readFileSync(logoPath);
      const base64Logo = logoBuffer.toString('base64');
      const ext = path.extname(logoPath).toLowerCase();
      const mimeType = ext === '.jpg' || ext === '.jpeg' ? 'image/jpeg' : 'image/png';
      return `data:${mimeType};base64,${base64Logo}`;
    }
  } catch (error) {
    console.error("Logo not found:", error);
  }
  return null;
};

/* ===========================
   ✅ VIEW PAYSLIP DATA (JSON)
=========================== */
exports.viewPayslip = async (req, res) => {
  try {
    const { id } = req.params;
    const orgId = req.session.user?.orgId;

    const result = await pool.query(
      `SELECT 
        pr.*,
        e.id AS employee_db_id,
        e.first_name, 
        e.last_name, 
        CONCAT(e.first_name, ' ', e.last_name) AS employee_name,
        e.employee_code,
        e.joining_date,
        e.date_of_birth,
        e.gender,
        e.pan_number,
        e.aadhar_number,
        e.work_location,
        e.working_status,
        e.salary AS monthly_salary,
        pr.month AS month_number,
        TO_CHAR(MAKE_DATE(pr.year, 1, 1), 'YYYY') || '-' ||
          TO_CHAR(MAKE_DATE(pr.year + 1, 1, 1), 'YYYY') AS financial_year,
        (SELECT COUNT(*) FROM attendance 
         WHERE employee_id = e.id 
           AND EXTRACT(YEAR FROM attendance_date) = pr.year 
           AND EXTRACT(MONTH FROM attendance_date) = pr.month
           AND attendance_status != 'Holiday') AS calendar_days,
        COALESCE(d.dept_name, 'Unknown') AS department_name,
        COALESCE(des.designation_name, 'Unknown') AS designation_name,
        bd.bank_name,
        bd.account_number,
        bd.account_holder_name,
        bd.ifsc_code,
        bd.branch
      FROM payroll_records pr
      JOIN employees e ON e.id = pr.employee_id AND e.status = 'Active' AND e.is_deleted = false
      LEFT JOIN departments d ON d.id::text = e.department_id AND d.is_deleted = false
      LEFT JOIN designations des ON des.id::text = e.designation_id AND des.is_deleted = false
      LEFT JOIN employee_bank_details bd ON bd.employee_id = e.id AND bd.is_primary = true AND bd.is_deleted = false AND bd.is_active = true
      WHERE pr.id = $1 AND pr.org_id = $2 AND pr.is_deleted = false`,
      [id, orgId]
    );

    if (result.rows.length === 0) {
      return res.status(404).json({ success: false, message: "Payroll record not found" });
    }

    res.json({ success: true, data: result.rows[0] });
  } catch (error) {
    console.error("View payslip error:", error);
    res.status(500).json({ success: false, message: error.message });
  }
};

exports.downloadPayslip = async (req, res) => {
  try {
    const { id } = req.params;
    const orgId = req.session.user?.orgId;

    const APP_NAME = process.env.VITE_APP_NAME || "VIVEK BIO-PRODUCTS";
    const APP_VERSION = process.env.VITE_APP_VERSION || "v1.1";
    const logoBase64 = getLogoBase64();

    // ── 1. Fetch org details ────────────────────────────────────────────────
    const orgResult = await pool.query(
      `SELECT org_name, email, phone, address, gstin
       FROM organizations
       WHERE id = $1 AND is_deleted = false LIMIT 1`,
      [orgId]
    );
    const org = orgResult.rows[0] || {};
    const companyName = org.org_name || APP_NAME;
    const companyEmail = org.email || "";
    const companyPhone = org.phone || "";
    const companyAddr = org.address || "";
    const companyGstin = org.gstin || "";

    // ── 2. Fetch payroll record with ALL needed fields ──────────────────────
    const result = await pool.query(
      `SELECT
        pr.*,
        e.id AS employee_db_id,
        e.first_name,
        e.last_name,
        e.employee_code,
        e.joining_date,
        e.date_of_birth,
        e.gender,
        e.pan_number,
        e.aadhar_number,
        e.work_location,
        e.working_status,
        e.salary AS monthly_salary,
        e.department_id,
        e.designation_id,
        -- pr.month is already an integer (1-12)
        pr.month AS month_number,
        TO_CHAR(MAKE_DATE(pr.year, 1, 1), 'YYYY') || '-' ||
          TO_CHAR(MAKE_DATE(pr.year + 1, 1, 1), 'YYYY') AS financial_year,
        -- Get calendar days from attendance
        (SELECT COUNT(*)
         FROM attendance
         WHERE employee_id = e.id
           AND EXTRACT(YEAR FROM attendance_date) = pr.year
           AND EXTRACT(MONTH FROM attendance_date) = pr.month
           AND attendance_status != 'Holiday') AS calendar_days,
        -- ✅ FIXED: Remove e.department since it doesn't exist, use COALESCE with only department_name from join
        COALESCE(d.dept_name, 'Unknown') AS department_name,
        COALESCE(des.designation_name, 'Unknown') AS designation_name,
        -- Bank details
        bd.bank_name,
        bd.account_number,
        bd.account_holder_name,
        bd.ifsc_code,
        bd.branch
      FROM payroll_records pr
      JOIN employees e ON e.id = pr.employee_id AND e.status = 'Active' AND e.is_deleted = false
      LEFT JOIN departments d ON d.id::text = e.department_id AND d.is_deleted = false
      LEFT JOIN designations des ON des.id::text = e.designation_id AND des.is_deleted = false
      LEFT JOIN employee_bank_details bd ON bd.employee_id = e.id AND bd.is_primary = true AND bd.is_deleted = false AND bd.is_active = true
      WHERE pr.id = $1 AND pr.org_id = $2 AND pr.is_deleted = false`,
      [id, orgId]
    );

    if (result.rows.length === 0) {
      return res.status(404).json({ success: false, message: "Payroll record not found" });
    }

    const d = result.rows[0];

    // ── 3. Get month name from month number ──────────────────────────────────
    const MONTHS = [
      "January", "February", "March", "April", "May", "June",
      "July", "August", "September", "October", "November", "December"
    ];
    
    // pr.month is already an integer (1-12)
    const monthNumber = parseInt(d.month_number) || 1;
    const monthName = MONTHS[monthNumber - 1];
    
    // ── 4. Fetch ATTENDANCE data from attendance table ──────────────────────
    const attendanceResult = await pool.query(
      `SELECT 
        COUNT(*) as total_days,
        COUNT(CASE WHEN attendance_status = 'Present' THEN 1 END) as present_days,
        COUNT(CASE WHEN attendance_status = 'Absent' THEN 1 END) as absent_days,
        COUNT(CASE WHEN attendance_status = 'Late' THEN 1 END) as late_days,
        COUNT(CASE WHEN attendance_status = 'Half Day' THEN 1 END) as half_days,
        COUNT(CASE WHEN attendance_status = 'Holiday' THEN 1 END) as holidays
      FROM attendance
      WHERE employee_id = $1 
        AND EXTRACT(YEAR FROM attendance_date) = $2
        AND EXTRACT(MONTH FROM attendance_date) = $3
        AND is_deleted = false`,
      [d.employee_db_id, d.year, monthNumber]
    );

    const attendance = attendanceResult.rows[0] || {};
    
    // Calculate attendance metrics
    const totalDaysInMonth = new Date(d.year, monthNumber, 0).getDate();
    const calendarDays = parseInt(d.calendar_days) || parseInt(attendance.total_days) || totalDaysInMonth;
    const presentDays = parseInt(attendance.present_days) || 0;
    const absentDays = parseInt(attendance.absent_days) || 0;
    const lateDays = parseInt(attendance.late_days) || 0;
    const halfDays = parseInt(attendance.half_days) || 0;
    
    // Calculate paid days (Present + Half days count as 0.5)
    const calculatedPaidDays = presentDays + (halfDays * 0.5);
    const calculatedLopDays = (totalDaysInMonth - calculatedPaidDays);
    
    // Use manual values from payroll_records if they exist, otherwise use calculated ones
    const paidDays = parseFloat(d.paid_days) > 0 ? parseFloat(d.paid_days) : calculatedPaidDays;
    const lopDays = parseFloat(d.loss_of_pay_days) > 0 ? parseFloat(d.loss_of_pay_days) : calculatedLopDays;

    // ── 5. Fetch active deduction rules ──────────────────────────────────────
    const rulesResult = await pool.query(
      `SELECT * FROM deduction_rules
       WHERE (org_id = $1 OR org_id IS NULL) AND is_active = true AND is_deleted = false`,
      [orgId]
    );
    const rules = rulesResult.rows;
    const getRule = (name) => rules.find((r) => r.rule_name === name);

    // ── 6. Salary calculations ──────────────────────────────────────────────
    const proratedGross = parseFloat(d.gross_salary) || 0;
    let monthlySalary = parseFloat(d.monthly_salary) || proratedGross;

    // Calculate prorated salary based on paid days if needed
    let finalMonthlySalary = monthlySalary;
    if (paidDays > 0 && paidDays < totalDaysInMonth && monthlySalary > 0) {
      finalMonthlySalary = (monthlySalary / totalDaysInMonth) * paidDays;
    }

    // If there's an absent deduction, use it to calculate the actual salary
    const absentDeductionAmount = parseFloat(d.absent_deduction) || 0;
    if (absentDeductionAmount > 0 && monthlySalary > 0) {
      finalMonthlySalary = monthlySalary - absentDeductionAmount;
    }

    const basic = parseFloat((finalMonthlySalary * 0.40).toFixed(2));
    const hra = parseFloat((finalMonthlySalary * 0.20).toFixed(2));
    const conveyance = parseFloat((finalMonthlySalary * 0.05).toFixed(2));
    const specialAllow = parseFloat((finalMonthlySalary * 0.35).toFixed(2));

    const shiftAllow = parseFloat(d.shift_allowance) || 0;
    const holidayWages = parseFloat(d.holiday_wages) || 0;
    const advBonus = parseFloat(d.advance_bonus) || 0;
    const overtimePay = parseFloat(d.overtime_pay) || parseFloat(d.overtime_payment) || 0;

    const totalEarnings = basic + hra + conveyance + specialAllow +
                          shiftAllow + holidayWages + advBonus + overtimePay;

    // ── 7. Deductions ───────────────────────────────────────────────────────
    let profTax = parseFloat(d.professional_tax) || 0;
    if (!profTax) {
      if (proratedGross >= 20000) profTax = 200;
      else if (proratedGross >= 15000) profTax = 150;
      else if (proratedGross >= 10000) profTax = 100;
    }

    let pfEmployee = parseFloat(d.pf_employee) || 0;
    if (!pfEmployee) {
      const pfRule = getRule("Provident Fund");
      const pct = pfRule ? (pfRule.percentage || 12) / 100 : 0.12;
      const cap = pfRule ? parseFloat(pfRule.fixed_amount || 1800) : 1800;
      pfEmployee = parseFloat(Math.min(finalMonthlySalary * pct, cap).toFixed(2));
    }

    let tds = parseFloat(d.tds) || 0;
    if (!tds) {
      const annual = finalMonthlySalary * 12;
      if (annual > 1000000) tds = (112500 + (annual - 1000000) * 0.30) / 12;
      else if (annual > 500000) tds = (12500 + (annual - 500000) * 0.20) / 12;
      else if (annual > 250000) tds = ((annual - 250000) * 0.05) / 12;
      tds = parseFloat(tds.toFixed(2));
    }

    const lateDeduction = parseFloat(d.late_deduction) || 0;
    const halfDayDeduction = parseFloat(d.half_day_deduction) || 0;
    const manualDeductions = parseFloat(d.manual_deductions_total) || 0;
    const salaryAdvance = parseFloat(d.salary_advance) || 0;

    const totalDeductions = profTax + pfEmployee + tds +
                            lateDeduction + absentDeductionAmount + halfDayDeduction +
                            manualDeductions + salaryAdvance;

    // Use net_salary from database if available, otherwise calculate
    let netSalary = parseFloat(d.net_salary);
    if (!netSalary || isNaN(netSalary)) {
      netSalary = parseFloat((totalEarnings - totalDeductions).toFixed(2));
    }

    // ── 8. Formatting helpers ────────────────────────────────────────────────
    const fmt = (num) =>
      !num || isNaN(num) ? "0" : new Intl.NumberFormat("en-IN", {
        minimumFractionDigits: 2,
        maximumFractionDigits: 2,
      }).format(num);

    const joiningDate = d.joining_date
      ? new Date(d.joining_date).toLocaleDateString("en-IN") : "N/A";
    const paidDateFmt = d.paid_date
      ? new Date(d.paid_date).toLocaleDateString("en-IN") : "Not Paid";
    const financialYr = d.financial_year || `${d.year}-${parseInt(d.year) + 1}`;

    // ── 9. Build earnings/deductions rows ────────────────────────────────────
    const earningsRows = [
      ["Basic", basic],
      ["House Rent Allowance", hra],
      ["Conveyance Allowance", conveyance],
      ["Special Allowance", specialAllow],
      ...(shiftAllow > 0 ? [["Shift Allowance-payout", shiftAllow]] : []),
      ...(holidayWages > 0 ? [["Holiday Wages", holidayWages]] : []),
      ...(advBonus > 0 ? [["Advance Statutory Bonus", advBonus]] : []),
      ...(overtimePay > 0 ? [["Overtime Payment", overtimePay]] : []),
    ];

    const deductionsRows = [
      ...(profTax > 0 ? [["Profession Tax", profTax]] : []),
      ...(pfEmployee > 0 ? [["Provident Fund-Employee Contribution", pfEmployee]] : []),
      ...(tds > 0 ? [["TDS", tds]] : []),
      ...(lateDeduction > 0 ? [["Late Deduction", lateDeduction]] : []),
      ...(absentDeductionAmount > 0 ? [["Absent Deduction", absentDeductionAmount]] : []),
      ...(halfDayDeduction > 0 ? [["Half Day Deduction", halfDayDeduction]] : []),
      ...(salaryAdvance > 0 ? [["Salary Advance-Recovery", salaryAdvance]] : []),
      ...(manualDeductions > 0 ? [["Other Deductions", manualDeductions]] : []),
    ];

    const maxRows = Math.max(earningsRows.length, deductionsRows.length);
    while (earningsRows.length < maxRows) earningsRows.push(["", null]);
    while (deductionsRows.length < maxRows) deductionsRows.push(["", null]);

    const tableRows = earningsRows.map(([eLabel, eVal], i) => {
      const [dLabel, dVal] = deductionsRows[i];
      return `
        <tr>
          <td style="width:33%">${eLabel}</td>
          <td style="width:17%" class="amt text-end">${eVal !== null && eVal !== "" ? fmt(eVal) : ""}</td>
          <td style="width:33%">${dLabel}</td>
          <td style="width:17%" class="amt text-end">${dVal !== null && dVal !== "" ? fmt(dVal) : ""}</td>
        </tr>`;
    }).join("");

    // ── 10. HTML Template ────────────────────────────────────────────────────
    const logoHtml = logoBase64 ? `<img src="${logoBase64}" alt="Company Logo" style="height: 60px; object-fit: contain;" />` : '<div style="height: 60px;"></div>';

    const html = `<!DOCTYPE html>
<html>
<head>
  <meta charset="UTF-8">
  <title>Payslip - ${d.employee_code} - ${monthName} ${d.year}</title>
  <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css"/>
  <style>
    * { box-sizing: border-box; }
    body {
      font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif;
      background: #f0f2f5;
      padding: 30px 20px;
      font-size: 13px;
    }
    .slip-card {
      max-width: 950px;
      margin: 0 auto;
      background: #fff;
      box-shadow: 0 4px 20px rgba(0,0,0,.1);
    }
    .slip-body { padding: 24px; }
    .slip-header {
      display: flex;
      justify-content: space-between;
      align-items: center;
      border-bottom: 1px solid #dee2e6;
      padding-bottom: 18px;
      margin-bottom: 18px;
    }
    .header-logo { flex: 0 0 auto; }
    .header-center {
      flex: 1;
      text-align: center;
    }
    .header-center h5 { 
      font-weight: 700; 
      margin: 0 0 2px; 
      font-size: 16px;
      color: #1a8f6f;
    }
    .header-center div { font-size: 12px; }
    .header-conf {
      flex: 0 0 auto;
      font-size: 11px;
      font-weight: 700;
      color: #6c757d;
    }
    table { width: 100%; border-collapse: collapse; margin-bottom: 12px; }
    th, td { border: 1px solid #dee2e6; padding: 6px 10px; vertical-align: middle; }
    thead th { background: #f8f9fa; font-weight: 600; text-align: center; }
    .info-table td:nth-child(odd) { color: #555; background: #fafafa; width: 19%; }
    .info-table td:nth-child(even) { width: 30%; }
    .info-table tbody tr:first-child td { font-size: 14px; font-weight: 600; }
    .sal-table { margin-bottom: 0; }
    .sal-table td.amt { text-align: right; }
    .sal-table .total-row td { font-weight: 700; background: #f8f9fa; }
    .sal-table .net-row td { 
      font-weight: 700; 
      font-size: 14px; 
      background: #e9ecef; 
      border-top: 2px solid #adb5bd; 
      padding: 10px 12px;
    }
    .text-end { text-align: right; }
    .pay-meta td:nth-child(odd) { font-weight: 600; background: #fafafa; width: 18%; }
    .slip-footer {
      text-align: center;
      font-size: 10px;
      color: #888;
      padding: 14px 24px;
      border-top: 1px solid #dee2e6;
    }
    .attendance-summary {
      margin-top: 10px;
      margin-bottom: 15px;
      padding: 8px;
      background: #f8f9fa;
      border-radius: 4px;
      font-size: 11px;
    }
  </style>
</head>
<body>
<div class="slip-card">
  <div class="slip-body">
    <div class="slip-header">
      <div class="header-logo">${logoHtml}</div>
      <div class="header-center">
        <h5>${APP_NAME}</h5>
        <div>Payslip for the month of ${monthName} ${d.year}</div>
        <div class="small">Financial Period ${financialYr}</div>
        <div class="small" style="color: #666;">${APP_NAME} | ${APP_VERSION}</div>
      </div>
      <div class="header-conf">Private &amp; Confidential</div>
    </div>

    <table class="info-table">
      <thead><tr><th colspan="4">Associate Information</th></tr></thead>
      <tbody>
        <tr><td colspan="4">${d.first_name || ""} ${d.last_name || ""}</td></tr>
        <tr><td>Associate Id</td><td>${d.employee_code || "N/A"}</td><td>Location</td><td>${d.work_location || "N/A"}</td></tr>
        <tr><td>Designation</td><td>${d.designation_name || "N/A"}</td><td>PAN</td><td>${d.pan_number || "N/A"}</td></tr>
        <tr><td>Gender</td><td>${d.gender || "N/A"}</td><td>Bank A/C</td><td>${d.account_number || "N/A"}</td></tr>
        <tr><td>Date Of Joining</td><td>${joiningDate}</td><td>Aadhar Number</td><td>${d.aadhar_number || "N/A"}</td></tr>
        <tr><td>Bank Name</td><td>${d.bank_name || "N/A"}</td><td>Status</td><td>${d.working_status || "Active"}</td></tr>
        <tr><td>IFSC Code</td><td>${d.ifsc_code || "N/A"}</td><td>Available Calendar Days</td><td>${calendarDays}</td></tr>
        <tr><td>Branch</td><td>${d.branch || "N/A"}</td><td>Paid Days</td><td>${paidDays.toFixed(1)}</td></tr>
        <tr><td>A/C Holder Name</td><td>${d.account_holder_name || "N/A"}</td><td>Loss of Pay Days</td><td>${lopDays.toFixed(1)}</td></tr>
        <tr><td>Department</td><td colspan="3">${d.department_name || "N/A"}</td></tr>
      </tbody>
    </table>

    <!-- Attendance Summary -->
    <div class="attendance-summary">
      <strong>Attendance Summary:</strong> Present: ${presentDays} | Absent: ${absentDays} | Late: ${lateDays} | Half Days: ${halfDays} | Holidays: ${attendance.holidays || 0}
    </div>

    <table class="sal-table">
      <thead>
        <tr>
          <th>Earnings</th>
          <th>Amount (₹)</th>
          <th>Deductions</th>
          <th>Amount (₹)</th>
        </tr>
      </thead>
      <tbody>
        ${tableRows}
        <tr class="total-row">
          <td colspan="1"><b>(A) Total Earnings</b></td>
          <td class="amt"><b>${fmt(totalEarnings)}</b></td>
          <td colspan="1"><b>(B) Total Deduction</b></td>
          <td class="amt"><b>${fmt(totalDeductions)}</b></td>
        </tr>
        <tr class="net-row">
          <td colspan="2"><b>Net Salary = (A) − (B)</b></td>
          <td colspan="2" class="amt"><b>₹ ${fmt(netSalary)}</b></td>
        </tr>
      </tbody>
    </table>

    <table class="pay-meta">
      <tbody>
        <tr>
          <td>Payment Status:</td>
          <td style="color:${(d.payment_status || d.status) === "Paid" ? "green" : "orange"};font-weight:600">
            ${d.payment_status || d.status || "Pending"}
          </td>
          <td>Payment Date:</td>
          <td>${paidDateFmt}</td>
        </tr>
        <tr>
          <td>Payment Mode:</td>
          <td>${d.payment_type || "Bank Transfer"}</td>
          <td>Payment Reference:</td>
          <td>${d.payment_reference || "-"}</td>
        </tr>
      </tbody>
    </table>
  </div>
  <div class="slip-footer">
    <p class="mb-1">This is a system generated payslip and does not require signature.</p>
    <p class="mb-1">Generated by ${APP_NAME} | ${APP_VERSION}</p>
    ${companyEmail ? `<p style="margin:2px 0">Email: ${companyEmail}${companyPhone ? " &nbsp;|&nbsp; Phone: " + companyPhone : ""}</p>` : ""}
    ${companyAddr ? `<p style="margin:2px 0">${companyAddr}</p>` : ""}
    ${companyGstin ? `<p style="margin:2px 0">GSTIN: ${companyGstin}</p>` : ""}
  </div>
</div>
</body>
</html>`;

    // ── 11. Generate PDF ─────────────────────────────────────────────────────
    const browser = await puppeteer.launch({
      executablePath: "C:\\Program Files\\Google\\Chrome\\Application\\chrome.exe",
      headless: "new",
      args: ["--no-sandbox", "--disable-setuid-sandbox", "--disable-dev-shm-usage"],
      timeout: 60000
    });

    const page = await browser.newPage();
    await page.setViewport({ width: 1024, height: 800 });
    await page.setContent(html, { waitUntil: "networkidle0", timeout: 30000 });

    const pdf = await page.pdf({
      format: "A4",
      printBackground: true,
      margin: { top: "20px", bottom: "20px", left: "20px", right: "20px" },
      timeout: 30000
    });

    await browser.close();

    res.set({
      "Content-Type": "application/pdf",
      "Content-Disposition": `attachment; filename="Payslip_${d.employee_code}_${monthName}_${d.year}.pdf"`,
      "Content-Length": pdf.length,
    });
    res.send(pdf);

  } catch (error) {
    console.error("Payslip generation error:", error);
    res.status(500).json({
      success: false,
      message: "Failed to generate payslip",
      error: error.message,
    });
  }
};