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

const Notification = {
    /**
     * Get all notifications with pagination, search, and filters
     */
    getAll: async (page = 1, limit = 10, search = '', startDate = null, endDate = null, filterType = 'all') => {
        try {
            const offset = (page - 1) * limit;
            let query = 'SELECT * FROM notifications WHERE 1=1';
            let countQuery = 'SELECT COUNT(*) as total FROM notifications WHERE 1=1';
            let params = [];

            // Filter Type Logic (All vs Deleted)
            if (filterType === 'deleted') {
                query += ' AND is_delete = 1';
                countQuery += ' AND is_delete = 1';
            } else {
                query += ' AND is_delete = 0';
                countQuery += ' AND is_delete = 0';
            }

            // Add search filter (title and description)
            if (search && search.trim()) {
                const searchTerm = `%${search}%`;
                query += ' AND (title LIKE ? OR description LIKE ?)';
                countQuery += ' AND (title LIKE ? OR description LIKE ?)';
                params.push(searchTerm, searchTerm);
            }

            // Add date range filter
            if (startDate) {
                query += ' AND DATE(date_created) >= ?';
                countQuery += ' AND DATE(date_created) >= ?';
                params.push(startDate);
            }
            if (endDate) {
                query += ' AND DATE(date_created) <= ?';
                countQuery += ' AND DATE(date_created) <= ?';
                params.push(endDate);
            }

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

            // Get paginated data
            const limitNum = parseInt(limit, 10) || 10;
            const offsetNum = parseInt(offset, 10) || 0;

            query += ' ORDER BY date_created 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 notification by id
     */
    findById: async (id) => {
        try {
            const [rows] = await pool.query(
                'SELECT * FROM notifications WHERE id = ?',
                [id]
            );
            return rows.length > 0 ? rows[0] : null;
        } catch (error) {
            throw new Error(`Database error: ${error.message}`);
        }
    },

    /**
     * Create new notification
     */
    create: async (data) => {
        try {
            const { title, description, status, from_date, end_date, notify, type } = data;
            const query = `
        INSERT INTO notifications (
          title, description, status, from_date, end_date, notify, type, date_created, date_updated
        ) VALUES (?, ?, ?, ?, ?, ?, ?, NOW(), NOW())
      `;
            const [result] = await pool.query(query, [
                title, description, status || 0, from_date || null, end_date || null, notify || 'All', type || 'general'
            ]);
            return result.insertId;
        } catch (error) {
            throw new Error(`Database error: ${error.message}`);
        }
    },

    /**
     * Update notification
     */
    update: async (id, data) => {
        try {
            const fields = [];
            const params = [];
            const allowedFields = ['title', 'description', 'status', 'from_date', 'end_date', 'notify', 'type'];

            for (const field of allowedFields) {
                if (data[field] !== undefined) {
                    fields.push(`${field} = ?`);
                    params.push(data[field]);
                }
            }

            if (fields.length === 0) return false;

            fields.push('date_updated = NOW()');
            const query = `UPDATE notifications SET ${fields.join(', ')} WHERE id = ?`;
            const [result] = await pool.query(query, [...params, id]);

            return result.affectedRows > 0;
        } catch (error) {
            throw new Error(`Database error: ${error.message}`);
        }
    },

    /**
     * Delete notification (soft delete)
     */
    delete: async (id) => {
        try {
            const [result] = await pool.query(
                'UPDATE notifications SET is_delete = 1, date_updated = NOW() WHERE id = ?',
                [id]
            );
            return result.affectedRows > 0;
        } catch (error) {
            throw new Error(`Database error: ${error.message}`);
        }
    },

    /**
     * Restore notification
     */
    restore: async (id) => {
        try {
            const [result] = await pool.query(
                'UPDATE notifications SET is_delete = 0, date_updated = NOW() WHERE id = ?',
                [id]
            );
            return result.affectedRows > 0;
        } catch (error) {
            throw new Error(`Database error: ${error.message}`);
        }
    }
};

module.exports = Notification;
