const express = require("express");
const fs = require("fs");
const path = require("path");
const { upload, multerErrorHandler } = require("../middlewares/fileUpload");
const {db}= require("../config/dbConfig");
const puppeteer = require('puppeteer'); 
const htmlPdf = require('html-pdf');
const mustache = require('mustache');
const router = express.Router();


//updating bank details to cutomer
router.post('/api/bpayment-details', upload.single('qrCode'), async (req, res) => {
  try {
      if (!req.file) {
          return res.status(400).json({
              success: false,
              message: 'QR code image is required'
          });
      }

      const {
          qrName,
          accountName,
          accountNumber,
          accountType,
          bankName,
          ifscCode
      } = req.body;

      const qrCodeUrl = `/uploads/${req.file.filename}`;

      const query = `
          INSERT INTO payment_details (
              qr_name,
              qr_code_url,
              account_name,
              account_number,
              account_type,
              bank_name,
              ifsc_code
          ) VALUES (?, ?, ?, ?, ?, ?, ?)
      `;

      db.query(
          query,
          [qrName, qrCodeUrl, accountName, accountNumber, accountType, bankName, ifscCode],
          (error, results) => {
              if (error) {
                  fs.unlinkSync(req.file.path);
                  console.error('Database error:', error);
                  return res.status(500).json({
                      success: false,
                      message: 'Error saving payment details'
                  });
              }

              res.status(201).json({
                  success: true,
                  message: 'Payment details saved successfully',
                  data: {
                      id: results.insertId,
                      qrCodeUrl
                  }
              });
          }
      );
  } catch (error) {
      if (req.file) {
          fs.unlinkSync(req.file.path);
      }
      console.error('Server error:', error);
      res.status(500).json({
          success: false,
          message: 'Internal server error'
      });
  }
});


router.get('/api/bpayments-details', (req, res) => {
  const sql = `SELECT id, qr_name, qr_code_url, account_name, account_number, account_type, bank_name, ifsc_code FROM payment_details`;

  db.query(sql, (err, results) => {
    if (err) {
      console.error('Error fetching payment details:', err);
      return res.status(500).json({ error: 'Database error' });
    }
    res.status(200).json(results); // Send the results as JSON
  });
});

// In your backend server.js or routes file
router.put('/api/bpayments-details/:id/toggle-status', (req, res) => {
  const { id } = req.params;

  const query = `
      UPDATE payment_details 
      SET is_active = CASE WHEN is_active = 1 THEN 0 ELSE 1 END
      WHERE id = ?
  `;

  db.query(query, [id], (error, result) => {
      if (error) {
          console.error('Error toggling status:', error);
          return res.status(500).json({
              success: false,
              message: 'Error updating status'
          });
      }

      // Get the updated status
      db.query('SELECT is_active FROM payment_details WHERE id = ?', [id], (err, results) => {
          if (err || !results.length) {
              return res.status(500).json({
                  success: false,
                  message: 'Error fetching updated status'
              });
          }

          res.json({
              success: true,
              message: 'Status updated successfully',
              is_active: results[0].is_active
          });
      });
  });
});
  
router.put('/api/update-bpayments-details/:id', upload.single('qr_code'), (req, res) => {
  const { id } = req.params;
  const { account_name, account_number, account_type, bank_name, ifsc_code } = req.body;

  // First get the existing QR code to delete it
  const getOldQrQuery = 'SELECT qr_code_url FROM payment_details WHERE id = ?';
  
  db.query(getOldQrQuery, [id], (error, results) => {
      if (error) {
          console.error('Error fetching old QR code:', error);
          return res.status(500).json({
              success: false,
              message: 'Error updating payment detail'
          });
      }

      // Prepare update query
      let query = `
          UPDATE payment_details 
          SET 
              account_name = ?,
              account_number = ?,
              account_type = ?,
              bank_name = ?,
              ifsc_code = ?,
              created_at = CURRENT_TIMESTAMP
      `;

      const values = [account_name, account_number, account_type, bank_name, ifsc_code];

      // If new file is uploaded, add it to the query
      if (req.file) {
          query += ', qr_code_url = ?, qr_name = ?';
          values.push('/uploads/' + req.file.filename);
          values.push(req.file.originalname);

          // Delete old QR code file if it exists
          if (results.length > 0 && results[0].qr_code_url) {
              const oldFilePath = path.join(__dirname, '..', results[0].qr_code_url);
              try {
                  if (fs.existsSync(oldFilePath)) {
                      fs.unlinkSync(oldFilePath);
                  }
              } catch (err) {
                  console.error('Error deleting old QR code:', err);
              }
          }
      }

      query += ' WHERE id = ?';
      values.push(id);

      // Execute update query
      db.query(query, values, (error, result) => {
          if (error) {
              console.error('Error updating payment detail:', error);
              // Delete newly uploaded file if update fails
              if (req.file) {
                  fs.unlinkSync(req.file.path);
              }
              return res.status(500).json({
                  success: false,
                  message: 'Error updating payment detail'
              });
          }

          if (result.affectedRows === 0) {
              // Delete newly uploaded file if no rows affected
              if (req.file) {
                  fs.unlinkSync(req.file.path);
              }
              return res.status(404).json({
                  success: false,
                  message: 'Payment detail not found'
              });
          }

          res.json({
              success: true,
              message: 'Payment detail updated successfully'
          });
      });
  });
});

