DROP PROCEDURE IF EXISTS process_vehicle_geofences;



CREATE OR REPLACE PROCEDURE process_vehicle_geofences(
    IN  p_batch_size        integer,
    IN  p_jitter_buffer_m   numeric,
    OUT o_status1_to_2_count integer,
    OUT o_inserted_count     integer,
    OUT o_processed_count    integer
)
LANGUAGE plpgsql
AS $$
DECLARE
    r_gps RECORD;
    r_customer RECORD;
    r_geo RECORD;
    v_last_state "GeoFenceEventType";
    v_new_state  "GeoFenceEventType";
    v_distance_check numeric;
BEGIN

    o_status1_to_2_count := 0;
    o_inserted_count := 0;
    o_processed_count := 0;

    FOR r_gps IN
        SELECT
            *
        FROM vehicle_gps_data
        WHERE created_at >= date_trunc('day', NOW())
          AND process_status_id IN (1,2)
        ORDER BY
            latitude,
            longitude,
            shipment_id,
            driver_id,
            gps_timestamp DESC
        LIMIT p_batch_size
        FOR UPDATE SKIP LOCKED
    LOOP

        -- 1️⃣ Move 1 → 2
        IF r_gps.process_status_id = 1 THEN
            UPDATE vehicle_gps_data
            SET process_status_id = 2,
                processed_at = NOW()
            WHERE id = r_gps.id;

            o_status1_to_2_count := o_status1_to_2_count + 1;
        END IF;

        -- 2️⃣ Shipment customers
        FOR r_customer IN
            SELECT sc.customer_id
            FROM shipment_customers sc
            WHERE sc.shipment_id = r_gps.shipment_id
        LOOP

            -- 3️⃣ Geofences per customer
            FOR r_geo IN
                SELECT
                    g.id,
                    g.center_geog,
                    (g.radius::numeric * 1000) AS radius_m
                FROM customer_geofence_mapping cgm
                JOIN geofences g ON g.id = cgm.geofence_id
                WHERE cgm.customer_id = r_customer.customer_id
            LOOP

                -- 4️⃣ Last state
                SELECT event_type
                INTO v_last_state
                FROM vehicle_geofence_history
                WHERE shipment_id = r_gps.shipment_id
                  AND vehicle_id = r_gps.vehicle_id
                  AND driver_id = r_gps.driver_id
                  AND geofence_id = r_geo.id
                ORDER BY event_timestamp DESC
                LIMIT 1;

                IF v_last_state IS NULL THEN
                    v_last_state := 'exit';
                END IF;

                -- 5️⃣ Jitter logic
                IF v_last_state = 'entry' THEN
                    v_distance_check := r_geo.radius_m + p_jitter_buffer_m;
                ELSE
                    v_distance_check := r_geo.radius_m;
                END IF;

                -- 6️⃣ Inside/outside check
                IF ST_DWithin(
                    r_gps.point_geog,
                    r_geo.center_geog,
                    v_distance_check
                ) THEN
                    v_new_state := 'entry';
                ELSE
                    v_new_state := 'exit';
                END IF;

                -- 7️⃣ Insert if changed
                IF v_new_state <> v_last_state THEN

                    INSERT INTO vehicle_geofence_history
                    (
                        uuid,
                        latitude,
                        longitude,
                        vehicle_id,
                        shipment_id,
                        driver_id,
                        customer_id,
                        geofence_id,
                        company_id,
                        event_type,
                        event_timestamp
                    )
                    VALUES
                    (
                        gen_random_uuid(),
                        r_gps.latitude,
                        r_gps.longitude,
                        r_gps.vehicle_id,
                        r_gps.shipment_id,
                        r_gps.driver_id,
                        r_customer.customer_id,
                        r_geo.id,
                        r_gps.company_id,
                        v_new_state,
                        r_gps.gps_timestamp
                    );

                    o_inserted_count := o_inserted_count + 1;

                END IF;

            END LOOP;
        END LOOP;

        -- 8️⃣ Finalize GPS row
        UPDATE vehicle_gps_data
        SET process_status_id = 3,
            processed_at = NOW()
        WHERE id = r_gps.id;

        o_processed_count := o_processed_count + 1;

    END LOOP;

END;
$$;