Schema DDL

-- Layer 1: Reference (Entity master, Instrument master, Calendars)
-- Translated faithfully from design.md §4.1–§4.5
-- Idempotent: DROP TYPE IF EXISTS / DROP TABLE IF EXISTS before each CREATE

-- ============================================================
-- ENUMs
-- ============================================================

DROP TYPE IF EXISTS entity_type;
CREATE TYPE entity_type AS ENUM (
    'LEGAL_ENTITY',
    'FUND',
    'SPV',
    'COUNTERPARTY',
    'OPERATOR',
    'CEDANT',
    'VENUE',
    'INVESTOR'
);

DROP TYPE IF EXISTS entity_status;
CREATE TYPE entity_status AS ENUM (
    'ACTIVE',
    'INACTIVE',
    'DISSOLVED'
);

DROP TYPE IF EXISTS relationship_type;
CREATE TYPE relationship_type AS ENUM (
    'OWNS',
    'MANAGES',
    'SUBSIDIARY_OF',
    'INVESTS_IN'
);

DROP TYPE IF EXISTS asset_class;
CREATE TYPE asset_class AS ENUM (
    'WELL',
    'TREATY',
    'SFR_PORTFOLIO',
    'CRYPTO_DERIV'
);

DROP TYPE IF EXISTS instrument_status;
CREATE TYPE instrument_status AS ENUM (
    'ACTIVE',
    'MATURED',
    'EXPIRED',
    'DEFAULTED',
    'SOLD',
    'P_AND_A'
);

DROP TYPE IF EXISTS treaty_type;
CREATE TYPE treaty_type AS ENUM (
    'CAT_XOL',
    'CASUALTY_QS',
    'PROPERTY_QS',
    'STOP_LOSS'
);

DROP TYPE IF EXISTS ulae_basis;
CREATE TYPE ulae_basis AS ENUM (
    'PRO_RATA',
    'FLAT',
    'NONE'
);

DROP TYPE IF EXISTS product_type;
CREATE TYPE product_type AS ENUM (
    'FUTURE',
    'OPTION',
    'SWAP',
    'PERPETUAL'
);

DROP TYPE IF EXISTS option_type;
CREATE TYPE option_type AS ENUM (
    'CALL',
    'PUT'
);

DROP TYPE IF EXISTS settlement_type;
CREATE TYPE settlement_type AS ENUM (
    'CASH',
    'PHYSICAL'
);

DROP TYPE IF EXISTS role_type;
CREATE TYPE role_type AS ENUM (
    'OPERATOR',
    'CEDANT',
    'REINSURER',
    'CUSTODIAN',
    'VENUE_COUNTERPARTY',
    'PROPERTY_MANAGER'
);

-- ============================================================
-- §4.1 Entity master
-- ============================================================

DROP TABLE IF EXISTS entity_relationship CASCADE;
DROP TABLE IF EXISTS entity_xref CASCADE;
DROP TABLE IF EXISTS entity CASCADE;

CREATE TABLE entity (
    entity_id          UUID PRIMARY KEY,
    entity_type        entity_type,
    legal_name         TEXT NOT NULL,
    jurisdiction       TEXT,
    status             entity_status,
    inception_date     DATE,
    termination_date   DATE
);

CREATE TABLE entity_xref (
    entity_id          UUID,
    id_system          TEXT,         -- 'LEI', 'CIK', 'INTERNAL_LEGACY'
    external_id        TEXT,
    valid_from         TIMESTAMPTZ,
    valid_to           TIMESTAMPTZ,
    PRIMARY KEY (entity_id, id_system, external_id, valid_from)
);

-- entity_relationship is bitemporal because ownership corrections happen
-- and have to be auditable. It's also a graph (not a tree) by design.
CREATE TABLE entity_relationship (
    parent_entity_id      UUID,
    child_entity_id       UUID,
    relationship_type     relationship_type,
    ownership_pct         NUMERIC(7,4),
    elimination_required  BOOLEAN DEFAULT FALSE,  -- TRUE for cross-holdings that must be eliminated in firmwide rollup
    valid_from            TIMESTAMPTZ,
    valid_to              TIMESTAMPTZ,
    txn_from              TIMESTAMPTZ,
    txn_to                TIMESTAMPTZ,
    PRIMARY KEY (parent_entity_id, child_entity_id,
                 relationship_type, valid_from, txn_from)
);

-- ============================================================
-- §4.2 Instrument master
-- ============================================================

DROP TABLE IF EXISTS instrument_entity_role CASCADE;
DROP TABLE IF EXISTS instrument_crypto_deriv CASCADE;
DROP TABLE IF EXISTS instrument_sfr_portfolio CASCADE;
DROP TABLE IF EXISTS instrument_treaty_casualty_qs CASCADE;
DROP TABLE IF EXISTS instrument_treaty CASCADE;
DROP TABLE IF EXISTS instrument_well CASCADE;
DROP TABLE IF EXISTS instrument_xref CASCADE;
DROP TABLE IF EXISTS instrument CASCADE;

CREATE TABLE instrument (
    instrument_id      UUID PRIMARY KEY,
    asset_class        asset_class,
    subtype            TEXT,         -- 'HORIZONTAL_GAS', 'CAT_XOL', 'MSA_PORTFOLIO',
                                     -- 'CME_FUT', 'DERIBIT_OPT'
    inception_date     DATE NOT NULL,
    termination_date   DATE,
    base_currency      CHAR(3) NOT NULL,
    status             instrument_status,
    valid_from         TIMESTAMPTZ,
    valid_to           TIMESTAMPTZ
);

-- Decision: the firm's primary key is never an external ID. External IDs
-- change, get reissued, and have varying quality. instrument_xref tracks
-- them as time-bounded attributes.
CREATE TABLE instrument_xref (
    instrument_id      UUID,
    id_system          TEXT,         -- 'API_WELL_NUMBER', 'UPI', 'CME_PRODUCT',
                                     -- 'DERIBIT_INSTRUMENT', 'INTERNAL_LEGACY'
    external_id        TEXT,
    valid_from         TIMESTAMPTZ,
    valid_to           TIMESTAMPTZ,
    PRIMARY KEY (instrument_id, id_system, external_id, valid_from)
);

-- ============================================================
-- §4.3 Per-class detail tables
-- Each is 1:1 on instrument_id. Intrinsic facts only —
-- holder-specific economics (WI, NRI, cession share, occupancy)
-- live in Layer 3. Time-series go to Layer 2.
-- ============================================================

