-- Service control helpers for worker management.
-- These functions use sc.exe via COPY FROM PROGRAM and return textual output.

-- DROP FUNCTION IF EXISTS sonex.get_service_by_name(text);
CREATE OR REPLACE FUNCTION sonex.get_service_by_name(
    p_service_name text)
RETURNS TABLE(
    caption text,
    description text,
    name text,
    status text,
    started boolean,
    start_mode text,
    accept_pause boolean,
    accept_stop boolean,
    display_name text,
    error_control text,
    exit_code integer,
    path_name text,
    service_specific_exit_code integer,
    start_name text,
    state text,
    delayed_auto_start boolean,
    process_id integer)
LANGUAGE plpgsql
AS $BODY$
DECLARE
    v_state_text text;
    v_exit_code integer;
    v_service_exit_code integer;
    v_pid integer;
    v_accepts text;
    v_start_mode text;
    v_error_control text;
    v_path_name text;
    v_display_name text;
    v_start_name text;
BEGIN
    IF p_service_name IS NULL OR btrim(p_service_name) = '' THEN
        RAISE EXCEPTION 'Service name cannot be empty';
    END IF;

    CREATE TEMP TABLE IF NOT EXISTS pg_temp._sc_service_output (
        line text
    ) ON COMMIT DROP;

    TRUNCATE TABLE pg_temp._sc_service_output;

    EXECUTE format(
        'COPY pg_temp._sc_service_output(line) FROM PROGRAM %L',
        format('sc.exe queryex %s', quote_ident(btrim(p_service_name)))
    );

    SELECT (regexp_match(line, '^\s*STATE\s*:\s*[0-9]+\s+([A-Z_]+).*$'))[1]
    INTO v_state_text
    FROM pg_temp._sc_service_output
    WHERE line ~ '^\s*STATE\s*:'
    LIMIT 1;

    SELECT (regexp_match(line, '^\s*WIN32_EXIT_CODE\s*:\s*([0-9]+).*$'))[1]::integer
    INTO v_exit_code
    FROM pg_temp._sc_service_output
    WHERE line ~ '^\s*WIN32_EXIT_CODE\s*:'
    LIMIT 1;

    SELECT (regexp_match(line, '^\s*SERVICE_EXIT_CODE\s*:\s*([0-9]+).*$'))[1]::integer
    INTO v_service_exit_code
    FROM pg_temp._sc_service_output
    WHERE line ~ '^\s*SERVICE_EXIT_CODE\s*:'
    LIMIT 1;

    SELECT (regexp_match(line, '^\s*PID\s*:\s*([0-9]+).*$'))[1]::integer
    INTO v_pid
    FROM pg_temp._sc_service_output
    WHERE line ~ '^\s*PID\s*:'
    LIMIT 1;

    SELECT line
    INTO v_accepts
    FROM pg_temp._sc_service_output
    WHERE line ~ '\(.*\)'
    LIMIT 1;

    TRUNCATE TABLE pg_temp._sc_service_output;

    EXECUTE format(
        'COPY pg_temp._sc_service_output(line) FROM PROGRAM %L',
        format('sc.exe qc %s', quote_ident(btrim(p_service_name)))
    );

    SELECT (regexp_match(line, '^\s*START_TYPE\s*:\s*[0-9]+\s+([A-Z_]+).*$'))[1]
    INTO v_start_mode
    FROM pg_temp._sc_service_output
    WHERE line ~ '^\s*START_TYPE\s*:'
    LIMIT 1;

    SELECT nullif(btrim(regexp_replace(line, '^\s*ERROR_CONTROL\s*:\s*[0-9]+\s+', '')), '')
    INTO v_error_control
    FROM pg_temp._sc_service_output
    WHERE line ~ '^\s*ERROR_CONTROL\s*:'
    LIMIT 1;

    SELECT nullif(btrim(regexp_replace(line, '^\s*BINARY_PATH_NAME\s*:\s*', '')), '')
    INTO v_path_name
    FROM pg_temp._sc_service_output
    WHERE line ~ '^\s*BINARY_PATH_NAME\s*:'
    LIMIT 1;

    SELECT nullif(btrim(regexp_replace(line, '^\s*DISPLAY_NAME\s*:\s*', '')), '')
    INTO v_display_name
    FROM pg_temp._sc_service_output
    WHERE line ~ '^\s*DISPLAY_NAME\s*:'
    LIMIT 1;

    SELECT nullif(btrim(regexp_replace(line, '^\s*SERVICE_START_NAME\s*:\s*', '')), '')
    INTO v_start_name
    FROM pg_temp._sc_service_output
    WHERE line ~ '^\s*SERVICE_START_NAME\s*:'
    LIMIT 1;

    RETURN QUERY
    SELECT
        btrim(p_service_name)::text AS caption,
        NULL::text AS description,
        btrim(p_service_name)::text AS name,
        CASE
            WHEN v_state_text = 'RUNNING' THEN 'OK'
            WHEN v_state_text IS NULL THEN NULL
            ELSE v_state_text
        END::text AS status,
        (v_state_text = 'RUNNING')::boolean AS started,
        v_start_mode::text AS start_mode,
        COALESCE(v_accepts ILIKE '%PAUSABLE%', false)::boolean AS accept_pause,
        COALESCE(v_accepts ILIKE '%STOPPABLE%', false)::boolean AS accept_stop,
        v_display_name::text AS display_name,
        v_error_control::text AS error_control,
        v_exit_code::integer AS exit_code,
        v_path_name::text AS path_name,
        v_service_exit_code::integer AS service_specific_exit_code,
        v_start_name::text AS start_name,
        v_state_text::text AS state,
        CASE
            WHEN v_start_mode = 'DELAYED_AUTO_START' THEN true
            ELSE NULL
        END::boolean AS delayed_auto_start,
        v_pid::integer AS process_id;
