"""Opening hours (Stream G, G1): the `airport_hours` table.

Schema only (build plan §2). One row per reading, collected from an airport operator's site or
entered by hand, nothing deleted; the reader picks the newest row per airport, hand over collected
(`app/services/hours/store.py`). `entered_by_id` is a who-column: `FK accounts.id NULL`. Rehearsed
up, down and up on a copy of the 11 Sep nightly dump. Revises migration #4 (accounts), the single
head at the time of writing; `alembic heads` was checked immediately before this file was written
and before it was committed.

Revision ID: d8e9f0a1b2c3
Revises: c6d7e8f9a0b1
Create Date: 2026-09-11
"""

import sqlalchemy as sa
from sqlalchemy.dialects import postgresql

from alembic import op

revision = "d8e9f0a1b2c3"
down_revision = "c6d7e8f9a0b1"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.create_table(
        "airport_hours",
        sa.Column("id", sa.Integer(), primary_key=True),
        sa.Column("location_id", sa.Integer(), sa.ForeignKey("locations.id"), nullable=False),
        sa.Column("source_kind", sa.String(length=12), nullable=False),
        sa.Column("entered_by_id", sa.Integer(), sa.ForeignKey("accounts.id"), nullable=True),
        sa.Column("observed_at", sa.DateTime(timezone=True), nullable=False),
        sa.Column("source_url", sa.String(length=600), nullable=True),
        sa.Column("text", sa.Text(), nullable=False),
        sa.Column("detail", postgresql.JSONB(), nullable=True),
        sa.Column(
            "created_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False
        ),
    )
    op.create_index("ix_airport_hours_location_id", "airport_hours", ["location_id"])


def downgrade() -> None:
    op.drop_index("ix_airport_hours_location_id", table_name="airport_hours")
    op.drop_table("airport_hours")