CREATE TABLE instrument_well (
    instrument_id          UUID PRIMARY KEY,
    api_number             TEXT,             -- 14-digit API; also in instrument_xref
    basin                  TEXT,
    formation              TEXT,
    spud_date              DATE,
    first_prod_date        DATE,
    decline_qi             NUMERIC,          -- Arps initial rate (mcf/month) — fit from real data
    decline_Di             NUMERIC,          -- annual nominal decline
    decline_b              NUMERIC,          -- hyperbolic exponent
    basis_hub              TEXT              -- 'HENRY_HUB', 'WAHA', 'DOM_SOUTH'
);
-- NOTE: working_interest_pct, nri_pct, monthly_loe_usd moved to position_well.
-- The well's decline curve is intrinsic to the well; SRHG's share of it is not.

CREATE TABLE instrument_treaty (
    instrument_id             UUID PRIMARY KEY,
    treaty_type               treaty_type,
    peril                     TEXT,             -- 'NA_HURRICANE', 'US_EARTHQUAKE',
                                                -- 'GENERAL_LIABILITY', 'AUTO_LIABILITY'
    attachment_usd            NUMERIC,          -- intrinsic to the contract structure
    limit_usd                 NUMERIC,
    reinstatements_max        INT,              -- e.g. 2 paid reinstatements available
    reinstatement_premium_pct NUMERIC,          -- typically 100% pro-rata as to amount/time
    ulae_basis                ulae_basis,
    hours_clause_hours        INT,              -- e.g. 168 (single cat event window)
    inception_date            DATE,
    expiry_date               DATE,
    currency                  CHAR(3),
    underwriting_year         INT
);
-- NOTE: ceded_share_pct moved to position_treaty.
-- The treaty layer's attachment/limit is intrinsic; SRHG's share of it is not.

-- Casualty QS extension. Cat XoL treaties don't populate this.
CREATE TABLE instrument_treaty_casualty_qs (
    instrument_id          UUID PRIMARY KEY,
    ceding_commission_pct  NUMERIC(7,4),
    sliding_scale_min_pct  NUMERIC(7,4),     -- min ceding commission in slide
    sliding_scale_max_pct  NUMERIC(7,4),
    loss_participation_pct NUMERIC(7,4),
    loss_corridor_attach   NUMERIC,
    loss_corridor_limit    NUMERIC
);

CREATE TABLE instrument_sfr_portfolio (
    instrument_id          UUID PRIMARY KEY,
    msa_code               TEXT,             -- OMB CBSA code
    msa_name               TEXT,
    property_count         INT,              -- portfolio shape, not SRHG share
    avg_acquisition_cost   NUMERIC,
    avg_monthly_rent       NUMERIC,
    climate_exposure_tags  TEXT[]            -- ['HURRICANE', 'WILDFIRE', 'FLOOD']
);

CREATE TABLE instrument_crypto_deriv (
    instrument_id          UUID PRIMARY KEY,
    venue                  TEXT,             -- 'CME', 'DERIBIT', 'OTC'
    product_type           product_type,
    underlying_index       TEXT,             -- 'CME_CF_BRR'
    strike                 NUMERIC,
    expiry                 DATE,
    option_type            option_type,      -- null for futures/perps
    contract_size_btc      NUMERIC,
    settlement_type        settlement_type
);
-- NOTE: notional_btc and lot accounting live in position_crypto_deriv +
-- cost_basis_lot (Layer 3).

-- ============================================================
-- §4.4 Roles
-- ============================================================

-- Replaces the equity world's single "issuer" pointer. A well has an
-- OPERATOR, a treaty has a CEDANT, a derivative has a VENUE_COUNTERPARTY
-- — each is just a role_type on this table.
CREATE TABLE instrument_entity_role (
    instrument_id       UUID,
    entity_id           UUID,
    role_type           role_type,
    effective_share_pct NUMERIC(7,4),         -- e.g. WI %, cession %
    valid_from          TIMESTAMPTZ,
    valid_to            TIMESTAMPTZ
);

-- ============================================================
-- §4.5 Calendars
-- ============================================================

DROP TABLE IF EXISTS holiday_calendar CASCADE;

-- Settlement timing, NAV strike days, and reconciliation cut-offs all
-- depend on the right calendar. Cheap to add; expensive to retrofit.
CREATE TABLE holiday_calendar (
    calendar_id        TEXT,                  -- 'NYSE', 'CME', 'FED', 'NERC', 'TX_OG'
    holiday_date       DATE,
    description        TEXT,
    PRIMARY KEY (calendar_id, holiday_date)
);

-- =============================================================================
-- Layer 2 — Time-series DDL
-- =============================================================================
-- Tables: series, series_obs, event, event_instrument_exposure, series_derivation
-- Design reference: docs/design.md §5.1–5.4
--
-- DuckDB notes:
--   - JSON (not JSONB) — DuckDB's JSON type is the correct equivalent
--   - ENUM idempotency: DROP TYPE IF EXISTS before each CREATE TYPE
--   - All tables use CREATE TABLE IF NOT EXISTS for idempotency
-- =============================================================================

-- ---------------------------------------------------------------------------
-- ENUMs
-- ---------------------------------------------------------------------------

-- §5.1 — series periodicity
DROP TYPE IF EXISTS periodicity;
CREATE TYPE periodicity AS ENUM (
    'DAILY',
    'MONTHLY',
    'QUARTERLY',
    'IRREGULAR'
);

-- §5.3 — event classification
DROP TYPE IF EXISTS event_type;
CREATE TYPE event_type AS ENUM (
    'HURRICANE',
    'EARTHQUAKE',
    'WELL_SHUT_IN',
    'BTC_HALVING',
    'OPTION_EXERCISE',
    'TREATY_LOSS'
);

-- §5.3 — how an event touches an instrument
DROP TYPE IF EXISTS exposure_role;
CREATE TYPE exposure_role AS ENUM (
    'DIRECT_LOSS',
    'REINSURANCE_RECOVERY',
    'COLLATERAL_CALL',
    'IMPAIRMENT',
    'MARK_ADJUSTMENT',
    'REGULATORY_TRIGGER'
);

