// index.js or app.js

const express = require("express");
const dotenv = require("dotenv");
const cors = require("cors");
const morgan = require("morgan");
const jwt = require("jsonwebtoken");

const { connectToDatabase } = require("./utils/connectDb");
const { loginHandler } = require("./controllers/login");
const { authenticateToken } = require("./middlewares/authentication");

// Load environment variables from .env file
dotenv.config();


// Create Express app
const app = express();
const port = process.env.PORT || 8080;
app.use(express.json());

// Enable Cross-Origin Resource Sharing (CORS)
app.use(cors());

// Use Morgan for HTTP request logging
app.use(morgan("dev")); // 'dev' is a predefined format string

app.get("/", (req,res)=>{res.send('ok')});

const doctorsRouter = require('./routes/doctors.route');
const hospitalsRouter = require('./routes/hospitals.route');
const labsRouter = require('./routes/labs.route');
const pharmaciesRouter = require('./routes/pharmacies.route');
const raysRouter = require('./routes/rays.route');

app.use(`/doctors`, doctorsRouter);
app.use(`/hospitals`, hospitalsRouter);
app.use(`/labs`, labsRouter);
app.use(`/pharmacies`, pharmaciesRouter);
app.use(`/rays`, raysRouter);

app.get(`/version.php`, async (req,res)=>{
  const connection = await connectToDatabase();
    connection.query("SELECT * FROM version", undefined, (error, results) => {
      if (error) {
        res.send(error)
      } else {
        res.send(results)
      }
    });
});

app.post("/login", loginHandler);

connectToDatabase();
function getTablesSet(choice) {
  // Replace these with your actual table names
  const tables = {
    doctors: {
      main: "webapp_doctors",
      relation: "webapp_docrelation",
      number: "webapp_docnumber",
      brancherel: "webapp_docbrancherel",
      branche: "webapp_docbranche",
    },
    hospitals: {
      main: "webapp_hospitals",
      relation: "webapp_hosrelation",
      number: "webapp_hosnumber",
      brancherel: "webapp_hosbrancherel",
      branche: "webapp_hosbranche",
    },
        labs: {
      main: "webapp_labs",
      relation: "webapp_labrelation",
      number: "webapp_labnumber",
      brancherel: "webapp_labbrancherel",
      branche: "webapp_labbranche",
    },
        pharmacies: {
      main: "webapp_pharmacies",
      relation: "webapp_pharelation",
      number: "webapp_phanumber",
      brancherel: "webapp_phabrancherel",
      branche: "webapp_phabranche",
    },
        rays: {
      main: "webapp_rays",
      relation: "webapp_rayrelation",
      number: "webapp_raynumber",
      brancherel: "webapp_raybrancherel",
      branche: "webapp_raybranche",
    },
  };
  return tables[choice];
}

