-- 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 DATETIME,
            v_user_profile_id BIGINT(20),
            v_transaction_request_type_id BIGINT(20),
            v_balance_category_id BIGINT(20),
            v_currency_id BIGINT(20),
            v_agent_id BIGINT(20),
            v_master_agent_id BIGINT(20),
            v_operator_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_transaction_from VARCHAR(255),
            v_type_name VARCHAR(255),
            v_status_name VARCHAR(255),
            v_balance_category VARCHAR(255),
            v_currency VARCHAR(255),
            v_total_transaction_count BIGINT(20),
            v_total_diff_seconds BIGINT(20),
            v_key_token CHAR(36),
            v_check_sum VARCHAR(100),
            v_current_datetime VARCHAR(100)
        );

        DECLARE c CURSOR FOR
            SELECT
                *,
                UUID() as key_token,
                MD5(
                CONCAT(
                    COALESCE(transaction_date,''),
                    COALESCE(user_profile_id,''),
                    COALESCE(agent_id,''),
                    COALESCE(master_agent_id,''),
                    COALESCE(operator_id,''),
                    COALESCE(transaction_request_type_id,''),
                    COALESCE(balance_category_id,''),
                    COALESCE(currency_id,''),
                    COALESCE(payment_method_name,''),
                    COALESCE(transaction_from,'')
                )
                ) as check_sum,
                CONVERT_TZ(NOW(), '+00:00', '+00:00') AS current_datetime -- Timezone GMT+08:00 is set
            FROM (
                SELECT
                    DATE_FORMAT(
                        t.completed_at,
                        '%Y-%m-%d %H:00:00'
                    ) as transaction_date,
                    t.user_profile_id,
                    t.transaction_request_type_id,
                    bc.id AS balance_category_id,
                    c.id AS currency_id,
                    CASE
                        WHEN tru.ag_id IS NOT NULL THEN tru.ag_id
                        WHEN role1.slug = 'agent' THEN usp1.id
                        ELSE null
                    END as agent_id,
                    CASE
                        WHEN tru.ma_id IS NOT NULL THEN tru.ma_id
                        WHEN role1.slug = 'master-agent' THEN usp1.id
                        WHEN role2.slug = 'master-agent' THEN usp2.id
                        ELSE null
                    END as master_agent_id,
                    CASE
                        WHEN tru.op_id IS NOT NULL THEN tru.op_id
                        WHEN role2.slug = 'operator' THEN usp2.id
                        WHEN role3.slug = 'operator' THEN usp3.id
                        WHEN role1.slug = 'operator' THEN usp1.id
                        ELSE null
                    END as operator_id,
                    SUM(ABS(t.amount)) as amount,
                    SUM(ABS(t.fee_amount)) as fee_amount,
                    SUM(ABS(t.fix_fee_amount)) as fix_fee_amount,
                    SUM(ABS(t.convenience_fee_amount)) as convenience_fee_amount,
                    SUM(ABS(t.platform_fee_amount)) as platform_fee_amount,
                    pm.parent_method as payment_parent_method,
                    pm.name as payment_method_name,
                    CASE
                        WHEN t.for_release_at IS NULL then 'Auto'
                        ELSE 'Manual'
                    END AS transaction_from,
                    trt.name AS type_name,
                    trs.name AS status_name,
                    bc.key_name AS balance_category,
                    c.code AS currency,
                    COUNT(1) AS total_transaction_count,
                    SUM(TIMESTAMPDIFF(SECOND, t.created_at, t.completed_at)) AS total_diff_seconds
                FROM transaction_requests AS t

                -- Transaction request users
                LEFT JOIN transaction_request_users as tru ON tru.transaction_request_id = t.id

                -- Payment Method
                LEFT JOIN payment_methods as pm ON pm.id = t.payment_method_id

                -- Player User
                LEFT JOIN user_profiles as usp ON usp.id = t.user_profile_id

                -- Agent User
                LEFT JOIN user_profiles as usp1 ON usp1.id = usp.referred_by
                LEFT JOIN users AS usr1 ON usr1.id = usp1.user_id
                LEFT JOIN user_roles AS usrl1 ON usrl1.user_id = usr1.id
                LEFT JOIN roles AS role1 ON role1.id = usrl1.role_id

                -- Master Agent User
                LEFT JOIN user_profiles as usp2 ON usp2.id = usp1.referred_by
                LEFT JOIN users AS usr2 ON usr2.id = usp2.user_id
                LEFT JOIN user_roles AS usrl2 ON usrl2.user_id = usr2.id
                LEFT JOIN roles AS role2 ON role2.id = usrl2.role_id

                -- Operator User
                LEFT JOIN user_profiles as usp3 ON usp3.id = usp2.referred_by
                LEFT JOIN users AS usr3 ON usr3.id = usp3.user_id
                LEFT JOIN user_roles AS usrl3 ON usrl3.user_id = usr3.id
                LEFT JOIN roles AS role3 ON role3.id = usrl3.role_id

                -- Transaction request types
                LEFT JOIN transaction_request_types as trt ON trt.id = t.transaction_request_type_id

                -- Transaction request status
                LEFT JOIN transaction_request_statuses AS trs ON trs.id = t.transaction_request_status_id

                -- User balance category
                LEFT JOIN user_profile_balances AS uspb ON uspb.id = t.user_profile_balance_id
                LEFT JOIN balance_types AS bt ON bt.id = uspb.balance_type_id
                LEFT JOIN balance_categories AS bc ON bc.id = bt.balance_category_id
                LEFT JOIN currencies AS c ON c.id = bt.currency_id
                WHERE t.completed_by IS NOT NULL AND t.completed_at BETWEEN v_start AND v_end
                GROUP BY
                    DATE_FORMAT(
                        t.completed_at,
                        '%Y-%m-%d %H:00:00'
                    ),
                    t.user_profile_id,
                    t.transaction_request_type_id,
                    bt.balance_category_id,
                    c.id,
                    t.payment_method_id,
                    CASE
                        WHEN t.for_release_at IS NULL then 'Auto'
                        ELSE 'Manual'
                    END
                ORDER BY t.user_profile_id ASC, t.completed_at ASC
            ) as wrapped_table;

        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,
                        transaction_request_type_id,
                        balance_category_id,
                        currency_id,
                        agent_id,
                        master_agent_id,
                        operator_id,
                        amount,
                        fee_amount,
                        fix_fee_amount,
                        convenience_fee_amount,
                        platform_fee_amount,
                        payment_parent_method,
                        payment_method_name,
                        transaction_from,
                        type_name,
                        status_name,
                        balance_category,
                        currency,
                        total_transaction_count,
                        total_diff_seconds,
                        key_token,
                        check_sum,
                        created_at
                    )
                    VALUES (
                        rec.v_transaction_date,
                        rec.v_user_profile_id,
                        rec.v_transaction_request_type_id,
                        rec.v_balance_category_id,
                        rec.v_currency_id,
                        rec.v_agent_id,
                        rec.v_master_agent_id,
                        rec.v_operator_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_transaction_from,
                        rec.v_type_name,
                        rec.v_status_name,
                        rec.v_balance_category,
                        rec.v_currency,
                        rec.v_total_transaction_count,
                        rec.v_total_diff_seconds,
                        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,
                        total_transaction_count = rec.v_total_transaction_count,
                        total_diff_seconds      = rec.v_total_diff_seconds,
                        updated_at              = rec.v_current_datetime;
            END LOOP;
        CLOSE c;
    END;
-- //