migration-script.sql

delimiter $$

drop procedure if exists migrate_forecasts $$
create procedure migrate_forecasts(instance_id int, user_id int, fx1_id int, fx2_id int)
begin
    declare f1id int default null;
    declare f2id int default null;
    declare f1p int default null;
    declare f2p int default null;
    declare forecast_to_keep_id int default null;
    declare forecast_to_delete_id int default null;

    select
        forecast1_id,
        forecast2_id,
        forecast1_points,
        forecast2_points,
        if(r.forecast1_points >= r.forecast2_points, r.forecast1_id, r.forecast2_id),
        if(r.forecast1_points >= r.forecast2_points, r.forecast2_id, r.forecast1_id)
    into
        f1id,
        f2id,
        f1p,
        f2p,
        forecast_to_keep_id,
        forecast_to_delete_id
    from (
        select
            group_concat(if(f.fixture_id = fx1_id, id, null)) forecast1_id,
            group_concat(if(f.fixture_id = fx2_id, id, null)) forecast2_id,
            group_concat(if(f.fixture_id = fx1_id, points, null)) forecast1_points,
            group_concat(if(f.fixture_id = fx2_id, points, null)) forecast2_points
        from scorecast_backup.forecasts f
        where f.instance_id = instance_id and f.user_id = user_id and (f.fixture_id = fx1_id OR f.fixture_id = fx2_id)
        group by f.user_id
    ) r;

    delete from forecasts where forecasts.id = forecast_to_delete_id;
    update forecasts f set f.fixture_id = fx1_id where f.id = forecast_to_keep_id;
    insert into aaa_logs set msg = concat('        f1(id = ', f1id, ', fx_id = ', fx1_id ,') has ', f1p, ' points');
    insert into aaa_logs set msg = concat('        f2(id = ', f2id, ', fx_id = ', fx2_id ,') has ', f2p, ' points');
    insert into aaa_logs set msg = concat('                Deleted forecast ', forecast_to_delete_id);
    insert into aaa_logs set msg = concat('                Updated forecast ', forecast_to_keep_id);
    insert into aaa_logs set msg = concat('                Kept forecast is linked to fixture ', (select fixture_id from forecasts where forecasts.id = forecast_to_keep_id));
    insert into aaa_logs set msg = '';
end $$

drop procedure if exists remove_forecast_duplicates $$
create procedure remove_forecast_duplicates(fx1_id int, fx2_id int)
begin
    declare done tinyint default false;
    declare instance_id int default null;
    declare user_id int default null;
    declare forecasts_count int default null;

    declare cursor1
    cursor for
    select
        r3.instance_id,
        r3.user_id
    from (
        select * from (
            select
                *,
                count(*) as forecasts_count from
            (
                select
                    f.instance_id,
                    f.user_id
                from forecasts f
                WHERE f.fixture_id = fx1_id OR f.fixture_id = fx2_id
            ) r1
            group by r1.instance_id, r1.user_id
        ) r2
        where r2.forecasts_count > 1
    ) r3;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    open cursor1;

    my_loop:
    loop
        fetch cursor1 into instance_id, user_id;
        if done then
            leave my_loop;
        else
            call migrate_forecasts(instance_id, user_id, fx1_id, fx2_id);
        end if;
    end loop;

    close cursor1;

end $$

drop procedure if exists remove_fixture_duplicate $$
create procedure remove_fixture_duplicate(fx_id int)
begin
    delete from fixtures where fixtures.id = fx_id;
    insert into aaa_logs set msg = concat('        Deleted fixture ', fx_id);
    insert into aaa_logs set msg = concat('');
end $$

drop procedure if exists remove_duplicates $$
create procedure remove_duplicates(fx1_id int, fx2_id int)
begin
    insert into aaa_logs set msg = concat('Call remove_forecast_duplicates(fx1 (keep): ', fx1_id, ', fx2 (delete): ', fx2_id);
    CALL remove_forecast_duplicates(fx1_id, fx2_id);
    insert into aaa_logs set msg = concat('Call remove_fixture_duplicate(fx: ', fx2_id,')');
    CALL remove_fixture_duplicate(fx2_id);
end $$

delimiter ;

drop table if exists aaa_logs;
create table aaa_logs (msg varchar(255));

CALL remove_duplicates(25065, 25274);
CALL remove_duplicates(25067, 25275);
CALL remove_duplicates(25069, 25276);
CALL remove_duplicates(25071, 25277);
CALL remove_duplicates(25073, 25278);
CALL remove_duplicates(25075, 25279);
CALL remove_duplicates(25320, 26176);