-- DELIMITER //

CREATE PROCEDURE $procName(IN v_date 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_type VARCHAR(255),
            v_user_count BIGINT(20),
            v_d1 BIGINT(20),
            v_d7 BIGINT(20),
            v_d30 BIGINT(20),
            v_key_token CHAR(36),
            v_current_datetime VARCHAR(100)
        );

        DECLARE c CURSOR FOR
            SELECT
                DATE_FORMAT(v_date, "%Y-%m-%d") as transaction_date,
                'registration' as type,
                (SELECT COUNT(DISTINCT(user_profiles.id))
                    FROM user_profiles
                    LEFT JOIN user_roles ON user_roles.user_id = user_profiles.user_id
                    LEFT JOIN roles ON roles.id = user_roles.role_id
                    WHERE roles.slug = 'player'
                    AND DATE_FORMAT(user_profiles.created_at, "%Y-%m-%d") BETWEEN v_date AND v_date) AS user_count,
                (
                    CASE
                        WHEN DATE(NOW()) <= DATE_ADD(v_date, INTERVAL 1 DAY)
                        THEN NULL
                        ELSE (SELECT COUNT(DISTINCT(user_profile_id))
                        FROM (
                            SELECT DISTINCT(user_profiles.id) AS user_profile_id
                            FROM login_logs
                            LEFT JOIN user_profiles ON user_profiles.user_id = login_logs.user_id
                            AND login_logs.created_at BETWEEN DATE_ADD(v_date, INTERVAL 1 DAY) AND DATE_ADD(v_date, INTERVAL 1 DAY)
                            UNION
                            SELECT DISTINCT(user_profile_id) AS user_profile_id
                            FROM report_bets
                            WHERE report_bets.transaction_date BETWEEN DATE_ADD(v_date, INTERVAL 1 DAY) AND DATE_ADD(v_date, INTERVAL 1 DAY)
                            UNION
                            SELECT DISTINCT(v_transaction_requests.user_profile_id) AS user_profile_id
                            FROM v_transaction_requests
                            WHERE v_transaction_requests.transaction_date
                            BETWEEN DATE_ADD(v_date, INTERVAL 1 DAY) AND DATE_ADD(v_date, INTERVAL 1 DAY)
                        ) as dau WHERE user_profile_id IN (
                            SELECT DISTINCT(user_profiles.id) as user_profile_id
                            FROM user_profiles
                            LEFT JOIN user_roles ON user_roles.user_id = user_profiles.user_id
                            LEFT JOIN roles ON roles.id = user_roles.role_id
                            WHERE roles.slug = 'player'
                            AND DATE_FORMAT(user_profiles.created_at, "%Y-%m-%d") BETWEEN v_date AND v_date
                        ))
                    END
                ) AS d1,
                (
                    CASE WHEN DATE(NOW()) <= DATE_ADD(v_date, INTERVAL 7 DAY)
                    THEN NULL
                    ELSE (SELECT COUNT(DISTINCT(user_profile_id))
                    FROM (
                        SELECT DISTINCT(user_profiles.id) AS user_profile_id
                        FROM login_logs
                        LEFT JOIN user_profiles ON user_profiles.user_id = login_logs.user_id
                        AND login_logs.created_at BETWEEN DATE_ADD(v_date, INTERVAL 7 DAY) AND DATE_ADD(v_date, INTERVAL 7 DAY)
                        UNION
                        SELECT DISTINCT(user_profile_id) AS user_profile_id
                        FROM report_bets
                        WHERE report_bets.transaction_date BETWEEN DATE_ADD(v_date, INTERVAL 7 DAY) AND DATE_ADD(v_date, INTERVAL 7 DAY)
                        UNION
                        SELECT DISTINCT(v_transaction_requests.user_profile_id) AS user_profile_id
                        FROM v_transaction_requests
                        WHERE v_transaction_requests.transaction_date
                        BETWEEN DATE_ADD(v_date, INTERVAL 7 DAY) AND DATE_ADD(v_date, INTERVAL 7 DAY)
                    ) as dau WHERE user_profile_id IN (
                        SELECT DISTINCT(user_profiles.id) as user_profile_id
                        FROM user_profiles
                        LEFT JOIN user_roles ON user_roles.user_id = user_profiles.user_id
                        LEFT JOIN roles ON roles.id = user_roles.role_id
                        WHERE roles.slug = 'player'
                        AND DATE_FORMAT(user_profiles.created_at, "%Y-%m-%d") BETWEEN v_date AND v_date
                    ))
                    END
                ) AS d7,
                (
                    CASE WHEN DATE(NOW()) <= DATE_ADD(v_date, INTERVAL 30 DAY)
                    THEN NULL
                    ELSE (SELECT COUNT(DISTINCT(user_profile_id))
                    FROM (
                        SELECT DISTINCT(user_profiles.id) AS user_profile_id
                        FROM login_logs
                        LEFT JOIN user_profiles ON user_profiles.user_id = login_logs.user_id
                        AND login_logs.created_at BETWEEN DATE_ADD(v_date, INTERVAL 30 DAY) AND DATE_ADD(v_date, INTERVAL 30 DAY)
                        UNION
                        SELECT DISTINCT(user_profile_id) AS user_profile_id
                        FROM report_bets
                        WHERE report_bets.transaction_date BETWEEN DATE_ADD(v_date, INTERVAL 30 DAY) AND DATE_ADD(v_date, INTERVAL 30 DAY)
                        UNION
                        SELECT DISTINCT(v_transaction_requests.user_profile_id) AS user_profile_id
                        FROM v_transaction_requests
                        WHERE v_transaction_requests.transaction_date
                        BETWEEN DATE_ADD(v_date, INTERVAL 30 DAY) AND DATE_ADD(v_date, INTERVAL 30 DAY)
                    ) as dau WHERE user_profile_id IN (
                        SELECT DISTINCT(user_profiles.id) as user_profile_id
                        FROM user_profiles
                        LEFT JOIN user_roles ON user_roles.user_id = user_profiles.user_id
                        LEFT JOIN roles ON roles.id = user_roles.role_id
                        WHERE roles.slug = 'player'
                        AND DATE_FORMAT(user_profiles.created_at, "%Y-%m-%d") BETWEEN v_date AND v_date
                    ))
                    END
                ) AS d30,
                UUID() as key_token,
                CONVERT_TZ(NOW(), '+00:00', '+00:00') AS current_datetime; -- Timezone GMT+08:00 is set

        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,
                        type,
                        user_count,
                        d1,
                        d7,
                        d30,
                        key_token,
                        created_at
                    )
                    VALUES (
                        rec.v_transaction_date,
                        rec.v_type,
                        rec.v_user_count,
                        rec.v_d1,
                        rec.v_d7,
                        rec.v_d30,
                        rec.v_key_token,
                        rec.v_current_datetime
                    ) ON DUPLICATE KEY UPDATE
                        user_count = rec.v_user_count,
                        d1         = rec.v_d1,
                        d7         = rec.v_d7,
                        d30        = rec.v_d30,
                        updated_at = rec.v_current_datetime;
            END LOOP;
        CLOSE c;
    END;
-- //
