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

const ActivityLog = {
    /**
     * Get logs with pagination, search (email), and filter (log_type)
     */
    getAll: async (page = 1, limit = 20, searchEmail = '', logType = '') => {
        try {
            const offset = (page - 1) * limit;
            let query = `
        SELECT 
          al.*, 
          u.first_name, 
          u.last_name, 
          u.email 
        FROM activitylogs al
        LEFT JOIN users u ON al.user_id = u.user_id
        WHERE 1=1
      `;
            let countQuery = `
        SELECT COUNT(*) as total 
        FROM activitylogs al
        LEFT JOIN users u ON al.user_id = u.user_id
        WHERE 1=1
      `;
            let params = [];

            // Email & Description & LogType Search
            if (searchEmail && searchEmail.trim()) {
                const term = `%${searchEmail}%`;
                query += ' AND (u.email LIKE ? OR al.logmessage LIKE ? OR al.log_type LIKE ?)';
                countQuery += ' AND (u.email LIKE ? OR al.logmessage LIKE ? OR al.log_type LIKE ?)';
                params.push(term, term, term);
            }

            // Log Type Filter
            if (logType && logType.trim()) {
                query += ' AND al.log_type = ?';
                countQuery += ' AND al.log_type = ?';
                params.push(logType);
            }

            // Get total count
            const [countResult] = await pool.query(countQuery, params);
            const total = countResult[0].total;

            // Get Data
            // Ensure numbers
            const limitNum = parseInt(limit, 10) || 20;
            const offsetNum = parseInt(offset, 10) || 0;

            query += ' ORDER BY al.id DESC LIMIT ? OFFSET ?';
            const [rows] = await pool.query(query, [...params, limitNum, offsetNum]);

            return {
                data: rows,
                pagination: {
                    total,
                    page: parseInt(page, 10),
                    limit: limitNum,
                    pages: Math.ceil(total / limitNum),
                },
            };
        } catch (error) {
            throw new Error(`Database error: ${error.message}`);
        }
    },

    /**
     * Get specific logs for dashboard (Recent Activity)
     */
    getDashboardLogs: async (limit = 20) => {
        try {
            const targetTypes = ['report-generation', 'userlogin_failed', 'custom_cover_update', 'userlogin'];
            // We need to construct the IN clause dynamically or just use it straight since it's fixed
            const placeholders = targetTypes.map(() => '?').join(',');

            const query = `
        SELECT 
          al.id, 
          al.log_type, 
          al.logmessage, 
          al.created,
          u.first_name,
          u.last_name,
          u.email
        FROM activitylogs al
        LEFT JOIN users u ON al.user_id = u.user_id
        WHERE al.log_type IN (${placeholders})
        ORDER BY al.id DESC
        LIMIT ?
      `;

            const [rows] = await pool.query(query, [...targetTypes, limit]);
            return rows;
        } catch (error) {
            throw new Error(`Database error: ${error.message}`);
        }
    },

    /**
     * Get distinct log types for filter dropdown
     */
    getLogTypes: async () => {
        try {
            const [rows] = await pool.query('SELECT DISTINCT log_type FROM activitylogs ORDER BY log_type ASC');
            return rows.map(r => r.log_type);
        } catch (error) {
            throw new Error(`Database error: ${error.message}`);
        }
    }
};

module.exports = ActivityLog;
