"""The catalogue the featured suites share (Stream AW2.2): two airports and an online
catalogue, a picture at every level, a hidden line, a merged variant, the ledger tables.
SQLite in memory, no network. Imported by `test_featured_records.py` and `test_featured_pins.py`."""
from __future__ import annotations

from datetime import UTC, datetime, timedelta

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.pool import StaticPool

from app.models import (Account, AttributeAlias, Award, Base, Brand, CollectionRun, LEDGER_TABLES, Listing, Merge, PriceObservation,
                        ProductLine, ProductVariant, Retailer, Shop, Source, Suggestion)
from app.models.places import Place, ShopPlace
from app.models.quality import VerificationCheck
from app.services import imagery, keying
from tests.conftest import forbid_ledger_updates

TABLES = [Account.__table__, Source.__table__, CollectionRun.__table__, Brand.__table__, ProductLine.__table__, ProductVariant.__table__,
          AttributeAlias.__table__, Award.__table__, Retailer.__table__, Shop.__table__, Listing.__table__, PriceObservation.__table__,
          Merge.__table__, Suggestion.__table__, Place.__table__, ShopPlace.__table__, VerificationCheck.__table__, *LEDGER_TABLES]
NOW = datetime(2026, 9, 19, 12, 0, tzinfo=UTC)
RIAN = 1
LHR, CDG, ONLINE = 1, 2, 3
ADMIN = "admin:pernod-ricard-via-adam"


def price(s, variant_id: int, shop_id: int, usd: float, *, days_ago: int = 0, in_stock: bool | None = None) -> None:
    listing = s.query(Listing).filter_by(variant_id=variant_id, shop_id=shop_id).one_or_none()
    if listing is None:
        listing = Listing(variant_id=variant_id, shop_id=shop_id, source_sku=f"v{variant_id}s{shop_id}", listed_name="x")
        s.add(listing); s.flush()
    s.add(PriceObservation(listing_id=listing.id, price=usd, currency="USD", price_usd=usd, in_stock=in_stock,
                           observed_at=NOW - timedelta(days=days_ago)))


def variant(s, id_: int, brand_id: int, line_id: int | None, name: str, **kw) -> ProductVariant:
    v = ProductVariant(id=id_, name=name, brand="B", brand_id=brand_id, vertical="liquor", category=kw.pop("category", "Whisky"),
                       match_key=f"k{id_}", product_line_id=line_id, form="single", attributes={},
                       quantity_value=kw.pop("quantity_value", 700), quantity_unit="ml", quantity_ml=700, **kw)
    s.add(v); s.flush()
    return v


def build():
    """A session over the kit. Prices: every compared variant at LHR and CDG, the saving is the
    id in dollars (v1 saves $1 of $101 ... v7 saves $7 of $107, so the order is readable)."""
    engine = create_engine("sqlite://", connect_args={"check_same_thread": False}, poolclass=StaticPool)
    Base.metadata.create_all(engine, tables=TABLES)
    forbid_ledger_updates(engine)
    keying.invalidate()
    s = sessionmaker(bind=engine, autoflush=False, expire_on_commit=False)()
    s.add(Account(id=RIAN, username="rian", display_name="rian"))
    s.add(Account(id=2, username="adi", display_name="adi"))
    s.add(Retailer(id=1, slug="r", name="R")); s.flush()
    s.add_all([
        Brand(id=1, slug="glen", name="Glen", image_url="https://off.example/glen.jpg", image_source=imagery.PUBLIC_COMMONS, image_level="brand"),
        Brand(id=2, slug="house", name="House", image_url="/uploads/images/brand/house.webp", image_source=ADMIN, image_level="brand"),
        Brand(id=3, slug="bare", name="Bare"),
    ])
    s.flush()
    s.add_all([
        Shop(id=LHR, retailer_id=1, code="LHR-T2", iata="LHR", name="Heathrow T2", city="London", currency="GBP", visible=True),
        Shop(id=CDG, retailer_id=1, code="CDG", iata="CDG", name="Paris CDG", city="Paris", currency="EUR", visible=True),
        Shop(id=ONLINE, retailer_id=1, code="WEB", iata=None, name="The catalogue", city="Online", currency="USD", visible=True,
             is_catalogue_only=True),
    ])
    s.add_all([Place(id=1, slug="lhr-london", kind="airport", name="Heathrow"), Place(id=2, slug="cdg-paris", kind="airport", name="CDG")])
    s.flush()
    s.add_all([ShopPlace(shop_id=LHR, place_id=1, role="primary"), ShopPlace(shop_id=CDG, place_id=2, role="primary")])
    s.add_all([
        ProductLine(id=11, brand_id=1, key="public-line", slug="glen-public", name="Public line",
                    image_url="https://off.example/line.jpg", image_source=imagery.PUBLIC_OFF_BARCODE, image_level="line"),
        ProductLine(id=12, brand_id=1, key="bare-line", slug="glen-bare", name="Bare line"),
        ProductLine(id=13, brand_id=2, key="admin-line", slug="house-admin", name="Admin line",
                    image_url="/uploads/images/line/house-admin.webp", image_source=ADMIN, image_level="line"),
        ProductLine(id=14, brand_id=3, key="nothing", slug="bare-nothing", name="Nothing"),
        ProductLine(id=15, brand_id=3, key="hidden", slug="bare-hidden", name="Hidden line", hidden=True),
        ProductLine(id=16, brand_id=3, key="other", slug="bare-other", name="Other"),
        ProductLine(id=17, brand_id=2, key="house-bare", slug="house-bare", name="House bare"),
    ])
    s.flush()
    variant(s, 1, 1, 12, "Tier one (brand mark)")
    variant(s, 2, 1, 11, "Tier two (public line)")
    variant(s, 3, 1, 12, "Tier three (own public photo)", image_url="https://off.example/3.jpg", thumb_url="https://off.example/3-t.jpg",
            image_source=imagery.PUBLIC_OFF_BARCODE, image_level="variant")
    variant(s, 4, 2, 17, "Tier four (admin brand mark)")
    variant(s, 5, 2, 13, "Tier five (admin line)")
    variant(s, 6, 2, 17, "Tier six (own admin photo)", image_url="/uploads/images/variant/6.webp", thumb_url="/uploads/images/variant/6-t.webp",
            image_source=ADMIN, image_level="variant")
    variant(s, 7, 3, 14, "Tier none")
    for vid in range(1, 8):
        price(s, vid, LHR, 100.0)
        price(s, vid, CDG, 100.0 + vid)
    variant(s, 8, 3, 16, "One airport and the catalogue")
    price(s, 8, LHR, 50.0); price(s, 8, ONLINE, 80.0)
    variant(s, 9, 3, 15, "On the hidden line")
    price(s, 9, LHR, 20.0); price(s, 9, CDG, 30.0)
    s.add(Award(variant_id=3, competition_slug="iwsc", competition="IWSC", medal="Gold", year=2025))
    s.commit()
    return s