admin / Bid-Sentinel

"""SQLAlchemy ORM models."""
import enum
from datetime import datetime

from sqlalchemy import (
    Boolean,
    DateTime,
    Enum,
    Float,
    ForeignKey,
    Integer,
    String,
    Text,
    UniqueConstraint,
    func,
)
from sqlalchemy.orm import Mapped, mapped_column

from app.database import Base


class UserRole(str, enum.Enum):
    admin = "admin"
    analyst = "analyst"  # read-only + Intelligence access
    standard = "standard"


class BidStatus(str, enum.Enum):
    pending = "Pending"
    bid = "Bid"
    no_bid = "No Bid"


class BidOutcome(str, enum.Enum):
    """Result of a bid, set after submission. Drives win-rate metrics.

    Stored as a plain string column (not a DB enum) so the column can be added
    to existing databases with a simple idempotent ALTER (see seed.create_tables).
    """

    awaiting = "Awaiting"
    won = "Won"
    lost = "Lost"
    no_bid = "No Bid"  # auto-set when Bid Status is "No Bid"
    disregarded = "Disregarded"  # no-bid: hide from active pipeline


class User(Base):
    __tablename__ = "users"

    id: Mapped[int] = mapped_column(primary_key=True)
    email: Mapped[str] = mapped_column(String(255), unique=True, index=True, nullable=False)
    full_name: Mapped[str | None] = mapped_column(String(255), nullable=True)
    hashed_password: Mapped[str] = mapped_column(String(255), nullable=False)
    role: Mapped[UserRole] = mapped_column(
        Enum(UserRole, name="user_role"), default=UserRole.standard, nullable=False
    )
    is_active: Mapped[bool] = mapped_column(Boolean, default=True, nullable=False)
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), nullable=False
    )


class Tender(Base):
    __tablename__ = "tenders"

    id: Mapped[int] = mapped_column(primary_key=True)

    # Deduplication keys. reference is the portal's notice id; url is the canonical link.
    reference: Mapped[str | None] = mapped_column(String(255), unique=True, index=True, nullable=True)
    url: Mapped[str] = mapped_column(String(1024), unique=True, index=True, nullable=False)

    title: Mapped[str] = mapped_column(String(1024), nullable=False)
    description: Mapped[str | None] = mapped_column(Text, nullable=True)

    published_date: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    closing_date: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)

    # Numeric value where parseable; raw string preserved for ranges / "POA".
    value_amount: Mapped[float | None] = mapped_column(Float, nullable=True)
    value_text: Mapped[str | None] = mapped_column(String(255), nullable=True)
    currency: Mapped[str] = mapped_column(String(8), default="GBP", nullable=False)

    duration: Mapped[str | None] = mapped_column(String(255), nullable=True)
    buyer: Mapped[str | None] = mapped_column(String(512), nullable=True)
    source: Mapped[str | None] = mapped_column(String(64), nullable=True)
    matched_keyword: Mapped[str | None] = mapped_column(String(128), nullable=True)

    bid_status: Mapped[BidStatus] = mapped_column(
        Enum(BidStatus, name="bid_status"), default=BidStatus.pending, nullable=False
    )
    # Plain string (see BidOutcome docstring); values are "Awaiting"/"Won"/"Lost".
    outcome: Mapped[str] = mapped_column(
        String(16), default="Awaiting", server_default="Awaiting", nullable=False
    )

    # Deterministic analysis (no AI) — see app/analysis.py.
    required_certs: Mapped[str] = mapped_column(
        String(1024), default="", server_default="", nullable=False
    )
    fit_score: Mapped[int | None] = mapped_column(Integer, nullable=True)
    fit_matched: Mapped[str] = mapped_column(
        String(1024), default="", server_default="", nullable=False
    )
    # Top technical requirements scraped from the description (newline-joined).
    tech_requirements: Mapped[str] = mapped_column(
        Text, default="", server_default="", nullable=False
    )
    # AI-generated summary (populated only when the AI bolt-on is enabled).
    ai_summary: Mapped[str] = mapped_column(
        Text, default="", server_default="", nullable=False
    )

    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), nullable=False
    )
    updated_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), onupdate=func.now(), nullable=False
    )


class Portal(Base):
    """A bid portal Bid Sentinel can scan. Seeded from the portal registry."""

    __tablename__ = "portals"

    id: Mapped[int] = mapped_column(primary_key=True)
    key: Mapped[str] = mapped_column(String(64), unique=True, index=True, nullable=False)
    name: Mapped[str] = mapped_column(String(255), nullable=False)
    url: Mapped[str] = mapped_column(String(1024), nullable=False)
    scope: Mapped[str | None] = mapped_column(Text, nullable=True)
    # live = a working scraper exists (vs. registered/scaffolded).
    live: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False)
    # enabled = user wants this portal scanned (default on, can be unticked).
    enabled: Mapped[bool] = mapped_column(Boolean, default=True, nullable=False)
    # custom = added by an admin via the UI (vs. a built-in registry portal).
    custom: Mapped[bool] = mapped_column(Boolean, default=False, server_default="false", nullable=False)


class CustomKeyword(Base):
    """User-supplied keyword, searched in addition to the built-in cyber list."""

    __tablename__ = "custom_keywords"

    id: Mapped[int] = mapped_column(primary_key=True)
    keyword: Mapped[str] = mapped_column(String(128), unique=True, nullable=False)
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), nullable=False
    )


