const { pool, db } = require("../config/database")




// Insert menu
const customSecMenuAdd = async (tablename, menuId) => {
    try {
        // Replace "_" with " " in the tablename
        const origTableName = tablename.replace(/_/g, " ");

        // Construct the new MenuDet object
        const menuDet = {
            menuId: menuId,
            itemSeq: 8001,
            main_menu_icon_name: tablename,
            menuItemDesc: origTableName,
            moduleName: tablename,
            main_menu_action_name: tablename,
            status: "Enable"
        };

        // Insert the new record into the sec_menu_det table
        const insertQuery = `
        INSERT INTO sec_menu_det 
        (menu_id, item_seq, main_menu_icon_name, menu_item_desc, module_name, main_menu_action_name, status) 
        VALUES (?, ?, ?, ?, ?, ?, ?)
      `;

        const result = await executeQuery(insertQuery, [
            menuDet.menuId,
            menuDet.itemSeq,
            menuDet.main_menu_icon_name,
            menuDet.menuItemDesc,
            menuDet.moduleName,
            menuDet.main_menu_action_name,
            menuDet.status
        ]);

        // Call the sync function (implement this based on your requirement)
        await sync(menuId, { /* pass necessary params if needed */ });

        // Return the inserted record or some indication of success
        return result;
    } catch (error) {
        console.error("Error in customSecMenuAdd:", error);
        throw error;
    }
};

//   Insert dashboard
const createDashboard = (req, res) => {
    const body = req.body;

    // Get current date and time in the format YYYY-MM-DD HH:MM:SS
    const today = new Date();
    const date =
        today.getFullYear() + "-" + (today.getMonth() + 1) + "-" + today.getDate();
    const time =
        today.getHours() + ":" + today.getMinutes() + ":" + today.getSeconds();
    const dateTime = date + " " + time;

    // Step 1: Check if the entity already exists in builder_entity_t table
    const existingEntityQuery = `SELECT * FROM builder_entity_t WHERE job_name = ? AND job_type = ?`;

    pool.query(
        existingEntityQuery,
        [body.dashboard_name, "Dashboard"],
        (err, existingEntity) => {
            if (err) {
                console.error("Error checking existing entity:", err);
                return res.status(500).json({
                    success: 0,
                    message: "Error checking existing entity",
                });
            }

            if (existingEntity.length > 0) {
                console.log(`${body.dashboard_name} dashboard already exists`);
                return res.status(409).json({
                    success: 0,
                    message: "Dashboard already exists",
                });
            }

            // Step 2: Insert into dashbord_header
            const insertHeaderQuery = `
          INSERT INTO dashbord_header SET 
            created_at = ?,
            updated_at = ?,
            is_build = ?,
            dashboard_name = ?,
            description = ?,
            menu_name = ?,
            module_id = ?,
            secuirity_profile = ?,
            tech_stack = ?,
            is_updated = ?,
            object_type = ?,
            sub_object_type = ?,
            testing = ?
        `;

            const headerValues = [
                dateTime,
                dateTime,
                body.is_build || null,
                body.dashboard_name || null,
                body.description || null,
                body.menu_name || null,
                body.module_id || 0, // Provide a default value for module_id
                body.secuirity_profile || null,
                body.tech_stack || null,
                body.is_updated || null,
                body.object_type || null,
                body.sub_object_type || null,
                body.testing || false,
            ];

            pool.query(insertHeaderQuery, headerValues, (headerError, headerResults) => {
                if (headerError) {
                    console.error("Error inserting dashboard header:", headerError);
                    return res.status(500).json({
                        success: 0,
                        message: "Failed to create dashboard",
                    });
                }

                if (!headerResults.insertId) {
                    return res.status(500).json({
                        success: 0,
                        message: "Failed to create dashboard",
                    });
                }

                const dashboardId = headerResults.insertId;

                // Step 3: Insert into dashbord1_line
                const insertLineQuery = `
            INSERT INTO dashbord1_line SET 
              created_at = ?,
              updated_at = ?,
              model = ?,
              header_id = ?,
              dashbord_header_id = ?
          `;

                const lineValues = [
                    dateTime,
                    dateTime,
                    body.model || null, // Assuming 'model' is in the request body
                    body.header_id || null, // Assuming 'header_id' is in the request body
                    dashboardId,
                ];

                pool.query(insertLineQuery, lineValues, (lineError, lineResults) => {
                    if (lineError) {
                        console.error("Error inserting dashboard line:", lineError);
                        // Optionally, rollback the previous dashboard_header insertion
                        return res.status(500).json({
                            success: 0,
                            message: "Failed to create dashboard and line",
                        });
                    }

                    // Return success response
                    return res.status(201).json({
                        success: 1,
                        message: "Dashboard and line created successfully",
                    });
                });
            });
        }
    );
};

