const express = require("express");
const multer = require("multer");
const path = require("path");
const {db}= require("../config/dbConfig");  // Assuming db.js is in the parent directory
const fs = require("fs");
const { upload, multerErrorHandler } = require("../middlewares/fileUpload");
const router = express.Router();

// Fetch all crackers
router.get('/api/crackers', (req, res) => {
  const query = `
    SELECT id, code, product_name, content, actual_price, price, quantity, photo_url, status, category_id
    FROM crackers 
    ORDER BY id DESC
  `;

  db.query(query, (err, results) => {
    if (err) {
      console.error("Error fetching crackers:", err);
      return res.status(500).json({ error: "Error fetching crackers" });
    }

    const formattedResults = results.map(product => ({
      id: product.id,
      code: product.code,
      product_name: product.product_name,
      content: product.content,
      actual_price: parseFloat(product.actual_price),
      price: parseFloat(product.price),
      quantity: parseInt(product.quantity),
      photo_url: product.photo_url,
      status: product.status === 1 || product.status === true,
      category_id: product.category_id
    }));

    res.status(200).json(formattedResults);
  });
});

router.post('/api/add-category', (req, res) => {
  const { category_name } = req.body;

  if (!category_name || typeof category_name !== 'string' || category_name.trim() === '') {
    return res.status(400).json({ message: 'Valid category name is required.' });
  }

  db.query('INSERT INTO categories (category_name) VALUES (?)', [category_name], (error, result) => {
    if (error) {
      console.error('Error adding category:', error);
      return res.status(500).json({ message: 'An unexpected error occurred.' });
    }
    
    if (result.affectedRows > 0) {
      return res.status(201).json({ 
        message: 'Category added successfully!',
        category_id: result.insertId
      });
    } else {
      return res.status(400).json({ message: 'Failed to add category.' });
    }
  });
});
router.get('/api/categories', (req, res) => {
  db.query('SELECT * FROM categories ORDER BY order_number ASC', (error, results) => {
    if (error) {
      console.error('Error fetching categories:', error);
      return res.status(500).json({ message: 'An unexpected error occurred.' });
    }
    
    // Add logging to debug
    console.log('Categories fetched:', results);
    res.status(200).json(results);
  });
});

router.put('/api/update-category/:id', (req, res) => {
  const { id } = req.params;
  const { category_name } = req.body;
  
  const query = 'UPDATE categories SET category_name = ? WHERE category_id = ?';
  
  db.query(query, [category_name, id], (error, result) => {
    if (error) {
      console.error('Error updating category:', error);
      return res.status(500).json({ message: 'An unexpected error occurred.' });
    }
    
    if (result.affectedRows === 0) {
      return res.status(404).json({ message: 'Category not found' });
    }
    
    db.query('SELECT * FROM categories WHERE category_id = ?', [id], (error, results) => {
      if (error) {
        console.error('Error fetching updated category:', error);
        return res.status(500).json({ message: 'An unexpected error occurred.' });
      }
      
      res.json(results[0]);
    });
  });
});
router.delete('/api/delete-category/:id', (req, res) => {
  const { id } = req.params;
  
  const query = 'DELETE FROM categories WHERE category_id = ?';
  
  db.query(query, [id], (error, result) => {
    if (error) {
      console.error('Error deleting category:', error);
      return res.status(500).json({ message: 'An unexpected error occurred.' });
    }
    
    if (result.affectedRows === 0) {
      return res.status(404).json({ message: 'Category not found' });
    }
    
    res.json({ message: 'Category deleted successfully' });
  });
});
router.put('/api/update-category-order/:id', async (req, res) => {
  const { id } = req.params;
  const { order_number } = req.body;
  
  // Convert order_number to integer or null
  const orderNum = order_number === '0' || order_number === 0 ? null : parseInt(order_number);

  try {
    const checkQuery = 'SELECT COUNT(*) as count FROM categories';
    db.query(checkQuery, (error, results) => {
      if (error) {
        console.error('Error checking categories:', error);
        return res.status(500).json({ message: 'An unexpected error occurred.' });
      }

      const totalCategories = results[0].count;
      
      // Validate order number if it's not null
      if (orderNum !== null && (orderNum < 1 || orderNum > totalCategories)) {
        return res.status(400).json({ message: 'Invalid order number' });
      }

      // Update query now handles null values
      const updateQuery = `
        UPDATE categories 
        SET order_number = ? 
        WHERE category_id = ?
      `;

      db.query(updateQuery, [orderNum, id], (error, result) => {
        if (error) {
          console.error('Error updating category order:', error);
          return res.status(500).json({ message: 'An unexpected error occurred.' });
        }

        if (result.affectedRows === 0) {
          return res.status(404).json({ message: 'Category not found' });
        }

        // Only reorder if setting a non-null order number
        if (orderNum !== null) {
          const reorderQuery = `
            UPDATE categories 
            SET order_number = order_number + 1 
            WHERE category_id != ? 
            AND order_number >= ? 
            AND order_number IS NOT NULL
          `;

          db.query(reorderQuery, [id, orderNum], (error) => {
            if (error) {
              console.error('Error reordering categories:', error);
              return res.status(500).json({ message: 'An unexpected error occurred.' });
            }
            res.json({ message: 'Category order updated successfully' });
          });
        } else {
          res.json({ message: 'Category order updated successfully' });
        }
      });
    });
  } catch (error) {
    console.error('Error updating category order:', error);
    res.status(500).json({ message: 'An unexpected error occurred.' });
  }
});

