DELIMITER //

CREATE OR REPLACE PROCEDURE compareMeterReads (IN starting_date datetime)

BEGIN

      	/*Declare the variables*/
      	DECLARE first_date_val DATETIME;
      	DECLARE ser_num_val VARCHAR(25);		
        DECLARE last_date_val DATETIME;
		
		DECLARE first_black_meter INT;
		DECLARE first_color_meter INT;
		DECLARE last_black_meter INT;
		DECLARE last_color_meter INT;        
        
        DECLARE black_diff INT;
        DECLARE color_diff INT;
        
        
        /*When this = 1, the fetch will be done*/
        DECLARE exit_results_fetch INT DEFAULT 0;
        
      
      	/*Declare the cursor that will get the earliest created_date and serial_number
        for each serial_number.*/
		DECLARE results_cursor CURSOR FOR
       
      	SELECT MIN(created_date) first_date, serial_number
        FROM `y2019_meters`
        WHERE created_date > starting_date AND serial_number != ''
        GROUP BY serial_number;
        
        /*Declare the cursor that will get the earliest created_date, that follows
        the first_date from the results_cursor, for each serial_number.*/
        DECLARE last_results_cursor CURSOR FOR
            SELECT MIN(created_date) last_date
            FROM `y2019_meters`
            WHERE created_date > first_date_val AND serial_number = ser_num_val
            GROUP BY serial_number;
       
    
		/*Declare NOT FOUND handler*/
		DECLARE CONTINUE HANDLER 
        FOR NOT FOUND SET exit_results_fetch = 1;      
               
        
        /*Open the first cursor*/
        OPEN results_cursor;        
        
            getData: LOOP

                /*Get the results and assign the values to first_date_val and ser_num_val*/
                FETCH results_cursor INTO first_date_val, ser_num_val;            


                /*Open the cursor that will return a last_date value*/
                OPEN last_results_cursor;


                    /*Get the results and assign the value to last_date_val*/
                    FETCH last_results_cursor INTO last_date_val;

                /*Close the cursor*/
                CLOSE last_results_cursor; 
            
                BEGIN

                    DECLARE black_meter_val INT;
                    DECLARE color_meter_val INT;
                    DECLARE meter_read_order VARCHAR(10);					
					

                    /*When this = 1, the fetch will be done*/ 
                    DECLARE exit_volumes_fetch INT DEFAULT 0;


                    DECLARE volumes_cursor CURSOR FOR

                    SELECT black_meter, color_meter, 'first_read' read_order
                    FROM `y2019_meters`
                    WHERE created_date = first_date_val
                    AND serial_number = ser_num_val

                    UNION

                    SELECT black_meter, color_meter, 'last_read' read_order
                    FROM `y2019_meters`
                    WHERE created_date = last_date_val
                    AND serial_number = ser_num_val;


                    /*Declare NOT FOUND handler*/
                    DECLARE CONTINUE HANDLER 
                    FOR NOT FOUND SET exit_volumes_fetch = 1;

            
            
                   /*Open the cursor that will return the black and color meter values for first and last meter reads
                    for the current serial_number*/
                    OPEN volumes_cursor;

                        getVolumes: LOOP

                            /*Get the results and assign the values to black_meter_val, color_meter_val, and meter_read_order*/        	           
                            FETCH volumes_cursor INTO black_meter_val, color_meter_val, meter_read_order;

                            IF (meter_read_order = "first_read") THEN
                                SET first_black_meter = black_meter_val;
                                SET first_color_meter = color_meter_val;
                                

                            ELSEIF (meter_read_order = "last_read") THEN
                                SET last_black_meter = black_meter_val;
                                SET last_color_meter = color_meter_val;
                                SET color_diff = (last_color_meter - first_color_meter);
								
							ELSE
								SET exit_results_fetch = 1;
                            END IF;
                            
                            

                            IF exit_volumes_fetch = 1 THEN
                                LEAVE getVolumes;
                            END IF; 
                            
                            

                        END LOOP getVolumes; 


                    /*Close the cursor*/
                    CLOSE volumes_cursor;
					
					
               END;  
            
            SET black_diff = (last_black_meter - first_black_meter);                           
                            
			SET color_diff = (last_color_meter - first_color_meter);
            
                      
            
            /*Insert all bad values, along with the serial_number and corresponding date_timestamps, into the table*/
          	SET @aSql = 'INSERT INTO meter_read_comparisons (serial_number, first_date, last_date, bad_value_black, bad_value_color)  VALUES( ?, ?, ?, ?, ?)';
            
            PREPARE stmt FROM @aSql;

			if(last_date_val > first_date_val) THEN
            
            EXECUTE stmt USING ser_num_val, first_date_val, last_date_val, black_diff, color_diff;
			END IF;
			
			
            DEALLOCATE PREPARE stmt;
            
          
          	IF exit_results_fetch = 1 THEN
           		LEAVE getData;
           	END IF; 
          
		END LOOP getData; 
            
        
        /*Close the cursor*/
        CLOSE results_cursor;	   
		
         
       END //

DELIMITER ;