"""Extend items table for Add New Item form.

Revision ID: 011_item_form_fields
Revises: 010_warehouses
Create Date: 2026-08-05

"""

from typing import Sequence, Union

import sqlalchemy as sa
from alembic import op

revision: str = "011_item_form_fields"
down_revision: Union[str, None] = "010_warehouses"
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def _drop_fk_on_column(table: str, column: str) -> None:
    conn = op.get_bind()
    inspector = sa.inspect(conn)
    for fk in inspector.get_foreign_keys(table):
        if column in fk.get("constrained_columns", []):
            name = fk.get("name")
            if name:
                op.drop_constraint(name, table, type_="foreignkey")


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

    if "barcode" not in columns:
        op.add_column("items", sa.Column("barcode", sa.String(length=100), nullable=True))
    if "specifications" not in columns:
        op.add_column("items", sa.Column("specifications", sa.String(length=500), nullable=True))
    if "remarks" not in columns:
        op.add_column("items", sa.Column("remarks", sa.String(length=500), nullable=True))
    if "brand_name" not in columns:
        op.add_column("items", sa.Column("brand_name", sa.String(length=100), nullable=True))
    if "base_unit_id" not in columns:
        op.add_column("items", sa.Column("base_unit_id", sa.String(length=36), nullable=True))
    if "warehouse_id" not in columns:
        op.add_column("items", sa.Column("warehouse_id", sa.String(length=36), nullable=True))
    if "tax_percent" not in columns:
        op.add_column(
            "items",
            sa.Column(
                "tax_percent",
                sa.Numeric(8, 4),
                nullable=False,
                server_default="0.0000",
            ),
        )

    op.execute(
        sa.text(
            """
            UPDATE items i
            INNER JOIN (
                SELECT company_id, MIN(id) AS id
                FROM base_units
                GROUP BY company_id
            ) b ON b.company_id = i.company_id
            SET i.base_unit_id = b.id
            WHERE i.base_unit_id IS NULL
            """
        )
    )

    op.execute(
        sa.text(
            """
            UPDATE items i
            INNER JOIN (
                SELECT company_id, MIN(id) AS id
                FROM warehouses
                GROUP BY company_id
            ) w ON w.company_id = i.company_id
            SET i.warehouse_id = w.id
            WHERE i.warehouse_id IS NULL
            """
        )
    )

    op.execute(
        sa.text(
            """
            UPDATE items i
            INNER JOIN (
                SELECT company_id, MIN(id) AS id
                FROM item_categories
                GROUP BY company_id
            ) c ON c.company_id = i.company_id
            SET i.category_id = c.id
            WHERE i.category_id IS NULL
            """
        )
    )

    op.execute(
        sa.text(
            """
            UPDATE items i
            INNER JOIN (
                SELECT company_id, MIN(id) AS id
                FROM item_types
                GROUP BY company_id
            ) t ON t.company_id = i.company_id
            SET i.item_type_id = t.id
            WHERE i.item_type_id IS NULL
            """
        )
    )

    op.execute(
        sa.text(
            """
            UPDATE items i
            INNER JOIN (
                SELECT company_id, MIN(id) AS id
                FROM item_groups
                GROUP BY company_id
            ) g ON g.company_id = i.company_id
            SET i.group_id = g.id
            WHERE i.group_id IS NULL
            """
        )
    )

    op.execute(
        sa.text(
            """
            DELETE FROM items
            WHERE category_id IS NULL
               OR item_type_id IS NULL
               OR group_id IS NULL
               OR base_unit_id IS NULL
               OR warehouse_id IS NULL
            """
        )
    )

    for column in ("category_id", "item_type_id", "group_id", "unit_type_id"):
        _drop_fk_on_column("items", column)

    op.alter_column(
        "items",
        "unit_type_id",
        existing_type=sa.String(length=36),
        nullable=True,
    )

    op.alter_column(
        "items",
        "description",
        existing_type=sa.Text(),
        type_=sa.String(length=500),
        existing_nullable=True,
    )

    op.alter_column("items", "category_id", existing_type=sa.String(length=36), nullable=False)
    op.alter_column("items", "item_type_id", existing_type=sa.String(length=36), nullable=False)
    op.alter_column("items", "group_id", existing_type=sa.String(length=36), nullable=False)
    op.alter_column("items", "base_unit_id", existing_type=sa.String(length=36), nullable=False)
    op.alter_column("items", "warehouse_id", existing_type=sa.String(length=36), nullable=False)
    op.alter_column("items", "tax_percent", server_default=None)

    op.create_foreign_key(
        "fk_items_category_id",
        "items",
        "item_categories",
        ["category_id"],
        ["id"],
        ondelete="RESTRICT",
    )
    op.create_foreign_key(
        "fk_items_item_type_id",
        "items",
        "item_types",
        ["item_type_id"],
        ["id"],
        ondelete="RESTRICT",
    )
    op.create_foreign_key(
        "fk_items_group_id",
        "items",
        "item_groups",
        ["group_id"],
        ["id"],
        ondelete="RESTRICT",
    )
    op.create_foreign_key(
        "fk_items_unit_type_id",
        "items",
        "unit_types",
        ["unit_type_id"],
        ["id"],
        ondelete="SET NULL",
    )

    indexes = {idx["name"] for idx in sa.inspect(conn).get_indexes("items")}
    if "ix_items_base_unit_id" not in indexes:
        op.create_index("ix_items_base_unit_id", "items", ["base_unit_id"], unique=False)
    if "ix_items_warehouse_id" not in indexes:
        op.create_index("ix_items_warehouse_id", "items", ["warehouse_id"], unique=False)

    fks = {fk["name"] for fk in sa.inspect(conn).get_foreign_keys("items")}
    if "fk_items_base_unit_id" not in fks:
        op.create_foreign_key(
            "fk_items_base_unit_id",
            "items",
            "base_units",
            ["base_unit_id"],
            ["id"],
            ondelete="RESTRICT",
        )
    if "fk_items_warehouse_id" not in fks:
        op.create_foreign_key(
            "fk_items_warehouse_id",
            "items",
            "warehouses",
            ["warehouse_id"],
            ["id"],
            ondelete="RESTRICT",
        )


def downgrade() -> None:
    for name in (
        "fk_items_warehouse_id",
        "fk_items_base_unit_id",
        "fk_items_unit_type_id",
        "fk_items_group_id",
        "fk_items_item_type_id",
        "fk_items_category_id",
    ):
        conn = op.get_bind()
        fks = {fk["name"] for fk in sa.inspect(conn).get_foreign_keys("items")}
        if name in fks:
            op.drop_constraint(name, "items", type_="foreignkey")

    op.drop_index("ix_items_warehouse_id", table_name="items")
    op.drop_index("ix_items_base_unit_id", table_name="items")
    op.drop_column("items", "tax_percent")
    op.drop_column("items", "warehouse_id")
    op.drop_column("items", "base_unit_id")
    op.drop_column("items", "brand_name")
    op.drop_column("items", "remarks")
    op.drop_column("items", "specifications")
    op.drop_column("items", "barcode")
    op.alter_column(
        "items",
        "description",
        existing_type=sa.String(length=500),
        type_=sa.Text(),
        existing_nullable=True,
    )