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

function mapOrder(row) {
  return {
    id: row.id,
    orderNumber: row.order_number,
    customerId: row.customer_id,
    customerName: row.customer_name,
    customerPhone: row.customer_phone,
    customerEmail: row.customer_email,
    status: row.status,
    pickupDate: row.pickup_date,
    deliveryDate: row.delivery_date,
    subtotal: Number(row.subtotal || 0),
    tax: Number(row.tax || 0),
    discount: Number(row.discount || 0),
    total: Number(row.total || 0),
    notes: row.notes,
    isPaid: Boolean(row.is_paid),
    createdAt: row.created_at,
    updatedAt: row.updated_at
  };
}

async function hasOpenCashRegister(connection = pool) {
  const [rows] = await connection.query(
    "SELECT id FROM cash_register_sessions WHERE status = 'open' ORDER BY opened_at DESC, id DESC LIMIT 1"
  );
  return rows.length > 0;
}

async function ensureOrderItemTrackingColumns(connection = pool) {
  const [rows] = await connection.query(`
    SELECT COLUMN_NAME
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'order_items'
      AND COLUMN_NAME IN ('tag_code', 'garment_count')
  `);
  const columns = new Set(rows.map(row => row.COLUMN_NAME));
  if (!columns.has("tag_code")) {
    await connection.query("ALTER TABLE order_items ADD COLUMN tag_code VARCHAR(40) NULL AFTER descripcion_item");
  }
  if (!columns.has("garment_count")) {
    await connection.query("ALTER TABLE order_items ADD COLUMN garment_count INT NOT NULL DEFAULT 1 AFTER tag_code");
  }

  const [missingTags] = await connection.query(`
    SELECT oi.id, o.order_number
    FROM order_items oi
    JOIN orders o ON o.id = oi.order_id
    WHERE oi.tag_code IS NULL OR oi.tag_code = ''
    ORDER BY oi.order_id ASC, oi.id ASC
  `);
  const counters = new Map();
  for (const row of missingTags) {
    const current = counters.get(row.order_number) || 0;
    const next = current + 1;
    counters.set(row.order_number, next);
    await connection.query(
      "UPDATE order_items SET tag_code = ? WHERE id = ?",
      [buildGarmentTag(row.order_number, next - 1), row.id]
    );
  }

  await connection.query(`
    UPDATE order_items oi
    JOIN services s ON s.id = oi.service_id
    SET oi.garment_count = CASE
      WHEN s.unit = 'lb' THEN 1
      ELSE GREATEST(1, CEIL(oi.quantity))
    END
    WHERE oi.garment_count IS NULL OR oi.garment_count <= 0
  `);
}

function buildGarmentTag(orderNumber, index) {
  return `${orderNumber}-${String(index + 1).padStart(2, "0")}`;
}

export async function getDashboardStats(req, res, next) {
  try {
    // Today's orders count and revenue
    const [[today]] = await pool.query(`
      SELECT
        COUNT(*) AS orders_today,
        COALESCE(SUM(total), 0) AS revenue_today
      FROM orders
      WHERE DATE(created_at) = CURDATE()
    `);

    // This week
    const [[week]] = await pool.query(`
      SELECT
        COUNT(*) AS orders_week,
        COALESCE(SUM(total), 0) AS revenue_week
      FROM orders
      WHERE YEARWEEK(created_at, 1) = YEARWEEK(CURDATE(), 1)
    `);

    // This month
    const [[month]] = await pool.query(`
      SELECT
        COUNT(*) AS orders_month,
        COALESCE(SUM(total), 0) AS revenue_month
      FROM orders
      WHERE created_at >= DATE_FORMAT(CURDATE(), '%Y-%m-01')
    `);

    // All time
    const [[allTime]] = await pool.query(`
      SELECT
        COUNT(*) AS orders_total,
        COALESCE(SUM(total), 0) AS revenue_total
      FROM orders
    `);

    // Orders by status
    const [statusRows] = await pool.query(`
      SELECT status, COUNT(*) AS count
      FROM orders
      GROUP BY status
    `);
    const byStatus = {};
    for (const r of statusRows) byStatus[r.status] = Number(r.count);

    // Pending payment (no payment record)
    const [[pending]] = await pool.query(`
      SELECT COUNT(*) AS count, COALESCE(SUM(o.total), 0) AS amount
      FROM orders o
      LEFT JOIN payments p ON p.order_id = o.id
      WHERE p.id IS NULL AND o.status != 'cancelled'
    `);

    // Recent 8 orders
    const [recent] = await pool.query(`
      SELECT o.id, o.order_number, o.status, o.total, o.created_at,
             c.full_name AS customer_name,
             (SELECT COUNT(*) FROM payments WHERE order_id = o.id) > 0 AS is_paid
      FROM orders o
      JOIN customers c ON o.customer_id = c.id
      ORDER BY o.created_at DESC, o.id DESC
      LIMIT 8
    `);

    // Last 7 days revenue per day
    const [dailyRevenue] = await pool.query(`
      SELECT DATE(created_at) AS day, COALESCE(SUM(total),0) AS revenue, COUNT(*) AS orders
      FROM orders
      WHERE created_at >= DATE_SUB(CURDATE(), INTERVAL 6 DAY)
      GROUP BY DATE(created_at)
      ORDER BY day ASC
    `);

    res.json({
      success: true,
      data: {
        today: {
          orders: Number(today.orders_today),
          revenue: Number(today.revenue_today)
        },
        week: {
          orders: Number(week.orders_week),
          revenue: Number(week.revenue_week)
        },
        month: {
          orders: Number(month.orders_month),
          revenue: Number(month.revenue_month)
        },
        allTime: {
          orders: Number(allTime.orders_total),
          revenue: Number(allTime.revenue_total)
        },
        byStatus,
        pendingPayment: {
          count: Number(pending.count),
          amount: Number(pending.amount)
        },
        recentOrders: recent.map(r => ({
          id: r.id,
          orderNumber: r.order_number,
          customerName: r.customer_name,
          status: r.status,
          total: Number(r.total),
          isPaid: Boolean(r.is_paid),
          createdAt: r.created_at
        })),
        dailyRevenue: dailyRevenue.map(r => ({
          day: r.day,
          revenue: Number(r.revenue),
          orders: Number(r.orders)
        }))
      }
    });
  } catch (err) {
    next(err);
  }
}

