"""Extend warehouses table for New Warehouse form.

Revision ID: 020_warehouse_form_fields
Revises: 019_item_transactions
Create Date: 2026-08-08
"""

from typing import Sequence, Union

import sqlalchemy as sa
from alembic import op

revision: str = "020_warehouse_form_fields"
down_revision: Union[str, None] = "019_item_transactions"
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("warehouses")}
    indexes = {idx["name"] for idx in inspector.get_indexes("warehouses")}
    fks = {fk["name"] for fk in inspector.get_foreign_keys("warehouses")}

    adds = [
        (
            "status",
            sa.Column(
                "status",
                sa.String(length=20),
                nullable=False,
                server_default="active",
            ),
        ),
        (
            "priority",
            sa.Column(
                "priority",
                sa.String(length=20),
                nullable=False,
                server_default="normal",
            ),
        ),
        (
            "parent_warehouse_id",
            sa.Column("parent_warehouse_id", sa.String(length=36), nullable=True),
        ),
        ("contact_person", sa.Column("contact_person", sa.String(length=200), nullable=True)),
        ("address_line1", sa.Column("address_line1", sa.String(length=255), nullable=True)),
        ("address_line2", sa.Column("address_line2", sa.String(length=255), nullable=True)),
        ("country", sa.Column("country", sa.String(length=100), nullable=True)),
        ("state_province", sa.Column("state_province", sa.String(length=100), nullable=True)),
        ("city", sa.Column("city", sa.String(length=100), nullable=True)),
        ("postal_code", sa.Column("postal_code", sa.String(length=30), nullable=True)),
        ("latitude", sa.Column("latitude", sa.String(length=30), nullable=True)),
        ("longitude", sa.Column("longitude", sa.String(length=30), nullable=True)),
        ("capacity_uom", sa.Column("capacity_uom", sa.String(length=50), nullable=True)),
        ("operating_hours", sa.Column("operating_hours", sa.String(length=100), nullable=True)),
        ("notes", sa.Column("notes", sa.String(length=500), nullable=True)),
        (
            "allow_stock_in",
            sa.Column(
                "allow_stock_in",
                sa.Boolean(),
                nullable=False,
                server_default=sa.text("1"),
            ),
        ),
        (
            "allow_stock_out",
            sa.Column(
                "allow_stock_out",
                sa.Boolean(),
                nullable=False,
                server_default=sa.text("1"),
            ),
        ),
        (
            "allow_stock_transfer",
            sa.Column(
                "allow_stock_transfer",
                sa.Boolean(),
                nullable=False,
                server_default=sa.text("1"),
            ),
        ),
        (
            "allow_returns",
            sa.Column(
                "allow_returns",
                sa.Boolean(),
                nullable=False,
                server_default=sa.text("1"),
            ),
        ),
        ("attachments", sa.Column("attachments", sa.Text(), nullable=True)),
    ]
    for name, column in adds:
        if name not in columns:
            op.add_column("warehouses", column)

    op.execute(
        sa.text(
            """
            UPDATE warehouses
            SET
                address_line1 = COALESCE(address_line1, NULLIF(address, '')),
                city = COALESCE(city, NULLIF(location, '')),
                status = CASE
                    WHEN is_active = 0 THEN 'inactive'
                    ELSE COALESCE(NULLIF(status, ''), 'active')
                END,
                notes = COALESCE(notes, remarks)
            """
        )
    )

    if "ix_warehouses_parent_warehouse_id" not in indexes:
        op.create_index(
            "ix_warehouses_parent_warehouse_id",
            "warehouses",
            ["parent_warehouse_id"],
            unique=False,
        )
    if "ix_warehouses_status" not in indexes:
        op.create_index("ix_warehouses_status", "warehouses", ["status"], unique=False)

    if "fk_warehouses_parent_warehouse_id" not in fks:
        op.create_foreign_key(
            "fk_warehouses_parent_warehouse_id",
            "warehouses",
            "warehouses",
            ["parent_warehouse_id"],
            ["id"],
            ondelete="SET NULL",
        )


def downgrade() -> None:
    conn = op.get_bind()
    inspector = sa.inspect(conn)
    columns = {col["name"] for col in inspector.get_columns("warehouses")}
    indexes = {idx["name"] for idx in inspector.get_indexes("warehouses")}
    fks = {fk["name"] for fk in inspector.get_foreign_keys("warehouses")}

    if "fk_warehouses_parent_warehouse_id" in fks:
        op.drop_constraint("fk_warehouses_parent_warehouse_id", "warehouses", type_="foreignkey")
    if "ix_warehouses_status" in indexes:
        op.drop_index("ix_warehouses_status", table_name="warehouses")
    if "ix_warehouses_parent_warehouse_id" in indexes:
        op.drop_index("ix_warehouses_parent_warehouse_id", table_name="warehouses")

    drops = [
        "attachments",
        "allow_returns",
        "allow_stock_transfer",
        "allow_stock_out",
        "allow_stock_in",
        "notes",
        "operating_hours",
        "capacity_uom",
        "longitude",
        "latitude",
        "postal_code",
        "city",
        "state_province",
        "country",
        "address_line2",
        "address_line1",
        "contact_person",
        "parent_warehouse_id",
        "priority",
        "status",
    ]
    for name in drops:
        if name in columns:
            op.drop_column("warehouses", name)