-- DELIMITER //

CREATE PROCEDURE {ProcName}(
    IN DateFrom VARCHAR(100),
    IN DateTo VARCHAR(100)
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''

BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE rec ROW(
        v_settlement_date DATETIME,
        v_operator_id BIGINT(20),
        v_player_id BIGINT(20),
        v_currency_id BIGINT(20),
        v_type VARCHAR(255),
        v_platform VARCHAR(255),
        v_rake DECIMAL(20,15),
        v_status VARCHAR(255),
        v_currency VARCHAR(255),
        v_total_bet DECIMAL(65,30),
        v_total_win DECIMAL(65,30),
        v_total_transaction BIGINT(20) ,
        v_uuid VARCHAR(255),
        v_check_sum VARCHAR(255),
        v_current_datetime DATETIME
    );

    DECLARE c CURSOR FOR
        SELECT
            DATE_FORMAT(
                btr.`updated_at`,
                '%Y-%m-%d %H:00:00'
            ) AS `settlement_date`,
            '{OperatorID}' AS `operator_id`,
            btr.`player_id`,
            btr.`currency_id`,
            btr.`type`,
            btr.`platform`,
            btr.`rake`,
            btr.`status`,
            c.`code` AS `currency`,
            SUM(ABS(bt.`amount`)) AS `total_bet`,
            SUM(ABS(btr.`amount`)) AS `total_win`,
            COUNT(1) as `total_transaction`,
            UUID() as uuid,
            MD5(
                CONCAT(
                    COALESCE(DATE_FORMAT(btr.`updated_at`, '%Y-%m-%d %H:00:00'),''),
                    COALESCE('{OperatorID}',''),
                    COALESCE(btr.`player_id`,''),
                    COALESCE(btr.`currency_id`,''),
                    COALESCE(btr.`type`,''),
                    COALESCE(btr.`rake`,''),
                    COALESCE(btr.`status`,'')
                )
            ) as check_sum,
            CONVERT_TZ(NOW(), '+00:00', '+00:00') AS current_datetime -- Timezone GMT+08:00 is set

        FROM `{DBTransaction}`.`{TableTransaction}` AS btr
            LEFT JOIN `{DBTransaction}`.`{TableTransaction}` AS bt ON bt.`id` = btr.`transaction_id`
            LEFT JOIN `{DBUser}`.`currencies` AS c ON c.`id` = btr.`currency_id`
            LEFT JOIN `{DBUser}`.`players` AS p ON p.`id` = btr.`player_id`

        WHERE
            btr.`type` IN (
                'settled',
                'resettled',
                'cancelled'
            )
            AND btr.`transaction_id` IS NOT NULL
            AND btr.`updated_at` BETWEEN DateFrom AND DateTo

        GROUP BY
            btr.`currency_id`,
            btr.`player_id`,
            btr.`type`,
            btr.`platform`,
            btr.`rake`,
            btr.`status`,
            DATE_FORMAT(
                btr.`updated_at`,
                '%Y-%m-%d %H:00:00'
            );


    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN c;
    read_loop:
        LOOP
            FETCH c INTO rec;
                -- Break the loop if done
                IF done THEN
                    LEAVE read_loop;
                END IF;

                INSERT INTO report_bets(
                    settlement_date,
                    operator_id,
                    player_id,
                    currency_id,
                    type,
                    platform,
                    rake,
                    status,
                    currency,
                    total_bet,
                    total_win,
                    total_transaction,
                    uuid,
                    check_sum,
                    created_at
                )
                VALUES (
                    rec.v_settlement_date,
                    rec.v_operator_id,
                    rec.v_player_id,
                    rec.v_currency_id,
                    rec.v_type,
                    rec.v_platform,
                    rec.v_rake,
                    rec.v_status,
                    rec.v_currency,
                    rec.v_total_bet,
                    rec.v_total_win,
                    rec.v_total_transaction,
                    rec.v_uuid,
                    rec.v_check_sum,
                    rec.v_current_datetime
                ) ON DUPLICATE KEY UPDATE
                    total_bet         = rec.v_total_bet,
                    total_win         = rec.v_total_win,
                    total_transaction = rec.v_total_transaction,
                    updated_at        = rec.v_current_datetime;
        END LOOP;
    CLOSE c;
END;
-- //

-- DELIMITER ;