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

// ─── Helpers ──────────────────────────────────────────────────────────────────
function makeCouponCode() {
  const chars = "ABCDEFGHJKLMNPQRSTUVWXYZ23456789";
  let code = "LY-";
  for (let i = 0; i < 8; i++) code += chars[Math.floor(Math.random() * chars.length)];
  return code;
}

function tierForPoints(points) {
  if (points >= 4000) return "platinum";
  if (points >= 1500) return "gold";
  if (points >= 500)  return "silver";
  return "bronze";
}

// ─── GET /api/loyalty/dashboard ─────────────────────────────────────────────
export async function getDashboard(req, res, next) {
  try {
    // Tier distribution
    const [tierRows] = await pool.query(`
      SELECT loyalty_tier AS tier, COUNT(*) AS count
      FROM customers
      GROUP BY loyalty_tier
    `);

    // Total points stats
    const [[pts]] = await pool.query(`
      SELECT
        COALESCE(SUM(CASE WHEN type='earned'   THEN points ELSE 0 END), 0) AS total_earned,
        COALESCE(SUM(CASE WHEN type='redeemed' THEN ABS(points) ELSE 0 END), 0) AS total_redeemed,
        COUNT(DISTINCT customer_id) AS active_members
      FROM loyalty_points
    `);

    // Total redemptions
    const [[redStats]] = await pool.query(`
      SELECT COUNT(*) AS total, SUM(discount_applied) AS total_discount
      FROM loyalty_redemptions
    `);

    // Top 8 customers by points
    const [topCustomers] = await pool.query(`
      SELECT id, full_name, phone, loyalty_points_balance AS balance,
             loyalty_tier AS tier, loyalty_total_earned AS total_earned,
             loyalty_total_redeemed AS total_redeemed
      FROM customers
      WHERE loyalty_points_balance > 0
      ORDER BY loyalty_points_balance DESC
      LIMIT 8
    `);

    // Recent point transactions (last 10)
    const [recentActivity] = await pool.query(`
      SELECT lp.id, lp.points, lp.type, lp.description, lp.created_at,
             c.full_name AS customer_name, lp.order_id
      FROM loyalty_points lp
      JOIN customers c ON c.id = lp.customer_id
      ORDER BY lp.created_at DESC
      LIMIT 10
    `);

    // Points issued this month
    const [[thisMonth]] = await pool.query(`
      SELECT
        COALESCE(SUM(CASE WHEN type='earned' THEN points ELSE 0 END), 0) AS earned_month,
        COUNT(DISTINCT customer_id) AS new_earners
      FROM loyalty_points
      WHERE created_at >= DATE_FORMAT(CURDATE(), '%Y-%m-01')
    `);

    res.json({
      success: true,
      data: {
        tiers: tierRows.map(r => ({ tier: r.tier, count: Number(r.count) })),
        totalEarned:     Number(pts.total_earned),
        totalRedeemed:   Number(pts.total_redeemed),
        activeMembers:   Number(pts.active_members),
        totalRedemptions: Number(redStats.total || 0),
        totalDiscountGiven: Number(redStats.total_discount || 0),
        topCustomers,
        recentActivity,
        thisMonth: {
          earned: Number(thisMonth.earned_month),
          earners: Number(thisMonth.new_earners)
        }
      }
    });
  } catch (err) {
    next(err);
  }
}

