

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

// /* ─────────────────────────────────────────────
//    HELPER — format date
// ───────────────────────────────────────────── */
// const formatDate = (dateVal) => {
//   if (!dateVal) return new Date().toLocaleDateString("en-IN", { day: "numeric", month: "long", year: "numeric" });
//   const d = new Date(dateVal);
//   if (isNaN(d)) return new Date().toLocaleDateString("en-IN", { day: "numeric", month: "long", year: "numeric" });
//   return d.toLocaleDateString("en-IN", { day: "numeric", month: "long", year: "numeric" });
// };

// /* ─────────────────────────────────────────────
//    HELPER — format currency
// ───────────────────────────────────────────── */
// const formatCurrency = (amount) => {
//   if (!amount || isNaN(amount) || amount === 0) return null;
//   return new Intl.NumberFormat("en-IN", {
//     style: "currency",
//     currency: "INR",
//     minimumFractionDigits: 2,
//     maximumFractionDigits: 2,
//   }).format(amount);
// };

// /* ─────────────────────────────────────────────
//    HELPER — Get organization details
// ───────────────────────────────────────────── */
// const getOrganizationDetails = async (orgId) => {
//   try {
//     const orgResult = await pool.query(
//       `SELECT org_name, email, phone, address 
//        FROM organizations 
//        WHERE id = $1 AND is_deleted = false 
//        LIMIT 1`,
//       [orgId]
//     );
    
//     if (orgResult.rows.length > 0) {
//       return orgResult.rows[0];
//     }
    
//     return {
//       org_name: process.env.VITE_ORG_NAME || "Organization",
//       email: "",
//       phone: "",
//       address: ""
//     };
//   } catch (error) {
//     console.error("Error fetching organization details:", error);
//     return {
//       org_name: process.env.VITE_ORG_NAME || "Organization",
//       email: "",
//       phone: "",
//       address: ""
//     };
//   }
// };

// /* ─────────────────────────────────────────────
//    OFFER LETTER HTML (DOC001)
// ───────────────────────────────────────────── */
// const getOfferLetterHTML = (letter, org) => {
//    const orgName = org.org_name || process.env.VITE_ORG_NAME || "Organization";
//   const appName = process.env.VITE_APP_NAME || "";
//   const appVersion = process.env.VITE_APP_VERSION || "";
//   return `<!DOCTYPE html>
// <html>
// <head>
//   <meta charset="UTF-8">
//   <title>Offer Letter - ${letter.employee_name}</title>
//   <style>
//     * { margin: 0; padding: 0; box-sizing: border-box; }
//     body { font-family: Arial, sans-serif; background: white; padding: 0; margin: 0; font-size: 11px; line-height: 1.4; }
//     .page { width: 100%; padding: 0.4in 0.5in; }
//     .header { margin-bottom: 15px; border-bottom: 1px solid #e0e0e0; padding-bottom: 10px; text-align: center; }
//     .company-name { font-size: 18px; font-weight: bold; color: #1a8f6f; }
//     .company-tagline { font-size: 10px; color: #666; margin-top: 4px; }
//     .date { text-align: right; margin-bottom: 15px; font-weight: bold; font-size: 10px; }
//     .employee-details { margin-bottom: 15px; font-size: 10px; }
//     .title { text-align: center; font-size: 14px; font-weight: bold; text-decoration: underline; margin: 15px 0; }
//     .content { font-size: 10px; line-height: 1.4; }
//     .content p { margin-bottom: 6px; }
//     ul, ol { padding-left: 20px; margin: 6px 0; }
//     li { margin-bottom: 2px; font-size: 10px; }
//     .signature { margin-top: 15px; padding-top: 10px; border-top: 1px solid #e0e0e0; }
//     .footer { margin-top: 15px; text-align: center; font-size: 8px; color: #888; padding-top: 8px; border-top: 1px solid #e0e0e0; }
//   </style>
// </head>
// <body>
// <div class="page">
//   <div class="header">
//     <div class="company-name">${org.org_name || process.env.VITE_ORG_NAME || 'Company Name'}</div>
//     <div class="company-tagline">${process.env.VITE_APP_NAME || ''} | ${process.env.VITE_APP_VERSION || ''}</div>
//   </div>

//   <div class="date"><b>${formatDate(letter.issue_date)}</b></div>

//   <div class="employee-details">
//     <b>${letter.employee_name || "Candidate Name"}</b><br>
//     ${letter.position || "Position"}<br>
//     ${letter.location || "Location"}
//   </div>

//   <div class="title">OFFER LETTER</div>

//   <div class="content">
//     <p>Dear <b>${letter.employee_name?.split(' ')[0] || "Candidate"}</b>,</p>
//     <p>We are pleased to offer you the position of <b>${letter.position || "Position"}</b> at <b>${letter.company || org.org_name || "Company Name"}</b>.</p>
//     <p><b>1. Position and Reporting:</b> Your role will be <b>${letter.position || "Position"}</b>, reporting to the Shift Supervisor/Manager.</p>
//     <p><b>2. Commencement of Employment:</b> Your employment is scheduled to commence on <b>${formatDate(letter.start_date) || "To be determined"}</b>.</p>
//     <p><b>3. Probation Period:</b> You will be on a probation period of three (3) months from your date of joining.</p>
//     <p><b>4. Compensation:</b> You will be entitled to a gross annual compensation of <b>${formatCurrency(letter.annual_ctc) || "To be determined"}</b>.</p>
//     <p><b>5. Work Hours and Location:</b> Location: <b>${letter.location || "On-site"}</b></p>
//     <p><b>6. Termination:</b></p>
//     <ul><li>7 days notice during probation</li><li>30 days after confirmation</li></ul>
//     <p>We are excited to welcome you to <b>${letter.company || org.org_name || "Company Name"}</b>.</p>
//     <p class="mb-0">Warm regards,</p>
//     <p class="mb-0">${letter.hr_name || "HR Manager"}</p>
//     <p>${letter.company || org.org_name || "Company Name"}</p>
//     <hr />
//     <p><b>Accepted and Agreed by:</b></p>
//     <p>Signature: ___________________________</p>
//     <p>Name: <b>${letter.employee_name || "Candidate Name"}</b></p>
//     <p>Date: ___________________________</p>
//   </div>

//   <div class="footer">This is a system-generated document | For any queries contact: ${org.email || 'hr@company.com'}</div>
// </div>
// </body>
// </html>`;
// };

// /* ─────────────────────────────────────────────
//    APPOINTMENT LETTER HTML (DOC002)
// ───────────────────────────────────────────── */
// const getAppointmentLetterHTML = (letter, org) => {
//    const orgName = org.org_name || process.env.VITE_ORG_NAME || "Organization";
//   const appName = process.env.VITE_APP_NAME || "";
//   const appVersion = process.env.VITE_APP_VERSION || "";
//   return `<!DOCTYPE html>
// <html>
// <head>
//   <meta charset="UTF-8">
//   <title>Appointment Letter - ${letter.employee_name}</title>
//   <style>
//     * { margin: 0; padding: 0; box-sizing: border-box; }
//     body { font-family: Arial, sans-serif; background: white; padding: 0; margin: 0; font-size: 11px; line-height: 1.4; }
//     .page { width: 100%; padding: 0.4in 0.5in; }
//     .header { margin-bottom: 15px; border-bottom: 1px solid #e0e0e0; padding-bottom: 10px; text-align: center; }
//     .company-name { font-size: 18px; font-weight: bold; color: #1a8f6f; }
//     .company-tagline { font-size: 10px; color: #666; margin-top: 4px; }
//     .date { text-align: right; margin-bottom: 15px; font-weight: bold; font-size: 10px; }
//     .employee-details { margin-bottom: 15px; font-size: 10px; }
//     .title { text-align: center; font-size: 14px; font-weight: bold; text-decoration: underline; margin: 15px 0; }
//     .content { font-size: 10px; line-height: 1.4; }
//     .content p { margin-bottom: 6px; }
//     ul, ol { padding-left: 20px; margin: 6px 0; }
//     li { margin-bottom: 2px; font-size: 10px; }
//     .terms { margin-top: 10px; }
//     .terms h6 { font-size: 11px; font-weight: bold; text-decoration: underline; margin: 10px 0 6px 0; }
//     .signature { margin-top: 15px; padding-top: 10px; border-top: 1px solid #e0e0e0; }
//     .acknowledgment { margin-top: 12px; display: flex; justify-content: space-between; font-size: 10px; }
//     .footer { margin-top: 15px; text-align: center; font-size: 8px; color: #888; padding-top: 8px; border-top: 1px solid #e0e0e0; }
//   </style>
// </head>
// <body>
// <div class="page">
//   <div class="header">
//     <div class="company-name">${org.org_name || process.env.VITE_ORG_NAME || 'Company Name'}</div>
//     <div class="company-tagline">${process.env.VITE_APP_NAME || ''} | ${process.env.VITE_APP_VERSION || ''}</div>
//   </div>

//   <div class="date"><b>${formatDate(letter.issue_date)}</b></div>

//   <div class="employee-details">
//     <b>${letter.employee_name || "Employee Name"}</b><br>
//     ${letter.position || "Position"}<br>
//     ${letter.location || "Location"}
//   </div>

//   <div class="title">APPOINTMENT LETTER</div>

//   <div class="content">
//     <p>Dear Ms. ${letter.employee_name?.split(' ')[0] || "Employee"},</p>
//     <p>With reference to your application and subsequent interviews with us, we are pleased to offer you the role of <b>${letter.position || "Position"}</b> in our organization.</p>
//     <p><b>Reporting:</b> You shall report to ${letter.sign_authority || letter.hr_name || "Manager"}.</p>
//     <p><b>Date of Joining:</b> ${formatDate(letter.start_date) || "To be determined"}.</p>
//     <p><b>Remuneration:</b> ${formatCurrency(letter.annual_ctc) || "To be determined"} annually (Monthly ${formatCurrency(letter.monthly_salary) || "To be determined"}).</p>
//     <p><b>Job Responsibilities:</b> Duties assigned by management may change based on business needs.</p>
//     <p><b>Working Hours:</b> As per company policy.</p>
//     <p><b>Location:</b> ${letter.location || "Company location"}.</p>
//     <p><b>Probation Period:</b> 3 months probation. Can be extended or reduced.</p>
//     <p><b>Notice Period:</b> 7 days during probation period | 30 days after confirmation</p>
//     <p><b>Termination:</b> Immediate termination for misconduct.</p>
//     <p><b>Resignation:</b> One-month notice required.</p>

//     <div class="terms">
//       <h6>Terms & Conditions of Employment</h6>
//       <ol>
//         <li>Submit all required documents within 15 days of joining.</li>
//         <li>Maintain discipline and integrity at all times.</li>
//         <li>No other employment without written permission.</li>
//         <li>False information will lead to termination.</li>
//         <li>No gifts/commissions allowed from vendors/clients.</li>
//         <li>Return all company assets on exit.</li>
//         <li>Maintain confidentiality of company information.</li>
//       </ol>
//     </div>

//     <p>All terms are confidential and governed by company rules and Indian law.</p>
//     <p>This letter supersedes all previous agreements.</p>
//     <p>Please sign and return this letter as acceptance.</p>
//     <p>We welcome you and wish you success.</p>
//   </div>