END;
$BODY$;

ALTER FUNCTION sonex.get_service_by_name(text)
    OWNER TO postgres;


-- DROP FUNCTION IF EXISTS sonex.start_service_by_name(text);
CREATE OR REPLACE FUNCTION sonex.start_service_by_name(
    p_service_name text)
RETURNS TABLE(success boolean, output text)
LANGUAGE plpgsql
AS $BODY$
DECLARE
    v_output text;
BEGIN
    IF p_service_name IS NULL OR btrim(p_service_name) = '' THEN
        RETURN QUERY SELECT false, 'Service name cannot be empty.';
        RETURN;
    END IF;

    CREATE TEMP TABLE IF NOT EXISTS pg_temp._sc_service_action_output (
        line text
    ) ON COMMIT DROP;

    TRUNCATE TABLE pg_temp._sc_service_action_output;

    EXECUTE format(
        'COPY pg_temp._sc_service_action_output(line) FROM PROGRAM %L',
        format('sc.exe start %s', quote_ident(btrim(p_service_name)))
    );

    SELECT string_agg(line, E'\n')
    INTO v_output
    FROM pg_temp._sc_service_action_output;

    IF v_output ILIKE '%FAILED%' THEN
        RETURN QUERY SELECT false, COALESCE(v_output, 'Service start failed.');
        RETURN;
    END IF;

    RETURN QUERY SELECT true, COALESCE(v_output, 'Service start requested.');
END;
$BODY$;

ALTER FUNCTION sonex.start_service_by_name(text)
    OWNER TO postgres;


-- DROP FUNCTION IF EXISTS sonex.continue_service_by_name(text);
CREATE OR REPLACE FUNCTION sonex.continue_service_by_name(
    p_service_name text)
RETURNS TABLE(success boolean, output text)
LANGUAGE plpgsql
AS $BODY$
DECLARE
    v_output text;
BEGIN
    IF p_service_name IS NULL OR btrim(p_service_name) = '' THEN
        RETURN QUERY SELECT false, 'Service name cannot be empty.';
        RETURN;
    END IF;

    CREATE TEMP TABLE IF NOT EXISTS pg_temp._sc_service_action_output (
        line text
    ) ON COMMIT DROP;

    TRUNCATE TABLE pg_temp._sc_service_action_output;

    EXECUTE format(
        'COPY pg_temp._sc_service_action_output(line) FROM PROGRAM %L',
        format('sc.exe continue %s', quote_ident(btrim(p_service_name)))
    );

    SELECT string_agg(line, E'\n')
    INTO v_output
    FROM pg_temp._sc_service_action_output;

    IF v_output ILIKE '%FAILED%' THEN
        RETURN QUERY SELECT false, COALESCE(v_output, 'Service continue failed.');
        RETURN;
    END IF;

    RETURN QUERY SELECT true, COALESCE(v_output, 'Service continue requested.');
END;
$BODY$;

ALTER FUNCTION sonex.continue_service_by_name(text)
    OWNER TO postgres;


-- DROP FUNCTION IF EXISTS sonex.pause_service_by_name(text);
CREATE OR REPLACE FUNCTION sonex.pause_service_by_name(
    p_service_name text)
RETURNS TABLE(success boolean, output text)
LANGUAGE plpgsql
AS $BODY$
DECLARE
    v_output text;
