// ============================================================
//  incomeHelper.js  –  MLM Income Distribution Engine
// ============================================================
//
//  4 Income Types:
//  1. DSPCI  – 30% of order value
//             • 1st purchase  → sponsor gets it
//             • 2nd+ purchase → buyer gets it (self income)
//  2. DBR    – 10% of DSPCI  → direct sponsor (every purchase)
//  3. TRI    – 1%  of DSPCI  → every ancestor in upline chain
//  4. DCI    – 15% of order  → customer's sponsor
//             + 15% CASHBACK → customer themselves
// ============================================================

import db from '../config/database.js';

// ─────────────────────────────────────────────
//  Private Helpers
// ─────────────────────────────────────────────

/** Count previously approved member orders (excluding current order) */
const getPreviousApprovedCount = async (userId, currentOrderId) => {
  const [[{ cnt }]] = await db.query(
    `SELECT COUNT(*) AS cnt FROM orders
      WHERE user_id = ? AND user_type = 'member'
        AND payment_status = 'Approved' AND id != ?`,
    [userId, currentOrderId]
  );
  return parseInt(cnt);
};

/**
 * Walk the full upline chain starting from a member_id code (e.g. "MEM001").
 * Returns array of member rows: [direct_sponsor, grand_sponsor, ...]
 */
const getUplineChain = async (startMemberCode) => {
  const chain   = [];
  const visited = new Set();
  let currentCode = startMemberCode;

  while (currentCode && !visited.has(currentCode)) {
    visited.add(currentCode);
    const [rows] = await db.query(
      'SELECT id, member_id, sponsor_id FROM members WHERE member_id = ?',
      [currentCode]
    );
    if (!rows?.length) break;
    chain.push(rows[0]);
    currentCode = rows[0].sponsor_id;
  }

  return chain;
};

/**
 * Credit income to a member: updates the specific income column,
 * wallet_balance, total_income, and inserts a transaction record.
 */
const creditMemberIncome = async ({ memberUUID, memberCode, col, amount, orderId, txType, description }) => {
  await db.query(
    `UPDATE members
        SET wallet_balance = wallet_balance + ?,
            ${col}         = ${col} + ?,
            total_income   = total_income + ?
      WHERE id = ?`,
    [amount, amount, amount, memberUUID]
  );
  await db.query(
    `INSERT INTO transactions
       (user_type, user_id, user_member_id, transaction_type, amount, description, order_id)
     VALUES ('member', ?, ?, ?, ?, ?, ?)`,
    [memberUUID, memberCode, txType, amount, description, orderId]
  );
};

// ─────────────────────────────────────────────
//  Customer Income  (CASHBACK + DCI)
// ─────────────────────────────────────────────

const handleCustomerIncome = async (order, orderAmount) => {

  // ── 15% CASHBACK → Customer ───────────────────────────────
  const cashback = orderAmount * 0.15;

  await db.query(
    `UPDATE customers
        SET wallet_balance  = wallet_balance  + ?,
            cashback_income = cashback_income + ?,
            total_income    = total_income    + ?
      WHERE id = ?`,
    [cashback, cashback, cashback, order.user_id]
  );

  await db.query(
    `INSERT INTO transactions
       (user_type, user_id, user_member_id, transaction_type, amount, description, order_id)
     VALUES ('customer', ?, ?, 'CASHBACK', ?, '15% cashback on purchase', ?)`,
    [order.user_id, order.user_member_id, cashback, order.order_id]
  );

  console.log(`✅ [CASHBACK] ₹${cashback.toFixed(2)} → customer ${order.user_member_id}`);

  // ── 15% DCI → Sponsor Member ─────────────────────────────
  const [custRows] = await db.query(
    'SELECT sponsor_id FROM customers WHERE id = ?',   // ✅ sponsor_id (not sponsor_member_id)
    [order.user_id]
  );

  if (!custRows?.[0]?.sponsor_id) {
    console.log(`⚠️ [DCI] No sponsor found for customer ${order.user_member_id}`);
    return;
  }

  const dci = orderAmount * 0.15;

  const [spRows] = await db.query(
    'SELECT id, member_id FROM members WHERE member_id = ?',
    [custRows[0].sponsor_id]                           // ✅ sponsor_id (not sponsor_member_id)
  );

  if (!spRows?.length) {
    console.log(`⚠️ [DCI] Sponsor member ${custRows[0].sponsor_id} not found in members table`);
    return;
  }

  const sp = spRows[0];

  await creditMemberIncome({
    memberUUID:  sp.id,
    memberCode:  sp.member_id,
    col:         'dci_income',
    amount:      dci,
    orderId:     order.order_id,
    txType:      'DCI',
    description: `15% DCI from customer ${order.user_member_id}'s purchase`,
  });

  console.log(`✅ [DCI] ₹${dci.toFixed(2)} → member ${sp.member_id}`);
};