//   <div class="signature">
//     <b>Authorized Signatory</b><br>
//     ${letter.hr_name || "HR Manager"}<br>
//     HR Manager<br>
//     ${letter.company || org.org_name || "Company Name"}
//   </div>

//   <div class="acknowledgment">
//     <div><b>Name of Employee:</b> ${letter.employee_name || ""}</div>
//     <div><b>Date:</b> ___________</div>
//   </div>

//   <div class="footer">This is a system-generated document | For any queries contact: ${org.email || 'hr@company.com'}</div>
// </div>
// </body>
// </html>`;
// };

// /* ─────────────────────────────────────────────
//    EXPERIENCE LETTER HTML (DOC003)
// ───────────────────────────────────────────── */
// const getExperienceLetterHTML = (letter, org) => {
//    const orgName = org.org_name || process.env.VITE_ORG_NAME || "Organization";
//   const appName = process.env.VITE_APP_NAME || "";
//   const appVersion = process.env.VITE_APP_VERSION || "";
//   return `<!DOCTYPE html>
// <html>
// <head>
//   <meta charset="UTF-8">
//   <title>Experience Letter - ${letter.employee_name}</title>
//   <style>
//     * { margin: 0; padding: 0; box-sizing: border-box; }
//     body { font-family: Arial, sans-serif; background: white; padding: 0; margin: 0; font-size: 11px; line-height: 1.4; }
//     .page { width: 100%; padding: 0.4in 0.5in; }
//     .header { margin-bottom: 15px; border-bottom: 1px solid #e0e0e0; padding-bottom: 10px; text-align: center; }
//     .company-name { font-size: 18px; font-weight: bold; color: #1a8f6f; }
//     .company-tagline { font-size: 10px; color: #666; margin-top: 4px; }
//     .title { text-align: center; font-size: 14px; font-weight: bold; text-decoration: underline; margin: 15px 0; }
//     .content { font-size: 10px; line-height: 1.4; }
//     .content p { margin-bottom: 6px; }
//     ul { padding-left: 20px; margin: 6px 0; }
//     li { margin-bottom: 2px; font-size: 10px; }
//     .signature { margin-top: 15px; padding-top: 10px; border-top: 1px solid #e0e0e0; }
//     .footer { margin-top: 15px; text-align: center; font-size: 8px; color: #888; padding-top: 8px; border-top: 1px solid #e0e0e0; }
//     .company-address { margin-top: 15px; padding-top: 10px; border-top: 1px solid #e0e0e0; display: flex; justify-content: space-between; font-size: 9px; }
//   </style>
// </head>
// <body>
// <div class="page">
//   <div class="header">
//     <div class="company-name">${org.org_name || process.env.VITE_ORG_NAME || 'Company Name'}</div>
//     <div class="company-tagline">${process.env.VITE_APP_NAME || ''} | ${process.env.VITE_APP_VERSION || ''}</div>
//   </div>

//   <div class="title">EXPERIENCE LETTER</div>

//   <div class="content">
//     <p>To Whomsoever It May Concern,</p>
//     <p>This is to certify that <b>${letter.employee_name || "Employee Name"}</b>, Employee ID <b>${letter.employee_id || "EMP001"}</b>, was employed with <b>${letter.company || org.org_name || "Company Name"}</b> as a <b>${letter.position || "Position"}</b> in the <b>${letter.department || "Department"}</b> from <b>${formatDate(letter.start_date) || "Start date"}</b> to <b>${formatDate(letter.relieving_date) || "End date"}</b>.</p>
//     <p>During the tenure, the employee was responsible for:</p>
//     <ul><li>Monitoring daily operations</li><li>Ensuring quality and process standards</li><li>Handling workflow execution</li><li>Coordinating with team and supervisors</li><li>Maintaining reports and operational records</li></ul>
//     <p>During the employment period, <b>${letter.employee_name?.split(' ')[0] || "Employee"}</b> demonstrated sincerity, dedication, punctuality, and professionalism. Their performance was found to be <b>${letter.performance || "Excellent"}</b>.</p>
//     <p>We wish them all the best in their future endeavors.</p>
//     <br />
//     <p>For <b>${letter.company || org.org_name || "Company Name"}</b>,</p>
//     <p><b>${letter.sign_authority || letter.hr_name || "HR Manager"}</b></p>
//     <br />
//     <p>Date: ${formatDate(letter.issue_date)}</p>
//     <p>Place: ${letter.place || "Hyderabad"}</p>
//   </div>

//   <div class="company-address">
//     <div><b>${(letter.company || org.org_name || "COMPANY NAME").toUpperCase()}</b><br>${org.address || "1-8-303/48/15, 3rd Floor, KRK House, PG Road, Begumpet, Hyderabad Telangana 500 003"}</div>
//     <div>${org.email ? `Email: ${org.email}<br>` : ''}${org.phone ? `Phone: ${org.phone}` : ''}</div>
//   </div>

//   <div class="footer">This is a system-generated document</div>
// </div>
// </body>
// </html>`;
// };

// /* ─────────────────────────────────────────────
//    RELIEVING LETTER HTML (DOC004)
// ───────────────────────────────────────────── */
// const getRelievingLetterHTML = (letter, org) => {
//    const orgName = org.org_name || process.env.VITE_ORG_NAME || "Organization";
//   const appName = process.env.VITE_APP_NAME || "";
//   const appVersion = process.env.VITE_APP_VERSION || "";
//   return `<!DOCTYPE html>
// <html>
// <head>
//   <meta charset="UTF-8">
//   <title>Relieving Letter - ${letter.employee_name}</title>
//   <style>
//     * { margin: 0; padding: 0; box-sizing: border-box; }
//     body { font-family: Arial, sans-serif; background: white; padding: 0; margin: 0; font-size: 11px; line-height: 1.4; }
//     .page { width: 100%; padding: 0.4in 0.5in; }
//     .header { margin-bottom: 15px; border-bottom: 1px solid #e0e0e0; padding-bottom: 10px; text-align: center; }
//     .company-name { font-size: 18px; font-weight: bold; color: #1a8f6f; }
//     .company-tagline { font-size: 10px; color: #666; margin-top: 4px; }
//     .date { text-align: right; margin-bottom: 15px; font-weight: bold; font-size: 10px; }
//     .employee-details { margin-bottom: 15px; font-size: 10px; }
//     .title { text-align: center; font-size: 14px; font-weight: bold; text-decoration: underline; margin: 15px 0; }
//     .content { font-size: 10px; line-height: 1.4; }
//     .content p { margin-bottom: 6px; }
//     .signature { margin-top: 15px; padding-top: 10px; border-top: 1px solid #e0e0e0; }
//     .acknowledgment { margin-top: 15px; }
//     .footer { margin-top: 15px; text-align: center; font-size: 8px; color: #888; padding-top: 8px; border-top: 1px solid #e0e0e0; }
//   </style>
// </head>
// <body>
// <div class="page">
//   <div class="header">
//     <div class="company-name">${org.org_name || process.env.VITE_ORG_NAME || 'Company Name'}</div>
//     <div class="company-tagline">${process.env.VITE_APP_NAME || ''} | ${process.env.VITE_APP_VERSION || ''}</div>
//   </div>

//   <div class="date"><b>${formatDate(letter.relieving_date) || formatDate(letter.issue_date)}</b></div>

//   <div class="employee-details">
//     <b>${letter.employee_name || "Employee Name"}</b><br>
//     ${letter.position || "Position"}<br>
//     ${letter.location || "Location"}
//   </div>

//   <div class="title">RELIEVING LETTER</div>

//   <div class="content">
//     <p>Dear ${letter.employee_name?.split(' ')[0] || "Employee"},</p>
//     <p>This is to formally acknowledge that you have resigned from your position as <b>${letter.position || "Position"}</b> at <b>${letter.company || org.org_name || "Company Name"}</b>.</p>
//     <p>Your resignation has been accepted, and you have been relieved from your duties effective <b>${formatDate(letter.relieving_date) || "To be determined"}</b>.</p>
//     <p>During your tenure from <b>${formatDate(letter.start_date) || "Start date"}</b> to <b>${formatDate(letter.relieving_date) || "End date"}</b>, you have worked with us in the capacity of <b>${letter.position || "Position"}</b>.</p>
//     <p>We confirm that you have completed all your assigned responsibilities and handed over your duties and company assets properly.</p>
//     <p>We appreciate your contributions during your tenure and wish you success in your future endeavors.</p>
//   </div>

//   <div class="signature">
//     Sincerely,<br>
//     <b>${letter.hr_name || "HR Manager"}</b><br>
//     ${letter.company || org.org_name || "Company Name"}
//   </div>

//   <div class="acknowledgment">
//     <p><b>Acknowledgement by Employee</b></p>
//     <p>I hereby acknowledge the receipt of this relieving letter.</p>
//     <div style="display: flex; justify-content: space-between; margin-top: 10px;">
//       <div><b>Name:</b> ${letter.employee_name || ""}</div>
//       <div><b>Date:</b> ___________</div>
//     </div>
//   </div>

//   <div class="footer">This is a system-generated document</div>
// </div>
// </body>
// </html>`;
// };

// /* ─────────────────────────────────────────────
//    WARNING LETTER HTML (DOC005)
// ───────────────────────────────────────────── */
// const getWarningLetterHTML = (letter, org) => {
//    const orgName = org.org_name || process.env.VITE_ORG_NAME || "Organization";
//   const appName = process.env.VITE_APP_NAME || "";
//   const appVersion = process.env.VITE_APP_VERSION || "";
//   return `<!DOCTYPE html>
// <html>
// <head>
//   <meta charset="UTF-8">
//   <title>Warning Letter - ${letter.employee_name}</title>
//   <style>
//     * { margin: 0; padding: 0; box-sizing: border-box; }
//     body { font-family: Arial, sans-serif; background: white; padding: 0; margin: 0; font-size: 11px; line-height: 1.4; }
//     .page { width: 100%; padding: 0.4in 0.5in; }
//     .header { margin-bottom: 15px; border-bottom: 1px solid #e0e0e0; padding-bottom: 10px; text-align: center; }
//     .company-name { font-size: 18px; font-weight: bold; color: #c0392b; }
//     .company-tagline { font-size: 10px; color: #666; margin-top: 4px; }
//     .date { text-align: right; margin-bottom: 15px; font-weight: bold; font-size: 10px; }
//     .employee-details { margin-bottom: 15px; font-size: 10px; }
//     .title { text-align: center; font-size: 14px; font-weight: bold; text-decoration: underline; color: #c0392b; margin: 15px 0; }
//     .content { font-size: 10px; line-height: 1.4; }
//     .content p { margin-bottom: 6px; }
//     .signature { margin-top: 15px; padding-top: 10px; border-top: 1px solid #e0e0e0; }
//     .acknowledgment { margin-top: 15px; }
//     .footer { margin-top: 15px; text-align: center; font-size: 8px; color: #888; padding-top: 8px; border-top: 1px solid #e0e0e0; }
//   </style>
// </head>
// <body>
// <div class="page">
//   <div class="header">
//     <div class="company-name">${org.org_name || process.env.VITE_ORG_NAME || 'Company Name'}</div>
//     <div class="company-tagline">${process.env.VITE_APP_NAME || ''} | ${process.env.VITE_APP_VERSION || ''}</div>
//   </div>

