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

function mapCustomer(row) {
  return {
    id: row.id,
    fullName: row.full_name,
    phone: row.phone,
    email: row.email,
    plan: row.plan,
    status: row.status,
    address: row.address,
    city: row.city,
    notes: row.notes,
    totalSpent: Number(row.total_spent || 0),
    lastOrderDate: row.last_order_date,
    createdAt: row.created_at,
    updatedAt: row.updated_at
  };
}

async function ensureWalkingCustomer() {
  const [existing] = await pool.query(
    "SELECT id FROM customers WHERE full_name = 'Walking Customer' OR phone = 'WALK-IN' ORDER BY id ASC LIMIT 1"
  );

  if (existing.length) return existing[0].id;

  const [result] = await pool.query(
    `
    INSERT INTO customers
    (full_name, phone, email, plan, status, address, city, notes, created_at)
    VALUES
    ('Walking Customer', 'WALK-IN', NULL, 'regular', 'active', NULL, NULL, 'Cliente generico para ventas de mostrador.', NOW())
    `
  );

  return result.insertId;
}

export async function getCustomerStats(req, res, next) {
  try {
    const [[totals]] = await pool.query(`
      SELECT
        COUNT(*) AS total_customers,
        SUM(status = 'active') AS active_customers,
        SUM(status = 'pending') AS pending_customers,
        SUM(status = 'inactive') AS inactive_customers,
        SUM(plan = 'vip') AS vip_customers,
        COALESCE(SUM(total_spent), 0) AS total_revenue
      FROM customers
    `);

    const [[orders]] = await pool.query(`
      SELECT COUNT(*) AS monthly_orders
      FROM orders
      WHERE created_at >= DATE_FORMAT(CURDATE(), '%Y-%m-01')
    `);

    res.json({
      success: true,
      data: {
        totalCustomers: Number(totals.total_customers || 0),
        activeCustomers: Number(totals.active_customers || 0),
        pendingCustomers: Number(totals.pending_customers || 0),
        inactiveCustomers: Number(totals.inactive_customers || 0),
        vipCustomers: Number(totals.vip_customers || 0),
        totalRevenue: Number(totals.total_revenue || 0),
        monthlyOrders: Number(orders.monthly_orders || 0)
      }
    });
  } catch (err) {
    next(err);
  }
}

export async function getCustomers(req, res, next) {
  try {
    await ensureWalkingCustomer();

    const {
      search = "",
      status = "",
      plan = "",
      page = 1,
      limit = 10
    } = req.query;

    const pageNumber = Math.max(Number(page) || 1, 1);
    const limitNumber = Math.min(Math.max(Number(limit) || 10, 1), 100);
    const offset = (pageNumber - 1) * limitNumber;

    const where = [];
    const params = {};

    if (search.trim()) {
      where.push(`(
        full_name LIKE :search OR
        phone LIKE :search OR
        email LIKE :search OR
        city LIKE :search
      )`);
      params.search = `%${search.trim()}%`;
    }

    if (status) {
      where.push("status = :status");
      params.status = status;
    }

    if (plan) {
      where.push("plan = :plan");
      params.plan = plan;
    }

    const whereSql = where.length ? `WHERE ${where.join(" AND ")}` : "";

    const [rows] = await pool.query(
      `
      SELECT *
      FROM customers
      ${whereSql}
      ORDER BY created_at DESC, id DESC
      LIMIT :limit OFFSET :offset
      `,
      {
        ...params,
        limit: limitNumber,
        offset
      }
    );

    const [[countRow]] = await pool.query(
      `
      SELECT COUNT(*) AS total
      FROM customers
      ${whereSql}
      `,
      params
    );

    res.json({
      success: true,
      data: rows.map(mapCustomer),
      pagination: {
        page: pageNumber,
        limit: limitNumber,
        total: Number(countRow.total),
        totalPages: Math.ceil(Number(countRow.total) / limitNumber)
      }
    });
  } catch (err) {
    next(err);
  }
}

export async function getCustomerById(req, res, next) {
  try {
    const { id } = req.params;

    const [rows] = await pool.query("SELECT * FROM customers WHERE id = ?", [id]);

    if (!rows.length) {
      return res.status(404).json({
        success: false,
        message: "Cliente no encontrado"
      });
    }

    res.json({
      success: true,
      data: mapCustomer(rows[0])
    });
  } catch (err) {
    next(err);
  }
}

export async function createCustomer(req, res, next) {
  try {
    const {
      fullName,
      phone,
      email,
      plan = "regular",
      status = "active",
      address,
      city,
      notes
    } = req.body;

    if (!fullName || !phone) {
      return res.status(400).json({
        success: false,
        message: "Nombre completo y teléfono son requeridos"
      });
    }

    const [result] = await pool.query(
      `
      INSERT INTO customers
      (full_name, phone, email, plan, status, address, city, notes, created_at)
      VALUES
      (:fullName, :phone, :email, :plan, :status, :address, :city, :notes, NOW())
      `,
      {
        fullName,
        phone,
        email: email || null,
        plan,
        status,
        address: address || null,
        city: city || null,
        notes: notes || null
      }
    );

    const [rows] = await pool.query("SELECT * FROM customers WHERE id = ?", [result.insertId]);

    res.status(201).json({
      success: true,
      message: "Cliente creado correctamente",
      data: mapCustomer(rows[0])
    });
  } catch (err) {
    next(err);
  }
}

export async function updateCustomer(req, res, next) {
  try {
    const { id } = req.params;
    const {
      fullName,
      phone,
      email,
      plan,
      status,
      address,
      city,
      notes
    } = req.body;

    const [existing] = await pool.query("SELECT id FROM customers WHERE id = ?", [id]);

    if (!existing.length) {
      return res.status(404).json({
        success: false,
        message: "Cliente no encontrado"
      });
    }

    await pool.query(
      `
      UPDATE customers
      SET
        full_name = :fullName,
        phone = :phone,
        email = :email,
        plan = :plan,
        status = :status,
        address = :address,
        city = :city,
        notes = :notes
      WHERE id = :id
      `,
      {
        id,
        fullName,
        phone,
        email: email || null,
        plan,
        status,
        address: address || null,
        city: city || null,
        notes: notes || null
      }
    );

    const [rows] = await pool.query("SELECT * FROM customers WHERE id = ?", [id]);

    res.json({
      success: true,
      message: "Cliente actualizado correctamente",
      data: mapCustomer(rows[0])
    });
  } catch (err) {
    next(err);
  }
}

export async function deleteCustomer(req, res, next) {
  try {
    const { id } = req.params;

    const [existing] = await pool.query("SELECT id FROM customers WHERE id = ?", [id]);

    if (!existing.length) {
      return res.status(404).json({
        success: false,
        message: "Cliente no encontrado"
      });
    }

    await pool.query("DELETE FROM customers WHERE id = ?", [id]);

    res.json({
      success: true,
      message: "Cliente eliminado correctamente"
    });
  } catch (err) {
    next(err);
  }
}
