CREATE DEFINER=CURRENT_USER PROCEDURE `appointments_full_rpt`(IN `period_filter` VARCHAR(20), IN `start_date_filter` DATE, IN `end_date_filter` DATE, IN `department_id_filter` INT, IN `doctor_id_filter` INT, IN `payment_method_filter` INT, IN `payment_status_filter` VARCHAR(20))
BEGIN
                SELECT
                    ROW_NUMBER() OVER (ORDER BY appointments.date DESC, appointments.id DESC) AS Sqn,
                    DATE_FORMAT(appointments.date, '%d %b, %Y') AS Date,
                    IFNULL(appointments.appointment_no, appointments.id) AS `Appt No`,
                    patients.name AS Patient,
                    patients.patient_id AS `Patient ID`,
                    IFNULL(patients.phone, '-') AS Phone,
                    IFNULL(CONCAT(TIMESTAMPDIFF(YEAR, patients.dob, CURDATE()), ' years'), '-') AS Age,
                    IFNULL(patients.gender, '-') AS Sex,
                    IFNULL(hrm_staffs.name, '-') AS Doctor,
                    IFNULL(hrm_departments.name, '-') AS Department,
                    CONCAT('$ ', FORMAT(appointments.price, 2)) AS Charge,
                    CONCAT('$ ', FORMAT(IFNULL(pay.paid, 0), 2)) AS Paid,
                    CONCAT('$ ', FORMAT(IFNULL(pay.discount, 0), 2)) AS Discount,
                    CONCAT('$ ', FORMAT(IFNULL(pay.promotion, 0), 2)) AS Promotion,
                    CONCAT('$ ', FORMAT(appointments.price - IFNULL(pay.paid, 0) - IFNULL(pay.discount, 0) - IFNULL(pay.promotion, 0), 2)) AS Balance,
                    IFNULL(pay.methods, '-') AS `Payment method`,
                    appointments.payment_status AS `Payment status`
                FROM appointments
                LEFT JOIN patients ON patients.id = appointments.patient_id
                LEFT JOIN hrm_staffs ON hrm_staffs.id = appointments.doctor_id
                LEFT JOIN hrm_departments ON hrm_departments.id = appointments.department_id
                LEFT JOIN (
                    SELECT
                        p.reference_id,
                        SUM(CASE WHEN p.currency = 2 THEN p.payment / p.exchange_rate ELSE p.payment END) AS paid,
                        SUM(CASE WHEN p.currency = 2 THEN p.discount / p.exchange_rate ELSE p.discount END) AS discount,
                        SUM(CASE WHEN p.currency = 2 THEN p.promotion / p.exchange_rate ELSE p.promotion END) AS promotion,
                        GROUP_CONCAT(DISTINCT acc_payment_types.name SEPARATOR ', ') AS methods
                    FROM payments p
                    LEFT JOIN acc_payment_types ON acc_payment_types.id = p.payment_method
                    WHERE p.reference = 'appointment' AND p.status <> 'Refund'
                    GROUP BY p.reference_id
                ) pay ON pay.reference_id = appointments.id
                WHERE (department_id_filter = 0 OR appointments.department_id = department_id_filter)
                    AND (doctor_id_filter = 0 OR appointments.doctor_id = doctor_id_filter)
                    AND (payment_status_filter = '0' OR CONVERT(appointments.payment_status USING utf8mb4) COLLATE utf8mb4_unicode_ci = CONVERT(payment_status_filter USING utf8mb4) COLLATE utf8mb4_unicode_ci)
                    AND (
                        payment_method_filter = 0
                        OR EXISTS (
                            SELECT 1 FROM payments pm
                            WHERE pm.reference = 'appointment' AND pm.reference_id = appointments.id
                                AND pm.status <> 'Refund' AND pm.payment_method = payment_method_filter
                        )
                    )
                    AND (
                        period_filter = '0'
                        OR (period_filter = 'today' AND DATE(appointments.date) = CURDATE())
                        OR (period_filter = 'this_week' AND YEARWEEK(appointments.date, 1) = YEARWEEK(CURDATE(), 1))
                        OR (period_filter = 'this_month' AND YEAR(appointments.date) = YEAR(CURDATE()) AND MONTH(appointments.date) = MONTH(CURDATE()))
                        OR (period_filter = 'last_month' AND YEAR(appointments.date) = YEAR(CURDATE() - INTERVAL 1 MONTH) AND MONTH(appointments.date) = MONTH(CURDATE() - INTERVAL 1 MONTH))
                        OR (period_filter = 'custom' AND DATE(appointments.date) BETWEEN start_date_filter AND end_date_filter)
                    )
                ORDER BY appointments.date DESC, appointments.id DESC;
            END