// Update existing cracker
router.put("/api/update-cracker/:id", upload.single('photo'), async (req, res) => {
  try {
    const { code, product_name, content, actual_price, price, quantity, category_id } = req.body;
    const photoUpdate = req.file ? `, photo_url = '/uploads/${req.file.filename}'` : '';

    const query = `
      UPDATE crackers 
      SET code = ?, 
          product_name = ?, 
          content = ?, 
          actual_price = ?, 
          price = ?, 
          quantity = ?,
          category_id = ?
          ${photoUpdate}
      WHERE id = ?
    `;

    db.query(
      query, 
      [code, product_name, content, actual_price, price, quantity, category_id, req.params.id],
      (err, result) => {
        if (err) {
          console.error("Error updating cracker:", err);
          return res.status(500).json({ error: "Error updating cracker" });
        }

        if (result.affectedRows === 0) {
          return res.status(404).json({ error: "Cracker not found" });
        }

        res.status(200).json({ 
          message: "Cracker updated successfully",
          data: {
            id: req.params.id,
            code,
            product_name,
            content,
            actual_price,
            price,
            quantity,
            category_id,
            photo_url: req.file ? `/uploads/${req.file.filename}` : undefined
          }
        });
    });
  } catch (error) {
    console.error("Error in update:", error);
    res.status(500).json({ error: "Internal server error" });
  }
});

router.put("/api/update-cracker-status/:id", async (req, res) => {
  const { id } = req.params;
  const { status } = req.body;
  
  try {
    const query = "UPDATE crackers SET status = ? WHERE id = ?";
    const newStatus = status ? 1 : 0; // Convert to tinyint values
    
    db.query(query, [newStatus, id], (err, result) => {
      if (err) {
        console.error("Error updating cracker status:", err);
        return res.status(500).json({ error: "Error updating cracker status" });
      }

      if (result.affectedRows > 0) {
        res.status(200).json({ 
          message: "Status updated successfully",
          status: newStatus === 1 ? 'active' : 'inactive' // Convert back to string for frontend
        });
      } else {
        res.status(404).json({ message: "Cracker not found" });
      }
    });
  } catch (error) {
    console.error("Error:", error);
    res.status(500).json({ error: "Internal server error" });
  }
});