-- ---------------------------------------------------------------------------
-- §5.1  series — the catalog of every observable in the firm
-- ---------------------------------------------------------------------------
-- Decision: measure_code, instrument_id, periodicity, tenor, strike,
-- curve_node, and FX legs are columns — not concatenated into a string or
-- buried in JSON. Lets you ask "all 30d ATM implied vol series across BTC
-- products" or "the full HH forward curve at M1-M12 on a given date" with
-- normal SQL.
--
-- FX rates are first-class series. Every USD valuation depends on them;
-- treating them as a series (rather than a special table) keeps the
-- bitemporal and vintage story uniform.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS series (
    series_id          UUID PRIMARY KEY,
    instrument_id      UUID,             -- nullable; null for market data
    measure_code       TEXT,             -- 'GAS_MCF_MONTHLY', 'NET_REVENUE_USD_MONTHLY',
                                         -- 'NAV_USD', 'PRICE_USD_PER_MMBTU',
                                         -- 'OCCUPANCY_PCT', 'IMPLIED_VOL', 'FX_RATE'
    periodicity        periodicity,
    unit               TEXT,
    source             TEXT,             -- 'EIA', 'CME', 'OPERATOR_JIB', 'DERIVED'
    -- Explicit dimensions (avoid leaking structure into JSON):
    tenor              TEXT,             -- 'M1', 'M2', 'M12', '30D', '2Y', '10Y'
    strike             NUMERIC,          -- for vol surfaces, options
    curve_node         TEXT,             -- 'ON', '1W', '3M' for curves
    fx_base            CHAR(3),          -- for FX series: e.g. 'USD'
    fx_quote           CHAR(3),          -- for FX series: e.g. 'EUR'
    scope              JSON,             -- remaining context: {region?, basin?, msa?, ...}
    tags               TEXT[],
    semantic_context   TEXT,             -- prose for AI grounding
    is_derived         BOOLEAN DEFAULT FALSE
);

-- ---------------------------------------------------------------------------
-- §5.2  series_obs — observations with valid-time bitemporality + vintage
-- ---------------------------------------------------------------------------
-- Decision: valid-time bitemporality plus a vintage field. No txn_from/txn_to
-- on series_obs. Vintage covers the "which pipeline run produced this" question
-- for derived series — the load-bearing piece for reproducibility. Full
-- bitemporal is reserved for Layer 3 (positions, trades, reconciliation).
--
-- vintage is nullable: null for raw observations; populated for derived series
-- (e.g. 'derivation_pipeline_2026-05-26T08:00Z').
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS series_obs (
    series_id          UUID,
    observation_ts     TIMESTAMPTZ,
    value              NUMERIC,
    valid_from         TIMESTAMPTZ,
    valid_to           TIMESTAMPTZ,
    vintage            TEXT,             -- 'derivation_pipeline_2026-05-26T08:00Z'
                                         -- null for raw observations
    source_ref         TEXT,
    PRIMARY KEY (series_id, observation_ts, valid_from)
);

-- ---------------------------------------------------------------------------
-- §5.3  event — structured non-scalar occurrences
-- ---------------------------------------------------------------------------
-- Rationale for separating events from series: a hurricane is not a scalar at
-- a timestamp. Forcing structured event data into (series_id, ts, value) would
-- push the structure into JSON anyway, only less queryably.
--
-- payload is JSON but validated against a per-type schema at write time
-- (e.g., a HURRICANE payload must have category, landfall_state, landfall_lat,
-- landfall_lon, max_windspeed_kt, modeled_industry_loss_usd). This keeps event
-- data structured without forcing a wide nullable table.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS event (
    event_id           UUID PRIMARY KEY,
    event_type         event_type,
    occurred_at        TIMESTAMPTZ,
    payload            JSON              -- typed per event_type; validated at write time
);

-- ---------------------------------------------------------------------------
-- §5.3  event_instrument_exposure — normalized event-to-instrument linkage
-- ---------------------------------------------------------------------------
-- Event-to-instrument linkage is normalized rather than a UUID array on event.
-- Lets you query "why is this treaty affected by Irma" with a join, attach
-- modeled losses per instrument, and distinguish role types (direct exposure
-- vs. reinsurance recovery vs. collateral call).
--
-- Bitemporal valid-time on this table: exposure estimates are revised as
-- models are re-run or realized losses come in; valid_from/valid_to track
-- which estimate was current at any given time.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS event_instrument_exposure (
    event_id           UUID,
    instrument_id      UUID,
    exposure_role      exposure_role,
    modeled_loss_usd   NUMERIC,           -- the event's modeled impact on this instrument
    confidence         NUMERIC(4,2),      -- 0.0–1.0
    source             TEXT,              -- 'cat_model', 'expert_judgment', 'realized'
    rationale          TEXT,
    valid_from         TIMESTAMPTZ,
    valid_to           TIMESTAMPTZ,
    PRIMARY KEY (event_id, instrument_id, exposure_role, valid_from)
);

-- ---------------------------------------------------------------------------
-- §5.4  series_derivation — catalog of derived series and their formulas
-- ---------------------------------------------------------------------------
-- Formulas live in code, keyed by formula_code. Pipeline runs evaluate them
-- and write into series_obs with the run's vintage.
--
-- POC scoping note: for the POC, series_derivation is a stub catalog —
-- registrations exist but the runtime lookup is hardcoded in the derivation
-- pipeline. In production this table becomes the source of truth that the
-- pipeline reads at startup. Documented here because the schema position
-- matters for interview-signal even if the runtime indirection is deferred.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS series_derivation (
    series_id          UUID PRIMARY KEY,    -- the derived series
    formula_code       TEXT,                -- 'WELL_NET_REVENUE_V1', 'SFR_NOI_V1', ...
    formula_version    TEXT,
    input_series_ids   UUID[],
    static_params      JSON,
    rationale          TEXT
);

-- =============================================================================
-- srDB Layer 3 — IBOR / Position
-- =============================================================================
-- This is the JD's literal headline. The substrate has to support reconciling
-- against ABOR with full audit lineage. The position layer is polymorphic —
-- a header table with a single scalar `quantity` can't honestly represent a
-- treaty's reinstatement state or a well's WI/NRI split. Per-class extensions
-- on `position_id` carry the state that matters.
--
-- Design ref: docs/design.md §6
-- Idempotent: run twice, no error.
-- DuckDB notes:
--   - JSON not JSONB
--   - Composite PRIMARY KEY and FOREIGN KEY supported
--   - ENUM idempotency: DROP TYPE IF EXISTS before CREATE TYPE
--   - Views: CREATE OR REPLACE VIEW
--   - Table drops before ENUM drops (types in use cannot be dropped)
--   - Table drops in reverse-FK order (children before parents)
-- =============================================================================

