Linear referencing events in PostGIS

-- Check for invalid geometries
SELECT fid, ST_IsValid(geom) as is_valid, 
       CASE WHEN NOT ST_IsValid(geom) 
            THEN ST_IsValidReason(geom) 
            ELSE 'Valid' 
       END as reason
FROM greatpond.trails
WHERE NOT ST_IsValid(geom);

-- Fix invalid geometries if needed
UPDATE greatpond.trails 
SET geom = ST_MakeValid(geom)
WHERE NOT ST_IsValid(geom);

CREATE TABLE IF NOT EXISTS greatpond.obs
(
    id serial,
    name character varying(50),
    "desc" character varying(250),
    severity_int integer,
    size numeric NOT NULL,
--    geom geometry, -- we will load this field with POINTs, not MULTIPOINTs
    PRIMARY KEY (id)
);

COMMENT ON TABLE greatpond.obs
    IS 'Field observations';
 
 -- Add a spatial column to the table
 -- AddGeometryColumn(varchar schema_name, varchar table_name, 
 --   varchar column_name, integer srid, varchar type, integer dimension, boolean use_typmod=true);

SELECT AddGeometryColumn ('greatpond','obs','geom',6348,'POINT',2); -- EPSG:6348 - NAD83(2011) / UTM zone 19N
 
 CREATE INDEX obs_geom_idx
  ON greatpond.obs
  USING GIST (geom);

DROP TABLE IF NOT EXISTS greatpond.events;
CREATE TABLE greatpond.events AS
WITH ordered_nearest AS (
SELECT
  ST_GeometryN(greatpond.trails.geom,1) AS trails_geom, -- Reads in geom. Return the 1-based Nth element geometry of an input geometry.
  greatpond.trails.fid AS trails_fid,
  greatpond.trails.osm_id AS trails_osm_id,
  ST_LENGTH(greatpond.trails.geom) AS trail_length ,
  greatpond.obs.geom AS obs_geom,
  greatpond.obs.size AS obs_size,
  greatpond.obs.id AS obs_id,
  ST_Distance(greatpond.trails.geom, greatpond.obs.geom) AS dist_to_trail
FROM greatpond.trails
  JOIN greatpond.obs
  ON ST_DWithin(greatpond.trails.geom, greatpond.obs.geom, 200) -- Returns true if the geometries are within a given distance, in this case 200m
ORDER BY obs_id, dist_to_trail ASC
);

SELECT
  DISTINCT ON (obs_id)
  obs_id,
  trails_fid,
  trails_osm_id,
  trail_length,
  obs_size,
  ST_LineLocatePoint(trails_geom, obs_geom) AS measure,
  ST_LineLocatePoint(trails_geom, obs_geom) * trail_length AS meas_length,
  dist_to_trail
FROM ordered_nearest;

ALTER TABLE greatpond.events ADD PRIMARY KEY (obs_id);
ALTER TABLE greatpond.events 
 ADD column meas_per_m numeric, 
 ADD column lower_m numeric, 
 ADD column upper_m numeric, 
 ADD column lower_meas numeric, 
 ADD column upper_meas numeric
 ;

update greatpond.events SET
 meas_per_m = measure / meas_length,
 lower_m = meas_length - (obs_size/2),
 upper_m = meas_length + (obs_size/2);
 
update greatpond.events SET
 lower_meas = meas_per_m * lower_m,
 upper_meas = meas_per_m * upper_m -- this field did not update the first time so process as second step.
;

update greatpond.events SET
 lower_meas = 0 where lower_meas < 0;
update greatpond.events SET
 lower_meas = 1 where lower_meas > 1;
update greatpond.events SET
 upper_meas = 1 where upper_meas > 1;
update greatpond.events SET
 upper_meas = 0 where lower_meas < 0;

