"""Lookup columns: "<link field>.<field>" reads a field of the record a link
points at (many-to-one) — selectable, filterable, sortable and groupable
like the source's own columns, for Masters' reference fields and Forms'
datasource-linked choice fields."""

from app.crud import dataset_query
from tests.conftest import AsyncSessionLocal
from app.models.master import Master, MasterRecord


async def _orders(tenant_id):
    async with AsyncSessionLocal() as db:
        async with db.begin():
            customers = Master(tenant_id=tenant_id, name="Customers", field_schema=[{"name": "name", "type": "text"}, {"name": "city", "type": "text"}])
            db.add(customers)
            await db.flush()
            ids = {}
            for name, city in [("Asha", "Pune"), ("Ben", "Kochi"), ("Chen", "Pune")]:
                rec = MasterRecord(tenant_id=tenant_id, master_id=customers.id, field_values={"name": name, "city": city})
                db.add(rec)
                await db.flush()
                ids[name] = str(rec.id)
            orders = Master(
                tenant_id=tenant_id,
                name="Orders",
                field_schema=[
                    {"name": "no", "type": "text"},
                    {"name": "amount", "type": "number"},
                    {"name": "customer", "type": "reference", "reference_master_id": str(customers.id), "reference_display_field": "name"},
                    {"name": "watchers", "type": "reference", "reference_master_id": str(customers.id), "reference_display_field": "name", "multi": True},
                ],
            )
            db.add(orders)
            await db.flush()
            for no, amount, who, watchers in [("O1", 10, "Asha", ["Ben"]), ("O2", 20, "Ben", ["Asha", "Chen"]), ("O3", 5, "Chen", []), ("O4", 7, None, [])]:
                values = {"no": no, "amount": amount, "watchers": [ids[w] for w in watchers]}
                if who:
                    values["customer"] = ids[who]
                db.add(MasterRecord(tenant_id=tenant_id, master_id=orders.id, field_values=values))
            return customers, orders, ids


async def test_lookup_columns(test_tenant):
    tenant_id = test_tenant["tenant_id"]
    _, orders, _ = await _orders(tenant_id)

    async def rows(**kw):
        async with AsyncSessionLocal() as db:
            async with db.begin():
                return await dataset_query.resolve_rows(
                    db, tenant_id, "master", orders.id, kw.pop("filters", []), kw.pop("sort_field", None), kw.pop("sort_dir", "asc"), kw.pop("columns", []), **kw
                )

    got, total, cols = await rows(columns=["no", "customer", "customer.city", "watchers.name"], sort_field="no")
    assert cols == ["no", "customer", "customer.city", "watchers.name"] and total == 4
    by_no = {r["no"]: r for r in got}
    assert by_no["O1"]["customer"] == "Asha" and by_no["O1"]["customer.city"] == "Pune"  # label and lookup side by side
    assert by_no["O2"]["watchers.name"] == "Asha, Chen"
    assert by_no["O4"]["customer.city"] is None

    pune, total, _ = await rows(columns=["no"], filters=[{"field": "customer.city", "operator": "equals", "value": "Pune"}], sort_field="customer.city")
    assert sorted(r["no"] for r in pune) == ["O1", "O3"] and total == 2

    grouped, _, cols = await rows(group_by="customer.city", aggregate_fn="sum", aggregate_field="amount")
    sums = {r["customer.city"]: r[cols[1]] for r in grouped}
    assert sums["Pune"] == 15 and sums["Kochi"] == 20

    async with AsyncSessionLocal() as db:
        async with db.begin():
            series, _ = await dataset_query.resolve_grouped_series(db, tenant_id, "master", orders.id, [], "customer.city", "none", [{"aggregate": "count", "label": "n"}])
    assert {r["x"]: r["n"] for r in series} == {"Pune": 2, "Kochi": 1, "(empty)": 1}


async def test_lookup_through_a_form_choice_field(client, auth_headers, test_tenant):
    tenant_id = test_tenant["tenant_id"]
    customers, _, ids = await _orders(tenant_id)
    form = (
        await client.post(
            "/forms",
            headers=auth_headers,
            json={"name": "Visit", "field_definitions": [{"name": "who", "type": "dropdown", "label": "Who", "master_id": str(customers.id), "master_display_field": "name"}]},
        )
    ).json()
    await client.post(f"/forms/{form['form_id']}/publish", headers=auth_headers)
    sub = await client.post("/form-submissions", headers=auth_headers, json={"form_id": form["form_id"], "form_version": 1, "field_values": {"who": ids["Ben"]}})
    assert sub.status_code == 201, sub.text
    res = await client.post("/datasets/query", headers=auth_headers, json={"source_type": "form", "source_id": form["form_id"], "columns": ["who", "who.city"]})
    assert res.status_code == 200, res.text
    assert res.json()["rows"][0]["who"] == "Ben" and res.json()["rows"][0]["who.city"] == "Kochi"