-- ---------------------------------------------------------------------------
-- Table drops — reverse FK dependency order (children before parents).
-- Must precede ENUM drops: DuckDB will not drop a type while a table uses it.
-- ---------------------------------------------------------------------------
DROP TABLE IF EXISTS break_history;
DROP TABLE IF EXISTS reconciliation_break;
DROP TABLE IF EXISTS reconciliation_pair;
DROP TABLE IF EXISTS reconciliation_run;
DROP TABLE IF EXISTS cost_basis_method;
DROP TABLE IF EXISTS cost_basis_lot;
DROP TABLE IF EXISTS cash_transaction;
DROP TABLE IF EXISTS trade;
DROP TABLE IF EXISTS position_crypto_deriv;
DROP TABLE IF EXISTS position_sfr;
DROP TABLE IF EXISTS position_treaty;
DROP TABLE IF EXISTS position_well;
DROP TABLE IF EXISTS abor_position_feed;
DROP TABLE IF EXISTS position;

-- ---------------------------------------------------------------------------
-- ENUM definitions (dropped after tables to avoid "type in use" errors)
-- ---------------------------------------------------------------------------

DROP TYPE IF EXISTS valuation_method;
CREATE TYPE valuation_method AS ENUM (
    'MARK_TO_MARKET',
    'NAV',
    'DCF',
    'APPRAISAL',
    'COST',
    'MARK_TO_MODEL'
);

-- Extended trade side covers all asset classes:
--   BUY / SELL             — standard equity/futures
--   CEDE / ASSUME / COMMUTE — reinsurance treaties
--   OPEN / CLOSE           — crypto derivatives (open/close lot)
--   SUBSCRIBE / REDEEM     — fund units
DROP TYPE IF EXISTS trade_side;
CREATE TYPE trade_side AS ENUM (
    'BUY',
    'SELL',
    'CEDE',
    'ASSUME',
    'COMMUTE',
    'OPEN',
    'CLOSE',
    'SUBSCRIBE',
    'REDEEM'
);

DROP TYPE IF EXISTS trade_status;
CREATE TYPE trade_status AS ENUM (
    'NEW',
    'AMENDED',
    'CANCELLED',
    'NOVATED',
    'ALLOCATED',
    'GIVEN_UP',
    'SETTLED'
);

-- 25-value transaction_type covering all asset classes modeled:
--   Reinsurance:  PREMIUM_INSTALLMENT, PREMIUM_RETURN, CLAIM_PAYMENT,
--                 REINSTATEMENT_PREMIUM, COMMUTATION_SETTLEMENT
--   NatGas wells: SEVERANCE_TAX, AD_VALOREM_TAX, IMPACT_FEE,
--                 ROYALTY_PAID, LOE_PAYMENT
--   SFR:          RENT_RECEIVED, PROPERTY_TAX, INSURANCE_PREMIUM
--   FX / Crypto:  FX_TRADE_BOUGHT, FX_TRADE_SOLD,
--                 FUNDING_PAYMENT, MARGIN_CALL, MARGIN_RELEASE
--   Fees:         FEE_BROKERAGE, FEE_CUSTODY, FEE_MANAGEMENT
--   General:      INTEREST, DIVIDEND, SUBSCRIPTION_IN, REDEMPTION_OUT
DROP TYPE IF EXISTS transaction_type;
CREATE TYPE transaction_type AS ENUM (
    'PREMIUM_INSTALLMENT',
    'PREMIUM_RETURN',
    'CLAIM_PAYMENT',
    'REINSTATEMENT_PREMIUM',
    'COMMUTATION_SETTLEMENT',
    'SEVERANCE_TAX',
    'AD_VALOREM_TAX',
    'IMPACT_FEE',
    'ROYALTY_PAID',
    'LOE_PAYMENT',
    'RENT_RECEIVED',
    'PROPERTY_TAX',
    'INSURANCE_PREMIUM',
    'FX_TRADE_BOUGHT',
    'FX_TRADE_SOLD',
    'FUNDING_PAYMENT',
    'MARGIN_CALL',
    'MARGIN_RELEASE',
    'FEE_BROKERAGE',
    'FEE_CUSTODY',
    'FEE_MANAGEMENT',
    'INTEREST',
    'DIVIDEND',
    'SUBSCRIPTION_IN',
    'REDEMPTION_OUT'
);

DROP TYPE IF EXISTS lot_status;
CREATE TYPE lot_status AS ENUM (
    'OPEN',
    'PARTIALLY_CLOSED',
    'CLOSED'
);

DROP TYPE IF EXISTS cost_basis_method_type;
CREATE TYPE cost_basis_method_type AS ENUM (
    'HIFO',
    'LIFO',
    'FIFO',
    'SPECIFIC_ID',
    'AVERAGE_COST'
);

DROP TYPE IF EXISTS break_category;
CREATE TYPE break_category AS ENUM (
    'PRICE',
    'QUANTITY',
    'FX',
    'TIMING',
    'MISSING_IN_ABOR',
    'MISSING_IN_SRDB',
    'CLASSIFICATION',
    'COST_BASIS'
);

DROP TYPE IF EXISTS break_status;
CREATE TYPE break_status AS ENUM (
    'OPEN',
    'INVESTIGATING',
    'RESOLVED',
    'ACCEPTED',
    'ESCALATED'
);

-- Severity tiers driven by break_amount_usd thresholds + asset_class at creation time
DROP TYPE IF EXISTS severity_tier;
CREATE TYPE severity_tier AS ENUM (
    'P1',
    'P2',
    'P3',
    'P4'
);

-- ---------------------------------------------------------------------------
-- §6.1 — position header (bitemporal)
-- ---------------------------------------------------------------------------
-- Key design decisions:
--   - quantity is the canonical scalar in the instrument's native unit
--   - market_value_usd is computed from upstream price/NAV series
--   - mark_series_id / mark_observation_ts / mark_vintage stamp the lineage of
--     the mark used so reconciliation can attribute breaks to upstream re-derivations
--   - For multi-input valuations (options: S + σ + r), a future
--     position_pricing_lineage child table tracks per-input vintages (Phase 2)
--   - valuation_source is the human label; the three mark_* columns are the
--     structured lineage

