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

class PatientReportEmail {
    static async findByEmailAndCode(email, code) {
        const query = `
            SELECT pre.*, pr.request_id
            FROM patient_reports_emails pre
            JOIN patient_reports pr ON pre.patient_report_id = pr.id
            WHERE pre.email = ? AND pre.code = ?
        `;

        try {
            const [rows] = await db.execute(query, [email, code]);
            return rows.length > 0 ? rows[0] : null;
        } catch (error) {
            throw error;
        }
    }

    static async getPatientReportWithDetails(patientReportId) {
        const query = `
            SELECT 
                pr.*,
                it.name as incident_type_name,
                ar.request_by,
                ar.location,
                ar.destination,
                u.first_name,
                u.last_name,
                u.email as user_email,
                c.date_of_birth,
                fa.id as fluid_id,
                fa.fluid_name,
                fa.volume,
                fa.time_given as fluid_time_given,
                da.id as drug_administered_id,
                da.drug_id,
                da.dose,
                da.route,
                da.time_given as drug_time_given,
                d.name as drug_name,
                d.type as drug_type,
                o.id as observation_id,
                o.observation,
                o.value,
                o.created_at as observation_time,
                dvr.driver_id,
                du.first_name as driver_first_name,
                du.last_name as driver_last_name
            FROM patient_reports pr
            LEFT JOIN incident_type it ON pr.incident_type_id = it.id
            LEFT JOIN ambulance_requests ar ON pr.request_id = ar.id
            LEFT JOIN users u ON ar.request_by = u.id
            LEFT JOIN clients c ON u.id = c.user_id
            LEFT JOIN dispatch dvr ON ar.id = dvr.ambulance_request_id
            LEFT JOIN driver dr ON dvr.driver_id = dr.id
            LEFT JOIN users du ON dr.user_id = du.id
            LEFT JOIN fluids_administered fa ON pr.id = fa.patient_report_id
            LEFT JOIN drugs_administered da ON pr.id = da.patient_report_id
            LEFT JOIN drugs d ON da.drug_id = d.id
            LEFT JOIN observations o ON pr.id = o.patient_report_id
            WHERE pr.id = ?
            ORDER BY o.created_at DESC, da.time_given DESC, fa.time_given DESC
        `;

        try {
            const [rows] = await db.execute(query, [patientReportId]);
            
            if (rows.length === 0) {
                return null;
            }

            // Calculate age from user's date of birth if available
            const calculateAge = (dateOfBirth) => {
                if (!dateOfBirth) return null;
                const today = new Date();
                const birthDate = new Date(dateOfBirth);
                let age = today.getFullYear() - birthDate.getFullYear();
                const monthDiff = today.getMonth() - birthDate.getMonth();
                if (monthDiff < 0 || (monthDiff === 0 && today.getDate() < birthDate.getDate())) {
                    age--;
                }
                return age;
            };

            // Group the results by patient report
            const report = {
                id: rows[0].id,
                request_id: rows[0].request_id,
                incident_type_id: rows[0].incident_type_id,
                incident_type_name: rows[0].incident_type_name,
                general_complaints: rows[0].general_complaints,
                impression: rows[0].impression,
                patient_allergies: rows[0].patient_allergies,
                patient_past_history: rows[0].patient_past_history,
                patient_admitting_hospital: rows[0].patient_admitting_hospital,
                created_at: rows[0].created_at,
                updated_at: rows[0].updated_at,
                patient_info: {
                    name: rows[0].first_name,
                    surname: rows[0].last_name,
                    email: rows[0].user_email,
                    age: calculateAge(rows[0].date_of_birth)
                },
                location_info: {
                    pickup_location: rows[0].location,
                    destination: rows[0].destination
                },
                driver_info: rows[0].driver_first_name ? {
                    name: rows[0].driver_first_name,
                    surname: rows[0].driver_last_name
                } : null,
                fluids: [],
                drugs: [],
                observations: []
            };

            // Group fluids, drugs, and observations
            const fluidsMap = new Map();
            const drugsMap = new Map();
            const observationsMap = new Map();

            rows.forEach(row => {
                if (row.fluid_id && !fluidsMap.has(row.fluid_id)) {
                    fluidsMap.set(row.fluid_id, {
                        id: row.fluid_id,
                        fluid_name: row.fluid_name,
                        volume: row.volume,
                        time_given: row.fluid_time_given
                    });
                }

                if (row.drug_administered_id && !drugsMap.has(row.drug_administered_id)) {
                    drugsMap.set(row.drug_administered_id, {
                        id: row.drug_administered_id,
                        drug_id: row.drug_id,
                        drug_name: row.drug_name,
                        drug_type: row.drug_type,
                        dose: row.dose,
                        route: row.route,
                        time_given: row.drug_time_given
                    });
                }

                if (row.observation_id && !observationsMap.has(row.observation_id)) {
                    observationsMap.set(row.observation_id, {
                        id: row.observation_id,
                        observation: row.observation,
                        value: row.value,
                        created_at: row.observation_time
                    });
                }
            });

            report.fluids = Array.from(fluidsMap.values());
            report.drugs = Array.from(drugsMap.values());
            report.observations = Array.from(observationsMap.values());

            return report;
        } catch (error) {
            throw error;
        }
    }
}

module.exports = PatientReportEmail;
