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

class General {
    static async getSummaryStats() {
        const [results] = await pool.query(`
            -- User Statistics
            SELECT 
                (SELECT COUNT(*) FROM users) AS total_users,
                (SELECT COUNT(*) FROM users  WHERE role = "PARTNER") AS total_samaritan_partners,
                
                -- Post Statistics
                (SELECT COUNT(*) FROM posts) AS total_posts,
                (SELECT COUNT(*) FROM posts WHERE is_approved = FALSE) AS pending_approval_posts,
                
                -- Message Statistics
                (SELECT COUNT(*) FROM messages) AS total_messages_sent,
                
                -- Cell Group Statistics
                (SELECT COUNT(*) FROM cell_groups) AS total_cell_groups,
                
                -- Donation Statistics
                (SELECT COUNT(*) FROM payments WHERE status = 'completed') AS total_donations,
                (SELECT IFNULL(SUM(amount), 0) FROM payments WHERE status = 'completed') AS total_donation_amount 
        `);
        
        return results[0]; 
    }
    static async getAuditTrails() {
    const [rows] = await pool.query(`
        SELECT 
            at.id AS audit_id,
            u.first_name,
            u.last_name,
            u.email,
            at.action,
            at.created_at AS action_date
        FROM audit_trail at
        JOIN users u ON at.user_id = u.id
        ORDER BY at.created_at DESC
    `);
    return rows;
}

}

module.exports = General;