-- Wuhan Children's Respiratory Disease Risk Prediction - PostGIS Schema
-- Database: wuhan_risk
-- Created: 2026-04-25

-- Enable PostGIS extension
CREATE EXTENSION IF NOT EXISTS postgis;
CREATE EXTENSION IF NOT EXISTS postgis_topology;

-- Drop existing tables if they exist (for re-deployment)
DROP TABLE IF EXISTS alerts CASCADE;
DROP TABLE IF EXISTS risk_predictions CASCADE;
DROP TABLE IF EXISTS medical_daily CASCADE;
DROP TABLE IF EXISTS weather_daily CASCADE;
DROP TABLE IF EXISTS road_edges CASCADE;
DROP TABLE IF EXISTS road_nodes CASCADE;
DROP TABLE IF EXISTS wuhan_districts CASCADE;

-- ============================================================================
-- Table: wuhan_districts
-- Description: Wuhan administrative district boundaries
-- Source: Datas/武汉市.geojson
-- ============================================================================
CREATE TABLE wuhan_districts (
    district_code VARCHAR(6) PRIMARY KEY,
    district_name VARCHAR(100) NOT NULL,
    adcode VARCHAR(6) NOT NULL,
    geom GEOMETRY(MultiPolygon, 4326) NOT NULL,
    area_km2 NUMERIC(10, 2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Spatial index on district boundaries
CREATE INDEX idx_wuhan_districts_geom ON wuhan_districts USING GIST (geom);

-- ============================================================================
-- Table: road_nodes
-- Description: Road network nodes (intersections + segment midpoints)
-- Source: OSM Hubei extract, filtered to Wuhan boundary
-- ============================================================================
CREATE TABLE road_nodes (
    osmid BIGINT PRIMARY KEY,
    node_type VARCHAR(20) NOT NULL CHECK (node_type IN ('intersection', 'midpoint')),
    lat NUMERIC(10, 8) NOT NULL,
    lon NUMERIC(11, 8) NOT NULL,
    elevation_m NUMERIC(8, 2),
    pop_density NUMERIC(10, 2),
    district_code VARCHAR(6),
    highway_tag VARCHAR(50),
    node_degree INTEGER DEFAULT 0,
    geom GEOMETRY(Point, 4326) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Spatial index on road nodes
CREATE INDEX idx_road_nodes_geom ON road_nodes USING GIST (geom);
CREATE INDEX idx_road_nodes_district ON road_nodes (district_code);

-- ============================================================================
-- Table: road_edges
-- Description: Road network edges (road segments between nodes)
-- Source: OSM Hubei extract
-- ============================================================================
CREATE TABLE road_edges (
    edge_id BIGINT PRIMARY KEY,
    source_osmid BIGINT NOT NULL REFERENCES road_nodes(osmid),
    target_osmid BIGINT NOT NULL REFERENCES road_nodes(osmid),
    road_type VARCHAR(50) NOT NULL,
    road_type_abbrev VARCHAR(10),
    length_m NUMERIC(10, 2) NOT NULL,
    speed_limit_kmh INTEGER,
    weight NUMERIC(10, 6) NOT NULL,
    geometry GEOMETRY(LineString, 4326) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Spatial index on road edges
CREATE INDEX idx_road_edges_geometry ON road_edges USING GIST (geometry);
CREATE INDEX idx_road_edges_source ON road_edges (source_osmid);
CREATE INDEX idx_road_edges_target ON road_edges (target_osmid);

-- ============================================================================
-- Table: weather_daily
-- Description: Daily aggregated weather and air quality data per station
-- Source: Datas/气象 + 空气/站点_YYYYMMDD-YYYYMMDD/*.csv
-- ============================================================================
CREATE TABLE weather_daily (
    id BIGSERIAL PRIMARY KEY,
    date DATE NOT NULL,
    station_id VARCHAR(10) NOT NULL,
    district_code VARCHAR(6),
    lat NUMERIC(10, 8),
    lon NUMERIC(11, 8),
    aqi NUMERIC(6, 2),
    pm25 NUMERIC(8, 2),
    pm10 NUMERIC(8, 2),
    so2 NUMERIC(8, 2),
    no2 NUMERIC(8, 2),
    o3 NUMERIC(8, 2),
    co NUMERIC(8, 2),
    nox NUMERIC(8, 2),
    so2_24h NUMERIC(8, 2),
    no2_24h NUMERIC(8, 2),
    o3_8h NUMERIC(8, 2),
    co_24h NUMERIC(8, 2),
    pm10_24h NUMERIC(8, 2),
    pm25_24h NUMERIC(8, 2),
    primary_pollutant VARCHAR(50),
    air_quality_level VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(date, station_id)
);

-- Indexes for efficient querying
CREATE INDEX idx_weather_daily_date ON weather_daily (date);
CREATE INDEX idx_weather_daily_station ON weather_daily (station_id);
CREATE INDEX idx_weather_daily_district ON weather_daily (district_code);
CREATE INDEX idx_weather_daily_date_station ON weather_daily (date, station_id);

-- ============================================================================
-- Table: medical_daily
-- Description: Daily aggregated medical visits per district
-- Source: Datas/view_门诊.xlsx, Datas/view_住院.xlsx
-- ============================================================================
CREATE TABLE medical_daily (
    id BIGSERIAL PRIMARY KEY,
    date DATE NOT NULL,
    district_code VARCHAR(6) NOT NULL,
    outpatient_count INTEGER NOT NULL DEFAULT 0,
    inpatient_count INTEGER NOT NULL DEFAULT 0,
    respiratory_outpatient INTEGER NOT NULL DEFAULT 0,
    respiratory_inpatient INTEGER NOT NULL DEFAULT 0,
    total_visits INTEGER GENERATED ALWAYS AS (outpatient_count + inpatient_count) STORED,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(date, district_code)
);

-- Indexes for efficient querying
CREATE INDEX idx_medical_daily_date ON medical_daily (date);
CREATE INDEX idx_medical_daily_district ON medical_daily (district_code);
CREATE INDEX idx_medical_daily_date_district ON medical_daily (date, district_code);

-- ============================================================================
-- Table: risk_predictions
-- Description: Model predictions for disease risk per road node
-- Source: Model inference output
-- ============================================================================
CREATE TABLE risk_predictions (
    id BIGSERIAL PRIMARY KEY,
    date DATE NOT NULL,
    osmid BIGINT NOT NULL REFERENCES road_nodes(osmid),
    district_code VARCHAR(6) NOT NULL,
    risk_1d NUMERIC(5, 4) NOT NULL CHECK (risk_1d >= 0 AND risk_1d <= 1),
    risk_3d NUMERIC(5, 4) NOT NULL CHECK (risk_3d >= 0 AND risk_3d <= 1),
    risk_7d NUMERIC(5, 4) NOT NULL CHECK (risk_7d >= 0 AND risk_7d <= 1),
    risk_level VARCHAR(10) NOT NULL CHECK (risk_level IN ('green', 'yellow', 'orange', 'red')),
    lat NUMERIC(10, 8) NOT NULL,
    lon NUMERIC(11, 8) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(date, osmid)
);

-- Indexes for efficient querying
CREATE INDEX idx_risk_predictions_date ON risk_predictions (date);
CREATE INDEX idx_risk_predictions_osmid ON risk_predictions (osmid);
CREATE INDEX idx_risk_predictions_district ON risk_predictions (district_code);
CREATE INDEX idx_risk_predictions_level ON risk_predictions (risk_level);
CREATE INDEX idx_risk_predictions_date_district ON risk_predictions (date, district_code);

-- ============================================================================
-- Table: alerts
-- Description: Generated alerts based on risk predictions and medical data
-- Source: Alert engine
-- ============================================================================
CREATE TABLE alerts (
    alert_id BIGSERIAL PRIMARY KEY,
    alert_type VARCHAR(20) NOT NULL CHECK (alert_type IN ('monitoring', 'warning')),
    alert_level VARCHAR(10) NOT NULL CHECK (alert_level IN ('yellow', 'orange', 'red')),
    date DATE NOT NULL,
    district_code VARCHAR(6) NOT NULL,
    osmid BIGINT REFERENCES road_nodes(osmid),
    trigger_source VARCHAR(50) NOT NULL,
    trigger_value NUMERIC(10, 4),
    threshold NUMERIC(10, 4),
    description TEXT,
    acknowledged BOOLEAN DEFAULT FALSE,
    acknowledged_at TIMESTAMP,
    acknowledged_by VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Indexes for efficient querying
CREATE INDEX idx_alerts_date ON alerts (date);
CREATE INDEX idx_alerts_district ON alerts (district_code);
CREATE INDEX idx_alerts_level ON alerts (alert_level);
CREATE INDEX idx_alerts_type ON alerts (alert_type);
CREATE INDEX idx_alerts_acknowledged ON alerts (acknowledged);
CREATE INDEX idx_alerts_date_district ON alerts (date, district_code);

-- ============================================================================
-- Comments for documentation
-- ============================================================================
COMMENT ON TABLE wuhan_districts IS 'Wuhan administrative district boundaries from GeoJSON';
COMMENT ON TABLE road_nodes IS 'Road network nodes (intersections and segment midpoints) from OSM';
COMMENT ON TABLE road_edges IS 'Road network edges with weights for graph traversal';
COMMENT ON TABLE weather_daily IS 'Daily aggregated weather and air quality data per monitoring station';
COMMENT ON TABLE medical_daily IS 'Daily aggregated outpatient and inpatient counts per district';
COMMENT ON TABLE risk_predictions IS 'GCN+Transformer model predictions for 1/3/7 day disease risk';
COMMENT ON TABLE alerts IS 'Generated alerts from monitoring (medical) and warning (risk prediction) systems';

COMMENT ON COLUMN road_nodes.node_type IS 'intersection: OSM node where roads meet; midpoint: center point of road segment';
COMMENT ON COLUMN road_edges.weight IS 'Edge weight: 1/length_km for road segments, 60/speed_limit for highways';
COMMENT ON COLUMN weather_daily.station_id IS 'Monitoring station ID (e.g., 1001A, 1002A)';
COMMENT ON COLUMN risk_predictions.risk_level IS 'Risk level: green (<0.3), yellow (0.3-0.5), orange (0.5-0.7), red (>0.7)';
COMMENT ON COLUMN alerts.alert_type IS 'monitoring: triggered by medical data z-scores; warning: triggered by risk predictions';
COMMENT ON COLUMN alerts.trigger_source IS 'Source of alert trigger (e.g., outpatient_z, inpatient_z, risk_3d, risk_7d)';

-- ============================================================================
-- Load Wuhan districts from GeoJSON (requires ogr2ogr or manual import)
-- Alternative: Use COPY command with pre-processed CSV
-- ============================================================================
-- Example: Import districts (run after processing GeoJSON to CSV)
-- COPY wuhan_districts (district_code, district_name, adcode, geom)
-- FROM '/path/to/wuhan_districts.csv' WITH (FORMAT csv, HEADER true);

-- ============================================================================
-- Helper Views
-- ============================================================================

-- View: Latest risk predictions per node
CREATE OR REPLACE VIEW v_latest_risk AS
SELECT rp.*
FROM risk_predictions rp
INNER JOIN (
    SELECT osmid, MAX(date) as max_date
    FROM risk_predictions
    GROUP BY osmid
) latest ON rp.osmid = latest.osmid AND rp.date = latest.max_date;

-- View: Active alerts (unacknowledged)
CREATE OR REPLACE VIEW v_active_alerts AS
SELECT *
FROM alerts
WHERE acknowledged = FALSE
ORDER BY 
    CASE alert_level 
        WHEN 'red' THEN 1 
        WHEN 'orange' THEN 2 
        WHEN 'yellow' THEN 3 
    END,
    date DESC;

-- View: District-level risk summary
CREATE OR REPLACE VIEW v_district_risk_summary AS
SELECT 
    date,
    district_code,
    COUNT(*) as node_count,
    AVG(risk_1d) as avg_risk_1d,
    AVG(risk_3d) as avg_risk_3d,
    AVG(risk_7d) as avg_risk_7d,
    SUM(CASE WHEN risk_level = 'green' THEN 1 ELSE 0 END) as green_count,
    SUM(CASE WHEN risk_level = 'yellow' THEN 1 ELSE 0 END) as yellow_count,
    SUM(CASE WHEN risk_level = 'orange' THEN 1 ELSE 0 END) as orange_count,
    SUM(CASE WHEN risk_level = 'red' THEN 1 ELSE 0 END) as red_count
FROM risk_predictions
GROUP BY date, district_code;

-- ============================================================================
-- Grant permissions (adjust as needed)
-- ============================================================================
-- GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;
-- GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO app_user;
-- GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO app_user;

-- ============================================================================
-- Schema deployment complete
-- ============================================================================
