const express = require('express');
const router = express.Router();
const {
  getAllInvoices,
  getInvoiceById,
  getNextInvoiceNo,
  createInvoice,
  updateInvoice,
  deleteInvoice,
  recordInvoicePayment,
  getCompanyProfile
} = require('../config/db');
const notificationService = require('../utils/notificationService');

// Date formatter helper
const formatDateToLocal = (dateString) => {
  if (!dateString) return '';
  return new Date(dateString).toLocaleDateString('en-GB', { day: 'numeric', month: 'short', year: 'numeric' });
};

// Helper to generate Invoice HTML
const generateInvoiceHtml = (invData, profile = {}) => {
  return `
<div style="font-family: 'Helvetica Neue', Helvetica, Arial, sans-serif; background-color: #f8fafc; padding: 40px 20px; color: #334155; line-height: 1.6;">
  <div style="max-width: 680px; margin: 0 auto; background-color: #ffffff; border-radius: 12px; overflow: hidden; box-shadow: 0 4px 15px rgba(0,0,0,0.05);">
    
    <!-- Header Banner -->
    <div style="background: linear-gradient(135deg, #4f46e5 0%, #3730a3 100%); color: #ffffff; padding: 40px; display: table; width: 100%; box-sizing: border-box;">
      <div style="display: table-cell; vertical-align: middle;">
        <h1 style="margin: 0; font-size: 32px; letter-spacing: 1px;">INVOICE</h1>
        <p style="margin: 5px 0 0 0; color: #c7d2fe; font-size: 14px;">${invData.invoice_no}</p>
      </div>
      <div style="display: table-cell; vertical-align: middle; text-align: right;">
        <h2 style="margin: 0; font-size: 24px;">${profile.company_name || 'LS SOFT'}</h2>
        <p style="margin: 5px 0 0 0; color: #c7d2fe; font-size: 13px; line-height: 1.5;">
          ${profile.address ? profile.address + '<br>' : 'IT & Engineering<br>'}
          ${profile.phone ? `Phone: <a href="tel:${profile.phone}" style="color: #ffffff; text-decoration: none;">${profile.phone}</a><br>` : ''}
          ${profile.email ? `Email: <a href="mailto:${profile.email}" style="color: #ffffff; text-decoration: none;">${profile.email}</a><br>` : ''}
          ${profile.website ? `Web: <a href="${profile.website.startsWith('http') ? profile.website : 'https://' + profile.website}" style="color: #ffffff; text-decoration: none;">${profile.website}</a>` : ''}
        </p>
      </div>
    </div>

    <!-- Info Section -->
    <div style="padding: 40px;">
      <table style="width: 100%; margin-bottom: 30px;" cellpadding="0" cellspacing="0" border="0">
        <tr>
          <td style="vertical-align: top; width: 50%;">
            <p style="font-size: 12px; color: #94a3b8; text-transform: uppercase; letter-spacing: 1px; margin: 0 0 5px 0;">Billed To</p>
            <h3 style="margin: 0 0 5px 0; color: #1e293b; font-size: 18px;">${invData.customer_name}</h3>
            ${invData.customer_company ? `<p style="margin: 0 0 5px 0; font-size: 14px;">${invData.customer_company}</p>` : ''}
            <p style="margin: 0; font-size: 14px; color: #64748b;">${invData.customer_email || ''}<br>${invData.customer_phone || ''}</p>
          </td>
          <td style="vertical-align: top; width: 50%; text-align: right;">
            <table style="width: 100%; border-collapse: collapse;">
              <tr>
                <td style="padding-bottom: 8px; color: #64748b; font-size: 14px; text-align: right;">Invoice Date:</td>
                <td style="padding-bottom: 8px; font-weight: 600; color: #1e293b; text-align: right; padding-left: 15px;">${formatDateToLocal(invData.invoice_date)}</td>
              </tr>
              <tr>
                <td style="padding-bottom: 8px; color: #64748b; font-size: 14px; text-align: right;">Due Date:</td>
                <td style="padding-bottom: 8px; font-weight: 600; color: #ef4444; text-align: right; padding-left: 15px;">${formatDateToLocal(invData.due_date)}</td>
              </tr>
              <tr>
                <td style="padding-bottom: 8px; color: #64748b; font-size: 14px; text-align: right;">Amount Due:</td>
                <td style="padding-bottom: 8px; font-weight: 700; color: #4f46e5; font-size: 18px; text-align: right; padding-left: 15px;">BDT ${Number(invData.due_amount).toLocaleString()}</td>
              </tr>
            </table>
          </td>
        </tr>
      </table>

      <!-- Items Table -->
      <table style="width: 100%; border-collapse: collapse; margin-bottom: 30px;">
        <thead>
          <tr>
            <th style="padding: 12px; background-color: #f1f5f9; color: #475569; text-align: left; font-size: 13px; text-transform: uppercase; border-radius: 6px 0 0 6px;">Description</th>
            <th style="padding: 12px; background-color: #f1f5f9; color: #475569; text-align: center; font-size: 13px; text-transform: uppercase;">Qty</th>
            <th style="padding: 12px; background-color: #f1f5f9; color: #475569; text-align: right; font-size: 13px; text-transform: uppercase;">Price</th>
            <th style="padding: 12px; background-color: #f1f5f9; color: #475569; text-align: right; font-size: 13px; text-transform: uppercase; border-radius: 0 6px 6px 0;">Total</th>
          </tr>
        </thead>
        <tbody>
          ${(invData.items || []).map(item => `
            <tr>
              <td style="padding: 16px 12px; border-bottom: 1px solid #e2e8f0;">
                <p style="margin: 0; font-weight: 600; color: #1e293b; white-space: pre-line;">${item.description}</p>
                ${item.note ? `<p style="margin: 6px 0 0 0; font-size: 13px; color: #64748b; white-space: pre-line;">Note: ${item.note}</p>` : ''}
              </td>
              <td style="padding: 16px 12px; border-bottom: 1px solid #e2e8f0; text-align: center;">${item.qty}</td>
              <td style="padding: 16px 12px; border-bottom: 1px solid #e2e8f0; text-align: right;">${Number(item.unit_price).toLocaleString()}</td>
              <td style="padding: 16px 12px; border-bottom: 1px solid #e2e8f0; text-align: right; font-weight: 600;">${Number(item.total).toLocaleString()}</td>
            </tr>
          `).join('')}
        </tbody>
      </table>

      <!-- Totals -->
      <table style="width: 100%;" cellpadding="0" cellspacing="0" border="0">
        <tr>
          <td style="width: 50%;"></td>
          <td style="width: 50%;">
            <table style="width: 100%; border-collapse: collapse;">
              <tr>
                <td style="padding: 8px 0; color: #64748b; text-align: right; padding-right: 15px;">Subtotal:</td>
                <td style="padding: 8px 0; text-align: right; font-weight: 600;">BDT ${Number(invData.subtotal).toLocaleString()}</td>
              </tr>
              ${Number(invData.discount) > 0 ? `
              <tr>
                <td style="padding: 8px 0; color: #ef4444; text-align: right; padding-right: 15px;">Discount:</td>
                <td style="padding: 8px 0; text-align: right; font-weight: 600; color: #ef4444;">- BDT ${Number(invData.discount).toLocaleString()}</td>
              </tr>` : ''}
              ${Number(invData.tax) > 0 ? `
              <tr>
                <td style="padding: 8px 0; color: #64748b; text-align: right; padding-right: 15px;">Tax:</td>
                <td style="padding: 8px 0; text-align: right; font-weight: 600;">BDT ${Number(invData.tax).toLocaleString()}</td>
              </tr>` : ''}
              <tr>
                <td colspan="2" style="border-top: 2px solid #e2e8f0; padding-top: 12px; margin-top: 12px;"></td>
              </tr>
              <tr>
                <td style="padding: 8px 0; font-weight: 700; font-size: 16px; color: #1e293b; text-align: right; padding-right: 15px;">Total Amount:</td>
                <td style="padding: 8px 0; text-align: right; font-weight: 700; font-size: 16px; color: #4f46e5;">BDT ${Number(invData.total_amount).toLocaleString()}</td>
              </tr>
              <tr>
                <td style="padding: 8px 0; color: #64748b; text-align: right; padding-right: 15px;">Amount Paid:</td>
                <td style="padding: 8px 0; text-align: right; font-weight: 600; color: #10b981;">BDT ${Number(invData.paid_amount).toLocaleString()}</td>
              </tr>
              <tr>
                <td style="padding: 8px 0; font-weight: 700; font-size: 16px; color: #1e293b; text-align: right; padding-right: 15px;">Balance Due:</td>
                <td style="padding: 8px 0; text-align: right; font-weight: 700; font-size: 16px; color: #ef4444;">BDT ${Number(invData.due_amount).toLocaleString()}</td>
              </tr>
            </table>
          </td>
        </tr>
      </table>

      ${invData.payment_info ? `
      <div style="margin-top: 40px; padding: 20px; background-color: #f8fafc; border-radius: 8px; border-left: 4px solid #4f46e5;">
        <h4 style="margin: 0 0 10px 0; color: #1e293b;">Payment Information</h4>
        <p style="margin: 0; font-size: 14px; color: #64748b; white-space: pre-wrap;">${invData.payment_info}</p>
      </div>` : ''}
    </div>
    
    <!-- Footer -->
    <div style="background-color: #f1f5f9; padding: 20px; text-align: center; border-top: 1px solid #e2e8f0;">
      <p style="margin: 0; font-size: 12px; color: #94a3b8;">Thank you for doing business with ${profile.company_name || 'LS SOFT'}!</p>
      <p style="margin: 5px 0 0 0; font-size: 12px; color: #94a3b8;">This is an automated invoice email. Please do not reply directly to this email.</p>
    </div>
  </div>
</div>
  `;
};

