"""link doc_sources to a product (nullable product_id)

Lets a seller manually match a crawled documentation source to a specific
module. NULL = unlinked / global (previous behaviour). When set, the AI draft
retrieval prioritises pages from that module's linked sources.

Revision ID: docp01
Revises: bns001
Create Date: 2026-07-03

"""
from alembic import op
import sqlalchemy as sa
from sqlalchemy import text


# revision identifiers, used by Alembic.
revision = 'docp01'
down_revision = 'bns001'
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_sources'):
        return  # public schema or schema without doc_sources — skip
    if not _column_exists(conn, 'doc_sources', 'product_id'):
        op.add_column(
            'doc_sources',
            sa.Column('product_id', sa.Integer(), nullable=True),
        )
    # Create index only if column now exists and index doesn't
    idx_exists = conn.execute(
        text(
            "SELECT 1 FROM pg_indexes "
            "WHERE schemaname = current_schema() "
            "AND tablename = 'doc_sources' AND indexname = 'ix_doc_sources_product_id'"
        )
    ).fetchone()
    if not idx_exists:
        op.create_index('ix_doc_sources_product_id', 'doc_sources', ['product_id'])


def downgrade():
    conn = op.get_bind()
    if not _table_exists(conn, 'doc_sources'):
        return
    if _column_exists(conn, 'doc_sources', 'product_id'):
        op.drop_index('ix_doc_sources_product_id', 'doc_sources')
        op.drop_column('doc_sources', 'product_id')