const db = require('../config/db');
const { logAuditFromReq } = require('../middlewares/audit');
const fs = require('fs');
const path = require('path');

exports.getAll = async (req, res, next) => {
  try {
    const { category_id, status, search } = req.query;
    let sql = `
      SELECT p.*, c.name AS category_name
      FROM products p
      JOIN categories c ON c.id = p.category_id
      WHERE 1=1
    `;
    const params = [];

    if (category_id) {
      params.push(category_id);
      sql += ' AND p.category_id = ?';
    }
    if (status) {
      params.push(status);
      sql += ' AND p.status = ?';
    }
    if (search) {
      const like = `%${search}%`;
      params.push(like, like);
      sql += ' AND (p.name LIKE ? OR p.sku LIKE ?)';
    }

    sql += ' ORDER BY p.id DESC';
    const [rows] = await db.query(sql, params);
    res.json(rows);
  } catch (err) {
    next(err);
  }
};

exports.getById = async (req, res, next) => {
  try {
    const [rows] = await db.query(
      `SELECT p.*, c.name AS category_name
       FROM products p JOIN categories c ON c.id = p.category_id
       WHERE p.id = ?`,
      [req.params.id]
    );
    if (rows.length === 0) return res.status(404).json({ error: 'Product not found' });
    res.json(rows[0]);
  } catch (err) {
    next(err);
  }
};

exports.create = async (req, res, next) => {
  try {
    let {
      name, sku, variant, category_id,
      selling_price, cost_price, stock_level,
      low_stock_threshold, status,
    } = req.body;

    // Validate required fields
    if (!name || !category_id) {
      return res.status(400).json({ error: 'name and category_id are required' });
    }

    // Auto-generate SKU if not provided
    if (!sku || !sku.trim()) {
      sku = `AUTO-${Date.now()}`;
    }

    // Convert numeric fields from strings to numbers safely
    const stockLevelNum = Number(stock_level ?? 0); // default 0
    const lowStockThresholdNum = Number(low_stock_threshold ?? 5); // default 5
    const sellingPriceNum = Number(selling_price ?? 0);
    const costPriceNum = Number(cost_price ?? 0);

    // Handle image upload
    let image_url = null;
    if (req.file) {
      image_url = `/api/uploads/products/${req.file.filename}`;
    }

    // Compute product status based on stock levels
    const computedStatus = stockLevelNum === 0 ? 'out_of_stock' : (stockLevelNum <= lowStockThresholdNum ? 'low_stock' : (status || 'active'));

    // Insert product into database
    const [result] = await db.query(
      `INSERT INTO products
        (name, sku, variant, category_id, selling_price, cost_price,
         stock_level, low_stock_threshold, status, image_url)
       VALUES (?,?,?,?,?,?,?,?,?,?)`,
      [
        name,
        sku,
        variant || null,
        category_id,
        sellingPriceNum,
        costPriceNum,
        stockLevelNum,
        lowStockThresholdNum,
        computedStatus,
        image_url,
      ]
    );

    // Record initial stock movement if stock > 0
    if (stockLevelNum > 0) {
      await db.query(
        "INSERT INTO stock_movements (product_id, quantity_change, reason, user_id) VALUES (?, ?, 'restock', ?)",
        [result.insertId, stockLevelNum, req.user.id]
      );
    }

    // Fetch newly created product
    const [rows] = await db.query('SELECT * FROM products WHERE id = ?', [result.insertId]);

    // Log audit
    logAuditFromReq(req, 'create', 'product', result.insertId, {
      name,
      sku,
      stock_level: stockLevelNum,
    });

    res.status(201).json(rows[0]);
  } catch (err) {
    next(err);
  }
};