router.post("/api/add-cracker", upload.single("photo"), (req, res) => {
  const { code, product_name, content, actual_price, price, quantity, status, category_id } = req.body;

  // Ensure that required fields are present
  if (!code || !product_name || !content || !actual_price || !price || !quantity || !category_id) {
    return res.status(400).json({
      success: false,
      message: "Missing required fields",
    });
  }

  // Handle file upload
  let photo_url = null;
  if (req.file) {
    photo_url = `/uploads/${req.file.filename}`;
  }

  // Query to insert cracker into the database
  const query = `
    INSERT INTO crackers (code, product_name, content, actual_price, price, quantity, photo_url, status, category_id)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
  `;

  const statusValue = status === undefined ? true : !!status;

  db.query(
    query,
    [code, product_name, content, actual_price, price, quantity, photo_url, statusValue, category_id],
    (err, results) => {
      if (err) {
        console.error("Error adding cracker:", err);
        // If there's an error with file upload or DB, remove the uploaded file
        if (req.file) {
          fs.unlinkSync(path.join(__dirname, "../uploads", req.file.filename));
        }
        return res.status(500).json({
          success: false,
          message: "Error adding cracker",
        });
      }

      res.status(201).json({
        success: true,
        message: "Cracker added successfully",
        data: {
          id: results.insertId,
          code,
          product_name,
          content,
          actual_price,
          price,
          quantity,
          photo_url,
          status: statusValue,
          category_id
        },
      });
    }
  );
});

// Delete cracker
router.delete("/api/delete-cracker/:id", async (req, res) => {
  try {
    const query = "DELETE FROM crackers WHERE id = ?";
    
    db.query(query, [req.params.id], (err, result) => {
      if (err) {
        console.error("Error deleting cracker:", err);
        return res.status(500).json({ error: "Error deleting cracker" });
      }
      res.status(200).json({ message: "Cracker deleted successfully" });
    });
  } catch (error) {
    res.status(500).json({ error: "Internal server error" });
  }
});

//To fecth and display in public
router.get('/api/products/all', (req, res) => {
  try {
    const query = `
      SELECT 
        id,
        code,
        product_name,
        content,
        actual_price,
        price,
        quantity,
        photo_url,
        status
      FROM crackers
      ORDER BY id DESC
    `;

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

      const formattedResults = results.map(product => ({
        id: product.id,
        code: product.code,
        product_name: product.product_name,
        content: product.content,
        actual_price: parseFloat(product.actual_price || 0),
        price: parseFloat(product.price || 0),
        quantity: product.quantity,
        photo_url: product.photo_url,
        status: product.status === 1 || product.status === true
      }));

      res.json(formattedResults);
    });
  } catch (error) {
    console.error('Server error:', error);
    res.status(500).json({
      success: false,
      message: 'Internal server error'
    });
  }
});

// Products route
router.get('/api/products/top5', (req, res) => {
  try {
      const query = `
          SELECT 
              id,
              code,
              product_name,
              content,
              actual_price,
              price,
              quantity,
              photo_url
          FROM crackers
          WHERE quantity > 0 
          ORDER BY id DESC
          LIMIT 10
      `;

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

          // Format the results and ensure photo_url is properly formatted
          const formattedResults = results.map(product => ({
              id: product.id,
              code: product.code,
              product_name: product.product_name,
              content: product.content,
              actual_price: parseFloat(product.actual_price || 0).toFixed(2),
              price: parseFloat(product.price || 0).toFixed(2),
              quantity: parseInt(product.quantity),
              photo_url: product.photo_url ? `/uploads/${product.photo_url.replace(/^\/uploads\//, '')}` : null
          }));

          res.status(200).json(formattedResults);
      });
  } catch (error) {
      console.error('Server error:', error);
      res.status(500).json({
          success: false,
          message: 'Internal server error'
      });
  }
});

