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

const generateMemberId = async () => {
  const [rows] = await db.query(
    'SELECT member_id FROM members ORDER BY created_at DESC LIMIT 1'
  );
  if (rows.length === 0) return 'ID001';
  const lastId = rows[0].member_id;
  const num = parseInt(lastId.substring(2)) + 1;
  return 'ID' + num.toString().padStart(3, '0');
};

const filePath = (files, field) => {
  if (files?.[field]?.[0]) return `/uploads/${files[field][0].filename}`;
  return null;
};

// ─────────────────────────────────────────────
//  Register
// ─────────────────────────────────────────────

export const registerMember = async (req, res) => {
  try {
    const memberData = req.body;
    const files = req.files;

    const [existingEmail] = await db.query('SELECT email FROM members WHERE email = ?', [memberData.email]);
    if (existingEmail.length > 0)
      return res.status(400).json({ success: false, message: 'Email already registered' });

    const [existingMobile] = await db.query('SELECT mobile FROM members WHERE mobile = ?', [memberData.mobile]);
    if (existingMobile.length > 0)
      return res.status(400).json({ success: false, message: 'Mobile number already registered' });

    if (memberData.sponsor_id) {
      const [sponsor] = await db.query(
        'SELECT member_id, full_name FROM members WHERE member_id = ?',
        [memberData.sponsor_id]
      );
      if (sponsor.length === 0)
        return res.status(400).json({ success: false, message: 'Invalid sponsor ID' });
      memberData.sponsor_name = sponsor[0].full_name;
    }

    const member_id      = await generateMemberId();
    const hashedPassword = await bcrypt.hash(memberData.password, 10);

    const profile_photo  = filePath(files, 'profile_photo');
    const aadhaar_front  = filePath(files, 'aadhaar_front');
    const aadhaar_back   = filePath(files, 'aadhaar_back');
    const pan_image      = filePath(files, 'pan_image');
    const passbook_image = filePath(files, 'passbook_image');

    await db.query(
      `INSERT INTO members (
        member_id, status, title, full_name, relative_title, relative_name, gender, dob,
        profession, marital_status, mobile, whatsapp, email, profile_photo,
        nominee_name, nominee_relation, nominee_dob, nominee_contact,
        village, post_office, police_station, district, state, landmark, pincode,
        bank_holder_name, bank_name, account_number, ifsc_code, branch_name,
        aadhaar_front, aadhaar_back, aadhaar_number, pan_image, pan_number, passbook_image,
        sponsor_id, sponsor_name, password_hash, plan
      ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [
        member_id, 'Pending',
        memberData.title, memberData.full_name,
        memberData.relative_title, memberData.relative_name,
        memberData.gender, memberData.dob,
        memberData.profession, memberData.marital_status,
        memberData.mobile, memberData.whatsapp, memberData.email,
        profile_photo,
        memberData.nominee_name, memberData.nominee_relation,
        memberData.nominee_dob, memberData.nominee_contact,
        memberData.village, memberData.post_office, memberData.police_station,
        memberData.district, memberData.state, memberData.landmark, memberData.pincode,
        memberData.bank_holder_name  || null, memberData.bank_name      || null,
        memberData.account_number    || null, memberData.ifsc_code       || null,
        memberData.branch_name       || null,
        aadhaar_front, aadhaar_back,
        memberData.aadhaar_number    || null,
        pan_image, memberData.pan_number || null,
        passbook_image,
        memberData.sponsor_id   || null, memberData.sponsor_name || null,
        hashedPassword,
        '1500'
      ]
    );

    res.status(201).json({
      success: true,
      message: 'Registration successful. Please wait for admin approval.',
      member_id,
    });
  } catch (error) {
    res.status(500).json({ success: false, message: 'Server error', error: error.message });
  }
};

export const getMemberTree = async (req, res) => {
  try {
    const memberId = String(req.params.memberId || req.query.memberId || req.user.member_id);

    const [team] = await db.execute(`
      WITH RECURSIVE member_tree AS (
        SELECT 
          *,
          1 AS level
        FROM members
        WHERE member_id = ?

        UNION ALL

        SELECT 
          m.*,
          mt.level + 1 AS level
        FROM members m
        INNER JOIN member_tree mt ON m.sponsor_id = mt.member_id
      )
      SELECT * FROM member_tree ORDER BY level, member_id
    `, [memberId]);
    res.json({ team });
  } catch (error) {
    console.error('Member tree error:', error);
    res.status(500).json({ error: 'Failed to fetch member tree' });
  }
};

export const updateMemberProfile = async (req, res) => {
  try {
    // Status check — only Pending members can edit
    const [[member]] = await db.query(
      'SELECT status FROM members WHERE id = ?', [req.user.id]
    );
    if (!member)
      return res.status(404).json({ success: false, message: 'Member not found' });

    if (member.status !== 'Pending')
      return res.status(403).json({
        success: false,
        message: 'Profile can only be edited while account is Pending approval',
      });

    const allowedFields = [
      // Basic
      'title', 'full_name', 'relative_title', 'relative_name',
      'gender', 'dob', 'profession', 'marital_status',
      // Contact
      'mobile', 'whatsapp',
      // Nominee
      'nominee_name', 'nominee_relation', 'nominee_dob', 'nominee_contact',
      // Address
      'village', 'post_office', 'police_station',
      'district', 'state', 'landmark', 'pincode',
      // Bank
      'bank_holder_name', 'bank_name', 'account_number', 'ifsc_code', 'branch_name',
      // Documents
      'aadhaar_number', 'pan_number',
    ];

    const updates = [];
    const values  = [];

    // ── Text fields ───────────────────────────
    Object.keys(req.body).forEach(key => {
      if (allowedFields.includes(key)) {
        updates.push(`${key} = ?`);
        values.push(req.body[key] || null);
      }
    });

    // ── File fields (multer) ──────────────────
    const fileFields = [
      'profile_photo',
      'aadhaar_front',
      'aadhaar_back',
      'pan_image',
      'passbook_image',
    ];

    if (req.files) {
      fileFields.forEach(field => {
        if (req.files[field]?.[0]) {
          updates.push(`${field} = ?`);
          values.push(`/uploads/${req.files[field][0].filename}`);
        }
      });
    }

    if (!updates.length)
      return res.status(400).json({ success: false, message: 'No valid fields to update' });

    values.push(req.user.id);
    await db.query(
      `UPDATE members SET ${updates.join(', ')}, updated_at = CURRENT_TIMESTAMP WHERE id = ?`,
      values
    );

    const [members] = await db.query('SELECT * FROM members WHERE id = ?', [req.user.id]);
    const updated = members[0];
    delete updated.password_hash;
    res.json({ success: true, message: 'Profile updated successfully', data: updated });
  } catch (error) {
    res.status(500).json({ success: false, message: 'Server error', error: error.message });
  }
};


export const getMemberProfile = async (req, res) => {
  try {
    const [members] = await db.query('SELECT * FROM members WHERE id = ?', [req.user.id]);
    if (!members.length)
      return res.status(404).json({ success: false, message: 'Member not found' });
    const member = members[0];
    delete member.password_hash;
    res.json({ success: true, data: member });
  } catch (error) {
    res.status(500).json({ success: false, message: 'Server error', error: error.message });
  }
};


export const getMemberDownline = async (req, res) => {
  try {
    const [downline] = await db.query(
      `SELECT member_id, full_name, mobile, email, status, created_at, wallet_balance, total_income
       FROM members WHERE sponsor_id = ?`,
      [req.user.member_id]
    );
    res.json({ success: true, data: downline });
  } catch (error) {
    res.status(500).json({ success: false, message: 'Server error', error: error.message });
  }
};

// ─────────────────────────────────────────────
//  Verify Sponsor
// ─────────────────────────────────────────────

export const verifySponsor = async (req, res) => {
  try {
    const { sponsorId } = req.params;
    const [members] = await db.query(
      'SELECT member_id, full_name, email, mobile, status FROM members WHERE member_id = ? AND status = ?',
      [sponsorId, 'Active']
    );
    if (!members.length)
      return res.status(404).json({ success: false, message: 'Sponsor not found or not active' });
    res.json({ success: true, data: members[0], message: 'Sponsor verified successfully' });
  } catch (error) {
    res.status(500).json({ success: false, message: 'Server error', error: error.message });
  }
};

// ─────────────────────────────────────────────
//  Dashboard
// ─────────────────────────────────────────────

export const getMemberDashboard = async (req, res) => {
  try {
    const memberId   = req.user.id;
    const memberCode = req.user.member_id;

      const [memberRows] = await db.query(
        `SELECT * 
        FROM members 
        WHERE id = ?`,
        [memberId]
      );
    if (!memberRows.length)
      return res.status(404).json({ success: false, message: 'Member not found' });

    const member = memberRows[0];

    const [[{ direct_count }]] = await db.query(
      'SELECT COUNT(*) AS direct_count FROM members WHERE sponsor_id = ?', [memberCode]
    );

    const [[{ total_team }]] = await db.query(
      `WITH RECURSIVE team AS (
         SELECT member_id FROM members WHERE sponsor_id = ?
         UNION ALL
         SELECT m.member_id FROM members m INNER JOIN team t ON m.sponsor_id = t.member_id
       )
       SELECT COUNT(*) AS total_team FROM team`,
      [memberCode]
    );

    const [[{ team_bv }]] = await db.query(
      `WITH RECURSIVE team AS (
         SELECT member_id FROM members WHERE sponsor_id = ?
         UNION ALL
         SELECT m.member_id FROM members m INNER JOIN team t ON m.sponsor_id = t.member_id
       )
       SELECT COALESCE(SUM(mb.total_income), 0) AS team_bv
       FROM team JOIN members mb ON mb.member_id = team.member_id`,
      [memberCode]
    );

    const [recent_transactions] = await db.query(
      `SELECT id, transaction_type, amount, description, order_id, created_at
       FROM transactions WHERE user_member_id = ? ORDER BY created_at DESC LIMIT 20`,
      [memberCode]
    );



    res.json({
      success: true,
      member: { ...member, home_wallet: member.wallet_balance, team_bv: parseFloat(team_bv) || 0 },
      direct_downline:     parseInt(direct_count) || 0,
      total_team:          parseInt(total_team)   || 0,
      recent_transactions,    });
  } catch (error) {
    console.error('❌ getMemberDashboard error:', error.message);
    res.status(500).json({ success: false, message: 'Server error', error: error.message });
  }
};



export const getMemberSalaryHistory = async (req, res) => {
  try {
    const memberCode = req.user.member_id;   // JWT se — e.g. "RV001"

    const [items] = await db.query(
      `SELECT
         spi.id,
         sp.payout_month,
         sp.paid_at,
         spi.gross_amount,
         spi.tds_rate,
         spi.tds_amount,
         spi.net_amount,
         spi.pan_number,
         spi.bank_holder_name,
         spi.bank_name,
         spi.account_number,
         spi.ifsc_code,
         spi.branch_name
       FROM salary_payout_items spi
       JOIN salary_payouts      sp  ON sp.id = spi.payout_id
       WHERE spi.member_id = ?
       ORDER BY sp.paid_at DESC`,
      [memberCode]
    );

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