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

class PatientReport {
    static async create(patientData) {
        const {
            request_id,
            incident_type_id,
            general_complaints,
            impression,
            patient_allergies,
            patient_past_history,
            patient_admitting_hospital
        } = patientData;

        const query = `
            INSERT INTO patient_reports 
            (request_id, incident_type_id, general_complaints, impression, patient_allergies, patient_past_history, patient_admitting_hospital)
            VALUES (?, ?, ?, ?, ?, ?, ?)
        `;

        try {
            const [result] = await db.execute(query, [
                request_id,
                incident_type_id || null,
                general_complaints || null,
                impression || null,
                patient_allergies || null,
                patient_past_history || null,
                patient_admitting_hospital || null
            ]);
            return result.insertId;
        } catch (error) {
            throw error;
        }
    }

    static async findByRequestId(requestId) {
        const query = `
            SELECT pr.*, it.name as incident_type_name
            FROM patient_reports pr
            LEFT JOIN incident_type it ON pr.incident_type_id = it.id
            WHERE pr.request_id = ?
        `;

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

    static async update(id, patientData) {
        const {
            incident_type_id,
            general_complaints,
            impression,
            patient_allergies,
            patient_past_history,
            patient_admitting_hospital
        } = patientData;

        const query = `
            UPDATE patient_reports 
            SET incident_type_id = ?, general_complaints = ?, impression = ?, patient_allergies = ?, patient_past_history = ?, 
                patient_admitting_hospital = ?, updated_at = CURRENT_TIMESTAMP
            WHERE id = ?
        `;

        try {
            const [result] = await db.execute(query, [
                incident_type_id || null,
                general_complaints || null,
                impression || null,
                patient_allergies || null,
                patient_past_history || null,
                patient_admitting_hospital || null,
                id
            ]);
            return result.affectedRows > 0;
        } catch (error) {
            throw error;
        }
    }

    static async getWithDetails(requestId) {
        const query = `
            SELECT 
                pr.*,
                it.name as incident_type_name,
                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
            FROM patient_reports pr
            LEFT JOIN incident_type it ON pr.incident_type_id = it.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.request_id = ?
            ORDER BY o.created_at DESC, da.time_given DESC, fa.time_given DESC
        `;

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

            // 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,
                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 = PatientReport;
