#!/usr/bin/env python3
"""
Seeds reference data (countries, exchanges, sectors, industries), the built-in strategies
(docs/SCREENING.md §2), and the DEMO DATA universe (docs/DATA_SOURCES.md §9) — then runs
ingestion + recompute synchronously (no Celery broker required) so a fresh `docker compose up`
has a fully populated, browsable database. Run with:

    PYTHONPATH=. python3 scripts/seed_demo.py

Idempotent: re-running skips rows that already exist rather than duplicating them.
"""
from __future__ import annotations

import sys

from app.adapters.demo import DEMO_SEED_PROFILES, DemoDataAdapter
from app.core.db import SessionLocal
from app.core.logging import get_logger
from app.engines.reference_data import reference_plan, rows_to_insert
from app.engines.screening.presets import BUILT_IN_STRATEGIES
from app.models import Country, Exchange, Industry, Screener, Sector
from app.workers.ingest import ingest_security
from app.workers.recompute import recompute_security

logger = get_logger(__name__)


def seed_reference_data(db) -> dict[str, int]:
    """Create every reference row DEMO ingestion needs. Returns `{table: rows_inserted}`.

    AUDIT FIX (incomplete reference seed). This function used to insert **countries only** and
    exit 0, leaving:

        countries: 19   exchanges: 0   sectors: 0   industries: 0

    Everything else was created lazily by `get_or_create_company()` during ingestion, which meant
    (a) nothing about the reference layer could be verified until an INSERT failed, (b) every
    exchange row got `name = <the MIC>` and `timezone = "UTC"`, and (c) a provider short name
    ("NASDAQ") and its MIC ("XNAS") could become two rows for one venue.

    The data comes from `app/engines/reference_data.py`, which `app/engines/identity.py` also
    reads — so the venue this resolves and the venue this seeds are the same fact, not two copies
    of it that can drift.

    Idempotent by lookup on each table's natural key (`countries.iso2`, `exchanges.mic`,
    `sectors.code`, `industries.code`), all of which are UNIQUE in the schema. Re-running inserts
    nothing and returns zeros.
    """
    plan = reference_plan((p.sector_code, p.industry_code) for p in DEMO_SEED_PROFILES)
    missing = rows_to_insert(
        plan,
        {row[0] for row in db.query(Country.iso2).all()},
        {row[0] for row in db.query(Exchange.mic).all()},
        {row[0] for row in db.query(Sector.code).all()},
        {row[0] for row in db.query(Industry.code).all()},
    )

    # 1. Countries first: exchanges.country_id and companies.country_id both point at them.
    for c in missing.countries:
        db.add(Country(iso2=c.iso2, iso3=c.iso3, name=c.name, region=c.region,
                       currency=c.currency))
    db.flush()
    country_id_by_iso2 = {iso2: cid for cid, iso2 in db.query(Country.id, Country.iso2).all()}

    # 2. Exchanges, with a real venue name and IANA timezone rather than the MIC and "UTC".
    for e in missing.exchanges:
        country_id = country_id_by_iso2.get(e.country_iso2)
        if country_id is None:
            # Unreachable while app/engines/reference_data.py is self-consistent, and
            # tests/test_seed_reference_data.py asserts that it is. Explicit rather than an
            # IntegrityError on a NOT NULL column if that ever stops being true.
            raise RuntimeError(
                f"exchange {e.mic} needs country {e.country_iso2}, which is not in COUNTRIES"
            )
        db.add(Exchange(mic=e.mic, name=e.name, country_id=country_id, timezone=e.timezone))
    db.flush()

    # 3. Sectors, then industries (industries.sector_id points at sectors).
    for code, name in missing.sectors:
        db.add(Sector(code=code, name=name))
    db.flush()
    sector_id_by_code = {code: sid for sid, code in db.query(Sector.id, Sector.code).all()}
    for code, name, sector_code in missing.industries:
        db.add(Industry(code=code, name=name, sector_id=sector_id_by_code[sector_code]))

    db.commit()
    inserted = missing.counts()
    logger.info("seed.reference_data.complete", **inserted)
    return inserted


def seed_strategies(db) -> None:
    for key, strategy in BUILT_IN_STRATEGIES.items():
        if db.query(Screener).filter_by(key=key).first():
            continue
        db.add(Screener(key=key, name=strategy["name"], filter_json=strategy["filter_json"],
                         weights=strategy["weights"], is_system=True))
    db.commit()
    logger.info("seed.strategies.complete", count=len(BUILT_IN_STRATEGIES))


def seed_demo_universe(db) -> int:
    """Ingest and score every demo company. Returns the number that succeeded.

    AUDIT FIX (DEMO ingestion defect). The `except` branch used to log and continue **without
    rolling back**. A single failing ticker therefore left the session in a failed transaction,
    and SQLAlchemy raised `PendingRollbackError` on the very next statement — so one bad ticker
    did not cost one company, it cost all twenty, and the run ended with an empty database while
    the log showed nineteen separate "failed" lines that were all the same original error.

    `db.rollback()` restores the session, so the loop now genuinely continues. The failures are
    also counted and returned, because a seed that quietly ingests 3 of 20 and exits 0 is a worse
    outcome than one that says so.
    """
    adapter = DemoDataAdapter()
    succeeded = 0
    for profile in DEMO_SEED_PROFILES:
        try:
            security_id = ingest_security(db, adapter, profile.ticker)
            recompute_security(db, security_id)
            logger.info("seed.demo.ticker.complete", ticker=profile.ticker)
            succeeded += 1
        except Exception as e:
            # Without this the session stays poisoned and every later ticker fails too.
            db.rollback()
            logger.error("seed.demo.ticker.failed", ticker=profile.ticker,
                         error=str(e), error_type=type(e).__name__)
    return succeeded


def main() -> int:
    db = SessionLocal()
    try:
        reference = seed_reference_data(db)
        print(
            "Reference data: "
            + ", ".join(f"{table} +{n}" for table, n in reference.items())
            + "  (0 means the row was already present — the seed is idempotent)"
        )
        seed_strategies(db)
        succeeded = seed_demo_universe(db)
    finally:
        db.close()
    total = len(DEMO_SEED_PROFILES)
    print(f"Seeded {succeeded}/{total} DEMO companies and {len(BUILT_IN_STRATEGIES)} strategies.")
    print("Every row is tagged sources.is_demo = true — see docs/DATA_SOURCES.md §9.")
    if succeeded != total:
        # Exit non-zero so a container start-up or CI step that seeds cannot report success while
        # the database is partially or entirely empty. The per-ticker errors are in the log above.
        print(f"FAILED: {total - succeeded} of {total} DEMO companies did not ingest.")
        return 1
    return 0


if __name__ == "__main__":
    sys.exit(main())