// ─── GET /api/loyalty/customer/:id ──────────────────────────────────────────
export async function getCustomerLoyalty(req, res, next) {
  try {
    const { id } = req.params;

    const [custRows] = await pool.query(
      `SELECT id, full_name, phone, email, plan, loyalty_points_balance AS balance,
              loyalty_tier AS tier, loyalty_total_earned AS total_earned,
              loyalty_total_redeemed AS total_redeemed
       FROM customers WHERE id = ?`,
      [id]
    );
    if (!custRows.length) return res.status(404).json({ success: false, message: "Cliente no encontrado" });

    // Points history
    const [history] = await pool.query(
      `SELECT lp.id, lp.points, lp.type, lp.description, lp.created_at, lp.order_id
       FROM loyalty_points lp
       WHERE lp.customer_id = ?
       ORDER BY lp.created_at DESC LIMIT 30`,
      [id]
    );

    // Active redemptions (coupons not yet used)
    const [coupons] = await pool.query(
      `SELECT lr.id, lr.coupon_code, lr.points_used, lr.discount_applied, lr.status,
              lr.expires_at, lr.created_at, r.name AS reward_name, r.emoji
       FROM loyalty_redemptions lr
       JOIN loyalty_rewards r ON r.id = lr.reward_id
       WHERE lr.customer_id = ? AND lr.status = 'active'
       ORDER BY lr.created_at DESC`,
      [id]
    );

    const customer = custRows[0];
    const tier = tierForPoints(Number(customer.balance));

    res.json({
      success: true,
      data: {
        customer,
        tier,
        history,
        activeCoupons: coupons
      }
    });
  } catch (err) {
    next(err);
  }
}

// ─── GET /api/loyalty/rewards ────────────────────────────────────────────────
export async function getRewards(req, res, next) {
  try {
    const { active } = req.query;
    let sql = `SELECT * FROM loyalty_rewards`;
    if (active === "1") sql += ` WHERE is_active = 1`;
    sql += ` ORDER BY points_cost ASC`;
    const [rows] = await pool.query(sql);
    res.json({ success: true, data: rows });
  } catch (err) {
    next(err);
  }
}

