<filename>app/backend/wells/migrations/0115_update_well_class_indended_water_use_20200616_2027.py
# Generated by Django 2.2.13 on 2020-06-16 20:27

from django.db import migrations

ADD_NOT_APPLICABLE_INTENDED_WATER_USE_CODE = """
    INSERT INTO intended_water_use_code
        (
            create_user,
            create_date,
            update_user,
            update_date,
            display_order,
            effective_date,
            expiry_date,
            intended_water_use_code,
            description
        )
        VALUES
        (
            'WELLS',
            '2017-07-01 08:00:00+00',
            'WELLS',
            '2017-07-01 08:00:00+00',
            200,
            '2020-01-01 00:00:00+00',
            '9999-12-31 23:59:59+00',
            'NA',
            'Not Applicable'
        );
"""

UPDATE_WELLS = """
    UPDATE well SET well_class_code = 'UNK' WHERE well_class_code IS NULL;
    UPDATE well SET intended_water_use_code = 'NA' WHERE intended_water_use_code IS NULL;
    UPDATE well
        SET well_class_code = 'WATR_SPPLY'
    WHERE
        intended_water_use_code IN ('DOM', 'COM', 'DWS', 'IRR', 'OP_LP_GEO') AND
        well_class_code = 'UNK';
"""

UPDATE_SUBMISSIONS = """
    UPDATE activity_submission
        SET well_class_code = 'UNK'
    WHERE
        well_class_code IS NULL AND
        well_activity_code IN ('CON', 'LEGACY');

    UPDATE activity_submission
        SET intended_water_use_code = 'NA'
    WHERE
        intended_water_use_code IS NULL AND
        well_class_code != 'WATR_SPPLY' AND
        well_activity_code IN ('CON', 'LEGACY');

    UPDATE activity_submission
        SET well_class_code = 'WATR_SPPLY'
    WHERE
        intended_water_use_code IN ('DOM', 'COM', 'DWS', 'IRR', 'OP_LP_GEO') AND
        (well_class_code = 'UNK' OR well_class_code IS NULL);
"""

UPDATE_FIELDS_PROVIDED = """
    UPDATE fields_provided AS fp
        SET well_class = true
    FROM activity_submission AS act
    WHERE
        act.filing_number = fp.filing_number AND
        fp.well_class = false AND
        act.well_class_code IS NOT NULL;

    UPDATE fields_provided AS fp
        SET intended_water_use = true
    FROM activity_submission AS act
    WHERE
        act.filing_number = fp.filing_number AND
        fp.intended_water_use = false AND
        act.intended_water_use_code IS NOT NULL;
"""

class Migration(migrations.Migration):

    dependencies = [
        ('wells', '0114_obswells_in_vector_tileserver_view_20200618_1800'),
    ]

    operations = [
        migrations.RunSQL([
            ADD_NOT_APPLICABLE_INTENDED_WATER_USE_CODE,
            UPDATE_WELLS,
            UPDATE_SUBMISSIONS,
            UPDATE_FIELDS_PROVIDED,
        ]),
    ]