exports.update = async (req, res, next) => {
  try {
    const {
      name, sku, variant, category_id,
      selling_price, cost_price, stock_level,
      low_stock_threshold, status,
    } = req.body;

    // Handle image upload
    let image_url = undefined;
    if (req.file) {
      image_url = `/api/uploads/products/${req.file.filename}`;
      // Delete old image if exists
      const [existing] = await db.query('SELECT image_url FROM products WHERE id = ?', [req.params.id]);
      if (existing.length > 0 && existing[0].image_url) {
        const oldPath = path.join(__dirname, '..', '..', existing[0].image_url);
        if (fs.existsSync(oldPath)) fs.unlinkSync(oldPath);
      }
    }

    const [result] = await db.query(
      `UPDATE products SET
         name                = COALESCE(?, name),
         sku                 = COALESCE(?, sku),
         variant             = COALESCE(?, variant),
         category_id         = COALESCE(?, category_id),
         selling_price       = COALESCE(?, selling_price),
         cost_price          = COALESCE(?, cost_price),
         stock_level         = COALESCE(?, stock_level),
         low_stock_threshold = COALESCE(?, low_stock_threshold),
         status              = COALESCE(?, status),
         image_url           = COALESCE(?, image_url)
       WHERE id = ?`,
      [
        name, sku, variant, category_id,
        selling_price, cost_price, stock_level,
        low_stock_threshold, status, image_url,
        req.params.id,
      ]
    );
    if (result.affectedRows === 0) return res.status(404).json({ error: 'Product not found' });

    const [rows] = await db.query('SELECT * FROM products WHERE id = ?', [req.params.id]);
    const product = rows[0];

    // Auto-update status based on stock level if stock_level was provided
    if (stock_level != null) {
      const newStatus = product.stock_level === 0 ? 'out_of_stock' : (product.stock_level <= product.low_stock_threshold ? 'low_stock' : 'active');
      if (newStatus !== product.status) {
        await db.query('UPDATE products SET status = ? WHERE id = ?', [newStatus, product.id]);
        product.status = newStatus;
      }
    }

    logAuditFromReq(req, 'update', 'product', parseInt(req.params.id, 10), {
      fields_updated: Object.keys(req.body),
    });

    res.json(product);
  } catch (err) {
    next(err);
  }
};

exports.remove = async (req, res, next) => {
  try {
    const [result] = await db.query(
      "UPDATE products SET status = 'inactive' WHERE id = ?",
      [req.params.id]
    );
    if (result.affectedRows === 0) return res.status(404).json({ error: 'Product not found' });

    logAuditFromReq(req, 'deactivate', 'product', parseInt(req.params.id, 10));

    res.json({ message: 'Product deactivated' });
  } catch (err) {
    next(err);
  }
};

exports.adjustStock = async (req, res, next) => {
  try {
    const { quantity_change, reason } = req.body;
    if (quantity_change == null || !reason) {
      return res.status(400).json({ error: 'quantity_change and reason are required' });
    }

    const [result] = await db.query(
      'UPDATE products SET stock_level = stock_level + ? WHERE id = ?',
      [quantity_change, req.params.id]
    );
    if (result.affectedRows === 0) return res.status(404).json({ error: 'Product not found' });

    // Auto-update status based on threshold
    const [rows] = await db.query('SELECT * FROM products WHERE id = ?', [req.params.id]);
    const product = rows[0];
    const newStatus = product.stock_level === 0 ? 'out_of_stock' : (product.stock_level <= product.low_stock_threshold ? 'low_stock' : 'active');
    if (newStatus !== product.status) {
      await db.query('UPDATE products SET status = ? WHERE id = ?', [newStatus, product.id]);
      product.status = newStatus;
    }

    // Record movement
    await db.query(
      'INSERT INTO stock_movements (product_id, quantity_change, reason, user_id) VALUES (?, ?, ?, ?)',
      [product.id, quantity_change, reason, req.user.id]
    );

    logAuditFromReq(req, 'stock_adjust', 'product', product.id, {
      quantity_change, reason, new_stock_level: product.stock_level,
    });

    res.json(product);
  } catch (err) {
    next(err);
  }
};

exports.getLowStock = async (_req, res, next) => {
  try {
    const [rows] = await db.query(
      `SELECT p.*, c.name AS category_name
       FROM products p JOIN categories c ON c.id = p.category_id
       WHERE p.stock_level <= p.low_stock_threshold AND p.status != 'inactive'
       ORDER BY p.stock_level ASC`
    );
    res.json(rows);
  } catch (err) {
    next(err);
  }
};
