admin / Bid-Sentinel

"""Database initialization: create tables and seed the portal registry.

The administrator account is no longer auto-seeded — it is created on first run
via the setup screen (POST /auth/setup). See app/routers/auth.py.
"""
import logging

from sqlalchemy import select, text

from app.database import AsyncSessionLocal, Base, engine
from app.models import Portal
from app.scraper.portals import PORTALS

logger = logging.getLogger("seed")

# Lightweight, idempotent migrations for columns added after the first release.
# (No Alembic in this project; create_all only creates missing tables.)
_MIGRATIONS = [
    "ALTER TABLE tenders ADD COLUMN IF NOT EXISTS outcome VARCHAR(16) NOT NULL DEFAULT 'Awaiting'",
    "ALTER TABLE tenders ADD COLUMN IF NOT EXISTS required_certs VARCHAR(1024) NOT NULL DEFAULT ''",
    "ALTER TABLE tenders ADD COLUMN IF NOT EXISTS fit_score INTEGER",
    "ALTER TABLE tenders ADD COLUMN IF NOT EXISTS fit_matched VARCHAR(1024) NOT NULL DEFAULT ''",
    "ALTER TABLE tenders ADD COLUMN IF NOT EXISTS tech_requirements TEXT NOT NULL DEFAULT ''",
    "ALTER TABLE tenders ADD COLUMN IF NOT EXISTS ai_summary TEXT NOT NULL DEFAULT ''",
    "ALTER TABLE portals ADD COLUMN IF NOT EXISTS custom BOOLEAN NOT NULL DEFAULT false",
    "ALTER TABLE cpv_codes ADD COLUMN IF NOT EXISTS enabled BOOLEAN NOT NULL DEFAULT true",
    "ALTER TABLE suppressed_opportunities ADD COLUMN IF NOT EXISTS batch_id VARCHAR(64)",
]

# Enum-type changes are run one-per-transaction and tolerated if they fail
# (e.g. the value already exists). ADD VALUE IF NOT EXISTS needs PostgreSQL 12+.
_TYPE_MIGRATIONS = [
    "ALTER TYPE user_role ADD VALUE IF NOT EXISTS 'analyst'",
]


async def create_tables() -> None:
    async with engine.begin() as conn:
        await conn.run_sync(Base.metadata.create_all)
        for stmt in _MIGRATIONS:
            await conn.execute(text(stmt))
    for stmt in _TYPE_MIGRATIONS:
        try:
            async with engine.begin() as conn:
                await conn.execute(text(stmt))
        except Exception as exc:  # noqa: BLE001 - isolated; must not block startup
            logger.warning("Type migration skipped (%s): %s", stmt, exc)
    logger.info("Database tables ensured.")


async def seed_portals() -> None:
    """Insert any registry portals missing from the DB (default enabled=True).

    Existing rows are left untouched so a user's enabled/disabled choices are
    preserved across restarts; only name/url/scope/live are kept in sync with
    the registry.
    """
    async with AsyncSessionLocal() as db:
        existing = await db.execute(select(Portal.key))
        existing_keys = {k for (k,) in existing.all()}

        added = 0
        for p in PORTALS:
            if p.key in existing_keys:
                row = (
                    await db.execute(select(Portal).where(Portal.key == p.key))
                ).scalar_one()
                row.name, row.url, row.scope, row.live = p.name, p.url, p.scope, p.live
                row.custom = False
            else:
                db.add(
                    Portal(
                        key=p.key,
                        name=p.name,
                        url=p.url,
                        scope=p.scope,
                        live=p.live,
                        enabled=True,
                        custom=False,
                    )
                )
                added += 1
        await db.commit()
        if added:
            logger.info("Seeded %d new portal(s).", added)