const express = require("express");
const {db}= require("../config/dbConfig"); 
const router = express.Router();

// Get all chit customers route
router.get("/api/chit-customers", (req, res) => {
  const query = `
    SELECT 
      id,
      chit_id,
      name,
      mobile,
      scheme_plan,
      join_date,
      created_at
    FROM chit_customers
    ORDER BY join_date DESC
  `;
  
  db.query(query, (err, results) => {
    if (err) {
      console.error("Error fetching chit customers:", err);
      return res.status(500).json({
        success: false,
        message: "Error fetching chit customers",
      });
    }
    res.json({
      success: true,
      data: results,
    });
  });
});
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 chit customers with active/inactive payment status for current month
router.get("/api/chit-uccustomers", (req, res) => {
  try {
    const query = `
      SELECT 
        c.chit_id,
        c.name,
        c.mobile,
        c.scheme_plan,
        c.join_date,
        up.created_at as payment_upload_date,
        CASE 
          WHEN up.created_at IS NOT NULL 
          AND DATE_FORMAT(up.created_at, '%Y-%m') = DATE_FORMAT(CURRENT_DATE(), '%Y-%m')
          THEN 'active'
          ELSE 'inactive'
        END as payment_status
      FROM chit_customers c
      LEFT JOIN uc_payments up ON c.chit_id = up.chit_id 
        AND DATE_FORMAT(up.created_at, '%Y-%m') = DATE_FORMAT(CURRENT_DATE(), '%Y-%m')
      ORDER BY c.created_at DESC
    `;

    db.query(query, (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 customers found",
        });
      }
      res.json({
        success: true,
        currentDateTime: new Date().toLocaleString("en-IN", {
          timeZone: "Asia/Kolkata",
          year: "numeric",
          month: "2-digit",
          day: "2-digit",
          hour: "2-digit",
          minute: "2-digit",
          second: "2-digit",
          hour12: true,
        }),
        data: results,
      });
    });
  } catch (error) {
    console.error("Server error:", error);
    res.status(500).json({
      success: false,
      message: "Internal server error",
    });
  }
});

// Add customer to chit scheme
router.post("/api/join-chit-scheme", async (req, res) => {
  const { userName, userMobile, scheme, joinDate } = req.body;

  try {
    // Generate chit ID (CH + YEAR + 5-digit sequential number)
    const year = new Date().getFullYear();
    const getLastChitQuery = `
      SELECT chit_id 
      FROM chit_customers 
      WHERE chit_id LIKE 'CH${year}%' 
      ORDER BY chit_id DESC 
      LIMIT 1
    `;

    db.query(getLastChitQuery, async (err, results) => {
      if (err) {
        console.error("Error getting last chit ID:", err);
        return res.status(500).json({ success: false, message: "Error generating chit ID" });
      }

      let chitNumber = 1;
      if (results.length > 0) {
        const lastNumber = parseInt(results[0].chit_id.slice(-5));
        chitNumber = lastNumber + 1;
      }

      const chitId = `CH${year}${chitNumber.toString().padStart(5, "0")}`;

      // Insert the customer into the chit_customers table
      const insertCustomerQuery = `
        INSERT INTO chit_customers (
          chit_id,
          name,
          mobile,
          scheme_plan,
          join_date
        ) VALUES (?, ?, ?, ?, ?)
      `;

      db.query(
        insertCustomerQuery,
        [chitId, userName, userMobile, scheme.plan, joinDate],
        (err) => {
          if (err) {
            console.error("Error inserting customer:", err);
            return res.status(500).json({ success: false, message: "Error joining scheme" });
          }

          res.status(200).json({
            success: true,
            message: "Successfully joined scheme",
            chitId,
          });
        }
      );
    });
  } catch (error) {
    console.error("Error processing scheme join:", error);
    res.status(500).json({ success: false, message: "Error processing scheme join" });
  }
});

// Add a new chit scheme
router.post("/api/add-chit-scheme", (req, res) => {
  const { plan, installment_to_be_paid, you_pay, you_get } = req.body;

  const insertChitSchemeQuery = `
    INSERT INTO chit_schemes (plan, installment_to_be_paid, you_pay, you_get)
    VALUES (?, ?, ?, ?)
  `;

  db.query(
    insertChitSchemeQuery,
    [plan, installment_to_be_paid, you_pay, you_get],
    (err) => {
      if (err) {
        console.error("Error adding chit scheme:", err);
        return res.status(500).json({
          success: false,
          message: "Error adding chit scheme",
        });
      }
      res.status(200).json({
        success: true,
        message: "Chit scheme added successfully",
      });
    }
  );
});

// Get all chit schemes
router.get("/api/chit-schemes", (req, res) => {
  db.query("SELECT * FROM chit_schemes", (err, results) => {
    if (err) {
      console.error("Error fetching chit schemes:", err);
      return res.status(500).json({
        success: false,
        message: "Server error",
      });
    }

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



module.exports = router;
