-- Geography: accurate distances in meters for GPS data
SELECT ST_Distance(
    ST_MakePoint(-87.6298, 41.8781)::geography,  -- Chicago
    ST_MakePoint(-122.4194, 37.7749)::geography  -- San Francisco
) / 1609.34 AS distance_miles;
-- Returns: ~1856 miles (accurate)

-- Geometry: faster but less accurate for large distances
SELECT ST_Distance(
    ST_SetSRID(ST_MakePoint(-87.6298, 41.8781), 4326),
    ST_SetSRID(ST_MakePoint(-122.4194, 37.7749), 4326)
) AS distance_degrees;
-- Returns: degrees (must convert, less accurate for long distances)

-- Creating a point from GPS coordinates
ST_SetSRID(ST_MakePoint(longitude, latitude), 4326)

-- Creating a geography point (preferred for distance calculations)
ST_SetSRID(ST_MakePoint(longitude, latitude), 4326)::geography

-- Example: Create point for a facility location
ST_SetSRID(ST_MakePoint(-87.6298, 41.8781), 4326)::geography

-- Migration: Create geofences table

CREATE TABLE public.geofences (
    -- Primary key
    id UUID DEFAULT gen_random_uuid() NOT NULL,

    -- Identification
    name TEXT NOT NULL,
    description TEXT,

    -- Relationship to facility (optional - standalone geofences allowed)
    facility_id UUID REFERENCES facilities(id) ON DELETE SET NULL,

    -- Spatial boundary - geography type for accurate distance calculations
    boundary geography(Polygon, 4326) NOT NULL,

    -- For circular geofences, store radius for reference
    radius_miles NUMERIC(10,2),

    -- Geofence type for business logic
    geofence_type TEXT NOT NULL DEFAULT 'facility',

    -- Active flag for enabling/disabling
    is_active BOOLEAN NOT NULL DEFAULT true,

    -- Standard audit columns
    created_at TIMESTAMPTZ DEFAULT now() NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT now() NOT NULL,
    created_by INT4,
    updated_by INT4,
    deleted_at TIMESTAMPTZ,
    deleted_by INT4,

    CONSTRAINT geofences_pkey PRIMARY KEY (id),
    CONSTRAINT chk_geofences_type CHECK (
        geofence_type IN ('facility', 'city', 'region', 'custom')
    )
);

COMMENT ON TABLE public.geofences IS
    'Geographic boundaries for tracking events (arrival, departure, dwell time)';

COMMENT ON COLUMN public.geofences.boundary IS
    'Polygon boundary in WGS84 (SRID 4326). Use geography type for accurate distance calculations';

COMMENT ON COLUMN public.geofences.radius_miles IS
    'For circular geofences, the original radius used to generate the boundary polygon';

-- Create a circular geofence around a facility
INSERT INTO geofences (name, facility_id, boundary, radius_miles, geofence_type)
SELECT
    f.name || ' Geofence',
    f.id,
    -- ST_Buffer creates a circle; convert miles to meters (1 mile = 1609.34 meters)
    ST_Buffer(
        ST_SetSRID(ST_MakePoint(f.longitude, f.latitude), 4326)::geography,
        0.5 * 1609.34  -- 0.5 mile radius in meters
    ),
    0.5,
    'facility'
FROM facilities f
WHERE f.id = $1;

-- Create a custom polygon geofence (e.g., terminal yard)
INSERT INTO geofences (name, boundary, geofence_type)
VALUES (
    'Terminal Yard A',
    ST_GeomFromText(
        'POLYGON((-87.630 41.878, -87.628 41.878, -87.628 41.876, -87.630 41.876, -87.630 41.878))',
        4326
    )::geography,
    'custom'
);

-- Migration: Add spatial indexes to geofences table

-- GIST index on geofence boundaries (required for spatial queries)
CREATE INDEX idx_geofences_boundary
    ON public.geofences USING GIST(boundary);

-- Standard indexes
CREATE INDEX idx_geofences_facility_id
    ON public.geofences(facility_id)
    WHERE facility_id IS NOT NULL;