CREATE TABLE position (
    position_id          UUID PRIMARY KEY,
    instrument_id        UUID,                   -- FK → instrument.instrument_id (Layer 1)
    holding_entity_id    UUID,                   -- which SRHG entity holds it
    as_of_date           DATE,
    quantity             NUMERIC,                -- canonical scalar in instrument's native unit
    quantity_unit        TEXT,                   -- 'CONTRACTS', 'BTC', 'FUND_UNITS', ...
    cost_basis_usd       NUMERIC,
    market_value_usd     NUMERIC,
    valuation_method     valuation_method,
    valuation_source     TEXT,
    -- Bitemporal columns
    valid_from           TIMESTAMPTZ,
    valid_to             TIMESTAMPTZ,
    txn_from             TIMESTAMPTZ,
    txn_to               TIMESTAMPTZ,
    -- Mark lineage: stamps the vintage of the mark used to derive market_value_usd.
    -- Lets reconciliation attribute breaks to upstream re-derivations vs. position errors.
    mark_series_id       UUID,                   -- the price/NAV series used to mark this position
    mark_observation_ts  TIMESTAMPTZ,            -- the observation_ts within that series
    mark_vintage         TEXT                    -- vintage of the mark obs (for derived-series re-derivation attribution)
);

-- ---------------------------------------------------------------------------
-- §6.1 — per-class position extensions (1:1 with position via FK on position_id)
-- ---------------------------------------------------------------------------
-- Decision: instrument-level facts are intrinsic (what the thing is);
-- holder-specific facts (WI, NRI, cession share, lot accounting) live here,
-- not on the instrument. The same well or treaty can be held in different
-- proportions by different SRHG entities.

-- Well extension: SRHG's holder-specific economics
-- NOTE: working_interest_pct, nri_pct, monthly_loe_usd moved here from instrument_well
--       The well's decline curve is intrinsic to the well; SRHG's share of it is not.
CREATE TABLE position_well (
    position_id          UUID PRIMARY KEY REFERENCES position(position_id),
    working_interest_pct NUMERIC(7,4),           -- SRHG's gross share at this as_of_date
    nri_pct              NUMERIC(7,4),           -- SRHG's net-of-royalty share
    monthly_loe_usd      NUMERIC,                -- holder's allocable LOE
    severance_tax_pct    NUMERIC(7,4),
    ad_valorem_pct       NUMERIC(7,4)
);

-- Treaty extension: SRHG's reinsurer-side economics at position date
-- NOTE: cession_pct moved here from instrument_treaty
--       The treaty's attachment/limit is intrinsic; SRHG's share of it is not.
CREATE TABLE position_treaty (
    position_id                UUID PRIMARY KEY REFERENCES position(position_id),
    cession_pct                NUMERIC(7,4),     -- SRHG's share of this layer
    limit_remaining_usd        NUMERIC,          -- after losses to date
    reinstatements_consumed    INT,
    attached_loss_to_date_usd  NUMERIC,
    upr_usd                    NUMERIC,          -- unearned premium reserve
    paid_loss_usd              NUMERIC,
    reported_loss_usd          NUMERIC,          -- includes case reserves
    ibnr_usd                   NUMERIC,          -- incurred but not reported
    ulae_usd                   NUMERIC           -- unallocated loss adjustment expense
);

-- SFR extension: SRHG's holder-specific portfolio state
CREATE TABLE position_sfr (
    position_id              UUID PRIMARY KEY REFERENCES position(position_id),
    units_owned              INT,
    units_in_lease           INT,
    occupancy_pct            NUMERIC(7,4),
    arrears_usd              NUMERIC,
    monthly_gross_rent_usd   NUMERIC,
    monthly_opex_usd         NUMERIC
);

-- Crypto derivative extension: NYDIG-specific derivative state
-- NOTE: notional_btc and lot accounting live here + cost_basis_lot
CREATE TABLE position_crypto_deriv (
    position_id              UUID PRIMARY KEY REFERENCES position(position_id),
    notional_btc             NUMERIC,
    margin_posted_usd        NUMERIC,
    funding_accrued_usd      NUMERIC,            -- for perps + futures
    mark_price               NUMERIC,
    unrealized_pnl_usd       NUMERIC
);

-- ---------------------------------------------------------------------------
-- §6.2 — trade (bitemporal, with lifecycle)
-- ---------------------------------------------------------------------------
-- parent_trade_id links amendments, novations, and allocations to their origin.
-- A trade_link table for partial allocations is Phase 2; parent_trade_id is the
-- only link mechanism for the POC.

CREATE TABLE trade (
    trade_id               UUID PRIMARY KEY,
    parent_trade_id        UUID,                 -- for amendments, novations, allocations; nullable
    instrument_id          UUID,
    holding_entity_id      UUID,
    counterparty_entity_id UUID,
    trade_date             DATE,
    settle_date            DATE,
    side                   trade_side,
    trade_status           trade_status,
    quantity               NUMERIC,
    quantity_unit          TEXT,
    price                  NUMERIC,
    price_currency         CHAR(3),
    notional_usd           NUMERIC,
    -- Bitemporal columns
    valid_from             TIMESTAMPTZ,
    valid_to               TIMESTAMPTZ,
    txn_from               TIMESTAMPTZ,
    txn_to                 TIMESTAMPTZ
);

-- ---------------------------------------------------------------------------
-- §6.3 — cash_transaction (bitemporal; balance is derived)
-- ---------------------------------------------------------------------------
-- Decision: transactions are primary; balance is a view (cash_balance below).
-- A balance-only table can't represent the *why* of cash movements.
-- Every movement is typed and linked to its source object (trade, event, instrument).
-- Amount sign convention: positive = inflow to entity.

CREATE TABLE cash_transaction (
    txn_id                 UUID PRIMARY KEY,
    entity_id              UUID,
    currency               CHAR(3),
    amount                 NUMERIC,              -- signed; positive = inflow to entity
    transaction_type       transaction_type,
    related_instrument_id  UUID,                 -- nullable
    related_trade_id       UUID,                 -- nullable
    related_event_id       UUID,                 -- for claim payments tied to a cat event; nullable
    trade_date             DATE,
    value_date             DATE,
    description            TEXT,
    -- Bitemporal columns
    valid_from             TIMESTAMPTZ,
    valid_to               TIMESTAMPTZ,
    txn_from               TIMESTAMPTZ,
    txn_to                 TIMESTAMPTZ
);

