"""Rollup columns: values from the records that link TO each row — an
application's reply count, its latest reply's date, the sum of an order's
lines — usable as columns, filters, sorts and groups."""

import uuid
from datetime import datetime, timedelta, timezone

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


async def _orders_with_lines(tenant_id):
    async with AsyncSessionLocal() as db:
        async with db.begin():
            orders = Master(tenant_id=tenant_id, name=f"Orders {uuid.uuid4().hex[:6]}", field_schema=[{"name": "no", "type": "text"}])
            db.add(orders)
            await db.flush()
            ids = {}
            for no in ["O1", "O2", "O3"]:
                rec = MasterRecord(tenant_id=tenant_id, master_id=orders.id, field_values={"no": no})
                db.add(rec)
                await db.flush()
                ids[no] = str(rec.id)
            lines = Master(
                tenant_id=tenant_id,
                name=f"Lines {uuid.uuid4().hex[:6]}",
                field_schema=[
                    {"name": "item", "type": "text"},
                    {"name": "amount", "type": "number"},
                    {"name": "order", "type": "reference", "reference_master_id": str(orders.id), "reference_display_field": "no"},
                ],
            )
            db.add(lines)
            await db.flush()
            start = datetime(2026, 10, 1, tzinfo=timezone.utc)
            for i, (item, amount, order) in enumerate([("pen", 10, "O1"), ("ink", 5, "O1"), ("pad", 7, "O2"), ("pen", 3, "O1")]):
                db.add(MasterRecord(tenant_id=tenant_id, master_id=lines.id, field_values={"item": item, "amount": amount, "order": ids[order]}, created_at=start + timedelta(days=i)))
            return orders.id, lines.id


async def test_rollup_columns(test_tenant):
    tenant_id = test_tenant["tenant_id"]
    orders_id, lines_id = await _orders_with_lines(tenant_id)
    base = f"master:{lines_id}:order"
    count, total, latest, items, biggest = (
        f"rollup:count:{base}",
        f"rollup:sum:{base}:amount",
        f"rollup:latest:{base}:item",
        f"rollup:list:{base}:item",
        f"rollup:max:{base}:amount",
    )

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

    got, _, cols = await rows(columns=["no", count, total, latest, items, biggest])
    assert cols == ["no", count, total, latest, items, biggest]
    assert [(r["no"], r[count], r[total], r[latest], r[items], r[biggest]) for r in got] == [
        ("O1", 3, 18, "pen", "pen, ink", 10),
        ("O2", 1, 7, "pad", "pad", 7),
        ("O3", 0, 0, None, None, None),  # no lines
    ]
    # Filter, sort and group by a rollup.
    busy, total_rows, _ = await rows(columns=["no"], filters=[{"field": count, "operator": "gte", "value": 1}], sort_field=total, sort_dir="desc")
    assert [r["no"] for r in busy] == ["O1", "O2"] and total_rows == 2
    grouped, _, gcols = await rows(group_by=count)
    assert {r[count]: r[gcols[1]] for r in grouped} == {"3": 1, "1": 1, "0": 1}
    # A calculated column can use one.
    with_avg, _, _ = await rows(columns=["no", "avg_line"], computed=[{"name": "avg_line", "expression": f"{{{count}}} > 0 ? {{{total}}} / {{{count}}} : 0"}])
    assert [r["avg_line"] for r in with_avg] == [6, 7, 0]


def test_rollup_names():
    child = uuid.uuid4()
    assert parse_rollup(f"rollup:count:form:{child}:application_id") == ("count", "form", child, "application_id", None)
    assert parse_rollup(f"rollup:sum:master:{child}:order:amount") == ("sum", "master", child, "order", "amount")
    assert parse_rollup(f"rollup:sum:master:{child}:order") is None  # sum needs a field
    assert parse_rollup("rollup:count:table:x:y") is None
    assert parse_rollup("customer.city") is None