CREATE INDEX idx_geofences_deleted_at
    ON public.geofences(deleted_at)
    WHERE deleted_at IS NULL;

CREATE INDEX idx_geofences_is_active
    ON public.geofences(is_active)
    WHERE is_active = true;

-- Create index on load_cognition for location-based queries
-- Note: Creates expression index since location is stored as lat/lon columns

CREATE INDEX idx_load_cognition_location
    ON public.load_cognition USING GIST(
        ST_SetSRID(ST_MakePoint(longitude, latitude), 4326)::geography
    );

-- Alternative: Partial index for valid coordinates only
CREATE INDEX idx_load_cognition_location_valid
    ON public.load_cognition USING GIST(
        ST_SetSRID(ST_MakePoint(longitude, latitude), 4326)::geography
    )
    WHERE latitude IS NOT NULL
      AND longitude IS NOT NULL
      AND latitude BETWEEN -90 AND 90
      AND longitude BETWEEN -180 AND 180;

-- Create spatial index on facilities for proximity queries
CREATE INDEX idx_facilities_location
    ON public.facilities USING GIST(
        ST_SetSRID(ST_MakePoint(longitude, latitude), 4326)::geography
    )
    WHERE latitude IS NOT NULL AND longitude IS NOT NULL;

-- Distance from truck to destination facility (in miles)
SELECT
    lc.load_id,
    ST_Distance(
        ST_SetSRID(ST_MakePoint(lc.longitude, lc.latitude), 4326)::geography,
        ST_SetSRID(ST_MakePoint(f.longitude, f.latitude), 4326)::geography
    ) / 1609.34 AS distance_miles
FROM load_cognition lc
JOIN loads l ON lc.load_id = l.id
JOIN stops s ON l.id = s.load_id AND s.stop_type = 'destination'
JOIN facilities f ON s.facility_id = f.id
WHERE lc.load_id = $1
ORDER BY lc.cognition_time DESC
LIMIT 1;

-- Find all trucks within 50 miles of a facility
SELECT
    lc.load_id,
    l.load_number,
    lc.driver_name,
    ST_Distance(
        ST_SetSRID(ST_MakePoint(lc.longitude, lc.latitude), 4326)::geography,
        ST_SetSRID(ST_MakePoint(f.longitude, f.latitude), 4326)::geography
    ) / 1609.34 AS distance_miles
FROM load_cognition lc
JOIN loads l ON lc.load_id = l.id
CROSS JOIN (
    SELECT longitude, latitude FROM facilities WHERE id = $1
) f
WHERE ST_DWithin(
    ST_SetSRID(ST_MakePoint(lc.longitude, lc.latitude), 4326)::geography,
    ST_SetSRID(ST_MakePoint(f.longitude, f.latitude), 4326)::geography,
    50 * 1609.34  -- 50 miles in meters
)
AND lc.cognition_time > now() - INTERVAL '1 hour'
ORDER BY distance_miles;

-- Check if truck is inside any active geofence
SELECT g.id, g.name, g.facility_id
FROM geofences g
WHERE ST_Contains(
    g.boundary::geometry,
    ST_SetSRID(ST_MakePoint($longitude, $latitude), 4326)
)
AND g.is_active = true
AND g.deleted_at IS NULL;

-- Create a 10-mile buffer zone around current truck position
SELECT ST_Buffer(
    ST_SetSRID(ST_MakePoint($longitude, $latitude), 4326)::geography,
    10 * 1609.34  -- 10 miles in meters
) AS buffer_zone;

-- Find facilities within buffer
SELECT f.id, f.name, f.city, f.state
FROM facilities f
WHERE ST_DWithin(
    ST_SetSRID(ST_MakePoint(f.longitude, f.latitude), 4326)::geography,
    ST_SetSRID(ST_MakePoint($longitude, $latitude), 4326)::geography,
    10 * 1609.34
)
AND f.deleted_at IS NULL;

