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

async function ensureSuppliersTable() {
  await pool.query(`
    CREATE TABLE IF NOT EXISTS suppliers (
      id INT AUTO_INCREMENT PRIMARY KEY,
      supplier_code VARCHAR(50) NOT NULL UNIQUE,
      name VARCHAR(160) NOT NULL,
      contact_name VARCHAR(120) NULL,
      phone VARCHAR(40) NULL,
      email VARCHAR(120) NULL,
      category VARCHAR(100) NOT NULL DEFAULT 'General',
      address VARCHAR(255) NULL,
      notes TEXT NULL,
      status VARCHAR(20) NOT NULL DEFAULT 'active',
      created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
  `);
}

function mapSupplier(row) {
  return {
    id: row.id,
    supplierCode: row.supplier_code,
    name: row.name,
    contactName: row.contact_name || "",
    phone: row.phone || "",
    email: row.email || "",
    category: row.category || "General",
    address: row.address || "",
    notes: row.notes || "",
    status: row.status || "active",
    createdAt: row.created_at,
    updatedAt: row.updated_at
  };
}

function validateSupplier(body) {
  if (!body.name || !String(body.name).trim()) return "Nombre del suplidor requerido";
  if (!body.supplierCode || !String(body.supplierCode).trim()) return "Codigo del suplidor requerido";
  return "";
}

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

    if (search) {
      where.push("(supplier_code LIKE :search OR name LIKE :search OR contact_name LIKE :search OR phone LIKE :search OR email LIKE :search)");
      params.search = `%${search}%`;
    }
    if (status) {
      where.push("status = :status");
      params.status = status;
    }
    if (category) {
      where.push("category = :category");
      params.category = category;
    }

    const [rows] = await pool.query(
      `SELECT * FROM suppliers ${where.length ? `WHERE ${where.join(" AND ")}` : ""} ORDER BY status ASC, name ASC`,
      params
    );
    const [categories] = await pool.query("SELECT DISTINCT category FROM suppliers WHERE category IS NOT NULL AND category != '' ORDER BY category ASC");

    const data = rows.map(mapSupplier);
    const metas = data.reduce((acc, supplier) => {
      acc.total += 1;
      if (supplier.status === "active") acc.active += 1;
      if (supplier.status === "inactive") acc.inactive += 1;
      if (supplier.status === "pending") acc.pending += 1;
      return acc;
    }, { total: 0, active: 0, inactive: 0, pending: 0 });

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

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

    const payload = {
      supplierCode: String(req.body.supplierCode).trim().toUpperCase(),
      name: String(req.body.name).trim(),
      contactName: String(req.body.contactName || "").trim() || null,
      phone: String(req.body.phone || "").trim() || null,
      email: String(req.body.email || "").trim() || null,
      category: String(req.body.category || "General").trim() || "General",
      address: String(req.body.address || "").trim() || null,
      notes: String(req.body.notes || "").trim() || null,
      status: String(req.body.status || "active").trim() || "active"
    };

    const [result] = await pool.query(
      `INSERT INTO suppliers
        (supplier_code, name, contact_name, phone, email, category, address, notes, status, created_at)
       VALUES
        (:supplierCode, :name, :contactName, :phone, :email, :category, :address, :notes, :status, NOW())`,
      payload
    );

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

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

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

    await pool.query(
      `UPDATE suppliers
       SET supplier_code = :supplierCode,
           name = :name,
           contact_name = :contactName,
           phone = :phone,
           email = :email,
           category = :category,
           address = :address,
           notes = :notes,
           status = :status
       WHERE id = :id`,
      {
        id,
        supplierCode: String(req.body.supplierCode).trim().toUpperCase(),
        name: String(req.body.name).trim(),
        contactName: String(req.body.contactName || "").trim() || null,
        phone: String(req.body.phone || "").trim() || null,
        email: String(req.body.email || "").trim() || null,
        category: String(req.body.category || "General").trim() || "General",
        address: String(req.body.address || "").trim() || null,
        notes: String(req.body.notes || "").trim() || null,
        status: String(req.body.status || "active").trim() || "active"
      }
    );

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

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