// save report
const saveReport = (req, res) => {
    const body = req.body;

    // Get the current date and time
    const today = new Date();
    const date =
        today.getFullYear() + "-" + (today.getMonth() + 1) + "-" + today.getDate();
    const time =
        today.getHours() + ":" + today.getMinutes() + ":" + today.getSeconds();
    const dateTime = date + " " + time;

    // Step 1: Check if the report with the given name already exists in builder_entity_t
    const existingEntityQuery = `SELECT * FROM builder_entity_t WHERE job_name = ? AND job_type = ?`;

    pool.query(
        existingEntityQuery,
        [body.reportName, "Report"],
        (err, existingEntity) => {
            if (err) {
                console.error("Error checking existing report:", err);
                return res.status(500).json({
                    success: 0,
                    message: "Error checking existing report",
                });
            }

            if (existingEntity.length > 0) {
                console.log(`${body.reportName} report already exists`);
                return res.status(409).json({
                    success: 0,
                    message: "Report already exists",
                });
            }

            // Step 2: Insert into rpt_builder2_t table
            const insertReportQuery = `
          INSERT INTO rpt_builder2_t SET 
            created_at = ?,
            updated_at = ?,
            report_name = ?,
            description = ?,
            is_sql = ?
        `;

            const reportValues = [
                dateTime,
                dateTime,
                body.reportName || null,
                body.description || null,
                body.isSql || false,  // Boolean field
            ];

            pool.query(insertReportQuery, reportValues, (reportError, reportResults) => {
                if (reportError) {
                    console.error("Error inserting report:", reportError);
                    return res.status(500).json({
                        success: 0,
                        message: "Failed to create report",
                    });
                }

                if (!reportResults.insertId) {
                    return res.status(500).json({
                        success: 0,
                        message: "Failed to create report",
                    });
                }

                const reportId = reportResults.insertId;

                // Step 3: Insert into rpt_builder2_lines_t table
                const insertLineQuery = `
            INSERT INTO rpt_builder2_lines_t SET 
              created_at = ?,
              updated_at = ?,
              model = ?,
              header_id = ?
          `;

                const lineValues = [
                    dateTime,
                    dateTime,
                    body.model || null,  // Assuming 'model' is part of the request body
                    reportId,
                ];

                pool.query(insertLineQuery, lineValues, (lineError, lineResults) => {
                    if (lineError) {
                        console.error("Error inserting report line:", lineError);
                        return res.status(500).json({
                            success: 0,
                            message: "Failed to create report and line",
                        });
                    }

                    // Step 4: Save the builder entity into builder_entity_t table
                    const saveEntityQuery = `
              INSERT INTO builder_entity_t SET 
                job_name = ?,
                job_type = ?
            `;

                    pool.query(
                        saveEntityQuery,
                        [body.reportName, "Report"],
                        (entityError, entityResults) => {
                            if (entityError) {
                                console.error("Error saving builder entity:", entityError);
                                return res.status(500).json({
                                    success: 0,
                                    message: "Failed to save builder entity",
                                });
                            }

                            // Return success response after all operations
                            return res.status(201).json({
                                success: 1,
                                message: "Report and line created successfully",
                            });
                        }
                    );
                });
            });
        }
    );
};