// Helper to generate Payment HTML
const generatePaymentHtml = (invData, amount, txn_date, profile = {}) => {
  return `
<div style="font-family: 'Helvetica Neue', Helvetica, Arial, sans-serif; background-color: #f8fafc; padding: 40px 20px; color: #334155; line-height: 1.6;">
  <div style="max-width: 500px; margin: 0 auto; background-color: #ffffff; border-radius: 12px; overflow: hidden; box-shadow: 0 4px 15px rgba(0,0,0,0.05);">
    
    <!-- Header Banner -->
    <div style="background: linear-gradient(135deg, #10b981 0%, #047857 100%); color: #ffffff; padding: 40px 20px; text-align: center;">
      <div style="width: 60px; height: 60px; background-color: rgba(255,255,255,0.2); border-radius: 50%; display: inline-block; line-height: 60px; text-align: center; margin-bottom: 15px;">
        <span style="font-size: 30px; display: inline-block; vertical-align: middle;">✓</span>
      </div>
      <h1 style="margin: 0; font-size: 28px; letter-spacing: 1px;">Payment Successful</h1>
      <p style="margin: 10px 0 0 0; color: #d1fae5; font-size: 16px;">Thank you for your payment!</p>
    </div>

    <!-- Info Section -->
    <div style="padding: 40px;">
      <p style="margin-top: 0; font-size: 16px;">Hello <strong>${invData.customer_name}</strong>,</p>
      <p style="color: #64748b; font-size: 15px;">We've received your payment towards Invoice <strong style="color: #1e293b;">${invData.invoice_no}</strong>.</p>
      
      <div style="margin: 30px 0; padding: 20px; border: 2px dashed #e2e8f0; border-radius: 8px; text-align: center; background-color: #f8fafc;">
        <p style="margin: 0; font-size: 14px; color: #64748b; text-transform: uppercase; letter-spacing: 1px;">Amount Paid</p>
        <h2 style="margin: 10px 0 0 0; color: #10b981; font-size: 36px;">BDT ${Number(amount).toLocaleString()}</h2>
        <p style="margin: 10px 0 0 0; font-size: 14px; color: #94a3b8;">Paid on: ${formatDateToLocal(txn_date || new Date())}</p>
      </div>

      <table style="width: 100%; border-collapse: collapse; margin-bottom: 30px;">
        <tr>
          <td style="padding: 12px 0; border-bottom: 1px solid #e2e8f0; color: #64748b;">Total Invoice Amount</td>
          <td style="padding: 12px 0; border-bottom: 1px solid #e2e8f0; text-align: right; font-weight: 600;">BDT ${Number(invData.total_amount).toLocaleString()}</td>
        </tr>
        <tr>
          <td style="padding: 12px 0; border-bottom: 1px solid #e2e8f0; color: #64748b;">Remaining Balance</td>
          <td style="padding: 12px 0; border-bottom: 1px solid #e2e8f0; text-align: right; font-weight: 700; color: #ef4444;">BDT ${Number(invData.due_amount).toLocaleString()}</td>
        </tr>
      </table>
    </div>
    
    <!-- Footer -->
    <div style="background-color: #f1f5f9; padding: 20px; text-align: center; border-top: 1px solid #e2e8f0;">
      <p style="margin: 0; font-size: 12px; color: #94a3b8;">${profile.company_name || 'LS SOFT'}</p>
      <p style="margin: 5px 0 0 0; font-size: 12px; color: #94a3b8;">If you have any questions, please contact our support team.</p>
    </div>
  </div>
</div>
  `;
};