export async function getOrders(req, res, next) {
  try {
    await ensureOrderItemTrackingColumns();
    const { status = "", search = "" } = req.query;

    let query = `
      SELECT o.*, c.full_name AS customer_name,
             (SELECT COUNT(*) FROM payments WHERE order_id = o.id) > 0 AS is_paid
      FROM orders o
      JOIN customers c ON o.customer_id = c.id
    `;
    const where = [];
    const params = {};

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

    if (search.trim()) {
      where.push("(o.order_number LIKE :search OR c.full_name LIKE :search)");
      params.search = `%${search.trim()}%`;
    }

    if (where.length) {
      query += ` WHERE ${where.join(" AND ")}`;
    }

    query += " ORDER BY o.created_at DESC, o.id DESC";

    const [rows] = await pool.query(query, params);

    res.json({
      success: true,
      data: rows.map(mapOrder)
    });
  } catch (err) {
    next(err);
  }
}

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

    const [rows] = await pool.query(
      `
      SELECT o.*, c.full_name AS customer_name, c.phone AS customer_phone, c.email AS customer_email,
             (SELECT COUNT(*) FROM payments WHERE order_id = o.id) > 0 AS is_paid
      FROM orders o
      JOIN customers c ON o.customer_id = c.id
      WHERE o.id = ?
      `,
      [id]
    );

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

    const [items] = await pool.query(
      `
      SELECT oi.*, s.service_name, s.unit
      FROM order_items oi
      JOIN services s ON oi.service_id = s.id
      WHERE oi.order_id = ?
      `,
      [id]
    );

    const order = mapOrder(rows[0]);
    order.items = items.map(item => ({
      id: item.id,
      serviceId: item.service_id,
      serviceName: item.service_name,
      unit: item.unit,
      quantity: Number(item.quantity || 0),
      unitPrice: Number(item.unit_price || 0),
      lineTotal: Number(item.line_total || 0),
      descripcion_item: item.descripcion_item || "",
      tagCode: item.tag_code || "",
      garmentCount: Number(item.garment_count || 1)
    }));

    res.json({
      success: true,
      data: order
    });
  } catch (err) {
    next(err);
  }
}