DROP TABLE IF EXISTS greatpond.event_points;
CREATE table greatpond.event_points AS
SELECT
  ST_LineInterpolatePoint(ST_GeometryN(greatpond.trails.geom, 1), events.measure) AS geom,
  obs_id,
  trails_fid,
  trails_osm_id,
  trail_length,
  measure,
  meas_length,
  obs_size,
  dist_to_trail,
  meas_per_m,
  lower_m ,
  upper_m,
  lower_meas,
  upper_meas
FROM greatpond.events
JOIN greatpond.trails
ON (greatpond.trails.fid = greatpond.events.trails_fid);

DROP TABLE IF EXISTS greatpond.segments;
create table greatpond.segments as (
WITH cuts AS (
    SELECT events.obs_id, events.trails_fid, events.lower_meas, events.upper_meas, 
 ST_GeometryN(trails.geom,1) as geom, trails.osm_id, trails.fid, trails.id 
 from greatpond.trails
 inner join greatpond.events
 ON trails.fid=events.trails_fid order by events.upper_meas 
)
SELECT
 ST_LineSubstring(geom, lower_meas, upper_meas) as mygeom, obs_id, trails_fid, lower_meas, upper_meas
FROM 
    cuts);
 
 ALTER TABLE greatpond.segments ADD column id serial; 
 ALTER TABLE greatpond.segments ADD PRIMARY KEY (id);

create view greatpond.v_segments as (
WITH cuts AS (
    SELECT events.obs_id, events.trails_fid, events.lower_meas, events.upper_meas, 
 ST_GeometryN(trails.geom,1) as geom, trails.osm_id, trails.fid, trails.id 
 from greatpond.trails
 inner join greatpond.events
 ON trails.fid=events.trails_fid order by events.upper_meas 
)
SELECT
 ST_LineSubstring(geom, lower_meas, upper_meas) as mygeom, obs_id, trails_fid, lower_meas, upper_meas
FROM 
    cuts);

-- Create indexes on commonly queried geometry columns
CREATE INDEX idx_trails_geom ON greatpond.trails USING GIST (geom);
CREATE INDEX idx_obs_geom ON greatpond.obs USING GIST (geom);

-- Create a materialized view for frequently accessed segments
CREATE MATERIALIZED VIEW greatpond.mv_segments AS
SELECT * FROM greatpond.segments;

-- Create an index on the materialized view
CREATE INDEX idx_mv_segments_geom ON greatpond.mv_segments USING GIST (mygeom);

-- Refresh when needed
REFRESH MATERIALIZED VIEW greatpond.mv_segments;

-- Find segments that overlap and calculate the overlap length
SELECT 
    a.obs_id as seg1_id,           -- First segment ID
    b.obs_id as seg2_id,           -- Second segment ID
    -- Calculate the length of overlap in meters
    ST_Length(ST_Intersection(a.mygeom, b.mygeom)) as overlap_length,
    -- Get segment details for context
    a.size as seg1_size,
    b.size as seg2_size
FROM greatpond.segments a 
JOIN greatpond.segments b ON ST_Overlaps(a.mygeom, b.mygeom)
WHERE a.obs_id < b.obs_id;  -- Avoid duplicate pairs

-- Summarize maintenance needs by trail
SELECT 
    t.name as trail_name,                                    -- Trail identifier
    COUNT(*) as num_segments,                               -- Number of issues
    SUM(ST_Length(s.mygeom)) as total_maintenance_length,   -- Total length needing repair
    ROUND(SUM(ST_Length(s.mygeom)) / ST_Length(t.geom) * 100, 2) as percent_affected,  -- Percentage of trail affected
    AVG(o.severity_int)::numeric(3,1) as avg_severity      -- Average severity of issues
FROM greatpond.segments s
JOIN greatpond.trails t ON s.trails_fid = t.fid
JOIN greatpond.obs o ON s.obs_id = o.id
GROUP BY t.fid, t.name, t.geom
ORDER BY total_maintenance_length DESC;