class CapabilityTerm(Base):
    """A service/capability term (the company profile) used for fit scoring."""

    __tablename__ = "capability_terms"

    id: Mapped[int] = mapped_column(primary_key=True)
    term: Mapped[str] = mapped_column(String(128), unique=True, nullable=False)
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), nullable=False
    )


class SuppressedOpportunity(Base):
    """A specific opportunity the user bulk-deleted. Its url/reference are blocked
    so the scraper never inserts it again (see scraper/runner._persist)."""

    __tablename__ = "suppressed_opportunities"

    id: Mapped[int] = mapped_column(primary_key=True)
    url: Mapped[str | None] = mapped_column(String(1024), index=True, nullable=True)
    reference: Mapped[str | None] = mapped_column(String(255), index=True, nullable=True)
    title: Mapped[str | None] = mapped_column(String(1024), nullable=True)
    # Links a suppression to the delete batch that created it, so an Undo can lift it.
    batch_id: Mapped[str | None] = mapped_column(String(64), index=True, nullable=True)
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), nullable=False
    )


class DeletedOpportunity(Base):
    """Snapshot of a deleted opportunity, kept briefly so a deletion can be undone.
    Grouped by ``batch_id`` (one per delete action)."""

    __tablename__ = "deleted_opportunities"

    id: Mapped[int] = mapped_column(primary_key=True)
    batch_id: Mapped[str] = mapped_column(String(64), index=True, nullable=False)
    snapshot: Mapped[str] = mapped_column(Text, nullable=False)  # JSON of the tender fields
    suppressed: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False)
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), index=True, nullable=False
    )


class TuneOutTerm(Base):
    """A 'tune-out' (negative) term. Opportunities matching one are suppressed
    from scrapes and the dashboard — UNLESS they are cyber-security related, which
    are always kept (see app/scraper/keywords.matches_tuneout)."""

    __tablename__ = "tuneout_terms"

    id: Mapped[int] = mapped_column(primary_key=True)
    term: Mapped[str] = mapped_column(String(255), unique=True, nullable=False)
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), nullable=False
    )


class CpvCode(Base):
    """A CPV (Common Procurement Vocabulary) code + description used as scrape
    criteria, in addition to the keyword filters. A notice classified with (or
    mentioning) one of these codes/descriptions is treated as relevant."""

    __tablename__ = "cpv_codes"

    id: Mapped[int] = mapped_column(primary_key=True)
    # Stored normalised (digits only, check-digit suffix stripped) for lenient matching.
    code: Mapped[str] = mapped_column(String(16), unique=True, nullable=False)
    description: Mapped[str] = mapped_column(
        String(255), default="", server_default="", nullable=False
    )
    # Toggled off = kept in the list but not applied to the scrape criteria.
    enabled: Mapped[bool] = mapped_column(
        Boolean, default=True, server_default="true", nullable=False
    )
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), nullable=False
    )


class Accreditation(Base):
    """A certification/accreditation the company holds (powers cert-gap analysis)."""

    __tablename__ = "accreditations"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(128), unique=True, nullable=False)
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), nullable=False
    )


class ScheduleSlot(Base):
    """One recurring scrape time: a specific day-of-week + time (local TZ).

    Scheduling by "multiple days and times" is modelled as multiple rows, e.g.
    Mon/Wed/Fri at 08:00 and 17:00 = 6 slots.
    """

    __tablename__ = "schedule_slots"
    __table_args__ = (
        UniqueConstraint("day_of_week", "hour", "minute", name="uq_schedule_slot"),
    )

    id: Mapped[int] = mapped_column(primary_key=True)
    day_of_week: Mapped[int] = mapped_column(Integer, nullable=False)  # 0=Mon .. 6=Sun
    hour: Mapped[int] = mapped_column(Integer, nullable=False)  # 0..23
    minute: Mapped[int] = mapped_column(Integer, default=0, nullable=False)  # 0..59
    enabled: Mapped[bool] = mapped_column(Boolean, default=True, nullable=False)
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), nullable=False
    )


class Comment(Base):
    """A user comment/note attached to an opportunity."""

    __tablename__ = "comments"

    id: Mapped[int] = mapped_column(primary_key=True)
    tender_id: Mapped[int] = mapped_column(
        ForeignKey("tenders.id", ondelete="CASCADE"), index=True, nullable=False
    )
    author_email: Mapped[str] = mapped_column(String(255), nullable=False)
    body: Mapped[str] = mapped_column(Text, nullable=False)
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), nullable=False
    )


class AppSetting(Base):
    """Simple key/value application settings (e.g. the AI on/off toggle)."""

    __tablename__ = "app_settings"

    key: Mapped[str] = mapped_column(String(64), primary_key=True)
    value: Mapped[str] = mapped_column(String(512), nullable=False)


class AuditLog(Base):
    """Append-only record of application activity, viewable by admins."""

    __tablename__ = "audit_logs"

    id: Mapped[int] = mapped_column(primary_key=True)
    created_at: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), server_default=func.now(), index=True, nullable=False
    )
    actor: Mapped[str] = mapped_column(String(255), default="anonymous", nullable=False)
    action: Mapped[str] = mapped_column(String(255), nullable=False)
    detail: Mapped[str] = mapped_column(String(512), default="", nullable=False)
    status_code: Mapped[int | None] = mapped_column(Integer, nullable=True)
    ip: Mapped[str | None] = mapped_column(String(64), nullable=True)