router.delete('/api/delete-bpayments-details/:id', (req, res) => {
  const { id } = req.params;

  // First get the QR code URL to delete the file
  const getQrQuery = 'SELECT qr_code_url FROM payment_details WHERE id = ?';
  
  db.query(getQrQuery, [id], (error, results) => {
      if (error) {
          console.error('Error fetching QR code URL:', error);
          return res.status(500).json({
              success: false,
              message: 'Error deleting payment detail'
          });
      }

      // If QR code exists, delete the file
      if (results.length > 0 && results[0].qr_code_url) {
          // Fix the file path by going up one directory
          const filePath = path.join(__dirname, '..', results[0].qr_code_url);
          try {
              if (fs.existsSync(filePath)) {
                  fs.unlinkSync(filePath);
              }
          } catch (err) {
              console.error('Error deleting QR code file:', err);
              // Continue with deletion even if file deletion fails
          }
      }

      // Delete the record from database
      const deleteQuery = 'DELETE FROM payment_details WHERE id = ?';
      
      db.query(deleteQuery, [id], (error, result) => {
          if (error) {
              console.error('Error deleting payment detail:', error);
              return res.status(500).json({
                  success: false,
                  message: 'Error deleting payment detail'
              });
          }

          if (result.affectedRows === 0) {
              return res.status(404).json({
                  success: false,
                  message: 'Payment detail not found'
              });
          }

          res.json({
              success: true,
              message: 'Payment detail deleted successfully'
          });
      });
  });
});



router.get('/api/active-payments-details', (req, res) => {
  const sql = `
    SELECT 
      id, 
      qr_name, 
      qr_code_url, 
      account_name, 
      account_number, 
      account_type, 
      bank_name, 
      ifsc_code,
      is_active
    FROM payment_details
    WHERE is_active = 1
    ORDER BY created_at DESC
  `;

  db.query(sql, (err, results) => {
    if (err) {
      console.error('Error fetching payment details:', err);
      return res.status(500).json({ 
        success: false, 
        message: 'Database error' 
      });
    }
    res.status(200).json(results);
  });
});

