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

// ─────────────────────────────────────────────
//  GET /admin/salary/preview
//  Sabhi Active members jinke wallet_balance > 0
//  TDS: 6% agar pan_number hai, 20% agar nahi
// ─────────────────────────────────────────────
export const getSalaryPreview = async (req, res) => {
  try {
    const [members] = await db.query(
      `SELECT
         id, member_id, full_name,
         pan_number,
         wallet_balance,
         bank_holder_name, bank_name, account_number, ifsc_code, branch_name,
         profile_photo
       FROM members
       WHERE status = 'Active' AND wallet_balance > 0
       ORDER BY full_name ASC`
    );

    // Current payout month  e.g. "2025-06"
    const now          = new Date();
    const payoutMonth  = `${now.getFullYear()}-${String(now.getMonth() + 1).padStart(2,'0')}`;

    // Already paid this month?
    const [[existing]] = await db.query(
      'SELECT id, paid_at FROM salary_payouts WHERE payout_month = ?',
      [payoutMonth]
    );

    const rows = members.map(m => {
      const gross   = parseFloat(m.wallet_balance);
      const tdsRate = m.pan_number ? 6 : 20;
      const tds     = parseFloat(((gross * tdsRate) / 100).toFixed(2));
      const net     = parseFloat((gross - tds).toFixed(2));
      return { ...m, gross, tds_rate: tdsRate, tds_amount: tds, net_amount: net };
    });

    const totalGross = rows.reduce((s, r) => s + r.gross, 0);
    const totalTds   = rows.reduce((s, r) => s + r.tds_amount, 0);
    const totalNet   = rows.reduce((s, r) => s + r.net_amount, 0);

    res.json({
      success:      true,
      payout_month: payoutMonth,
      already_paid: !!existing,
      paid_at:      existing?.paid_at || null,
      summary: {
        total_members: rows.length,
        total_gross:   parseFloat(totalGross.toFixed(2)),
        total_tds:     parseFloat(totalTds.toFixed(2)),
        total_net:     parseFloat(totalNet.toFixed(2)),
      },
      data: rows,
    });
  } catch (error) {
    console.error('❌ getSalaryPreview:', error.message);
    res.status(500).json({ success: false, message: 'Server error', error: error.message });
  }
};

// ─────────────────────────────────────────────
//  POST /admin/salary/pay
//  Sab members ka wallet 0 karo + payout records save karo
// ─────────────────────────────────────────────
export const processSalaryPayout = async (req, res) => {
  const conn = await db.getConnection();
  try {
    await conn.beginTransaction();

    const now         = new Date();
    const payoutMonth = `${now.getFullYear()}-${String(now.getMonth() + 1).padStart(2,'0')}`;

    // Already paid this month check
    const [[existing]] = await conn.query(
      'SELECT id FROM salary_payouts WHERE payout_month = ?', [payoutMonth]
    );
    if (existing) {
      await conn.rollback();
      return res.status(400).json({
        success: false,
        message: `${payoutMonth} ka salary already paid hai`,
      });
    }

    // Fetch eligible members (lock rows)
    const [members] = await conn.query(
      `SELECT id, member_id, full_name, pan_number, wallet_balance,
              bank_holder_name, bank_name, account_number, ifsc_code, branch_name
       FROM members
       WHERE status = 'Active' AND wallet_balance > 0
       FOR UPDATE`
    );

    if (!members.length) {
      await conn.rollback();
      return res.status(400).json({
        success: false,
        message: 'Koi bhi eligible member nahi mila (wallet_balance > 0)',
      });
    }

    // Calculate totals
    let totalGross = 0, totalTds = 0, totalNet = 0;
    const items = members.map(m => {
      const gross   = parseFloat(m.wallet_balance);
      const tdsRate = m.pan_number ? 6 : 20;
      const tds     = parseFloat(((gross * tdsRate) / 100).toFixed(2));
      const net     = parseFloat((gross - tds).toFixed(2));
      totalGross += gross;
      totalTds   += tds;
      totalNet   += net;
      return { ...m, gross, tdsRate, tds, net };
    });

    // Insert master payout record
    const [payoutResult] = await conn.query(
      `INSERT INTO salary_payouts
         (payout_month, paid_by, total_members, total_gross, total_tds, total_net)
       VALUES (?, 'Admin', ?, ?, ?, ?)`,
      [payoutMonth, items.length,
       parseFloat(totalGross.toFixed(2)),
       parseFloat(totalTds.toFixed(2)),
       parseFloat(totalNet.toFixed(2))]
    );
    const payoutId = payoutResult.insertId;

    // Insert per-member items + zero wallet + insert transaction
    for (const m of items) {
      // payout item
      await conn.query(
        `INSERT INTO salary_payout_items
           (payout_id, member_id, full_name, pan_number, tds_rate,
            gross_amount, tds_amount, net_amount,
            bank_name, account_number, ifsc_code, bank_holder_name, branch_name)
         VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
        [payoutId, m.member_id, m.full_name, m.pan_number || null,
         m.tdsRate, m.gross, m.tds, m.net,
         m.bank_name || null, m.account_number || null, m.ifsc_code || null,
         m.bank_holder_name || null, m.branch_name || null]
      );

      // Zero wallet
      await conn.query(
        'UPDATE members SET wallet_balance = 0, updated_at = NOW() WHERE id = ?',
        [m.id]
      );

      // Transaction record
      await conn.query(
        `INSERT INTO transactions
           (user_type, user_id, user_member_id, transaction_type, amount, description, order_id)
         VALUES ('member', ?, ?, 'SALARY', ?, ?, NULL)`,
        [m.id, m.member_id, m.net,
         `Salary paid ₹${m.net} (Gross ₹${m.gross} - TDS ${m.tdsRate}% ₹${m.tds}) — ${payoutMonth}`]
      );
    }

    await conn.commit();

    res.json({
      success:      true,
      message:      `${items.length} members ko salary paid successfully`,
      payout_month: payoutMonth,
      summary: {
        total_members: items.length,
        total_gross:   parseFloat(totalGross.toFixed(2)),
        total_tds:     parseFloat(totalTds.toFixed(2)),
        total_net:     parseFloat(totalNet.toFixed(2)),
      },
    });
  } catch (error) {
    await conn.rollback();
    console.error('❌ processSalaryPayout:', error.message);
    res.status(500).json({ success: false, message: 'Server error', error: error.message });
  } finally {
    conn.release();
  }
};

// ─────────────────────────────────────────────
//  GET /admin/salary/history
//  Past payout records
// ─────────────────────────────────────────────
export const getSalaryHistory = async (req, res) => {
  try {
    const [payouts] = await db.query(
      `SELECT * FROM salary_payouts ORDER BY paid_at DESC`
    );

    // For each payout, fetch items
    for (const p of payouts) {
      const [items] = await db.query(
        `SELECT * FROM salary_payout_items WHERE payout_id = ? ORDER BY full_name ASC`,
        [p.id]
      );
      p.items = items;
    }

    res.json({ success: true, data: payouts });
  } catch (error) {
    console.error('❌ getSalaryHistory:', error.message);
    res.status(500).json({ success: false, message: 'Server error', error: error.message });
  }
};