aonestar.public

Parameters

Name	Type	Mode
p_source_id	integer	IN

Definition

DECLARE
    v_state text; v_message text; v_detail text; v_hint text; v_context text;
    v_src    record;
    v_status integer;
    v_ics    text;
    v_events jsonb;
    v_count  integer := 0;
BEGIN
    SELECT * INTO v_src FROM public.calendar_source WHERE id = p_source_id;
    IF v_src.id IS NULL THEN
        RETURN fn_result_error('50507', sys_msg('50507', 'Calendar source not found (%s)'), coalesce(p_source_id::text, ''));
    END IF;
    IF NOT v_src.enabled THEN
        RETURN fn_result_error('50511', sys_msg('50511', 'Calendar source is disabled'));
    END IF;
    IF coalesce(v_src.ics_url, '') = '' THEN
        RETURN fn_result_error('50508', sys_msg('50508', 'Calendar source has no ICS URL'));
    END IF;

-- Fetch the feed (synchronous http extension via plpython3u/requests)
    SELECT status, content_text INTO v_status, v_ics FROM public.http_get(v_src.ics_url);
    IF v_status IS DISTINCT FROM 200 THEN
        UPDATE public.calendar_source
        SET last_sync_at = now(), last_sync_status = 'error',
            last_sync_message = format('HTTP %s', coalesce(v_status::text, '?'))
        WHERE id = p_source_id;
        RETURN fn_result_error('50509', sys_msg('50509', 'Failed to fetch calendar feed (%s)'), coalesce(v_status::text, '?'));
    END IF;

v_events := public.fn_parse_ics(v_ics);

-- Upsert events keyed on (calendar_id, external_uid)
    WITH ev AS (
        SELECT * FROM jsonb_to_recordset(v_events) AS x(
            external_uid text, name text, start_date date, end_date date,
            all_day boolean, start_time time, end_time time)
        WHERE external_uid IS NOT NULL AND start_date IS NOT NULL
    ), up AS (
        INSERT INTO public.calendar_event(
            calendar_id, name, start_date, end_date, all_day, start_time, end_time, source_id, external_uid)
        SELECT v_src.calendar_id, ev.name, ev.start_date, ev.end_date,
               coalesce(ev.all_day, true), ev.start_time, ev.end_time, p_source_id, ev.external_uid
        FROM ev
        ON CONFLICT (calendar_id, external_uid) WHERE external_uid IS NOT NULL
        DO UPDATE SET name = EXCLUDED.name, start_date = EXCLUDED.start_date, end_date = EXCLUDED.end_date,
                      all_day = EXCLUDED.all_day, start_time = EXCLUDED.start_time,
                      end_time = EXCLUDED.end_time, source_id = EXCLUDED.source_id
        RETURNING 1
    )
    SELECT count(*)::int INTO v_count FROM up;

-- Drop events previously imported from this source but no longer in the feed
    DELETE FROM public.calendar_event ce
    WHERE ce.source_id = p_source_id
      AND ce.external_uid IS NOT NULL
      AND NOT EXISTS (
          SELECT 1 FROM jsonb_to_recordset(v_events) AS x(external_uid text)
          WHERE x.external_uid = ce.external_uid);

UPDATE public.calendar_source
    SET last_sync_at = now(), last_sync_status = 'ok',
        last_sync_message = format('Imported %s events', v_count), last_event_count = v_count
    WHERE id = p_source_id;

RETURN fn_result_success(jsonb_build_object('source_id', p_source_id, 'event_count', v_count)::json);
EXCEPTION WHEN OTHERS THEN
    GET STACKED DIAGNOSTICS
        v_state = RETURNED_SQLSTATE, v_message = MESSAGE_TEXT,
        v_detail = PG_EXCEPTION_DETAIL, v_hint = PG_EXCEPTION_HINT, v_context = PG_EXCEPTION_CONTEXT;
    UPDATE public.calendar_source
    SET last_sync_at = now(), last_sync_status = 'error', last_sync_message = left(v_message, 500)
    WHERE id = p_source_id;
    RETURN fn_handle_error(v_state, v_message, v_detail, v_hint, v_context, 'public.sp_import_ics',
        jsonb_build_object('source_id', p_source_id));
END