-- 88_nspd_quarter_dumps.sql
-- Context : Persistent storage for NSPD quarter-level harvests (QuarterDump).
--           Each successful NSPDClient.search_by_quarter(cad_quarter) snapshot is
--           upserted here. Downstream: analyze_parcel reads this table for G1/G3/P2/E1
--           scoring without making fresh NSPD requests in the request cycle.
--           Celery task harvest_quarter (Sprint 1.1 item #3) writes batches here.
-- Dependencies:
--   - PostGIS extension (geometry types, ST_MakeEnvelope, GIST indexes)
--   - Standalone: no FK references to other tables
-- Deploy order: standalone, safe to apply before any backend code changes.
-- Idempotent: yes — IF NOT EXISTS on table and all indexes.
-- Related:
--   - backend/app/services/scrapers/nspd_client.py :: QuarterDump dataclass
--   - Sprint4_SiteFinder_v4.md :: Phase 0 #94 item #3

BEGIN;

-- ── Main table ────────────────────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS nspd_quarter_dumps (
    -- Natural primary key: one authoritative dump per cadastral quarter.
    -- 3-segment format: '66:41:0204016' (region:district:quarter).
    -- Updated in-place on re-harvest (UPSERT replaces stale data).
    quarter_cad         TEXT PRIMARY KEY,

    -- Quarter polygon in WGS84. Transformed from EPSG:3857 (NSPD native) on
    -- ingest. Nullable: quarter may be absent in NSPD (bbox_3857 also NULL then).
    quarter_geom        GEOMETRY(MultiPolygon, 4326),

    -- Bounding box of the quarter in Web Mercator (EPSG:3857) — as returned by
    -- the NSPD REST API. Stored untransformed for efficient spatial filtering via
    -- GIST without ST_Transform overhead per row. Use for:
    --   ST_Intersects(bbox_3857, ST_MakeEnvelope(xmin,ymin,xmax,ymax,3857))
    -- Nullable when quarter_geom is NULL.
    bbox_3857           GEOMETRY(Polygon, 3857),

    -- Per-layer feature counts. Denormalised from features_json for fast
    -- statistical queries without unpacking JSONB. Filled on every upsert.
    parcels_count       INT NOT NULL DEFAULT 0,
    buildings_count     INT NOT NULL DEFAULT 0,
    territorial_zones_count INT NOT NULL DEFAULT 0,
    red_lines_count     INT NOT NULL DEFAULT 0,
    engineering_count   INT NOT NULL DEFAULT 0,
    -- Sum of features across all 5 ЗОУИТ groups (zouit.okn/engineering/natural/
    -- protected/other from QuarterDump.zouit dict).
    zouit_count         INT NOT NULL DEFAULT 0,
    -- Sum of features across all 11 risk groups (QuarterDump.risks dict).
    risks_count         INT NOT NULL DEFAULT 0,
    -- Convenience total = sum of all counts above. Mirrors QuarterDump.total_features
    -- property. Useful for freshness ranking: ORDER BY total_features DESC.
    total_features      INT NOT NULL DEFAULT 0,

    -- Serialised array of NSPDFeature objects. Each element:
    --   {
    --     "layer":       "parcels" | "buildings" | "territorial_zones" |
    --                    "red_lines" | "engineering_structures" |
    --                    "zouit_okn" | "zouit_engineering" | "zouit_natural" |
    --                    "zouit_protected" | "zouit_other" |
    --                    "risk_flooding_underground" | "risk_flooding" | ...
    --     "feature_id":  <string> | null,
    --     "geometry":    <GeoJSON dict in EPSG:3857> | null,
    --     "properties":  { <NSPD attribute dict> }
    --   }
    -- All ~117 objects per typical quarter stored here. Downstream consumers
    -- (analyze_parcel) parse and filter by "layer" key. JSONB supports containment
    -- queries: features_json @> '[{"layer":"buildings"}]' for partial selects.
    features_json       JSONB NOT NULL DEFAULT '[]'::jsonb,

    -- List of phase/layer names actually fetched during this harvest cycle.
    -- Maps to QuarterDump.layers_fetched tuple. Always starts with 'search'.
    -- Example: ARRAY['search','parcels','buildings','territorial_zones',
    --                'red_lines','engineering_structures',
    --                'zouit_okn','zouit_engineering','zouit_natural',
    --                'zouit_protected','zouit_other']
    -- Used for freshness audit: if 'zouit_okn' absent, G3 data may be incomplete.
    layers_fetched      TEXT[] NOT NULL DEFAULT '{}',

    -- UTC timestamp when this harvest was completed (ISO 8601 from Python's
    -- datetime.now(UTC).isoformat()). Used for staleness detection by the
    -- beat scheduler: re-harvest quarters older than configured threshold.
    fetched_at_utc      TIMESTAMPTZ NOT NULL,

    -- How long the full harvest took in milliseconds. Optional — collected by
    -- Celery task for capacity planning (rate-limit tuning, ETA estimation).
    harvest_duration_ms INT,

    -- If the last harvest attempt failed (WAF 429, timeout, parse error), the
    -- error message is stored here. NULL means the dump is healthy and current.
    -- Celery task sets this on exception and writes partial data OR skips upsert
    -- (caller decides). Monitoring query: WHERE harvest_error IS NOT NULL.
    harvest_error       TEXT,

    -- Region code for multi-region scaling. 66 = Sverdlovsk oblast.
    -- Future partitioning key if table exceeds ~50k rows.
    region_code         SMALLINT NOT NULL
);

COMMENT ON TABLE nspd_quarter_dumps IS
    'Persistent snapshots of NSPD harvest results per cadastral quarter. '
    'One row per quarter_cad (natural PK). Updated on every re-harvest. '
    'Foundation for offline Gate-scoring (G1 territorial zones, G3 ЗОУИТ, '
    'P2 neighboring buildings, E1 engineering networks) without live NSPD calls. '
    'Written by Celery task harvest_quarter; read by analyze_parcel service.';

COMMENT ON COLUMN nspd_quarter_dumps.quarter_cad IS
    '3-segment cadastral number of the quarter: region:district:quarter '
    '(e.g. ''66:41:0204016''). Natural PK — one authoritative dump per quarter.';

COMMENT ON COLUMN nspd_quarter_dumps.quarter_geom IS
    'Quarter boundary polygon in WGS84 (EPSG:4326). '
    'Transformed from NSPD-native EPSG:3857 on ingest via ST_Transform. '
    'NULL when NSPD returns no geometry for this quarter.';

COMMENT ON COLUMN nspd_quarter_dumps.bbox_3857 IS
    'Bounding box of the quarter in EPSG:3857 Web Mercator (as returned by NSPD). '
    'Stored untransformed so GIST spatial queries can run without per-row ST_Transform. '
    'Use: ST_Intersects(bbox_3857, ST_MakeEnvelope(x1,y1,x2,y2,3857)).';

COMMENT ON COLUMN nspd_quarter_dumps.features_json IS
    'JSONB array of all NSPDFeature objects across all fetched layers. '
    'Each element has keys: layer (string), feature_id (string|null), '
    'geometry (GeoJSON dict in EPSG:3857), properties (dict). '
    'Geometry remains in EPSG:3857 as returned by NSPD to avoid lossy re-serialisation; '
    'callers must apply ST_Transform / pyproj when needed.';

COMMENT ON COLUMN nspd_quarter_dumps.layers_fetched IS
    'Array of layer/phase names actually fetched in this harvest. '
    'Always starts with ''search''. Missing entries mean data was not collected '
    '(e.g. include_risks=False → no risk_* entries). Used for audit/freshness.';

COMMENT ON COLUMN nspd_quarter_dumps.harvest_error IS
    'Last harvest error message (WAF 429, timeout, parse error). '
    'NULL = dump is healthy. Non-NULL = stale/partial data; re-harvest needed.';

COMMENT ON COLUMN nspd_quarter_dumps.region_code IS
    'Region code (OKTMO/FIAS prefix). 66 = Sverdlovsk oblast. '
    'Future partitioning key for multi-region deployments.';

-- ── Indexes ───────────────────────────────────────────────────────────────────

-- GIST spatial index on bbox_3857: enables fast "find all quarters overlapping
-- area X" queries used by the Celery scheduler and admin map view.
-- Example: SELECT quarter_cad FROM nspd_quarter_dumps
--           WHERE ST_Intersects(bbox_3857, ST_MakeEnvelope(6165000,7450000,6190000,7480000,3857));
-- Expected plan: Index Scan using nspd_quarter_dumps_bbox_gist on nspd_quarter_dumps
CREATE INDEX IF NOT EXISTS nspd_quarter_dumps_bbox_gist
    ON nspd_quarter_dumps USING GIST (bbox_3857);

-- GIST spatial index on quarter_geom (WGS84): for joins with other 4326 tables
-- (ekb_districts_geom, pzz_zones_ekb). Less critical than bbox_3857 index.
CREATE INDEX IF NOT EXISTS nspd_quarter_dumps_geom_gist
    ON nspd_quarter_dumps USING GIST (quarter_geom);

-- B-tree on fetched_at_utc DESC: used by the staleness monitor and Celery beat
-- to find quarters needing re-harvest.
-- Example: SELECT quarter_cad FROM nspd_quarter_dumps
--           WHERE fetched_at_utc < NOW() - INTERVAL '30 days';
-- Expected plan: Index Scan Backward using nspd_quarter_dumps_fetched_idx
CREATE INDEX IF NOT EXISTS nspd_quarter_dumps_fetched_idx
    ON nspd_quarter_dumps (fetched_at_utc DESC);

-- B-tree on region_code: partition-like filter for future multi-region queries.
-- Small cardinality (66, 74, ...) — enables fast per-region dashboard queries.
CREATE INDEX IF NOT EXISTS nspd_quarter_dumps_region_idx
    ON nspd_quarter_dumps (region_code);

-- Partial index on healthy dumps only. Optimises the common monitoring query
-- "how many valid dumps do we have?" and analyze_parcel cache lookup which
-- should skip failed records.
-- Example: SELECT COUNT(*) FROM nspd_quarter_dumps WHERE harvest_error IS NULL;
CREATE INDEX IF NOT EXISTS nspd_quarter_dumps_healthy_idx
    ON nspd_quarter_dumps (fetched_at_utc DESC)
    WHERE harvest_error IS NULL;

-- ── Freshness monitoring view ─────────────────────────────────────────────────

CREATE OR REPLACE VIEW v_quarter_dumps_freshness AS
SELECT
    quarter_cad,
    region_code,
    fetched_at_utc,
    total_features,
    parcels_count,
    buildings_count,
    zouit_count,
    harvest_error,
    harvest_duration_ms,
    AGE(NOW(), fetched_at_utc)              AS age,
    harvest_error IS NOT NULL               AS is_failed,
    layers_fetched
FROM nspd_quarter_dumps
ORDER BY fetched_at_utc DESC;

COMMENT ON VIEW v_quarter_dumps_freshness IS
    'Admin/monitoring view: quarter dump inventory with computed age and health flag. '
    'Rows ordered by most recent first. is_failed=true → re-harvest candidate.';

COMMIT;