// GET /api/invoices/next-no - Get next sequential invoice number for date
router.get('/next-no', async (req, res) => {
  try {
    const dateStr = req.query.date || new Date().toISOString().split('T')[0];
    const invoiceNo = await getNextInvoiceNo(dateStr);
    res.json({ success: true, invoice_no: invoiceNo });
  } catch (error) {
    console.error('Error fetching next invoice no:', error);
    res.status(500).json({ success: false, message: 'Failed to fetch next invoice no' });
  }
});

// GET /api/invoices - List all invoices
router.get('/', async (req, res) => {
  try {
    const invoices = await getAllInvoices();
    res.json({ success: true, invoices });
  } catch (error) {
    console.error('Error fetching invoices:', error);
    res.status(500).json({ success: false, message: 'Failed to fetch invoices' });
  }
});

// GET /api/invoices/:id - Get single invoice with items and payment history
router.get('/:id', async (req, res) => {
  try {
    const invoice = await getInvoiceById(req.params.id);
    if (!invoice) {
      return res.status(404).json({ success: false, message: 'Invoice not found' });
    }
    res.json({ success: true, invoice });
  } catch (error) {
    console.error('Error fetching invoice details:', error);
    res.status(500).json({ success: false, message: 'Failed to fetch invoice details' });
  }
});