//   <div class="date"><b>${formatDate(letter.issue_date)}</b></div>

//   <div class="employee-details">
//     <b>${letter.employee_name || "Employee Name"}</b><br>
//     ${letter.position || "Position"}<br>
//     ${letter.location || "Location"}
//   </div>

//   <div class="title">WARNING LETTER</div>

//   <div class="content">
//     <p>Dear ${letter.employee_name?.split(' ')[0] || "Employee"},</p>
//     <p>This letter is to formally notify you regarding your conduct/performance observed during your tenure at <b>${letter.company || org.org_name || "Company Name"}</b>.</p>
//     <p>It has been observed that you have been involved in <b>${letter.warning_issue || "irregular attendance / not meeting expected performance standards"}</b>, which is not in line with the company policies and expectations.</p>
//     <p>Despite previous discussions and verbal warnings, there has not been sufficient improvement in your behavior/performance.</p>
//     <p>You are hereby advised to take this matter seriously and show immediate improvement. Failure to do so may result in further disciplinary action, which may include termination of your employment.</p>
//     <p>We expect you to maintain professionalism and adhere strictly to company policies moving forward.</p>
//   </div>

//   <div class="signature">
//     Sincerely,<br>
//     <b>${letter.hr_name || "HR Manager"}</b><br>
//     ${letter.company || org.org_name || "Company Name"}
//   </div>

//   <div class="acknowledgment">
//     <p><b>Acknowledgement by Employee</b></p>
//     <p>I acknowledge receipt of this warning letter and understand the contents.</p>
//     <div style="display: flex; justify-content: space-between; margin-top: 10px;">
//       <div><b>Name:</b> ${letter.employee_name || ""}</div>
//       <div><b>Date:</b> ___________</div>
//     </div>
//   </div>

//   <div class="footer">This is a system-generated document</div>
// </div>
// </body>
// </html>`;
// };

// /* ─────────────────────────────────────────────
//    SALARY CERTIFICATE HTML (DOC006)
// ───────────────────────────────────────────── */
// /* ─────────────────────────────────────────────
//    SALARY CERTIFICATE HTML (DOC006)
// ───────────────────────────────────────────── */
// const getSalaryCertificateHTML = (letter, org) => {
//   const formatDate = (dateVal) => {
//     if (!dateVal) return new Date().toLocaleDateString("en-IN", { day: "numeric", month: "long", year: "numeric" });
//     const d = new Date(dateVal);
//     if (isNaN(d)) return new Date().toLocaleDateString("en-IN", { day: "numeric", month: "long", year: "numeric" });
//     return d.toLocaleDateString("en-IN", { day: "numeric", month: "long", year: "numeric" });
//   };
//  const orgName = org.org_name || process.env.VITE_ORG_NAME || "Organization";
//   const appName = process.env.VITE_APP_NAME || "";
//   const appVersion = process.env.VITE_APP_VERSION || "";
//   const formatCurrency = (amount) => {
    
//     if (!amount || isNaN(amount) || amount === 0) return "To be determined";
//     return new Intl.NumberFormat("en-IN", {
//       style: "currency",
//       currency: "INR",
//       minimumFractionDigits: 2,
//       maximumFractionDigits: 2,
//     }).format(amount);
//   };

//   return `<!DOCTYPE html>
// <html>
// <head>
//   <meta charset="UTF-8">
//   <title>Salary Certificate - ${letter.employee_name}</title>
//   <style>
//     * { margin: 0; padding: 0; box-sizing: border-box; }
//     body { font-family: Arial, sans-serif; background: white; padding: 0; margin: 0; font-size: 11px; line-height: 1.4; }
//     .page { width: 100%; padding: 0.4in 0.5in; }
//     .header { margin-bottom: 15px; border-bottom: 1px solid #e0e0e0; padding-bottom: 10px; text-align: center; }
//     .company-name { font-size: 18px; font-weight: bold; color: #1a8f6f; }
//     .company-tagline { font-size: 10px; color: #666; margin-top: 4px; }
//     .date { text-align: right; margin-bottom: 15px; font-weight: bold; font-size: 10px; }
//     .employee-details { margin-bottom: 15px; font-size: 10px; }
//     .title { text-align: center; font-size: 14px; font-weight: bold; text-decoration: underline; margin: 15px 0; }
//     .content { font-size: 10px; line-height: 1.4; }
//     .content p { margin-bottom: 6px; }
//     table { width: 100%; border-collapse: collapse; margin: 10px 0; }
//     th, td { border: 1px solid #dee2e6; padding: 8px; text-align: center; }
//     th { background-color: #f8f9fa; font-weight: bold; }
//     .signature { margin-top: 15px; padding-top: 10px; border-top: 1px solid #e0e0e0; display: flex; justify-content: space-between; }
//     .footer { margin-top: 15px; text-align: center; font-size: 8px; color: #888; padding-top: 8px; border-top: 1px solid #e0e0e0; }
//   </style>
// </head>
// <body>
// <div class="page">
//   <div class="header">
//     <div class="company-name">${org.org_name || process.env.VITE_ORG_NAME || 'Company Name'}</div>
//     <div class="company-tagline">${process.env.VITE_APP_NAME || ''} | ${process.env.VITE_APP_VERSION || ''}</div>
//   </div>

//   <div class="date"><b>Date: ${formatDate(letter.issue_date)}</b></div>

//   <div class="title">SALARY CERTIFICATE</div>

//   <div class="content">
//     <p>This is to certify that <b>${letter.employee_name || "Employee Name"}</b> is currently employed with <b>${letter.company || org.org_name || "Company Name"}</b> as a <b>${letter.position || "Position"}</b>.</p>

//     <p>As per the company records, the employee is drawing the following salary:</p>

//     <table>
//       <thead>
//         <tr><th>Particulars</th><th>Amount (INR)</th></tr>
//       </thead>
//       <tbody>
//         <tr><td>Monthly Salary</td><td>${formatCurrency(letter.monthly_salary)}</td></tr>
//         <tr><td>Annual Salary (CTC)</td><td>${formatCurrency(letter.annual_ctc)}</td></tr>
//       </tbody>
//     </table>

//     <p>This certificate is issued upon the request of the employee for official and personal purposes.</p>
//   </div>

//   <div class="signature">
//     <div>
//       For <b>${letter.company || org.org_name || "Company Name"}</b><br><br>
//       <b>${letter.hr_name || "HR Manager"}</b><br>
//       Authorized Signatory
//     </div>
//     <div style="text-align: right;">
//       <b>Employee Signature</b><br><br>
//       ____________________
//     </div>
//   </div>

//   <div class="footer">This is a system-generated document | Valid for 30 days from issue date</div>
// </div>
// </body>
// </html>`;
// };

// /* ─────────────────────────────────────────────
//    DATABASE QUERY FUNCTIONS
// ───────────────────────────────────────────── */
// const getHrDocuments = async (req, res) => {
  
//   try {
//     const org_id = req.session.user.orgId;
//     const result = await pool.query(
//       `SELECT id, doc_code, name, type, route_key, created_at
//        FROM hr_documents
//        WHERE is_deleted = false AND org_id = $1
//        ORDER BY doc_code ASC`,
//       [org_id]
//     );
//     return res.json({ success: true, data: result.rows });
//   } catch (err) {
//     console.error("GET HR DOCUMENTS ERROR:", err);
//     return res.status(500).json({ success: false, message: err.message });
//   }
// };

// // const getGeneratedLetters = async (req, res) => {
// //   try {
// //     const org_id = req.session.user.orgId;
// //     const result = await pool.query(
// //       `SELECT gl.*, hd.name AS doc_name, hd.doc_code, hd.route_key
// //        FROM generated_letters gl
// //        JOIN hr_documents hd ON hd.id = gl.doc_id
// //        WHERE gl.is_deleted = false AND gl.org_id = $1
// //        ORDER BY gl.created_at DESC`,
// //       [org_id]
// //     );
// //     return res.json({ success: true, data: result.rows, totalRecords: result.rows.length });
// //   } catch (err) {
// //     console.error("GET GENERATED LETTERS ERROR:", err);
// //     return res.status(500).json({ success: false, message: err.message });
// //   }
// // };
// const getGeneratedLetters = async (req, res) => {
//   try {
//     const org_id = req.session.user.orgId;
//     const { page = 1, limit = 10, doc_code, search } = req.query;
//     const offset = (parseInt(page) - 1) * parseInt(limit);
    
//     let query = `
//       SELECT gl.*, hd.name AS doc_name, hd.doc_code, hd.route_key
//       FROM generated_letters gl
//       JOIN hr_documents hd ON hd.id = gl.doc_id
//       WHERE gl.is_deleted = false AND gl.org_id = $1
//     `;
//     let countQuery = `
//       SELECT COUNT(*) 
//       FROM generated_letters gl
//       JOIN hr_documents hd ON hd.id = gl.doc_id
//       WHERE gl.is_deleted = false AND gl.org_id = $1
//     `;
//     let params = [org_id];
//     let countParams = [org_id];
//     let paramIndex = 2;
    
//     if (doc_code && doc_code !== 'all') {
//       query += ` AND hd.doc_code = $${paramIndex}`;
//       countQuery += ` AND hd.doc_code = $${paramIndex}`;
//       params.push(doc_code);
//       countParams.push(doc_code);
//       paramIndex++;
//     }
    
//     if (search) {
//       query += ` AND (gl.employee_name ILIKE $${paramIndex} OR hd.name ILIKE $${paramIndex})`;
//       countQuery += ` AND (gl.employee_name ILIKE $${paramIndex} OR hd.name ILIKE $${paramIndex})`;
//       params.push(`%${search}%`);
//       countParams.push(`%${search}%`);
//       paramIndex++;
//     }
    
//     query += ` ORDER BY gl.created_at DESC LIMIT $${paramIndex} OFFSET $${paramIndex + 1}`;
//     params.push(parseInt(limit), offset);
    
//     const [result, countResult] = await Promise.all([
//       pool.query(query, params),
//       pool.query(countQuery, countParams)
//     ]);
    
//     return res.json({ 
//       success: true, 
//       data: result.rows, 
//       totalRecords: parseInt(countResult.rows[0].count)
//     });
//   } catch (err) {
//     console.error("GET GENERATED LETTERS ERROR:", err);
//     return res.status(500).json({ success: false, message: err.message });
//   }
// };
// const getGeneratedLetterById = async (req, res) => {
//   try {
//     const { id } = req.params;
//     const org_id = req.session.user.orgId;
//     const result = await pool.query(
//       `SELECT gl.*, hd.name AS doc_name, hd.doc_code, hd.route_key
//        FROM generated_letters gl
//        JOIN hr_documents hd ON hd.id = gl.doc_id
//        WHERE gl.id = $1 AND gl.org_id = $2 AND gl.is_deleted = false`,
//       [id, org_id]
//     );
//     if (!result.rows.length) {
//       return res.status(404).json({ success: false, message: "Letter not found" });
//     }
//     return res.json({ success: true, data: result.rows[0] });
//   } catch (err) {
//     console.error("GET GENERATED LETTER BY ID ERROR:", err);
//     return res.status(500).json({ success: false, message: err.message });
//   }
// };