BEGIN
    IF p_service_name IS NULL OR btrim(p_service_name) = '' THEN
        RETURN QUERY SELECT false, 'Service name cannot be empty.';
        RETURN;
    END IF;

    CREATE TEMP TABLE IF NOT EXISTS pg_temp._sc_service_action_output (
        line text
    ) ON COMMIT DROP;

    TRUNCATE TABLE pg_temp._sc_service_action_output;

    EXECUTE format(
        'COPY pg_temp._sc_service_action_output(line) FROM PROGRAM %L',
        format('sc.exe pause %s', quote_ident(btrim(p_service_name)))
    );

    SELECT string_agg(line, E'\n')
    INTO v_output
    FROM pg_temp._sc_service_action_output;

    IF v_output ILIKE '%FAILED%' THEN
        RETURN QUERY SELECT false, COALESCE(v_output, 'Service pause failed.');
        RETURN;
    END IF;

    RETURN QUERY SELECT true, COALESCE(v_output, 'Service pause requested.');
END;
$BODY$;

ALTER FUNCTION sonex.pause_service_by_name(text)
    OWNER TO postgres;


-- DROP FUNCTION IF EXISTS sonex.stop_service_by_name(text);
CREATE OR REPLACE FUNCTION sonex.stop_service_by_name(
    p_service_name text)
RETURNS TABLE(success boolean, output text)
LANGUAGE plpgsql
AS $BODY$
DECLARE
    v_output text;
BEGIN
    IF p_service_name IS NULL OR btrim(p_service_name) = '' THEN
        RETURN QUERY SELECT false, 'Service name cannot be empty.';
        RETURN;
    END IF;

    CREATE TEMP TABLE IF NOT EXISTS pg_temp._sc_service_action_output (
        line text
    ) ON COMMIT DROP;

    TRUNCATE TABLE pg_temp._sc_service_action_output;

    EXECUTE format(
        'COPY pg_temp._sc_service_action_output(line) FROM PROGRAM %L',
        format('sc.exe stop %s', quote_ident(btrim(p_service_name)))
    );

    SELECT string_agg(line, E'\n')
    INTO v_output
    FROM pg_temp._sc_service_action_output;

    IF v_output ILIKE '%FAILED%' THEN
        RETURN QUERY SELECT false, COALESCE(v_output, 'Service stop failed.');
        RETURN;
    END IF;

    RETURN QUERY SELECT true, COALESCE(v_output, 'Service stop requested.');
END;
$BODY$;

ALTER FUNCTION sonex.stop_service_by_name(text)
    OWNER TO postgres;


-- DROP FUNCTION IF EXISTS sonex.set_service_start_mode_by_name(text, text);
CREATE OR REPLACE FUNCTION sonex.set_service_start_mode_by_name(
    p_service_name text,
    p_start_mode text)
RETURNS TABLE(success boolean, output text)
LANGUAGE plpgsql
AS $BODY$
DECLARE
    v_output text;
    v_mode text;
BEGIN
    IF p_service_name IS NULL OR btrim(p_service_name) = '' THEN
        RETURN QUERY SELECT false, 'Service name cannot be empty.';
        RETURN;
    END IF;

    IF p_start_mode IS NULL OR btrim(p_start_mode) = '' THEN
        RETURN QUERY SELECT false, 'Start mode cannot be empty.';
        RETURN;
    END IF;

    v_mode := lower(btrim(p_start_mode));
    IF v_mode NOT IN ('auto', 'demand') THEN
        RETURN QUERY SELECT false, format('Unsupported start mode: %s', p_start_mode);
        RETURN;
    END IF;

    CREATE TEMP TABLE IF NOT EXISTS pg_temp._sc_service_action_output (
        line text
    ) ON COMMIT DROP;

    TRUNCATE TABLE pg_temp._sc_service_action_output;

    BEGIN
        EXECUTE format(
            'COPY pg_temp._sc_service_action_output(line) FROM PROGRAM %L',
            format('sc.exe config %s start= %s', quote_ident(btrim(p_service_name)), v_mode)
        );
    EXCEPTION
        WHEN OTHERS THEN
            RETURN QUERY
            SELECT false,
                   format(
                       'Service start mode update command failed. %s. Ensure PostgreSQL service account has rights to reconfigure this Windows service.',
                       SQLERRM);
            RETURN;
    END;

    SELECT string_agg(line, E'\n')
    INTO v_output
    FROM pg_temp._sc_service_action_output;

    IF v_output ILIKE '%FAILED%' THEN
        RETURN QUERY SELECT false, COALESCE(v_output, 'Service start mode update failed.');
        RETURN;
    END IF;

    RETURN QUERY SELECT true, COALESCE(v_output, format('Service start mode updated to %s.', v_mode));
END;
$BODY$;

ALTER FUNCTION sonex.set_service_start_mode_by_name(text, text)
    OWNER TO postgres;