//To calculate  produts to create graph
router.get('/api/product-sales', (req, res) => {
  const { filter } = req.query;

  // Determine the date range based on the filter
  let dateCondition = '';
  const now = new Date();

  if (filter === 'week') {
    const startOfWeek = new Date(now);
    startOfWeek.setDate(now.getDate() - now.getDay());
    startOfWeek.setHours(0, 0, 0, 0);
    dateCondition = `AND order_date >= '${startOfWeek.toISOString().slice(0, 19).replace('T', ' ')}'`;
  } else if (filter === 'month') {
    const startOfMonth = new Date(now.getFullYear(), now.getMonth(), 1);
    dateCondition = `AND order_date >= '${startOfMonth.toISOString().slice(0, 19).replace('T', ' ')}'`;
  } else if (filter === 'year') {
    const startOfYear = new Date(now.getFullYear(), 0, 1);
    dateCondition = `AND order_date >= '${startOfYear.toISOString().slice(0, 19).replace('T', ' ')}'`;
  }

  const query = `
    SELECT order_details
    FROM orders
    WHERE payment_status = 'verified' ${dateCondition};
  `;

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

    const productSales = {};

    // Process the fetched order results
    results.forEach(order => {
      try {
        const orderDetails = JSON.parse(order.order_details);
        orderDetails.forEach(item => {
          if (productSales[item.name]) {
            productSales[item.name] += item.quantity;
          } else {
            productSales[item.name] = item.quantity;
          }
        });
      } catch (error) {
        console.error("Error parsing order details:", error);
      }
    });

    try {
      // Map product names to their IDs
      const productNames = Object.keys(productSales).map(name => {
        return {
          name,
          quantity: productSales[name]
        };
      });

      if (productNames.length === 0) {
        return res.json([]); // No sales data found
      }

      // Fetch cracker names based on product IDs
      const crackerQuery = 'SELECT id, product_name FROM crackers WHERE product_name IN (?)';
      const productNamesList = productNames.map(item => item.name); // List of product names
      db.query(crackerQuery, [productNamesList], (err, crackers) => {
        if (err) {
          console.error('Error fetching cracker names:', err);
          return res.status(500).json({ message: 'Error fetching cracker names' });
        }

        // Match product sales with cracker names
        const salesData = crackers.map(cracker => {
          const product = productNames.find(item => item.name === cracker.product_name);
          return {
            name: cracker.product_name,
            quantity: product ? product.quantity : 0,
          };
        });

        return res.json(salesData);
      });
    } catch (error) {
      console.error('Error processing order details:', error);
      res.status(500).json({ message: 'Error processing order details' });
    }
  });
});

// Add this new endpoint to fetch cracker by ID
router.get("/api/crackers/:orderid", async (req, res) => {
  try {
    const { id } = req.params;
    
    const query = `
      SELECT id, code, product_name, content, actual_price, price, quantity, photo_url 
      FROM crackers 
      WHERE id = ?
    `;

    db.query(query, [id], (err, results) => {
      if (err) {
        console.error("Error fetching cracker:", err);
        return res.status(500).json({ error: "Error fetching cracker details" });
      }

      if (results.length === 0) {
        return res.status(404).json({ error: "Cracker not found" });
      }

      res.status(200).json(results[0]);
    });
  } catch (error) {
    console.error("Server error:", error);
    res.status(500).json({ error: "Internal server error" });
  }
});


// Get all products endpoint
router.get('/api/products', (req, res) => {
  const query = `
    SELECT 
      id,
      code,
      product_name,
      content,
      actual_price,
      price,
      quantity,
      photo_url,
      status
    FROM crackers
    WHERE quantity > 0
  `;

  db.query(query, (err, results) => {
    if (err) {
      console.error('Error fetching products:', err);
      return res.status(500).json({ error: 'Failed to fetch products' });
    }

    const formattedProducts = results.map(product => ({
      id: product.id,
      name: product.product_name,
      code: product.code,
      content: product.content,
      actual_price: parseFloat(product.actual_price),
      price: parseFloat(product.price),
      quantity: parseInt(product.quantity),
      photo_url: product.photo_url,
      status: product.status === 1 || product.status === true
    }));

    res.json(formattedProducts);
  });
});


module.exports = router;