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

const VALID_TYPES = new Set(["ventas", "clientes", "servicios", "inventario", "impuestos"]);

function cleanDate(value, fallback) {
  return /^\d{4}-\d{2}-\d{2}$/.test(String(value || "")) ? value : fallback;
}

function dateRange(req) {
  const today = new Date();
  const end = cleanDate(req.query.hasta, today.toISOString().slice(0, 10));
  const startDefault = new Date(today);
  startDefault.setDate(startDefault.getDate() - 30);
  const start = cleanDate(req.query.desde, startDefault.toISOString().slice(0, 10));
  return { start, end };
}

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

async function tableExists(tableName) {
  const [rows] = await pool.query("SHOW TABLES LIKE ?", [tableName]);
  return rows.length > 0;
}

async function getSettingsMap() {
  const [rows] = await pool.query("SELECT clave, valor FROM configuracion");
  const settings = {};
  for (const row of rows) settings[row.clave] = row.valor;
  return settings;
}

function getTaxSettings(settings) {
  try {
    const parsed = JSON.parse(settings.system_taxes || "[]");
    if (Array.isArray(parsed) && parsed.length) {
      return parsed
        .map(tax => ({ name: tax.name || "Impuesto", rate: Number(tax.rate || 0) }))
        .filter(tax => tax.rate > 0);
    }
  } catch {
    // Fall through to legacy settings.
  }

  const legacyRate = Number(settings.impuesto_porcentaje || 0);
  return legacyRate > 0
    ? [{ name: settings.impuesto_descripcion || "Impuesto", rate: legacyRate }]
    : [{ name: "Impuesto", rate: 0 }];
}

async function salesReport(start, end) {
  const [orders] = await pool.query(
    `
    SELECT o.id, o.order_number, o.created_at, o.status, o.subtotal, o.tax,
           o.discount, o.total, c.full_name AS customer_name,
           p.method AS payment_method,
           p.id IS NOT NULL AS is_paid
    FROM orders o
    JOIN customers c ON c.id = o.customer_id
    LEFT JOIN (
      SELECT p1.*
      FROM payments p1
      JOIN (
        SELECT order_id, MAX(id) AS max_id
        FROM payments
        WHERE payment_status != 'void'
        GROUP BY order_id
      ) last_payment ON last_payment.max_id = p1.id
    ) p ON p.order_id = o.id
    WHERE DATE(o.created_at) BETWEEN :start AND :end
    ORDER BY o.created_at DESC, o.id DESC
    `,
    { start, end }
  );

  const metas = orders.reduce(
    (acc, order) => {
      acc.total_ventas += number(order.total);
      acc.total_subtotal += number(order.subtotal);
      acc.total_impuestos += number(order.tax);
      acc.total_descuentos += number(order.discount);
      acc.cantidad_ordenes += 1;
      if (order.is_paid) acc.pagadas += 1;
      return acc;
    },
    {
      total_ventas: 0,
      total_subtotal: 0,
      total_impuestos: 0,
      total_descuentos: 0,
      cantidad_ordenes: 0,
      pagadas: 0
    }
  );
  metas.pendientes = metas.cantidad_ordenes - metas.pagadas;

  return {
    ordenes: orders.map(order => ({
      id: order.id,
      orderNumber: order.order_number,
      customerName: order.customer_name,
      createdAt: order.created_at,
      status: order.status,
      paymentMethod: order.payment_method || "Pendiente",
      subtotal: number(order.subtotal),
      tax: number(order.tax),
      discount: number(order.discount),
      total: number(order.total),
      isPaid: Boolean(order.is_paid)
    })),
    metas
  };
}

async function customersReport(start, end) {
  const [customers] = await pool.query(
    `
    SELECT c.id, c.full_name, c.email, c.phone, c.plan, c.status,
           COUNT(o.id) AS total_orders,
           COALESCE(SUM(o.total), 0) AS total_spent
    FROM customers c
    JOIN orders o ON o.customer_id = c.id
    WHERE DATE(o.created_at) BETWEEN :start AND :end
      AND o.status != 'cancelled'
    GROUP BY c.id, c.full_name, c.email, c.phone, c.plan, c.status
    ORDER BY total_spent DESC, total_orders DESC
    `,
    { start, end }
  );

  const [[active]] = await pool.query("SELECT COUNT(*) AS total FROM customers WHERE status = 'active'");
  const [[average]] = await pool.query(
    "SELECT AVG(total) AS average_ticket FROM orders WHERE DATE(created_at) BETWEEN :start AND :end AND status != 'cancelled'",
    { start, end }
  );
  const [[recurrent]] = await pool.query(
    `
    SELECT COUNT(*) AS total
    FROM (
      SELECT customer_id
      FROM orders
      WHERE DATE(created_at) BETWEEN :start AND :end
        AND status != 'cancelled'
      GROUP BY customer_id
      HAVING COUNT(id) > 1
    ) repeated
    `,
    { start, end }
  );

  return {
    clientes: customers.map(customer => ({
      id: customer.id,
      customerName: customer.full_name,
      email: customer.email,
      phone: customer.phone,
      plan: customer.plan,
      status: customer.status,
      totalOrders: number(customer.total_orders),
      totalSpent: number(customer.total_spent)
    })),
    metas: {
      clientes_activos: number(active.total),
      promedio_gasto: number(average.average_ticket),
      clientes_recurrentes: number(recurrent.total)
    }
  };
}