export async function createOrder(req, res, next) {
  const connection = await pool.getConnection();
  try {
    await connection.beginTransaction();
    await ensureOrderItemTrackingColumns(connection);

    const {
      customerId,
      status = "received",
      pickupDate,
      deliveryDate,
      discount = 0,
      notes,
      paymentMethod = "cash",
      isPaid = false,
      items = []
    } = req.body;

    if (!(await hasOpenCashRegister(connection))) {
      await connection.rollback();
      return res.status(400).json({
        success: false,
        message: "Debes abrir la caja antes de crear ordenes"
      });
    }

    if (!customerId) {
      await connection.rollback();
      return res.status(400).json({
        success: false,
        message: "El cliente es requerido"
      });
    }

    // Calculate totals
    let subtotal = 0;
    for (const item of items) {
      subtotal += Number(item.quantity || 1) * Number(item.unitPrice || 0);
    }
    const tax = req.body.tax !== undefined ? Number(req.body.tax) : Math.round((subtotal - discount) * 0.18 * 100) / 100;
    const total = req.body.total !== undefined ? Number(req.body.total) : Math.max(subtotal - discount + tax, 0);

    const orderNumber = `ORD-${Date.now().toString().slice(-6)}${Math.floor(Math.random() * 10)}`;

    const [orderResult] = await connection.query(
      `
      INSERT INTO orders
      (order_number, customer_id, status, pickup_date, delivery_date, subtotal, tax, discount, total, notes, created_at)
      VALUES
      (:orderNumber, :customerId, :status, :pickupDate, :deliveryDate, :subtotal, :tax, :discount, :total, :notes, NOW())
      `,
      {
        orderNumber,
        customerId,
        status,
        pickupDate: pickupDate || null,
        deliveryDate: deliveryDate || null,
        subtotal,
        tax,
        discount,
        total,
        notes: notes || null
      }
    );

    const orderId = orderResult.insertId;

    // Insert items
    for (const [index, item] of items.entries()) {
      const lineTotal = Number(item.quantity || 1) * Number(item.unitPrice || 0);
      const tagCode = item.tagCode || buildGarmentTag(orderNumber, index);
      const garmentCount = Math.max(1, Math.ceil(Number(item.garmentCount || item.quantity || 1)));
      await connection.query(
        `
        INSERT INTO order_items (order_id, service_id, quantity, unit_price, line_total, descripcion_item, tag_code, garment_count)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?)
        `,
        [orderId, item.serviceId, item.quantity, item.unitPrice, lineTotal, item.description || null, tagCode, garmentCount]
      );
    }

    if (status === "delivered" || isPaid === true) {
      await connection.query(
        `INSERT INTO payments (order_id, amount, method, payment_status, paid_at)
         VALUES (?, ?, ?, 'paid', NOW())`,
        [orderId, total, paymentMethod]
      );
      await connection.query(
        `UPDATE customers SET total_spent = total_spent + ? WHERE id = ?`,
        [total, customerId]
      );
      // Earn loyalty points
      await earnPointsForOrder(connection, customerId, orderId, total);
    }

    await connection.commit();

    res.status(201).json({
      success: true,
      message: "Orden creada correctamente",
      data: {
        id: orderId,
        orderNumber,
        total
      }
    });
  } catch (err) {
    await connection.rollback();
    next(err);
  } finally {
    connection.release();
  }
}

export async function updateOrderStatus(req, res, next) {
  try {
    const { id } = req.params;
    const { status, paymentMethod = "cash" } = req.body;

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

    const oldStatus = existing[0].status;
    const customerId = existing[0].customer_id;
    const total = Number(existing[0].total);
    const [existingPayment] = await pool.query("SELECT id FROM payments WHERE order_id = ?", [id]);
    const isAlreadyPaid = existingPayment.length > 0;

    if (status === "delivered" && oldStatus !== "delivered" && !isAlreadyPaid && !(await hasOpenCashRegister())) {
      return res.status(400).json({
        success: false,
        message: "Debes abrir la caja antes de entregar y registrar el pago"
      });
    }

    await pool.query(
      "UPDATE orders SET status = ? WHERE id = ?",
      [status, id]
    );

    // If status transitions to 'delivered' and wasn't already delivered, and is not already paid
    if (status === "delivered" && oldStatus !== "delivered" && !isAlreadyPaid) {
      // Create payment record
      await pool.query(
        `
        INSERT INTO payments (order_id, amount, method, payment_status, paid_at)
        VALUES (?, ?, ?, 'paid', NOW())
        `,
        [id, total, paymentMethod]
      );

      // Update customer total spent
      await pool.query(
        `
        UPDATE customers
        SET total_spent = total_spent + ?, last_order_date = CURDATE()
        WHERE id = ?
        `,
        [total, customerId]
      );
      // Earn loyalty points
      await earnPointsForOrder(pool, customerId, id, total);
    }

    res.json({
      success: true,
      message: "Estado de orden actualizado correctamente"
    });
  } catch (err) {
    next(err);
  }
}

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

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

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

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