-- Identify clusters of maintenance issues
WITH hotspots AS (
    -- Group nearby issues using clustering and buffers
    SELECT 
        ST_Union(ST_Buffer(mygeom, 50)) as cluster_geom,  -- 50m buffer around segments
        COUNT(*) as issue_count,                          -- Number of issues in cluster
        AVG(o.severity_int)::numeric(3,1) as avg_severity,  -- Average severity
        string_agg(o."desc", '; ') as issue_descriptions    -- List all issues
    FROM greatpond.segments s
    JOIN greatpond.obs o ON s.obs_id = o.id
    -- Group issues within 100m of each other
    GROUP BY ST_SnapToGrid(ST_Centroid(s.mygeom), 100)
    HAVING COUNT(*) > 1
)
SELECT 
    issue_count,
    ST_Area(cluster_geom) as affected_area,
    avg_severity,
    issue_descriptions,
    -- Calculate priority score
    (issue_count * avg_severity * ST_Area(cluster_geom))::integer as priority_score
FROM hotspots
ORDER BY priority_score DESC;

-- Create a view for field crews that simplifies data entry
CREATE VIEW greatpond.field_collection AS
SELECT 
    obs.id,
    obs.name,
    obs."desc" as description,
    obs.severity_int,
    obs.size,
    ST_AsGeoJSON(obs.geom) as geometry
FROM greatpond.obs;

-- Create a function to add new observations
CREATE OR REPLACE FUNCTION greatpond.add_observation(
    p_name text,
    p_desc text,
    p_severity int,
    p_size numeric,
    p_geom geometry
) RETURNS integer AS $$
DECLARE
    new_id integer;
BEGIN
    INSERT INTO greatpond.obs (name, "desc", severity_int, size, geom)
    VALUES (p_name, p_desc, p_severity, p_size, p_geom)
    RETURNING id INTO new_id;
    
    RETURN new_id;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION greatpond.get_maintenance_geojson()
RETURNS json AS $$
SELECT jsonb_build_object(
    'type',     'FeatureCollection',
    'features', jsonb_agg(features.feature)
)
FROM (
    SELECT jsonb_build_object(
        'type',       'Feature',
        'geometry',   ST_AsGeoJSON(s.mygeom)::jsonb,
        'properties', to_jsonb(row(s.obs_id, s.trails_fid, e.severity_int)) - 'geom'
    ) AS feature
    FROM greatpond.segments s
    JOIN greatpond.events e ON s.obs_id = e.obs_id
) features;
$$ LANGUAGE SQL;

-- Create a table for maintenance tasks
CREATE TABLE greatpond.maintenance_tasks (
    id serial PRIMARY KEY,
    segment_id integer REFERENCES greatpond.segments(id),
    status text DEFAULT 'pending',
    priority integer,
    assigned_to text,
    estimated_hours numeric,
    created_at timestamp DEFAULT now()
);

-- Create a trigger to automatically create tasks for high-severity issues
CREATE OR REPLACE FUNCTION greatpond.create_maintenance_task()
RETURNS trigger AS $$
BEGIN
    IF NEW.severity_int >= 4 THEN
        INSERT INTO greatpond.maintenance_tasks (
            segment_id,
            priority,
            estimated_hours
        )
        VALUES (
            NEW.id,
            NEW.severity_int,
            NEW.size / 10  -- Rough estimate: 10 meters per hour
        );
        
        -- Notify monitoring systems
        PERFORM pg_notify(
            'maintenance_channel',
            format('New high-priority task created: %s', NEW.id)
        );
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER maintenance_task_creator
AFTER INSERT ON greatpond.segments
FOR EACH ROW
EXECUTE FUNCTION greatpond.create_maintenance_task();