// // const generateLetter = async (req, res) => {
// //   try {
// //      const orgName = org.org_name || process.env.VITE_ORG_NAME || "Organization";
// //   const appName = process.env.VITE_APP_NAME || "";
// //   const appVersion = process.env.VITE_APP_VERSION || "";
// //     const org_id = req.session.user.orgId;
// //     const userId = req.session.user.id;
// //     const { doc_code, employee_db_id, issue_date, relieving_date, start_date, warning_issue, performance, place, sign_authority, hr_name, company } = req.body;

// //     if (!doc_code || !employee_db_id) {
// //       return res.status(400).json({ success: false, message: "doc_code and employee_db_id are required" });
// //     }

// //     const docResult = await pool.query(
// //       `SELECT id FROM hr_documents WHERE doc_code = $1 AND is_deleted = false AND org_id = $2`,
// //       [doc_code, org_id]
// //     );
// //     if (!docResult.rows.length) {
// //       return res.status(404).json({ success: false, message: `Document type '${doc_code}' not found` });
// //     }
// //     const doc_id = docResult.rows[0].id;

// //     const empResult = await pool.query(
// //       `SELECT e.id, e.employee_id, e.first_name, e.last_name, e.salary,
// //               COALESCE(des.designation_name, e.designation) AS position,
// //               COALESCE(d.dept_name, e.department) AS department,
// //               e.work_location, e.joining_date
// //        FROM employees e
// //        LEFT JOIN departments d ON d.id::text = e.department
// //        LEFT JOIN designations des ON des.id::text = e.designation
// //        WHERE e.id = $1 AND e.org_id = $2 AND e.is_deleted = false`,
// //       [employee_db_id, org_id]
// //     );
// //     if (!empResult.rows.length) {
// //       return res.status(404).json({ success: false, message: "Employee not found" });
// //     }

// //     const emp = empResult.rows[0];
// //     const monthly = parseFloat(emp.salary) || 0;
// //     const annual = monthly * 12;

// //     const insertResult = await pool.query(
// //       `INSERT INTO generated_letters (doc_id, employee_db_id, employee_id, employee_name, position, department, location, monthly_salary, annual_ctc, joining_date, company, hr_name, sign_authority, place, issue_date, start_date, relieving_date, warning_issue, performance, generated_by, org_id, created_by)
// //        VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20,$21,$22) RETURNING *`,
// //       [doc_id, emp.id, emp.employee_id, `${emp.first_name} ${emp.last_name}`, emp.position || null, emp.department || null, emp.work_location || null, monthly, annual, emp.joining_date || null, company || orgName, hr_name || "HR Manager", sign_authority || null, place || "Hyderabad", issue_date || new Date().toISOString().split("T")[0], start_date || emp.joining_date || null, relieving_date || null, warning_issue || null, performance || null, req.session.user.name || null, org_id, userId]
// //     );

// //     return res.status(201).json({ success: true, message: "Letter generated successfully", data: insertResult.rows[0] });
// //   } catch (err) {
// //     console.error("GENERATE LETTER ERROR:", err);
// //     return res.status(500).json({ success: false, message: err.message });
// //   }
// // };
// const generateLetter = async (req, res) => {
//   try {
//     const org_id = req.session.user.orgId;
//     const userId = req.session.user.id;

//     // FETCH ORGANIZATION DETAILS
//     const org = await getOrganizationDetails(org_id);

//     const orgName =
//       org?.org_name ||
//       process.env.VITE_ORG_NAME ||
//       "Organization";

//     const {
//       doc_code,
//       employee_db_id,
//       issue_date,
//       relieving_date,
//       start_date,
//       warning_issue,
//       performance,
//       place,
//       sign_authority,
//       hr_name,
//       company,
//     } = req.body;

//     if (!doc_code || !employee_db_id) {
//       return res.status(400).json({
//         success: false,
//         message: "doc_code and employee_db_id are required",
//       });
//     }

//     // GET DOCUMENT
//     const docResult = await pool.query(
//       `SELECT id
//        FROM hr_documents
//        WHERE doc_code = $1
//          AND is_deleted = false
//          AND org_id = $2`,
//       [doc_code, org_id]
//     );

//     if (!docResult.rows.length) {
//       return res.status(404).json({
//         success: false,
//         message: `Document type '${doc_code}' not found`,
//       });
//     }

//     const doc_id = docResult.rows[0].id;

//     // GET EMPLOYEE
//     const empResult = await pool.query(
//       `SELECT
//           e.id,
//           e.employee_id,
//           e.first_name,
//           e.last_name,
//           e.salary,
//           COALESCE(des.designation_name, e.designation) AS position,
//           COALESCE(d.dept_name, e.department) AS department,
//           e.work_location,
//           e.joining_date
//        FROM employees e
//        LEFT JOIN departments d
//          ON d.id::text = e.department
//        LEFT JOIN designations des
//          ON des.id::text = e.designation
//        WHERE e.id = $1
//          AND e.org_id = $2
//          AND e.is_deleted = false`,
//       [employee_db_id, org_id]
//     );

//     if (!empResult.rows.length) {
//       return res.status(404).json({
//         success: false,
//         message: "Employee not found",
//       });
//     }

//     const emp = empResult.rows[0];

//     const monthly = parseFloat(emp.salary) || 0;
//     const annual = monthly * 12;

//     // INSERT LETTER
//     const insertResult = await pool.query(
//       `INSERT INTO generated_letters (
//           doc_id,
//           employee_db_id,
//           employee_id,
//           employee_name,
//           position,
//           department,
//           location,
//           monthly_salary,
//           annual_ctc,
//           joining_date,
//           company,
//           hr_name,
//           sign_authority,
//           place,
//           issue_date,
//           start_date,
//           relieving_date,
//           warning_issue,
//           performance,
//           generated_by,
//           org_id,
//           created_by
//        )
//        VALUES (
//           $1,$2,$3,$4,$5,$6,$7,$8,$9,$10,
//           $11,$12,$13,$14,$15,$16,$17,$18,
//           $19,$20,$21,$22
//        )
//        RETURNING *`,
//       [
//         doc_id,
//         emp.id,
//         emp.employee_id,
//         `${emp.first_name} ${emp.last_name}`,
//         emp.position || null,
//         emp.department || null,
//         emp.work_location || null,
//         monthly,
//         annual,
//         emp.joining_date || null,
//         company || orgName,
//         hr_name || "HR Manager",
//         sign_authority || null,
//         place || "Hyderabad",
//         issue_date || new Date().toISOString().split("T")[0],
//         start_date || emp.joining_date || null,
//         relieving_date || null,
//         warning_issue || null,
//         performance || null,
//         req.session.user.name || null,
//         org_id,
//         userId,
//       ]
//     );

//     return res.status(201).json({
//       success: true,
//       message: "Letter generated successfully",
//       data: insertResult.rows[0],
//     });
//   } catch (err) {
//     console.error("GENERATE LETTER ERROR:", err);

//     return res.status(500).json({
//       success: false,
//       message: err.message,
//     });
//   }
// };
// const getLetterData = async (req, res) => {
//   try {
//     const { id } = req.params;
//     const org_id = req.session.user.orgId;
//     const result = await pool.query(
//       `SELECT gl.*, hd.name AS doc_name, hd.doc_code, hd.route_key
//        FROM generated_letters gl
//        JOIN hr_documents hd ON hd.id = gl.doc_id
//        WHERE gl.id = $1 AND gl.org_id = $2 AND gl.is_deleted = false`,
//       [id, org_id]
//     );
//     if (!result.rows.length) {
//       return res.status(404).json({ success: false, message: "Not found" });
//     }
//     const r = result.rows[0];
//     const letterData = {
//       name: r.employee_name,
//       empId: r.employee_id,
//       position: r.position,
//       department: r.department,
//       location: r.location,
//       salary: formatCurrency(r.annual_ctc),
//       monthlySalary: formatCurrency(r.monthly_salary),
//       company: r.company,
//       hrName: r.hr_name,
//       signAuthority: r.sign_authority,
//       place: r.place,
//       date: formatDate(r.issue_date),
//       startDate: formatDate(r.start_date),
//       relievingDate: formatDate(r.relieving_date),
//       joiningDate: formatDate(r.joining_date),
//       issue: r.warning_issue,
//       performance: r.performance,
//       issueDate: formatDate(r.issue_date),
//       docName: r.doc_name,
//       docCode: r.doc_code,
//       routeKey: r.route_key,
//     };
//     return res.json({ success: true, data: letterData });
//   } catch (err) {
//     console.error("GET LETTER DATA ERROR:", err);
//     return res.status(500).json({ success: false, message: err.message });
//   }
// };

// const deleteGeneratedLetter = async (req, res) => {
//   try {
//     const { id } = req.params;
//     const org_id = req.session.user.orgId;
//     const userId = req.session.user.id;
//     const result = await pool.query(
//       `UPDATE generated_letters SET is_deleted = true, updated_by = $1, updated_at = NOW()
//        WHERE id = $2 AND org_id = $3 AND is_deleted = false RETURNING id`,
//       [userId, id, org_id]
//     );
//     if (!result.rows.length) {
//       return res.status(404).json({ success: false, message: "Letter not found" });
//     }
//     return res.json({ success: true, message: "Letter deleted successfully" });
//   } catch (err) {
//     console.error("DELETE GENERATED LETTER ERROR:", err);
//     return res.status(500).json({ success: false, message: err.message });
//   }
// };

// const getLetterStats = async (req, res) => {
//   try {
//     const org_id = req.session.user.orgId;
//     const result = await pool.query(
//       `SELECT COUNT(*) AS total_generated,
//               COUNT(*) FILTER (WHERE hd.doc_code = 'DOC001') AS offer_letters,
//               COUNT(*) FILTER (WHERE hd.doc_code = 'DOC002') AS appointment_letters,
//               COUNT(*) FILTER (WHERE hd.doc_code = 'DOC003') AS experience_letters,
//               COUNT(*) FILTER (WHERE hd.doc_code = 'DOC004') AS relieving_letters,
//               COUNT(*) FILTER (WHERE hd.doc_code = 'DOC005') AS warning_letters,
//               COUNT(*) FILTER (WHERE hd.doc_code = 'DOC006') AS salary_certificates,
//               COUNT(*) FILTER (WHERE gl.created_at >= NOW() - INTERVAL '30 days') AS this_month
//        FROM generated_letters gl
//        JOIN hr_documents hd ON hd.id = gl.doc_id
//        WHERE gl.is_deleted = false AND gl.org_id = $1`,
//       [org_id]
//     );
//     return res.json({ success: true, data: result.rows[0] });
//   } catch (err) {
//     console.error("GET LETTER STATS ERROR:", err);
//     return res.status(500).json({ success: false, message: err.message });
//   }
// };

