import { pool } from "../config/db.js";

async function ensureProductsTable() {
  await pool.query(`
    CREATE TABLE IF NOT EXISTS products (
      id INT AUTO_INCREMENT PRIMARY KEY,
      product_code VARCHAR(50) NOT NULL UNIQUE,
      product_name VARCHAR(160) NOT NULL,
      categoria VARCHAR(100) NOT NULL DEFAULT 'General',
      stock DECIMAL(10,2) NOT NULL DEFAULT 0,
      min_stock DECIMAL(10,2) NOT NULL DEFAULT 0,
      unit_cost DECIMAL(10,2) NOT NULL DEFAULT 0,
      unit VARCHAR(30) NOT NULL DEFAULT 'unidad',
      supplier VARCHAR(160) NULL,
      is_active TINYINT(1) NOT NULL DEFAULT 1,
      created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
  `);
}

function number(value) {
  return Number(value || 0);
}

function mapProduct(row) {
  return {
    id: row.id,
    productCode: row.product_code,
    productName: row.product_name,
    category: row.categoria,
    stock: number(row.stock),
    minStock: number(row.min_stock),
    unitCost: number(row.unit_cost),
    unit: row.unit,
    supplier: row.supplier || "",
    isActive: Boolean(row.is_active),
    stockValue: number(row.stock) * number(row.unit_cost),
    createdAt: row.created_at,
    updatedAt: row.updated_at
  };
}

function validatePayload(body) {
  if (!body.productName || !body.productCode) {
    return "Nombre y codigo son requeridos";
  }
  if (Number(body.stock || 0) < 0 || Number(body.minStock || 0) < 0 || Number(body.unitCost || 0) < 0) {
    return "Stock, minimo y costo no pueden ser negativos";
  }
  return "";
}

export async function getInventory(req, res, next) {
  try {
    await ensureProductsTable();
    const { search = "", category = "", lowStock = "" } = req.query;
    const where = [];
    const params = {};

    if (search) {
      where.push("(product_name LIKE :search OR product_code LIKE :search OR supplier LIKE :search)");
      params.search = `%${search}%`;
    }
    if (category) {
      where.push("categoria = :category");
      params.category = category;
    }
    if (lowStock === "1") {
      where.push("stock <= min_stock");
    }

    const [rows] = await pool.query(
      `
      SELECT *
      FROM products
      ${where.length ? `WHERE ${where.join(" AND ")}` : ""}
      ORDER BY is_active DESC, product_name ASC
      `,
      params
    );

    const [categories] = await pool.query(
      "SELECT DISTINCT categoria AS category FROM products WHERE categoria IS NOT NULL AND categoria != '' ORDER BY categoria ASC"
    );

    const products = rows.map(mapProduct);
    const metas = products.reduce(
      (acc, product) => {
        acc.totalItems += 1;
        acc.totalStock += product.stock;
        acc.totalValue += product.stockValue;
        if (product.stock <= product.minStock) acc.lowStock += 1;
        return acc;
      },
      { totalItems: 0, totalStock: 0, totalValue: 0, lowStock: 0 }
    );

    res.json({
      success: true,
      data: products,
      categories: categories.map(row => row.category),
      metas
    });
  } catch (err) {
    next(err);
  }
}

export async function createInventoryItem(req, res, next) {
  try {
    await ensureProductsTable();
    const error = validatePayload(req.body);
    if (error) return res.status(400).json({ success: false, message: error });

    const payload = {
      productCode: String(req.body.productCode).trim().toUpperCase(),
      productName: String(req.body.productName).trim(),
      category: String(req.body.category || "General").trim() || "General",
      stock: Number(req.body.stock || 0),
      minStock: Number(req.body.minStock || 0),
      unitCost: Number(req.body.unitCost || 0),
      unit: String(req.body.unit || "unidad").trim() || "unidad",
      supplier: String(req.body.supplier || "").trim() || null,
      isActive: req.body.isActive === false ? 0 : 1
    };

    const [result] = await pool.query(
      `
      INSERT INTO products
        (product_code, product_name, categoria, stock, min_stock, unit_cost, unit, supplier, is_active, created_at)
      VALUES
        (:productCode, :productName, :category, :stock, :minStock, :unitCost, :unit, :supplier, :isActive, NOW())
      `,
      payload
    );

    const [[row]] = await pool.query("SELECT * FROM products WHERE id = ?", [result.insertId]);
    res.status(201).json({ success: true, message: "Producto creado correctamente", data: mapProduct(row) });
  } catch (err) {
    if (err.code === "ER_DUP_ENTRY") {
      return res.status(409).json({ success: false, message: "Ya existe un producto con ese codigo" });
    }
    next(err);
  }
}

export async function updateInventoryItem(req, res, next) {
  try {
    await ensureProductsTable();
    const { id } = req.params;
    const error = validatePayload(req.body);
    if (error) return res.status(400).json({ success: false, message: error });

    const [existing] = await pool.query("SELECT id FROM products WHERE id = ?", [id]);
    if (!existing.length) {
      return res.status(404).json({ success: false, message: "Producto no encontrado" });
    }

    await pool.query(
      `
      UPDATE products
      SET product_code = :productCode,
          product_name = :productName,
          categoria = :category,
          stock = :stock,
          min_stock = :minStock,
          unit_cost = :unitCost,
          unit = :unit,
          supplier = :supplier,
          is_active = :isActive
      WHERE id = :id
      `,
      {
        id,
        productCode: String(req.body.productCode).trim().toUpperCase(),
        productName: String(req.body.productName).trim(),
        category: String(req.body.category || "General").trim() || "General",
        stock: Number(req.body.stock || 0),
        minStock: Number(req.body.minStock || 0),
        unitCost: Number(req.body.unitCost || 0),
        unit: String(req.body.unit || "unidad").trim() || "unidad",
        supplier: String(req.body.supplier || "").trim() || null,
        isActive: req.body.isActive === false ? 0 : 1
      }
    );

    const [[row]] = await pool.query("SELECT * FROM products WHERE id = ?", [id]);
    res.json({ success: true, message: "Producto actualizado correctamente", data: mapProduct(row) });
  } catch (err) {
    if (err.code === "ER_DUP_ENTRY") {
      return res.status(409).json({ success: false, message: "Ya existe un producto con ese codigo" });
    }
    next(err);
  }
}

export async function adjustInventoryStock(req, res, next) {
  try {
    await ensureProductsTable();
    const { id } = req.params;
    const delta = Number(req.body.delta || 0);
    if (!delta) return res.status(400).json({ success: false, message: "Cantidad de ajuste requerida" });

    await pool.query("UPDATE products SET stock = GREATEST(stock + :delta, 0) WHERE id = :id", { id, delta });
    const [[row]] = await pool.query("SELECT * FROM products WHERE id = ?", [id]);
    if (!row) return res.status(404).json({ success: false, message: "Producto no encontrado" });

    res.json({ success: true, message: "Stock ajustado correctamente", data: mapProduct(row) });
  } catch (err) {
    next(err);
  }
}

export async function deleteInventoryItem(req, res, next) {
  try {
    await ensureProductsTable();
    const { id } = req.params;
    await pool.query("DELETE FROM products WHERE id = ?", [id]);
    res.json({ success: true, message: "Producto eliminado correctamente" });
  } catch (err) {
    next(err);
  }
}
