"""NetSuite GL inbound coordinator — pure mapping / FX / period resolution
/ idempotent replace / graceful degradation.

The live NetSuite SuiteQL surface can't be exercised from CI (no creds)
and Helix never tests against production, so these pin the LOGIC the
coordinator owns: SuiteQL summary-row → GL-actual mapping, USD conversion
at the month-frozen rate, accounting-period id resolution + closed flag,
the deterministic grain key, idempotent replace per (source, period), and
graceful skip when no connector. The exact vendor column shape is
validated live by the admin gl-probe endpoint before the cron is enabled.
"""
from __future__ import annotations

from decimal import Decimal

import pytest
import pytest_asyncio
from sqlalchemy import select

from app.core.coordinators import finance_netsuite_coordinator as ns
from app.domains.finance.fx import FxResolution, upsert_monthly_rate
from app.domains.finance.models import (
    FinanceGLActual,
    FinanceNetSuiteAccountingPeriod,
    FinanceNetSuiteSubsidiary,
)

PERIOD = "2026-05"
CNY_RATE = Decimal("0.14")


# ─── Pure mappers (no DB / no network) ──────────────────────────────


def _fx_cny():
    return FxResolution(usd_per_unit=CNY_RATE, fx_basis="month_frozen", frozen=True)


def test_map_gl_rows_converts_and_keys():
    rows = [
        {"account_id": "1", "account_number": "5000", "account_type": "Expense",
         "account_name": "Travel", "subsidiary_id": "10", "department_id": "3",
         "department_name": "R&D", "amount": "1000.00"},
        # zero-movement grain → dropped
        {"account_id": "2", "account_number": "5001", "subsidiary_id": "10",
         "amount": "0"},
    ]
    out = ns.map_gl_rows_to_actuals(
        rows, period=PERIOD, period_closed=True,
        subsidiary_currency={"10": "CNY"}, fx_for_currency=lambda c: _fx_cny(),
    )
    assert len(out) == 1
    r = out[0]
    assert r["amount_native"] == Decimal("1000.00")
    assert r["currency"] == "CNY"
    assert r["amount_usd"] == Decimal("140.00")  # 1000 × 0.14
    assert r["fx_basis"] == "month_frozen"
    assert r["is_closed"] is True
    assert r["account_type"] == "Expense"
    # Deterministic, grain-stable key.
    assert r["gl_key"] == ns.gl_key(PERIOD, "10", "1", "3")


def test_map_gl_rows_negative_amount_kept():
    # Credit movements (negative) are faithful GL, never dropped.
    out = ns.map_gl_rows_to_actuals(
        [{"account_id": "9", "subsidiary_id": "10", "amount": "-250.50"}],
        period=PERIOD, period_closed=False,
        subsidiary_currency={"10": "USD"},
        fx_for_currency=lambda c: FxResolution(
            usd_per_unit=Decimal("1"), fx_basis="month_frozen", frozen=True),
    )
    assert len(out) == 1
    assert out[0]["amount_usd"] == Decimal("-250.50")
    assert out[0]["department_id"] is None  # missing dept → NULL, not ""


def test_map_gl_rows_aggregates_multiple_posting_periods_per_month():
    # One Helix month can map to several NetSuite posting periods (base
    # monthly + year-end adjustment periods). The SuiteQL groups by
    # ``postingperiod`` so the same grain returns one row per posting
    # period; ``gl_key`` is grain-only, so without aggregation these collide
    # and the bare insert loop would trip ``uq_finance_gl_key`` and abort the
    # whole pull. They must be summed into a single grain row.
    rows = [
        {"account_id": "1", "subsidiary_id": "10", "department_id": "3",
         "account_number": "5000", "amount": "1000.00"},   # base period
        {"account_id": "1", "subsidiary_id": "10", "department_id": "3",
         "account_number": "5000", "amount": "250.00"},     # adjustment period
        # A different grain in the same month stays separate.
        {"account_id": "2", "subsidiary_id": "10", "department_id": "3",
         "amount": "40.00"},
    ]
    out = ns.map_gl_rows_to_actuals(
        rows, period=PERIOD, period_closed=False,
        subsidiary_currency={"10": "CNY"}, fx_for_currency=lambda c: _fx_cny(),
    )
    by_key = {r["gl_key"]: r for r in out}
    assert len(out) == 2
    merged = by_key[ns.gl_key(PERIOD, "10", "1", "3")]
    assert merged["amount_native"] == Decimal("1250.00")  # 1000 + 250
    assert merged["amount_usd"] == Decimal("175.00")       # 1250 × 0.14


