"""Add required item_type_id to item_groups.

Revision ID: 007_item_group_item_type
Revises: 006_item_group_fields
Create Date: 2026-08-04

"""

from typing import Sequence, Union

import sqlalchemy as sa
from alembic import op

revision: str = "007_item_group_item_type"
down_revision: Union[str, None] = "006_item_group_fields"
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    conn = op.get_bind()
    inspector = sa.inspect(conn)
    columns = {col["name"] for col in inspector.get_columns("item_groups")}

    if "item_type_id" not in columns:
        op.add_column(
            "item_groups",
            sa.Column("item_type_id", sa.String(length=36), nullable=True),
        )

    # Prefer item type matching the group's category, else any type in the company
    op.execute(
        sa.text(
            """
            UPDATE item_groups g
            INNER JOIN (
                SELECT t.company_id, t.category_id, MIN(t.id) AS id
                FROM item_types t
                WHERE t.category_id IS NOT NULL
                GROUP BY t.company_id, t.category_id
            ) t ON t.company_id = g.company_id AND t.category_id = g.category_id
            SET g.item_type_id = t.id
            WHERE g.item_type_id IS NULL
            """
        )
    )
    op.execute(
        sa.text(
            """
            UPDATE item_groups g
            INNER JOIN (
                SELECT company_id, MIN(id) AS id
                FROM item_types
                GROUP BY company_id
            ) t ON t.company_id = g.company_id
            SET g.item_type_id = t.id
            WHERE g.item_type_id IS NULL
            """
        )
    )
    op.execute(sa.text("DELETE FROM item_groups WHERE item_type_id IS NULL"))

    op.alter_column(
        "item_groups",
        "item_type_id",
        existing_type=sa.String(length=36),
        nullable=False,
    )

    indexes = {idx["name"] for idx in inspector.get_indexes("item_groups")}
    if "ix_item_groups_item_type_id" not in indexes:
        op.create_index(
            "ix_item_groups_item_type_id", "item_groups", ["item_type_id"], unique=False
        )

    fks = {fk["name"] for fk in inspector.get_foreign_keys("item_groups")}
    if "fk_item_groups_item_type_id" not in fks:
        op.create_foreign_key(
            "fk_item_groups_item_type_id",
            "item_groups",
            "item_types",
            ["item_type_id"],
            ["id"],
            ondelete="RESTRICT",
        )


def downgrade() -> None:
    op.drop_constraint("fk_item_groups_item_type_id", "item_groups", type_="foreignkey")
    op.drop_index("ix_item_groups_item_type_id", table_name="item_groups")
    op.drop_column("item_groups", "item_type_id")