-- NetWrittenPremium — dbt-style view for the finance warehouse
--
-- Illustration for a fictional insurer. Hand-authored in a format CoreModels exports
-- (JSON Schema, ShEx) or renders to in an Ariesnet engagement (SQL, Cypher); not
-- produced by CoreModels.
-- https://ariesnet.com/resources/governed-definition-end-to-end
--
-- Grain: one row per policy per accounting month. The group by IS that grain, and the
-- CI gate reads it: a column added to or dropped from the group by returns a different
-- grain than the definition states, and the total moves.
--
-- Netting rule: cancellations net at period close, not in the month the cancellation
-- is recorded. That is the case expression below, from the 2026-02-17 change.
--
-- Each clause below is the same string the resource page shows in its Clause / SQL
-- table, so the page and this file can be diffed line for line.
--
-- Executed 2026-09-05 on PostgreSQL 16.15 (Docker) with {{ ref('fct_premium_txn') }}
-- resolved to a table of that name holding a six-row fixture — P-1001: written 2026-01-10
-- +1000, endorsement 2026-01-22 +150, cancellation recorded 2026-02-11 with
-- period_close_month 2026-03-01 -400, refund 2026-02-12 -50; P-1002: written 2026-02-03
-- +2000, endorsement 2026-02-20 -100. Result: P-1001 2026-01 1150.00; P-1001 2026-03
-- -400.00; P-1002 2026-02 1900.00 — the same three rows the Cypher render returns on
-- Neo4j 5.26.30 from the same fixture.

select
  policy_id, case when txn_type = 'cancellation' then period_close_month else date_trunc('month', accounting_date) end as accounting_month,
  sum(signed_amount) as net_written_premium
from {{ ref('fct_premium_txn') }}
where txn_type in ('written', 'endorsement', 'cancellation')
group by 1, 2