def test_map_gl_rows_drops_grain_that_nets_to_zero():
    # A base posting and an equal-and-opposite adjustment net to no movement
    # → the merged grain is dropped (mirrors the per-row zero skip).
    rows = [
        {"account_id": "1", "subsidiary_id": "10", "amount": "500.00"},
        {"account_id": "1", "subsidiary_id": "10", "amount": "-500.00"},
    ]
    out = ns.map_gl_rows_to_actuals(
        rows, period=PERIOD, period_closed=False,
        subsidiary_currency={"10": "USD"},
        fx_for_currency=lambda c: FxResolution(
            usd_per_unit=Decimal("1"), fx_basis="month_frozen", frozen=True),
    )
    assert out == []


def test_period_from_date_variants():
    assert ns._period_from_date("2026-05-01") == "2026-05"      # ISO
    assert ns._period_from_date("2026/05/31") == "2026-05"      # ISO slashes
    assert ns._period_from_date("20260501") == "2026-05"        # compact
    # US MM/DD/YYYY — NetSuite SuiteQL's ACTUAL startdate format (verified
    # live 2026-06-28). Year is the LAST token; must NOT be read year-first.
    assert ns._period_from_date("12/01/2029") == "2029-12"
    assert ns._period_from_date("5/1/2026") == "2026-05"
    assert ns._period_from_date("06/30/2026") == "2026-06"
    assert ns._period_from_date(None) is None
    assert ns._period_from_date("garbage") is None


def test_gl_suiteql_aggregates_builtin_df():
    # A GROUP BY SuiteQL that SELECTs a bare ``BUILTIN.DF(col)`` not present in
    # the GROUP BY is rejected by NetSuite with HTTP 400 "Invalid or
    # unsupported search" (verified live against prod 2026-06-28 — this left
    # finance_gl_actuals empty). Every BUILTIN.DF in the GL query MUST be
    # wrapped in an aggregate (MAX) so the query is valid. Guard against
    # regressing to the bare form.
    q = ns._DEFAULT_GL_SUITEQL
    assert "BUILTIN.DF" in q  # we still want the descriptive name columns
    # No bare ``BUILTIN.DF(`` may appear except immediately inside ``MAX(``.
    idx = 0
    while True:
        idx = q.find("BUILTIN.DF(", idx)
        if idx == -1:
            break
        preceding = q[max(0, idx - 4):idx]
        assert preceding.endswith("MAX("), (
            f"bare BUILTIN.DF at offset {idx} — must be MAX(BUILTIN.DF(...)) "
            "or SuiteQL 400s on the GROUP BY query"
        )
        idx += len("BUILTIN.DF(")


def test_as_bool_netsuite_flags():
    assert ns._as_bool("T") is True
    assert ns._as_bool("F") is False
    assert ns._as_bool(True) is True
    assert ns._as_bool(None) is False
    assert ns._as_bool("true") is True