-- Create a view that provides a complete maintenance summary for each trail
CREATE VIEW greatpond.maintenance_report AS
SELECT 
    t.trails_fid,
    COUNT(*) as total_issues,                    -- Total number of maintenance issues
    AVG(e.severity_int) as avg_severity,         -- Average severity (1-5 scale)
    SUM(ST_Length(s.mygeom)) as total_length_meters,  -- Total length needing repair
    SUM(m.estimated_hours) as total_estimated_hours,   -- Estimated work hours
    -- Count completed and pending tasks separately
    COUNT(CASE WHEN m.status = 'completed' THEN 1 END) as completed_tasks,
    COUNT(CASE WHEN m.status = 'pending' THEN 1 END) as pending_tasks
FROM greatpond.segments s
JOIN greatpond.events e ON s.obs_id = e.obs_id           -- Link to original events
JOIN greatpond.trails t ON s.trails_fid = t.fid          -- Link to trail information
LEFT JOIN greatpond.maintenance_tasks m ON s.id = m.segment_id  -- Include task status
GROUP BY t.trails_fid;

-- Check for orphaned observations (no nearby trail)
CREATE OR REPLACE FUNCTION greatpond.check_orphaned_observations()
RETURNS TABLE (
    obs_id integer,
    distance_to_nearest_trail double precision
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        o.id,
        MIN(ST_Distance(o.geom, t.geom)) as min_distance
    FROM greatpond.obs o
    CROSS JOIN LATERAL (
        SELECT geom 
        FROM greatpond.trails 
        ORDER BY o.geom <-> geom 
        LIMIT 1
    ) t
    GROUP BY o.id
    HAVING MIN(ST_Distance(o.geom, t.geom)) > 200;
END;
$$ LANGUAGE plpgsql;

-- Validate segment sizes
CREATE OR REPLACE FUNCTION greatpond.validate_segment_sizes()
RETURNS TABLE (
    segment_id integer,
    recorded_size numeric,
    actual_size numeric,
    difference numeric
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        s.id,
        e.obs_size as recorded_size,
        ST_Length(s.mygeom) as actual_size,
        ABS(e.obs_size - ST_Length(s.mygeom)) as difference
    FROM greatpond.segments s
    JOIN greatpond.events e ON s.obs_id = e.obs_id
    WHERE ABS(e.obs_size - ST_Length(s.mygeom)) > 1; -- 1 meter tolerance
END;
$$ LANGUAGE plpgsql;

-- Check for overlapping segments
CREATE OR REPLACE FUNCTION greatpond.check_segment_overlaps()
RETURNS TABLE (
    segment1_id integer,
    segment2_id integer,
    overlap_length numeric
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        a.id as segment1_id,
        b.id as segment2_id,
        ST_Length(ST_Intersection(a.mygeom, b.mygeom)) as overlap_length
    FROM greatpond.segments a
    JOIN greatpond.segments b ON ST_Overlaps(a.mygeom, b.mygeom)
    WHERE a.id < b.id
    ORDER BY overlap_length DESC;
END;
$$ LANGUAGE plpgsql;

-- Trigger function to validate observation data
CREATE OR REPLACE FUNCTION greatpond.validate_observation()
RETURNS TRIGGER AS $$
BEGIN
    -- Check if size is reasonable
    IF NEW.size <= 0 OR NEW.size > 1000 THEN
        RAISE EXCEPTION 'Invalid size: % meters. Must be between 0 and 1000 meters', NEW.size;
    END IF;
    
    -- Check if point is near any trail
    IF NOT EXISTS (
        SELECT 1 
        FROM greatpond.trails 
        WHERE ST_DWithin(NEW.geom, geom, 200)
    ) THEN
        RAISE WARNING 'Observation % is not within 200m of any trail', NEW.id;
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Create trigger
CREATE TRIGGER obs_validation_trigger
    BEFORE INSERT OR UPDATE ON greatpond.obs
    FOR EACH ROW
    EXECUTE FUNCTION greatpond.validate_observation();

-- Add timestamp columns if not present
ALTER TABLE greatpond.obs 
ADD COLUMN created_at timestamp DEFAULT current_timestamp,
ADD COLUMN updated_at timestamp DEFAULT current_timestamp;

-- Create a function to analyze maintenance patterns
CREATE OR REPLACE FUNCTION greatpond.analyze_maintenance_patterns(
    p_start_date timestamp,
    p_end_date timestamp
)
RETURNS TABLE (
    trail_id integer,
    total_maintenance_length numeric,
    num_issues integer,
    avg_severity numeric,
    maintenance_density numeric
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        t.fid as trail_id,
        SUM(ST_Length(s.mygeom)) as total_maintenance_length,
        COUNT(DISTINCT o.id) as num_issues,
        AVG(o.severity_int::numeric) as avg_severity,
        SUM(ST_Length(s.mygeom)) / ST_Length(t.geom) as maintenance_density
    FROM greatpond.trails t
    JOIN greatpond.segments s ON s.trails_fid = t.fid
    JOIN greatpond.obs o ON s.obs_id = o.id
    WHERE o.created_at BETWEEN p_start_date AND p_end_date
    GROUP BY t.fid, t.geom;
END;
$$ LANGUAGE plpgsql;

-- Create a maintenance cost estimation function
CREATE OR REPLACE FUNCTION greatpond.estimate_maintenance_costs(
    p_labor_rate numeric DEFAULT 50.0,  -- $/hour
    p_material_cost_per_meter numeric DEFAULT 10.0  -- $/meter
)
RETURNS TABLE (
    trail_id integer,
    length_meters numeric,
    estimated_hours numeric,
    labor_cost numeric,
    material_cost numeric,
    total_cost numeric
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        s.trails_fid,
        SUM(ST_Length(s.mygeom)) as length_meters,
        SUM(ST_Length(s.mygeom)) / 10 as estimated_hours,  -- Assume 10 meters per hour
        (SUM(ST_Length(s.mygeom)) / 10) * p_labor_rate as labor_cost,
        SUM(ST_Length(s.mygeom)) * p_material_cost_per_meter as material_cost,
        ((SUM(ST_Length(s.mygeom)) / 10) * p_labor_rate) + 
        (SUM(ST_Length(s.mygeom)) * p_material_cost_per_meter) as total_cost
    FROM greatpond.segments s
    GROUP BY s.trails_fid
    ORDER BY total_cost DESC;
END;
$$ LANGUAGE plpgsql;

-- Create a function to generate optimized work orders
CREATE OR REPLACE FUNCTION greatpond.generate_work_orders(
    p_max_distance numeric DEFAULT 1000.0,  -- meters
    p_min_severity integer DEFAULT 3
)
RETURNS TABLE (
    work_order_id integer,
    segments json,
    total_length numeric,
    estimated_duration interval,
    priority integer
) AS $$
WITH clustered_segments AS (
    SELECT 
        s.id,
        s.obs_id,
        s.trails_fid,
        o.severity_int,
        ST_Length(s.mygeom) as length,
        ST_ClusterDBSCAN(ST_Centroid(s.mygeom), eps := p_max_distance, minpoints := 1) 
            OVER () as cluster_id
    FROM greatpond.segments s
    JOIN greatpond.obs o ON s.obs_id = o.id
    WHERE o.severity_int >= p_min_severity
)
SELECT 
    cluster_id as work_order_id,
    jsonb_agg(jsonb_build_object(
        'segment_id', id,
        'observation_id', obs_id,
        'length', length,
        'severity', severity_int
    ))::json as segments,
    SUM(length) as total_length,
    (SUM(length) / 10 * interval '1 hour') as estimated_duration,
    MAX(severity_int) as priority
FROM clustered_segments
GROUP BY cluster_id
ORDER BY priority DESC, total_length;
$$ LANGUAGE sql;

-- First, install the pgmail extension
CREATE EXTENSION IF NOT EXISTS pgmail;

-- Create a function to send maintenance notifications
CREATE OR REPLACE FUNCTION greatpond.notify_maintenance_team()
RETURNS trigger AS $$
DECLARE
    email_body text;
    trail_name text;
BEGIN
    -- Get trail name
    SELECT name INTO trail_name 
    FROM greatpond.trails 
    WHERE fid = NEW.trails_fid;

    -- Construct email body
    email_body := format(
        'New maintenance task recorded:\n\n' ||
        'Trail: %s\n' ||
        'Location: %s meters from start\n' ||
        'Size: %s meters\n' ||
        'Severity: %s\n' ||
        'Description: %s',
        trail_name,
        NEW.meas_length,
        NEW.obs_size,
        NEW.severity_int,
        NEW.description
    );

    -- Send email
    PERFORM pgmail.send(
        sender := 'trails@organization.com',
        recipient := 'maintenance@organization.com',
        subject := 'New Trail Maintenance Task',
        body := email_body
    );

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Create trigger for new events
CREATE TRIGGER maintenance_notification
AFTER INSERT ON greatpond.events
FOR EACH ROW
EXECUTE FUNCTION greatpond.notify_maintenance_team();

-- Create API schema
CREATE SCHEMA IF NOT EXISTS api;

-- Create observation input type
CREATE TYPE api.observation_input AS (
    name text,
    description text,
    severity_int integer,
    size numeric,
    lat numeric,
    lon numeric
);

-- Create function to add new observations via API
CREATE OR REPLACE FUNCTION api.add_observation(
    input api.observation_input
)
RETURNS json AS $$
DECLARE
    new_id integer;
    result json;
BEGIN
    -- Insert new observation
    INSERT INTO greatpond.obs (
        name,
        "desc",
        severity_int,
        size,
        geom
    )
    VALUES (
        input.name,
        input.description,
        input.severity_int,
        input.size,
        ST_SetSRID(ST_MakePoint(input.lon, input.lat), 6348)
    )
    RETURNING id INTO new_id;

    -- Return response
    SELECT json_build_object(
        'status', 'success',
        'id', new_id,
        'message', 'Observation recorded successfully'
    ) INTO result;

    RETURN result;
END;
$$ LANGUAGE plpgsql;

-- Create function to update existing observations
CREATE OR REPLACE FUNCTION api.update_observation(
    p_id integer,
    input api.observation_input
)
RETURNS json AS $$
DECLARE
    result json;
BEGIN
    UPDATE greatpond.obs
    SET name = input.name,
        "desc" = input.description,
        severity_int = input.severity_int,
        size = input.size,
        geom = ST_SetSRID(ST_MakePoint(input.lon, input.lat), 6348),
        updated_at = CURRENT_TIMESTAMP
    WHERE id = p_id;

    SELECT json_build_object(
        'status', 'success',
        'id', p_id,
        'message', 'Observation updated successfully'
    ) INTO result;

    RETURN result;
END;
$$ LANGUAGE plpgsql;

-- Financial summary view
CREATE OR REPLACE VIEW greatpond.financial_summary AS
WITH maintenance_costs AS (
    SELECT 
        DATE_TRUNC('month', o.created_at) as month,
        t.name as trail_name,
        COUNT(*) as num_repairs,
        SUM(o.size) as total_length,
        SUM(o.size * CASE 
            WHEN o.severity_int >= 4 THEN 150  -- High severity cost per meter
            WHEN o.severity_int >= 2 THEN 100  -- Medium severity
            ELSE 50                            -- Low severity
        END) as estimated_cost
    FROM greatpond.obs o
    JOIN greatpond.events e ON o.id = e.obs_id
    JOIN greatpond.trails t ON e.trails_fid = t.fid
    GROUP BY DATE_TRUNC('month', o.created_at), t.name
)
SELECT 
    month,
    trail_name,
    num_repairs,
    total_length,
    estimated_cost,
    estimated_cost / NULLIF(total_length, 0) as cost_per_meter
FROM maintenance_costs
ORDER BY month DESC, estimated_cost DESC;

-- Maintenance schedule view
CREATE OR REPLACE VIEW greatpond.maintenance_schedule AS
WITH prioritized_tasks AS (
    SELECT 
        o.id,
        t.name as trail_name,
        o.severity_int,
        o.size,
        o."desc" as description,
        e.meas_length as distance_from_start,
        o.created_at,
        CASE 
            WHEN o.severity_int >= 4 THEN 1  -- Immediate attention
            WHEN o.severity_int >= 2 THEN 2  -- Next week
            ELSE 3                           -- Within month
        END as priority,
        CASE 
            WHEN o.severity_int >= 4 THEN o.created_at + INTERVAL '2 days'
            WHEN o.severity_int >= 2 THEN o.created_at + INTERVAL '7 days'
            ELSE o.created_at + INTERVAL '30 days'
        END as target_completion_date
    FROM greatpond.obs o
    JOIN greatpond.events e ON o.id = e.obs_id
    JOIN greatpond.trails t ON e.trails_fid = t.fid
    WHERE o.id NOT IN (SELECT obs_id FROM greatpond.completed_maintenance)
)
SELECT 
    id as task_id,
    trail_name,
    severity_int,
    size as repair_length_meters,
    description,
    distance_from_start,
    created_at,
    priority,
    target_completion_date,
    CASE 
        WHEN CURRENT_DATE > target_completion_date THEN 'OVERDUE'
        WHEN CURRENT_DATE = target_completion_date THEN 'DUE TODAY'
        ELSE format('%s days', target_completion_date - CURRENT_DATE)
    END as status
FROM prioritized_tasks
ORDER BY priority, target_completion_date;

-- Trail condition overview
CREATE OR REPLACE VIEW greatpond.trail_condition_summary AS
SELECT 
    t.name as trail_name,
    COUNT(o.id) as total_issues,
    AVG(o.severity_int)::numeric(3,1) as avg_severity,
    SUM(o.size) as total_repair_length,
    ROUND((SUM(o.size) / ST_Length(t.geom) * 100)::numeric, 2) as percent_needs_repair,
    MAX(o.severity_int) as max_severity,
    COUNT(CASE WHEN o.severity_int >= 4 THEN 1 END) as critical_issues,
    MAX(o.created_at) as latest_observation
FROM greatpond.trails t
LEFT JOIN greatpond.events e ON t.fid = e.trails_fid
LEFT JOIN greatpond.obs o ON e.obs_id = o.id
GROUP BY t.name, t.geom
ORDER BY avg_severity DESC NULLS LAST;

CREATE OR REPLACE FUNCTION greatpond.generate_weekly_report()
RETURNS void AS $$
DECLARE
    report_body text;
BEGIN
    -- Generate report content
    WITH report_data AS (
        SELECT 
            COUNT(*) as new_issues,
            SUM(CASE WHEN severity_int >= 4 THEN 1 ELSE 0 END) as critical_issues,
            SUM(size) as total_repair_length,
            SUM(size * CASE 
                WHEN severity_int >= 4 THEN 150
                WHEN severity_int >= 2 THEN 100
                ELSE 50
            END) as estimated_cost
        FROM greatpond.obs
        WHERE created_at >= CURRENT_DATE - INTERVAL '7 days'
    )
    SELECT format(
        'Weekly Trail Maintenance Report\n\n' ||
        'New Issues: %s\n' ||
        'Critical Issues: %s\n' ||
        'Total Repair Length: %s meters\n' ||
        'Estimated Cost: $%s\n\n' ||
        'See attached detailed report for more information.',
        new_issues, critical_issues, total_repair_length, estimated_cost
    ) INTO report_body
    FROM report_data;

    -- Send email with report
    PERFORM pgmail.send(
        sender := 'trails@organization.com',
        recipient := 'management@organization.com',
        subject := 'Weekly Trail Maintenance Report',
        body := report_body
    );
END;
$$ LANGUAGE plpgsql;