// ─── POST /api/loyalty/rewards ───────────────────────────────────────────────
export async function createReward(req, res, next) {
  try {
    const {
      name, description, points_cost, discount_type = "fixed",
      discount_value, min_order_value = 0, tier_required = null,
      stock = null, emoji = "🎁"
    } = req.body;

    if (!name || !points_cost || discount_value === undefined) {
      return res.status(400).json({ success: false, message: "Nombre, costo en puntos y valor de descuento son requeridos" });
    }

    const [result] = await pool.query(
      `INSERT INTO loyalty_rewards (name, description, points_cost, discount_type, discount_value,
        min_order_value, tier_required, stock, emoji)
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [name, description || null, points_cost, discount_type, discount_value,
       min_order_value, tier_required, stock, emoji]
    );

    const [rows] = await pool.query("SELECT * FROM loyalty_rewards WHERE id = ?", [result.insertId]);
    res.status(201).json({ success: true, message: "Recompensa creada", data: rows[0] });
  } catch (err) {
    next(err);
  }
}

// ─── PUT /api/loyalty/rewards/:id ───────────────────────────────────────────
export async function updateReward(req, res, next) {
  try {
    const { id } = req.params;
    const { name, description, points_cost, discount_type, discount_value,
            min_order_value, tier_required, stock, emoji, is_active } = req.body;

    await pool.query(
      `UPDATE loyalty_rewards
       SET name=?, description=?, points_cost=?, discount_type=?, discount_value=?,
           min_order_value=?, tier_required=?, stock=?, emoji=?, is_active=?
       WHERE id=?`,
      [name, description||null, points_cost, discount_type, discount_value,
       min_order_value||0, tier_required||null, stock||null, emoji||'🎁', is_active===false?0:1, id]
    );

    const [rows] = await pool.query("SELECT * FROM loyalty_rewards WHERE id = ?", [id]);
    res.json({ success: true, message: "Recompensa actualizada", data: rows[0] });
  } catch (err) {
    next(err);
  }
}

// ─── DELETE /api/loyalty/rewards/:id ────────────────────────────────────────
export async function deleteReward(req, res, next) {
  const connection = await pool.getConnection();
  try {
    await connection.beginTransaction();
    const { id } = req.params;
    
    // Delete any dependent redemptions first
    await connection.query("DELETE FROM loyalty_redemptions WHERE reward_id = ?", [id]);
    
    // Now delete the reward itself
    await connection.query("DELETE FROM loyalty_rewards WHERE id = ?", [id]);
    
    await connection.commit();
    res.json({ success: true, message: "Recompensa eliminada" });
  } catch (err) {
    await connection.rollback();
    next(err);
  } finally {
    connection.release();
  }
}

// ─── POST /api/loyalty/redeem ────────────────────────────────────────────────
export async function redeemReward(req, res, next) {
  const connection = await pool.getConnection();
  try {
    await connection.beginTransaction();

    const { customerId, rewardId } = req.body;
    if (!customerId || !rewardId) {
      return res.status(400).json({ success: false, message: "customerId y rewardId son requeridos" });
    }

    // Get customer
    const [[customer]] = await connection.query(
      "SELECT id, full_name, loyalty_points_balance FROM customers WHERE id = ?", [customerId]
    );
    if (!customer) throw new Error("Cliente no encontrado");

    // Get reward
    const [[reward]] = await connection.query(
      "SELECT * FROM loyalty_rewards WHERE id = ? AND is_active = 1", [rewardId]
    );
    if (!reward) throw new Error("Recompensa no disponible");

    // Check points balance
    if (Number(customer.loyalty_points_balance) < Number(reward.points_cost)) {
      return res.status(400).json({
        success: false,
        message: `Puntos insuficientes. Tiene ${customer.loyalty_points_balance} pts, necesita ${reward.points_cost} pts.`
      });
    }

    // Check stock
    if (reward.stock !== null && reward.stock <= 0) {
      return res.status(400).json({ success: false, message: "Recompensa sin stock disponible" });
    }

    // Check tier requirement
    if (reward.tier_required) {
      const [[cust2]] = await connection.query(
        "SELECT loyalty_tier FROM customers WHERE id = ?", [customerId]
      );
      const tierRank = { bronze:1, silver:2, gold:3, platinum:4 };
      if ((tierRank[cust2.loyalty_tier] || 0) < (tierRank[reward.tier_required] || 0)) {
        return res.status(400).json({
          success: false,
          message: `Esta recompensa requiere nivel ${reward.tier_required}.`
        });
      }
    }

    const couponCode = makeCouponCode();
    const expiresAt = new Date();
    expiresAt.setDate(expiresAt.getDate() + 30); // 30 days

    // Create redemption
    await connection.query(
      `INSERT INTO loyalty_redemptions
         (customer_id, reward_id, points_used, discount_applied, status, coupon_code, expires_at)
       VALUES (?, ?, ?, ?, 'active', ?, ?)`,
      [customerId, rewardId, reward.points_cost, reward.discount_value, couponCode, expiresAt]
    );

    // Deduct points in ledger
    await connection.query(
      `INSERT INTO loyalty_points (customer_id, points, type, description, created_at)
       VALUES (?, ?, 'redeemed', ?, NOW())`,
      [customerId, -reward.points_cost, `Canje: ${reward.name}`]
    );

    // Update customer balance
    const newBalance = Number(customer.loyalty_points_balance) - Number(reward.points_cost);
    const newTier = tierForPoints(newBalance);
    await connection.query(
      `UPDATE customers
       SET loyalty_points_balance = ?,
           loyalty_tier = ?,
           loyalty_total_redeemed = loyalty_total_redeemed + ?
       WHERE id = ?`,
      [newBalance, newTier, reward.points_cost, customerId]
    );

    // Reduce stock if applicable
    if (reward.stock !== null) {
      await connection.query(
        "UPDATE loyalty_rewards SET stock = stock - 1 WHERE id = ?", [rewardId]
      );
    }

    await connection.commit();

    res.json({
      success: true,
      message: "Canje exitoso",
      data: {
        couponCode,
        pointsUsed: reward.points_cost,
        discountApplied: reward.discount_value,
        discountType: reward.discount_type,
        rewardName: reward.name,
        newBalance,
        expiresAt
      }
    });
  } catch (err) {
    await connection.rollback();
    next(err);
  } finally {
    connection.release();
  }
}

// ─── POST /api/loyalty/adjust ────────────────────────────────────────────────
export async function adjustPoints(req, res, next) {
  const connection = await pool.getConnection();
  try {
    await connection.beginTransaction();

    const { customerId, points, type = "bonus", description } = req.body;
    if (!customerId || points === undefined || points === 0) {
      return res.status(400).json({ success: false, message: "customerId y points son requeridos" });
    }

    const [[customer]] = await connection.query(
      "SELECT id, loyalty_points_balance FROM customers WHERE id = ?", [customerId]
    );
    if (!customer) throw new Error("Cliente no encontrado");

    const newBalance = Math.max(0, Number(customer.loyalty_points_balance) + Number(points));
    const newTier = tierForPoints(newBalance);

    await connection.query(
      `INSERT INTO loyalty_points (customer_id, points, type, description, created_at)
       VALUES (?, ?, ?, ?, NOW())`,
      [customerId, points, type, description || (points > 0 ? "Ajuste manual positivo" : "Ajuste manual negativo")]
    );

    const updateFields = [`loyalty_points_balance = ?`, `loyalty_tier = ?`];
    const updateVals = [newBalance, newTier];

    if (Number(points) > 0) {
      updateFields.push("loyalty_total_earned = loyalty_total_earned + ?");
      updateVals.push(points);
    }
    updateVals.push(customerId);

    await connection.query(
      `UPDATE customers SET ${updateFields.join(", ")} WHERE id = ?`,
      updateVals
    );

    await connection.commit();

    res.json({
      success: true,
      message: `Puntos ${points > 0 ? "añadidos" : "deducidos"} correctamente`,
      data: { newBalance, newTier }
    });
  } catch (err) {
    await connection.rollback();
    next(err);
  } finally {
    connection.release();
  }
}

// ─── GET /api/loyalty/tiers ──────────────────────────────────────────────────
export async function getTiers(req, res, next) {
  try {
    const [rows] = await pool.query("SELECT * FROM loyalty_tiers ORDER BY sort_order ASC");
    res.json({ success: true, data: rows });
  } catch (err) {
    next(err);
  }
}

// ─── Internal: earn points after order ──────────────────────────────────────
// Called from ordersController when an order is paid
export async function earnPointsForOrder(connection, customerId, orderId, orderTotal) {
  try {
    // Get points rate from config
    const [[cfg]] = await connection.query(
      "SELECT valor FROM configuracion WHERE clave = 'loyalty_points_per_dollar'"
    );
    const rate = cfg ? parseFloat(cfg.valor) : 1;
    const points = Math.floor(orderTotal * rate);
    if (points <= 0) return;

    // Insert ledger entry
    await connection.query(
      `INSERT INTO loyalty_points (customer_id, order_id, points, type, description, created_at)
       VALUES (?, ?, ?, 'earned', ?, NOW())`,
      [customerId, orderId, points, `Orden #${orderId} — ${points} pts`]
    );

    // Update customer balance
    await connection.query(
      `UPDATE customers
       SET loyalty_points_balance  = loyalty_points_balance + ?,
           loyalty_total_earned     = loyalty_total_earned + ?,
           loyalty_tier             = CASE
             WHEN loyalty_points_balance + ? >= 4000 THEN 'platinum'
             WHEN loyalty_points_balance + ? >= 1500 THEN 'gold'
             WHEN loyalty_points_balance + ? >= 500  THEN 'silver'
             ELSE 'bronze'
           END
       WHERE id = ?`,
      [points, points, points, points, points, customerId]
    );
  } catch {
    // Non-fatal: loyalty points failure should not break order creation
  }
}
