"""added normalized unit and food names

Revision ID: 0341b154f79a
Revises: bcfdad6b7355
Create Date: 2023-09-01 14:55:42.166766

"""

import sqlalchemy as sa
from sqlalchemy import orm, select

from alembic import op
from mealie.db.models.recipe.ingredient import IngredientFoodModel, IngredientUnitModel

# revision identifiers, used by Alembic.
revision = "0341b154f79a"
down_revision = "bcfdad6b7355"
branch_labels: str | tuple[str, ...] | None = None
depends_on: str | tuple[str, ...] | None = None


def populate_normalized_fields():
    bind = op.get_bind()
    session = orm.Session(bind=bind)

    units = (
        session.execute(
            select(IngredientUnitModel).options(
                orm.load_only(IngredientUnitModel.name, IngredientUnitModel.abbreviation)
            )
        )
        .scalars()
        .all()
    )
    for unit in units:
        if unit.name is not None:
            session.execute(
                sa.text(
                    f"UPDATE {IngredientUnitModel.__tablename__} SET name_normalized=:name_normalized WHERE id=:id"
                ).bindparams(name_normalized=IngredientUnitModel.normalize(unit.name), id=unit.id)
            )

        if unit.abbreviation is not None:
            session.execute(
                sa.text(
                    f"UPDATE {IngredientUnitModel.__tablename__} SET abbreviation_normalized=:abbreviation_normalized WHERE id=:id"
                ).bindparams(abbreviation_normalized=IngredientUnitModel.normalize(unit.abbreviation), id=unit.id)
            )

    foods = (
        session.execute(select(IngredientFoodModel).options(orm.load_only(IngredientFoodModel.name))).scalars().all()
    )
    for food in foods:
        if food.name is not None:
            session.execute(
                sa.text(
                    f"UPDATE {IngredientFoodModel.__tablename__} SET name_normalized=:name_normalized WHERE id=:id"
                ).bindparams(name_normalized=IngredientFoodModel.normalize(food.name), id=food.id)
            )

    session.commit()


def upgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.add_column("ingredient_foods", sa.Column("name_normalized", sa.String(), nullable=True))
    op.create_index(op.f("ix_ingredient_foods_name_normalized"), "ingredient_foods", ["name_normalized"], unique=False)
    op.add_column("ingredient_units", sa.Column("name_normalized", sa.String(), nullable=True))
    op.add_column("ingredient_units", sa.Column("abbreviation_normalized", sa.String(), nullable=True))
    op.create_index(
        op.f("ix_ingredient_units_abbreviation_normalized"),
        "ingredient_units",
        ["abbreviation_normalized"],
        unique=False,
    )
    op.create_index(op.f("ix_ingredient_units_name_normalized"), "ingredient_units", ["name_normalized"], unique=False)
    # ### end Alembic commands ###

    populate_normalized_fields()


def downgrade():
    # ### commands auto generated by Alembic - please adjust! ###
    op.drop_index(op.f("ix_ingredient_units_name_normalized"), table_name="ingredient_units")
    op.drop_index(op.f("ix_ingredient_units_abbreviation_normalized"), table_name="ingredient_units")
    op.drop_column("ingredient_units", "abbreviation_normalized")
    op.drop_column("ingredient_units", "name_normalized")
    op.drop_index(op.f("ix_ingredient_foods_name_normalized"), table_name="ingredient_foods")
    op.drop_column("ingredient_foods", "name_normalized")
    # ### end Alembic commands ###