const special_data = {
  1: "الجراحة الــعامـة",
  2: "جراحة الاوعية الدموية",
  3: "جراحة الاطفـال",
  4: "جراحة القلب والصدر",
  5: "جراحة التجميل والحـــروق",
  6: "العظام",
  7: "جراحة المسالك البولية والكـلي",
  8: "الباطنة العامـة",
  9: "باطنة كلي",
  10: "باطنة قلب وأوعية دموية",
  11: "باطنة حميات وجهاز هضمي وكبد",
  12: "الامراض الصدريــة",
  13: "أنف وأذن وحنجرة",
  14: "عيون / رمد",
  15: "جلدية وأمراض تناسلية",
  16: "روماتيزم ومناعة",
  17: "غدد صماء",
  18: "الأورام والطب النووي",
  19: "علاج الالام",
  20: "جراحة المخ والأعصاب",
  21: "مخ واعصاب وطب نفسي",
  22: "النساء والتوليد",
  23: "الأطفال",
  24: "تغذية علاجية",
  25: "طب وجراحة الأسنان",
  26: "العلاج الطبيعي",
};
async function addInfo(type, body, connectionConfig) {
  const connection = await connectToDatabase();

  const query = (sql, params) => {
    return new Promise((resolve, reject) => {
      connection.query(sql, params, (error, results) => {
        if (error) {
          reject(error);
        } else {
          resolve(results);
        }
      });
    });
  };

  const tables = getTablesSet(type);
  let includeSpecialFields = type === "doctors";

  let name = body.name || null;
  let special = includeSpecialFields ? body.special || null : null;
  let notes = body.note || null;
  let specialName = includeSpecialFields && special ? special_data[special] || "" : "";

  let insertMainQuery;
  let insertData;

  if (includeSpecialFields) {
    insertMainQuery = `INSERT INTO ${tables.main} (name, special, specialname, note) VALUES (?, ?, ?, ?)`;
    insertData = [name, special, specialName, notes];
  } else {
    insertMainQuery = `INSERT INTO ${tables.main} (name, note) VALUES (?, ?)`;
    insertData = [name, notes];
  }

  try {
    const mainTableResult = await query(insertMainQuery, insertData);
    let mainTableId = mainTableResult.insertId;

    const extraInfoData = body.extra_info && body.extra_info.data;
    if (extraInfoData) {
      for (const data of extraInfoData) {
        const { branch_name, country_value, numbers } = data;
        const branchResult = await query(`INSERT INTO ${tables.branche} (branche) VALUES (?)`, [branch_name]);
        let branchId = branchResult.insertId;

        await query(`INSERT INTO ${tables.relation} (relation_name_id, relation_branche_id, relation_country_id) VALUES (?, ?, ?)`, [mainTableId, branchId, country_value]);

        for (const number of numbers) {
          const numberResult = await query(`INSERT INTO ${tables.number} (number) VALUES (?)`, [number.value]);
          let numberId = numberResult.insertId;

          await query(`INSERT INTO ${tables.brancherel} (relation_number_id, relation_branche_id) VALUES (?, ?)`, [numberId, branchId]);
        }
      }
    }
  } catch (error) {
    console.error("Error during database operation:", error);
    throw error; // or handle the error as you see fit
  } finally {
    connection.end();
  }
}


async function postHandler(req, res) {
  try {
    await addInfo(req, res);
    res.status(200).send("record has been added successfully");
  } catch (error) {
    console.log("error", error);
            res.status(500).json({
            status: 'error',
            message: error.message,
            ...(process.env.NODE_ENV === 'development' && { stack: error.stack }) // Include stack trace in development mode only
        });
  }
}


function getDoctorDetails(connection, choice, id) {
    return new Promise((resolve, reject) => {
        let doctorQuery = id
            ? `SELECT * FROM ${choice.main} WHERE id = ?`
            : `SELECT * FROM ${choice.main}`;

        connection.query(doctorQuery, id ? [id] : [], (err, doctors) => {
            if (err) {
                reject(err);
                return;
            }

            let response = [];
            let processDoctors = async () => {
                for (let doctor of doctors) {
                    let data = {
                        name: doctor.name,
                        id: doctor.id,
                        note: doctor.note,
                        special: doctor.special, // Assuming this field exists in both doctors and hospitals tables
                        extra_info: {
                            data: [],
                        },
                    };

                    let relationQuery = `
                        SELECT dr.*, b.branche as branch_name, c.name as country_name 
                        FROM ${choice.relation} dr
                        JOIN ${choice.branche} b ON dr.relation_branche_id = b.id
                        LEFT JOIN webapp_countries c ON dr.relation_country_id = c.id
                        WHERE dr.relation_name_id = ?`;

                    // Wrap each sub-query in a promise
                    try {
                        const docrelations = await new Promise((resolve, reject) => {
                            connection.query(relationQuery, [doctor.id], (err, results) => {
                                if (err) reject(err);
                                else resolve(results);
                            });
                        });

                        for (let docrelation of docrelations) {
                            const numberQuery = `
                                SELECT dn.id, dn.number 
                                FROM ${choice.brancherel} dbr
                                JOIN ${choice.number} dn ON dbr.relation_number_id = dn.id
                                WHERE dbr.relation_branche_id = ?`;

                            const numbers = await new Promise((resolve, reject) => {
                                connection.query(numberQuery, [docrelation.relation_branche_id], (err, results) => {
                                    if (err) reject(err);
                                    else resolve(results);
                                });
                            });

                            let mappedNumbers = numbers.map(number => ({
                                id: number.id,
                                value: number.number,
                            }));

                            let branchData = {
                                branch_name: docrelation.branch_name,
                                branch_value: docrelation.relation_branche_id,
                                country_name: docrelation.country_name,
                                country_value: docrelation.relation_country_id,
                                numbers: mappedNumbers,
                            };

                            data.extra_info.data.push(branchData);
                        }

                        response.push(data);
                    } catch (error) {
                        reject(error);
                        return;
                    }
                }
                resolve(response);
            };

            processDoctors();
        });
    });
}

