"""Extend item_groups for Group Item form fields.

Revision ID: 006_item_group_fields
Revises: 005_item_type_category
Create Date: 2026-08-04

"""

from typing import Sequence, Union

import sqlalchemy as sa
from alembic import op

revision: str = "006_item_group_fields"
down_revision: Union[str, None] = "005_item_type_category"
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 "category_id" not in columns:
        op.add_column(
            "item_groups",
            sa.Column("category_id", sa.String(length=36), nullable=True),
        )
    if "sort_order" not in columns:
        op.add_column("item_groups", sa.Column("sort_order", sa.Integer(), nullable=True))
    if "icon" not in columns:
        op.add_column("item_groups", sa.Column("icon", sa.String(length=500), nullable=True))
    if "remarks" not in columns:
        op.add_column("item_groups", sa.Column("remarks", sa.String(length=255), nullable=True))

    # Ensure description is non-null varchar(255) for existing rows
    op.execute(
        sa.text(
            "UPDATE item_groups SET description = '' WHERE description IS NULL"
        )
    )
    op.alter_column(
        "item_groups",
        "description",
        existing_type=sa.Text(),
        type_=sa.String(length=255),
        nullable=False,
        existing_nullable=True,
    )

    # Backfill category_id from first category in the same company when missing
    op.execute(
        sa.text(
            """
            UPDATE item_groups g
            INNER JOIN (
                SELECT company_id, MIN(id) AS id
                FROM item_categories
                GROUP BY company_id
            ) c ON c.company_id = g.company_id
            SET g.category_id = c.id
            WHERE g.category_id IS NULL
            """
        )
    )

    # Drop orphan groups that still have no category (no categories in company)
    op.execute(sa.text("DELETE FROM item_groups WHERE category_id IS NULL"))

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

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

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


def downgrade() -> None:
    op.drop_constraint("fk_item_groups_category_id", "item_groups", type_="foreignkey")
    op.drop_index("ix_item_groups_category_id", table_name="item_groups")
    op.drop_column("item_groups", "remarks")
    op.drop_column("item_groups", "icon")
    op.drop_column("item_groups", "sort_order")
    op.drop_column("item_groups", "category_id")
    op.alter_column(
        "item_groups",
        "description",
        existing_type=sa.String(length=255),
        type_=sa.Text(),
        nullable=True,
        existing_nullable=False,
    )