[Feature] Site type as controlled vocabulary (SiteType lookup table)

[jeandavidt]

Problem

Site.Type is currently a free-text NVARCHAR(255) column. This leads to inconsistent data (e.g., "WWTP", "Wastewater Treatment Plant", "wwtp" all meaning the same thing). Site type should be a FK to a controlled-vocabulary lookup table, consistent with how other reference data is handled in this schema.

Required Changes

1. Schema migration

• Create SiteType table: SiteType_ID INT IDENTITY PK, Name NVARCHAR(100) NOT NULL UNIQUE, Description NVARCHAR(MAX)
• Add SiteType_ID INT NULL FK → SiteType column to Site
• Drop Site.Type NVARCHAR(255) column
• Provide forward migration script and rollback script per project convention

2. Seed data

Seed SiteType with at minimum:

• Wastewater Treatment Plant
• Combined Sewer Overflow
• River / Stream
• Lake / Reservoir
• Groundwater / Well
• Drinking Water Distribution Network
• Other

3. Schema dictionary

• Add schema_dictionary/tables/site_type.yaml entry

4. API

• Add GET /site-types endpoint returning the lookup list
• Update SiteIn / SiteOut Pydantic schemas: replace type: str with site_type_id: int | None
• Update site_repository.py queries to JOIN SiteType for display

5. Frontend

• Replace the free-text type input in app/pages/1_Sites.py with a dropdown populated from GET /site-types
• Update app/api_client.py to add list_site_types() function

Acceptance Criteria

• SiteType table exists with ≥ 7 seed values
• Site table has SiteType_ID FK (nullable for backward compat)
• Migration script + rollback script provided in migrations/
• GET /site-types returns the full list
• Site creation/edit form shows a dropdown of site types
• schema_dictionary/tables/site_type.yaml added

Files to Modify

• migrations/ — new forward + rollback migration scripts
• sql_generation_scripts/v3.0.0_create_mssql.sql — add SiteType table, update Site
• sql/seed_v2.2.0.sql — add SiteType seed inserts, update existing Site rows
• schema_dictionary/tables/site_type.yaml — new dictionary entry
• api/v1/endpoints/sites.py — add GET /site-types route
• api/v1/schemas/metadata.py — update SiteIn / SiteOut
• api/v1/repositories/site_repository.py — update queries
• app/pages/1_Sites.py — dropdown instead of free-text input
• app/api_client.py — add list_site_types()