-- Function to check if a position is inside any geofence
CREATE OR REPLACE FUNCTION public.check_geofence_entry(
    p_longitude NUMERIC,
    p_latitude NUMERIC
)
RETURNS TABLE (
    geofence_id UUID,
    geofence_name TEXT,
    facility_id UUID,
    geofence_type TEXT
)
LANGUAGE plpgsql
SECURITY INVOKER
SET search_path = 'public'
AS $$
BEGIN
    RETURN QUERY
    SELECT
        g.id,
        g.name,
        g.facility_id,
        g.geofence_type
    FROM geofences g
    WHERE ST_Contains(
        g.boundary::geometry,
        ST_SetSRID(ST_MakePoint(p_longitude, p_latitude), 4326)
    )
    AND g.is_active = true
    AND g.deleted_at IS NULL;
END;
$$;

COMMENT ON FUNCTION public.check_geofence_entry(NUMERIC, NUMERIC) IS
    'Returns all active geofences containing the given GPS coordinates';

-- Table to track geofence entry/exit events
CREATE TABLE public.geofence_events (
    id UUID DEFAULT gen_random_uuid() NOT NULL,

    load_id UUID NOT NULL REFERENCES loads(id) ON DELETE CASCADE,
    geofence_id UUID NOT NULL REFERENCES geofences(id) ON DELETE CASCADE,

    event_type TEXT NOT NULL,  -- 'entry', 'exit', 'dwell'
    event_time TIMESTAMPTZ NOT NULL DEFAULT now(),

    -- Position at time of event
    latitude NUMERIC(10,7),
    longitude NUMERIC(11,7),

    -- Dwell time tracking (for exit events)
    entry_time TIMESTAMPTZ,
    dwell_minutes INTEGER,

    -- Standard audit columns
    created_at TIMESTAMPTZ DEFAULT now() NOT NULL,

    CONSTRAINT geofence_events_pkey PRIMARY KEY (id),
    CONSTRAINT chk_geofence_events_type CHECK (
        event_type IN ('entry', 'exit', 'dwell')
    )
);

CREATE INDEX idx_geofence_events_load_id ON geofence_events(load_id);
CREATE INDEX idx_geofence_events_geofence_id ON geofence_events(geofence_id);
CREATE INDEX idx_geofence_events_event_time ON geofence_events(event_time);

-- Function to process location update and detect geofence events
CREATE OR REPLACE FUNCTION public.process_location_geofence(
    p_load_id UUID,
    p_longitude NUMERIC,
    p_latitude NUMERIC,
    p_timestamp TIMESTAMPTZ DEFAULT now()
)
RETURNS SETOF geofence_events
LANGUAGE plpgsql
SECURITY INVOKER
SET search_path = 'public'
AS $$
DECLARE
    v_current_geofences UUID[];
    v_previous_geofences UUID[];
    v_entered_geofence UUID;
    v_exited_geofence UUID;
    v_entry_time TIMESTAMPTZ;
    v_event geofence_events;
