"""Extend vendors table for Add Vendor / Supplier form.

Revision ID: 014_vendor_form_fields
Revises: 013_item_image_barcode
Create Date: 2026-08-05

"""

from typing import Sequence, Union

import sqlalchemy as sa
from alembic import op

revision: str = "014_vendor_form_fields"
down_revision: Union[str, None] = "013_item_image_barcode"
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("vendors")}

    adds = [
        ("business_type", sa.Column("business_type", sa.String(length=40), nullable=True)),
        ("contact_person", sa.Column("contact_person", sa.String(length=200), nullable=True)),
        ("designation", sa.Column("designation", sa.String(length=100), nullable=True)),
        ("alternate_phone", sa.Column("alternate_phone", sa.String(length=50), nullable=True)),
        ("website", sa.Column("website", sa.String(length=255), nullable=True)),
        ("ntn", sa.Column("ntn", sa.String(length=50), nullable=True)),
        ("sales_tax_no", sa.Column("sales_tax_no", sa.String(length=50), 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)),
        ("city", sa.Column("city", sa.String(length=100), nullable=True)),
        ("state_province", sa.Column("state_province", sa.String(length=100), nullable=True)),
        ("country", sa.Column("country", sa.String(length=100), nullable=True)),
        ("postal_code", sa.Column("postal_code", sa.String(length=30), nullable=True)),
        ("notes", sa.Column("notes", sa.String(length=500), nullable=True)),
        ("payment_terms", sa.Column("payment_terms", sa.String(length=30), nullable=True)),
        (
            "credit_limit",
            sa.Column(
                "credit_limit",
                sa.Numeric(18, 2),
                nullable=False,
                server_default="0.00",
            ),
        ),
        (
            "opening_balance",
            sa.Column(
                "opening_balance",
                sa.Numeric(18, 2),
                nullable=False,
                server_default="0.00",
            ),
        ),
        (
            "currency",
            sa.Column(
                "currency",
                sa.String(length=10),
                nullable=False,
                server_default="PKR",
            ),
        ),
        ("po_prefix", sa.Column("po_prefix", sa.String(length=30), nullable=True)),
        (
            "default_warehouse_id",
            sa.Column("default_warehouse_id", sa.String(length=36), nullable=True),
        ),
        (
            "preferred_payment_method",
            sa.Column("preferred_payment_method", sa.String(length=40), nullable=True),
        ),
        ("logo", sa.Column("logo", sa.String(length=500), nullable=True)),
    ]
    for name, column in adds:
        if name not in columns:
            op.add_column("vendors", column)

    op.execute(
        sa.text(
            """
            UPDATE vendors
            SET address_line1 = COALESCE(NULLIF(address_line1, ''), address, '')
            WHERE address_line1 IS NULL OR address_line1 = ''
            """
        )
    )
    op.execute(
        sa.text(
            """
            UPDATE vendors
            SET contact_person = COALESCE(NULLIF(contact_person, ''), name, '')
            WHERE contact_person IS NULL OR contact_person = ''
            """
        )
    )
    op.execute(
        sa.text(
            """
            UPDATE vendors
            SET phone = COALESCE(phone, '')
            WHERE phone IS NULL
            """
        )
    )
    op.execute(
        sa.text(
            """
            UPDATE vendors
            SET city = COALESCE(NULLIF(city, ''), '')
            WHERE city IS NULL
            """
        )
    )
    op.execute(
        sa.text(
            """
            UPDATE vendors
            SET country = COALESCE(NULLIF(country, ''), 'Pakistan')
            WHERE country IS NULL OR country = ''
            """
        )
    )

    op.alter_column(
        "vendors",
        "contact_person",
        existing_type=sa.String(length=200),
        nullable=False,
        server_default="",
    )
    op.alter_column(
        "vendors",
        "phone",
        existing_type=sa.String(length=50),
        nullable=False,
        server_default="",
    )
    op.alter_column(
        "vendors",
        "address_line1",
        existing_type=sa.String(length=255),
        nullable=False,
        server_default="",
    )
    op.alter_column(
        "vendors",
        "city",
        existing_type=sa.String(length=100),
        nullable=False,
        server_default="",
    )
    op.alter_column(
        "vendors",
        "country",
        existing_type=sa.String(length=100),
        nullable=False,
        server_default="Pakistan",
    )
    op.alter_column("vendors", "contact_person", server_default=None)
    op.alter_column("vendors", "phone", server_default=None)
    op.alter_column("vendors", "address_line1", server_default=None)
    op.alter_column("vendors", "city", server_default=None)
    op.alter_column("vendors", "country", server_default=None)
    op.alter_column("vendors", "credit_limit", server_default=None)
    op.alter_column("vendors", "opening_balance", server_default=None)
    op.alter_column("vendors", "currency", server_default=None)

    indexes = {idx["name"] for idx in sa.inspect(conn).get_indexes("vendors")}
    if "ix_vendors_default_warehouse_id" not in indexes:
        op.create_index(
            "ix_vendors_default_warehouse_id",
            "vendors",
            ["default_warehouse_id"],
            unique=False,
        )

    fks = {fk["name"] for fk in sa.inspect(conn).get_foreign_keys("vendors")}
    if "fk_vendors_default_warehouse_id" not in fks:
        op.create_foreign_key(
            "fk_vendors_default_warehouse_id",
            "vendors",
            "warehouses",
            ["default_warehouse_id"],
            ["id"],
            ondelete="SET NULL",
        )


def downgrade() -> None:
    conn = op.get_bind()
    fks = {fk["name"] for fk in sa.inspect(conn).get_foreign_keys("vendors")}
    if "fk_vendors_default_warehouse_id" in fks:
        op.drop_constraint("fk_vendors_default_warehouse_id", "vendors", type_="foreignkey")
    indexes = {idx["name"] for idx in sa.inspect(conn).get_indexes("vendors")}
    if "ix_vendors_default_warehouse_id" in indexes:
        op.drop_index("ix_vendors_default_warehouse_id", table_name="vendors")

    for col in (
        "logo",
        "preferred_payment_method",
        "default_warehouse_id",
        "po_prefix",
        "currency",
        "opening_balance",
        "credit_limit",
        "payment_terms",
        "notes",
        "postal_code",
        "country",
        "state_province",
        "city",
        "address_line2",
        "address_line1",
        "sales_tax_no",
        "ntn",
        "website",
        "alternate_phone",
        "designation",
        "contact_person",
        "business_type",
    ):
        columns = {c["name"] for c in sa.inspect(conn).get_columns("vendors")}
        if col in columns:
            op.drop_column("vendors", col)