"""SQLAlchemy declarative models for the compiled journal-quality DB.

These models map onto the read-only `journal_quality.db` SQLite file
that is built by `db.build_db()` from the gzipped JSON snapshots in
the user data directory. They are deliberately flat — no relationships
between tables — so that statement caching keeps per-query ORM
overhead in the ~100–300 µs range for the filter hot path.

Three tables:

- `sources`     — academic venues (journals + conferences) with
                  h-index, impact factor, DOAJ flags, predatory flags.
                  Compiled from OpenAlex sources + DOAJ + Stop Predatory
                  Journals + CORE conferences.
- `institutions`— OpenAlex institutions with h-index and ROR ID,
                  used by the Tier 3.5 affiliation-based scoring path.
- `abbreviations`— JabRef journal-name abbreviation expansions used by
                  Tier 2 name normalization (e.g. "Phys. Rev. Lett."
                  → "Physical Review Letters").

The DB is rebuilt from scratch after every download, so schema changes
ride along automatically — no Alembic, no migration plumbing.
"""

from __future__ import annotations

from sqlalchemy import Boolean, CheckConstraint, Float, Integer, String
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column


class JournalQualityBase(DeclarativeBase):
    """Standalone declarative base for the journal-quality DB.

    Intentionally NOT shared with other declarative bases in the
    codebase (`database/models/base.py`, `library/download_management/
    models/__init__.py`) — the journal-quality data is shared, public,
    read-only, and rebuilt monthly, while the other bases manage
    per-user encrypted state with completely different lifecycle.
    Sharing a base would tempt cross-table foreign keys we don't want.
    """


class Source(JournalQualityBase):
    """An academic venue (journal or conference)."""

    __tablename__ = "sources"
    __table_args__ = (
        # Defense-in-depth for ``score_source``. The API layer already
        # rejects out-of-allowlist values at ``/api/journals`` (see
        # metrics_routes.py _ALLOWED_SCORE_SOURCES), but that only
        # covers the read path. A DB-level CHECK catches any future
        # writer — a refactor of _populate_sources, a one-off import
        # script, a hand-edited manifest — that accidentally inserts
        # an invalid value. Build fails fast instead of corrupting the
        # dashboard silently. The allowlist matches what
        # _populate_sources actually emits in db.py (openalex + doaj).
        CheckConstraint(
            "score_source IN ('openalex', 'doaj')",
            name="ck_sources_score_source",
        ),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True)
    name: Mapped[str] = mapped_column(String, nullable=False)
    # Not unique: a journal can have multiple ISSN variants (print +
    # electronic), and DOAJ-only entries may collide with OpenAlex
    # entries on name. Dedup is done at build time on (name_lower, issn).
    name_lower: Mapped[str] = mapped_column(String, nullable=False, index=True)
    issn: Mapped[str | None] = mapped_column(String, index=True)
    openalex_source_id: Mapped[str | None] = mapped_column(String, index=True)
    source_type: Mapped[str | None] = mapped_column(String)
    publisher: Mapped[str | None] = mapped_column(String)
    h_index: Mapped[int | None] = mapped_column(Integer)
    impact_factor: Mapped[float | None] = mapped_column(Float)
    cited_by_count: Mapped[int | None] = mapped_column(Integer)
    # Display-only quartile derived at build time from cited_by_count
    # percentile within source_type. NULL when cited_by_count is missing.
    # Does NOT feed into the `quality` column — h-index remains canonical.
    # KNOWN-DEFERRED: index=True is unused — no query filters, sorts, or
    # groups by quartile. Kept because the reference DB is rebuilt from
    # JSON on schema-version bump (see _ensure_engine in db.py), so
    # removing the index requires only a schema version bump, not a
    # per-user migration. Post-merge cleanup.
    quartile: Mapped[str | None] = mapped_column(String(2), index=True)
    quality: Mapped[int | None] = mapped_column(Integer, index=True)
    is_in_doaj: Mapped[bool] = mapped_column(Boolean, default=False)
    # NB: there used to be a has_doaj_seal column here. DOAJ retired the
    # Seal in April 2025 and removed it from their metadata, so the
    # column was dropped (schema version 4).
    is_predatory: Mapped[bool] = mapped_column(
        Boolean, default=False, index=True
    )
    predatory_source: Mapped[str | None] = mapped_column(String)
    # Indexed: the dashboard's /api/journals endpoint filters by
    # score_source (allowlist {openalex, doaj, llm}) and without an
    # index this becomes a full scan of the ~217K-row table. With the
    # index, the filter is a single sub-millisecond lookup.
    score_source: Mapped[str] = mapped_column(
        String, default="openalex", index=True
    )


class Institution(JournalQualityBase):
    """An OpenAlex research institution.

    Used by the Tier 3.5 affiliation-based scoring path: when a paper
    has no recognizable venue, the filter falls back to the author
    institutions and takes the highest h-index across them, capped at 6.
    """

    __tablename__ = "institutions"

    openalex_id: Mapped[str] = mapped_column(String, primary_key=True)
    name: Mapped[str] = mapped_column(String, nullable=False)
    name_lower: Mapped[str] = mapped_column(String, index=True)
    ror_id: Mapped[str | None] = mapped_column(String, index=True)
    country: Mapped[str | None] = mapped_column(String)
    type: Mapped[str | None] = mapped_column(String)
    h_index: Mapped[int | None] = mapped_column(Integer, index=True)
    # KNOWN-DEFERRED: OpenAlex does not publish impact_factor for
    # institutions (only for sources/journals). This column is NULL
    # for essentially all 200K+ institution rows and is not read in
    # any scoring path. Retained for schema symmetry with
    # Source.impact_factor and to allow future enrichment. Post-merge
    # candidate for removal.
    impact_factor: Mapped[float | None] = mapped_column(Float)
    works_count: Mapped[int | None] = mapped_column(Integer)
    cited_by_count: Mapped[int | None] = mapped_column(Integer)


class PredatoryJournal(JournalQualityBase):
    """A journal name on the Stop Predatory Journals list.

    Stored as a separate table (not just an `is_predatory` flag on
    `Source`) so the runtime check works for arbitrary input names that
    aren't in OpenAlex's source list. The dict-based predecessor used
    a Python set for the same reason.
    """

    __tablename__ = "predatory_journals"

    name_lower: Mapped[str] = mapped_column(String, primary_key=True)


class PredatoryPublisher(JournalQualityBase):
    """A publisher name on the Stop Predatory Journals list.

    `is_long` flags entries with name length >= 10 chars; those are
    eligible for substring matching to catch renamed variants like
    "OMICS Publishing" matching "OMICS Publishing Group Ltd." Short
    names are exact-match only to avoid false positives.
    """

    __tablename__ = "predatory_publishers"

    name_lower: Mapped[str] = mapped_column(String, primary_key=True)
    is_long: Mapped[bool] = mapped_column(Boolean, default=False, index=True)


class PredatoryHijacked(JournalQualityBase):
    """A hijacked journal name (clone of a legitimate journal)."""

    __tablename__ = "predatory_hijacked"

    name_lower: Mapped[str] = mapped_column(String, primary_key=True)


class Abbreviation(JournalQualityBase):
    """A JabRef journal-name abbreviation → full-name expansion.

    Looked up case-insensitively when the filter sees an abbreviated
    journal_ref like 'Phys. Rev. Lett.' that needs to be expanded to
    'Physical Review Letters' before the OpenAlex name lookup.
    """

    __tablename__ = "abbreviations"

    abbrev_lower: Mapped[str] = mapped_column(String, primary_key=True)
    full_name: Mapped[str] = mapped_column(String, nullable=False)
