# Generated by Django 4.0.10 on 2024-01-22 23:05

import django.db.models.deletion
from django.db import connection, migrations, models

BASEROW_TABLE_ROW_COUNT_FUNC = """
CREATE OR REPLACE FUNCTION get_baserow_table_row_count(table_id INT) RETURNS BIGINT AS $$
DECLARE
    row_count BIGINT;
BEGIN
    BEGIN
        EXECUTE 'SELECT COUNT(*) FROM database_table_' || table_id || ' WHERE trashed=false;' INTO row_count;
        RETURN row_count;
    EXCEPTION WHEN OTHERS THEN
        return null;
    END;
end;
$$
LANGUAGE plpgsql;
"""

BASEROW_TABLE_FILE_UNIQUES_QUERY_FUN = """
CREATE OR REPLACE FUNCTION _get_baserow_table_file_uniques(table__id INT) RETURNS TABLE(file_unique TEXT, field_id INT, table_id INT) AS $$
DECLARE
    field RECORD;
    filename TEXT;
BEGIN
FOR field IN EXECUTE 'SELECT * FROM database_field JOIN database_filefield ON id=field_ptr_id WHERE trashed=false AND table_id=' || table__id || ';'
LOOP
    BEGIN
        RETURN QUERY EXECUTE 'SELECT SPLIT_PART(JSONB_ARRAY_ELEMENTS(field_' || field.id || ') ->> ''name'', ''_'', 1), ' || field.id || ', ' || field.table_id || ' FROM database_table_' || field.table_id;
    EXCEPTION
        WHEN undefined_table THEN
            RAISE NOTICE 'Could not find database_table_%', field.table_id;
        WHEN undefined_column THEN
            RAISE NOTICE 'Could not find field_% in database_table_%', field.id, field.table_id;
    END;
END LOOP;
END;
$$
LANGUAGE plpgsql;
"""

BASEROW_TABLE_FILE_UNIQUES_FUNC = """
CREATE OR REPLACE FUNCTION get_distinct_baserow_table_file_uniques(table_id INT) RETURNS TEXT[] AS $$
DECLARE
    file_uniques TEXT[];
BEGIN
    BEGIN
        EXECUTE 'SELECT array_agg(distinct file_unique) from _get_baserow_table_file_uniques(' || table_id || ');' into file_uniques;
        return file_uniques;
    EXCEPTION WHEN OTHERS THEN
        return null;
    END;
END;
$$
LANGUAGE plpgsql;
"""


def forward(apps, schema_editor):
    with connection.cursor() as cursor:
        cursor.execute(BASEROW_TABLE_ROW_COUNT_FUNC)
        cursor.execute(BASEROW_TABLE_FILE_UNIQUES_QUERY_FUN)
        cursor.execute(BASEROW_TABLE_FILE_UNIQUES_FUNC)


def reverse(apps, schema_editor):
    with connection.cursor() as cursor:
        cursor.execute("DROP FUNCTION get_baserow_table_row_count(INT)")
        cursor.execute("DROP FUNCTION _get_baserow_table_file_uniques(INT)")
        cursor.execute("DROP FUNCTION get_baserow_table_file_uniques(INT)")


class Migration(migrations.Migration):
    dependencies = [
        ("database", "0150_formulafield_duration_format_and_more"),
    ]

    operations = [
        migrations.CreateModel(
            name="TableUsageUpdate",
            fields=[
                (
                    "id",
                    models.AutoField(
                        auto_created=True,
                        primary_key=True,
                        serialize=False,
                        verbose_name="ID",
                    ),
                ),
                (
                    "row_count",
                    models.IntegerField(
                        help_text="The change in the row count value. It can be positive or negative.A null value means that the row_count is not changed, but it might be changed storage count and we want to recalculate the storage needed by this table.",
                        null=True,
                    ),
                ),
                ("timestamp", models.DateTimeField(auto_now=True)),
                (
                    "table",
                    models.OneToOneField(
                        on_delete=django.db.models.deletion.CASCADE,
                        related_name="usage_update",
                        to="database.table",
                    ),
                ),
            ],
        ),
        migrations.CreateModel(
            name="TableUsage",
            fields=[
                (
                    "id",
                    models.AutoField(
                        auto_created=True,
                        primary_key=True,
                        serialize=False,
                        verbose_name="ID",
                    ),
                ),
                (
                    "row_count",
                    models.PositiveIntegerField(
                        help_text="The number of non-trashed rows of the linked table.",
                        null=True,
                    ),
                ),
                ("row_count_updated_at", models.DateTimeField(null=True)),
                (
                    "storage_usage",
                    models.PositiveIntegerField(
                        help_text="The storage needed in MB by files saved in file fields of the linked table.",
                        null=True,
                    ),
                ),
                ("storage_usage_updated_at", models.DateTimeField(null=True)),
                (
                    "table",
                    models.OneToOneField(
                        on_delete=django.db.models.deletion.CASCADE,
                        related_name="usage",
                        to="database.table",
                    ),
                ),
            ],
        ),
        migrations.AlterField(
            model_name="table",
            name="row_count",
            field=models.PositiveIntegerField(
                db_column="row_count",
                help_text="Deprecated: use usage.row_count instead. This field will be removed in a future version.",
                null=True,
            ),
        ),
        migrations.RenameField(
            model_name="table",
            old_name="row_count",
            new_name="_row_count",
        ),
        migrations.AlterField(
            model_name="table",
            name="row_count_updated_at",
            field=models.DateTimeField(
                db_column="row_count_updated_at",
                help_text="Deprecated: use usage.row_count_updated_at instead. This field will be removed in a future version.",
                null=True,
            ),
        ),
        migrations.RenameField(
            model_name="table",
            old_name="row_count_updated_at",
            new_name="_row_count_updated_at",
        ),
        migrations.RunPython(forward, reverse),
    ]