// POST /api/invoices - Create new invoice
router.post('/', async (req, res) => {
  try {
    const { customer_id, new_customer, invoice_date, due_date, subtotal, discount, tax, notes, terms_conditions, payment_info, items, sendEmail, sendSms } = req.body;
    if (!customer_id) {
      return res.status(400).json({ success: false, message: 'Customer ID is required' });
    }

    const invoice = await createInvoice({ customer_id, new_customer, invoice_date, due_date, subtotal, discount, tax, notes, terms_conditions, payment_info, items });

    // Handle Notifications
    if (sendEmail || sendSms) {
      const invData = await getInvoiceById(invoice.id);
      if (invData) {
        const profile = await getCompanyProfile() || {};
        if (sendEmail && invData.customer_email) {
          const html = generateInvoiceHtml(invData, profile);
          await notificationService.sendEmail(invData.customer_email, `New Invoice: ${invData.invoice_no}`, html);
        }
        
        if (sendSms && invData.customer_phone) {
          const formattedDate = formatDateToLocal(invData.due_date);
          const formattedAmount = Number(invData.total_amount).toLocaleString();
          const msg = `Dear ${invData.customer_name}, Your invoice ${invData.invoice_no} (BDT ${formattedAmount}) is ready. Due: ${formattedDate}. Please pay by the due date. Thank you. ${profile.company_name || 'LS SOFT'}`;
          await notificationService.sendSms(invData.customer_phone, msg);
        }
      }
    }

    res.status(201).json({ success: true, invoice });
  } catch (error) {
    console.error('Error creating invoice:', error);
    res.status(500).json({ success: false, message: 'Failed to create invoice', error: error.message });
  }
});

// PUT /api/invoices/:id - Update invoice
router.put('/:id', async (req, res) => {
  try {
    const { id } = req.params;
    const data = req.body;
    
    if (!data.customer_id) {
      return res.status(400).json({ success: false, message: 'Customer ID is required' });
    }

    const updatedInvoice = await updateInvoice(id, data);
    if (!updatedInvoice) {
      return res.status(404).json({ success: false, message: 'Invoice not found' });
    }
    res.json({ success: true, invoice: updatedInvoice });
  } catch (error) {
    console.error('Error updating invoice:', error);
    res.status(500).json({ success: false, message: 'Failed to update invoice', error: error.message });
  }
});

// DELETE /api/invoices/:id - Delete invoice
router.delete('/:id', async (req, res) => {
  try {
    const { id } = req.params;
    const deleted = await deleteInvoice(id);
    
    if (!deleted) {
      return res.status(404).json({ success: false, message: 'Invoice not found' });
    }
    res.json({ success: true, message: 'Invoice deleted successfully' });
  } catch (error) {
    console.error('Error deleting invoice:', error);
    res.status(500).json({ success: false, message: 'Failed to delete invoice', error: error.message });
  }
});