app.get("/data/:type?/:id?", authenticateToken, async (req, res) => {
    const id = req.params.id || null;
    const type = req.params.type || null;
    const connection = await connectToDatabase();
    try {
        const tableSet = getTablesSet(type);
        if (id) {
            // Check if the doctor exists
            const [existing] = await new Promise((resolve, reject) => {
                connection.query(
                    `SELECT * FROM ${tableSet.main} WHERE id = ?`,
                    [id],
                    (err, results) => {
                        if (err) reject(err);
                        else resolve(results);
                    }
                );
            });

            if (existing.length === 0) {
                return res.status(404).json({ message: "Doctor not found" });
            }
        }
        const doctors = await getDoctorDetails(connection, tableSet, id);
        res.json(doctors);
    } catch (error) {
        console.log("error", error);
        res.status(500).json({
            status: 'error',
            message: error.message,
            ...(process.env.NODE_ENV === 'development' && { stack: error.stack }) // Include stack trace in development mode only
        });
    } finally {
        connection.end();
    }
});

function queryPromise(connection, query, params) {
    return new Promise((resolve, reject) => {
        connection.query(query, params, (err, results) => {
            if (err) {
                reject(err);
            } else {
                resolve(results);
            }
        });
    });
}

// Modify postHandler to use the new addInfo
app.post("/data/:type", authenticateToken, async (req, res) => {
  try {
    const connection = await connectToDatabase();
    await addInfo(req.params.type, req.body, connection);
    connection.end();
    res.status(200).send("Record has been added successfully");
  } catch (error) {
    console.log("error", error);
    connection.end();
    res.status(500).json({
      status: 'error',
      message: error.message,
      ...(process.env.NODE_ENV === 'development' && { stack: error.stack })
    });
  }
});



