"""People/Organizations subtype tables + the 1:1 invariant (ADR #012/#013).

The refactor is additive: parties is unchanged, so the harness parity test
(test_harness_parity.py) proves behavior is preserved. These tests prove
the new structure: the tables exist, every party has its detail row after a
clean transform run, no orphans, and the cascade keeps rebuilds clean.
"""

from sqlalchemy import inspect, text


def test_subtype_tables_exist(mig_engine):
    tables = set(inspect(mig_engine).get_table_names(schema="public"))
    assert {"organizations", "people", "party_affiliations"} <= tables


def test_organizations_columns(mig_engine):
    cols = {c["name"] for c in inspect(mig_engine).get_columns("organizations")}
    assert {"party_id", "legal_name", "currency", "drive_folder_url",
            "mosiah_folder"} <= cols


def test_one_to_one_invariant_after_clean_run(mig_engine, clean_report):
    """Every org party has exactly one organizations row and every person
    party exactly one people row — no orphans, no missing, both directions."""
    with mig_engine.connect() as conn:
        org_parties = conn.execute(
            text("SELECT count(*) FROM parties WHERE kind = 'org'")
        ).scalar()
        org_rows = conn.execute(text("SELECT count(*) FROM organizations")).scalar()
        person_parties = conn.execute(
            text("SELECT count(*) FROM parties WHERE kind = 'person'")
        ).scalar()
        person_rows = conn.execute(text("SELECT count(*) FROM people")).scalar()

        # No detail row points at a wrong-kind (or missing) party.
        bad_org = conn.execute(
            text(
                "SELECT count(*) FROM organizations o"
                " LEFT JOIN parties p ON p.id = o.party_id"
                " WHERE p.id IS NULL OR p.kind <> 'org'"
            )
        ).scalar()
        bad_person = conn.execute(
            text(
                "SELECT count(*) FROM people pe"
                " LEFT JOIN parties p ON p.id = pe.party_id"
                " WHERE p.id IS NULL OR p.kind <> 'person'"
            )
        ).scalar()

    assert org_parties > 0 and person_parties > 0
    assert org_rows == org_parties, "every org party needs one organizations row"
    assert person_rows == person_parties, "every person party needs one people row"
    assert bad_org == 0 and bad_person == 0


def test_cascade_removes_detail_on_party_delete(mig_engine, reload_fixture):
    """DELETE FROM parties cascades to the detail tables — the rebuild
    transform relies on this (it never adds these to its TRUNCATE list)."""
    reload_fixture()
    from app.services.migrate import run_all

    assert run_all(engine=mig_engine).ok
    with mig_engine.begin() as conn:
        # a fresh rebuild deletes then re-inserts; assert no orphans linger
        orphans = conn.execute(
            text(
                "SELECT (SELECT count(*) FROM organizations o"
                "        WHERE NOT EXISTS (SELECT 1 FROM parties p WHERE p.id = o.party_id))"
                "     + (SELECT count(*) FROM people pe"
                "        WHERE NOT EXISTS (SELECT 1 FROM parties p WHERE p.id = pe.party_id))"
            )
        ).scalar()
    assert orphans == 0