-- ---------------------------------------------------------------------------
-- §6.3 — cash_balance VIEW (balance is derived, not stored)
-- ---------------------------------------------------------------------------
-- Balance is a running sum over the current-valid bitemporal slice.
-- POC uses (valid_to IS NULL AND txn_to IS NULL) as the current-valid filter.
-- In production, parameterize with as-of-valid-time and as-of-txn-time.

CREATE OR REPLACE VIEW cash_balance AS
    SELECT
        entity_id,
        currency,
        value_date,
        SUM(amount) OVER (
            PARTITION BY entity_id, currency
            ORDER BY value_date
        ) AS balance
    FROM cash_transaction
    WHERE valid_to IS NULL
      AND txn_to IS NULL;

-- ---------------------------------------------------------------------------
-- §6.4 — cost_basis_lot (critical for NYDIG BTC tax-lot accounting)
-- ---------------------------------------------------------------------------
-- HIFO/LIFO/FIFO produces dramatically different reported PnL for crypto.
-- Also used for any position-keeping where realized vs. unrealized PnL matters.
-- remaining_quantity is decremented as lots close.

CREATE TABLE cost_basis_lot (
    lot_id                   UUID PRIMARY KEY,
    position_id              UUID,               -- FK → position.position_id
    open_trade_id            UUID,               -- FK → trade.trade_id
    open_date                DATE,
    opened_quantity          NUMERIC,
    remaining_quantity       NUMERIC,            -- decremented as lots close
    cost_basis_per_unit_usd  NUMERIC,
    total_cost_basis_usd     NUMERIC,
    lot_status               lot_status,
    closed_date              DATE,               -- nullable while open
    realized_pnl_usd         NUMERIC             -- nullable while open
);

-- cost_basis_method: entity-level (or entity+instrument-level) method election.
-- Keyed at (entity, instrument); HIFO is default for NYDIG crypto per §14 open question 5.
-- Real shops may need (entity, instrument, lot-acquired-method) for tax-driven specific-ID —
-- documented as Phase 2.

CREATE TABLE cost_basis_method (
    entity_id              UUID,
    instrument_id          UUID,                 -- nullable: null means class-wide election
    method                 cost_basis_method_type,
    effective_from         DATE,
    PRIMARY KEY (entity_id, instrument_id, effective_from)
);

-- ---------------------------------------------------------------------------
-- §6.5 — reconciliation: decomposed into 4 tables
-- ---------------------------------------------------------------------------
-- Decision: decompose rather than one flat break table.
-- A break persisting across N daily runs is the same logical break.
-- The reconciliation pipeline matches via logical_break_key before insert vs. update.
--
-- 4-table decomposition:
--   reconciliation_run   — the run that detected breaks (metadata + aggregate counts)
--   reconciliation_pair  — every (srdb, abor) pair compared, whether or not it broke
--   reconciliation_break — the break itself: root cause, status, aging, carry-forward
--   break_history        — status transitions over the break's lifetime

CREATE TABLE reconciliation_run (
    run_id                 UUID PRIMARY KEY,
    as_of_date             DATE,
    abor_snapshot_ref      TEXT,                 -- which ABOR file/version was compared
    started_at             TIMESTAMPTZ,
    completed_at           TIMESTAMPTZ,
    n_pairs                INT,
    n_breaks               INT,
    n_breaks_new           INT,                  -- breaks first detected in this run
    n_breaks_carried       INT,                  -- breaks carried forward from prior runs
    notes                  TEXT
);

CREATE TABLE reconciliation_pair (
    pair_id                UUID PRIMARY KEY,
    run_id                 UUID,                 -- FK → reconciliation_run.run_id
    holding_entity_id      UUID,
    instrument_id          UUID,
    srdb_quantity          NUMERIC,
    srdb_value_usd         NUMERIC,
    abor_quantity          NUMERIC,
    abor_value_usd         NUMERIC,
    is_break               BOOLEAN
);

-- Break carry-forward identity:
--   logical_break_key = sha256(holding_entity_id || instrument_id || break_category)
--   The pipeline matches incoming pairs to existing OPEN breaks via this key before
--   deciding insert vs. update. last_seen_run_id updates on every detection.
--   resolution_run_id populates when the pair stops breaking.
--   This keeps break_id stable for aging, escalation, and audit trail.
--
-- break_amount_usd sign convention: positive = srDB over-reports vs. ABOR
--   (srdb_value_usd − abor_value_usd)
--
-- severity_tier driven by break_amount_usd thresholds + asset_class at creation.
-- sla_target_date computed from severity_tier on creation.

CREATE TABLE reconciliation_break (
    break_id               UUID PRIMARY KEY,
    pair_id                UUID,                 -- FK → reconciliation_pair.pair_id
    break_category         break_category,
    break_amount_usd       NUMERIC,              -- sign: positive = srDB over-reports vs. ABOR
    root_cause_trade_id    UUID,                 -- often the trade that explains the break; nullable
    root_cause_event_id    UUID,                 -- e.g. a claim payment not yet booked; nullable
    status                 break_status,
    -- Break carry-forward tracking
    first_seen_run_id      UUID,                 -- when this break first appeared
    last_seen_run_id       UUID,                 -- most recent run where this break was still detected
    resolution_run_id      UUID,                 -- run that cleared this break (null while open)
    -- Denormalized carry-forward identity key:
    -- hash(holding_entity_id || instrument_id || break_category) — stable across daily runs
    -- Populated at insertion; used by the pipeline to match existing open breaks
    logical_break_key      TEXT,
    age_days               INT,
    current_owner_entity   TEXT,
    severity_tier          severity_tier,        -- P1–P4, driven by amount + asset_class
    sla_target_date        DATE,                 -- computed from severity_tier on creation
    notes                  TEXT
);

CREATE TABLE break_history (
    history_id             UUID PRIMARY KEY,
    break_id               UUID,                 -- FK → reconciliation_break.break_id
    status_from            TEXT,                 -- previous break_status value
    status_to              TEXT,                 -- new break_status value
    changed_at             TIMESTAMPTZ,
    changed_by             TEXT,
    notes                  TEXT
);

-- ---------------------------------------------------------------------------
-- §9.4 — abor_position_feed
-- ---------------------------------------------------------------------------
-- Mirrors the `position` shape for the same instrument set, minus the bitemporal
-- fields (valid_from/valid_to/txn_from/txn_to) and minus the mark-lineage columns.
-- Loaded with deliberate divergences to trigger reconciliation detection:
--   1. Stale price break on a well position (ABOR uses prior-month strip)
--   2. Quantity break on a treaty (off-by-one reinstatement consumed)
--   3. Classification break on an SFR portfolio (asset class mismatch)
--   4. Missing-in-ABOR for a recently-added crypto derivative