const updateData = async (req, res) => {
  const { type, id } = req.params;
  const connection = await connectToDatabase();

  const query = (sql, params) => {
    return new Promise((resolve, reject) => {
      connection.query(sql, params, (error, results) => {
        if (error) {
          reject(error);
        } else {
          resolve(results);
        }
      });
    });
  };

  const tables = getTablesSet(type);
  const body = req.body;

  try {
    await query('START TRANSACTION');

    if (type == "doctors") {
      let updateMain = `UPDATE ${tables.main} SET name = ?, note = ?, special = ? WHERE id = ?`;
      await query(updateMain, [body.name, body.note, body.special, id]);
    } else {
      let updateMain = `UPDATE ${tables.main} SET name = ?, note = ? WHERE id = ?`;
      await query(updateMain, [body.name, body.note, id]);
    }

    const oldBranches = await query(
      `SELECT relation_branche_id FROM ${tables.relation} WHERE relation_name_id = ?`, 
      [id]
    );
    const branchIds = oldBranches.map((branch) => branch.relation_branche_id);

    await query(`DELETE FROM ${tables.relation} WHERE relation_name_id = ?`, [id]);

    if (branchIds.length > 0) {
      await query(`DELETE FROM ${tables.brancherel} WHERE relation_branche_id IN (?)`, [branchIds]);
      await query(`DELETE FROM ${tables.number} WHERE id IN (SELECT relation_number_id FROM ${tables.brancherel} WHERE relation_branche_id IN (?))`, [branchIds]);
      await query(`DELETE FROM ${tables.branche} WHERE id IN (?)`, [branchIds]);
    }

    for (const data of body.extra_info.data) {
      const { branch_name, country_value, numbers } = data;
      const branchResult = await query(`INSERT INTO ${tables.branche} (branche) VALUES (?)`, [branch_name]);
      let branchId = branchResult.insertId;

      await query(`INSERT INTO ${tables.relation} (relation_name_id, relation_branche_id, relation_country_id) VALUES (?, ?, ?)`, [id, branchId, country_value]);

      for (const number of numbers) {
        const numberResult = await query(`INSERT INTO ${tables.number} (number) VALUES (?)`, [number.value]);
        let numberId = numberResult.insertId;

        await query(`INSERT INTO ${tables.brancherel} (relation_number_id, relation_branche_id) VALUES (?, ?)`, [numberId, branchId]);
      }
    }

    await query('COMMIT');
    res.json({ message: `${type.charAt(0).toUpperCase() + type.slice(1)} updated successfully` });
  } catch (error) {
    await query('ROLLBACK');
    console.error("Error during update operation:", error);
    res.status(500).json({
      status: 'error',
      message: error.message,
      ...(process.env.NODE_ENV === 'development' && { stack: error.stack }) // Include stack trace in development mode only
    });
  } finally {
    connection.end();
  }
};


// Define your Express route for updating data
app.put("/data/:type/:id", authenticateToken, updateData);

// DELETE handler for deleting a doctor and its related data
const mysql = require('mysql');

app.delete("/data/:type/:id", authenticateToken, async (req, res) => {
  const { type, id } = req.params;
  const connection = await connectToDatabase();

  const query = (sql, params) => {
    return new Promise((resolve, reject) => {
      connection.query(sql, params, (error, results) => {
        if (error) {
          reject(error);
        } else {
          resolve(results);
        }
      });
    });
  };

  const tables = getTablesSet(type);

  try {
    await query('START TRANSACTION');

    const oldBranches = await query(`SELECT relation_branche_id FROM ${tables.relation} WHERE relation_name_id = ?`, [id]);
    const branchIds = oldBranches.map((branch) => branch.relation_branche_id);

    await query(`DELETE FROM ${tables.relation} WHERE relation_name_id = ?`, [id]);

    if (branchIds.length > 0) {
      await query(`DELETE FROM ${tables.brancherel} WHERE relation_branche_id IN (?)`, [branchIds]);
      await query(`DELETE FROM ${tables.number} WHERE id IN (SELECT relation_number_id FROM ${tables.brancherel} WHERE relation_branche_id IN (?))`, [branchIds]);
      await query(`DELETE FROM ${tables.branche} WHERE id IN (?)`, [branchIds]);
    }

    const deleteDoctorResult = await query(`DELETE FROM ${tables.main} WHERE id = ?`, [id]);
    if (deleteDoctorResult.affectedRows === 0) {
      await query('ROLLBACK');
      return res.status(404).json({ message: `${type.charAt(0).toUpperCase() + type.slice(1)} not found` });
    }

    await query('COMMIT');
    res.json({ message: `${type.charAt(0).toUpperCase() + type.slice(1)} and all related data have been deleted successfully` });
  } catch (error) {
    await query('ROLLBACK');
    console.error("Error during the delete operation:", error);
    res.status(500).json({
      status: 'error',
      message: error.message,
      ...(process.env.NODE_ENV === 'development' && { stack: error.stack }) // Include stack trace in development mode only
    });
  } finally {
    connection.end();
  }
});

// Start the server
app.listen(() => {
  console.log(`Server running...`);
});
