"""Seed loader against the local database: idempotent upserts, aliases, relationships, discover queue (rows use `ztest-` slugs)."""
from __future__ import annotations

import json
from pathlib import Path

import pytest

from companyatlas.db import dispose, fetch_all, fetch_one, fetch_val, transaction
from companyatlas.registry.seed import add_company, normalise_row, seed, unique_slug

FIXTURE = [
    {"wikidata_id": "Q900000001", "display_name": "ZTest Alpha Corp", "legal_name": "ZTest Alpha Corporation", "aliases": ["ZTest Alpha", "Alpha Systems"],
     "website": "https://www.ztest-alpha.com/", "canonical_domain": "ztest-alpha.com", "country": "CA", "hq_city": "Montréal",
     "industries": ["software", "cloud-infrastructure"], "industry_labels": ["software industry"], "founded_year": 1999, "employees": 1200,
     "public_company": True, "ticker": "ZTA", "exchange": "Toronto Stock Exchange", "parent": None, "sitelinks": 40, "importance": 0.8, "tier": 1,
     "source": "wikidata", "harvested_at": "2026-09-12T00:00:00+00:00"},
    {"wikidata_id": "Q900000002", "display_name": "ZTest Beta", "website": "https://ztest-beta.com", "country": "US", "industries": ["fintech"],
     "parent": {"wikidata_id": "Q900000001", "name": "ZTest Alpha Corp"}, "importance": 0.4, "tier": 3, "source": "wikidata"},
    {"website": "ztest-gamma.com", "country": "QZ"},                                        # no name, unknown country → derived / nulled
    {"wikidata_id": "Q900000004", "display_name": "ZTest Alpha Duplicate", "website": "https://shop.ztest-alpha.com/x"},   # same domain → skipped
]


async def _cleanup() -> None:
    async with transaction() as conn:
        ids = [r["id"] for r in await fetch_all(conn, "select id from companies where slug like 'ztest-%'")]
        if ids:
            await conn.exec_driver_sql("delete from queue_jobs where key = any($1::text[])", ([f"discover:{i}" for i in ids],))
            await conn.exec_driver_sql("delete from companies where id = any($1::text[])", (ids,))


@pytest.fixture
def fixture_file(tmp_path: Path) -> Path:
    p = tmp_path / "ztest.ndjson"
    p.write_text("\n".join(json.dumps(r) for r in FIXTURE) + "\n", encoding="utf-8")
    return p