//displaying payment proof to admin
// Backend Route
router.get('/api/payment-proof/:enquiryId', (req, res) => {
  const { enquiryId } = req.params;
  
  if (!enquiryId) {
    return res.status(400).json({ success: false, message: 'Enquiry ID is required' });
  }

  const query = `
    SELECT payment_proof, payment_status, updated_at
    FROM orders
    WHERE enquiry_id = ?
  `;

  db.query(query, [enquiryId], (error, results) => {
    if (error) {
      console.error('Error fetching payment proof:', error);
      return res.status(500).json({ success: false, message: 'Error fetching payment proof' });
    }

    if (results.length === 0) {
      return res.status(404).json({ success: false, message: 'Payment proof not found' });
    }

    // Normalize the payment status
    const paymentStatus = results[0].payment_status?.toLowerCase() || 'pending';
    
    res.json({
      success: true,
      paymentProof: {
        filename: results[0].payment_proof,
        status: paymentStatus,
        uploadDate: results[0].updated_at
      }
    });
  });
});
// Add this new endpoint to update payment status in the admin side
router.put('/api/payment-proof/:enquiryId/status', (req, res) => {
  const { enquiryId } = req.params;
  const { status } = req.body;
  
  if (!enquiryId || !status) {
    return res.status(400).json({ 
      success: false, 
      message: 'Enquiry ID and status are required' 
    });
  }

  const validStatuses = ['pending', 'pending_verification', 'verified', 'rejected'];
  if (!validStatuses.includes(status)) {
    return res.status(400).json({ 
      success: false, 
      message: 'Invalid status value' 
    });
  }

  const query = `
    UPDATE orders 
    SET payment_status = ?
    WHERE enquiry_id = ?
  `;

  db.query(query, [status, enquiryId], (error, results) => {
    if (error) {
      console.error('Error updating payment status:', error);
      return res.status(500).json({ 
        success: false, 
        message: 'Error updating payment status' 
      });
    }

    res.json({
      success: true,
      message: 'Payment status updated successfully',
      status: status
    });
  });
});
// Confirm Payment for chit customers
router.post("/api/uc-payments/confirm-payment", (req, res) => {
  const { chitId, paymentMonth } = req.body;

  const query = `
    UPDATE uc_payments 
    SET payment_status = 'confirmed' 
    WHERE chit_id = ? AND payment_month = ?
  `;

  db.query(query, [chitId, paymentMonth], (error, results) => {
    if (error) {
      console.error("Database error:", error);
      return res.status(500).json({ success: false, message: "Error confirming payment" });
    }

    if (results.affectedRows === 0) {
      return res.status(404).json({ success: false, message: "Payment record not found" });
    }

    res.json({ success: true, message: "Payment confirmed successfully" });
  });
});

// Upload Payment Screenshot for chit customers
router.post('/api/uc-payments', upload.single('screenshot'), async (req, res) => {
  try {
    const { chitId, paymentMonth, schemePlan } = req.body;
    const screenshot = req.file;
    const screenshotPath = `/uploads/${screenshot.filename}`;

    db.query(
      'SELECT * FROM chit_customers WHERE chit_id = ?',
      [chitId],
      async (customerErr, customerRows) => {
        if (customerErr) {
          console.error(customerErr);
          return res.status(500).json({ success: false, error: 'Database error' });
        }

        if (!customerRows.length) {
          return res.status(404).json({ success: false, error: 'Customer not found' });
        }

        db.query(
          'SELECT * FROM chit_schemes WHERE plan = ?',
          [schemePlan],
          async (schemeErr, schemeRows) => {
            if (schemeErr) {
              console.error(schemeErr);
              return res.status(500).json({ success: false, error: 'Database error' });
            }

            try {
              // Prepare invoice data
              const invoiceData = {
                invoiceNumber: chitId,
                invoiceDate: new Date().toLocaleDateString(),
                currencySymbol: "₹",
                customer: {
                  name: customerRows[0].name,
                  mobile: customerRows[0].mobile,
                  joinDate: new Date(customerRows[0].join_date).toLocaleDateString(),
                  schemePlan: schemePlan
                },
                paymentDetails: {
                  month: new Date(paymentMonth).toLocaleDateString('en-US', { month: 'long', year: 'numeric' }),
                  amount: schemeRows[0].installment_to_be_paid
                }
              };

              // Define correct paths
              const templatePath = path.join(__dirname, '../template/invoicechit.html');

              // Check if template exists
              if (!fs.existsSync(templatePath)) {
                throw new Error(`Invoice template not found at: ${templatePath}`);
              }

              // Read template
              const template = fs.readFileSync(templatePath, 'utf-8');

              // PDF generation options
              const options = {
                format: 'A4',
                border: {
                  top: "20px",
                  right: "20px",
                  bottom: "20px",
                  left: "20px"
                },
                timeout: 30000
              };

              // Generate PDF
              htmlPdf.create(mustache.render(template, invoiceData), options)
                .toBuffer(async (err, buffer) => {
                  if (err) {
                    console.error('PDF Generation Error:', err);
                    return res.status(500).json({
                      success: false,
                      message: 'Error generating PDF'
                    });
                  }

                  const formattedPaymentMonth = new Date(paymentMonth).toISOString().slice(0, 10);

                  // Save payment with PDF
                  db.query(
                    `INSERT INTO uc_payments (chit_id, scheme_plan, payment_month, 
                     screenshot_url, invoice_pdf, payment_status) VALUES (?, ?, ?, ?, ?, ?)`,
                    [chitId, schemePlan, formattedPaymentMonth, screenshotPath, buffer, 'pending'],
                    (insertErr) => {
                      if (insertErr) {
                        console.error(insertErr);
                        return res.status(500).json({ success: false, error: 'Error saving payment' });
                      }

                      res.json({
                        success: true,
                        message: 'Payment uploaded successfully',
                        data: {
                          chitId,
                          schemePlan,
                          paymentMonth: formattedPaymentMonth,
                          screenshotUrl: screenshotPath,
                          status: 'pending'
                        }
                      });
                    }
                  );
                });
            } catch (error) {
              console.error('PDF Generation Error:', error);
              res.status(500).json({ success: false, error: 'Error generating invoice' });
            }
          }
        );
      }
    );
  } catch (error) {
    console.error(error);
    res.status(500).json({ success: false, error: error.message });
  }
});