// /* ─────────────────────────────────────────────
//    DOWNLOAD LETTER AS PDF
// ───────────────────────────────────────────── */
// const downloadLetterPDF = async (req, res) => {
//   let browser = null;
  
//   try {
//     const { id } = req.params;
//     const org_id = req.session.user?.orgId;

//     if (!org_id) {
//       return res.status(401).json({ success: false, message: "Organization not found" });
//     }

//     const result = await pool.query(
//       `SELECT gl.*, hd.name AS doc_name, hd.doc_code, hd.route_key
//        FROM generated_letters gl
//        JOIN hr_documents hd ON hd.id = gl.doc_id
//        WHERE gl.id = $1 AND gl.org_id = $2 AND gl.is_deleted = false`,
//       [id, org_id]
//     );

//     if (!result.rows.length) {
//       return res.status(404).json({ success: false, message: "Letter not found" });
//     }

//     const letter = result.rows[0];
//     const org = await getOrganizationDetails(org_id);
    
//     let html;
//    switch (letter.doc_code) {
//   case 'DOC001':
//     html = getOfferLetterHTML(letter, org);
//     break;
//   case 'DOC002':
//     html = getAppointmentLetterHTML(letter, org);
//     break;
//   case 'DOC003':
//     html = getExperienceLetterHTML(letter, org);
//     break;
//   case 'DOC004':
//     html = getRelievingLetterHTML(letter, org);
//     break;
//   case 'DOC005':
//     html = getWarningLetterHTML(letter, org);
//     break;
//   case 'DOC006':
//     html = getSalaryCertificateHTML(letter, org);
//     break;
//   default:
//     html = getAppointmentLetterHTML(letter, org);
// }
    
//     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();
//     page.setDefaultTimeout(30000);
    
//     await page.setRequestInterception(true);
//     page.on('request', (request) => {
//       const resourceType = request.resourceType();
//       if (resourceType === 'font' || resourceType === 'media') {
//         request.abort();
//       } else {
//         request.continue();
//       }
//     });
    
//     await page.setViewport({ width: 1024, height: 800 });
//     await page.setContent(html, { waitUntil: 'domcontentloaded', timeout: 30000 });
//     await page.evaluate(() => new Promise(resolve => setTimeout(resolve, 500)));

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

//     const filename = `${letter.doc_code}_${letter.employee_name.replace(/\s/g, '_')}_${new Date().toISOString().split('T')[0]}.pdf`;
    
//     res.set({
//       "Content-Type": "application/pdf",
//       "Content-Disposition": `attachment; filename="${filename}"`,
//       "Content-Length": pdf.length,
//     });
    
//     res.send(pdf);

//   } catch (error) {
//     console.error("PDF Generation Error:", error);
//     res.status(500).json({ success: false, message: "Failed to generate PDF: " + error.message });
//   } finally {
//     if (browser) {
//       await browser.close();
//     }
//   }
// };

// module.exports = {
//   getHrDocuments,
//   getGeneratedLetters,
//   getGeneratedLetterById,
//   generateLetter,
//   getLetterData,
//   deleteGeneratedLetter,
//   getLetterStats,
//   downloadLetterPDF,
// };


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