const handleMemberIncome = async (order, orderAmount) => {
  const dspci = orderAmount * 0.30;

  // Fetch buyer's full row
  const [buyerRows] = await db.query(
    'SELECT id, member_id, sponsor_id FROM members WHERE id = ?',
    [order.user_id]
  );
  if (!buyerRows?.length) return;
  const buyer = buyerRows[0];

  // ── 1️⃣  DSPCI ─────────────────────────────
  const prevCount       = await getPreviousApprovedCount(order.user_id, order.id);
  const isFirstPurchase = prevCount === 0;

  if (isFirstPurchase && buyer.sponsor_id) {
    // 1st purchase → DSPCI to sponsor
    const [spRows] = await db.query(
      'SELECT id, member_id FROM members WHERE member_id = ?',
      [buyer.sponsor_id]
    );
    if (spRows?.length > 0) {
      const sp = spRows[0];
      await creditMemberIncome({
        memberUUID:  sp.id,
        memberCode:  sp.member_id,
        col:         'dspci_income',
        amount:      dspci,
        orderId:     order.order_id,
        txType:      'DSPCI',
        description: `30% DSPCI from ${buyer.member_id}'s 1st purchase (sponsor benefit)`,
      });
      console.log(`✅ [DSPCI-1st] ₹${dspci.toFixed(2)} → sponsor ${sp.member_id}`);
    }
  } else {
    // Repeat purchase → DSPCI to buyer themselves
    await creditMemberIncome({
      memberUUID:  buyer.id,
      memberCode:  buyer.member_id,
      col:         'dspci_income',
      amount:      dspci,
      orderId:     order.order_id,
      txType:      'DSPCI',
      description: '30% DSPCI on self purchase',
    });
    console.log(`✅ [DSPCI-self] ₹${dspci.toFixed(2)} → ${buyer.member_id}`);
  }

  if (!buyer.sponsor_id) return;

  // ── 2️⃣  DBR ──────────────────────────────
  //  Direct sponsor gets 10% of DSPCI (every purchase, always)
  const dbr = dspci * 0.10;
  const [spRows] = await db.query(
    'SELECT id, member_id FROM members WHERE member_id = ?',
    [buyer.sponsor_id]
  );
  if (spRows?.length > 0) {
    const sp = spRows[0];
    await creditMemberIncome({
      memberUUID:  sp.id,
      memberCode:  sp.member_id,
      col:         'dbr_income',
      amount:      dbr,
      orderId:     order.order_id,
      txType:      'DBR',
      description: `10% DBR from direct downline ${buyer.member_id}'s DSPCI`,
    });
    console.log(`✅ [DBR] ₹${dbr.toFixed(2)} → ${sp.member_id}`);
  }

  // ── 3️⃣  TRI ──────────────────────────────
  //  Every ancestor in upline gets 1% of DSPCI
  const tri         = dspci * 0.01;
  const uplineChain = await getUplineChain(buyer.sponsor_id);

  for (let level = 0; level < uplineChain.length; level++) {
    const ancestor = uplineChain[level];
    await creditMemberIncome({
      memberUUID:  ancestor.id,
      memberCode:  ancestor.member_id,
      col:         'tri_income',
      amount:      tri,
      orderId:     order.order_id,
      txType:      'TRI',
      description: `1% TRI from team member ${buyer.member_id} (level ${level + 1})`,
    });
    console.log(`✅ [TRI L${level + 1}] ₹${tri.toFixed(2)} → ${ancestor.member_id}`);
  }
};

// ─────────────────────────────────────────────
//  Main Export  –  called from approveOrder
// ─────────────────────────────────────────────

export const calculateMLMIncome = async (order, user_type) => {
  try {
    const orderAmount = parseFloat(order.total_amount);
    if (!orderAmount || isNaN(orderAmount)) return;

    if (user_type === 'customer') {
      await handleCustomerIncome(order, orderAmount);
    } else if (user_type === 'member') {
      await handleMemberIncome(order, orderAmount);
    }

    console.log(`🎉 Income distribution complete for order ${order.order_id}`);
  } catch (error) {
    // Income failure should NOT block order approval
    console.error('❌ calculateMLMIncome error:', error.message);
  }
};

// ─────────────────────────────────────────────
//  Income History & Summary APIs
// ─────────────────────────────────────────────

