CREATE DEFINER = 'clientem_gmc'@'%'
PROCEDURE clientem_clientemelhorcompra.CalcPointsClients(IN IdPrograma INT, IN IdCliente INT)
begin
   -- Variaveis dos parâmetros do programa.
    DECLARE ParamValidity INT;
    DECLARE ParamRatio INT;
    DECLARE ParamPercent DOUBLE(8,2);
    DECLARE FirstIncrement INT;

    DECLARE SecondIncrement INT;
    DECLARE QtdDischarges INT;
    DECLARE DateDischarge DATETIME;
    DECLARE DateDischargeSub DATETIME;
    DECLARE QtdReleasesOnPeriod INT;
    DECLARE QtdReleasesNotUsedOnPeriod INT;
    DECLARE ReleaseLastedOnPeriod INT;
    DECLARE CodDischarge INT;
    DECLARE AmountDischarge DECIMAL(10,2);
    DECLARE ReleaseOnPeriod DECIMAL(10,2);
    DECLARE CodRelease INT;
    DECLARE DateRelease DATETIME;
    DECLARE TmpCodRelease INT;
    DECLARE TmpValueRelease DECIMAL(10,2);
    DECLARE TmpDateRelease DATETIME;
    DECLARE ThirdIncrement INT;
    DECLARE TmpQtdReleases INT;
	  DECLARE RestRelease DECIMAL(10,2);
    DECLARE RestDischarge DECIMAL(10,2);
    DECLARE VerifyTTPoints INT;
    DECLARE VerifyTTReleases INT;
    DECLARE QtdCMCPoints INT;
    DECLARE CMCPointsRelease DECIMAL(10,2);
    DECLARE DataAtual DATETIME;
    DECLARE DataPeriodoValidade DATETIME;

    -- Set Variaveis dos parâmetros do sistema.
    SET ParamValidity = (
        SELECT P.validity
        FROM tb_program
          JOIN tb_parameter AS P ON P.id = tb_program.parameter_id
        WHERE tb_program.id = IdPrograma
    );
    SET ParamRatio = (
        SELECT P.ratio
        FROM tb_program
          JOIN tb_parameter AS P ON P.id = tb_program.parameter_id
        WHERE tb_program.id = IdPrograma
    );

    DROP TABLE IF EXISTS rsp_points;
    CREATE TEMPORARY TABLE rsp_points (
          cd_release INT,
          cd_discharge INT,
          dt_release DATETIME,
          dt_discharge DATETIME,
          amount_release DECIMAL(10,2),
          amount_discharge DECIMAL(10,2),
          rest_release DECIMAL(10,2),
          rest_discharge DECIMAL(10,2)
    );

    SET QtdDischarges = (
        SELECT COUNT(created_at)
        FROM tb_discharge
        WHERE tb_discharge.program_id = IdPrograma
        AND tb_discharge.client_id = IdCliente
        AND tb_discharge.deleted_at IS NULL
    );


    DROP TABLE IF EXISTS tt_releases;
    CREATE TEMPORARY TABLE tt_releases (
          id int AUTO_INCREMENT PRIMARY KEY,
          id_release INT,
          type_release varchar(20),
          dt_release DATETIME,
          value_release DECIMAL(10,2),
          used_by int null
    );

    DROP TABLE IF EXISTS tt_releases_check;
    CREATE TEMPORARY TABLE tt_releases_check (
          id int,
          id_release INT,
          type_release varchar(20),
          dt_release DATETIME,
          value_release DECIMAL(10,2),
          used_by int null
    );


    -- Define o valor do Increment como Zero para iniciar o While.
    SET FirstIncrement = 0;

    WHILE FirstIncrement < QtdDischarges DO
        SET CodDischarge = (
            SELECT tb_discharge.id
            FROM tb_discharge
            WHERE tb_discharge.program_id = IdPrograma
            AND tb_discharge.client_id = IdCliente
            AND tb_discharge.deleted_at IS NULL
            ORDER BY tb_discharge.created_at ASC
            LIMIT 1
      		OFFSET FirstIncrement
		);

		SET DateDischarge = (
          SELECT tb_discharge.created_at
          FROM tb_discharge
          WHERE tb_discharge.id = CodDischarge
        );

        SET AmountDischarge = (
            SELECT ROUND(((tb_discharge.value / percent) * ParamRatio) * tb_discharge.quantity)
    		    FROM tb_discharge
            WHERE tb_discharge.deleted_at IS NULL
            AND tb_discharge.id = CodDischarge
		);

        SET DateDischargeSub = DATE_SUB(DateDischarge, INTERVAL ParamValidity DAY);

        DROP TABLE IF EXISTS tt_releases_check;
        CREATE TEMPORARY TABLE tt_releases_check (
              id int,
              id_release INT,
              type_release varchar(20),
              dt_release DATETIME,
              value_release DECIMAL(10,2),
              used_by int null
        );


        INSERT INTO tt_releases_check (id, id_release, type_release, dt_release, value_release, used_by)
        SELECT * FROM tt_releases tr;

        INSERT INTO tt_releases(id, id_release, type_release, dt_release, value_release, used_by)
        SELECT NULL, id, "MANUAL", created_at, ROUND(value * ParamRatio) as ttlrelease, null
        FROM tb_release
        WHERE tb_release.program_id = IdPrograma
        AND tb_release.client_id = IdCliente
        AND tb_release.deleted_at IS NULL
        AND tb_release.created_at BETWEEN DateDischargeSub AND DateDischarge
        AND tb_release.id NOT IN (SELECT id_release FROM tt_releases_check WHERE type_release = "MANUAL");


        TRUNCATE TABLE tt_releases_check;

        INSERT INTO tt_releases_check (id, id_release, type_release, dt_release, value_release, used_by)
        SELECT * FROM tt_releases tr;

        INSERT INTO tt_releases(id, id_release, type_release, dt_release, value_release, used_by)
        SELECT NULL, tri.id, "INTEGRA", tri.date_release, ROUND(tri.value * ParamRatio) as ttlrelease, null
        FROM tb_release_integration tri
          JOIN tb_user tu ON tu.id = IdCliente
        WHERE tri.program_id = IdPrograma
        AND tri.client_id = IdCliente
        AND tri.deleted_at IS NULL
        AND tri.canceled = 'N'
        AND tri.value > 0
        AND tri.date_release >= tu.created_at
        AND tri.date_release BETWEEN DateDischargeSub AND DateDischarge
        AND tri.id NOT IN (SELECT id_release FROM tt_releases_check WHERE type_release = "INTEGRA");


        SET QtdReleasesNotUsedOnPeriod = (
            SELECT IFNULL(COUNT(dt_release), 0)
            FROM tt_releases
            WHERE used_by IS NULL
		);

        SET ReleaseLastedOnPeriod = (
            SELECT IFNULL(COUNT(dt_release), 0)
            FROM rsp_points
            WHERE rest_release > 0
			AND dt_release BETWEEN DateDischargeSub AND DateDischarge
		);

        SET QtdReleasesOnPeriod = QtdReleasesNotUsedOnPeriod + ReleaseLastedOnPeriod;

        SET SecondIncrement = 0;

        WHILE SecondIncrement < QtdReleasesOnPeriod DO
			IF (FirstIncrement > 0) THEN
				SET VerifyTTReleases = (
					SELECT cd_release
					FROM rsp_points
					WHERE rest_release > 0
				);

				IF (VerifyTTReleases IS NOT NULL) THEN
					SET CodRelease = VerifyTTReleases;
				ELSE
					SET CodRelease = (
						SELECT tt_releases.id
						FROM tt_releases
						WHERE tt_releases.used_by IS NULL
						AND tt_releases.dt_release BETWEEN DateDischargeSub AND DateDischarge
						ORDER BY tt_releases.dt_release LIMIT 1
					);
				END IF;
            ELSE
				SET CodRelease = (
					SELECT tt_releases.id
					FROM tt_releases
					WHERE tt_releases.used_by IS NULL
					ORDER BY tt_releases.dt_release ASC LIMIT 1
				);
            END IF;

            IF (VerifyTTReleases IS NOT NULL) THEN
				SET ReleaseOnPeriod = (
					SELECT rest_release
					FROM rsp_points
					WHERE rsp_points.cd_release = CodRelease
				);

				SET DateRelease = (
					SELECT dt_release
					FROM rsp_points
					WHERE rsp_points.cd_release = CodRelease
				);
            ELSE
				SET ReleaseOnPeriod = (
					SELECT tt_releases.value_release
					FROM tt_releases
					WHERE tt_releases.id = CodRelease
                );

				SET DateRelease = (
					SELECT tt_releases.dt_release
					FROM tt_releases
					WHERE tt_releases.id = CodRelease
				);
            END IF;

			IF (RestDischarge IS NOT NULL) THEN
  				IF (RestDischarge > ReleaseOnPeriod) THEN
  					SET RestRelease = 0;
  					SET RestDischarge = RestDischarge - ReleaseOnPeriod;
  				ELSE
  					SET RestRelease = ReleaseOnPeriod - RestDischarge;
  					SET RestDischarge = 0;
				END IF;
			ELSE
  				IF (AmountDischarge > ReleaseOnPeriod) THEN
  					SET RestRelease = 0;
  					SET RestDischarge = AmountDischarge - ReleaseOnPeriod;
  				ELSE
  					SET RestDischarge = 0;
  					SET RestRelease = ReleaseOnPeriod - AmountDischarge;
  				END IF;
			END IF;

			IF (VerifyTTReleases IS NOT NULL) THEN
				UPDATE rsp_points
				SET rest_release = RestRelease
				WHERE cd_release = CodRelease;
			ELSE
				INSERT INTO rsp_points
				VALUES (CodRelease, CodDischarge, DateRelease, DateDischarge, ReleaseOnPeriod, AmountDischarge, RestRelease, RestDischarge);

				UPDATE tt_releases
				SET used_by = CodDischarge
				WHERE tt_releases.id = CodRelease;
			END IF;

			IF (RestRelease > 0) THEN
				SET SecondIncrement = QtdReleasesOnPeriod;
  			ELSE
				SET SecondIncrement = SecondIncrement + 1;
			END IF;
        END WHILE;

		SET FirstIncrement = FirstIncrement + 1;
		SET RestDischarge = NULL;

    END WHILE;

    -- Incio Calculo Final.

    SET DataAtual = DATE_ADD(CURRENT_TIMESTAMP(), INTERVAL 3 HOUR);
    SET DataPeriodoValidade = DATE_SUB(DataAtual, INTERVAL ParamValidity Day);


        TRUNCATE TABLE tt_releases_check;
        INSERT INTO tt_releases_check (id, id_release, type_release, dt_release, value_release, used_by)
        SELECT * FROM tt_releases tr;

        INSERT INTO tt_releases(id, id_release, type_release, dt_release, value_release, used_by)
        SELECT NULL, id, "MANUAL", created_at, ROUND(value * ParamRatio) as ttlrelease, null
        FROM tb_release
        WHERE tb_release.program_id = IdPrograma
        AND tb_release.client_id = IdCliente
        AND tb_release.deleted_at IS NULL
        AND tb_release.created_at BETWEEN DataPeriodoValidade AND DataAtual
        AND tb_release.id NOT IN (
            SELECT id_release
            FROM tt_releases_check
            WHERE type_release = "MANUAL"
            );


        TRUNCATE TABLE tt_releases_check;

        INSERT INTO tt_releases_check (id, id_release, type_release, dt_release, value_release, used_by)
        SELECT * FROM tt_releases tr;

        INSERT INTO tt_releases(id, id_release, type_release, dt_release, value_release, used_by)
        SELECT NULL, tri.id, "INTEGRA", tri.date_release, ROUND(tri.value * ParamRatio) as ttlrelease, null
        FROM tb_release_integration tri
          JOIN tb_user tu ON tu.id = IdCliente
        WHERE tri.program_id = IdPrograma
        AND tri.client_id = IdCliente
        AND tri.created_at >= tu.created_at
        AND tri.deleted_at IS NULL
        AND tri.canceled = 'N'
        AND tri.value > 0
        AND tri.date_release BETWEEN DataPeriodoValidade AND DataAtual
        AND tri.id NOT IN (
            SELECT id_release
            FROM tt_releases_check
            WHERE type_release = "INTEGRA"
            );

        DROP TABLE IF EXISTS tt_points_check;
        CREATE TEMPORARY TABLE tt_points_check (
            cd_release INT,
            cd_discharge INT,
            dt_release DATETIME,
            dt_discharge DATETIME,
            amount_release DECIMAL(10,2),
            amount_discharge DECIMAL(10,2),
            rest_release DECIMAL(10,2),
            rest_discharge DECIMAL(10,2)
        );

        INSERT INTO tt_points_check (cd_release, cd_discharge, dt_release, dt_discharge, amount_release, amount_discharge, rest_release, rest_discharge)
        SELECT * FROM rsp_points rp;

        INSERT INTO rsp_points (cd_release, cd_discharge, dt_release, dt_discharge, amount_release, amount_discharge, rest_release, rest_discharge)
        SELECT id, 0, dt_release, CURRENT_TIMESTAMP(), value_release, 0, value_release, 0
        FROM tt_releases tr
        WHERE tr.used_by IS NULL
        AND tr.dt_release BETWEEN DataPeriodoValidade AND DataAtual
        AND tr.id NOT IN (
            SELECT cd_release FROM tt_points_check tpc
            );

      SELECT ROUND(SUM(rp.rest_release)) AS totalPontos FROM rsp_points rp WHERE rp.dt_release BETWEEN DataPeriodoValidade AND DataAtual;
end