/* ─────────────────────────────────────────────
   HELPER — Get logo as Base64
───────────────────────────────────────────── */
const getLogoBase64 = () => {
  try {
    // Try multiple possible paths for the logo
    const possiblePaths = [

      path.join(__dirname, '..', '..', 'public', 'logo.png'),
     
     
    ];
    
    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;
};

/* ─────────────────────────────────────────────
   HELPER — format date
───────────────────────────────────────────── */
const formatDate = (dateVal) => {
  if (!dateVal) return new Date().toLocaleDateString("en-IN", { day: "numeric", month: "long", year: "numeric" });
  const d = new Date(dateVal);
  if (isNaN(d)) return new Date().toLocaleDateString("en-IN", { day: "numeric", month: "long", year: "numeric" });
  return d.toLocaleDateString("en-IN", { day: "numeric", month: "long", year: "numeric" });
};

/* ─────────────────────────────────────────────
   HELPER — format currency
───────────────────────────────────────────── */
const formatCurrency = (amount) => {
  if (!amount || isNaN(amount) || amount === 0) return null;
  return new Intl.NumberFormat("en-IN", {
    style: "currency",
    currency: "INR",
    minimumFractionDigits: 2,
    maximumFractionDigits: 2,
  }).format(amount);
};

/* ─────────────────────────────────────────────
   HELPER — Get organization details
───────────────────────────────────────────── */
const getOrganizationDetails = async (orgId) => {
  try {
    const orgResult = await pool.query(
      `SELECT org_name, email, phone, address 
       FROM organizations 
       WHERE id = $1 AND is_deleted = false 
       LIMIT 1`,
      [orgId]
    );
    
    if (orgResult.rows.length > 0) {
      return orgResult.rows[0];
    }
    
    return {
      org_name: process.env.VITE_APP_NAME || "VIVEK BIO-PRODUCTS",
      email: "",
      phone: "",
      address: ""
    };
  } catch (error) {
    console.error("Error fetching organization details:", error);
    return {
      org_name: process.env.VITE_APP_NAME || "VIVEK BIO-PRODUCTS",
      email: "",
      phone: "",
      address: ""
    };
  }
};

/* ─────────────────────────────────────────────
   HELPER — Get app details from env
───────────────────────────────────────────── */
const getAppName = () => {
  return process.env.VITE_APP_NAME || "VIVEK BIO-PRODUCTS";
};

const getAppVersion = () => {
  return process.env.VITE_APP_VERSION || "v1.1";
};

const getOrgName = () => {
  return process.env.VITE_ORG_NAME || "Varunix";
};

/* ─────────────────────────────────────────────
   OFFER LETTER HTML (DOC001)
───────────────────────────────────────────── */
const getOfferLetterHTML = (letter, org) => {
  const companyName = getAppName();
  const appName = getAppName();
  const appVersion = getAppVersion();
  const logoBase64 = getLogoBase64();
  const logoHtml = logoBase64 ? `<img src="${logoBase64}" alt="Company Logo" class="logo" />` : '<div style="width: 60px;"></div>';
  
  return `<!DOCTYPE html>
<html>
<head>
  <meta charset="UTF-8">
  <title>Offer Letter - ${letter.employee_name}</title>
  <style>
    * { margin: 0; padding: 0; box-sizing: border-box; }
    body { font-family: Arial, sans-serif; background: white; padding: 0; margin: 0; font-size: 11px; line-height: 1.4; }
    .page { width: 100%; padding: 0.4in 0.5in; }
    .header { margin-bottom: 15px; border-bottom: 1px solid #e0e0e0; padding-bottom: 10px; display: flex; justify-content: space-between; align-items: center; }
    .logo { height: 60px; object-fit: contain; }
    .company-info { text-align: right; }
    .company-name { font-size: 18px; font-weight: bold; color: #1a8f6f; }
    .company-tagline { font-size: 10px; color: #666; margin-top: 4px; }
    .date { text-align: right; margin-bottom: 15px; font-weight: bold; font-size: 10px; }
    .employee-details { margin-bottom: 15px; font-size: 10px; }
    .title { text-align: center; font-size: 14px; font-weight: bold; text-decoration: underline; margin: 15px 0; }
    .content { font-size: 10px; line-height: 1.4; }
    .content p { margin-bottom: 6px; }
    ul, ol { padding-left: 20px; margin: 6px 0; }
    li { margin-bottom: 2px; font-size: 10px; }
    .signature { margin-top: 15px; padding-top: 10px; border-top: 1px solid #e0e0e0; }
    .footer { margin-top: 15px; text-align: center; font-size: 8px; color: #888; padding-top: 8px; border-top: 1px solid #e0e0e0; }
  </style>
</head>
<body>
<div class="page">
  <div class="header">
    ${logoHtml}
    <div class="company-info">
      <div class="company-name">${companyName}</div>
      <div class="company-tagline">${appName} | ${appVersion}</div>
    </div>
  </div>

  <div class="date"><b>${formatDate(letter.issue_date)}</b></div>

  <div class="employee-details">
    <b>${letter.employee_name || "Candidate Name"}</b><br>
    ${letter.position || "Position"}<br>
    ${letter.location || "Location"}
  </div>

  <div class="title">OFFER LETTER</div>

  <div class="content">
    <p>Dear <b>${letter.employee_name?.split(' ')[0] || "Candidate"}</b>,</p>
    <p>We are pleased to offer you the position of <b>${letter.position || "Position"}</b> at <b>${companyName}</b>.</p>
    <p><b>1. Position and Reporting:</b> Your role will be <b>${letter.position || "Position"}</b>, reporting to the Shift Supervisor/Manager.</p>
    <p><b>2. Commencement of Employment:</b> Your employment is scheduled to commence on <b>${formatDate(letter.start_date) || "To be determined"}</b>.</p>
    <p><b>3. Probation Period:</b> You will be on a probation period of three (3) months from your date of joining.</p>
    <p><b>4. Compensation:</b> You will be entitled to a gross annual compensation of <b>${formatCurrency(letter.annual_ctc) || "To be determined"}</b>.</p>
    <p><b>5. Work Hours and Location:</b> Location: <b>${letter.location || "On-site"}</b></p>
    <p><b>6. Termination:</b></p>
    <ul><li>7 days notice during probation</li><li>30 days after confirmation</li></ul>
    <p>We are excited to welcome you to <b>${companyName}</b>.</p>
    <p class="mb-0">Warm regards,</p>
    <p class="mb-0">${letter.hr_name || "HR Manager"}</p>
    <p>${companyName}</p>
    <hr />
    <p><b>Accepted and Agreed by:</b></p>
    <p>Signature: ___________________________</p>
    <p>Name: <b>${letter.employee_name || "Candidate Name"}</b></p>
    <p>Date: ___________________________</p>
  </div>

  <div class="footer">This is a system-generated document | ${appName} | ${appVersion}</div>
</div>
</body>
</html>`;
};

/* ─────────────────────────────────────────────
   APPOINTMENT LETTER HTML (DOC002)
───────────────────────────────────────────── */
const getAppointmentLetterHTML = (letter, org) => {
  const companyName = getAppName();
  const appName = getAppName();
  const appVersion = getAppVersion();
  const logoBase64 = getLogoBase64();
  const logoHtml = logoBase64 ? `<img src="${logoBase64}" alt="Company Logo" class="logo" />` : '<div style="width: 60px;"></div>';
  
  return `<!DOCTYPE html>
<html>
<head>
  <meta charset="UTF-8">
  <title>Appointment Letter - ${letter.employee_name}</title>
  <style>
    * { margin: 0; padding: 0; box-sizing: border-box; }
    body { font-family: Arial, sans-serif; background: white; padding: 0; margin: 0; font-size: 11px; line-height: 1.4; }
    .page { width: 100%; padding: 0.4in 0.5in; }
    .header { margin-bottom: 15px; border-bottom: 1px solid #e0e0e0; padding-bottom: 10px; display: flex; justify-content: space-between; align-items: center; }
    .logo { height: 60px; object-fit: contain; }
    .company-info { text-align: right; }
    .company-name { font-size: 18px; font-weight: bold; color: #1a8f6f; }
    .company-tagline { font-size: 10px; color: #666; margin-top: 4px; }
    .date { text-align: right; margin-bottom: 15px; font-weight: bold; font-size: 10px; }
    .employee-details { margin-bottom: 15px; font-size: 10px; }
    .title { text-align: center; font-size: 14px; font-weight: bold; text-decoration: underline; margin: 15px 0; }
    .content { font-size: 10px; line-height: 1.4; }
    .content p { margin-bottom: 6px; }
    ul, ol { padding-left: 20px; margin: 6px 0; }
    li { margin-bottom: 2px; font-size: 10px; }
    .terms { margin-top: 10px; }
    .terms h6 { font-size: 11px; font-weight: bold; text-decoration: underline; margin: 10px 0 6px 0; }
    .signature { margin-top: 15px; padding-top: 10px; border-top: 1px solid #e0e0e0; }
    .acknowledgment { margin-top: 12px; display: flex; justify-content: space-between; font-size: 10px; }
    .footer { margin-top: 15px; text-align: center; font-size: 8px; color: #888; padding-top: 8px; border-top: 1px solid #e0e0e0; }
  </style>
</head>
<body>
<div class="page">
  <div class="header">
    ${logoHtml}
    <div class="company-info">
      <div class="company-name">${companyName}</div>
      <div class="company-tagline">${appName} | ${appVersion}</div>
    </div>
  </div>

  <div class="date"><b>${formatDate(letter.issue_date)}</b></div>

  <div class="employee-details">
    <b>${letter.employee_name || "Employee Name"}</b><br>
    ${letter.position || "Position"}<br>
    ${letter.location || "Location"}
  </div>

  <div class="title">APPOINTMENT LETTER</div>

  <div class="content">
    <p>Dear ${letter.employee_name?.split(' ')[0] || "Employee"},</p>
    <p>With reference to your application and subsequent interviews with us, we are pleased to offer you the role of <b>${letter.position || "Position"}</b> in our organization.</p>
    <p><b>Reporting:</b> You shall report to ${letter.sign_authority || letter.hr_name || "Manager"}.</p>
    <p><b>Date of Joining:</b> ${formatDate(letter.start_date) || "To be determined"}.</p>
    <p><b>Remuneration:</b> ${formatCurrency(letter.annual_ctc) || "To be determined"} annually (Monthly ${formatCurrency(letter.monthly_salary) || "To be determined"}).</p>
    <p><b>Job Responsibilities:</b> Duties assigned by management may change based on business needs.</p>
    <p><b>Working Hours:</b> As per company policy.</p>
    <p><b>Location:</b> ${letter.location || "Company location"}.</p>
    <p><b>Probation Period:</b> 3 months probation. Can be extended or reduced.</p>
    <p><b>Notice Period:</b> 7 days during probation period | 30 days after confirmation</p>
    <p><b>Termination:</b> Immediate termination for misconduct.</p>
    <p><b>Resignation:</b> One-month notice required.</p>

    <div class="terms">
      <h6>Terms & Conditions of Employment</h6>
      <ol>
        <li>Submit all required documents within 15 days of joining.</li>
        <li>Maintain discipline and integrity at all times.</li>
        <li>No other employment without written permission.</li>
        <li>False information will lead to termination.</li>
        <li>No gifts/commissions allowed from vendors/clients.</li>
        <li>Return all company assets on exit.</li>
        <li>Maintain confidentiality of company information.</li>
      </ol>
    </div>

    <p>All terms are confidential and governed by company rules and Indian law.</p>
    <p>This letter supersedes all previous agreements.</p>
    <p>Please sign and return this letter as acceptance.</p>
    <p>We welcome you and wish you success.</p>
  </div>

  <div class="signature">
    <b>Authorized Signatory</b><br>
    ${letter.hr_name || "HR Manager"}<br>
    HR Manager<br>
    ${companyName}
  </div>

  <div class="acknowledgment">
    <div><b>Name of Employee:</b> ${letter.employee_name || ""}</div>
    <div><b>Date:</b> ___________</div>
  </div>

  <div class="footer">This is a system-generated document | ${appName} | ${appVersion}</div>
</div>
</body>
</html>`;
};

/* ─────────────────────────────────────────────
   EXPERIENCE LETTER HTML (DOC003)
───────────────────────────────────────────── */
const getExperienceLetterHTML = (letter, org) => {
  const companyName = getAppName();
  const appName = getAppName();
  const appVersion = getAppVersion();
  const logoBase64 = getLogoBase64();
  const logoHtml = logoBase64 ? `<img src="${logoBase64}" alt="Company Logo" class="logo" />` : '<div style="width: 60px;"></div>';
  
  return `<!DOCTYPE html>
<html>
<head>
  <meta charset="UTF-8">
  <title>Experience Letter - ${letter.employee_name}</title>
  <style>
    * { margin: 0; padding: 0; box-sizing: border-box; }
    body { font-family: Arial, sans-serif; background: white; padding: 0; margin: 0; font-size: 11px; line-height: 1.4; }
    .page { width: 100%; padding: 0.4in 0.5in; }
    .header { margin-bottom: 15px; border-bottom: 1px solid #e0e0e0; padding-bottom: 10px; display: flex; justify-content: space-between; align-items: center; }
    .logo { height: 60px; object-fit: contain; }
    .company-info { text-align: right; }
    .company-name { font-size: 18px; font-weight: bold; color: #1a8f6f; }
    .company-tagline { font-size: 10px; color: #666; margin-top: 4px; }
    .title { text-align: center; font-size: 14px; font-weight: bold; text-decoration: underline; margin: 15px 0; }
    .content { font-size: 10px; line-height: 1.4; }
    .content p { margin-bottom: 6px; }
    ul { padding-left: 20px; margin: 6px 0; }
    li { margin-bottom: 2px; font-size: 10px; }
    .signature { margin-top: 15px; padding-top: 10px; border-top: 1px solid #e0e0e0; }
    .footer { margin-top: 15px; text-align: center; font-size: 8px; color: #888; padding-top: 8px; border-top: 1px solid #e0e0e0; }
    .company-address { margin-top: 15px; padding-top: 10px; border-top: 1px solid #e0e0e0; display: flex; justify-content: space-between; font-size: 9px; }
  </style>
</head>
<body>
<div class="page">
  <div class="header">
    ${logoHtml}
    <div class="company-info">
      <div class="company-name">${companyName}</div>
      <div class="company-tagline">${appName} | ${appVersion}</div>
    </div>
  </div>

  <div class="title">EXPERIENCE LETTER</div>

  <div class="content">
    <p>To Whomsoever It May Concern,</p>
    <p>This is to certify that <b>${letter.employee_name || "Employee Name"}</b>, Employee ID <b>${letter.employee_id || "EMP001"}</b>, was employed with <b>${companyName}</b> as a <b>${letter.position || "Position"}</b> in the <b>${letter.department || "Department"}</b> from <b>${formatDate(letter.start_date) || "Start date"}</b> to <b>${formatDate(letter.relieving_date) || "End date"}</b>.</p>
    <p>During the tenure, the employee was responsible for:</p>
    <ul><li>Monitoring daily operations</li><li>Ensuring quality and process standards</li><li>Handling workflow execution</li><li>Coordinating with team and supervisors</li><li>Maintaining reports and operational records</li></ul>
    <p>During the employment period, <b>${letter.employee_name?.split(' ')[0] || "Employee"}</b> demonstrated sincerity, dedication, punctuality, and professionalism. Their performance was found to be <b>${letter.performance || "Excellent"}</b>.</p>
    <p>We wish them all the best in their future endeavors.</p>
    <br />
    <p>For <b>${companyName}</b>,</p>
    <p><b>${letter.sign_authority || letter.hr_name || "HR Manager"}</b></p>
    <br />
    <p>Date: ${formatDate(letter.issue_date)}</p>
    <p>Place: ${letter.place || "Hyderabad"}</p>
  </div>

  <div class="company-address">
    <div><b>${companyName.toUpperCase()}</b><br>${org.address || "1-8-303/48/15, 3rd Floor, KRK House, PG Road, Begumpet, Hyderabad Telangana 500 003"}</div>
    <div>${org.email ? `Email: ${org.email}<br>` : ''}${org.phone ? `Phone: ${org.phone}` : ''}</div>
  </div>

  <div class="footer">This is a system-generated document | ${appName} | ${appVersion}</div>
</div>
</body>
</html>`;
};

/* ─────────────────────────────────────────────
   RELIEVING LETTER HTML (DOC004)
───────────────────────────────────────────── */
const getRelievingLetterHTML = (letter, org) => {
  const companyName = getAppName();
  const appName = getAppName();
  const appVersion = getAppVersion();
  const logoBase64 = getLogoBase64();
  const logoHtml = logoBase64 ? `<img src="${logoBase64}" alt="Company Logo" class="logo" />` : '<div style="width: 60px;"></div>';
  
  return `<!DOCTYPE html>
<html>
<head>
  <meta charset="UTF-8">
  <title>Relieving Letter - ${letter.employee_name}</title>
  <style>
    * { margin: 0; padding: 0; box-sizing: border-box; }
    body { font-family: Arial, sans-serif; background: white; padding: 0; margin: 0; font-size: 11px; line-height: 1.4; }
    .page { width: 100%; padding: 0.4in 0.5in; }
    .header { margin-bottom: 15px; border-bottom: 1px solid #e0e0e0; padding-bottom: 10px; display: flex; justify-content: space-between; align-items: center; }
    .logo { height: 60px; object-fit: contain; }
    .company-info { text-align: right; }
    .company-name { font-size: 18px; font-weight: bold; color: #1a8f6f; }
    .company-tagline { font-size: 10px; color: #666; margin-top: 4px; }
    .date { text-align: right; margin-bottom: 15px; font-weight: bold; font-size: 10px; }
    .employee-details { margin-bottom: 15px; font-size: 10px; }
    .title { text-align: center; font-size: 14px; font-weight: bold; text-decoration: underline; margin: 15px 0; }
    .content { font-size: 10px; line-height: 1.4; }
    .content p { margin-bottom: 6px; }
    .signature { margin-top: 15px; padding-top: 10px; border-top: 1px solid #e0e0e0; }
    .acknowledgment { margin-top: 15px; }
    .footer { margin-top: 15px; text-align: center; font-size: 8px; color: #888; padding-top: 8px; border-top: 1px solid #e0e0e0; }
  </style>
</head>
<body>
<div class="page">
  <div class="header">
    ${logoHtml}
    <div class="company-info">
      <div class="company-name">${companyName}</div>
      <div class="company-tagline">${appName} | ${appVersion}</div>
    </div>
  </div>

  <div class="date"><b>${formatDate(letter.relieving_date) || formatDate(letter.issue_date)}</b></div>

  <div class="employee-details">
    <b>${letter.employee_name || "Employee Name"}</b><br>
    ${letter.position || "Position"}<br>
    ${letter.location || "Location"}
  </div>

  <div class="title">RELIEVING LETTER</div>

  <div class="content">
    <p>Dear ${letter.employee_name?.split(' ')[0] || "Employee"},</p>
    <p>This is to formally acknowledge that you have resigned from your position as <b>${letter.position || "Position"}</b> at <b>${companyName}</b>.</p>
    <p>Your resignation has been accepted, and you have been relieved from your duties effective <b>${formatDate(letter.relieving_date) || "To be determined"}</b>.</p>
    <p>During your tenure from <b>${formatDate(letter.start_date) || "Start date"}</b> to <b>${formatDate(letter.relieving_date) || "End date"}</b>, you have worked with us in the capacity of <b>${letter.position || "Position"}</b>.</p>
    <p>We confirm that you have completed all your assigned responsibilities and handed over your duties and company assets properly.</p>
    <p>We appreciate your contributions during your tenure and wish you success in your future endeavors.</p>
  </div>

  <div class="signature">
    Sincerely,<br>
    <b>${letter.hr_name || "HR Manager"}</b><br>
    ${companyName}
  </div>

  <div class="acknowledgment">
    <p><b>Acknowledgement by Employee</b></p>
    <p>I hereby acknowledge the receipt of this relieving letter.</p>
    <div style="display: flex; justify-content: space-between; margin-top: 10px;">
      <div><b>Name:</b> ${letter.employee_name || ""}</div>
      <div><b>Date:</b> ___________</div>
    </div>
  </div>

  <div class="footer">This is a system-generated document | ${appName} | ${appVersion}</div>
</div>
</body>
</html>`;
};

/* ─────────────────────────────────────────────
   WARNING LETTER HTML (DOC005)
───────────────────────────────────────────── */
const getWarningLetterHTML = (letter, org) => {
  const companyName = getAppName();
  const appName = getAppName();
  const appVersion = getAppVersion();
  const logoBase64 = getLogoBase64();
  const logoHtml = logoBase64 ? `<img src="${logoBase64}" alt="Company Logo" class="logo" />` : '<div style="width: 60px;"></div>';
  
  return `<!DOCTYPE html>
<html>
<head>
  <meta charset="UTF-8">
  <title>Warning Letter - ${letter.employee_name}</title>
  <style>
    * { margin: 0; padding: 0; box-sizing: border-box; }
    body { font-family: Arial, sans-serif; background: white; padding: 0; margin: 0; font-size: 11px; line-height: 1.4; }
    .page { width: 100%; padding: 0.4in 0.5in; }
    .header { margin-bottom: 15px; border-bottom: 1px solid #e0e0e0; padding-bottom: 10px; display: flex; justify-content: space-between; align-items: center; }
    .logo { height: 60px; object-fit: contain; }
    .company-info { text-align: right; }
    .company-name { font-size: 18px; font-weight: bold; color: #c0392b; }
    .company-tagline { font-size: 10px; color: #666; margin-top: 4px; }
    .date { text-align: right; margin-bottom: 15px; font-weight: bold; font-size: 10px; }
    .employee-details { margin-bottom: 15px; font-size: 10px; }
    .title { text-align: center; font-size: 14px; font-weight: bold; text-decoration: underline; color: #c0392b; margin: 15px 0; }
    .content { font-size: 10px; line-height: 1.4; }
    .content p { margin-bottom: 6px; }
    .signature { margin-top: 15px; padding-top: 10px; border-top: 1px solid #e0e0e0; }
    .acknowledgment { margin-top: 15px; }
    .footer { margin-top: 15px; text-align: center; font-size: 8px; color: #888; padding-top: 8px; border-top: 1px solid #e0e0e0; }
  </style>
</head>
<body>
<div class="page">
  <div class="header">
    ${logoHtml}
    <div class="company-info">
      <div class="company-name">${companyName}</div>
      <div class="company-tagline">${appName} | ${appVersion}</div>
    </div>
  </div>

  <div class="date"><b>${formatDate(letter.issue_date)}</b></div>

  <div class="employee-details">
    <b>${letter.employee_name || "Employee Name"}</b><br>
    ${letter.position || "Position"}<br>
    ${letter.location || "Location"}
  </div>

  <div class="title">WARNING LETTER</div>

  <div class="content">
    <p>Dear ${letter.employee_name?.split(' ')[0] || "Employee"},</p>
    <p>This letter is to formally notify you regarding your conduct/performance observed during your tenure at <b>${companyName}</b>.</p>
    <p>It has been observed that you have been involved in <b>${letter.warning_issue || "irregular attendance / not meeting expected performance standards"}</b>, which is not in line with the company policies and expectations.</p>
    <p>Despite previous discussions and verbal warnings, there has not been sufficient improvement in your behavior/performance.</p>
    <p>You are hereby advised to take this matter seriously and show immediate improvement. Failure to do so may result in further disciplinary action, which may include termination of your employment.</p>
    <p>We expect you to maintain professionalism and adhere strictly to company policies moving forward.</p>
  </div>

  <div class="signature">
    Sincerely,<br>
    <b>${letter.hr_name || "HR Manager"}</b><br>
    ${companyName}
  </div>

  <div class="acknowledgment">
    <p><b>Acknowledgement by Employee</b></p>
    <p>I acknowledge receipt of this warning letter and understand the contents.</p>
    <div style="display: flex; justify-content: space-between; margin-top: 10px;">
      <div><b>Name:</b> ${letter.employee_name || ""}</div>
      <div><b>Date:</b> ___________</div>
    </div>
  </div>

  <div class="footer">This is a system-generated document | ${appName} | ${appVersion}</div>
</div>
</body>
</html>`;
};

/* ─────────────────────────────────────────────
   SALARY CERTIFICATE HTML (DOC006)
───────────────────────────────────────────── */
const getSalaryCertificateHTML = (letter, org) => {
  const companyName = getAppName();
  const appName = getAppName();
  const appVersion = getAppVersion();
  const logoBase64 = getLogoBase64();
  const logoHtml = logoBase64 ? `<img src="${logoBase64}" alt="Company Logo" class="logo" />` : '<div style="width: 60px;"></div>';

  return `<!DOCTYPE html>
<html>
<head>
  <meta charset="UTF-8">
  <title>Salary Certificate - ${letter.employee_name}</title>
  <style>
    * { margin: 0; padding: 0; box-sizing: border-box; }
    body { font-family: Arial, sans-serif; background: white; padding: 0; margin: 0; font-size: 11px; line-height: 1.4; }
    .page { width: 100%; padding: 0.4in 0.5in; }
    .header { margin-bottom: 15px; border-bottom: 1px solid #e0e0e0; padding-bottom: 10px; display: flex; justify-content: space-between; align-items: center; }
    .logo { height: 60px; object-fit: contain; }
    .company-info { text-align: right; }
    .company-name { font-size: 18px; font-weight: bold; color: #1a8f6f; }
    .company-tagline { font-size: 10px; color: #666; margin-top: 4px; }
    .date { text-align: right; margin-bottom: 15px; font-weight: bold; font-size: 10px; }
    .employee-details { margin-bottom: 15px; font-size: 10px; }
    .title { text-align: center; font-size: 14px; font-weight: bold; text-decoration: underline; margin: 15px 0; }
    .content { font-size: 10px; line-height: 1.4; }
    .content p { margin-bottom: 6px; }
    table { width: 100%; border-collapse: collapse; margin: 10px 0; }
    th, td { border: 1px solid #dee2e6; padding: 8px; text-align: center; }
    th { background-color: #f8f9fa; font-weight: bold; }
    .signature { margin-top: 15px; padding-top: 10px; border-top: 1px solid #e0e0e0; display: flex; justify-content: space-between; }
    .footer { margin-top: 15px; text-align: center; font-size: 8px; color: #888; padding-top: 8px; border-top: 1px solid #e0e0e0; }
  </style>
</head>
<body>
<div class="page">
  <div class="header">
    ${logoHtml}
    <div class="company-info">
      <div class="company-name">${companyName}</div>
      <div class="company-tagline">${appName} | ${appVersion}</div>
    </div>
  </div>

  <div class="date"><b>Date: ${formatDate(letter.issue_date)}</b></div>

  <div class="title">SALARY CERTIFICATE</div>

  <div class="content">
    <p>This is to certify that <b>${letter.employee_name || "Employee Name"}</b> is currently employed with <b>${companyName}</b> as a <b>${letter.position || "Position"}</b>.</p>

    <p>As per the company records, the employee is drawing the following salary:</p>

    <table>
      <thead>
        <tr><th>Particulars</th><th>Amount (INR)</th></tr>
      </thead>
      <tbody>
        <tr><td>Monthly Salary</td><td>${formatCurrency(letter.monthly_salary)}</td>
        <tr><td>Annual Salary (CTC)</td><td>${formatCurrency(letter.annual_ctc)}</td>
      </tbody>
    </table>

    <p>This certificate is issued upon the request of the employee for official and personal purposes.</p>
  </div>

  <div class="signature">
    <div>
      For <b>${companyName}</b><br><br>
      <b>${letter.hr_name || "HR Manager"}</b><br>
      Authorized Signatory
    </div>
    <div style="text-align: right;">
      <b>Employee Signature</b><br><br>
      ____________________
    </div>
  </div>

  <div class="footer">This is a system-generated document | ${appName} | ${appVersion}</div>
</div>
</body>
</html>`;
};

/* ─────────────────────────────────────────────
   DATABASE QUERY FUNCTIONS
───────────────────────────────────────────── */
const getHrDocuments = async (req, res) => {
  try {
    const org_id = req.session.user.orgId;
    const result = await pool.query(
      `SELECT id, doc_code, name, type, route_key, created_at
       FROM hr_documents
       WHERE is_deleted = false AND org_id = $1
       ORDER BY doc_code ASC`,
      [org_id]
    );
    return res.json({ success: true, data: result.rows });
  } catch (err) {
    console.error("GET HR DOCUMENTS ERROR:", err);
    return res.status(500).json({ success: false, message: err.message });
  }
};

const getGeneratedLetters = async (req, res) => {
  try {
    const org_id = req.session.user.orgId;
    const { page = 1, limit = 10, doc_code, search } = req.query;
    const offset = (parseInt(page) - 1) * parseInt(limit);
    
    let query = `
      SELECT gl.*, hd.name AS doc_name, hd.doc_code, hd.route_key
      FROM generated_letters gl
      JOIN hr_documents hd ON hd.id = gl.doc_id
      WHERE gl.is_deleted = false AND gl.org_id = $1
    `;
    let countQuery = `
      SELECT COUNT(*) 
      FROM generated_letters gl
      JOIN hr_documents hd ON hd.id = gl.doc_id
      WHERE gl.is_deleted = false AND gl.org_id = $1
    `;
    let params = [org_id];
    let countParams = [org_id];
    let paramIndex = 2;
    
    if (doc_code && doc_code !== 'all') {
      query += ` AND hd.doc_code = $${paramIndex}`;
      countQuery += ` AND hd.doc_code = $${paramIndex}`;
      params.push(doc_code);
      countParams.push(doc_code);
      paramIndex++;
    }
    
    if (search) {
      query += ` AND (gl.employee_name ILIKE $${paramIndex} OR hd.name ILIKE $${paramIndex})`;
      countQuery += ` AND (gl.employee_name ILIKE $${paramIndex} OR hd.name ILIKE $${paramIndex})`;
      params.push(`%${search}%`);
      countParams.push(`%${search}%`);
      paramIndex++;
    }
    
    query += ` ORDER BY gl.created_at DESC LIMIT $${paramIndex} OFFSET $${paramIndex + 1}`;
    params.push(parseInt(limit), offset);
    
    const [result, countResult] = await Promise.all([
      pool.query(query, params),
      pool.query(countQuery, countParams)
    ]);
    
    return res.json({ 
      success: true, 
      data: result.rows, 
      totalRecords: parseInt(countResult.rows[0].count)
    });
  } catch (err) {
    console.error("GET GENERATED LETTERS ERROR:", err);
    return res.status(500).json({ success: false, message: err.message });
  }
};

const getGeneratedLetterById = async (req, res) => {
  try {
    const { id } = req.params;
    const org_id = req.session.user.orgId;
    const result = await pool.query(
      `SELECT gl.*, hd.name AS doc_name, hd.doc_code, hd.route_key
       FROM generated_letters gl
       JOIN hr_documents hd ON hd.id = gl.doc_id
       WHERE gl.id = $1 AND gl.org_id = $2 AND gl.is_deleted = false`,
      [id, org_id]
    );
    if (!result.rows.length) {
      return res.status(404).json({ success: false, message: "Letter not found" });
    }
    return res.json({ success: true, data: result.rows[0] });
  } catch (err) {
    console.error("GET GENERATED LETTER BY ID ERROR:", err);
    return res.status(500).json({ success: false, message: err.message });
  }
};

const generateLetter = async (req, res) => {
  try {
    const org_id = req.session.user.orgId;
    const userId = req.session.user.id;

    const org = await getOrganizationDetails(org_id);
    const orgName = getOrgName();
    const appName = getAppName();

    const {
      doc_code,
      employee_db_id,
      issue_date,
      relieving_date,
      start_date,
      warning_issue,
      performance,
      place,
      sign_authority,
      hr_name,
      company,
    } = req.body;

    if (!doc_code || !employee_db_id) {
      return res.status(400).json({
        success: false,
        message: "doc_code and employee_db_id are required",
      });
    }

    const docResult = await pool.query(
      `SELECT id
       FROM hr_documents
       WHERE doc_code = $1
         AND is_deleted = false
         AND org_id = $2`,
      [doc_code, org_id]
    );

    if (!docResult.rows.length) {
      return res.status(404).json({
        success: false,
        message: `Document type '${doc_code}' not found`,
      });
    }

    const doc_id = docResult.rows[0].id;

    // ✅ FIXED: Use department_id and designation_id with proper joins
    const empResult = await pool.query(
      `SELECT
          e.id,
          e.employee_id,
          e.first_name,
          e.last_name,
          e.salary,
          COALESCE(des.designation_name, 'Unknown') AS position,
          COALESCE(d.dept_name, 'Unknown') AS department,
          e.work_location,
          e.joining_date
       FROM employees e
       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
       WHERE e.id = $1
         AND e.org_id = $2
         AND e.is_deleted = false`,
      [employee_db_id, org_id]
    );

    if (!empResult.rows.length) {
      return res.status(404).json({
        success: false,
        message: "Employee not found",
      });
    }

    const emp = empResult.rows[0];
    const monthly = parseFloat(emp.salary) || 0;
    const annual = monthly * 12;

    const insertResult = await pool.query(
      `INSERT INTO generated_letters (
          doc_id,
          employee_db_id,
          employee_id,
          employee_name,
          position,
          department,
          location,
          monthly_salary,
          annual_ctc,
          joining_date,
          company,
          hr_name,
          sign_authority,
          place,
          issue_date,
          start_date,
          relieving_date,
          warning_issue,
          performance,
          generated_by,
          org_id,
          created_by
       )
       VALUES (
          $1,$2,$3,$4,$5,$6,$7,$8,$9,$10,
          $11,$12,$13,$14,$15,$16,$17,$18,
          $19,$20,$21,$22
       )
       RETURNING *`,
      [
        doc_id,
        emp.id,
        emp.employee_id,
        `${emp.first_name} ${emp.last_name}`,
        emp.position || null,
        emp.department || null,
        emp.work_location || null,
        monthly,
        annual,
        emp.joining_date || null,
        company || appName,
        hr_name || "HR Manager",
        sign_authority || null,
        place || "Hyderabad",
        issue_date || new Date().toISOString().split("T")[0],
        start_date || emp.joining_date || null,
        relieving_date || null,
        warning_issue || null,
        performance || null,
        req.session.user.name || null,
        org_id,
        userId,
      ]
    );

    return res.status(201).json({
      success: true,
      message: "Letter generated successfully",
      data: insertResult.rows[0],
    });
  } catch (err) {
    console.error("GENERATE LETTER ERROR:", err);
    return res.status(500).json({
      success: false,
      message: err.message,
    });
  }
};
const getLetterData = async (req, res) => {
  try {
    const { id } = req.params;
    const org_id = req.session.user.orgId;
    const result = await pool.query(
      `SELECT gl.*, hd.name AS doc_name, hd.doc_code, hd.route_key
       FROM generated_letters gl
       JOIN hr_documents hd ON hd.id = gl.doc_id
       WHERE gl.id = $1 AND gl.org_id = $2 AND gl.is_deleted = false`,
      [id, org_id]
    );
    if (!result.rows.length) {
      return res.status(404).json({ success: false, message: "Not found" });
    }
    const r = result.rows[0];
    const letterData = {
      name: r.employee_name,
      empId: r.employee_id,
      position: r.position,
      department: r.department,
      location: r.location,
      salary: formatCurrency(r.annual_ctc),
      monthlySalary: formatCurrency(r.monthly_salary),
      company: r.company,
      hrName: r.hr_name,
      signAuthority: r.sign_authority,
      place: r.place,
      date: formatDate(r.issue_date),
      startDate: formatDate(r.start_date),
      relievingDate: formatDate(r.relieving_date),
      joiningDate: formatDate(r.joining_date),
      issue: r.warning_issue,
      performance: r.performance,
      issueDate: formatDate(r.issue_date),
      docName: r.doc_name,
      docCode: r.doc_code,
      routeKey: r.route_key,
    };
    return res.json({ success: true, data: letterData });
  } catch (err) {
    console.error("GET LETTER DATA ERROR:", err);
    return res.status(500).json({ success: false, message: err.message });
  }
};

const deleteGeneratedLetter = async (req, res) => {
  try {
    const { id } = req.params;
    const org_id = req.session.user.orgId;
    const userId = req.session.user.id;
    const result = await pool.query(
      `UPDATE generated_letters SET is_deleted = true, updated_by = $1, updated_at = NOW()
       WHERE id = $2 AND org_id = $3 AND is_deleted = false RETURNING id`,
      [userId, id, org_id]
    );
    if (!result.rows.length) {
      return res.status(404).json({ success: false, message: "Letter not found" });
    }
    return res.json({ success: true, message: "Letter deleted successfully" });
  } catch (err) {
    console.error("DELETE GENERATED LETTER ERROR:", err);
    return res.status(500).json({ success: false, message: err.message });
  }
};

const getLetterStats = async (req, res) => {
  try {
    const org_id = req.session.user.orgId;
    const result = await pool.query(
      `SELECT COUNT(*) AS total_generated,
              COUNT(*) FILTER (WHERE hd.doc_code = 'DOC001') AS offer_letters,
              COUNT(*) FILTER (WHERE hd.doc_code = 'DOC002') AS appointment_letters,
              COUNT(*) FILTER (WHERE hd.doc_code = 'DOC003') AS experience_letters,
              COUNT(*) FILTER (WHERE hd.doc_code = 'DOC004') AS relieving_letters,
              COUNT(*) FILTER (WHERE hd.doc_code = 'DOC005') AS warning_letters,
              COUNT(*) FILTER (WHERE hd.doc_code = 'DOC006') AS salary_certificates,
              COUNT(*) FILTER (WHERE gl.created_at >= NOW() - INTERVAL '30 days') AS this_month
       FROM generated_letters gl
       JOIN hr_documents hd ON hd.id = gl.doc_id
       WHERE gl.is_deleted = false AND gl.org_id = $1`,
      [org_id]
    );
    return res.json({ success: true, data: result.rows[0] });
  } catch (err) {
    console.error("GET LETTER STATS ERROR:", err);
    return res.status(500).json({ success: false, message: err.message });
  }
};

/* ─────────────────────────────────────────────
   DOWNLOAD LETTER AS PDF
───────────────────────────────────────────── */
const downloadLetterPDF = async (req, res) => {
  let browser = null;
  
  try {
    const { id } = req.params;
    const org_id = req.session.user?.orgId;

    if (!org_id) {
      return res.status(401).json({ success: false, message: "Organization not found" });
    }

    const result = await pool.query(
      `SELECT gl.*, hd.name AS doc_name, hd.doc_code, hd.route_key
       FROM generated_letters gl
       JOIN hr_documents hd ON hd.id = gl.doc_id
       WHERE gl.id = $1 AND gl.org_id = $2 AND gl.is_deleted = false`,
      [id, org_id]
    );

    if (!result.rows.length) {
      return res.status(404).json({ success: false, message: "Letter not found" });
    }

    const letter = result.rows[0];
    const org = await getOrganizationDetails(org_id);
    
    let html;
    switch (letter.doc_code) {
      case 'DOC001':
        html = getOfferLetterHTML(letter, org);
        break;
      case 'DOC002':
        html = getAppointmentLetterHTML(letter, org);
        break;
      case 'DOC003':
        html = getExperienceLetterHTML(letter, org);
        break;
      case 'DOC004':
        html = getRelievingLetterHTML(letter, org);
        break;
      case 'DOC005':
        html = getWarningLetterHTML(letter, org);
        break;
      case 'DOC006':
        html = getSalaryCertificateHTML(letter, org);
        break;
      default:
        html = getAppointmentLetterHTML(letter, org);
    }
    
    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();
    page.setDefaultTimeout(30000);
    
    await page.setRequestInterception(true);
    page.on('request', (request) => {
      const resourceType = request.resourceType();
      if (resourceType === 'font' || resourceType === 'media') {
        request.abort();
      } else {
        request.continue();
      }
    });
    
    await page.setViewport({ width: 1024, height: 800 });
    await page.setContent(html, { waitUntil: 'domcontentloaded', timeout: 30000 });
    await page.evaluate(() => new Promise(resolve => setTimeout(resolve, 500)));

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

    const filename = `${letter.doc_code}_${letter.employee_name.replace(/\s/g, '_')}_${new Date().toISOString().split('T')[0]}.pdf`;
    
    res.set({
      "Content-Type": "application/pdf",
      "Content-Disposition": `attachment; filename="${filename}"`,
      "Content-Length": pdf.length,
    });
    
    res.send(pdf);

  } catch (error) {
    console.error("PDF Generation Error:", error);
    res.status(500).json({ success: false, message: "Failed to generate PDF: " + error.message });
  } finally {
    if (browser) {
      await browser.close();
    }
  }
};

module.exports = {
  getHrDocuments,
  getGeneratedLetters,
  getGeneratedLetterById,
  generateLetter,
  getLetterData,
  deleteGeneratedLetter,
  getLetterStats,
  downloadLetterPDF,
};