CREATE TABLE abor_position_feed (
    position_id          UUID PRIMARY KEY,       -- matches srDB position_id for pairing
    instrument_id        UUID,
    holding_entity_id    UUID,
    as_of_date           DATE,
    quantity             NUMERIC,
    quantity_unit        TEXT,
    cost_basis_usd       NUMERIC,
    market_value_usd     NUMERIC,
    valuation_method     valuation_method,
    valuation_source     TEXT
    -- No bitemporal columns — ABOR feed is a point-in-time snapshot
    -- No mark-lineage columns — ABOR does not expose mark sourcing
);

-- Layer 4: Analytics primitives
-- Translated faithfully from design.md §7.1–§7.3 and §8b
-- Idempotent: DROP TYPE IF EXISTS / DROP TABLE IF EXISTS before each CREATE

-- ============================================================
-- ENUMs
-- ============================================================

-- §7.2 — mechanism by which a driver series propagates into an asset class
DROP TYPE IF EXISTS exposure_mechanism;
CREATE TYPE exposure_mechanism AS ENUM (
    'REVENUE_DRIVER',
    'OPEX_DRIVER',
    'VALUATION_DRIVER',
    'CLAIM_TRIGGER',
    'MARGIN_DRIVER',
    'COST_BASIS_DRIVER'
);

-- §7.2 — directional sign of the driver→asset linkage
DROP TYPE IF EXISTS exposure_sign;
CREATE TYPE exposure_sign AS ENUM (
    'POSITIVE',
    'NEGATIVE',
    'AMBIGUOUS'
);

-- ============================================================
-- Tables
-- ============================================================

-- §7.1  instrument_pnl_model
-- Per-instrument engineering identity. Math lives in Python; this row
-- records which model applies and its static parameters.
DROP TABLE IF EXISTS instrument_pnl_model;
CREATE TABLE instrument_pnl_model (
    instrument_id      UUID PRIMARY KEY,
    model_code         TEXT,                -- e.g. 'WELL_NET_REVENUE_V1', 'BS_OPTION_V1'
    static_params      JSON,                -- DuckDB uses JSON (not JSONB)
    driver_series_ids  UUID[],              -- which series feed the model
    rationale          TEXT
);

-- §7.2  driver_exposure_map
-- Cross-asset propagation mechanism. Records which drivers touch which
-- asset classes through which economic mechanism — structured and auditable.
-- PK is 4-column composite: (driver_series_id, asset_class, treaty_peril,
-- exposure_mechanism). treaty_peril participates in PK; non-treaty rows
-- use '' (empty string) as a sentinel so the composite remains unique.
DROP TABLE IF EXISTS driver_exposure_map;
CREATE TABLE driver_exposure_map (
    driver_series_id   UUID,               -- e.g. series for NATGAS_HH_M1, BTC_SPOT
    asset_class        asset_class,        -- WELL, TREATY, SFR_PORTFOLIO, CRYPTO_DERIV
    treaty_peril       TEXT,               -- narrows by peril when asset_class=TREATY; '' for non-treaty
    exposure_mechanism exposure_mechanism, -- how the driver propagates into this asset class
    sign               exposure_sign,      -- POSITIVE, NEGATIVE, or AMBIGUOUS
    magnitude_hint     TEXT,               -- 'high', 'medium', 'low' — qualitative
    rationale          TEXT,               -- AI grounding: why this linkage exists
    estimation_method  TEXT,               -- 'analytical', 'expert_judgment', 'historical'
    PRIMARY KEY (driver_series_id, asset_class, treaty_peril, exposure_mechanism)
);

-- §7.3  scenario
-- Curated scenario library. AI maps NL questions to scenarios and narrates;
-- it does not invent scenarios from raw data.
DROP TABLE IF EXISTS scenario;
CREATE TABLE scenario (
    scenario_id        UUID PRIMARY KEY,
    name               TEXT,               -- e.g. 'CAT4_FLA_LANDFALL_80B', 'BTC_-30%_VOL_+40PT'
    driver_shocks      JSON,               -- {series_code: {pct: +0.20, window_days: 30}, ...}
    event_injections   JSON,               -- structured event payloads for event-driven scenarios
    rationale          TEXT,
    precedent_obs_ids  UUID[]              -- historical observations that resemble this scenario
);

-- §8b  position_lookthrough
-- Materialized table written by pipelines/lookthrough.py via recursive CTE
-- (depth-limited to 10, cycle-guarded). Two views sit on top:
--   vw_position_consolidated — single line per direct holding (consolidation view)
--   vw_position_lookthrough  — decomposed to ultimate instruments (lookthrough view)
DROP TABLE IF EXISTS position_lookthrough;
CREATE TABLE position_lookthrough (
    lookthrough_id           UUID      PRIMARY KEY,
    as_of_date               DATE      NOT NULL,
    holding_entity_id        UUID      NOT NULL,   -- the direct holder we started from (e.g. LTRe)
    ownership_chain          TEXT      NOT NULL,   -- JSON array of entity_ids walked
    ultimate_instrument_id   UUID      NOT NULL,
    asset_class              TEXT      NOT NULL,   -- denormalized from instrument
    attributed_quantity      DOUBLE    NOT NULL,   -- direct quantity × cumulative_ownership_pct
    attributed_value_usd     DOUBLE    NOT NULL,   -- direct market_value_usd × cumulative_ownership_pct
    cumulative_ownership_pct DOUBLE    NOT NULL,   -- product of ownership_pct at each edge in the chain
    direct_position_id       UUID,                -- the position row at the direct holder
    direct_quantity          DOUBLE,              -- raw quantity at direct holder (for conservation check)
    direct_value_usd         DOUBLE               -- raw market_value_usd at direct holder
);

-- §8b  lookthrough_diagnostics
-- Populated only when cycles are detected during traversal. The traversal
-- algorithm marks visited entities per traversal and refuses to revisit,
-- logging a warning here rather than silently recursing infinitely.
DROP TABLE IF EXISTS lookthrough_diagnostics;
CREATE TABLE lookthrough_diagnostics (
    diagnostic_id            UUID PRIMARY KEY,
    as_of_date               DATE,
    detected_at              TIMESTAMPTZ,
    traversal_root_entity_id UUID,          -- entity where the traversal started
    cycle_path               UUID[],        -- ordered list of entity_ids that form the cycle
    notes                    TEXT
);

