"""Migration #5: articles and subscribers (Stream D).

Schema only (build plan §2): two new tables, no data moves. Text arrives through
`python -m app.cli articles import` after the deploy; there is no backfill. Revises Stream A's
migration #3 (brands), which `articles.brand_id` points at.

* articles: editorial text of three kinds (article | airport_writeup | category_intro) with a
  draft/published status; public reads see published rows only. `author_id` follows the
  who-columns rule (FK accounts.id NULL); `source_upload_id` remembers the client hand-in a
  row came from (SET NULL if the upload goes).
* subscribers: the email-capture list, the minimum the client asked for plus the consent
  sentence and its timestamp. `unsubscribed_at` keeps a withdrawn row as a record.

Revision ID: a4b5c6d7e8f9
Revises: f3a4b5c6d7e8
Create Date: 2026-09-05
"""

import sqlalchemy as sa
from sqlalchemy.dialects import postgresql

from alembic import op

revision = "a4b5c6d7e8f9"
down_revision = "f3a4b5c6d7e8"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.create_table(
        "articles",
        sa.Column("id", sa.Integer(), primary_key=True),
        sa.Column("slug", sa.String(160), nullable=False, unique=True),
        sa.Column("title", sa.String(200), nullable=False),
        sa.Column("standfirst", sa.Text(), nullable=True),
        sa.Column("body_md", sa.Text(), nullable=False),
        sa.Column("kind", sa.String(20), nullable=False, server_default="article"),
        sa.Column("category", sa.String(80), nullable=True),
        sa.Column("airport_code", sa.String(4), nullable=True),
        sa.Column("brand_id", sa.Integer(), sa.ForeignKey("brands.id"), nullable=True),
        sa.Column("author_id", sa.Integer(), sa.ForeignKey("accounts.id"), nullable=True),
        sa.Column("status", sa.String(12), nullable=False, server_default="draft"),
        sa.Column("published_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column("hero_image", sa.String(600), nullable=True),
        sa.Column(
            "source_upload_id", sa.Integer(),
            sa.ForeignKey("client_uploads.id", ondelete="SET NULL"), nullable=True,
        ),
        sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
        sa.Column("updated_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
    )
    op.create_index("ix_articles_kind", "articles", ["kind"])
    op.create_index("ix_articles_status", "articles", ["status"])
    op.create_index("ix_articles_category", "articles", ["category"])
    op.create_index("ix_articles_airport_code", "articles", ["airport_code"])

    op.create_table(
        "subscribers",
        sa.Column("id", sa.Integer(), primary_key=True),
        sa.Column("email", sa.String(320), nullable=False, unique=True),
        sa.Column("first_name", sa.String(80), nullable=True),
        sa.Column("last_name", sa.String(80), nullable=True),
        sa.Column("home_airport", sa.String(4), nullable=True),
        sa.Column("interests", postgresql.JSONB(), nullable=False, server_default="[]"),
        sa.Column("consent_at", sa.DateTime(timezone=True), nullable=False),
        sa.Column("consent_text", sa.Text(), nullable=False),
        sa.Column("source", sa.String(40), nullable=False),
        sa.Column("created_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False),
        sa.Column("unsubscribed_at", sa.DateTime(timezone=True), nullable=True),
    )


def downgrade() -> None:
    op.drop_table("subscribers")
    for name in ("ix_articles_airport_code", "ix_articles_category", "ix_articles_status", "ix_articles_kind"):
        op.drop_index(name, table_name="articles")
    op.drop_table("articles")