async def test_lookup_of_a_linked_field_shows_its_label(test_tenant):
    """orders.customer.region where region is itself a link: the region's name, not its id."""
    tenant_id = test_tenant["tenant_id"]
    async with AsyncSessionLocal() as db:
        async with db.begin():
            regions = Master(tenant_id=tenant_id, name="Regions", field_schema=[{"name": "title", "type": "text"}])
            db.add(regions)
            await db.flush()
            south = MasterRecord(tenant_id=tenant_id, master_id=regions.id, field_values={"title": "South"})
            db.add(south)
            await db.flush()
            customers = Master(tenant_id=tenant_id, name="Customers", field_schema=[
                {"name": "name", "type": "text"},
                {"name": "region", "type": "reference", "reference_master_id": str(regions.id), "reference_display_field": "title"},
            ])
            db.add(customers)
            await db.flush()
            asha = MasterRecord(tenant_id=tenant_id, master_id=customers.id, field_values={"name": "Asha", "region": str(south.id)})
            db.add(asha)
            await db.flush()
            orders = Master(tenant_id=tenant_id, name="Orders", field_schema=[
                {"name": "no", "type": "text"},
                {"name": "customer", "type": "reference", "reference_master_id": str(customers.id), "reference_display_field": "name"},
            ])
            db.add(orders)
            await db.flush()
            db.add(MasterRecord(tenant_id=tenant_id, master_id=orders.id, field_values={"no": "O1", "customer": str(asha.id)}))
    async with AsyncSessionLocal() as db:
        rows, _, _ = await dataset_query.resolve_rows(db, tenant_id, "master", orders.id, [], None, "asc", ["no", "customer.region"])
    assert rows[0]["customer.region"] == "South"


async def test_lookups_follow_several_links(test_tenant):
    """"customer.region.manager" — Order → Customer → Region, read one hop at
    a time; the last hop's link shows its record's label."""
    tenant_id = test_tenant["tenant_id"]
    async with AsyncSessionLocal() as db:
        async with db.begin():
            people = Master(tenant_id=tenant_id, name="People", field_schema=[{"name": "full_name", "type": "text"}])
            db.add(people)
            await db.flush()
            meera = MasterRecord(tenant_id=tenant_id, master_id=people.id, field_values={"full_name": "Meera"})
            db.add(meera)
            await db.flush()
            regions = Master(
                tenant_id=tenant_id,
                name="Regions",
                field_schema=[{"name": "region", "type": "text"}, {"name": "manager", "type": "reference", "reference_master_id": str(people.id), "reference_display_field": "full_name"}],
            )
            db.add(regions)
            await db.flush()
            south = MasterRecord(tenant_id=tenant_id, master_id=regions.id, field_values={"region": "South", "manager": str(meera.id)})
            db.add(south)
            await db.flush()
            customers = Master(
                tenant_id=tenant_id,
                name="Customers 2",
                field_schema=[{"name": "name", "type": "text"}, {"name": "region", "type": "reference", "reference_master_id": str(regions.id), "reference_display_field": "region"}],
            )
            db.add(customers)
            await db.flush()
            asha = MasterRecord(tenant_id=tenant_id, master_id=customers.id, field_values={"name": "Asha", "region": str(south.id)})
            ben = MasterRecord(tenant_id=tenant_id, master_id=customers.id, field_values={"name": "Ben"})
            db.add_all([asha, ben])
            await db.flush()
            orders = Master(
                tenant_id=tenant_id,
                name="Orders 2",
                field_schema=[{"name": "no", "type": "text"}, {"name": "customer", "type": "reference", "reference_master_id": str(customers.id), "reference_display_field": "name"}],
            )
            db.add(orders)
            await db.flush()
            db.add_all(
                [
                    MasterRecord(tenant_id=tenant_id, master_id=orders.id, field_values={"no": "O1", "customer": str(asha.id)}),
                    MasterRecord(tenant_id=tenant_id, master_id=orders.id, field_values={"no": "O2", "customer": str(ben.id)}),
                ]
            )

    async def rows(filters=(), columns=("no", "customer.region.region", "customer.region.manager", "customer.region.manager.full_name")):
        async with AsyncSessionLocal() as db:
            got, _, _ = await dataset_query.resolve_rows(db, tenant_id, "master", orders.id, list(filters), "no", "asc", list(columns))
        return got

    o1, o2 = await rows()
    assert o1["customer.region.region"] == "South"
    assert o1["customer.region.manager"] == "Meera"  # a link at the end shows its label
    assert o1["customer.region.manager.full_name"] == "Meera"  # three hops
    assert o2["customer.region.region"] is None  # Ben has no region
    assert [r["no"] for r in await rows([{"field": "customer.region.region", "operator": "equals", "value": "South"}], ["no"])] == ["O1"]