# Generated by Django 5.0.6 on 2024-06-26 11:49

from django.db import migrations


class Migration(migrations.Migration):
    dependencies = [
        ("hordak", "0042_alter_account_code_alter_account_full_code"),
    ]

    operations = [
        # Runs as-is in both postgresql & mariadb.
        # Performance seems to be better on postgres, and postgres
        # performance can be improved further by not selecting the `account_balance`
        # column
        migrations.RunSQL(
            """
            create view hordak_leg_view as (SELECT
                L.id,
                L.uuid,
                transaction_id,
                account_id,
                A.full_code as account_full_code,
                A.name as account_name,
                A.type as account_type,
                T.date as date,
                ABS(amount) as amount,
                amount_currency,
                (CASE WHEN amount > 0 THEN 'CR' ELSE 'DR' END) AS type,
                (CASE WHEN amount > 0 THEN ABS(amount) END) AS credit,
                (CASE WHEN amount < 0 THEN ABS(amount) END) AS debit,
                (
                    CASE WHEN A.lft = A.rght - 1
                    THEN SUM(amount) OVER (PARTITION BY account_id, amount_currency ORDER BY T.date, L.id)
                    END
                ) AS account_balance,
                T.description as transaction_description,
                L.description as leg_description
            FROM hordak_leg L
            INNER JOIN hordak_transaction T on L.transaction_id = T.id
            INNER JOIN hordak_account A on A.id = L.account_id
            order by T.date desc, id desc);
            """,
            """drop view hordak_leg_view;""",
        )
    ]