// Get invoice endpoint
router.get('/api/uc-payments/invoice/:chitId/:month', (req, res) => {
  const { chitId, month } = req.params;

  const templatePath = path.join(__dirname, '../template/invoicechit.html');
  const logoPath = path.join(__dirname, '../assets/Logo.png');

  if (!fs.existsSync(templatePath)) {
    throw new Error(`Invoice template not found at: ${templatePath}`);
  }

  const template = fs.readFileSync(templatePath, 'utf-8');
  let logoBase64 = '';

  try {
    if (fs.existsSync(logoPath)) {
      logoBase64 = `data:image/png;base64,${fs.readFileSync(logoPath).toString('base64')}`;
    }
  } catch (error) {
    console.error('Error reading logo:', error);
  }

  const query = `
    SELECT 
      uc.payment_status,
      uc.payment_month,
      uc.created_at as payment_date,
      c.name as customer_name,
      c.mobile as customer_mobile,
      c.scheme_plan,
      c.join_date,
      s.installment_to_be_paid
    FROM uc_payments uc 
    JOIN chit_customers c ON uc.chit_id = c.chit_id
    JOIN chit_schemes s ON c.scheme_plan = s.plan
    WHERE uc.chit_id = ? AND uc.payment_month = ?
  `;

  db.query(query, [chitId, month], (err, rows) => {
    if (err) {
      console.error(err);
      return res.status(500).json({ success: false, error: 'Database error' });
    }

    if (!rows.length) {
      return res.status(404).json({ success: false, error: 'Invoice not found' });
    }

    if (rows[0].payment_status !== 'confirmed') {
      return res.status(403).json({ success: false, error: 'Payment not yet confirmed' });
    }

    const monthNames = ["January", "February", "March", "April", "May", "June",
      "July", "August", "September", "October", "November", "December"
    ];
    const paymentDate = new Date(rows[0].payment_date);
    const monthInWords = monthNames[paymentDate.getMonth()];

    // Updated invoice data with payments array
    const invoiceData = {
      logoUrl:logoBase64,
      chitId: chitId,
      customerName: rows[0].customer_name,
      customerMobile: rows[0].customer_mobile,
      schemePlan: rows[0].scheme_plan,
      joinDate: new Date(rows[0].join_date).toLocaleDateString(),
      monthlyAmount: rows[0].installment_to_be_paid,
      // Add payments array for the table
      payments: [{
        paymentDate: new Date(rows[0].payment_date).toLocaleDateString(),
        month: `${monthInWords} ${paymentDate.getFullYear()}`,
        amount: rows[0].installment_to_be_paid
      }]
    };

    const options = {
      format: 'A4',
      border: {
        top: "20px",
        right: "20px",
        bottom: "20px",
        left: "20px"
      },
      timeout: 30000
    };

    htmlPdf.create(mustache.render(template, invoiceData), options)
      .toBuffer((err, buffer) => {
        if (err) {
          console.error('PDF Generation Error:', err);
          return res.status(500).json({
            success: false,
            message: 'Error generating PDF'
          });
        }

        res.set({
          'Content-Type': 'application/pdf',
          'Content-Disposition': `attachment; filename=invoice-${chitId}-${month}.pdf`,
          'Content-Length': buffer.length,
          'Cache-Control': 'no-cache'
        });
        res.send(buffer);
      });
  });
});
// Get payment status endpoint
router.get('/api/uc-payments/status/:chitId', (req, res) => {
  const { chitId } = req.params;

  db.query(
    'SELECT payment_month, payment_status FROM uc_payments WHERE chit_id = ?',
    [chitId],
    (err, rows) => {
      if (err) {
        console.error(err);
        return res.status(500).json({ 
          success: false, 
          error: 'Database error' 
        });
      }

      res.json({ success: true, data: rows });
    }
  );
});


