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

export const getIncomeHistory = async (req, res) => {
  try {
    const { memberId } = req.params;
    const { type, page = 1, limit = 20 } = req.query;
    const offset = (parseInt(page) - 1) * parseInt(limit);
    const params = [memberId];
    let typeFilter = '';

    const validTypes = ['DSPCI', 'DBR', 'TRI', 'DCI', 'CASHBACK'];
    if (type && validTypes.includes(type.toUpperCase())) {
      typeFilter = 'AND transaction_type = ?';
      params.push(type.toUpperCase());
    }

    const [rows] = await db.query(
      `SELECT id, transaction_type AS income_type, amount,
              user_member_id, description, order_id, created_at
       FROM transactions
       WHERE user_member_id = ? ${typeFilter}
       ORDER BY created_at DESC
       LIMIT ? OFFSET ?`,
      [...params, parseInt(limit), offset]
    );

    const [[{ total }]] = await db.query(
      `SELECT COUNT(*) AS total FROM transactions WHERE user_member_id = ? ${typeFilter}`,
      params
    );

    res.json({
      success: true,
      data:    rows,
      pagination: {
        page:       parseInt(page),
        limit:      parseInt(limit),
        total,
        totalPages: Math.ceil(total / parseInt(limit)),
      },
    });
  } catch (error) {
    console.error('❌ getIncomeHistory error:', error.message);
    res.status(500).json({ success: false, message: 'Server error', error: error.message });
  }
};

// ── getIncomeHistory — with pagination, type filter, date range ──
export const getIncomeSummary = async (req, res) => {
  try {
    const { memberId } = req.params;
    const { page = 1, limit = 10, type, from, to } = req.query;

    const offset = (parseInt(page) - 1) * parseInt(limit);

    // Build WHERE conditions
    const conditions = ['user_member_id = ?'];
    const values     = [memberId];

    if (type && type !== 'ALL') {
      conditions.push('transaction_type = ?');
      values.push(type);
    }

    if (from) {
      conditions.push('DATE(created_at) >= ?');
      values.push(from); // format: YYYY-MM-DD
    }

    if (to) {
      conditions.push('DATE(created_at) <= ?');
      values.push(to); // format: YYYY-MM-DD
    }

    const where = conditions.join(' AND ');

    // Total count
    const [[{ total }]] = await db.query(
      `SELECT COUNT(*) AS total FROM transactions WHERE ${where}`,
      values
    );

    // Total amount in date range (for rangeTotal)
    const [[{ rangeTotal }]] = await db.query(
      `SELECT COALESCE(SUM(amount), 0) AS rangeTotal FROM transactions WHERE ${where}`,
      values
    );

    // Paginated data
    const [data] = await db.query(
      `SELECT * FROM transactions WHERE ${where}
       ORDER BY created_at DESC
       LIMIT ? OFFSET ?`,
      [...values, parseInt(limit), offset]
    );

    res.json({
      success: true,
      data,
      rangeTotal: Number(rangeTotal),
      pagination: {
        page:       parseInt(page),
        limit:      parseInt(limit),
        total:      parseInt(total),
        totalPages: Math.ceil(parseInt(total) / parseInt(limit)),
      },
    });
  } catch (error) {
    console.error('❌ getIncomeHistory error:', error.message);
    res.status(500).json({ success: false, message: 'Server error', error: error.message });
  }
};


;