
--
-- Dumping routines for database 'mis_new_stag'
--
/*!50003 DROP PROCEDURE IF EXISTS `delete_developer` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `delete_developer`(IN `p_developer_id` INT)
BEGIN
    DECLARE dev_exists INT;

    
    SELECT COUNT(*) INTO dev_exists
    FROM time_sheet_data
    WHERE developer_id = p_developer_id OR team_lead_id = p_developer_id;

    
    IF dev_exists = 0 THEN
        
        DELETE FROM developer_reporting_manager WHERE developer_id = p_developer_id;

        
        DELETE FROM master_developer WHERE id = p_developer_id;

        
        SELECT ROW_COUNT() AS affected_rows;
    ELSE
        
        SELECT 0 AS affected_rows;
    END IF;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `getLastTimesheetForTpl` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `getLastTimesheetForTpl`(
    IN developerIds VARCHAR(255),   -- Comma-separated list of developer IDs
    IN input_team_lead_id INT
)
BEGIN
    DECLARE sql_query TEXT;

    -- Build dynamic query
    SET sql_query = CONCAT(
        'SELECT ts1.id, ts1.date, ts1.developer_id, ts1.project_id, ts1.hours, ts1.actual_hours, ts1.remark, mp.tpl_id
         FROM time_sheet_data ts1
         JOIN (
             SELECT 
                 ts.developer_id, 
                 MAX(ts.`date`) AS last_date
             FROM 
                 time_sheet_data ts
             JOIN 
                 master_projects mp ON ts.project_id = mp.id
             WHERE 
                 ts.developer_id IN (', developerIds, ')
                 AND (mp.tpl_id = ', input_team_lead_id, ' OR mp.team_lead_id = ', input_team_lead_id, ')
             GROUP BY 
                 ts.developer_id
         ) ts2 
         ON ts1.developer_id = ts2.developer_id 
         AND ts1.`date` = ts2.last_date
         JOIN 
             master_projects mp ON ts1.project_id = mp.id 
         WHERE 
             (mp.tpl_id = ', input_team_lead_id, ' OR mp.team_lead_id = ', input_team_lead_id, ')'
    );

    -- Assign to user-defined variable
    SET @final_sql = sql_query;

    -- Prepare and execute
    PREPARE stmt FROM @final_sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `getLastTimesheetForTpl_bk` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `getLastTimesheetForTpl_bk`(IN `developerIds` VARCHAR(255), IN `input_team_lead_id` INT)
BEGIN
    
    SET @sql = CONCAT('
        SELECT ts1.id, ts1.date, ts1.developer_id, ts1.project_id, ts1.hours, ts1.actual_hours, remark, mp.tpl_id
        FROM time_sheet_data ts1
        JOIN (
            SELECT 
                ts.developer_id, 
                MAX(ts.`date`) AS last_date
            FROM 
                time_sheet_data ts
            JOIN 
                master_projects mp ON ts.project_id = mp.id
            WHERE 
                ts.developer_id IN (', developerIds, ')
                AND mp.tpl_id = ', input_team_lead_id, '
            GROUP BY 
                ts.developer_id
        ) ts2 ON ts1.developer_id = ts2.developer_id 
        AND ts1.`date` = ts2.last_date
        JOIN 
            master_projects mp ON ts1.project_id = mp.id 
        WHERE 
            mp.tpl_id = ', input_team_lead_id);

    
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `get_dashboard_data` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_dashboard_data`(IN `previousDays` BOOLEAN, IN `fromDate` DATE, IN `toDate` DATE)
BEGIN
    DECLARE latest_date DATETIME;
    DECLARE previous_date DATETIME;
    DECLARE limit_of_records INT;
    DECLARE where_condition VARCHAR(1000);  

    
    IF previousDays THEN
        SELECT value INTO limit_of_records 
        FROM configuration 
        WHERE key_name = 'max_fifteen';

        
        SET where_condition = CONCAT('WHERE DATE_FORMAT(t.date, ''%Y-%m-%d'') IN (',
            QUOTE(DATE_FORMAT(previous_date, '%Y-%m-%d')), ', ',
            QUOTE(DATE_FORMAT(latest_date, '%Y-%m-%d')), ')');

            SET where_condition = CONCAT('WHERE DATE_FORMAT(t.date, ''%Y-%m-%d'') = ',
                QUOTE(DATE_FORMAT(latest_date, '%Y-%m-%d')));

        ELSE
            
            IF toDate IS NULL THEN
                SET where_condition = CONCAT('WHERE DATE_FORMAT(t.date, ''%Y-%m-%d'') = ',
                    QUOTE(DATE_FORMAT(fromDate, '%Y-%m-%d')));
            ELSE
                SET where_condition = CONCAT('WHERE DATE_FORMAT(t.date, ''%Y-%m-%d'') BETWEEN ',
                    QUOTE(DATE_FORMAT(fromDate, '%Y-%m-%d')), ' AND ',
                    QUOTE(DATE_FORMAT(toDate, '%Y-%m-%d')));
            END IF;
        END IF;
    END IF;

    
    SET @sql = CONCAT('
        SELECT 
            t.id,
            t.developer_id,
            t.project_id,
            p.tpl_id,
            p.team_lead_id,
            d.name AS developer_name,
            CASE 
                WHEN tl.name IS NULL THEN d.name
                ELSE tl.name
            END AS tl_name,
            p.project_name,
            t.date,
            t.hours,
            t.actual_hours,
            t.remark,
            d.department
        FROM 
            time_sheet_data AS t
        LEFT JOIN 
            master_projects AS p ON p.id = t.project_id
        LEFT JOIN 
            master_developer AS d ON d.id = t.developer_id
        LEFT JOIN 
            master_developer AS tl ON tl.id = p.team_lead_id
        ', where_condition, '
        ORDER BY 
            t.project_id, t.date DESC;
    ');

    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `get_dashboard_data_bk` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_dashboard_data_bk`(IN `previousDays` BOOLEAN, IN `fromDate` DATE, IN `toDate` DATE)
BEGIN
    DECLARE latest_date DATETIME;
    DECLARE previous_date DATETIME;
    DECLARE limit_of_records INT;
    DECLARE where_condition VARCHAR(1000);  

    
    IF previousDays THEN
        SELECT value INTO limit_of_records 
        FROM configuration 
        WHERE key_name = 'max_fifteen';

        
        SET where_condition = CONCAT('WHERE DATE_FORMAT(t.date, ''%Y-%m-%d'') IN (',
            QUOTE(DATE_FORMAT(previous_date, '%Y-%m-%d')), ', ',
            QUOTE(DATE_FORMAT(latest_date, '%Y-%m-%d')), ')');

            SET where_condition = CONCAT('WHERE DATE_FORMAT(t.date, ''%Y-%m-%d'') = ',
                QUOTE(DATE_FORMAT(latest_date, '%Y-%m-%d')));

        ELSE
            
            IF toDate IS NULL THEN
                SET where_condition = CONCAT('WHERE DATE_FORMAT(t.date, ''%Y-%m-%d'') = ',
                    QUOTE(DATE_FORMAT(fromDate, '%Y-%m-%d')));
            ELSE
                SET where_condition = CONCAT('WHERE DATE_FORMAT(t.date, ''%Y-%m-%d'') BETWEEN ',
                    QUOTE(DATE_FORMAT(fromDate, '%Y-%m-%d')), ' AND ',
                    QUOTE(DATE_FORMAT(toDate, '%Y-%m-%d')));
            END IF;
        END IF;
    END IF;

    
    SET @sql = CONCAT('
        SELECT 
            t.id,
            t.developer_id,
            t.project_id,
            p.tpl_id,
            p.team_lead_id,
            d.name AS developer_name,
            CASE 
                WHEN tl.name IS NULL THEN d.name
                ELSE tl.name
            END AS tl_name,
            p.project_name,
            t.date,
            t.hours,
            t.actual_hours,
            t.remark,
            d.department          
        FROM 
            time_sheet_data AS t
        LEFT JOIN 
            master_projects AS p ON p.id = t.project_id
        LEFT JOIN 
            master_developer AS d ON d.id = t.developer_id
        LEFT JOIN 
            master_developer AS tl ON tl.id = p.team_lead_id
        ', where_condition, '
        ORDER BY 
            t.project_id, t.date DESC;
    ');

    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `get_project_developer_reports` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_project_developer_reports`(IN `p_year` INT, IN `p_month` VARCHAR(3), IN `r_type` VARCHAR(10), IN `p_project_id` INT, IN `p_developer_id` INT)
BEGIN
    DECLARE dynamic_query VARCHAR(4000);
    DECLARE first_day DATE;
    DECLARE last_day DATE;
    DECLARE table_name VARCHAR(50);
    DECLARE id_field VARCHAR(50);
    DECLARE name_field VARCHAR(50);
    DECLARE join_condition VARCHAR(100);
    DECLARE TPL_TL_join VARCHAR(200);
    DECLARE dynamic_query_for_tpl_tl VARCHAR(200);

	SET @row_number = 0;

    IF r_type = 'project' THEN
        SET table_name = 'master_projects';
        SET id_field = 'project_id';
        SET name_field = 'project_name';
        SET join_condition = CONCAT('md.id = tsd.project_id AND YEAR(tsd.date) = ', p_year);
    ELSEIF r_type = 'developer' THEN
        SET table_name = 'master_developer';
        SET id_field = 'developer_id';
        SET name_field = 'name';
        SET join_condition = CONCAT('md.id = tsd.developer_id AND YEAR(tsd.date) = ', p_year);
    END IF;

    SET TPL_TL_join = ' LEFT JOIN master_developer tpl ON md.tpl_id = tpl.id
                    LEFT JOIN master_developer tl ON md.team_lead_id = tl.id';
    SET dynamic_query_for_tpl_tl = ', tpl.name as "tpl_name", tl.name as "tl_name"';

    IF p_project_id IS NOT NULL THEN
        SET @dynamic_query = CONCAT('SELECT @row_number:=@row_number+1 AS SNo, md.name');
    ELSEIF p_developer_id IS NOT NULL THEN
        SET @dynamic_query = CONCAT('SELECT @row_number:=@row_number+1 AS SNo, md.project_name AS name ' , dynamic_query_for_tpl_tl);
    ELSE
        SET @dynamic_query = CONCAT('SELECT @row_number:=@row_number+1 AS SNo, md.', name_field ,' AS name ', IF(r_type = 'project', dynamic_query_for_tpl_tl , ''));
    END IF;

    IF p_month IS NOT NULL AND p_month <> '' THEN

        
        SET first_day = STR_TO_DATE(CONCAT(p_year, '-', p_month, '-01'), '%Y-%b-%d');
        SET last_day = LAST_DAY(first_day);        

        SET @dynamic_query = CONCAT(@dynamic_query, 
            ', IFNULL(ROUND(SUM(CASE WHEN YEAR(date) = ', p_year ,' AND MONTH(date) = ',MONTH(first_day),' THEN actual_hours END), 2), "-") AS Total');


        SET @date = first_day;
        WHILE @date <= last_day DO
            SET @dynamic_query = CONCAT(@dynamic_query, 
                ', IFNULL(ROUND(SUM(CASE WHEN DATE(date) = "', DATE_FORMAT(@date, '%Y-%m-%d'), '" THEN actual_hours END), 2), "-") AS "', DATE_FORMAT(@date, '%d-%m-%Y'), '"');
            SET @date = DATE_ADD(@date, INTERVAL 1 DAY);
        END WHILE;

        IF p_project_id IS NOT NULL THEN
            SET @dynamic_query = CONCAT(@dynamic_query, ' FROM master_developer md LEFT JOIN time_sheet_data tsd ON md.id = tsd.developer_id AND ( YEAR(date) = ', p_year, ' AND MONTH(date) = ', MONTH(first_day), ')');
        ELSEIF p_developer_id IS NOT NULL THEN
            SET @dynamic_query = CONCAT(@dynamic_query, ' FROM master_projects md LEFT JOIN time_sheet_data tsd ON md.id = tsd.project_id AND ( YEAR(date) = ', p_year, ' AND MONTH(date) = ', MONTH(first_day), ') ', TPL_TL_join);
        ELSE
            SET @dynamic_query = CONCAT(@dynamic_query, ' FROM ', table_name, ' AS md LEFT JOIN time_sheet_data tsd ON md.id = tsd.', id_field, ' AND ( YEAR(date) = ', p_year, ' OR MONTH(date) = ', MONTH(first_day), ')' , IF(r_type = 'project', TPL_TL_join , ''));
        END IF;
        
    ELSE       
        SET @dynamic_query = CONCAT(@dynamic_query, 
            ', IFNULL(ROUND(SUM(CASE WHEN YEAR(date) = ', p_year ,' THEN actual_hours END), 2), "-") AS Total');
         SET @dynamic_query = CONCAT(@dynamic_query, 
                ', IFNULL(ROUND(SUM(CASE WHEN MONTH(date) = 1 THEN actual_hours END), 2), "-") AS Jan',
                ', IFNULL(ROUND(SUM(CASE WHEN MONTH(date) = 2 THEN actual_hours END), 2), "-") AS Feb',
                ', IFNULL(ROUND(SUM(CASE WHEN MONTH(date) = 3 THEN actual_hours END), 2), "-") AS Mar',
                ', IFNULL(ROUND(SUM(CASE WHEN MONTH(date) = 4 THEN actual_hours END), 2), "-") AS Apr',
                ', IFNULL(ROUND(SUM(CASE WHEN MONTH(date) = 5 THEN actual_hours END), 2), "-") AS May',
                ', IFNULL(ROUND(SUM(CASE WHEN MONTH(date) = 6 THEN actual_hours END), 2), "-") AS Jun',
                ', IFNULL(ROUND(SUM(CASE WHEN MONTH(date) = 7 THEN actual_hours END), 2), "-") AS Jul',
                ', IFNULL(ROUND(SUM(CASE WHEN MONTH(date) = 8 THEN actual_hours END), 2), "-") AS Aug',
                ', IFNULL(ROUND(SUM(CASE WHEN MONTH(date) = 9 THEN actual_hours END), 2), "-") AS Sep',
                ', IFNULL(ROUND(SUM(CASE WHEN MONTH(date) = 10 THEN actual_hours END), 2), "-") AS Oct',
                ', IFNULL(ROUND(SUM(CASE WHEN MONTH(date) = 11 THEN actual_hours END), 2), "-") AS Nov',
                ', IFNULL(ROUND(SUM(CASE WHEN MONTH(date) = 12 THEN actual_hours END), 2), "-") AS "Dec"'
            );
        IF p_project_id IS NOT NULL THEN
            SET @dynamic_query = CONCAT(@dynamic_query, ' FROM master_developer md LEFT JOIN time_sheet_data tsd ON md.id = tsd.developer_id AND YEAR(date) = ', p_year);
        ELSEIF p_developer_id IS NOT NULL THEN
            SET @dynamic_query = CONCAT(@dynamic_query, ' FROM master_projects md LEFT JOIN time_sheet_data tsd ON md.id = tsd.project_id AND YEAR(date) = ', p_year , TPL_TL_join);
        ELSE 
            SET @dynamic_query = CONCAT(@dynamic_query, ' FROM ', table_name ,' md LEFT JOIN time_sheet_data tsd ON ', join_condition , IF(r_type = 'project', TPL_TL_join , ''));
        END IF;

    END IF;

    IF p_project_id IS NOT NULL THEN
        SET @dynamic_query = CONCAT(@dynamic_query, ' WHERE tsd.project_id = ', p_project_id);
    END IF;

    IF p_developer_id IS NOT NULL THEN
        SET @dynamic_query = CONCAT(@dynamic_query, ' WHERE tsd.developer_id = ', p_developer_id);
    END IF;

    IF p_project_id IS NOT NULL THEN
        SET @dynamic_query = CONCAT(@dynamic_query, ' GROUP BY md.name ');
    ELSEIF p_developer_id IS NOT NULL THEN
        SET @dynamic_query = CONCAT(@dynamic_query, ' GROUP BY md.project_name , tpl.name, tl.name');
    ELSE
        SET @dynamic_query = CONCAT(@dynamic_query, ' GROUP BY md.', name_field, IF(r_type = 'project', ', tpl.name, tl.name' , ''));
    END IF;

    
    PREPARE stmt FROM @dynamic_query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `insert_developer_with_reporting_managers` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_developer_with_reporting_managers`(
    IN `p_name` VARCHAR(255),
    IN `p_team_lead` TINYINT(1),
    IN `p_reporting_manager_ids` TEXT,
    IN `p_zoho_email` VARCHAR(255),
    IN `p_tl_hours` TEXT
)
BEGIN
    DECLARE v_developer_id INT;
    DECLARE v_reporting_manager_id INT;
    DECLARE v_allocated_hours DECIMAL(10,2);
    DECLARE v_total_hours DECIMAL(10,2) DEFAULT 0;
    DECLARE i INT DEFAULT 0;
    DECLARE v_json_length INT;
    DECLARE v_value TEXT;

    SET v_json_length = JSON_LENGTH(p_reporting_manager_ids);

    -- Calculate total hours
    WHILE i < v_json_length DO
        SET v_value = JSON_UNQUOTE(JSON_EXTRACT(p_reporting_manager_ids, CONCAT('$[', i, ']')));
        SET v_allocated_hours = CAST(JSON_UNQUOTE(JSON_EXTRACT(p_tl_hours, CONCAT('$."', v_value, '"'))) AS DECIMAL(10,2));
        SET v_total_hours = v_total_hours + IFNULL(v_allocated_hours, 0);
        SET i = i + 1;
    END WHILE;

    -- Reset loop counter
    SET i = 0;

    -- Get primary reporting manager (self if 0)
    SET v_value = JSON_UNQUOTE(JSON_EXTRACT(p_reporting_manager_ids, '$[0]'));
    SET v_reporting_manager_id = NULLIF(CAST(v_value AS UNSIGNED), 0);

    -- Insert developer
    INSERT INTO master_developer (name, team_lead, zoho_email_id, available_hours, reporting_manager_id)
    VALUES (p_name, p_team_lead, p_zoho_email, v_total_hours, v_reporting_manager_id);

    SET v_developer_id = LAST_INSERT_ID();

    -- If self-reporting, update the developer's own ID
    IF v_reporting_manager_id IS NULL THEN
        UPDATE master_developer SET reporting_manager_id = v_developer_id WHERE id = v_developer_id;
        INSERT INTO developer_reporting_manager (developer_id, reporting_manager_id, allocated_hours)
        VALUES (v_developer_id, v_developer_id, v_total_hours);
    ELSE
        WHILE i < v_json_length DO
            SET v_value = JSON_UNQUOTE(JSON_EXTRACT(p_reporting_manager_ids, CONCAT('$[', i, ']')));
            SET v_reporting_manager_id = CAST(v_value AS UNSIGNED);
            SET v_allocated_hours = CAST(JSON_UNQUOTE(JSON_EXTRACT(p_tl_hours, CONCAT('$."', v_value, '"'))) AS DECIMAL(10,2));
            INSERT INTO developer_reporting_manager (developer_id, reporting_manager_id, allocated_hours)
            VALUES (v_developer_id, v_reporting_manager_id, IFNULL(v_allocated_hours, 0));
            SET i = i + 1;
        END WHILE;
    END IF;

    -- Return the inserted ID
    SELECT v_developer_id AS insertId;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `saveOrUpdateTimesheets` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `saveOrUpdateTimesheets`(IN `timesheetData` JSON)
BEGIN
    DECLARE timesheetDate DATE;
    DECLARE developerId INT;
    DECLARE projectId INT;
    DECLARE teamLeadId INT;
    DECLARE hours FLOAT;
    DECLARE actualHours FLOAT;
    DECLARE remark VARCHAR(300);
    DECLARE cursor_index INT DEFAULT 0;
    DECLARE json_length INT;
    DECLARE teamLeadToDelete INT;

    -- Step 1: Loop through JSON to delete today's data for team_leads present in JSON
    SET json_length = JSON_LENGTH(timesheetData);

    WHILE cursor_index < json_length DO
        SET teamLeadToDelete = JSON_UNQUOTE(JSON_EXTRACT(timesheetData, CONCAT('$[', cursor_index, '].team_lead_id')));
        SET actualHours = CASE
            WHEN JSON_EXTRACT(timesheetData, CONCAT('$[', cursor_index, '].actual_hours')) IS NULL
                THEN NULL
            WHEN JSON_UNQUOTE(JSON_EXTRACT(timesheetData, CONCAT('$[', cursor_index, '].actual_hours'))) = '' 
                THEN NULL
            WHEN JSON_UNQUOTE(JSON_EXTRACT(timesheetData, CONCAT('$[', cursor_index, '].actual_hours'))) = 'null'
                THEN NULL
            WHEN JSON_UNQUOTE(JSON_EXTRACT(timesheetData, CONCAT('$[', cursor_index, '].actual_hours'))) REGEXP '^[0-9]+(.[0-9]+)?$'
                THEN CAST(JSON_UNQUOTE(JSON_EXTRACT(timesheetData, CONCAT('$[', cursor_index, '].actual_hours'))) AS DECIMAL(10, 2))
            ELSE NULL
        END;
        IF teamLeadToDelete IS NOT NULL AND teamLeadToDelete != '' AND actualHours IS NULL THEN
            DELETE FROM time_sheet_data
            WHERE DATE(`date`) = CURDATE()
              AND team_lead_id = teamLeadToDelete;
        END IF;

        SET cursor_index = cursor_index + 1;
    END WHILE;

    -- Reset cursor for main loop
    SET cursor_index = 0;

    -- Step 2: Loop again to insert or update
    WHILE cursor_index < json_length DO

        SET timesheetDate = JSON_UNQUOTE(JSON_EXTRACT(timesheetData, CONCAT('$[', cursor_index, '].date')));
        SET developerId = JSON_UNQUOTE(JSON_EXTRACT(timesheetData, CONCAT('$[', cursor_index, '].developer_id')));
        SET projectId = JSON_UNQUOTE(JSON_EXTRACT(timesheetData, CONCAT('$[', cursor_index, '].project_id')));
        SET teamLeadId = JSON_UNQUOTE(JSON_EXTRACT(timesheetData, CONCAT('$[', cursor_index, '].team_lead_id')));
        SET hours = JSON_UNQUOTE(JSON_EXTRACT(timesheetData, CONCAT('$[', cursor_index, '].hours')));

        SET actualHours = CASE
            WHEN JSON_EXTRACT(timesheetData, CONCAT('$[', cursor_index, '].actual_hours')) IS NULL
                THEN NULL
            WHEN JSON_UNQUOTE(JSON_EXTRACT(timesheetData, CONCAT('$[', cursor_index, '].actual_hours'))) = '' 
                THEN NULL
            WHEN JSON_UNQUOTE(JSON_EXTRACT(timesheetData, CONCAT('$[', cursor_index, '].actual_hours'))) = 'null'
                THEN NULL
            WHEN JSON_UNQUOTE(JSON_EXTRACT(timesheetData, CONCAT('$[', cursor_index, '].actual_hours'))) REGEXP '^[0-9]+(.[0-9]+)?$'
                THEN CAST(JSON_UNQUOTE(JSON_EXTRACT(timesheetData, CONCAT('$[', cursor_index, '].actual_hours'))) AS DECIMAL(10, 2))
            ELSE NULL
        END;

        SET remark = JSON_UNQUOTE(JSON_EXTRACT(timesheetData, CONCAT('$[', cursor_index, '].remark')));

        IF EXISTS (
            SELECT 1 FROM time_sheet_data
            WHERE DATE(`date`) = timesheetDate  
              AND developer_id = developerId
              AND project_id = projectId
        ) THEN
            UPDATE time_sheet_data
            SET 
                team_lead_id = teamLeadId,
                hours = hours,
                actual_hours = actualHours,
                remark = remark,
                `date` = CONCAT(timesheetDate, ' ', TIME(`date`))
            WHERE 
                DATE(`date`) = timesheetDate  
              AND developer_id = developerId
              AND project_id = projectId;
        ELSE
            IF NOT (DATE(timesheetDate) = CURDATE() AND hours = 0) THEN
                INSERT INTO time_sheet_data (
                    `date`, developer_id, project_id, team_lead_id, hours, actual_hours, remark
                ) VALUES (
                    CONCAT(timesheetDate, ' 00:00:00'),
                    developerId, projectId, teamLeadId, hours, actualHours, remark
                );
            END IF;
        END IF;

        SET cursor_index = cursor_index + 1;
    END WHILE;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `update_developer_and_managers` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `update_developer_and_managers`(IN `dev_id` INT, IN `dev_data` JSON)
BEGIN
    
    DECLARE done INT DEFAULT FALSE;
    DECLARE manager_id INT;

    
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        
        ROLLBACK;
    END;

    
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    START TRANSACTION;

    
    UPDATE master_developer
    SET 
        name = JSON_UNQUOTE(JSON_EXTRACT(dev_data, '$.name')),
        status = JSON_UNQUOTE(JSON_EXTRACT(dev_data, '$.status')),
        available_hours = JSON_UNQUOTE(JSON_EXTRACT(dev_data, '$.available_hours'))
    WHERE id = dev_id;

    
    DELETE FROM developer_reporting_manager WHERE developer_id = dev_id;

    
    OPEN manager_cursor;
    read_loop: LOOP
        FETCH manager_cursor INTO manager_id;
        IF done THEN
            LEAVE read_loop;
        END IF;
        INSERT INTO developer_reporting_manager (developer_id, reporting_manager_id) 
        VALUES (dev_id, manager_id);
    END LOOP;
    CLOSE manager_cursor;

    
    COMMIT;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `update_developer_attendance` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `update_developer_attendance`(IN `attendance_data` JSON)
BEGIN
    DECLARE dev_id INT;
    DECLARE tl_id INT;
    DECLARE att_date DATE;
    DECLARE att_value FLOAT; 
    DECLARE i INT DEFAULT 0;
    DECLARE total_records INT;

    
    SET total_records = JSON_LENGTH(attendance_data);

    
    WHILE i < total_records DO
        
        SET dev_id = JSON_UNQUOTE(JSON_EXTRACT(attendance_data, CONCAT('$[', i, '].developer_id')));
        SET tl_id = JSON_UNQUOTE(JSON_EXTRACT(attendance_data, CONCAT('$[', i, '].tl_id')));
        SET att_date = JSON_UNQUOTE(JSON_EXTRACT(attendance_data, CONCAT('$[', i, '].attendance_date')));
        SET att_value = JSON_UNQUOTE(JSON_EXTRACT(attendance_data, CONCAT('$[', i, '].attendance')));

        
        IF EXISTS (
            SELECT 1
            FROM developer_attendance
            WHERE developer_id = dev_id AND attendance_date = att_date
        ) THEN
            
            UPDATE developer_attendance
            SET attendance = att_value,
                updated_at = CURRENT_TIMESTAMP
            WHERE developer_id = dev_id AND attendance_date = att_date;
        ELSE
            
            INSERT INTO developer_attendance (developer_id, attendance_date, attendance, created_at, updated_at)
            VALUES (dev_id, att_date, att_value, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
        END IF;

        IF att_value IN (0, 2) THEN
            DELETE FROM time_sheet_data
            WHERE developer_id = dev_id
              AND team_lead_id = tl_id
              AND date = att_date;
        END IF;

        SET i = i + 1;
    END WHILE;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `update_developer_with_reporting_managers` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `update_developer_with_reporting_managers`(
    IN `p_id` INT,
    IN `p_name` VARCHAR(255),
    IN `p_team_lead` BOOLEAN,
    IN `p_reporting_manager_ids` TEXT,
    IN `p_zoho_email_id` VARCHAR(255),
    IN `p_tl_hours` TEXT,
    IN `p_status` VARCHAR(50)
)
BEGIN
    DECLARE v_reporting_manager_id INT;
    DECLARE v_allocated_hours DECIMAL(10,2);
    DECLARE v_total_hours DECIMAL(10,2) DEFAULT 0;
    DECLARE i INT DEFAULT 0;
    DECLARE v_json_length INT;
    DECLARE v_value TEXT;

    SET v_json_length = JSON_LENGTH(p_reporting_manager_ids);

    -- Calculate total hours
    WHILE i < v_json_length DO
        SET v_value = JSON_UNQUOTE(JSON_EXTRACT(p_reporting_manager_ids, CONCAT('$[', i, ']')));
        SET v_allocated_hours = CAST(JSON_UNQUOTE(JSON_EXTRACT(p_tl_hours, CONCAT('$."', v_value, '"'))) AS DECIMAL(10,2));
        SET v_total_hours = v_total_hours + IFNULL(v_allocated_hours, 0);
        SET i = i + 1;
    END WHILE;

    -- Reset loop counter
    SET i = 0;

    -- Get primary reporting manager (self if 0)
    SET v_value = JSON_UNQUOTE(JSON_EXTRACT(p_reporting_manager_ids, '$[0]'));
    SET v_reporting_manager_id = NULLIF(CAST(v_value AS UNSIGNED), 0);

    -- Update master_developer
    UPDATE master_developer 
    SET 
        name = p_name,
        team_lead = p_team_lead,
        zoho_email_id = p_zoho_email_id,
        available_hours = v_total_hours,
        reporting_manager_id = IFNULL(v_reporting_manager_id, p_id),
        status = p_status
    WHERE id = p_id;

    -- Delete existing reporting managers
    DELETE FROM developer_reporting_manager WHERE developer_id = p_id;

    -- If self-reporting
    IF v_reporting_manager_id IS NULL THEN
        -- Self reporting
        INSERT INTO developer_reporting_manager (developer_id, reporting_manager_id, allocated_hours)
        VALUES (p_id, p_id, v_total_hours);
    ELSE
        WHILE i < v_json_length DO
            SET v_value = JSON_UNQUOTE(JSON_EXTRACT(p_reporting_manager_ids, CONCAT('$[', i, ']')));
            SET v_reporting_manager_id = CAST(v_value AS UNSIGNED);
            SET v_allocated_hours = CAST(JSON_UNQUOTE(JSON_EXTRACT(p_tl_hours, CONCAT('$."', v_value, '"'))) AS DECIMAL(10,2));
            INSERT INTO developer_reporting_manager (developer_id, reporting_manager_id, allocated_hours)
            VALUES (p_id, v_reporting_manager_id, IFNULL(v_allocated_hours, 0));
            SET i = i + 1;
        END WHILE;
    END IF;

    -- Return the updated ID
    SELECT p_id AS updated_developer_id;
END ;;
DELIMITER ;