export async function payOrder(req, res, next) {
  try {
    const { id } = req.params;
    const { paymentMethod = "cash" } = req.body;

    if (!(await hasOpenCashRegister())) {
      return res.status(400).json({
        success: false,
        message: "Debes abrir la caja antes de registrar pagos"
      });
    }

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

    const order = existing[0];
    
    // Check if already paid
    const [existingPayment] = await pool.query("SELECT id FROM payments WHERE order_id = ?", [id]);
    if (existingPayment.length > 0) {
      return res.status(400).json({
        success: false,
        message: "La orden ya está pagada"
      });
    }

    // Insert payment record
    await pool.query(
      `
      INSERT INTO payments (order_id, amount, method, payment_status, paid_at)
      VALUES (?, ?, ?, 'paid', NOW())
      `,
      [id, order.total, paymentMethod]
    );

    // Update customer total spent
    await pool.query(
      `
      UPDATE customers
      SET total_spent = total_spent + ?, last_order_date = CURDATE()
      WHERE id = ?
      `,
      [order.total, order.customer_id]
    );

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

export async function updateOrder(req, res, next) {
  const connection = await pool.getConnection();
  try {
    await connection.beginTransaction();
    await ensureOrderItemTrackingColumns(connection);

    const { id } = req.params;
    const {
      customerId,
      pickupDate,
      deliveryDate,
      discount = 0,
      notes,
      items = []
    } = req.body;

    if (!customerId) {
      return res.status(400).json({
        success: false,
        message: "El cliente es requerido"
      });
    }

    // 1. Fetch old order details
    const [oldOrderRows] = await connection.query("SELECT * FROM orders WHERE id = ?", [id]);
    if (!oldOrderRows.length) {
      return res.status(404).json({
        success: false,
        message: "Orden no encontrada"
      });
    }
    const oldOrder = oldOrderRows[0];
    const oldTotal = Number(oldOrder.total);
    const oldCustomerId = oldOrder.customer_id;

    // 2. Calculate totals
    let subtotal = 0;
    for (const item of items) {
      subtotal += Number(item.quantity || 1) * Number(item.unitPrice || 0);
    }
    const tax = req.body.tax !== undefined ? Number(req.body.tax) : Math.round((subtotal - discount) * 0.18 * 100) / 100;
    const total = req.body.total !== undefined ? Number(req.body.total) : Math.max(subtotal - discount + tax, 0);

    // 3. Update order metadata
    await connection.query(
      `
      UPDATE orders
      SET customer_id = :customerId,
          pickup_date = :pickupDate,
          delivery_date = :deliveryDate,
          subtotal = :subtotal,
          tax = :tax,
          discount = :discount,
          total = :total,
          notes = :notes
      WHERE id = :id
      `,
      {
        customerId,
        pickupDate: pickupDate || null,
        deliveryDate: deliveryDate || null,
        subtotal,
        tax,
        discount,
        total,
        notes: notes || null,
        id
      }
    );

    // 4. Delete old items
    await connection.query("DELETE FROM order_items WHERE order_id = ?", [id]);

    // 5. Insert new items
    for (const [index, item] of items.entries()) {
      const lineTotal = Number(item.quantity || 1) * Number(item.unitPrice || 0);
      const tagCode = item.tagCode || buildGarmentTag(oldOrder.order_number, index);
      const garmentCount = Math.max(1, Math.ceil(Number(item.garmentCount || item.quantity || 1)));
      await connection.query(
        `
        INSERT INTO order_items (order_id, service_id, quantity, unit_price, line_total, descripcion_item, tag_code, garment_count)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?)
        `,
        [id, item.serviceId, item.quantity, item.unitPrice, lineTotal, item.description || null, tagCode, garmentCount]
      );
    }

    // 6. Check if payment exists and update totals
    const [existingPayment] = await connection.query("SELECT id FROM payments WHERE order_id = ?", [id]);
    const isPaid = existingPayment.length > 0;

    if (isPaid) {
      // Update payment record amount
      await connection.query(
        "UPDATE payments SET amount = ? WHERE order_id = ?",
        [total, id]
      );

      // Adjust customer total spent
      if (Number(oldCustomerId) === Number(customerId)) {
        // Same customer: subtract old total, add new total
        const diff = total - oldTotal;
        await connection.query(
          "UPDATE customers SET total_spent = total_spent + ? WHERE id = ?",
          [diff, customerId]
        );
      } else {
        // Different customer: subtract from old customer, add to new customer
        await connection.query(
          "UPDATE customers SET total_spent = total_spent - ? WHERE id = ?",
          [oldTotal, oldCustomerId]
        );
        await connection.query(
          "UPDATE customers SET total_spent = total_spent + ? WHERE id = ?",
          [total, customerId]
        );
      }
    }

    await connection.commit();

    res.json({
      success: true,
      message: "Orden actualizada correctamente",
      data: {
        id,
        total
      }
    });
  } catch (err) {
    await connection.rollback();
    next(err);
  } finally {
    connection.release();
  }
}
