CREATE OR REPLACE FUNCTION insert_vehicle_geofence_mail()
RETURNS TRIGGER AS $$
DECLARE
    v_driver_name VARCHAR;
    v_vehicle_plate_number VARCHAR;
    v_geofence_name VARCHAR;
    v_super_admin_id INT;
BEGIN
        -- Step 1: Get driver name
        SELECT ud.first_name
        INTO v_driver_name
        FROM users u 
        LEFT JOIN user_details ud ON ud.user_id = u.id
        WHERE u.status_id = 1 AND u.id = NEW.driver_id
        LIMIT 1;

        -- Step 2: Get vehicle plate number
        SELECT v.vehicle_plate_number
        INTO v_vehicle_plate_number
        FROM vehicles v 
        WHERE v.status_id = 1 AND v.id = NEW.vehicle_id
        LIMIT 1;

        -- Step 3: Get geofence name
        SELECT g.zone_name
        INTO v_geofence_name
        FROM geofences g 
        WHERE g.status_id = 1 AND g.id = NEW.geofence_id
        LIMIT 1;

    -- Step 4: Insert email job
    INSERT INTO notification_process_jobs(
        notification_template_process_slug,
        unique_id,
        user_id,
        prefill_data,
        company_id,
        status_id,
        created_by
    )
    VALUES (
        'geofence_vehicle_mail',
        NEW.vehicle_id::INT,
        NEW.customer_id::INT,
        jsonb_build_object(
            'vehicle_plate_number', v_vehicle_plate_number,
            'geofence_name', v_geofence_name,
            'driver_name', v_driver_name,
            'event_status', CASE 
                               WHEN NEW.event_type = 'entry' THEN 'entered'
                               ELSE 'exited'
                            END,
            'gps_timestamp', to_char(NEW.event_timestamp, 'DD/MM/YYYY - HH24:MI:SS')
        ),
        1,
        1,
        NEW.customer_id
    );

       RETURN NEW;
	   
END;
$$ LANGUAGE plpgsql;


DROP TRIGGER IF EXISTS trg_vehicle_geofence_history ON vehicle_geofence_history;


CREATE TRIGGER trg_vehicle_geofence_history
AFTER INSERT ON vehicle_geofence_history
FOR EACH ROW
EXECUTE FUNCTION insert_vehicle_geofence_mail();
