# Generated by Django 3.1.4 on 2021-01-04 08:25

from django.db import migrations, models


class Migration(migrations.Migration):
    dependencies = [
        ("shops", "0003_shop_reference"),
    ]

    operations = [
        migrations.AddField(
            model_name="salary",
            name="value",
            field=models.DecimalField(decimal_places=2, default=0, max_digits=7),
        ),
        migrations.RunSQL(
            """CREATE OR REPLACE FUNCTION close_shop(shopid int) RETURNS void
                    LANGUAGE plpgsql AS
                $$
                DECLARE
                    values text := '';
                    t_row  int;
                BEGIN
                    FOR t_row in SELECT shops_employment.user_id, shops_employment.id
                                 FROM shops_employment
                                 where shops_employment.shop_id = shopid
                                   and upper(timespan) is null
                        LOOP
                            values := values || '(now()::date, ' || t_row ||
                                      ', (select coalesce(avg(value), 2000) from shops_salary where employee_id = ' ||
                                       t_row || ')), ';
                        END LOOP;
                    values := rtrim(values, ', ');
                    if not values = '' then
                        EXECUTE 'insert into shops_salary ("when", "employee_id", "value") values' || values || ';';
                        EXECUTE 'update shops_employment SET timespan = daterange(lower(timespan), now()::date)
                                 where shop_id = ' || shopid ||
                                  ' and upper(timespan) is null';
                    end if;
                END;
                $$; """,
            reverse_sql="""
                DROP FUNCTION close_shop;
            """
        ),
    ]
