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

class TreatmentHistory {
    //ดึงข้อมูลการรักษา
    static async getTreatmentHistory(treatmenthistory_Id) {
        const query = `
            SELECT th.treatmenthistory_id,
                    th.tm_details,
                    th.dentist_name,
                    th.followUpdate, 
                    qd.patient_id, 
                    t.treatment_name, 
                    qd.date AS treatment_date
            FROM treatmenthistory th
            JOIN queuedetail qd ON th.queuedetail_id = qd.queuedetail_id
            JOIN treatment t ON qd.treatment_id = t.treatment_id
            WHERE qd.patient_id = ?
        `;

        return new Promise((resolve, reject) => {
            db.query(query, [treatmenthistory_Id], (error, results) => {
                if (error) {
                    console.error("SQL Error:", error);
                    reject(error);
                }
                console.log("SQL Query Results:", results);
                resolve(results);
            });
        });
    }

    //ดึงข้อมูลจาก queuedetail โดยใช้ queuedetail_id
    static async getTreatmentDetails(queuedetail_id) {
        const query = `
            SELECT qd.queuedetail_id, qd.patient_id, CONCAT(p.fname, ' ', p.lname) AS patient_name,
                    t.treatment_name, qd.date AS treatment_date
            FROM queuedetail qd
            JOIN patient p ON qd.patient_id = p.patient_id
            JOIN treatment t ON qd.treatment_id = t.treatment_id
            WHERE qd.queuedetail_id = ?
        `;

        return new Promise((resolve, reject) => {
            db.query(query, [queuedetail_id], (error, results) => {
                if (error) {
                    reject(error);
                }
                if (results.length === 0) {
                    resolve(null);
                } else {
                    resolve(results[0]);
                }
            });
        });
    }

    //ดึงข้อมูลการรักษาตาม ID ที่ได้รับจาก URL
    static async getTreatmentHistoryById(treatmenthistory_Id) {
        const query = `
            SELECT th.treatmenthistory_id,
                   th.tm_details,
                   th.dentist_name,
                   th.followUpdate, 
                   qd.patient_id, 
                   t.treatment_name, 
                   qd.date AS treatment_date
            FROM treatmenthistory th
            JOIN queuedetail qd ON th.queuedetail_id = qd.queuedetail_id
            JOIN treatment t ON qd.treatment_id = t.treatment_id
            WHERE th.treatmenthistory_id = ?
        `;
    
        return new Promise((resolve, reject) => {
            db.query(query, [treatmenthistory_Id], (error, results) => {
                if (error) {
                    console.error("SQL Error:", error);
                    reject(error);
                }
                resolve(results[0]); // คืนค่าผลลัพธ์ที่เป็นข้อมูลของการรักษาตาม ID
            });
        });
    }

    //อัปเดตข้อมูลการรักษา
    static async updateTreatmentHistory(treatmenthistory_id, dentist_name, tm_details, followUpdate) {
        const query = `
            UPDATE treatmenthistory
            SET dentist_name = ?, tm_details = ?, followUpdate = ?
            WHERE treatmenthistory_id = ?
        `;
        
        return new Promise((resolve, reject) => {
            db.query(query, [dentist_name, tm_details, followUpdate, treatmenthistory_id], (error, results) => {
                if (error) {
                    reject(error);
                }
                resolve(results); 
            });
        });
    }

    // เพิ่มประวัติการรักษาใหม่ลงในตาราง treatmenthistory
    static async addTreatmentHistory(queuedetail_id, dentist_name, tm_details, followUpdate) {
        const query = `
            INSERT INTO treatmenthistory (queuedetail_id, dentist_name, tm_details, followUpdate)
            VALUES (?, ?, ?, ?)
        `;

        return new Promise((resolve, reject) => {
            db.query(query, [queuedetail_id, dentist_name, tm_details, followUpdate], (error, results) => {
                if (error) {
                    reject(error);
                }
                resolve(results); 
            });
        });
    }

    //ลบประวัติการรักษา
    static async deleteTreatmentHistory(treatmenthistory_id) {
        const query = `DELETE FROM treatmenthistory WHERE treatmenthistory_id = ?`;
    
        return new Promise((resolve, reject) => {
            db.query(query, [treatmenthistory_id], (error, results) => {
                if (error) {
                    reject(error);
                }
                resolve(results); // เมื่อทำการลบเสร็จ
            });
        });
    }
    
}

module.exports = TreatmentHistory;