const db = require('../config/db');

// Get all transactions with user details
const getAllTransactions = async (req, res) => {
  try {
    const { status, transaction_type, start_date, end_date } = req.query;
    
    let query = `
      SELECT 
        t.id,
        u.name,
        t.order_id,
        t.transaction_type,
        t.transaction_id,
        t.amount,
        t.message,
        t.transaction_date,
        t.status
      FROM transactions t
      LEFT JOIN users u ON t.user_id = u.id
      WHERE t.status != 0
    `;
    
    const params = [];
    
    if (status) {
      query += ' AND t.status = ?';
      params.push(status);
    }
    
    if (transaction_type) {
      query += ' AND t.transaction_type = ?';
      params.push(transaction_type);
    }
    
    if (start_date) {
      query += ' AND DATE(t.transaction_date) >= ?';
      params.push(start_date);
    }
    
    if (end_date) {
      query += ' AND DATE(t.transaction_date) <= ?';
      params.push(end_date);
    }
    
    query += ' ORDER BY t.transaction_date DESC';
    
    const [rows] = await db.query(query, params);
    res.json({ success: true, data: rows });
  } catch (error) {
    console.error('Error fetching transactions:', error);
    res.status(500).json({ success: false, msg: 'Error fetching transactions' });
  }
};

// Get transaction by ID
const getTransactionById = async (req, res) => {
  try {
    const { id } = req.params;
    const [rows] = await db.query(`
      SELECT 
        t.*,
        u.name as user_name,
        u.email as user_email
      FROM transactions t
      LEFT JOIN users u ON t.user_id = u.id
      WHERE t.id = ?
    `, [id]);
    
    if (rows.length === 0) {
      return res.status(404).json({ success: false, msg: 'Transaction not found' });
    }
    
    res.json({ success: true, data: rows[0] });
  } catch (error) {
    console.error('Error fetching transaction:', error);
    res.status(500).json({ success: false, msg: 'Error fetching transaction' });
  }
};

// Get transactions by user ID
const getTransactionsByUser = async (req, res) => {
  try {
    const { user_id } = req.params;
    const [rows] = await db.query(`
      SELECT 
        t.id,
        t.order_id,
        t.transaction_type,
        t.transaction_id,
        t.amount,
        t.message,
        t.transaction_date,
        t.status
      FROM transactions t
      WHERE t.user_id = ? AND t.status != 0
      ORDER BY t.transaction_date DESC
    `, [user_id]);
    
    res.json({ success: true, data: rows });
  } catch (error) {
    console.error('Error fetching user transactions:', error);
    res.status(500).json({ success: false, msg: 'Error fetching user transactions' });
  }
};

// Create new transaction
const createTransaction = async (req, res) => {
  try {
    const { user_id, order_id, transaction_type, transaction_id, amount, message } = req.body;
    
    if (!user_id || !transaction_type || !amount) {
      return res.status(400).json({ success: false, msg: 'User ID, transaction_type, and amount are required' });
    }
    
    const [result] = await db.query(
      'INSERT INTO transactions (user_id, order_id, transaction_type, transaction_id, amount, message, transaction_date) VALUES (?, ?, ?, ?, ?, ?, NOW())',
      [user_id, order_id, transaction_type, transaction_id, amount, message]
    );
    
    const [newTransaction] = await db.query('SELECT * FROM transactions WHERE id = ?', [result.insertId]);
    
    res.status(201).json({ success: true, data: newTransaction[0], msg: 'Transaction created successfully' });
  } catch (error) {
    console.error('Error creating transaction:', error);
    res.status(500).json({ success: false, msg: 'Error creating transaction' });
  }
};

// Update transaction status
const updateTransactionStatus = async (req, res) => {
  try {
    const { id } = req.params;
    const { status } = req.body;
    
    const [result] = await db.query(
      'UPDATE transactions SET status = ?, updated_at = NOW() WHERE id = ?',
      [status, id]
    );
    
    if (result.affectedRows === 0) {
      return res.status(404).json({ success: false, msg: 'Transaction not found' });
    }
    
    const [updatedTransaction] = await db.query('SELECT * FROM transactions WHERE id = ?', [id]);
    
    res.json({ success: true, data: updatedTransaction[0], msg: 'Transaction status updated successfully' });
  } catch (error) {
    console.error('Error updating transaction status:', error);
    res.status(500).json({ success: false, msg: 'Error updating transaction status' });
  }
};

// Delete transaction (soft delete)
const deleteTransaction = async (req, res) => {
  try {
    const { id } = req.params;
    
    const [result] = await db.query(
      'UPDATE transactions SET status = 0, updated_at = NOW() WHERE id = ?',
      [id]
    );
    
    if (result.affectedRows === 0) {
      return res.status(404).json({ success: false, msg: 'Transaction not found' });
    }
    
    res.json({ success: true, msg: 'Transaction deleted successfully' });
  } catch (error) {
    console.error('Error deleting transaction:', error);
    res.status(500).json({ success: false, msg: 'Error deleting transaction' });
  }
};

// Get transaction statistics
const getTransactionStats = async (req, res) => {
  try {
    const [totalRows] = await db.query('SELECT COUNT(*) as total FROM transactions WHERE status != 0');
    const [successRows] = await db.query('SELECT COUNT(*) as success FROM transactions WHERE status = "success"');
    const [pendingRows] = await db.query('SELECT COUNT(*) as pending FROM transactions WHERE status = "pending"');
    const [failedRows] = await db.query('SELECT COUNT(*) as failed FROM transactions WHERE status = "failed"');
    const [totalAmount] = await db.query('SELECT SUM(amount) as total_amount FROM transactions WHERE status = "success"');
    
    res.json({
      success: true,
      data: {
        total: totalRows[0].total,
        success: successRows[0].success,
        pending: pendingRows[0].pending,
        failed: failedRows[0].failed,
        total_amount: totalAmount[0].total_amount || 0
      }
    });
  } catch (error) {
    console.error('Error fetching transaction stats:', error);
    res.status(500).json({ success: false, msg: 'Error fetching transaction stats' });
  }
};

module.exports = {
  getAllTransactions,
  getTransactionById,
  getTransactionsByUser,
  createTransaction,
  updateTransactionStatus,
  deleteTransaction,
  getTransactionStats
};
