# Generated by Django 5.0.6 on 2024-05-28 21:07

from django.db import migrations


def forward(apps, schema_editor):
    if schema_editor.connection.vendor == "postgresql":
        # Performance improvement, functionality unchanged
        schema_editor.execute(
            "DROP TRIGGER update_full_account_codes_trigger ON hordak_account"
        )
        schema_editor.execute(
            """
            CREATE OR REPLACE FUNCTION update_full_account_codes()
                RETURNS TRIGGER AS
            $$
            BEGIN
                -- Update all children's full account codes

                -- Only do this if the account code has been changed
                IF OLD.code != NEW.code OR (OLD.code IS NULL) != (NEW.code IS NULL) THEN
                    -- Useful for debugging, check the postgres logs to see output
                    -- RAISE WARNING 'Left %%, Right %%, tree %%, %%|%% -> %%|%%', NEW.lft, NEW.rght, NEW.tree_id, OLD.name, OLD.code, NEW.name, NEW.code;

                    UPDATE
                        hordak_account AS a
                    SET
                        full_code = (
                            SELECT CASE
                                -- Aggregate the codes of all parents to make the full account code.
                                -- However, if this path includes NULL values then the full code must be set to null.
                                -- Checking for the existence of NULLS in an array is not that easy in postgres
                                -- (https://stackoverflow.com/q/22695015/764723) so we do a little tick to keep things
                                -- sane and readable. We simply compare the stringified version of the array
                                -- against one that has NULL values replaced with '*'. If they match then we are free of any NULL
                                -- values.
                                WHEN array_to_string(array_agg(nullif(code, '')), '', '') = array_to_string(array_agg(nullif(code, '')), '', '*')
                                    THEN string_agg(code, '' order by lft)
                                ELSE NULL
                            END
                            FROM hordak_account AS a2
                            WHERE a2.lft <= a.lft AND a2.rght >= a.rght AND a.tree_id = a2.tree_id
                        )
                    -- Only update this account and the accounts below it in the tree
                    WHERE a.lft >= NEW.lft AND a.rght <= NEW.rght AND a.tree_id = NEW.tree_id
                    ;

                END IF;

                RETURN NULL;
            END;
            $$
            LANGUAGE plpgsql;
        """
        )
        # The function above requires 'FOR EACH ROW' on the trigger
        schema_editor.execute(
            """
            CREATE TRIGGER update_full_account_codes_trigger
            AFTER INSERT OR UPDATE OR DELETE ON hordak_account
            FOR EACH ROW
            WHEN (pg_trigger_depth() = 0)
            EXECUTE PROCEDURE update_full_account_codes();
        """
        )

    elif schema_editor.connection.vendor == "mysql":
        # Performance improvement not implemented in mysql (a port is welcome)
        # But we do fix an issue that surfaced
        schema_editor.execute("DROP PROCEDURE update_full_account_codes")
        schema_editor.execute(
            """
            CREATE PROCEDURE update_full_account_codes(changed_lft int, changed_rght int, changed_tree int)
            BEGIN
                UPDATE
                    hordak_account AS a
                SET
                    full_code = (
                        SELECT CASE
                            -- Aggregate the codes of all parents to make the full account code.
                            -- However, if this path includes NULL values then the full code must be set to null.
                            -- Checking for the existence of NULLS in an array is not that easy in mariadb
                            -- so we do a little tick to keep things
                            -- sane and readable. We simply compare the stringified version of the array
                            -- against one that has NULL values replaced with '*'. If they match then we are free of any NULL
                            -- values.
                            WHEN GROUP_CONCAT(IFNULL(NULLIF(code, ''), '*')) = GROUP_CONCAT(IFNULL(NULLIF(code, ''), ''))
                                THEN GROUP_CONCAT(code ORDER BY lft SEPARATOR '')
                            ELSE NULL
                        END
                        FROM hordak_account AS a2
                        WHERE a2.lft <= a.lft AND a2.rght >= a.rght AND a.tree_id = a2.tree_id
                    )
                -- Only update this account and the accounts below it in the tree
                WHERE a.lft >= changed_lft AND a.rght <= changed_rght AND a.tree_id = changed_tree
                ;
            END
        """
        )

    else:
        raise NotImplementedError(
            "Don't know how to create trigger for %s" % schema_editor.connection.vendor
        )


def reverse(apps, schema_editor):
    if schema_editor.connection.vendor == "postgresql":
        # Recreate update_full_account_codes as it was in migration 0027
        schema_editor.execute(
            "DROP TRIGGER update_full_account_codes_trigger ON hordak_account"
        )
        schema_editor.execute(
            """
            CREATE OR REPLACE FUNCTION update_full_account_codes()
                RETURNS TRIGGER AS
            $$
            BEGIN
                -- Set empty string codes to be NULL
                UPDATE hordak_account SET code = NULL where code = '';

                -- Set full code to the combination of the parent account's codes
                UPDATE
                    hordak_account AS a
                SET
                    full_code = (
                        SELECT string_agg(code, '' order by lft)
                        FROM hordak_account AS a2
                        WHERE a2.lft <= a.lft AND a2.rght >= a.rght AND a.tree_id = a2.tree_id AND code IS NOT NULL
                    )
                    WHERE tree_id IN (SELECT DISTINCT tree_id FROM hordak_account WHERE code IS NOT NULL);  -- search only account trees without null codes


                -- Set full codes to NULL where a parent account includes a NULL code
                UPDATE
                    hordak_account AS a
                SET
                    full_code = NULL
                WHERE
                    (
                        SELECT COUNT(*)
                        FROM hordak_account AS a2
                        WHERE a2.lft <= a.lft AND a2.rght >= a.rght AND a.tree_id = a2.tree_id AND a2.code IS NULL
                    ) > 0
                    AND full_code IS NOT NULL;  -- search only account trees without null codes
                RETURN NULL;
            END;
            $$
            LANGUAGE plpgsql;
        """
        )
        schema_editor.execute(
            """
            CREATE TRIGGER update_full_account_codes_trigger
            AFTER INSERT OR UPDATE OR DELETE ON hordak_account
            WHEN (pg_trigger_depth() = 0)
            EXECUTE PROCEDURE update_full_account_codes();
        """
        )
    elif schema_editor.connection.vendor == "mysql":
        # the triggers will have to be called within django again...
        schema_editor.execute("DROP PROCEDURE update_full_account_codes")
    else:
        raise NotImplementedError(
            "Don't know how to drop trigger for %s" % schema_editor.connection.vendor
        )


class Migration(migrations.Migration):
    dependencies = [
        ("hordak", "0040_alter_account_name"),
    ]
    atomic = False

    operations = [
        migrations.RunPython(forward, reverse),
    ]
