"""Initial schema + legacy location→vessel renames.

Revision ID: 001_initial
Revises:
Create Date: 2026-06-14
"""
from __future__ import annotations

from typing import Sequence, Union

from alembic import op

revision: str = "001_initial"
down_revision: Union[str, None] = None
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    # Legacy dev DBs may still use locations/location_id — rename before create_all.
    op.execute(
        """
        DO $$
        BEGIN
          IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'locations')
             AND NOT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'vessels')
          THEN
            ALTER TABLE locations RENAME TO vessels;
          END IF;
          IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'devices' AND column_name = 'location_id')
          THEN
            ALTER TABLE devices RENAME COLUMN location_id TO vessel_id;
          END IF;
          IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'tasks' AND column_name = 'location_id')
          THEN
            ALTER TABLE tasks RENAME COLUMN location_id TO vessel_id;
          END IF;
        END $$;
        """
    )

    from app import models  # noqa: F401
    from app.database import Base

    bind = op.get_bind()
    Base.metadata.create_all(bind)

    for stmt in (
        "CREATE INDEX IF NOT EXISTS ix_tasks_vessel_id ON tasks (vessel_id)",
        "CREATE INDEX IF NOT EXISTS ix_task_events_task_id_id ON task_events (task_id, id)",
        "CREATE INDEX IF NOT EXISTS ix_approval_requests_task_status "
        "ON approval_requests (task_id, status)",
    ):
        op.execute(stmt)

    # catalog_key added on older DBs that predate the column.
    op.execute(
        """
        DO $$
        BEGIN
          IF NOT EXISTS (
            SELECT 1 FROM information_schema.columns
            WHERE table_name = 'devices' AND column_name = 'catalog_key'
          ) THEN
            ALTER TABLE devices ADD COLUMN catalog_key VARCHAR(64);
          END IF;
        END $$;
        """
    )


def downgrade() -> None:
    op.drop_index("ix_approval_requests_task_status", table_name="approval_requests")
    op.drop_index("ix_task_events_task_id_id", table_name="task_events")
    op.drop_index("ix_tasks_vessel_id", table_name="tasks")

    for table in (
        "approval_requests",
        "task_events",
        "tasks",
        "devices",
        "vessels",
        "audit_logs",
        "balance_snapshots",
        "users",
    ):
        op.execute(f"DROP TABLE IF EXISTS {table} CASCADE")

    op.execute("DROP TYPE IF EXISTS approvalstatus")
    op.execute("DROP TYPE IF EXISTS taskstatus")
    op.execute("DROP TYPE IF EXISTS devicetype")
    op.execute("DROP TYPE IF EXISTS role")