BEGIN
    -- Get current geofences containing this position
    SELECT ARRAY_AGG(g.id) INTO v_current_geofences
    FROM geofences g
    WHERE ST_Contains(
        g.boundary::geometry,
        ST_SetSRID(ST_MakePoint(p_longitude, p_latitude), 4326)
    )
    AND g.is_active = true
    AND g.deleted_at IS NULL;

    -- Get previously active geofences for this load
    SELECT ARRAY_AGG(DISTINCT ge.geofence_id) INTO v_previous_geofences
    FROM geofence_events ge
    WHERE ge.load_id = p_load_id
      AND ge.event_type = 'entry'
      AND NOT EXISTS (
          SELECT 1 FROM geofence_events ge2
          WHERE ge2.load_id = p_load_id
            AND ge2.geofence_id = ge.geofence_id
            AND ge2.event_type = 'exit'
            AND ge2.event_time > ge.event_time
      );

    -- Handle NULL arrays
    v_current_geofences := COALESCE(v_current_geofences, ARRAY[]::UUID[]);
    v_previous_geofences := COALESCE(v_previous_geofences, ARRAY[]::UUID[]);

    -- Detect entries (in current but not in previous)
    FOR v_entered_geofence IN
        SELECT UNNEST(v_current_geofences)
        EXCEPT
        SELECT UNNEST(v_previous_geofences)
    LOOP
        INSERT INTO geofence_events (load_id, geofence_id, event_type, event_time, latitude, longitude)
        VALUES (p_load_id, v_entered_geofence, 'entry', p_timestamp, p_latitude, p_longitude)
        RETURNING * INTO v_event;
        RETURN NEXT v_event;
    END LOOP;

    -- Detect exits (in previous but not in current)
    FOR v_exited_geofence IN
        SELECT UNNEST(v_previous_geofences)
        EXCEPT
        SELECT UNNEST(v_current_geofences)
    LOOP
        -- Get entry time for dwell calculation
        SELECT ge.event_time INTO v_entry_time
        FROM geofence_events ge
        WHERE ge.load_id = p_load_id
          AND ge.geofence_id = v_exited_geofence
          AND ge.event_type = 'entry'
        ORDER BY ge.event_time DESC
        LIMIT 1;

        INSERT INTO geofence_events (
            load_id, geofence_id, event_type, event_time,
            latitude, longitude, entry_time, dwell_minutes
        )
        VALUES (
            p_load_id, v_exited_geofence, 'exit', p_timestamp,
            p_latitude, p_longitude, v_entry_time,
            EXTRACT(EPOCH FROM (p_timestamp - v_entry_time)) / 60
        )
        RETURNING * INTO v_event;
        RETURN NEXT v_event;
    END LOOP;

    RETURN;
END;
$$;

COMMENT ON FUNCTION public.process_location_geofence(UUID, NUMERIC, NUMERIC, TIMESTAMPTZ) IS
    'Processes location update and generates geofence entry/exit events';

-- Calculate simple ETA based on distance and average speed
CREATE OR REPLACE FUNCTION public.calculate_eta(
    p_current_lon NUMERIC,
    p_current_lat NUMERIC,
    p_dest_lon NUMERIC,
    p_dest_lat NUMERIC,
    p_avg_speed_mph NUMERIC DEFAULT 50
)
RETURNS TIMESTAMPTZ
LANGUAGE plpgsql
SECURITY INVOKER
SET search_path = 'public'
AS $$
DECLARE
    v_distance_miles NUMERIC;
    v_hours NUMERIC;
BEGIN
    -- Calculate distance in miles
    v_distance_miles := ST_Distance(
        ST_SetSRID(ST_MakePoint(p_current_lon, p_current_lat), 4326)::geography,
        ST_SetSRID(ST_MakePoint(p_dest_lon, p_dest_lat), 4326)::geography
    ) / 1609.34;

    -- Calculate travel time
    v_hours := v_distance_miles / p_avg_speed_mph;

    RETURN now() + (v_hours || ' hours')::INTERVAL;
END;
$$;

COMMENT ON FUNCTION public.calculate_eta(NUMERIC, NUMERIC, NUMERIC, NUMERIC, NUMERIC) IS
    'Calculates ETA based on straight-line distance and average speed. For accurate routing, integrate with external routing API';

-- Calculate ETA to next stop for a load
SELECT
    l.load_number,
    s.stop_sequence,
    f.name AS facility_name,
    ST_Distance(
        ST_SetSRID(ST_MakePoint(lc.longitude, lc.latitude), 4326)::geography,
        ST_SetSRID(ST_MakePoint(f.longitude, f.latitude), 4326)::geography
    ) / 1609.34 AS distance_miles,
    now() + (
        (ST_Distance(
            ST_SetSRID(ST_MakePoint(lc.longitude, lc.latitude), 4326)::geography,
            ST_SetSRID(ST_MakePoint(f.longitude, f.latitude), 4326)::geography
        ) / 1609.34) / 50  -- Assume 50 mph average
        || ' hours'
    )::INTERVAL AS estimated_arrival