// Sync 
const sync = async (gmenuid, gr) => {
    const list = [];

    try {
        // Step 1: Find MenuDet by gmenuid
        const formenuQuery = `SELECT * FROM sec_menu_det WHERE menu_item_id = ?`;
        const [formenu] = await executeQuery(formenuQuery, [gmenuid]);

        if (!formenu) {
            throw new Error(`MenuDet with id ${gmenuid} not found.`);
        }

        // Step 2: Get all GrpMenuAccess records for the found MenuDet
        const grpMenuAccessQuery = `SELECT * FROM sec_grp_menu_access WHERE menu_item_id = ?`;
        const all = await executeQuery(grpMenuAccessQuery, [formenu.menu_item_id]);

        // Step 3: Update GrpMenuAccess records with data from MenuDet
        for (let a of all) {
            const updateQuery = `
          UPDATE sec_grp_menu_access
          SET item_seq = ?, menu_item_desc = ?, module_name = ?, status = ?, main_menu_action_name = ?, main_menu_icon_name = ?, menu_id = ?
          WHERE id = ?
        `;
            await executeQuery(updateQuery, [
                formenu.item_seq,
                formenu.menu_item_desc,
                formenu.module_name,
                formenu.status,
                formenu.main_menu_action_name,
                formenu.main_menu_icon_name,
                formenu.menu_id,
                a.id
            ]);

            // Add updated record to the list
            list.push(a);
        }

        // Step 4: Find all submenus related to the given menu
        const submenuQuery = `SELECT * FROM sec_menu_det WHERE menu_id = ?`;
        const submenu = await executeQuery(submenuQuery, [gmenuid]);

        // Step 5: Iterate through submenus and update or insert GrpMenuAccess records
        for (let i = 0; i < submenu.length; i++) {
            const m = submenu[i];

            // Step 6: Find GrpMenuAccess for this submenu
            const findAccessQuery = `SELECT * FROM sec_grp_menu_access WHERE menu_item_id = ?`;
            const li = await executeQuery(findAccessQuery, [m.menu_item_id]);

            if (li.length === 0) {
                // Step 7: If no access found, insert new GrpMenuAccess records
                const findGroupQuery = `SELECT * FROM sec_grp_menu_access WHERE menu_item_id = ?`;
                const grp = await executeQuery(findGroupQuery, [gmenuid]);

                for (let a of grp) {
                    const findRoleQuery = `SELECT * FROM sec_user_group WHERE usr_grp = ?`;
                    const app = await executeQuery(findRoleQuery, [a.usr_grp]);

                    // Set the fields in the GrpMenuAccess object
                    gr.usr_grp = app[0].usr_grp;
                    gr.menu_item_id = m.menu_item_id;
                    gr.item_seq = m.item_seq;
                    gr.menu_item_desc = m.menu_item_desc;
                    gr.module_name = m.module_name;
                    gr.status = m.status;
                    gr.main_menu_action_name = m.main_menu_action_name;
                    gr.main_menu_icon_name = m.main_menu_icon_name;
                    gr.menu_id = m.menu_id;
                    gr.m_create = "true";
                    gr.m_delete = "true";
                    gr.m_edit = "true";
                    gr.m_query = "true";
                    gr.m_visible = "true";
                    gr.is_disable = "true";
                    gr.m_export = "true";
                    gr.created_at = new Date();
                    gr.updated_at = new Date();

                    // Insert new GrpMenuAccess
                    const insertAccessQuery = `
              INSERT INTO sec_grp_menu_access 
              (usr_grp, menu_item_id, item_seq, menu_item_desc, module_name, status, main_menu_action_name, main_menu_icon_name, menu_id, m_create, m_delete, m_edit, m_query, m_visible, is_disable, m_export, created_at, updated_at)
              VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            `;
                    await executeQuery(insertAccessQuery, [
                        gr.usr_grp,
                        gr.menu_item_id,
                        gr.item_seq,
                        gr.menu_item_desc,
                        gr.module_name,
                        gr.status,
                        gr.main_menu_action_name,
                        gr.main_menu_icon_name,
                        gr.menu_id,
                        gr.m_create,
                        gr.m_delete,
                        gr.m_edit,
                        gr.m_query,
                        gr.m_visible,
                        gr.is_disable,
                        gr.m_export,
                        gr.created_at,
                        gr.updated_at,
                    ]);

                    list.push(gr);
                }
            } else {
                // Step 8: If access exists, update the records
                for (let g of li) {
                    const updateAccessQuery = `
              UPDATE sec_grp_menu_access
              SET item_seq = ?, menu_item_desc = ?, module_name = ?, status = ?, main_menu_action_name = ?, main_menu_icon_name = ?, menu_id = ?
              WHERE id = ?
            `;
                    await executeQuery(updateAccessQuery, [
                        m.item_seq,
                        m.menu_item_desc,
                        m.module_name,
                        m.status,
                        m.main_menu_action_name,
                        m.main_menu_icon_name,
                        m.menu_id,
                        g.id
                    ]);

                    list.push(g);
                }
            }
        }
        return list;
    } catch (error) {
        console.error("Error in sync:", error);
        throw error;
    }
};


module.exports = {
    customSecMenuAdd,
    createDashboard,
    saveReport
}