-- ============================================================
-- EIGIS Database Triggers, Functions & Email Alert System
-- Anomalous progress detection, auto-population, audit trail
-- ============================================================

-- -----------------------------------------------------------
-- 1. AUTO-UPDATE updated_at TIMESTAMP TRIGGER
-- Generic function applied to all tables
-- -----------------------------------------------------------
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Apply to all tables with updated_at
DO $$
DECLARE
    t TEXT;
BEGIN
    FOR t IN
        SELECT table_name FROM information_schema.columns
        WHERE column_name = 'updated_at'
        AND table_schema = 'public'
        AND table_name NOT IN ('audit_log')
    LOOP
        EXECUTE format('
            CREATE TRIGGER set_updated_at
                BEFORE UPDATE ON %I
                FOR EACH ROW
                EXECUTE FUNCTION update_updated_at_column();
        ', t);
    END LOOP;
END;
$$;

-- -----------------------------------------------------------
-- 2. AUDIT LOG TRIGGER FUNCTION
-- Records all INSERT/UPDATE/DELETE operations
-- -----------------------------------------------------------
CREATE OR REPLACE FUNCTION audit_trigger_func()
RETURNS TRIGGER AS $$
DECLARE
    audit_row audit_log%ROWTYPE;
BEGIN
    audit_row := row(
        nextval('audit_log_audit_id_seq'),
        TG_TABLE_NAME,
        COALESCE(NEW.observation_id, NEW.observation_id, OLD.observation_id,
                 NEW.sample_id, NEW.disc_id, NEW.slope_id,
                 NEW.manifestation_id, NEW.soil_profile_id,
                 NEW.rock_desc_id, NEW.project_id, NEW.field_trip_id,
                 COALESCE(NEW, OLD)::uuid),
        TG_OP,
        CASE WHEN TG_OP IN ('UPDATE','DELETE') THEN to_jsonb(OLD) ELSE NULL END,
        CASE WHEN TG_OP IN ('INSERT','UPDATE') THEN to_jsonb(NEW) ELSE NULL END,
        current_user,
        NOW()
    );
    INSERT INTO audit_log VALUES (audit_row.*);
    RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

-- Apply audit triggers to key tables
SELECT apply_audit_trigger(t) FROM unnest(ARRAY[
    'observations', 'soil_profiles', 'soil_horizons', 'rock_descriptions',
    'discontinuity_measurements', 'slope_stability', 'rock_mass_classifications',
    'geothermal_manifestations', 'samples', 'landslide_inventory'
]) AS t;

-- Helper to create audit triggers
CREATE OR REPLACE FUNCTION apply_audit_trigger(tbl TEXT)
RETURNS VOID AS $$
BEGIN
    EXECUTE format('
        CREATE TRIGGER audit_%s
            AFTER INSERT OR UPDATE OR DELETE ON %I
            FOR EACH ROW EXECUTE FUNCTION audit_trigger_func();
    ', tbl, tbl);
END;
$$ LANGUAGE plpgsql;

-- Apply to all key tables
SELECT apply_audit_trigger(t) FROM unnest(ARRAY[
    'observations', 'soil_profiles', 'soil_horizons', 'rock_descriptions',
    'discontinuity_measurements', 'slope_stability', 'rock_mass_classifications',
    'geothermal_manifestations', 'samples', 'landslide_inventory'
]) AS t;

-- -----------------------------------------------------------
-- 3. AUTO-POPULATE ADMIN UNIT FROM GIS BOUNDARY
-- Trigger: When observation is inserted/updated with geom
-- -----------------------------------------------------------
CREATE OR REPLACE FUNCTION auto_populate_admin_unit()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.geom IS NOT NULL AND (NEW.admin_unit IS NULL OR NEW.admin_unit = '') THEN
        -- Attempt to intersect with administrative boundaries layer
        -- This assumes an admin_boundaries table exists or uses a service
        BEGIN
            SELECT name INTO NEW.admin_unit
            FROM admin_boundaries
            WHERE ST_Contains(geom, NEW.geom)
            LIMIT 1;
        EXCEPTION WHEN undefined_table THEN
            NEW.admin_unit := 'Auto · GIS Boundary';
        END;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_auto_admin_unit
    BEFORE INSERT OR UPDATE OF geom ON observations
    FOR EACH ROW EXECUTE FUNCTION auto_populate_admin_unit();

-- -----------------------------------------------------------
-- 4. AUTO-POPULATE WATERSHED FROM DEM LAYER
-- -----------------------------------------------------------
CREATE OR REPLACE FUNCTION auto_populate_watershed()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.geom IS NOT NULL AND (NEW.watershed IS NULL OR NEW.watershed = '') THEN
        BEGIN
            SELECT watershed_name INTO NEW.watershed
            FROM watershed_boundaries
            WHERE ST_Contains(geom, NEW.geom)
            LIMIT 1;
        EXCEPTION WHEN undefined_table THEN
            NEW.watershed := 'Auto · DEM Layer';
        END;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_auto_watershed
    BEFORE INSERT OR UPDATE OF geom ON observations
    FOR EACH ROW EXECUTE FUNCTION auto_populate_watershed();

-- -----------------------------------------------------------
-- 5. AUTO-POPULATE GEOLOGICAL FORMATION FROM GEO MAP
-- -----------------------------------------------------------
CREATE OR REPLACE FUNCTION auto_populate_geology()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.geom IS NOT NULL AND (NEW.geological_formation IS NULL OR NEW.geological_formation = '') THEN
        BEGIN
            SELECT unit_name INTO NEW.geological_formation
            FROM lithological_units
            WHERE ST_Contains(geom, NEW.geom)
            LIMIT 1;
        EXCEPTION WHEN undefined_table THEN
            NEW.geological_formation := 'Auto · Geo Map Layer';
        END;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_auto_geology
    BEFORE INSERT OR UPDATE OF geom ON observations
    FOR EACH ROW EXECUTE FUNCTION auto_populate_geology();

-- -----------------------------------------------------------
-- 6. COMPUTE ROCK MASS CLASSIFICATION SCORES
-- Auto-calculate RMR, Q, and GSI totals
-- -----------------------------------------------------------
CREATE OR REPLACE FUNCTION compute_rock_mass_scores()
RETURNS TRIGGER AS $$
BEGIN
    -- RMR Total
    IF NEW.rmr_strength IS NOT NULL AND NEW.rmq_drill_quality IS NOT NULL
       AND NEW.rmr_spacing IS NOT NULL AND NEW.rmr_condition IS NOT NULL
       AND NEW.rmr_groundwater IS NOT NULL THEN
        NEW.rmr_total := NEW.rmr_strength + NEW.rmr_drill_quality + NEW.rmr_spacing
                       + NEW.rmr_condition + NEW.rmr_groundwater
                       + COALESCE(NEW.rmr_adjustment, 0);

        -- RMR Class
        NEW.rmr_class := CASE
            WHEN NEW.rmr_total >= 81 THEN 'I - Very Good'
            WHEN NEW.rmr_total >= 61 THEN 'II - Good'
            WHEN NEW.rmr_total >= 41 THEN 'III - Fair'
            WHEN NEW.rmr_total >= 21 THEN 'IV - Poor'
            ELSE 'V - Very Poor'
        END;
    END IF;

    -- Q-System Value
    IF NEW.q_rqd IS NOT NULL AND NEW.q_jn IS NOT NULL
       AND NEW.q_jr IS NOT NULL AND NEW.q_ja IS NOT NULL
       AND NEW.q_jw IS NOT NULL AND NEW.q_srf IS NOT NULL THEN
        NEW.q_value := (NEW.q_rqd * NEW.q_jr * NEW.q_jw) /
                       (NEW.q_jn * NEW.q_ja * NEW.q_srf);

        NEW.q_class := CASE
            WHEN NEW.q_value >= 400 THEN 'Exceptionally Good'
            WHEN NEW.q_value >= 100 THEN 'Extremely Good'
            WHEN NEW.q_value >= 40 THEN 'Very Good'
            WHEN NEW.q_value >= 10 THEN 'Good'
            WHEN NEW.q_value >= 4 THEN 'Fair'
            WHEN NEW.q_value >= 1 THEN 'Poor'
            WHEN NEW.q_value >= 0.1 THEN 'Very Poor'
            ELSE 'Exceptionally Poor'
        END;
    END IF;

    -- Deformation Modulus from RMR (Bieniawski, 1978)
    IF NEW.rmr_total IS NOT NULL AND NEW.rmr_total > 0 THEN
        IF NEW.rmr_total > 50 THEN
            NEW.emod_gpa := 2.0 * NEW.rmr_total - 100.0;
        ELSE
            NEW.emod_gpa := 2.0 * POWER(10, (NEW.rmr_total + 10) / 40.0);
        END IF;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_compute_rmc_scores
    BEFORE INSERT OR UPDATE ON rock_mass_classifications
    FOR EACH ROW EXECUTE FUNCTION compute_rock_mass_scores();

-- -----------------------------------------------------------
-- 7. ANOMALOUS PROGRESS DETECTION & EMAIL ALERTS
-- Monitors project progress and triggers alerts
-- -----------------------------------------------------------

-- Alert queue table
CREATE TABLE alert_queue (
    alert_id         BIGSERIAL PRIMARY KEY,
    project_id       UUID NOT NULL REFERENCES projects(project_id),
    alert_type       VARCHAR(50) NOT NULL CHECK (alert_type IN (
        'data_gap','observation_spike','hazard_critical',
        'geothermal_anomaly','sample_failure','progress_delay',
        'spatial_outlier','data_quality_issue'
    )),
    severity         VARCHAR(20) DEFAULT 'warning' CHECK (severity IN ('info','warning','critical','emergency')),
    message          TEXT NOT NULL,
    details          JSONB,
    recipients       TEXT[],                             -- Email addresses
    is_sent          BOOLEAN DEFAULT FALSE,
    sent_at          TIMESTAMPTZ,
    created_at       TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_alert_project ON alert_queue(project_id);
CREATE INDEX idx_alert_sent ON alert_queue(is_sent);
CREATE INDEX idx_alert_severity ON alert_queue(severity);

-- Function: Detect anomalous data gaps
CREATE OR REPLACE FUNCTION detect_data_gaps()
RETURNS TABLE(project_id UUID, gap_type TEXT, gap_details TEXT) AS $$
BEGIN
    RETURN QUERY
    -- Projects with no observations in last 7 days despite being active
    SELECT p.project_id,
           'observation_gap'::TEXT,
           format('No observations recorded in 7+ days for active project %s', p.project_code)
    FROM projects p
    WHERE p.status = 'active'
    AND NOT EXISTS (
        SELECT 1 FROM field_trips ft
        JOIN observations o ON o.field_trip_id = ft.field_trip_id
        WHERE ft.project_id = p.project_id
        AND o.created_at > NOW() - INTERVAL '7 days'
    );

    RETURN QUERY
    -- Observations with missing critical data
    SELECT o.observation_id::UUID,
           'missing_critical_data'::TEXT,
           format('Observation %s at site %s missing GPS coordinates', o.observation_id, o.site_id)
    FROM observations o
    WHERE o.geom IS NULL AND o.status = 'submitted';

    RETURN QUERY
    -- Projects with high hazard observations not yet reviewed
    SELECT sl.observation_id::UUID,
           'unreviewed_hazard'::TEXT,
           format('High hazard slope assessment at site %s not reviewed', o.site_id)
    FROM slope_stability sl
    JOIN observations o ON o.observation_id = sl.observation_id
    WHERE sl.hazard_level IN ('high','very_high','extreme')
    AND o.status NOT IN ('reviewed','approved');
END;
$$ LANGUAGE plpgsql;

-- Function: Detect geothermal anomalies
CREATE OR REPLACE FUNCTION detect_geothermal_anomalies()
RETURNS TABLE(manifestation_id UUID, anomaly_type TEXT, anomaly_details TEXT) AS $$
BEGIN
    RETURN QUERY
    -- Unusually high surface temperatures
    SELECT gm.manifestation_id,
           'high_temperature'::TEXT,
           format('Surface temp %.1f°C exceeds threshold at manifestation near observation %s',
                  gm.surface_temp_c, gm.observation_id)
    FROM geothermal_manifestations gm
    WHERE gm.surface_temp_c > 95.0;  -- Near boiling

    RETURN QUERY
    -- pH anomalies (very acidic or very alkaline)
    SELECT gm.manifestation_id,
           'ph_anomaly'::TEXT,
           format('pH value %.1f outside normal range at observation %s',
                  gm.ph_value, gm.observation_id)
    FROM geothermal_manifestations gm
    WHERE gm.ph_value < 2.0 OR gm.ph_value > 10.0;

    RETURN QUERY
    -- Sudden discharge changes (comparing recent vs historical)
    SELECT gm.manifestation_id,
           'discharge_change'::TEXT,
           format('Discharge rate %.2f L/s significantly changed at observation %s',
                  gm.discharge_rate_lps, gm.observation_id)
    FROM geothermal_manifestations gm
    WHERE gm.discharge_rate_lps IS NOT NULL
    AND gm.discharge_rate_lps > 50.0;  -- Threshold for alert
END;
$$ LANGUAGE plpgsql;

-- Function: Queue email alerts for detected anomalies
CREATE OR REPLACE FUNCTION queue_anomaly_alerts()
RETURNS INTEGER AS $$
DECLARE
    alert_count INTEGER := 0;
    rec RECORD;
BEGIN
    -- Check data gaps
    FOR rec IN SELECT * FROM detect_data_gaps() LOOP
        INSERT INTO alert_queue (project_id, alert_type, severity, message, recipients)
        SELECT rec.project_id, 'data_gap', 'warning', rec.gap_details,
               ARRAY[p.client_email, p.receptionist_email]
        FROM projects p WHERE p.project_id = rec.project_id;
        alert_count := alert_count + 1;
    END LOOP;

    -- Check geothermal anomalies
    FOR rec IN SELECT * FROM detect_geothermal_anomalies() LOOP
        INSERT INTO alert_queue (project_id, alert_type, severity, message, recipients)
        SELECT o.field_trip_id::UUID, 'geothermal_anomaly', 'critical', rec.anomaly_details,
               ARRAY[p.client_email, p.receptionist_email]
        FROM observations o
        JOIN field_trips ft ON ft.field_trip_id = o.field_trip_id
        JOIN projects p ON p.project_id = ft.project_id
        WHERE o.observation_id = (SELECT observation_id FROM geothermal_manifestations WHERE manifestation_id = rec.manifestation_id)
        LIMIT 1;
        alert_count := alert_count + 1;
    END LOOP;

    RETURN alert_count;
END;
$$ LANGUAGE plpgsql;

-- Trigger: Auto-queue alert on critical hazard submission
CREATE OR REPLACE FUNCTION alert_critical_hazard()
RETURNS TRIGGER AS $$
DECLARE
    proj_id UUID;
    client_email TEXT;
    recep_email TEXT;
BEGIN
    IF NEW.hazard_level IN ('high','very_high','extreme') THEN
        SELECT ft.project_id INTO proj_id
        FROM observations o
        JOIN field_trips ft ON ft.field_trip_id = o.field_trip_id
        WHERE o.observation_id = NEW.observation_id;

        SELECT p.client_email, p.receptionist_email INTO client_email, recep_email
        FROM projects p WHERE p.project_id = proj_id;

        INSERT INTO alert_queue (project_id, alert_type, severity, message, recipients)
        VALUES (proj_id, 'hazard_critical', 'critical',
                format('Critical hazard level "%s" detected at observation %s. Immediate review required.',
                       NEW.hazard_level, NEW.observation_id),
                ARRAY[client_email, recep_email]);
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_alert_critical_hazard
    AFTER INSERT OR UPDATE OF hazard_level ON slope_stability
    FOR EACH ROW EXECUTE FUNCTION alert_critical_hazard();

-- Trigger: Auto-queue alert on geothermal anomaly detection
CREATE OR REPLACE FUNCTION alert_geothermal_anomaly()
RETURNS TRIGGER AS $$
DECLARE
    proj_id UUID;
    client_email TEXT;
    recep_email TEXT;
BEGIN
    -- High temperature alert
    IF NEW.surface_temp_c IS NOT NULL AND NEW.surface_temp_c > 95.0 THEN
        SELECT ft.project_id INTO proj_id
        FROM observations o
        JOIN field_trips ft ON ft.field_trip_id = o.field_trip_id
        WHERE o.observation_id = NEW.observation_id;

        SELECT p.client_email, p.receptionist_email INTO client_email, recep_email
        FROM projects p WHERE p.project_id = proj_id;

        INSERT INTO alert_queue (project_id, alert_type, severity, message, recipients)
        VALUES (proj_id, 'geothermal_anomaly', 'critical',
                format('Geothermal anomaly: Surface temperature %.1f°C detected at observation %s',
                       NEW.surface_temp_c, NEW.observation_id),
                ARRAY[client_email, recep_email]);
    END IF;

    -- pH anomaly alert
    IF NEW.ph_value IS NOT NULL AND (NEW.ph_value < 2.0 OR NEW.ph_value > 10.0) THEN
        SELECT ft.project_id INTO proj_id
        FROM observations o
        JOIN field_trips ft ON ft.field_trip_id = o.field_trip_id
        WHERE o.observation_id = NEW.observation_id;

        SELECT p.client_email, p.receptionist_email INTO client_email, recep_email
        FROM projects p WHERE p.project_id = proj_id;

        INSERT INTO alert_queue (project_id, alert_type, severity, message, recipients)
        VALUES (proj_id, 'geothermal_anomaly', 'critical',
                format('Geothermal anomaly: pH %.1f detected at observation %s',
                       NEW.ph_value, NEW.observation_id),
                ARRAY[client_email, recep_email]);
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_alert_geothermal_anomaly
    AFTER INSERT OR UPDATE OF surface_temp_c, ph_value ON geothermal_manifestations
    FOR EACH ROW EXECUTE FUNCTION alert_geothermal_anomaly();

-- -----------------------------------------------------------
-- 8. SPATIAL INDEX MAINTENANCE TRIGGER
-- Re-index after bulk loads for optimal performance
-- -----------------------------------------------------------
CREATE OR REPLACE FUNCTION maintain_spatial_indexes()
RETURNS VOID AS $$
BEGIN
    -- Re-analyze tables with spatial data for query plan optimization
    ANALYZE observations;
    ANALYZE discontinuity_measurements;
    ANALYZE slope_stability;
    ANALYZE geothermal_manifestations;
    ANALYZE lithological_units;
    ANALYZE landslide_inventory;
    ANALYZE samples;
END;
$$ LANGUAGE plpgsql;

-- -----------------------------------------------------------
-- 9. OBSERVATION STATUS TRANSITION VALIDATION
-- Ensures proper workflow: draft → saved → submitted → reviewed → approved
-- -----------------------------------------------------------
CREATE OR REPLACE FUNCTION validate_status_transition()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'UPDATE' AND OLD.status != NEW.status THEN
        -- Validate transition order
        IF NOT (
            (OLD.status = 'draft' AND NEW.status IN ('saved','draft'))
            OR (OLD.status = 'saved' AND NEW.status IN ('submitted','saved','draft'))
            OR (OLD.status = 'submitted' AND NEW.status IN ('reviewed','submitted','saved'))
            OR (OLD.status = 'reviewed' AND NEW.status IN ('approved','reviewed','submitted'))
            OR (OLD.status = 'approved' AND NEW.status = 'approved')
        ) THEN
            RAISE EXCEPTION 'Invalid status transition from % to % for observation %',
                OLD.status, NEW.status, NEW.observation_id;
        END IF;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_validate_status
    BEFORE UPDATE OF status ON observations
    FOR EACH ROW EXECUTE FUNCTION validate_status_transition();

-- -----------------------------------------------------------
-- 10. DASHBOARD MATERIALIZED VIEW - Real-time Metrics
-- Pre-computed for low-latency dashboard rendering
-- -----------------------------------------------------------
CREATE MATERIALIZED VIEW mv_dashboard_metrics AS
SELECT
    p.project_id,
    p.project_code,
    p.project_name,
    p.status AS project_status,
    -- Observation counts by theme
    COUNT(DISTINCT o.observation_id) AS total_observations,
    COUNT(DISTINCT o.observation_id) FILTER (WHERE o.observation_type IN (
        'test_pit','gully','slope_cut','river_valley','natural_exposure','road_cut','quarry_face'
    )) AS surface_geological_count,
    COUNT(DISTINCT o.observation_id) FILTER (WHERE o.observation_type IN (
        'landslide'
    )) OR COUNT(DISTINCT dm.disc_id) > 0 AS structural_count,
    COUNT(DISTINCT o.observation_id) FILTER (WHERE o.observation_type IN (
        'geothermal_spring','fumarole','hot_spring','geyser','mineral_deposit'
    )) AS geothermal_count,
    -- Sub-entity counts
    COUNT(DISTINCT sp.soil_profile_id) AS soil_profiles,
    COUNT(DISTINCT sh.horizon_id) AS soil_horizons,
    COUNT(DISTINCT rd.rock_desc_id) AS rock_descriptions,
    COUNT(DISTINCT dm.disc_id) AS discontinuity_sets,
    COUNT(DISTINCT sl.slope_id) AS slope_assessments,
    COUNT(DISTINCT rmc.classification_id) AS rock_mass_classifications,
    COUNT(DISTINCT gm.manifestation_id) AS geothermal_manifestations,
    COUNT(DISTINCT s.sample_id) AS total_samples,
    COUNT(DISTINCT s.sample_id) FILTER (WHERE s.lab_status = 'completed') AS samples_completed,
    COUNT(DISTINCT ph.photo_id) AS total_photos,
    -- Hazard summary
    COUNT(DISTINCT sl.slope_id) FILTER (WHERE sl.hazard_level = 'high') AS high_hazard_count,
    COUNT(DISTINCT sl.slope_id) FILTER (WHERE sl.hazard_level = 'very_high') AS very_high_hazard_count,
    COUNT(DISTINCT sl.slope_id) FILTER (WHERE sl.hazard_level = 'extreme') AS extreme_hazard_count,
    -- Geothermal stats
    MAX(gm.surface_temp_c) AS max_geothermal_temp,
    AVG(gm.surface_temp_c) AS avg_geothermal_temp,
    -- Landslide stats
    COUNT(DISTINCT li.landslide_id) AS landslide_count,
    -- Recent activity
    MAX(o.created_at) AS last_observation_at,
    -- Spatial extent
    ST_Extent(o.geom) AS bbox
FROM projects p
LEFT JOIN field_trips ft ON ft.project_id = p.project_id
LEFT JOIN observations o ON o.field_trip_id = ft.field_trip_id
LEFT JOIN soil_profiles sp ON sp.observation_id = o.observation_id
LEFT JOIN soil_horizons sh ON sh.soil_profile_id = sp.soil_profile_id
LEFT JOIN rock_descriptions rd ON rd.observation_id = o.observation_id
LEFT JOIN discontinuity_measurements dm ON dm.observation_id = o.observation_id
LEFT JOIN slope_stability sl ON sl.observation_id = o.observation_id
LEFT JOIN rock_mass_classifications rmc ON rmc.observation_id = o.observation_id
LEFT JOIN geothermal_manifestations gm ON gm.observation_id = o.observation_id
LEFT JOIN samples s ON s.observation_id = o.observation_id
LEFT JOIN photos ph ON ph.observation_id = o.observation_id
LEFT JOIN landslide_inventory li ON li.project_id = p.project_id
GROUP BY p.project_id, p.project_code, p.project_name, p.status;

CREATE UNIQUE INDEX idx_mv_dashboard ON mv_dashboard_metrics(project_id);

-- Refresh strategy: every 5 minutes or on demand
-- SELECT pg_catalog.pg_refresh_materialized_view('mv_dashboard_metrics');

-- -----------------------------------------------------------
-- 11. SPATIAL CLUSTER VIEW - For Map Rendering
-- Pre-computed clusters for low-latency vector tile rendering
-- -----------------------------------------------------------
CREATE MATERIALIZED VIEW mv_map_clusters AS
SELECT
    o.observation_id,
    o.site_id,
    o.observation_type,
    o.geom,
    o.elevation_m,
    o.status,
    o.created_at,
    -- Theme flags for styling
    CASE WHEN sp.soil_profile_id IS NOT NULL OR rd.rock_desc_id IS NOT NULL
         THEN TRUE ELSE FALSE END AS has_surface_geological,
    CASE WHEN dm.disc_id IS NOT NULL OR sl.slope_id IS NOT NULL
         THEN TRUE ELSE FALSE END AS has_structural,
    CASE WHEN gm.manifestation_id IS NOT NULL
         THEN TRUE ELSE FALSE END AS has_geothermal,
    -- Quick counts
    (SELECT COUNT(*) FROM samples s WHERE s.observation_id = o.observation_id) AS sample_count,
    (SELECT COUNT(*) FROM photos ph WHERE ph.observation_id = o.observation_id) AS photo_count
FROM observations o
LEFT JOIN soil_profiles sp ON sp.observation_id = o.observation_id
LEFT JOIN rock_descriptions rd ON rd.observation_id = o.observation_id
LEFT JOIN discontinuity_measurements dm ON dm.observation_id = o.observation_id
LEFT JOIN slope_stability sl ON sl.observation_id = o.observation_id
LEFT JOIN geothermal_manifestations gm ON gm.observation_id = o.observation_id;

CREATE UNIQUE INDEX idx_mv_map_clusters_id ON mv_map_clusters(observation_id);
CREATE INDEX idx_mv_map_clusters_geom ON mv_map_clusters USING GIST(geom);