async function servicesReport(start, end) {
  const [services] = await pool.query(
    `
    SELECT s.id, s.service_name, s.service_code, s.unit, s.price, s.is_active,
           COALESCE(SUM(CASE WHEN o.status != 'cancelled' THEN oi.quantity ELSE 0 END), 0) AS quantity_sold,
           COALESCE(SUM(CASE WHEN o.status != 'cancelled' THEN oi.line_total ELSE 0 END), 0) AS total_revenue
    FROM services s
    LEFT JOIN order_items oi ON oi.service_id = s.id
    LEFT JOIN orders o ON o.id = oi.order_id AND DATE(o.created_at) BETWEEN :start AND :end
    GROUP BY s.id, s.service_name, s.service_code, s.unit, s.price, s.is_active
    ORDER BY total_revenue DESC, quantity_sold DESC, s.service_name ASC
    `,
    { start, end }
  );

  const totalServices = services.filter(service => service.is_active).length;
  const totalQuantity = services.reduce((acc, service) => acc + number(service.quantity_sold), 0);
  const topService = services.find(service => number(service.total_revenue) > 0);

  return {
    servicios: services.map(service => ({
      id: service.id,
      serviceName: service.service_name,
      serviceCode: service.service_code,
      unit: service.unit,
      price: number(service.price),
      isActive: Boolean(service.is_active),
      quantitySold: number(service.quantity_sold),
      totalRevenue: number(service.total_revenue)
    })),
    metas: {
      total_servicios: totalServices,
      prendas_procesadas: totalQuantity,
      categoria_rentable: topService?.service_name || "Ninguna"
    }
  };
}

async function inventoryReport() {
  const hasProducts = await tableExists("products");
  const hasProductos = hasProducts ? false : await tableExists("productos");

  if (!hasProducts && !hasProductos) {
    return {
      productos: [],
      metas: {
        valor_total_inventario: 0,
        cantidad_productos: 0,
        alertas_bajo_stock: 0,
        unavailable: true
      }
    };
  }

  const table = hasProducts ? "products" : "productos";
  const nameCol = hasProducts ? "product_name" : "nombre";
  const codeCol = hasProducts ? "product_code" : "codigo";
  const categoryCol = "categoria";
  const stockCol = hasProducts ? "stock" : "stock_actual";
  const minCol = hasProducts ? "min_stock" : "stock_minimo";
  const costCol = hasProducts ? "unit_cost" : "costo_unitario";

  const [products] = await pool.query(`
    SELECT id, ${codeCol} AS code, ${nameCol} AS name, ${categoryCol} AS category,
           ${stockCol} AS current_stock, ${minCol} AS min_stock, ${costCol} AS unit_cost,
           (${stockCol} * ${costCol}) AS stock_value
    FROM ${table}
    ORDER BY stock_value DESC, name ASC
  `);

  return {
    productos: products.map(product => ({
      id: product.id,
      code: product.code,
      name: product.name,
      category: product.category,
      currentStock: number(product.current_stock),
      minStock: number(product.min_stock),
      unitCost: number(product.unit_cost),
      stockValue: number(product.stock_value)
    })),
    metas: {
      valor_total_inventario: products.reduce((acc, product) => acc + number(product.stock_value), 0),
      cantidad_productos: products.length,
      alertas_bajo_stock: products.filter(product => number(product.current_stock) <= number(product.min_stock)).length,
      unavailable: false
    }
  };
}

async function taxesReport(start, end) {
  const settings = await getSettingsMap();
  const taxes = getTaxSettings(settings);
  const report = await salesReport(start, end);
  const taxableBase = report.metas.total_subtotal - report.metas.total_descuentos;
  const ordenes = report.ordenes.map(order => {
    const orderBase = Math.max(number(order.subtotal) - number(order.discount), 0);
    return {
      ...order,
      taxBreakdown: taxes.map(tax => ({
        name: tax.name,
        rate: tax.rate,
        amount: Math.round(orderBase * (tax.rate / 100) * 100) / 100
      }))
    };
  });

  return {
    ordenes,
    metas: {
      ...report.metas,
      base_gravable: taxableBase,
      taxBreakdown: taxes.map(tax => ({
        name: tax.name,
        rate: tax.rate,
        amount: Math.round(taxableBase * (tax.rate / 100) * 100) / 100
      }))
    }
  };
}

export async function getReport(req, res, next) {
  try {
    const type = VALID_TYPES.has(req.query.type) ? req.query.type : "ventas";
    const { start, end } = dateRange(req);

    let data;
    if (type === "ventas") data = await salesReport(start, end);
    if (type === "clientes") data = await customersReport(start, end);
    if (type === "servicios") data = await servicesReport(start, end);
    if (type === "inventario") data = await inventoryReport();
    if (type === "impuestos") data = await taxesReport(start, end);

    res.json({
      success: true,
      data: {
        type,
        desde: start,
        hasta: end,
        ...data
      }
    });
  } catch (err) {
    next(err);
  }
}