// Get Payment Details by Chit ID for chit customer
router.get("/api/uc-payments/:chitId", (req, res) => {
  const { chitId } = req.params;

  const query = `
    SELECT chit_id, scheme_plan, payment_month, screenshot_url, payment_status, created_at 
    FROM uc_payments 
    WHERE chit_id = ? 
    ORDER BY payment_month ASC
  `;

  db.query(query, [chitId], (error, results) => {
    if (error) {
      console.error("Database error:", error);
      return res.status(500).json({ 
        success: false, 
        message: "Error fetching payment details" 
      });
    }

    // Format the screenshot URL correctly - since it's already saved with /uploads/
    const formattedResults = results.map(payment => ({
      ...payment,
      screenshot_url: payment.screenshot_url 
        ? `https://api.royaalcrackers.com${payment.screenshot_url}`  // Just prepend the host
        : null
    }));

    console.log('Formatted URLs:', formattedResults.map(r => r.screenshot_url)); // For debugging

    res.json({ 
      success: true, 
      data: formattedResults 
    });
  });
});

router.get('/api/uc-payments/status/:chitId', (req, res) => {
  const { chitId } = req.params;

  const query = `
      SELECT 
          payment_month,
          payment_status,
          screenshot_url,
          created_at
      FROM uc_payments 
      WHERE chit_id = ?
      ORDER BY payment_month ASC
  `;

  db.query(query, [chitId], (error, results) => {
      if (error) {
          console.error('Database error:', error);
          return res.status(500).json({
              success: false,
              message: 'Error fetching payment status'
          });
      }

      res.json({
          success: true,
          data: results
      });
  });
});

// Upload Payment Proof for orders
router.post("/api/upload-payment-proof", upload.single("paymentProof"), (req, res) => {
  const { enquiryId } = req.body;

  if (!req.file) {
    return res.status(400).json({ success: false, message: "No file uploaded" });
  }

  if (!enquiryId) {
    fs.unlinkSync(req.file.path);
    return res.status(400).json({ success: false, message: "Enquiry ID is required" });
  }

  const fileName = req.file.filename;
  const query = `
    UPDATE orders 
    SET payment_proof = ?, payment_status = 'pending_verification', updated_at = NOW() 
    WHERE enquiry_id = ?
  `;

  db.query(query, [fileName, enquiryId], (error, results) => {
    if (error) {
      fs.unlinkSync(req.file.path);
      console.error("Database error:", error);
      return res.status(500).json({ success: false, message: "Error saving payment proof" });
    }

    if (results.affectedRows === 0) {
      fs.unlinkSync(req.file.path);
      return res.status(404).json({ success: false, message: "Order not found" });
    }

    res.json({ success: true, message: "Payment proof uploaded successfully", fileName });
  });
});

// Get Payment Details (Latest) 
router.get("/api/payment-details", (req, res) => {
  const query = `
    SELECT * FROM payment_details 
    ORDER BY created_at DESC 
    LIMIT 1
  `;

  db.query(query, (error, results) => {
    if (error) {
      console.error("Database error:", error);
      return res.status(500).json({ success: false, message: "Error fetching payment details" });
    }

    if (results.length === 0) {
      return res.status(404).json({ success: false, message: "No payment details found" });
    }

    res.json({ success: true, data: results[0] });
  });
});

// Get All Payment Details for customer to pay
router.get("/api/payments-details", (req, res) => {
  const query = `
    SELECT id, qr_name, qr_code_url, account_name, account_number, account_type, bank_name, ifsc_code 
    FROM payment_details
  `;

  db.query(query, (error, results) => {
    if (error) {
      console.error("Database error:", error);
      return res.status(500).json({ success: false, message: "Error fetching payment details" });
    }

    res.status(200).json({ success: true, data: results });
  });
});

