Update relational schema: spec gaps, case mismatches, RI staging, PV fields, account terms, step functions

[benhayes21]

Gap analysis between OEDInputFields.csv and DatabaseProjectOED.sql identified several issues to fix:

1. Add PV fields (PVControlType, PVGlassThickness, PVMaterial, PVMounting, PVPanelArea, PVPanelAreaUnit, PVPanelYear, PVStowing, PVTiltAngle) to _import_location and LocationDetail
2. Implement Account-level financial terms (AccDed*/AccLimit*) - AccountTerm table exists but usp_Terms_Load has a TODO comment with no implementation
3. Fix 122 systematic case mismatches (BI->Bi, PD->Pd, XX->Xx, TIV->Tiv, ID->Id, LOB->Lob etc)
4. Add RI staging tables (_staging_riinfo, _staging_riscope, _import_riinfo, _import_riscope) to SQL schema
5. Fix load_sql.py: remove head(10) on location, uncomment usp_Database_Load call, fix RI table names
6. Add StepPolicy table and load procedure for step-function fields at policy level

Out of scope: cyber fields, occupant demographics, full reinsurance normalised model (separate issue).

Related files: SQL/SQL_Scripts/DatabaseProjectOED.sql, SQL/load_sql.py, OpenExposureData/OEDInputFields.csv

[benhayes21]

Problems found and fixed during implementation

While building the test suite against all 15 OasisLMF validation cases, six ETL bugs were uncovered and fixed. All 77 tests now pass.

---

1. _import_* tables not cleared between runs

Problem: usp_SourceTable_Load does INSERT INTO _import_account SELECT ... FROM _staging_account with no truncation beforehand. The cleanup block only cleared the normalised entity tables (Portfolio, Account, Location, etc.) and the business-key work tables — not the import tables. On repeated loads (or across test cases), _import_account accumulated rows from previous runs.

Effect: insurance_account had 104 distinct accounts in the CSV but Account got 205 rows because _import_account still held rows from the previous test case, inflating the distinct-account count used for Portfolio and the Account → Portfolio join.

Fix: Added TRUNCATE TABLE for _import_location, _import_account, _import_riinfo, _import_riscope at the top of the cleanup block in usp_Database_Load.

---

2. Duplicate ConditionId in usp_Condition_Load

Problem: The join onto _import_account to retrieve condition attributes (CondName, CondPriority, CondClass, CondTag) was on (PortNumber, AccNumber, PolNumber) only — missing CondNumber. When an account row had multiple conditions (e.g., CondNumber='1' and CondNumber='2'), each _businesskeys_condition row cross-joined with all matching _import_account rows, producing multiple rows with the same ConditionId but different attribute values. SELECT DISTINCT couldn't collapse them because the attribute values differed.

Fix: Changed to CROSS APPLY (SELECT TOP 1 ... WHERE isnull(CondNumber,'') = isnull(bkc.CondNumber,'')) so each condition gets exactly one canonical attribute row.

---

3. FK violation on ConditionCoverageTerm, ConditionPDTerm, ConditionTerm

Problem: The INSERT INTO #tmpterm_denormalised for condition levels (4, 5, 6) JOINs ConditionLocation to restrict to conditions that are actually applied to a location. So a condition with no matching ConditionLocation row produces no row in #tmpterm_denormalised, and therefore no row in Term. However, the subsequent INSERT INTO ConditionCoverageTerm/ConditionPDTerm/ConditionTerm read directly from vw_level_4/5/6_term without the same filter — trying to reference TermId values that were never inserted into Term.

Effect: Foreign key violation FK_term_TO_conditioncoverageterm when the insurance_conditions test case (which has conditions not linked to any location via CondTag) was loaded.

Fix: Added WHERE EXISTS (SELECT 1 FROM ConditionLocation CL WHERE CL.ConditionId = t.ConditionId) to all three condition term link inserts.

---

4. ItemTerm PK collision between usp_StepPolicyTerm_Load and usp_Terms_Load

Problem: usp_Database_Load calls usp_StepPolicyTerm_Load before usp_Terms_Load. usp_StepPolicyTerm_Load inserts into ItemTerm using ROW_NUMBER() + ISNULL(MAX(ItemTermId), 0) as the offset (correct). But the first INSERT INTO ItemTerm inside usp_Terms_Load (for loc/pol/layer-level terms) used just ROW_NUMBER() with no offset, so it always started from 1 — colliding with rows already written by usp_StepPolicyTerm_Load in step-function cases.

Fix: Added + ISNULL((SELECT MAX(ItemTermId) FROM ItemTerm), 0) offset to the first INSERT INTO ItemTerm in usp_Terms_Load.

---

5. Multiple narrow column widths causing truncation

Several columns were defined too narrow for real OED validation data:

Table / column	Old width	New width	Longest value found
Condition.CondNumber	VARCHAR(1)	VARCHAR(20)	8 chars
Condition.CondName	VARCHAR(1)	VARCHAR(100)	—
Condition.CondTag	VARCHAR(1)	VARCHAR(20)	—
LocationDetail.LocName + _import_location.LocName	NVARCHAR(20)	NVARCHAR(200)	57 chars
_import_riinfo.ReinsName (×3 tables)	VARCHAR(30)	VARCHAR(200)	36 chars
#tmpterm_denormalised.TermPeril	VARCHAR(50)	VARCHAR(250)	67 chars (insurance_conditions has a 17-peril semicolon-delimited string)

---

6. load_sql.py SA password and EXEC not running

Problem: The SA password was hardcoded in the connection string (a git security risk) and EXEC usp_Database_Load was commented out. Even when uncommented, it silently did nothing because engine.connect() opens a transaction that is never committed — SQL Server's EXEC effects are only visible after commit.

Fix: Password moved to OED_DB_PASSWORD env var; changed to engine.begin() which auto-commits on exit. Added .gitignore entry for .env files.