def test_map_period_rows_derives_calendar_and_flags():
    rows = [
        {"id": "11", "period_name": "May 2026", "start_date": "2026-05-01",
         "end_date": "2026-05-31", "closed": "T", "isquarter": "F", "isyear": "F"},
        {"id": "99", "period_name": "Q2 2026", "start_date": "2026-04-01",
         "closed": "F", "isquarter": "T", "isyear": "F"},
    ]
    out = {p["period_id"]: p for p in ns.map_period_rows(rows)}
    assert out["11"]["period"] == "2026-05" and out["11"]["closed"] is True
    assert out["99"]["is_quarter"] is True


def test_map_subsidiary_and_account_rows():
    subs = ns.map_subsidiary_rows([
        {"id": "10", "name": "PingCAP US", "currency_name": "USD",
         "iselimination": "F", "isinactive": "F"}])
    assert subs[0]["subsidiary_id"] == "10" and subs[0]["currency"] == "USD"
    accts = ns.map_account_rows([
        {"id": "1", "account_number": "5000", "accttype": "Expense",
         "account_name": "Travel"}])
    assert accts[0]["account_id"] == "1" and accts[0]["account_type"] == "Expense"


# ─── DB-backed: graceful skip + full pull via fake client ───────────


@pytest_asyncio.fixture
async def fx(db_session):
    await upsert_monthly_rate(db_session, "CNY", PERIOD, CNY_RATE,
                              source="manual", frozen=True)
    await db_session.flush()
    return db_session


class _FakeClient:
    """Stand-in for NetSuiteClient.suiteql — returns canned summary rows."""

    host = "fake.suitetalk.api.netsuite.com"

    def __init__(self, gl_rows):
        self._gl = gl_rows
        self.closed = False

    async def suiteql(self, q, *, page_size=1000, max_rows=20000):
        return self._gl

    async def aclose(self):
        self.closed = True


@pytest.mark.asyncio
async def test_pull_skips_when_period_not_synced(fx):
    # No accounting-period master rows → soft-skip (not a crash).
    res = await ns.pull_gl_actuals(fx, PERIOD)
    assert res["skipped"] == "period_not_synced"


@pytest.mark.asyncio
async def test_pull_lands_rows_idempotently(fx, monkeypatch):
    db = fx
    # Seed the accounting period (closed) + a CNY subsidiary.
    db.add(FinanceNetSuiteAccountingPeriod(
        period_id="11", period_name="May 2026", start_date="2026-05-01",
        period=PERIOD, closed=True))
    db.add(FinanceNetSuiteSubsidiary(
        subsidiary_id="10", name="PingCAP CN", currency="CNY"))
    await db.flush()

    gl_rows = [
        {"account_id": "1", "account_number": "5000", "account_type": "Expense",
         "account_name": "Travel", "subsidiary_id": "10", "department_id": "3",
         "department_name": "R&D", "amount": "1000.00"},
        {"account_id": "2", "account_number": "4000", "account_type": "Income",
         "account_name": "Revenue", "subsidiary_id": "10", "department_id": "3",
         "department_name": "R&D", "amount": "-5000.00"},
    ]

    async def _fake_build(_db):
        return _FakeClient(gl_rows), {}

    monkeypatch.setattr(ns, "_build_client", _fake_build)

    res = await ns.pull_gl_actuals(db, PERIOD)
    assert res["rows"] == 2 and res["period_closed"] is True
    await db.flush()

    landed = (await db.execute(
        select(FinanceGLActual).where(FinanceGLActual.period == PERIOD)
    )).scalars().all()
    assert len(landed) == 2
    travel = next(r for r in landed if r.account_number == "5000")
    assert travel.amount_usd == Decimal("140.00")  # 1000 × 0.14
    assert travel.currency == "CNY" and travel.is_closed is True

    # Re-pull → idempotent replace (still 2, no duplication).
    res2 = await ns.pull_gl_actuals(db, PERIOD)
    assert res2["rows"] == 2
    await db.flush()
    again = (await db.execute(
        select(FinanceGLActual).where(FinanceGLActual.period == PERIOD)
    )).scalars().all()
    assert len(again) == 2