const nodemailer = require('nodemailer');
const axios = require('axios');
const fs = require('fs');
const path = require('path');
const moment = require('moment');
const DesignEmailLog = require('../models/designEmailLog');
const sequelize = require('../config/dbconnection');
const Sequelize = require('sequelize');
const { Op } = Sequelize;

const { invoiceTable, invoiceTableFromUI, invoiceTableFromUIForQuotation, invoiceTableFromUIForPurchaseOrder } = require('../constants/emailTemplates');
const Settings = require('../models/settings');
const Requests = require('../models/request');
const { empty } = require('../Helpers/Common');
const { FACILITY_ID } = require('../config/SplitManagementDb');

// Define UNITS similar to mail.js
const UNITS = [
  { id: 1, name: 'Square feet', short_name: 'Sq ft', unit_id: 1 },
  { id: 2, name: 'Linear feet', short_name: 'Lin ft', unit_id: 1 },
  { id: 3, name: 'Square inches', short_name: 'Sq in', unit_id: 1 },
  { id: 16, name: 'Yards', short_name: 'yd', unit_id: 1 },
  { id: 4, name: 'Unit', short_name: 'Unit', unit_id: 2 },
  { id: 5, name: 'Gallons', short_name: 'Gal', unit_id: 3 },
  { id: 6, name: 'Quarts', short_name: 'Qt', unit_id: 3 },
  { id: 7, name: 'Milliliter', short_name: 'ml', unit_id: 3 },
  { id: 8, name: 'Liter', short_name: 'L', unit_id: 3 },
  { id: 9, name: 'Pint', short_name: 'pt', unit_id: 3 },
  { id: 10, name: 'Half Pint', short_name: 'half-pt', unit_id: 3 },
  { id: 11, name: 'Ounce', short_name: 'oz', unit_id: 3 },
  { id: 12, name: 'Kilogram', short_name: 'kg', unit_id: 4 },
  { id: 13, name: 'Gram', short_name: 'g', unit_id: 4 },
  { id: 14, name: 'Milligram', short_name: 'mg', unit_id: 4 },
  { id: 15, name: 'Each', short_name: 'EA', unit_id: 2 },
];

const transporter = nodemailer.createTransport({
  host: 'mail.signwisesolutions.com',
  port: 465,
  secure: true, // true for 465, false for other ports
  auth: {
    user: 'noreply@signwisesolutions.com',
    pass: 'signwise@2024',
  },
  connectionTimeout: 20000, // 20 seconds
  greetingTimeout: 20000, // 20 seconds
  socketTimeout: 20000, // 20 seconds
  tls: {
    rejectUnauthorized: false // Allow self-signed certificates if needed
  }
});

async function downloadFileFromUrl(fileUrl, savePath) {
  try {
    const response = await axios.get(fileUrl, { 
      responseType: 'arraybuffer',
      timeout: 30000
    });
    fs.writeFileSync(savePath, response.data);
    return savePath;
  } catch (error) {
    console.error('Error downloading file:', error.message);
    return null;
  }
}

function getFileExtension(file) {
  if (file.extension) {
    return file.extension;
  }
  // Check both 'name' and 'url' fields (split estimates use 'url', design orders use 'name')
  const fileUrl = file.url || file.name;
  if (fileUrl) {
    const urlParts = fileUrl.split('/');
    const fileName = urlParts[urlParts.length - 1].split('?')[0];
    const ext = fileName.split('.').pop()?.toLowerCase();
    return ext || 'jpg';
  }
  if (file.file_type) {
    if (file.file_type.includes('pdf')) return 'pdf';
    if (file.file_type.includes('image')) return 'jpg';
  }
  return 'jpg';
}

function getFileName(file) {
  // Check both 'name' and 'url' fields (split estimates use 'url', design orders use 'name')
  const fileUrl = file.url || file.name;
  if (fileUrl) {
    const urlParts = fileUrl.split('/');
    return urlParts[urlParts.length - 1].split('?')[0];
  }
  return `design_${Date.now()}.${getFileExtension(file)}`;
}

function getInvoiceFileExtension(file) {
  if (file.extension) {
    return file.extension;
  }
																							 
  if (file.name) {
				
    const urlParts = file.name.split('/');
    const fileName = urlParts[urlParts.length - 1].split('?')[0];
    const ext = fileName.split('.').pop()?.toLowerCase();
    return ext || 'jpg';
  }
  if (file.file_type) {
    if (file.file_type.includes('pdf')) return 'pdf';
    if (file.file_type.includes('image')) return 'jpg';
  }
  return 'jpg';
}

function getInvoiceFileName(file) {
																							 
  if (file.name) {
				
    const urlParts = file.name.split('/');
    return urlParts[urlParts.length - 1].split('?')[0];
  }
  return `design_${Date.now()}.${getInvoiceFileExtension(file)}`;
}

function getContentType(extension) {
  const ext = extension.toLowerCase();
  if (ext === 'pdf') return 'application/pdf';
  if (['jpg', 'jpeg'].includes(ext)) return 'image/jpeg';
  if (ext === 'png') return 'image/png';
  if (ext === 'gif') return 'image/gif';
  return 'application/octet-stream';
}