FROM loads l
JOIN stops s ON l.id = s.load_id
JOIN facilities f ON s.facility_id = f.id
JOIN LATERAL (
    SELECT longitude, latitude
    FROM load_cognition
    WHERE load_id = l.id
    ORDER BY cognition_time DESC
    LIMIT 1
) lc ON true
WHERE l.id = $1
  AND s.actual_arrival IS NULL  -- Not yet arrived
ORDER BY s.stop_sequence
LIMIT 1;

-- PGRouting requires a road network table
-- This is typically populated from OpenStreetMap data

-- Example: Calculate route distance using Dijkstra algorithm
-- Note: Requires pgr_createTopology and road network data

-- SELECT
--     sum(cost) AS total_distance,
--     ST_Union(geom) AS route_geometry
-- FROM pgr_dijkstra(
--     'SELECT id, source, target, cost FROM roads',
--     $start_node, $end_node,
--     directed := false
-- ) AS route
-- JOIN roads ON route.edge = roads.id;

-- For production ETA calculations, consider:
-- 1. External routing APIs (OSRM, Google Directions, Here)
-- 2. Pre-calculated route distances in lanes table
-- 3. Mileage tables from PC*MILER or similar

-- Key columns in load_cognition
-- id UUID
-- load_id UUID (FK to loads)
-- driver_name TEXT
-- latitude NUMERIC(10,7)
-- longitude NUMERIC(11,7)
-- cognition_time TIMESTAMPTZ
-- speed_mph NUMERIC
-- heading NUMERIC
-- source TEXT (e.g., 'eld', 'mobile', 'manual')

-- Get current position of all active loads
SELECT DISTINCT ON (l.id)
    l.id AS load_id,
    l.load_number,
    l.load_status,
    lc.driver_name,
    lc.latitude,
    lc.longitude,
    lc.speed_mph,
    lc.cognition_time,
    -- Calculate time since last update
    EXTRACT(EPOCH FROM (now() - lc.cognition_time)) / 60 AS minutes_since_update
FROM loads l
JOIN load_cognition lc ON l.id = lc.load_id
WHERE l.load_status IN ('dispatched', 'at_origin', 'in_transit', 'at_destination')
  AND l.deleted_at IS NULL
  AND lc.latitude IS NOT NULL
  AND lc.longitude IS NOT NULL
ORDER BY l.id, lc.cognition_time DESC;

-- Get tracking history for a load (for route visualization)
SELECT
    lc.latitude,
    lc.longitude,
    lc.cognition_time,
    lc.speed_mph,
    lc.heading
FROM load_cognition lc
WHERE lc.load_id = $1
  AND lc.latitude IS NOT NULL
  AND lc.longitude IS NOT NULL
ORDER BY lc.cognition_time ASC;

-- Get tracking history as GeoJSON for map display
SELECT json_build_object(
    'type', 'Feature',
    'properties', json_build_object(
        'load_id', $1::text,
        'point_count', count(*)
    ),
    'geometry', json_build_object(
        'type', 'LineString',
        'coordinates', json_agg(
            json_build_array(longitude, latitude)
            ORDER BY cognition_time
        )
    )
) AS geojson
FROM load_cognition
WHERE load_id = $1
  AND latitude IS NOT NULL
  AND longitude IS NOT NULL;

-- Facilities as GeoJSON FeatureCollection
SELECT json_build_object(
    'type', 'FeatureCollection',
    'features', json_agg(
        json_build_object(
            'type', 'Feature',
            'id', f.id,
            'properties', json_build_object(
                'name', f.name,
                'city', f.city,
                'state', f.state,
                'facility_type', f.facility_type
            ),
            'geometry', json_build_object(
                'type', 'Point',
                'coordinates', json_build_array(f.longitude, f.latitude)
            )
        )
    )
) AS geojson
FROM facilities f
WHERE f.latitude IS NOT NULL
  AND f.longitude IS NOT NULL
  AND f.deleted_at IS NULL;