// Get Customer Details by Chit ID to display chit customer
router.get("/api/chit-customers/:chitId", (req, res) => {
  const { chitId } = req.params;

  const query = `
    SELECT 
      c.*, 
      cs.plan AS scheme_plan, 
      cs.installment_to_be_paid, 
      cs.you_pay, 
      cs.you_get 
    FROM chit_customers c 
    JOIN chit_schemes cs ON c.scheme_plan = cs.plan 
    WHERE c.chit_id = ?
  `;

  db.query(query, [chitId], (error, results) => {
    if (error) {
      console.error("Database error:", error);
      return res.status(500).json({ success: false, message: "Error fetching customer details" });
    }

    if (results.length === 0) {
      return res.status(404).json({ success: false, message: "No customer found with this Chit ID" });
    }

    const customerData = {
      ...results[0],
      monthlyAmount: results[0].installment_to_be_paid,
      youPay: results[0].you_pay,
      youGet: results[0].you_get,
    };

    res.json({ success: true, data: customerData });
  });
});

// Get all photos for banner
router.get('/api/photos', (req, res) => {
  const query = `
      SELECT id, title, photo_url, created_at
      FROM photos
      ORDER BY created_at DESC
  `;

  db.query(query, (err, results) => {
      if (err) {
          console.error('Error fetching photos:', err);
          return res.status(500).json({ 
              success: false, 
              message: 'Error fetching photos' 
          });
      }
      res.json(results);
  });
});

// Add new photo for banner
router.post('/api/photos', upload.single('photo'), multerErrorHandler, async (req, res) => {
  try {
      const { title } = req.body;

      if (!req.file) {
          return res.status(400).json({
              success: false,
              message: 'No photo uploaded'
          });
      }

      const photoUrl = `/uploads/${req.file.filename}`;

      const query = `
          INSERT INTO photos (title, photo_url)
          VALUES (?, ?)
      `;

      db.query(query, [title, photoUrl], (err, result) => {
          if (err) {
              // Delete uploaded file if database insert fails
              if (req.file) {
                  fs.unlinkSync(path.join(__dirname, '..', photoUrl));
              }
              console.error('Error adding photo:', err);
              return res.status(500).json({
                  success: false,
                  message: 'Error adding photo'
              });
          }

          res.status(201).json({
              success: true,
              message: 'Photo added successfully',
              id: result.insertId,
              photo_url: photoUrl
          });
      });
  } catch (error) {
      // Delete uploaded file if any error occurs
      if (req.file) {
          fs.unlinkSync(path.join(__dirname, '..', `/uploads/${req.file.filename}`));
      }
      console.error('Error:', error);
      res.status(500).json({
          success: false,
          message: 'Internal server error'
      });
  }
});

// Delete photo for banner
router.delete('/api/photos/:id', async (req, res) => {
  const { id } = req.params;

  try {
      // First get the photo URL to delete the file
      const getPhotoQuery = 'SELECT photo_url FROM photos WHERE id = ?';
      
      db.query(getPhotoQuery, [id], (error, results) => {
          if (error) {
              console.error('Error fetching photo URL:', error);
              return res.status(500).json({
                  success: false,
                  message: 'Error deleting photo'
              });
          }

          // Delete file if exists
          if (results.length > 0 && results[0].photo_url) {
              const filePath = path.join(__dirname, '..', results[0].photo_url);
              try {
                  if (fs.existsSync(filePath)) {
                      fs.unlinkSync(filePath);
                  }
              } catch (err) {
                  console.error('Error deleting photo file:', err);
              }
          }

          // Delete database record
          const deleteQuery = 'DELETE FROM photos WHERE id = ?';
          
          db.query(deleteQuery, [id], (error, result) => {
              if (error) {
                  console.error('Error deleting photo:', error);
                  return res.status(500).json({
                      success: false,
                      message: 'Error deleting photo'
                  });
              }

              res.json({
                  success: true,
                  message: 'Photo deleted successfully'
              });
          });
      });
  } catch (error) {
      console.error('Error:', error);
      res.status(500).json({
          success: false,
          message: 'Internal server error'
      });
  }
});




module.exports = router;