function escapeHtml(text) {
  if (!text) return '';
  return text
    .replace(/&/g, '&amp;')
    .replace(/</g, '&lt;')
    .replace(/>/g, '&gt;')
    .replace(/"/g, '&quot;')
    .replace(/'/g, '&#039;');
}

const sendDesignsToEmail = async (req, reply) => {
  try {
    const {
      design_order_id,
      selectedDesignIds,
      quotation_id,
      request_id,
      designs,
      userId,
      userType,
      recipientEmail,
      ccEmails = [],
      subject,
      message = '',
      isFromSplitEstimate = false,
    } = req.body;

    if (!design_order_id && !quotation_id && !request_id) {
      return reply.send({
        status: 0,
        message: 'Design order ID, quotation ID or request ID is required'
      });
    }
    if (!recipientEmail || !recipientEmail.trim()) {
      return reply.send({ status: 0, message: 'Recipient email is required' });
    }
    if (!subject || !subject.trim()) {
      return reply.send({ status: 0, message: 'Subject is required' });
    }
    if (!designs || !Array.isArray(designs) || designs.length === 0) {
      return reply.send({ status: 0, message: 'No designs selected' });
    }

    const timestamp = moment().unix();
    const tempDir = path.join(__dirname, '../uploads/temp');
    
    if (!fs.existsSync(tempDir)) {
      fs.mkdirSync(tempDir, { recursive: true });
    }

    const attachments = [];
    const downloadedFiles = [];

    for (let i = 0; i < designs.length; i++) {
      const design = designs[i];
      // For split estimates, URL is in 'url' field; for design orders, it's in 'name' field
      const fileUrl = design.url || design.name;
      if (!fileUrl) {
        console.warn(`Skipping design ${i + 1}: No URL found`);
        continue;
      }

      const fileExtension = getFileExtension(design);
      const fileName = getFileName(design);
      const tempFilePath = path.join(tempDir, `${timestamp}_${i}_${fileName}`);

      try {
        const downloadedPath = await downloadFileFromUrl(fileUrl, tempFilePath);
        if (downloadedPath) {
          attachments.push({
            filename: fileName,
            path: downloadedPath,
            contentType: getContentType(fileExtension),
          });
          downloadedFiles.push(downloadedPath);
        } else {
          console.error(`Failed to download file: ${fileUrl}`);
        }
      } catch (error) {
        console.error(`Error processing file ${i + 1}:`, error.message);
      }
    }

    if (attachments.length === 0) {
      return reply.send({ 
        status: 0, 
        message: 'Failed to download design files. Please check the file URLs.' 
      });
    }

    // Fetch design order or split estimate name
    let designOrderName = isFromSplitEstimate ? 'Split Estimate' : 'Design Order';
    try {
      if (isFromSplitEstimate) {
        // Fetch from split_estimates table
        const splitEstimateResult = await sequelize.query(
          `SELECT name FROM split_estimates WHERE id = :design_order_id AND status != 0`,
          {
            replacements: { design_order_id: design_order_id },
            type: Sequelize.QueryTypes.SELECT
          }
        );
        if (splitEstimateResult && splitEstimateResult.length > 0 && splitEstimateResult[0].name) {
          designOrderName = splitEstimateResult[0].name;
        }
      } else {
        // Fetch from design_orders table
        const designOrderResult = await sequelize.query(
          `SELECT name FROM design_orders WHERE id = :design_order_id AND status != 0`,
          {
            replacements: { design_order_id: design_order_id },
            type: Sequelize.QueryTypes.SELECT
          }
        );
        if (designOrderResult && designOrderResult.length > 0 && designOrderResult[0].name) {
          designOrderName = designOrderResult[0].name;
        }
      }
    } catch (error) {
      console.error('Error fetching order name:', error);
      // Continue with default name if fetch fails
    }

    const escapedMessage = escapeHtml(message).replace(/\n/g, '<br>');
    const escapedOrderName = escapeHtml(designOrderName);
    const orderTypeLabel = isFromSplitEstimate ? 'Split Estimate' : 'Design Order';
    
    const emailHtml = `
      <!DOCTYPE html>
      <html>
      <head>
        <meta charset="utf-8">
        <style>
          body { font-family: Arial, sans-serif; line-height: 1.6; color: #333; }
          .container { max-width: 600px; margin: 0 auto; padding: 20px; }
          .message { margin-top: 20px; white-space: pre-wrap; }
        </style>
      </head>
      <body>
        <div class="container">
          <h2>${orderTypeLabel} - ${escapedOrderName}</h2>
          ${escapedMessage ? `<div class="message">${escapedMessage}</div>` : ''}
          <p style="margin-top: 20px; color: #666;">
            This email contains ${attachments.length} design file(s) as attachments.
          </p>
        </div>
      </body>
      </html>
    `;

    // Validate and format recipient email
    const recipientEmailFormatted = recipientEmail.trim();
    
    // Filter and validate CC emails
    let ccEmailsFormatted = [];
    if (Array.isArray(ccEmails) && ccEmails.length > 0) {
      ccEmailsFormatted = ccEmails
        .map(email => typeof email === 'string' ? email.trim() : '')
        .filter(email => email && email.length > 0);
    }

    // Build mailOptions following the same pattern as request controller
    const mailOptions = {
      from: '"SignWise Solutions" <noreply@signwisesolutions.com>',
      to: recipientEmailFormatted,
      subject: subject.trim(),
      text: subject.trim(),
      html: emailHtml,
      attachments: attachments,
    };

    // Only add CC if there are CC emails (don't include undefined)
    if (ccEmailsFormatted.length > 0) {
      mailOptions.cc = ccEmailsFormatted;
    }

    console.log('📧 Preparing to send email:', {
      to: recipientEmailFormatted,
      cc: ccEmailsFormatted.length > 0 ? ccEmailsFormatted : 'none',
      subject: subject.trim(),
      attachmentsCount: attachments.length
    });

    let emailStatus = 1;
    let errorMessage = null;

    // Send email following the same pattern as request controller
    try {
      // Send email directly without verify to avoid extra connection timeout
      const info = await transporter.sendMail(mailOptions);
      console.log(`✅ Email sent successfully to: ${recipientEmailFormatted}`);
      console.log('📧 Email info:', {
        messageId: info.messageId,
        response: info.response,
        accepted: info.accepted,
        rejected: info.rejected
      });
      if (ccEmailsFormatted.length > 0) {
        console.log(`✅ CC emails: ${ccEmailsFormatted.join(', ')}`);
      }
    } catch (error) {
      console.error(`❌ Error sending email to ${recipientEmailFormatted}:`, error);
      console.error('❌ Error details:', {
        message: error.message,
        code: error.code,
        command: error.command,
        response: error.response,
        responseCode: error.responseCode
      });
      
      // If connection timeout on port 465, try port 587 with STARTTLS
      if (error.code === 'ETIMEDOUT' && error.command === 'CONN') {
        console.log('🔄 Retrying with port 587 (STARTTLS)...');
        try {
          const fallbackTransporter = nodemailer.createTransport({
            host: 'mail.signwisesolutions.com',
            port: 587,
            secure: false, // false for STARTTLS
            auth: {
              user: 'noreply@signwisesolutions.com',
              pass: 'signwise@2024',
            },
            connectionTimeout: 20000,
            greetingTimeout: 20000,
            socketTimeout: 20000,
            tls: {
              rejectUnauthorized: false
            }
          });
          
          const info = await fallbackTransporter.sendMail(mailOptions);
          console.log(`✅ Email sent successfully via port 587 to: ${recipientEmailFormatted}`);
          console.log('📧 Email info:', {
            messageId: info.messageId,
            response: info.response,
            accepted: info.accepted,
            rejected: info.rejected
          });
          if (ccEmailsFormatted.length > 0) {
            console.log(`✅ CC emails: ${ccEmailsFormatted.join(', ')}`);
          }
          // Success with fallback, don't set error status
        } catch (fallbackError) {
          console.error(`❌ Fallback also failed:`, fallbackError);
          emailStatus = 0;
          errorMessage = `Connection timeout. Tried both port 465 and 587. Error: ${error.message}`;
        }
      } else {
        emailStatus = 0;
        errorMessage = error.message || 'Failed to send email';
      }
    }

    try {
      await DesignEmailLog.create({
        design_order_id: design_order_id,
        recipient_email: recipientEmail.trim(),
        cc_emails: Array.isArray(ccEmails) && ccEmails.length > 0 ? JSON.stringify(ccEmails) : null,
        subject: subject.trim(),
        message: message || null,
        selected_design_ids: Array.isArray(selectedDesignIds) ? selectedDesignIds.join(',') : null,
        sent_by: userId,
        user_type: userType,
        status: emailStatus,
        error_message: errorMessage,
        timestamp: timestamp,
        created_at: timestamp,
      });
    } catch (dbError) {
      console.error('Error saving email log to database:', dbError);
    }

    downloadedFiles.forEach((filePath) => {
      try {
        if (fs.existsSync(filePath)) {
          fs.unlinkSync(filePath);
        }
      } catch (cleanupError) {
        console.error('Error cleaning up file:', cleanupError.message);
      }
    });

    if (emailStatus === 1) {
      return reply.send({ 
        status: 1, 
        message: 'Email sent successfully' 
      });
    } else {
      return reply.send({ 
        status: 0, 
        message: errorMessage || 'Failed to send email' 
      });
    }

  } catch (error) {
    console.error('Error in sendDesignsToEmail:', error);
    return reply.send({ 
      status: 0, 
      message: 'An error occurred while sending the email: ' + error.message 
    });
  }
};

const sendInvoicePdfToEmail = async (req, reply) => {
  try {
		const {
		  design_order_id,
		  selectedDesignIds,
		  quotation_id,
		  request_id,
		  purchase_order_id,
		  designs,
		  userId,
		  userType,
		  recipientEmail,
		  ccEmails = [],
		  subject,
		  message = '',
		  isFromSplitEstimate = false,
		  pageType,
      senderEmail,
      senderName,
      details = {},
      invoice = {},
      pdfs = [],
      REST_API = '',
      loading = false,
		} = req.body;
    if (!design_order_id && !quotation_id && !request_id && !purchase_order_id) {
      return reply.send({ status: 0, message: 'Design order ID, quotation ID, request ID or purchase order ID is required' });
    }
    if (!recipientEmail || !recipientEmail.trim()) {
      return reply.send({ status: 0, message: 'Recipient email is required' });
    }
    if (!subject || !subject.trim()) {
      return reply.send({ status: 0, message: 'Subject is required' });
    }
    if (!designs || !Array.isArray(designs) || designs.length === 0) {
      return reply.send({ status: 0, message: 'No designs selected' });
    }

    const timestamp = moment().unix();
    const tempDir = path.join(__dirname, '../uploads/temp');
    
    if (!fs.existsSync(tempDir)) {
      fs.mkdirSync(tempDir, { recursive: true });
    }

    const attachments = [];
    const downloadedFiles = [];
    const isProposal =
    (FACILITY_ID === 3) &&
    !(
      ((details?.is_request_estimate == 1 &&
        details?.hold == 0 &&
        details?.estimate_job_status == 3 &&
        details?.quotation_status == 3) ||
        (details?.is_request_estimate == 0 &&
          details?.quotation_status != 4)) &&
      details?.send_to_client == 0
    );
  
    for (let i = 0; i < designs.length; i++) {
      const design = designs[i];
      
      // Check if this is an invoice PDF (has invoiceData)
      if (design.invoiceData) {
        try {
          // Handle invoice PDF generation
          const invoiceData = design.invoiceData;
          
          const hasInstallationAcceptance =
            invoiceData?.installation_acceptance == 1 ||
            invoiceData?.details?.installation_acceptance == 1;

          const pageTypePrefix =
            invoiceData?.isFromCompletedTab === true || hasInstallationAcceptance
              ? 'invoice'
              : pageType === 'request'
                ? 'sales_order'
                : pageType === 'quotation'
                  ? isProposal
                    ? 'proposal' : 'estimate'
                  : pageType === 'purchase_order'
                    ? 'purchase_order'
                    : 'invoice';
          const fileName = `${pageTypePrefix}_${invoiceData.details?.id || 'invoice'}_${moment().format('YYYY-MM-DD')}.pdf`;
          
          // Get company profile if not already in invoice data
          let companyProfile = invoiceData.invoice?.companyProfile;
          if (!companyProfile) {
            try {
              let profileData = await Settings.getCompanyProfileDetails();
              if (profileData && profileData.length > 0) {
                companyProfile = profileData[0];
              }
            } catch (error) {
              console.error('Error fetching company profile:', error);
            }
          }
          
          // Prepare data for invoiceTableFromUI (exact UI structure)
          // All data should come from the UI request body
          let transformedInvoice = invoiceData.invoice;
          
          // Transform purchase order invoice data if needed
          if (purchase_order_id && Array.isArray(invoiceData.invoice)) {
            // Purchase order invoice is an array of products, transform to expected format
            transformedInvoice = {
              cust_product: invoiceData.invoice.map((item, index) => ({
                product_name: item.name || item.product_name || '',
                description: item.description || '',
                quantity: item.actual_qty || item.qty || 0,
                unit_cost: item.unit_cost || 0,
                total_cost: item.actual_total_cost || item.total_cost || 0,
                service_name: item.service_name || '',
                service_cost: item.service_cost || 0,
                meterial: item.meterial !== undefined ? item.meterial : (item.material !== undefined ? item.material : 1),
                product_index: index + 1,
                services: [] // Services are handled separately for purchase orders
              }))
            };
          }
          
          const invoiceTableData = {
            invoice: transformedInvoice,
            details: invoiceData.details,
            invoiceSelectedServices: invoiceData.invoiceSelectedServices || [],
            additionalSelectedServices: invoiceData.additionalSelectedServices || [],
            cartService: invoiceData.cartService || (invoiceData.orderServices ? invoiceData.orderServices.map(s => ({
              name: s.name || s.description || '',
              price: parseFloat(s.price) || 0,
              quantity: 1
            })) : []),
            labourInstallService: invoiceData.labourInstallService || [],
            subTotal: invoiceData.subTotal || (invoiceData.details?.sub_total || 0),
            orderServices: invoiceData.orderServices || [],
            userType: userType,
            companyProfile: companyProfile,
            isFromCompletedTab: invoiceData.isFromCompletedTab === true,
            pdfs: pdfs || [],
            REST_API: REST_API,
            loading: loading,
          };
          
          // Determine if this is a quotation/estimate
          // Check if quotation_id is present or if details indicate it's a quotation
          let isQuotation = false;
          if (quotation_id) {
            isQuotation = true;
          } else if (invoiceData.details) {
            // Check if it's a quotation based on details
            // Quotations typically have quotation = 1 and quotation_status = 4
            try {
              let requestId = invoiceData.details.id || request_id;
              if (requestId) {
                const requestResult = await sequelize.query(
                  `SELECT quotation, quotation_status FROM requests WHERE id = :request_id AND status != 0`,
                  {
                    replacements: { request_id: requestId },
                    type: Sequelize.QueryTypes.SELECT
                  }
                );
                if (requestResult && requestResult.length > 0) {
                  const request = requestResult[0];
                  isQuotation = request.quotation === 1 && request.quotation_status === 4;
                }
              }
            } catch (error) {
              console.error('Error checking if quotation:', error);
              // Default to sales order template if check fails
            }
          }
          
          // Generate invoice HTML using appropriate template based on document type
          let invoiceHtml;
          if (purchase_order_id) {
            invoiceHtml = invoiceTableFromUIForPurchaseOrder(invoiceTableData);
          } else if (isQuotation) {
            invoiceHtml = invoiceTableFromUIForQuotation(invoiceTableData);
          } else {
            invoiceHtml = invoiceTableFromUI(invoiceTableData);
          }
          
          // Save HTML as temp file (used for PDF conversion and fallback)
          const tempHtmlPath = path.join(tempDir, `${timestamp}_${i}_invoice.html`);
          fs.writeFileSync(tempHtmlPath, invoiceHtml);
          downloadedFiles.push(tempHtmlPath);

          // Convert HTML to PDF; fallback to HTML if conversion fails
          let attachmentPath = tempHtmlPath;
          let attachmentFilename = fileName.replace(/\.pdf$/i, '.html');
          let attachmentContentType = 'text/html';

          try {
            let pdfFilePath = null;
            try {
              const puppeteer = require('puppeteer');
              const browser = await puppeteer.launch({
                headless: true,
                args: ['--no-sandbox', '--disable-setuid-sandbox']
              });
              const page = await browser.newPage();
              await page.setContent(invoiceHtml, { waitUntil: 'networkidle0' });
              const pdfBuffer = await page.pdf({
                format: 'A3',
                printBackground: true,
                margin: { top: '0.5in', right: '0.5in', bottom: '0.5in', left: '0.5in' }
              });
              await browser.close();
              pdfFilePath = path.join(tempDir, `${timestamp}_${i}_invoice.pdf`);
              fs.writeFileSync(pdfFilePath, pdfBuffer);
              downloadedFiles.push(pdfFilePath);
              attachmentPath = pdfFilePath;
              attachmentFilename = fileName.endsWith('.pdf') ? fileName : `${fileName.replace(/\.[^.]+$/i, '')}.pdf`;
              attachmentContentType = 'application/pdf';
            } catch (puppeteerErr) {
              try {
                const pdf = require('html-pdf');
                pdfFilePath = path.join(tempDir, `${timestamp}_${i}_invoice.pdf`);
                await new Promise((resolve, reject) => {
                  pdf.create(invoiceHtml, {
                    format: 'A3',
                    border: { top: '0.5in', right: '0.5in', bottom: '0.5in', left: '0.5in' }
                  }).toFile(pdfFilePath, (err, res) => (err ? reject(err) : resolve(res)));
                });
                downloadedFiles.push(pdfFilePath);
                attachmentPath = pdfFilePath;
                attachmentFilename = fileName.endsWith('.pdf') ? fileName : `${fileName.replace(/\.[^.]+$/i, '')}.pdf`;
                attachmentContentType = 'application/pdf';
              } catch (htmlPdfErr) {
                // Fallback: use HTML attachment
              }
            }
          } catch (pdfError) {
            console.error('Error generating invoice PDF:', pdfError.message);
          }

          attachments.push({
            filename: attachmentFilename,
            path: attachmentPath,
            contentType: attachmentContentType,
          });
        } catch (error) {
          console.error(`Error processing invoice PDF ${i + 1}:`, error.message);
        }
        continue;
      }
      
      // Handle regular design files (existing logic)
      if (!design.name) {
        console.warn(`Skipping design ${i + 1}: No URL found`);
        continue;
      }

      const fileExtension = getInvoiceFileExtension(design);
      const fileName = getInvoiceFileName(design);
      const tempFilePath = path.join(tempDir, `${timestamp}_${i}_${fileName}`);

      try {
        const downloadedPath = await downloadFileFromUrl(design.name, tempFilePath);
        if (downloadedPath) {
          attachments.push({
            filename: fileName,
            path: downloadedPath,
            contentType: getContentType(fileExtension),
          });
          downloadedFiles.push(downloadedPath);
        } else {
          console.error(`Failed to download file: ${design.name}`);
        }
      } catch (error) {
        console.error(`Error processing file ${i + 1}:`, error.message);
      }
    }

    // **NEW CODE** to handle sign agreement PDFs from both agreement_pdf_path and attachment (from Supabase)
    // if (invoice?.sign_agreements && invoice.sign_agreements.length > 0) {
    //   invoice.sign_agreements.forEach((agr) => {
    //     // Check for agreement_pdf_path or attachment (Supabase file)
    //     const pdfPath = agr.agreement_pdf_path || agr.attachment;

    //     if (pdfPath) {
    //       // Use the agreement's `name` from `sign_agreements`
    //       const fileName = agr.name || pdfPath.split('/').pop(); // Default to the extracted file name if `agr.name` is not available

    //       attachments.push({
    //         filename: fileName,
    //         path: pdfPath,
    //         contentType: 'application/pdf', // Treat it as a PDF
    //       });
    //     }
    //   });
    // }

    if (attachments.length === 0) {
      return reply.send({ 
        status: 0, 
        message: 'Failed to download design files. Please check the file URLs.' 
      });
    }

    // Determine document type and name
    let documentType = 'Document';
    let documentName = 'Document';
    let hasInvoice = designs.some(d => d.invoiceData);
    
    if (hasInvoice) {
      const invoiceData = designs.find(d => d.invoiceData)?.invoiceData;
      const hasInstallationAcceptance =
        invoiceData?.installation_acceptance == 1 ||
        invoiceData?.details?.installation_acceptance == 1;
    
      // 🔹 If from completed tab or installation acceptance → Always show Invoice
      if (invoiceData?.isFromCompletedTab === true || hasInstallationAcceptance) {
        documentType = 'Invoice';
      } 
      // 🔹 Otherwise follow pageType logic
      else if (pageType === 'request') {
        documentType = 'Sales Order';
      } else if (pageType === 'quotation') {
        documentType = isProposal ? 'Proposal' : 'Estimate';
      } else if (pageType === 'purchase_order') {
        documentType = 'Purchase Order';
      } else {
        documentType = 'Invoice';
      }
      // const invoiceData = designs.find(d => d.invoiceData)?.invoiceData;
     if (invoiceData && invoiceData.details) {
    documentName =
      invoiceData.details.name ||
      `${documentType} #${invoiceData.details.id || design_order_id || quotation_id || request_id || purchase_order_id || ''}`;
      } else {
        documentName =
          `${documentType} #${design_order_id || quotation_id || request_id || purchase_order_id || ''}`;
      }
    } else if (design_order_id || quotation_id || request_id) {
      // First check if it's a Design Order
      try {
        if (design_order_id) {
          const designOrderResult = await sequelize.query(
            `SELECT name FROM design_orders WHERE id = :design_order_id AND status != 0`,
            {
              replacements: { design_order_id: design_order_id },
              type: Sequelize.QueryTypes.SELECT
            }
          );
          if (designOrderResult && designOrderResult.length > 0 && designOrderResult[0].name) {
            documentType = 'Design Order';
            documentName = designOrderResult[0].name;
          }
        } else {
          // Check if it's an Estimate or Sales Order (Request)
          let requestId = quotation_id || request_id;
          const requestResult = await sequelize.query(
            `SELECT name, quotation, quotation_status FROM requests WHERE id = :request_id AND status != 0`,
            {
              replacements: { request_id: requestId },
              type: Sequelize.QueryTypes.SELECT
            }
          );
          if (requestResult && requestResult.length > 0) {
            const request = requestResult[0];
            // quotation_status: 4 = Approved/Estimate, others = Sales Order
            if (request.quotation === 1 && request.quotation_status === 4) {
              documentType = isProposal ? 'Proposal' : 'Estimate';
            } else {
              documentType = 'Sales Order';
            }
            documentName = request.name || `${documentType} #${design_order_id}`;
          } else {
            // Default fallback
            documentType = 'Document';
            documentName = `Document #${design_order_id}`;
          }
        }
      } catch (error) {
        console.error('Error fetching document name:', error);
        // Continue with default name if fetch fails
        documentType = 'Document';
        documentName = `Document #${design_order_id || quotation_id || request_id || ''}`;
      }
    } else if (purchase_order_id) {
      // Handle Purchase Order
      try {
        const purchaseOrderResult = await sequelize.query(
          `SELECT order_name FROM receive_orders WHERE id = :purchase_order_id AND status != 0`,
          {
            replacements: { purchase_order_id: purchase_order_id },
            type: Sequelize.QueryTypes.SELECT
          }
        );
        if (purchaseOrderResult && purchaseOrderResult.length > 0 && purchaseOrderResult[0].order_name) {
          documentType = 'Purchase Order';
          documentName = purchaseOrderResult[0].order_name;
        } else {
          documentType = 'Purchase Order';
          documentName = `Purchase Order #${purchase_order_id}`;
        }
      } catch (error) {
        console.error('Error fetching purchase order name:', error);
        documentType = 'Purchase Order';
        documentName = `Purchase Order #${purchase_order_id || ''}`;
      }
    }

    const escapedMessage = escapeHtml(message).replace(/\n/g, '<br>');
    const escapedDocumentName = escapeHtml(documentName);
    const escapedDocumentType = escapeHtml(documentType);
    
     // Determine attachment text based on document type
     let attachmentText = 'design file(s)';
      if (documentType === 'Estimate') {
        attachmentText = 'estimate file(s)';
      } else if (documentType === 'Proposal') {
        attachmentText = 'proposal file(s)';
      } else if (documentType === 'Sales Order') {
        attachmentText = 'sales order file(s)';
      } else if (documentType === 'Purchase Order') {
        attachmentText = 'purchase order file(s)';
      } else if (documentType === 'Invoice') {
        attachmentText = 'invoice file(s)';
        const invoiceDataForAttachment = designs.find(d => d.invoiceData)?.invoiceData;
        const hasInstallationAcceptance =
          invoiceDataForAttachment?.installation_acceptance == 1 ||
          invoiceDataForAttachment?.details?.installation_acceptance == 1;
        if (hasInstallationAcceptance) {
          attachmentText = 'invoice file(s)';
        }
      } else {
        attachmentText = 'document file(s)';
      }
    
    
    const emailHtml = `
      <!DOCTYPE html>
      <html>
      <head>
        <meta charset="utf-8">
        <style>
          body { font-family: Arial, sans-serif; line-height: 1.6; color: #333; }
          .container { max-width: 600px; margin: 0 auto; padding: 20px; }
          .message { margin-top: 20px; white-space: pre-wrap; }
        </style>
      </head>
      <body>
        <div class="container">
          <h2>${escapedDocumentType} - ${escapedDocumentName}</h2>
          ${escapedMessage ? `<div class="message">${escapedMessage}</div>` : ''}
          <p style="margin-top: 20px; color: #666;">
            This email contains ${attachments.length} ${attachmentText} as attachments.
          </p>
        </div>
      </body>
      </html>
    `;

    // Validate and format recipient email
    const recipientEmailFormatted = recipientEmail.trim();
    
    // Filter and validate CC emails
    let ccEmailsFormatted = [];
    if (Array.isArray(ccEmails) && ccEmails.length > 0) {
      ccEmailsFormatted = ccEmails
        .map(email => typeof email === 'string' ? email.trim() : '')
        .filter(email => email && email.length > 0);
    }

    // Build mailOptions following the same pattern as request controller
    // Allow overriding "from" when a valid senderEmail is provided (used for invoice approval flow)
    let fromAddress = '"SignWise Solutions" <noreply@signwisesolutions.com>';
    if (senderEmail && typeof senderEmail === 'string' && senderEmail.trim().length > 0) {
      const safeSenderEmail = senderEmail.trim();
      const displayName =
        senderName && typeof senderName === 'string' && senderName.trim().length > 0
          ? senderName.trim()
          : 'SignWise Solutions';
      fromAddress = `"${displayName}" <${safeSenderEmail}>`;
    }

    const mailOptions = {
      from: fromAddress,
      to: recipientEmailFormatted,
      subject: subject.trim(),
      text: subject.trim(),
      html: emailHtml,
      attachments: attachments,
    };

    // Only add CC if there are CC emails (don't include undefined)
    if (ccEmailsFormatted.length > 0) {
      mailOptions.cc = ccEmailsFormatted;
    }

    console.log('📧 Preparing to send email:', {
      to: recipientEmailFormatted,
      cc: ccEmailsFormatted.length > 0 ? ccEmailsFormatted : 'none',
      subject: subject.trim(),
      attachmentsCount: attachments.length
    });

    let emailStatus = 1;
    let errorMessage = null;

    // Send email following the same pattern as request controller
    try {
      // Send email directly without verify to avoid extra connection timeout
      const info = await transporter.sendMail(mailOptions);
      console.log(`✅ Email sent successfully to: ${recipientEmailFormatted}`);
      console.log('📧 Email info:', {
        messageId: info.messageId,
        response: info.response,
        accepted: info.accepted,
        rejected: info.rejected
      });
      if (ccEmailsFormatted.length > 0) {
        console.log(`✅ CC emails: ${ccEmailsFormatted.join(', ')}`);
      }
    } catch (error) {
      console.error(`❌ Error sending email to ${recipientEmailFormatted}:`, error);
      console.error('❌ Error details:', {
        message: error.message,
        code: error.code,
        command: error.command,
        response: error.response,
        responseCode: error.responseCode
      });
      
      // If connection timeout on port 465, try port 587 with STARTTLS
      if (error.code === 'ETIMEDOUT' && error.command === 'CONN') {
        console.log('🔄 Retrying with port 587 (STARTTLS)...');
        try {
          const fallbackTransporter = nodemailer.createTransport({
            host: 'mail.signwisesolutions.com',
            port: 587,
            secure: false, // false for STARTTLS
            auth: {
              user: 'noreply@signwisesolutions.com',
              pass: 'signwise@2024',
            },
            connectionTimeout: 20000,
            greetingTimeout: 20000,
            socketTimeout: 20000,
            tls: {
              rejectUnauthorized: false
            }
          });
          
          const info = await fallbackTransporter.sendMail(mailOptions);
          console.log(`✅ Email sent successfully via port 587 to: ${recipientEmailFormatted}`);
          console.log('📧 Email info:', {
            messageId: info.messageId,
            response: info.response,
            accepted: info.accepted,
            rejected: info.rejected
          });
          if (ccEmailsFormatted.length > 0) {
            console.log(`✅ CC emails: ${ccEmailsFormatted.join(', ')}`);
          }
          // Success with fallback, don't set error status
        } catch (fallbackError) {
          console.error(`❌ Fallback also failed:`, fallbackError);
          emailStatus = 0;
          errorMessage = `Connection timeout. Tried both port 465 and 587. Error: ${error.message}`;
        }
      } else {
        emailStatus = 0;
        errorMessage = error.message || 'Failed to send email';
      }
    }
    if (design_order_id) {
      try {
        await DesignEmailLog.create({
          design_order_id: design_order_id,
          // quotation_id: quotation_id,
          // request_id: request_id,
          recipient_email: recipientEmail.trim(),
          cc_emails: Array.isArray(ccEmails) && ccEmails.length > 0 ? JSON.stringify(ccEmails) : null,
          subject: subject.trim(),
          message: message || null,
          selected_design_ids: Array.isArray(selectedDesignIds) ? selectedDesignIds.join(',') : null,
          sent_by: userId,
          user_type: userType,
          status: emailStatus,
          error_message: errorMessage,
          timestamp: timestamp,
          created_at: timestamp,
        });
      } catch (dbError) {
        console.error('Error saving email log to database:', dbError);
      }
    }

    downloadedFiles.forEach((filePath) => {
      try {
        if (fs.existsSync(filePath)) {
          fs.unlinkSync(filePath);
        }
      } catch (cleanupError) {
        console.error('Error cleaning up file:', cleanupError.message);
      }
    });

    if (emailStatus === 1) {
      return reply.send({ 
        status: 1, 
        message: 'Email sent successfully' 
      });
    } else {
      return reply.send({ 
        status: 0, 
        message: errorMessage || 'Failed to send email' 
      });
    }

  } catch (error) {
    console.error('Error in sendDesignsToEmail:', error);
    return reply.send({ 
      status: 0, 
      message: 'An error occurred while sending the email: ' + error.message 
    });
  }
};

const getDesignEmailLogs = async (req, reply) => {
  try {
    const { design_order_id, userId } = req.body;

    if (!design_order_id) {
      return reply.send({ status: 0, message: 'Design order ID is required' });
    }

    const whereClause = {
      design_order_id: design_order_id,
      status: 1,
    };

    if (userId) {
      whereClause.sent_by = userId;
    }

    const emailLogs = await DesignEmailLog.findAll({
      where: whereClause,
      order: [['timestamp', 'DESC']],
      attributes: [
        'id',
        'design_order_id',
        'recipient_email',
        'cc_emails',
        'subject',
        'message',
        'selected_design_ids',
        'sent_by',
        'user_type',
        'status',
        'error_message',
        'timestamp',
        'created_at',
      ],
    });

    const formattedLogs = emailLogs.map(log => {
      const logData = log.toJSON();
      try {
        if (logData.cc_emails) {
          logData.cc_emails = JSON.parse(logData.cc_emails);
        }
        if (logData.selected_design_ids) {
          logData.selected_design_ids = logData.selected_design_ids.split(',');
        }
      } catch (e) {
        // If parsing fails, keep original value
      }
      return logData;
    });

    return reply.send({
      status: 1,
      message: 'Email logs fetched successfully',
      data: formattedLogs,
    });

  } catch (error) {
    console.error('Error in getDesignEmailLogs:', error);
    return reply.send({
      status: 0,
      message: 'An error occurred while fetching email logs: ' + error.message,
    });
  }
};

const getAllDesignEmailLogs = async (req, reply) => {
  try {
    const {
      design_order_id,
      recipientEmail,
      startDate,
      endDate,
      status,
      page = 1,
      rowsPerPage = 50,
    } = req.body;

    const whereClause = {};

    if (design_order_id) {
      whereClause.design_order_id = design_order_id;
    }

    if (recipientEmail) {
      whereClause.recipient_email = {
        [Op.like]: `%${recipientEmail}%`,
      };
    }

    if (status !== undefined && status !== null) {
      whereClause.status = status;
    }

    if (startDate || endDate) {
      whereClause.timestamp = {};
      if (startDate) {
        const startTimestamp = moment(startDate).startOf('day').unix();
        whereClause.timestamp[Op.gte] = startTimestamp;
      }
      if (endDate) {
        const endTimestamp = moment(endDate).endOf('day').unix();
        whereClause.timestamp[Op.lte] = endTimestamp;
      }
    }

    const offset = (page - 1) * rowsPerPage;

    const { count, rows } = await DesignEmailLog.findAndCountAll({
      where: whereClause,
      order: [['timestamp', 'DESC']],
      limit: rowsPerPage,
      offset: offset,
      attributes: [
        'id',
        'design_order_id',
        'recipient_email',
        'cc_emails',
        'subject',
        'message',
        'selected_design_ids',
        'sent_by',
        'user_type',
        'status',
        'error_message',
        'timestamp',
        'created_at',
      ],
    });

    const formattedLogs = rows.map(log => {
      const logData = log.toJSON();
      try {
        if (logData.cc_emails) {
          logData.cc_emails = JSON.parse(logData.cc_emails);
        }
        if (logData.selected_design_ids) {
          logData.selected_design_ids = logData.selected_design_ids.split(',');
        }
      } catch (e) {
        // If parsing fails, keep original value
      }
      return logData;
    });

    return reply.send({
      status: 1,
      message: 'Email logs fetched successfully',
      data: formattedLogs,
      total: count,
      page: page,
      rowsPerPage: rowsPerPage,
      totalPages: Math.ceil(count / rowsPerPage),
    });

  } catch (error) {
    console.error('Error in getAllDesignEmailLogs:', error);
    return reply.send({
      status: 0,
      message: 'An error occurred while fetching email logs: ' + error.message,
    });
  }
};

module.exports = {
  sendDesignsToEmail,
  getDesignEmailLogs,
  getAllDesignEmailLogs,
  sendInvoicePdfToEmail,
};
