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

class Payments {

    static async getAllDonations() {
        const [rows] = await pool.query(`
            SELECT 
                d.id AS donation_id,
                us.first_name,
                us.last_name,
                us.email,
                p.payment_method,
                p.amount,
                p.transaction_id,
                p.status,
                d.created_at AS donation_date
            FROM donations d
            JOIN users us ON d.user_id = us.id
            JOIN payments p ON d.payment_id = p.id
            ORDER BY d.created_at DESC
        `);
        return rows;
    }

    static async getMyDonations(user_id) {
        const [rows] = await pool.query(`
            SELECT 
                d.id AS donation_id,
                us.id,
                us.first_name,
                us.last_name,
                us.email,
                p.payment_method,
                p.amount,
                p.transaction_id,
                p.status,
                d.created_at AS donation_date
            FROM donations d
            JOIN users us ON d.user_id = us.id
            JOIN payments p ON d.payment_id = p.id
            WHERE us.id = ?
            ORDER BY d.created_at DESC
        `, [user_id]);
        return rows; 
    }

    static async createDonation(user_id, payment_method, amount, transaction_id) {
        // Start a transaction
        const connection = await pool.getConnection();
        await connection.beginTransaction();

        try {
            // First, insert into payments table
            const [paymentResult] = await connection.query(`
                INSERT INTO payments 
                (payment_method, amount, transaction_id, status)
                VALUES (?, ?, ?, 'pending')
            `, [payment_method, amount, transaction_id]);

            const paymentId = paymentResult.insertId;

            // Then, insert into donations table
            await connection.query(`
                INSERT INTO donations 
                (user_id, payment_id)
                VALUES (?, ?)
            `, [user_id, paymentId]);

            // Commit the transaction
            await connection.commit();
            connection.release();

            return {
                success: true,
                paymentId,
                message: 'Donation record created successfully'
            };
        } catch (error) {
            // Rollback the transaction in case of error
            await connection.rollback();
            connection.release();
            throw error;
        }
    }

    static async updatePaymentStatus(transaction_id, status) {
        const [result] = await pool.query(
            'UPDATE payments SET status = ? WHERE transaction_id = ?',
            [status, transaction_id]
        );

        return {
            affectedRows: result.affectedRows,
            transaction_id,
            new_status: status
        };
    }

    static async getPaymentByTransactionId(transaction_id) {
        const [payment] = await pool.query(`
        SELECT p.*, u.email 
        FROM payments p
        JOIN donations d ON p.id = d.payment_id
        JOIN users u ON d.user_id = u.id
        WHERE p.transaction_id = ?
    `, [transaction_id]);

        return payment.length > 0 ? payment[0] : null;
    }

    static async handlePaychanguWebhook(charge_id, status) {
        // Determine the payment status based on Paychangu's status
        let paymentStatus;
        if (status === 'success') {
            paymentStatus = 'completed';
        } else if (status === 'failed') {
            paymentStatus = 'failed';
        } else {
            throw new Error("Invalid status received from Paychangu");
        }

        // Update the payment status
        const updateResult = await this.updatePaymentStatus(charge_id, paymentStatus);

        if (updateResult.affectedRows === 0) {
            throw new Error("Failed to update payment status");
        }

        return {
            transaction_id: charge_id,
            status: paymentStatus
        };
    }
}

module.exports = Payments;