async def test_seed_twice_is_idempotent(fixture_file: Path) -> None:
    await _cleanup()
    try:
        async with transaction() as conn:
            first = await seed(conn, files=[fixture_file])
        assert first["industries"] >= 45 and first["countries"] >= 240
        assert first["companies_seen"] == 4 and first["companies_new"] == 3 and first["companies_skipped"] == 1
        assert first["relationships"] == 2 and first["queue_jobs"] == 3

        async with transaction() as conn:
            rows = await fetch_all(conn, "select * from companies where slug like 'ztest-%' order by slug")
            assert [r["slug"] for r in rows] == ["ztest-alpha-corp", "ztest-beta", "ztest-gamma"]
            alpha, beta, gamma = rows
            assert alpha["onboarding_status"] == "pending" and alpha["country"] == "CA" and alpha["industry_primary"] == "software"
            assert alpha["public_company"] is True and alpha["ticker"] == "ZTA" and alpha["tier"] == 1 and abs(alpha["importance"] - 0.8) < 1e-6
            assert alpha["source_meta"]["source"] == "wikidata" and alpha["source_meta"]["sitelinks"] == 40
            assert gamma["display_name"] == "Ztest Gamma" and gamma["country"] is None and gamma["source_meta"]["unknown_country"] == "QZ"
            aliases = await fetch_all(conn, "select alias_norm, kind from company_aliases where company_id = :id order by alias_norm", id=alpha["id"])
            # "ZTest Alpha Corp", "ZTest Alpha Corporation" and "ZTest Alpha" collapse to one key (legal suffixes dropped by normalize_alias)
            assert {a["alias_norm"] for a in aliases} == {"ztestalpha", "zta", "alphasystems"}
            assert {a["kind"] for a in aliases} >= {"brand", "ticker"}
            assert await fetch_val(conn, "select count(*) from domains where company_id = :id and kind = 'primary'", id=alpha["id"]) == 1
            rels = await fetch_all(conn, "select from_company_id, to_company_id, kind, confidence from company_relationships "
                                         "where from_company_id in (:a, :b) order by kind", a=alpha["id"], b=beta["id"])
            assert [(r["kind"], r["from_company_id"] == alpha["id"]) for r in rels] == [("PARENT_OF", True), ("SUBSIDIARY_OF", False)]
            assert all(abs(r["confidence"] - 0.8) < 1e-6 for r in rels)
            job = await fetch_one(conn, "select kind, status, priority, payload from queue_jobs where key = :k", k=f"discover:{alpha['id']}")
            assert job and job["kind"] == "discover" and job["status"] == "pending" and abs(job["priority"] - 0.8) < 1e-6
            assert job["payload"]["company_id"] == alpha["id"]
            assert (await fetch_one(conn, "select value from settings_kv where key = 'seed:last_run'"))["value"]["counters"]["companies_new"] == 3

        # Second run: nothing new, non-null values untouched even if the file changed, importance refreshed.
        changed = [dict(r) for r in FIXTURE]
        changed[0]["display_name"] = "RENAMED"
        changed[0]["legal_name"] = "Other Legal"
        changed[0]["importance"] = 0.5
        changed[0]["hq_region"] = "Quebec"      # was null → filled
        fixture_file.write_text("\n".join(json.dumps(r) for r in changed) + "\n", encoding="utf-8")
        async with transaction() as conn:
            second = await seed(conn, files=[fixture_file])
        assert second["companies_new"] == 0 and second["companies_updated"] == 3 and second["queue_jobs"] == 0 and second["relationships"] == 0
        async with transaction() as conn:
            alpha2 = await fetch_one(conn, "select * from companies where wikidata_id = 'Q900000001'")
            assert alpha2["display_name"] == "ZTest Alpha Corp" and alpha2["legal_name"] == "ZTest Alpha Corporation"
            assert alpha2["hq_region"] == "Quebec" and abs(alpha2["importance"] - 0.5) < 1e-6
            assert await fetch_val(conn, "select count(*) from companies where slug like 'ztest-%'") == 3
            assert await fetch_val(conn, "select count(*) from company_relationships r join companies c on c.id = r.from_company_id "
                                         "where c.slug like 'ztest-%'") == 2
            assert await fetch_val(conn, "select count(*) from queue_jobs where key like 'discover:%' and payload->>'company_id' in "
                                         "(select id from companies where slug like 'ztest-%')") == 3

        # add_company on an existing domain tops up; a new one is created pending with a job.
        async with transaction() as conn:
            res = await add_company(conn, "ztest-alpha.com", display_name="ignored")
            assert res["slug"] == "ztest-alpha-corp" and res["counters"]["companies_new"] == 0
            res = await add_company(conn, "https://ztest-delta.com/about", display_name="ZTest Delta", country="FR", industries=["banking"])
            assert res["slug"] == "ztest-delta" and res["onboarding_status"] == "pending" and res["counters"]["queue_jobs"] == 1
    finally:
        await _cleanup()
        await dispose()


def test_normalise_and_slug_helpers() -> None:
    assert normalise_row({"website": ""}, source="x") is None
    assert normalise_row({"website": "ftp://nope"}, source="x") is None
    row = normalise_row({"website": "example.org", "industries": ["software", "not-a-slug"], "industry": "banking", "tier": 9, "importance": 3},
                        source="manual")
    assert row is not None
    assert row["website"] == "https://example.org" and row["canonical_domain"] == "example.org" and row["display_name"] == "Example"
    assert row["industries"] == ["banking", "software"] and row["tier"] == 4 and row["importance"] == 1.0
    assert unique_slug("acme", "US", set()) == "acme"
    assert unique_slug("acme", "US", {"acme"}) == "acme-us"
    assert unique_slug("acme", "US", {"acme", "acme-us"}) == "acme-2"
    assert unique_slug("acme", None, {"acme", "acme-2"}) == "acme-3"