"""Full-text search over what an article actually says.

`search_vector` weighted title, summary and `content` — but 323 of the 331
articles have `content` NULL, because everything the generator writes goes into
the `sections` JSON and only the eight hand-seeded samples ever used the column.
So for 98% of the library the body contributed nothing, and a learner searching
for a drug name, a diagnostic criterion or an eponym that appears only in a
section got no result and no indication that the search had not looked there.

A generated column cannot contain a subquery, so the extraction lives in an
IMMUTABLE function it can call. `content` stays in the expression for the eight
articles that use it.

Revision ID: d6e7f8091a2b
Revises: c5d6e7f8091a
"""
from alembic import op

revision = "d6e7f8091a2b"
down_revision = "c5d6e7f8091a"
branch_labels = None
depends_on = None

EXTRACT = """
CREATE OR REPLACE FUNCTION article_sections_text(sections json)
RETURNS text LANGUAGE sql IMMUTABLE PARALLEL SAFE AS $$
  SELECT COALESCE(string_agg(CONCAT_WS(' ', s->>'title', s->>'content'), ' '), '')
  FROM json_array_elements(COALESCE(sections, '[]'::json)) AS s
$$;
"""

OLD = """
  setweight(to_tsvector('english', COALESCE(title, '')), 'A') ||
  setweight(to_tsvector('english', COALESCE(summary, '')), 'B') ||
  setweight(to_tsvector('english', COALESCE(content, '')), 'C')
"""

NEW = """
  setweight(to_tsvector('english', COALESCE(title, '')), 'A') ||
  setweight(to_tsvector('english', COALESCE(summary, '')), 'B') ||
  setweight(to_tsvector('english',
    COALESCE(content, '') || ' ' || article_sections_text(sections)), 'C')
"""


def _rebuild(expression: str) -> None:
    # The expression of a generated column cannot be altered in place, and
    # dropping the column takes its index with it.
    op.execute("DROP INDEX IF EXISTS ix_articles_search_vector")
    op.execute("ALTER TABLE articles DROP COLUMN IF EXISTS search_vector")
    op.execute(f"ALTER TABLE articles ADD COLUMN search_vector tsvector "
               f"GENERATED ALWAYS AS ({expression}) STORED")
    op.execute("CREATE INDEX ix_articles_search_vector ON articles USING gin (search_vector)")


def upgrade():
    op.execute(EXTRACT)
    _rebuild(NEW)


def downgrade():
    _rebuild(OLD)
    op.execute("DROP FUNCTION IF EXISTS article_sections_text(json)")