from django.db import migrations


class Migration(migrations.Migration):
    """
    We previously accepted invalid ISO 8601 dates like 1909-3-5 for
    date values of custom profile fields. Correct them by adding the
    missing leading zeros: 1909-03-05.
    """

    dependencies = [
        ("zerver", "0305_realm_deactivated_redirect"),
    ]

    operations = [
        migrations.RunSQL(
            sql="""\
                UPDATE zerver_customprofilefieldvalue
                SET value = to_char(to_date(value, 'YYYY-MM-DD'), 'YYYY-MM-DD')
                FROM zerver_customprofilefield AS f
                WHERE f.id = field_id
                AND f.field_type = 4
                AND value <> to_char(to_date(value, 'YYYY-MM-DD'), 'YYYY-MM-DD');
            """,
            reverse_sql="",
        ),
    ]

from django.db import migrations


class Migration(migrations.Migration):
    """
    We previously accepted invalid ISO 8601 dates like 1909-3-5 for
    date values of custom profile fields. Correct them by adding the
    missing leading zeros: 1909-03-05.
    """

    dependencies = [
        ("zerver", "0305_realm_deactivated_redirect"),
    ]

    operations = [
        migrations.RunSQL(
            sql="""\
                UPDATE zerver_customprofilefieldvalue
                SET value = to_char(to_date(value, 'YYYY-MM-DD'), 'YYYY-MM-DD')
                FROM zerver_customprofilefield AS f
                WHERE f.id = field_id
                AND f.field_type = 4
                AND CASE
                        WHEN f.field_type = 4
                        THEN value <> to_char(to_date(value, 'YYYY-MM-DD'), 'YYYY-MM-DD')
                    END;
            """,
            reverse_sql="",
        ),
    ]

Enforce evaluation order in 0306 WHERE clause.
migrations: Enforce evaluation order in 0306 WHERE clause.

Depending on PostgreSQL’s query plan, it was possible for the value
condition to be evaluated before the field_type condition was checked,
leading to errors like

psycopg2.errors.InvalidDatetimeFormat: invalid value "stri" for "YYYY"
DETAIL:  Value must be an integer.

Signed-off-by: Anders Kaseorg <dfdb7392591db597bc41cf266a9c3bc12a2706e5@zulip.com>

  from django.db import migrations
  
  
  class Migration(migrations.Migration):
      """
      We previously accepted invalid ISO 8601 dates like 1909-3-5 for
      date values of custom profile fields. Correct them by adding the
      missing leading zeros: 1909-03-05.
      """
  
      dependencies = [
          ("zerver", "0305_realm_deactivated_redirect"),
      ]
  
      operations = [
          migrations.RunSQL(
              sql="""\
                  UPDATE zerver_customprofilefieldvalue
                  SET value = to_char(to_date(value, 'YYYY-MM-DD'), 'YYYY-MM-DD')
                  FROM zerver_customprofilefield AS f
                  WHERE f.id = field_id
                  AND f.field_type = 4
+                 AND CASE
+                         WHEN f.field_type = 4
-                 AND value <> to_char(to_date(value, 'YYYY-MM-DD'), 'YYYY-MM-DD');
+                         THEN value <> to_char(to_date(value, 'YYYY-MM-DD'), 'YYYY-MM-DD')
+                     END;
              """,
              reverse_sql="",
          ),
      ]
  
Enforce evaluation order in 0306 WHERE clause.
## Code Before:
from django.db import migrations


class Migration(migrations.Migration):
    """
    We previously accepted invalid ISO 8601 dates like 1909-3-5 for
    date values of custom profile fields. Correct them by adding the
    missing leading zeros: 1909-03-05.
    """

    dependencies = [
        ("zerver", "0305_realm_deactivated_redirect"),
    ]

    operations = [
        migrations.RunSQL(
            sql="""\
                UPDATE zerver_customprofilefieldvalue
                SET value = to_char(to_date(value, 'YYYY-MM-DD'), 'YYYY-MM-DD')
                FROM zerver_customprofilefield AS f
                WHERE f.id = field_id
                AND f.field_type = 4
                AND value <> to_char(to_date(value, 'YYYY-MM-DD'), 'YYYY-MM-DD');
            """,
            reverse_sql="",
        ),
    ]

## Instruction:
Enforce evaluation order in 0306 WHERE clause.
## Code After:
from django.db import migrations


class Migration(migrations.Migration):
    """
    We previously accepted invalid ISO 8601 dates like 1909-3-5 for
    date values of custom profile fields. Correct them by adding the
    missing leading zeros: 1909-03-05.
    """

    dependencies = [
        ("zerver", "0305_realm_deactivated_redirect"),
    ]

    operations = [
        migrations.RunSQL(
            sql="""\
                UPDATE zerver_customprofilefieldvalue
                SET value = to_char(to_date(value, 'YYYY-MM-DD'), 'YYYY-MM-DD')
                FROM zerver_customprofilefield AS f
                WHERE f.id = field_id
                AND f.field_type = 4
                AND CASE
                        WHEN f.field_type = 4
                        THEN value <> to_char(to_date(value, 'YYYY-MM-DD'), 'YYYY-MM-DD')
                    END;
            """,
            reverse_sql="",
        ),
    ]
