"""Joins: a dataset that joins other sources' records onto each row — a view
over several sources (app/crud/dataset_query.py, _apply_joins)."""

import uuid

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


async def _company(tenant_id):
    """Departments ← Employees (by department id) ← Timesheets (by employee code)."""
    async with AsyncSessionLocal() as db:
        async with db.begin():
            depts = Master(tenant_id=tenant_id, name=f"Depts {uuid.uuid4().hex[:6]}", field_schema=[{"name": "dept_name", "type": "text"}])
            db.add(depts)
            await db.flush()
            ops = MasterRecord(tenant_id=tenant_id, master_id=depts.id, field_values={"dept_name": "Operations"})
            db.add(ops)
            await db.flush()
            emps = Master(
                tenant_id=tenant_id,
                name=f"Employees {uuid.uuid4().hex[:6]}",
                field_schema=[
                    {"name": "code", "type": "text"},
                    {"name": "full_name", "type": "text"},
                    {"name": "rate", "type": "number"},
                    {"name": "dept", "type": "reference", "reference_master_id": str(depts.id), "reference_display_field": "dept_name"},
                ],
            )
            db.add(emps)
            await db.flush()
            db.add_all(
                [
                    MasterRecord(tenant_id=tenant_id, master_id=emps.id, field_values={"code": "E1", "full_name": "Asha", "rate": 100, "dept": str(ops.id)}),
                    MasterRecord(tenant_id=tenant_id, master_id=emps.id, field_values={"code": "E2", "full_name": "Ben", "rate": 80}),
                ]
            )
            sheets = Master(tenant_id=tenant_id, name=f"Timesheets {uuid.uuid4().hex[:6]}", field_schema=[{"name": "emp_code", "type": "text"}, {"name": "hours", "type": "number"}])
            db.add(sheets)
            await db.flush()
            for code, hours in [(" e1 ", 5), ("E2", 3), ("E9", 2), ("E1", 4)]:  # case and spaces don't matter; E9 has no employee
                db.add(MasterRecord(tenant_id=tenant_id, master_id=sheets.id, field_values={"emp_code": code, "hours": hours}))
            return depts.id, emps.id, sheets.id


def _join(emps_id, **kw):
    return {"alias": "emp", "source_type": "master", "source_id": str(emps_id), "on_field": "emp_code", "join_field": "code", "columns": ["full_name", "rate", "dept"], **kw}


async def test_joins(test_tenant):
    tenant_id = test_tenant["tenant_id"]
    depts_id, emps_id, sheets_id = await _company(tenant_id)

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

    # Left join: every timesheet, the employee's fields where one matches; a
    # linked field shows its label.
    got, total, _ = await rows([_join(emps_id)], columns=["hours", "emp.full_name", "emp.rate", "emp.dept"])
    assert total == 4
    assert [(r["hours"], r["emp.full_name"], r["emp.dept"]) for r in got] == [(2, None, None), (3, "Ben", None), (4, "Asha", "Operations"), (5, "Asha", "Operations")]

    # Inner join drops the timesheet with no employee.
    got, total, _ = await rows([_join(emps_id, type="inner")], columns=["hours"])
    assert total == 3

    # Filter, sort and group by a joined column; a calculated column uses one.
    got, _, _ = await rows([_join(emps_id)], columns=["hours"], filters=[{"field": "emp.full_name", "operator": "equals", "value": "Asha"}])
    assert sorted(r["hours"] for r in got) == [4, 5]
    grouped, _, cols = await rows([_join(emps_id, type="inner")], group_by="emp.full_name", aggregate_fn="sum", aggregate_field="hours")
    assert {r["emp.full_name"]: r[cols[1]] for r in grouped} == {"Asha": 9, "Ben": 3}
    pay, _, _ = await rows([_join(emps_id, type="inner")], columns=["hours", "pay"], computed=[{"name": "pay", "expression": "{hours} * {emp.rate}"}])
    assert sorted(r["pay"] for r in pay) == [240, 400, 500]

    # A second join can match on the first's column — Timesheet → Employee → Department.
    # Joined link columns are labelled only after every join, so the next
    # join matches emp.dept's id while the row still shows "Operations".
    chained = [
        _join(emps_id, columns=["full_name", "dept"]),
        {"alias": "emp_dept", "source_type": "master", "source_id": str(depts_id), "on_field": "emp.dept", "join_field": "id", "columns": ["dept_name"]},
    ]
    got, _, _ = await rows(chained, columns=["hours", "emp.full_name", "emp.dept", "emp_dept.dept_name"])
    assert {(r["emp.full_name"], r["emp.dept"], r["emp_dept.dept_name"]) for r in got} == {(None, None, None), ("Ben", None, None), ("Asha", "Operations", "Operations")}


async def test_match_all_repeats_rows(test_tenant):
    tenant_id = test_tenant["tenant_id"]
    _, emps_id, sheets_id = await _company(tenant_id)
    # From employees: every timesheet of each — Asha twice, Ben once, nobody else.
    join = {"alias": "sheet", "source_type": "master", "source_id": str(sheets_id), "on_field": "code", "join_field": "emp_code", "columns": ["hours"], "match": "all", "type": "inner"}
    async with AsyncSessionLocal() as db:
        got, total, _ = await dataset_query.resolve_rows(db, tenant_id, "master", emps_id, [], "full_name", "asc", ["full_name", "sheet.hours"], joins=[join])
    assert total == 3
    assert sorted((r["full_name"], r["sheet.hours"]) for r in got) == [("Asha", 4), ("Asha", 5), ("Ben", 3)]


async def test_a_saved_dataset_with_a_join(client, auth_headers, test_tenant):
    _, emps_id, sheets_id = await _company(test_tenant["tenant_id"])
    join = _join(emps_id, type="inner", columns=["full_name"])
    resp = await client.post(
        "/datasets",
        headers=auth_headers,
        json={"name": "Hours by person", "source_type": "master", "source_id": str(sheets_id), "columns": ["emp.full_name", "hours"], "joins": [join], "sort_field": "hours"},
    )
    assert resp.status_code == 201, resp.text
    assert resp.json()["joins"][0]["alias"] == "emp"
    data = (await client.get(f"/datasets/{resp.json()['id']}/data", headers=auth_headers)).json()
    assert [r["emp.full_name"] for r in data["rows"]] == ["Ben", "Asha", "Asha"]
    # A join's name can't be one of the source's own fields.
    clash = await client.post(
        "/datasets/query", headers=auth_headers, json={"source_type": "master", "source_id": str(sheets_id), "joins": [{**join, "alias": "hours"}]}
    )
    assert clash.status_code == 422