"""add product_id to doc_pages (per-page module link)

Links individual scraped doc pages to a specific AddonsProducts module,
enabling per-page product matching in AI draft retrieval (vs source-level
linking). Auto-populated by the scraper via URL path inference; also
backfillable via scripts/backfill_doc_page_products.py.

Revision ID: docp02
Revises: nt001
Create Date: 2026-07-07
"""
from alembic import op
import sqlalchemy as sa
from sqlalchemy import text

revision = 'docp02'
down_revision = 'docp01'
branch_labels = None
depends_on = None


def _column_exists(conn, table, column):
    """Return True if column already exists in the current search_path schema."""
    result = conn.execute(
        text(
            "SELECT 1 FROM information_schema.columns "
            "WHERE table_schema = current_schema() "
            "AND table_name = :t AND column_name = :c"
        ),
        {"t": table, "c": column},
    ).fetchone()
    return result is not None


def _table_exists(conn, table):
    """Return True if table already exists in the current search_path schema."""
    result = conn.execute(
        text(
            "SELECT 1 FROM information_schema.tables "
            "WHERE table_schema = current_schema() AND table_name = :t"
        ),
        {"t": table},
    ).fetchone()
    return result is not None


def upgrade():
    conn = op.get_bind()
    if not _table_exists(conn, "doc_pages"):
        return  # public schema or schema without doc_pages — skip
    if not _column_exists(conn, "doc_pages", "product_id"):
        op.add_column(
            "doc_pages",
            sa.Column("product_id", sa.Integer(), nullable=True),
        )
    idx_exists = conn.execute(
        text(
            "SELECT 1 FROM pg_indexes "
            "WHERE schemaname = current_schema() "
            "AND tablename = 'doc_pages' AND indexname = 'ix_doc_pages_product_id'"
        )
    ).fetchone()
    if not idx_exists:
        op.create_index("ix_doc_pages_product_id", "doc_pages", ["product_id"])


def downgrade():
    conn = op.get_bind()
    if not _table_exists(conn, "doc_pages"):
        return
    if _column_exists(conn, "doc_pages", "product_id"):
        op.drop_index("ix_doc_pages_product_id", table_name="doc_pages")
        op.drop_column("doc_pages", "product_id")