-- =============================================================================
-- srDB — Lookthrough & Consolidation views
-- Design ref: docs/design.md §8b
-- =============================================================================
-- Two distinct views over the position layer:
--
--   vw_position_consolidated
--       One row per direct holding (no lookthrough).
--       Filters position to current-valid records (valid_to IS NULL,
--       txn_to IS NULL) and picks the latest as_of_date per
--       (holding_entity_id, instrument_id).
--       Also exposes the elimination_required flag from entity_relationship so
--       that firmwide-AUM rollup queries can subtract cross-holdings without
--       double-counting the LTRE → SRAM_MULTISTRAT loop.
--
--   vw_position_lookthrough
--       Joins position_lookthrough (materialised by pipelines/lookthrough.py)
--       to entity and instrument for human-readable output.
--       Each row decomposes one direct holding to its ultimate instrument,
--       weighted by the cumulative ownership_pct along the path.
--
-- Idempotent: CREATE OR REPLACE VIEW.
-- =============================================================================


-- ---------------------------------------------------------------------------
-- vw_position_consolidated
-- ---------------------------------------------------------------------------
-- "Single line per direct holding" view.  Does NOT walk the entity graph;
-- shows only what each entity directly owns in the position table.
--
-- Elimination convention:
--   The firmwide-AUM rollup sums market_value_usd across all rows EXCEPT those
--   where elimination_required = TRUE (the LTRE → SRAM_MULTISTRAT cross-holding).
--   Callers apply:
--       SUM(market_value_usd) FILTER (WHERE NOT COALESCE(elimination_required, FALSE))
--   to get the firmwide net AUM without double-counting inter-fund positions.
--
-- If a holding entity has no parent in entity_relationship (e.g. top-level
-- entities or counterparties), elimination_required defaults to FALSE via
-- the LEFT JOIN.
-- ---------------------------------------------------------------------------

CREATE OR REPLACE VIEW vw_position_consolidated AS
WITH latest AS (
    -- Pick the single most recent as_of_date row per (entity, instrument)
    -- among currently-valid bitemporal records.
    SELECT
        p.position_id,
        p.instrument_id,
        p.holding_entity_id,
        p.as_of_date,
        p.quantity,
        p.quantity_unit,
        p.cost_basis_usd,
        p.market_value_usd,
        p.valuation_method::TEXT    AS valuation_method,
        p.valuation_source,
        i.asset_class::TEXT         AS asset_class,
        i.subtype                   AS instrument_subtype,
        ROW_NUMBER() OVER (
            PARTITION BY p.holding_entity_id, p.instrument_id
            ORDER BY p.as_of_date DESC, p.txn_from DESC
        ) AS rn
    FROM position p
    JOIN instrument i ON i.instrument_id = p.instrument_id
    WHERE p.valid_to IS NULL
      AND p.txn_to   IS NULL
)
SELECT
    l.position_id,
    l.instrument_id,
    l.holding_entity_id,
    e_holder.legal_name             AS holding_entity_name,
    l.as_of_date,
    l.quantity,
    l.quantity_unit,
    l.cost_basis_usd,
    l.market_value_usd,
    l.valuation_method,
    l.valuation_source,
    l.asset_class,
    l.instrument_subtype,
    -- Elimination flag: TRUE when this entity's position in this fund must be
    -- subtracted from the firmwide rollup to avoid double-counting.
    -- We look for any parent→this_entity edge with elimination_required=TRUE.
    COALESCE(
        BOOL_OR(er.elimination_required),
        FALSE
    ) AS elimination_required
FROM latest l
JOIN entity e_holder
    ON e_holder.entity_id = l.holding_entity_id
LEFT JOIN entity_relationship er
    ON  er.child_entity_id  = l.holding_entity_id
    AND er.txn_to IS NULL
WHERE l.rn = 1
GROUP BY
    l.position_id,
    l.instrument_id,
    l.holding_entity_id,
    e_holder.legal_name,
    l.as_of_date,
    l.quantity,
    l.quantity_unit,
    l.cost_basis_usd,
    l.market_value_usd,
    l.valuation_method,
    l.valuation_source,
    l.asset_class,
    l.instrument_subtype;


-- ---------------------------------------------------------------------------
-- vw_position_lookthrough
-- ---------------------------------------------------------------------------
-- "Decomposed to ultimate instruments" view.  Reads from position_lookthrough
-- (the materialised table written by pipelines/lookthrough.py) and enriches
-- with entity and instrument names for readability.
--
-- Each row represents one (holder_start, path, ultimate_instrument) combination.
-- For a given holder_start + ultimate_instrument there may be multiple rows if
-- multiple ownership paths lead to the same instrument (e.g. SRAM_ENERGY_FUND
-- reaches ENERGY_SPV_HAYNESVILLE via INVESTS_IN directly AND via the
-- SRAM_MULTISTRAT → SRAM_ENERGY_FUND leg if Multi-Strat INVESTS_IN energy fund
-- as well).
--
-- Key columns:
--   holding_entity_id / holding_entity_name : the entity we started from
--   ultimate_instrument_id / ultimate_instrument_name : the leaf instrument
--   asset_class : denormalised from instrument (WELL, TREATY, SFR_PORTFOLIO,
--                                                CRYPTO_DERIV)
--   ownership_chain : JSON array of entity_ids walked
--   cumulative_ownership_pct : product of edge ownership_pct along the path
--   attributed_quantity  : direct_quantity × cumulative_pct
--   attributed_value_usd : direct_value_usd × cumulative_pct
-- ---------------------------------------------------------------------------

CREATE OR REPLACE VIEW vw_position_lookthrough AS
SELECT
    pl.lookthrough_id,
    pl.as_of_date,
    pl.holding_entity_id,
    e_h.legal_name                          AS holding_entity_name,
    pl.ultimate_instrument_id,
    i.asset_class::TEXT                     AS asset_class,
    i.subtype                               AS instrument_subtype,
    pl.ownership_chain,
    pl.cumulative_ownership_pct,
    pl.attributed_quantity,
    pl.attributed_value_usd,
    pl.direct_position_id,
    pl.direct_quantity,
    pl.direct_value_usd
FROM position_lookthrough pl
JOIN entity   e_h ON e_h.entity_id    = pl.holding_entity_id
JOIN instrument i  ON i.instrument_id = pl.ultimate_instrument_id;