// POST /api/invoices/:id/payment - Receive Payment against invoice (ACID Transaction)
router.post('/:id/payment', async (req, res) => {
  try {
    const { id } = req.params;
    const { amount, payment_method, category_id, reference, note, attachment, txn_date, sendEmail, sendSms } = req.body;

    if (!amount || Number(amount) <= 0) {
      return res.status(400).json({ success: false, message: 'Positive payment amount is required' });
    }

    const updatedInvoice = await recordInvoicePayment(id, {
      amount,
      payment_method,
      category_id,
      reference,
      note,
      attachment,
      txn_date
    });

    // Handle Notifications
    if (sendEmail || sendSms) {
      const invData = await getInvoiceById(id);
      if (invData) {
        let errors = [];
        const profile = await getCompanyProfile() || {};

        if (sendEmail && invData.customer_email) {
          const html = generatePaymentHtml(invData, amount, txn_date, profile);
          await notificationService.sendEmail(invData.customer_email, `Payment Receipt: ${invData.invoice_no}`, html);
        }
        
        if (sendSms && invData.customer_phone) {
          const formattedAmount = Number(amount).toLocaleString();
          const msg = `Dear ${invData.customer_name}, we received a payment of BDT ${formattedAmount} for Invoice ${invData.invoice_no}. Thank you! ${profile.company_name || 'LS SOFT'}`;
          await notificationService.sendSms(invData.customer_phone, msg);
        }
      }
    }

    res.json({ success: true, invoice: updatedInvoice, message: 'Payment recorded successfully' });
  } catch (error) {
    console.error('Error processing payment:', error);
    res.status(400).json({ success: false, message: error.message || 'Payment processing failed' });
  }
});

// POST /api/invoices/:id/notify - Send Invoice Notification (Email/SMS)
router.post('/:id/notify', async (req, res) => {
  try {
    const { id } = req.params;
    const { sendEmail, sendSms } = req.body;

    if (!sendEmail && !sendSms) {
      return res.status(400).json({ success: false, message: 'Please select at least one notification method' });
    }

    const invData = await getInvoiceById(id);
    if (!invData) {
      return res.status(404).json({ success: false, message: 'Invoice not found' });
    }

    let emailSent = false;
    let smsSent = false;
    let errors = [];

    if (sendEmail && invData.customer_email) {
      const profile = await getCompanyProfile() || {};
      const html = generateInvoiceHtml(invData, profile);
      const emailResult = await notificationService.sendEmail(invData.customer_email, `Invoice: ${invData.invoice_no}`, html);
      if (emailResult.success) {
        emailSent = true;
      } else {
        errors.push(`Email: ${emailResult.message}${emailResult.error ? ' - ' + emailResult.error : ''}`);
      }
    }

    if (sendSms && invData.customer_phone) {
      const profile = await getCompanyProfile() || {};
      const formattedDate = formatDateToLocal(invData.due_date);
      const formattedAmount = Number(invData.total_amount).toLocaleString();
      const msg = `Dear ${invData.customer_name}, Your invoice ${invData.invoice_no} (BDT ${formattedAmount}) is ready. Due: ${formattedDate}. Please pay by the due date. Thank you. ${profile.company_name || 'LS SOFT'}`;
      const smsResult = await notificationService.sendSms(invData.customer_phone, msg);
      if (smsResult.success) {
        smsSent = true;
      } else {
        errors.push(`SMS: ${smsResult.message}${smsResult.error ? ' - ' + smsResult.error : ''}`);
      }
    }

    if (errors.length > 0 && !emailSent && !smsSent) {
      return res.status(400).json({ success: false, message: 'Failed to send notifications', errors, details: { emailSent, smsSent } });
    }

    res.json({ 
      success: true, 
      message: errors.length > 0 ? 'Notifications processed with some errors' : 'Notifications processed successfully',
      errors: errors.length > 0 ? errors : undefined,
      details: { emailSent, smsSent }
    });
  } catch (error) {
    console.error('Error sending notifications:', error);
    res.status(500).json({ success: false, message: 'Failed to send notifications', error: error.message });
  }
});

module.exports = router;
