-- DELIMITER //

CREATE PROCEDURE $procName(IN v_start VARCHAR(100), IN v_end VARCHAR(100))
    LANGUAGE SQL
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''

    BEGIN
        DECLARE done INT DEFAULT FALSE;
        DECLARE rec ROW(
            v_transaction_date DATE,
            v_user_profile_id BIGINT(20),
            v_agent_id BIGINT(20),
            v_master_agent_id BIGINT(20),
            v_operator_id BIGINT(20),
            v_balance_category_id BIGINT(20),
            v_currency_id BIGINT(20),
            v_amount DECIMAL(20,6),
            v_fee_amount DECIMAL(20,6),
            v_fix_fee_amount DECIMAL(20,6),
            v_convenience_fee_amount DECIMAL(20,6),
            v_platform_fee_amount DECIMAL(20,6),
            v_payment_parent_method VARCHAR(255),
            v_payment_method_name VARCHAR(255),
            v_balance_category VARCHAR(255),
            v_currency VARCHAR(255),
            v_key_token CHAR(36),
            v_check_sum VARCHAR(100),
            v_current_datetime VARCHAR(100)
        );

        DECLARE c CURSOR FOR
            SELECT
                DATE(transaction_date) as transaction_date,
                user_profile_id,
                agent_id,
                master_agent_id,
                operator_id,
                balance_category_id,
                currency_id,
                amount,
                fee_amount,
                fix_fee_amount,
                convenience_fee_amount,
                platform_fee_amount,
                payment_parent_method,
                payment_method_name,
                balance_category,
                currency,
                UUID() as key_token,
                MD5(
                    CONCAT(
                        COALESCE(transaction_date,''),
                        COALESCE(user_profile_id,''),
                        COALESCE(agent_id,''),
                        COALESCE(master_agent_id,''),
                        COALESCE(operator_id,''),
                        COALESCE(balance_category_id,''),
                        COALESCE(currency_id,'')
                    )
                ) as check_sum,
                CONVERT_TZ(NOW(), '+00:00', '+00:00') AS current_datetime -- Timezone GMT+08:00 is set
            FROM (
                SELECT
                    MIN(transaction_date) as transaction_date,
                    user_profile_id,
                    agent_id,
                    master_agent_id,
                    operator_id,
                    balance_category_id,
                    currency_id,
                    amount,
                    fee_amount,
                    fix_fee_amount,
                    convenience_fee_amount,
                    platform_fee_amount,
                    payment_parent_method,
                    payment_method_name,
                    balance_category,
                    currency
                FROM $viewName
                WHERE type_name = 'Deposit'
                AND status_name = 'Completed'
                GROUP BY user_profile_id
            ) AS ftd
            WHERE transaction_date BETWEEN v_start and v_end;

        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 $reportName(
                        transaction_date,
                        user_profile_id,
                        agent_id,
                        master_agent_id,
                        operator_id,
                        balance_category_id,
                        currency_id,
                        amount,
                        fee_amount,
                        fix_fee_amount,
                        convenience_fee_amount,
                        platform_fee_amount,
                        payment_parent_method,
                        payment_method_name,
                        balance_category,
                        currency,
                        key_token,
                        check_sum,
                        created_at
                    )
                    VALUES (
                        rec.v_transaction_date,
                        rec.v_user_profile_id,
                        rec.v_agent_id,
                        rec.v_master_agent_id,
                        rec.v_operator_id,
                        rec.v_balance_category_id,
                        rec.v_currency_id,
                        rec.v_amount,
                        rec.v_fee_amount,
                        rec.v_fix_fee_amount,
                        rec.v_convenience_fee_amount,
                        rec.v_platform_fee_amount,
                        rec.v_payment_parent_method,
                        rec.v_payment_method_name,
                        rec.v_balance_category,
                        rec.v_currency,
                        rec.v_key_token,
                        rec.v_check_sum,
                        rec.v_current_datetime
                    ) ON DUPLICATE KEY UPDATE
                        amount                 = rec.v_amount,
                        fee_amount             = rec.v_fee_amount,
                        fix_fee_amount         = rec.v_fix_fee_amount,
                        convenience_fee_amount = rec.v_convenience_fee_amount,
                        platform_fee_amount    = rec.v_platform_fee_amount,
                        payment_parent_method  = rec.v_payment_parent_method,
                        payment_method_name    = rec.v_payment_method_name,
                        balance_category       = rec.v_balance_category,
                        currency               = rec.v_currency,
                        updated_at             = rec.v_current_datetime;

            END LOOP;
        CLOSE c;
    END;
-- //
