
CREATE OR REPLACE FUNCTION check_vehicle_geofence()
RETURNS TRIGGER AS $$
DECLARE
    v_shipment_id INT;
    v_customer_id INT;
    v_geofence_id INT;
BEGIN
    RAISE NOTICE 'Trigger Started for vehicle_id=%, driver_id=%, gps_timestamp=%',
        NEW.vehicle_id, NEW.driver_id, NEW.gps_timestamp;

    SELECT s.id
    INTO v_shipment_id
    FROM shipments s
    WHERE s.vehicle_id = NEW.vehicle_id
      AND s.status_id = 1
    LIMIT 1;

    RAISE NOTICE 'Active shipment_id found: % for vehicle_id=%',
        v_shipment_id, NEW.vehicle_id;

    IF v_shipment_id IS NULL THEN
        RAISE NOTICE 'No active shipment. Skipping trigger.';
        RETURN NEW;
    END IF;

    -- STEP 2: LOOP ALL CUSTOMERS OF THIS SHIPMENT
    FOR v_customer_id IN
        SELECT sc.customer_id
        FROM shipment_customers sc
        WHERE sc.shipment_id = v_shipment_id
    LOOP
        RAISE NOTICE 'Processing customer_id=% for shipment_id=%',
            v_customer_id, v_shipment_id;

        -- STEP 3: LOOP ALL GEOFENCES OF THIS CUSTOMER
        FOR v_geofence_id IN
            SELECT cgm.geofence_id
            FROM customer_geofence_mapping cgm
            WHERE cgm.customer_id = v_customer_id
        LOOP
            RAISE NOTICE 'Checking geofence_id=% for customer_id=%',
                v_geofence_id, v_customer_id;

            -- CHECK IF VEHICLE IS INSIDE GEOFENCE
            PERFORM 1
            FROM geofences g
            WHERE g.id = v_geofence_id
              AND ST_Contains(
                    g.shape,
                    ST_SetSRID(ST_Point(
                        NEW.longitude::double precision,
                        NEW.latitude::double precision
                    ), 4326)
                );

            IF FOUND THEN
                RAISE NOTICE 'Vehicle % is INSIDE geofence %',
                    NEW.vehicle_id, v_geofence_id;

                IF (
                    SELECT vgh.event_type
                    FROM vehicle_geofence_history vgh
                    WHERE vgh.vehicle_id = NEW.vehicle_id
                      AND vgh.geofence_id = v_geofence_id
                    ORDER BY vgh.event_timestamp DESC, vgh.id DESC
                    LIMIT 1
                ) IS DISTINCT FROM 'entry' THEN

                    INSERT INTO vehicle_geofence_history(
                        vehicle_id, driver_id, geofence_id, event_type,
                        event_timestamp, company_id, customer_id, shipment_id
                    )
                    VALUES (
                        NEW.vehicle_id, NEW.driver_id, v_geofence_id,
                        'entry', NEW.gps_timestamp, 1, v_customer_id, v_shipment_id
                    );

                    RAISE NOTICE 'INSERTED ENTRY → vehicle_id=%, geofence_id=%',
                        NEW.vehicle_id, v_geofence_id;
                END IF;

            ELSE
                RAISE NOTICE 'Vehicle % is OUTSIDE geofence %',
                    NEW.vehicle_id, v_geofence_id;

                IF (
                    SELECT vgh.event_type
                    FROM vehicle_geofence_history vgh
                    WHERE vgh.vehicle_id = NEW.vehicle_id
                      AND vgh.geofence_id = v_geofence_id
                    ORDER BY vgh.event_timestamp DESC, vgh.id DESC
                    LIMIT 1
                ) = 'entry' THEN

                    INSERT INTO vehicle_geofence_history(
                        vehicle_id, driver_id, geofence_id, event_type,
                        event_timestamp, company_id, customer_id, shipment_id
                    )
                    VALUES (
                        NEW.vehicle_id, NEW.driver_id, v_geofence_id,
                        'exit', NEW.gps_timestamp, 1, v_customer_id, v_shipment_id
                    );

                    RAISE NOTICE 'INSERTED EXIT → vehicle_id=%, geofence_id=%',
                        NEW.vehicle_id, v_geofence_id;
                END IF;
            END IF;

        END LOOP; -- geofences
    END LOOP; -- customers

    RAISE NOTICE 'Trigger Completed Successfully for vehicle_id=%', NEW.vehicle_id;
    RETURN NEW;

    -- ERROR HANDLING BLOCK
EXCEPTION WHEN OTHERS THEN
    RAISE EXCEPTION 
        'ERROR in check_vehicle_geofence: % | vehicle_id=% | customer_id=% | geofence_id=% | shipment_id=% | lat=% | long=%',
        SQLERRM,
        NEW.vehicle_id,
        v_customer_id,
        v_geofence_id,
        v_shipment_id,
        NEW.latitude,
        NEW.longitude;
END;
$$ LANGUAGE plpgsql;


DROP TRIGGER IF EXISTS trg_check_vehicle_geofence ON vehicle_gps_data;

CREATE TRIGGER trg_check_vehicle_geofence
AFTER INSERT ON vehicle_gps_data
FOR EACH ROW
EXECUTE FUNCTION check_vehicle_geofence();

