-- Copyright (C) 2025 The Meme Factory, Inc., http://www.karlpinc.com/
--
--  This program is free software: you can redistribute it and/or modify
--  it under the terms of the GNU Affero General Public License as published
--  by the Free Software Foundation, either version 3 of the License, or
--  (at your option) any later version.
--
--  This program is distributed in the hope that it will be useful,
--  but WITHOUT ANY WARRANTY; without even the implied warranty of
--  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
--  GNU Affero General Public License for more details.
--
--  You should have received a copy of the GNU Affero General Public License
--  along with this program.  If not, see <https://www.gnu.org/licenses/>.
--
-- Karl O. Pinc <kop@karlpinc.com>

-- Upgrade from 0.3.2 to 0.4.0

ALTER TABLE @extschema@.isok_results
  RENAME category TO type;

ALTER TABLE @extschema@.isok_results
  RENAME CONSTRAINT "Category must be an IR_TYPES.IRType value"
                 TO "Type must be an IR_TYPES.IRType value";

ALTER INDEX @extschema@.isok_results_category
  RENAME TO isok_results_type;

DROP FUNCTION run_isok_queries();
DROP FUNCTION run_isok_queries(TEXT);

CREATE OR REPLACE FUNCTION run_isok_queries()
  RETURNS TABLE(
    irid              @extschema@.isok_results.irid%TYPE
  , iqname            @extschema@.isok_results.iqname%TYPE
  , first_seen        @extschema@.isok_results.first_seen%TYPE
  , last_seen         @extschema@.isok_results.last_seen%TYPE
  , last_role         @extschema@.isok_results.last_role%TYPE
  , last_schemas      @extschema@.isok_results.last_schemas%TYPE
  , deferred_to       @extschema@.isok_results.deferred_to%TYPE
  , type              @extschema@.isok_results.type%TYPE
  , keep_until        @extschema@.isok_results.keep_until%TYPE
  , qr_id             @extschema@.isok_results.qr_id%TYPE
  , qr_message        @extschema@.isok_results.qr_message%TYPE
  , qr_extra          @extschema@.isok_results.qr_extra%TYPE
  , notes             @extschema@.isok_results.notes%TYPE
)
  LANGUAGE plpgsql
  SECURITY INVOKER
  AS $$
  -- Run all queries in ISOK_QUERIES.
  --
  -- Copyright (C) 2015, 2025 The Meme Factory, Inc.  http://www.karlpinc.com/
  -- Distributed under the GNU Affero General Public License, version 3 or later.
  --
  -- Syntax: run_isok_queries()
  --
  -- Arguments:
  --
  -- Remarks:
  --
  -- Bugs:

  DECLARE

  BEGIN

  RETURN QUERY EXECUTE 'SELECT * FROM @extschema@.run_isok_queries(
                          $RIQ$SELECT @extschema@.isok_queries.iqname
                                 FROM @extschema@.isok_queries
                                 ORDER BY @extschema@.isok_queries.iqname$RIQ$
                                 )';
  END;
$$;


CREATE OR REPLACE FUNCTION run_isok_queries(query TEXT)
  RETURNS TABLE(
    irid              @extschema@.isok_results.irid%TYPE
  , iqname            @extschema@.isok_results.iqname%TYPE
  , first_seen        @extschema@.isok_results.first_seen%TYPE
  , last_seen         @extschema@.isok_results.last_seen%TYPE
  , last_role         @extschema@.isok_results.last_role%TYPE
  , last_schemas      @extschema@.isok_results.last_schemas%TYPE
  , deferred_to       @extschema@.isok_results.deferred_to%TYPE
  , type              @extschema@.isok_results.type%TYPE
  , keep_until        @extschema@.isok_results.keep_until%TYPE
  , qr_id             @extschema@.isok_results.qr_id%TYPE
  , qr_message        @extschema@.isok_results.qr_message%TYPE
  , qr_extra          @extschema@.isok_results.qr_extra%TYPE
  , notes             @extschema@.isok_results.notes%TYPE
)
  LANGUAGE plpgsql
  SECURITY INVOKER
  AS $$
  -- Run the queries in ISOK_QUERIES that have an IQName returned by
  -- the SQL statement given as an argument.
  --
  -- Copyright (C) 2015, 2025 The Meme Factory, Inc.  http://www.karlpinc.com/
  -- Distributed under the GNU Affero General Public License, version 3 or later.
  --
  -- Syntax: run_isok_queries(query)
  --
  -- Arguments:
  --   query  A query returning 1 column, of INTEGRITY_QUERY.IQName values.
  --
  -- Remarks:
  --
  -- Bugs:

  DECLARE

    -- From query results
    this_iqname @extschema@.isok_queries.iqname%TYPE;
    this_keep @extschema@.isok_queries.keep%TYPE;
    saved_role @extschema@.isok_queries.role%TYPE;
    saved_search_path @extschema@.isok_queries.search_path%TYPE;
    saved_query @extschema@.isok_queries.query%TYPE;

    -- Dynamic commands
    starting_search_path TEXT;
    role_used name;
    become_starting_role TEXT;
    become_saved_role TEXT;

    -- Saved query results
    id TEXT;
    msg TEXT;
    extra JSONB;

    -- Exception handling
    my_message_text TEXT;
    my_pg_exception_detail TEXT;
    my_pg_exception_hint TEXT;
    my_pg_exception_context TEXT;

    exception_caught BOOLEAN := FALSE;

  BEGIN
    -- Run the supplied query and iterate over the saved queries.
    FOR this_iqname IN EXECUTE query LOOP

      -- Keep track of which query results we receive from the query
      CREATE TEMP TABLE _riq_qr_ids AS
        SELECT @extschema@.isok_results.qr_id
          FROM @extschema@.isok_results
        WITH NO DATA;

      -- Get the saved query
      SELECT
             @extschema@.isok_queries.query, @extschema@.isok_queries.keep
           , @extschema@.isok_queries.search_path
           , @extschema@.isok_queries.role
        INTO
             saved_query                   , this_keep
           , saved_search_path
           , saved_role
        FROM @extschema@.isok_queries
        WHERE @extschema@.isok_queries.iqname = this_iqname;

      IF NOT FOUND THEN
        RAISE EXCEPTION data_exception USING
          MESSAGE = 'Error in run_isok_queries(TEXT)'
          , DETAIL = 'Supplied query returned a value ('
                     || CASE WHEN this_iqname IS NULL THEN 'NULL' ELSE this_iqname::TEXT END

                     || ') that is not an existing'
                     || ' ISOK_QUERIES.IQName value';
        RETURN;
      END IF;

      IF saved_search_path IS NOT NULL THEN
        -- Use the given search path while executing the query
        starting_search_path := current_setting('search_path');

        BEGIN
          EXECUTE 'SET LOCAL search_path TO ' || saved_search_path || ';';

        EXCEPTION
          WHEN syntax_error_or_access_rule_violation THEN
            
        GET STACKED DIAGNOSTICS
            my_message_text = MESSAGE_TEXT
          , my_pg_exception_detail = PG_EXCEPTION_DETAIL
          , my_pg_exception_hint = PG_EXCEPTION_HINT
          , my_pg_exception_context = PG_EXCEPTION_CONTEXT;
        
        my_message_text := E'\n' || my_message_text;
        IF my_pg_exception_detail <> '' THEN
           my_message_text := my_message_text || E'\n';
           my_pg_exception_detail := E'\nThe detail is:\n' || my_pg_exception_detail;
        END IF;
        IF my_pg_exception_hint <> '' THEN
           my_pg_exception_detail := my_pg_exception_detail || E'\n';
           my_pg_exception_hint := E'\nThe hint is:\n' || my_pg_exception_hint;
        END IF;
        my_pg_exception_hint := my_pg_exception_hint || E'\n';
        IF my_pg_exception_context <> '' THEN
           my_pg_exception_context := E'\n\nAdditional diagnostic information:\n' || my_pg_exception_context;
        END IF;

            exception_caught := TRUE;
            RAISE EXCEPTION data_exception USING
              MESSAGE = 'run_isok_queries:'
                        || ' Error setting search_path before executing an'
                        || ' ISOK_QUERIES.Query'
              , DETAIL = 'Problem in the search_path with IQName = ('
                         || this_iqname || E'):\n'
                         || 'The error is: ' || my_message_text
                         || my_pg_exception_detail
                         || my_pg_exception_hint
                         || E'\nThe ISOK_QUERIES.Search_Path is:\n'
                         || saved_search_path
                         || my_pg_exception_context;
        END;
      END IF;

      IF saved_role IS NULL THEN
        role_used := current_user;
      ELSE
        -- The query has an associated role
        role_used := saved_role;

        -- Set the role, if any

        -- Save commands to switch back and forth between roles
        become_starting_role := 'SET LOCAL ROLE TO ' || current_user || ';';
        become_saved_role := 'SET LOCAL ROLE TO ' || saved_role || ';';

        BEGIN
          EXECUTE become_saved_role;

        EXCEPTION
          -- We want all of these to trap most that could go wrong:
          -- data_exception  (22000)  no such role, etc.
          -- syntax_error_or_access_rule_violation (42000)
          WHEN data_exception
               OR syntax_error_or_access_rule_violation THEN
            
        GET STACKED DIAGNOSTICS
            my_message_text = MESSAGE_TEXT
          , my_pg_exception_detail = PG_EXCEPTION_DETAIL
          , my_pg_exception_hint = PG_EXCEPTION_HINT
          , my_pg_exception_context = PG_EXCEPTION_CONTEXT;
        
        my_message_text := E'\n' || my_message_text;
        IF my_pg_exception_detail <> '' THEN
           my_message_text := my_message_text || E'\n';
           my_pg_exception_detail := E'\nThe detail is:\n' || my_pg_exception_detail;
        END IF;
        IF my_pg_exception_hint <> '' THEN
           my_pg_exception_detail := my_pg_exception_detail || E'\n';
           my_pg_exception_hint := E'\nThe hint is:\n' || my_pg_exception_hint;
        END IF;
        my_pg_exception_hint := my_pg_exception_hint || E'\n';
        IF my_pg_exception_context <> '' THEN
           my_pg_exception_context := E'\n\nAdditional diagnostic information:\n' || my_pg_exception_context;
        END IF;

            exception_caught := TRUE;
            RAISE EXCEPTION data_exception USING
              MESSAGE = 'run_isok_queries:'
                        || ' Error setting role before executing an'
                        || ' ISOK_QUERIES.Query'
              , DETAIL = 'Problem in the role with IQName = ('
                         || this_iqname || E'):\n'
                         || 'The error is: ' || my_message_text
                         || my_pg_exception_detail
                         || my_pg_exception_hint
                         || E'\nThe ISOK_QUERIES.Role is:\n'
                         || saved_role
                         || my_pg_exception_context;
        END;
      END IF;

      BEGIN
        FOR id, msg, extra IN EXECUTE saved_query LOOP
          IF saved_role IS NOT NULL THEN
            -- We don't know that the role used to execute the query
            -- has permissions to the ISOK_RESULTS table.
            EXECUTE become_starting_role;
          END IF;

          -- Remember that we processed the id.
          INSERT INTO _riq_qr_ids (qr_id)
            VALUES (id);

          BEGIN
            -- Make a new isok_results row.
            INSERT INTO @extschema@.isok_results
              (iqname            , first_seen       , last_seen
                     , last_role
                     , last_schemas
                     , keep_until
                     , qr_id, qr_message, qr_extra
                     , notes)
              VALUES (this_iqname, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP
                    , role_used
                    , current_schemas(TRUE)
                    , CASE WHEN this_keep
                        THEN 'infinity'::TIMESTAMP
                        ELSE NULL
                      END
                    , id, msg, extra
                    , '')
              ON CONFLICT
                ON CONSTRAINT "The combination of IQName and QR_ID must be unique"
                -- Update the existing isok_results row.
                DO UPDATE
                  SET last_seen = CURRENT_TIMESTAMP
                    , last_role = role_used
                    , last_schemas = current_schemas(TRUE)
                    , qr_message = msg
                    , qr_extra = extra
                  WHERE @extschema@.isok_results.iqname = this_iqname
                        AND @extschema@.isok_results.qr_id = id;
          EXCEPTION
            -- We want a different error message here because the
            -- context is useless, so don't include that.
            -- When something goes wrong here it's usually the values
            -- returned by the supplied query.  Trap everything,
            -- just in case.
            WHEN feature_not_supported
             OR sql_statement_not_yet_complete
             OR data_exception
             OR integrity_constraint_violation
             OR invalid_sql_statement_name
             OR sql_routine_exception
             OR syntax_error_or_access_rule_violation THEN
              
        GET STACKED DIAGNOSTICS
            my_message_text = MESSAGE_TEXT
          , my_pg_exception_detail = PG_EXCEPTION_DETAIL
          , my_pg_exception_hint = PG_EXCEPTION_HINT
          , my_pg_exception_context = PG_EXCEPTION_CONTEXT;
        
        my_message_text := E'\n' || my_message_text;
        IF my_pg_exception_detail <> '' THEN
           my_message_text := my_message_text || E'\n';
           my_pg_exception_detail := E'\nThe detail is:\n' || my_pg_exception_detail;
        END IF;
        IF my_pg_exception_hint <> '' THEN
           my_pg_exception_detail := my_pg_exception_detail || E'\n';
           my_pg_exception_hint := E'\nThe hint is:\n' || my_pg_exception_hint;
        END IF;
        my_pg_exception_hint := my_pg_exception_hint || E'\n';
        IF my_pg_exception_context <> '' THEN
           my_pg_exception_context := E'\n\nAdditional diagnostic information:\n' || my_pg_exception_context;
        END IF;

              exception_caught := TRUE;
              RAISE EXCEPTION data_exception USING
                MESSAGE = 'run_isok_queries:'
                          || ' Error storing the results of an'
                          || ' ISOK_QUERIES.Query'
                , DETAIL = 'Problem in the saved query with IQName = ('
                           || this_iqname || E'):\n'
                           || 'The error is: ' || my_message_text
                           || my_pg_exception_detail
                           || my_pg_exception_hint
                           || E'\nThe query is:\n'
                           || saved_query;
          END;

          IF saved_role IS NOT NULL THEN
            -- Put the desired effective role back in place for
            -- continued query execution.  There is no documentation
            -- of how row-level security policies execution is
            -- affected by changing the effective role while a query
            -- executes.  So make sure the effective role does not
            -- change while the query executes.
            EXECUTE become_saved_role;
          END IF;
        END LOOP;
      EXCEPTION
        -- We want all of these to trap most that could go wrong when a
        -- user-supplied bit of sql is executed
        WHEN feature_not_supported
             OR sql_statement_not_yet_complete
             OR data_exception
             OR integrity_constraint_violation
             OR invalid_sql_statement_name
             OR sql_routine_exception
             OR syntax_error_or_access_rule_violation THEN
          
        GET STACKED DIAGNOSTICS
            my_message_text = MESSAGE_TEXT
          , my_pg_exception_detail = PG_EXCEPTION_DETAIL
          , my_pg_exception_hint = PG_EXCEPTION_HINT
          , my_pg_exception_context = PG_EXCEPTION_CONTEXT;
        
        my_message_text := E'\n' || my_message_text;
        IF my_pg_exception_detail <> '' THEN
           my_message_text := my_message_text || E'\n';
           my_pg_exception_detail := E'\nThe detail is:\n' || my_pg_exception_detail;
        END IF;
        IF my_pg_exception_hint <> '' THEN
           my_pg_exception_detail := my_pg_exception_detail || E'\n';
           my_pg_exception_hint := E'\nThe hint is:\n' || my_pg_exception_hint;
        END IF;
        my_pg_exception_hint := my_pg_exception_hint || E'\n';
        IF my_pg_exception_context <> '' THEN
           my_pg_exception_context := E'\n\nAdditional diagnostic information:\n' || my_pg_exception_context;
        END IF;

          exception_caught := TRUE;
          RAISE EXCEPTION data_exception USING
            MESSAGE = 'run_isok_queries:'
                      || ' Error executing stored ISOK_QUERIES.Query'
            , DETAIL = 'Problem in the saved query with IQName = ('
                       || this_iqname || E'):\n'
                       || 'The error is: ' || my_message_text
                       || my_pg_exception_detail
                       || my_pg_exception_hint
                       || E'\nThe query is:\n'
                       || saved_query
                       || my_pg_exception_context;
      END;

      -- Reset the role to its initial state
      IF saved_role IS NOT NULL THEN
        EXECUTE become_starting_role;
      END IF;

      -- Reset the search path to its initial state
      IF saved_search_path IS NOT NULL THEN
        EXECUTE 'SET LOCAL search_path TO ' || starting_search_path || ';';
      END IF;

      -- Note that we've processed the saved query.
      UPDATE @extschema@.isok_queries
        SET last_run = CURRENT_TIMESTAMP
          , first_run = COALESCE(first_run, CURRENT_TIMESTAMP)
        WHERE @extschema@.isok_queries.iqname = this_iqname;

      -- Delete old query results that are not found this time.
      CREATE UNIQUE INDEX _riq_qr_ids_qr_id
        ON _riq_qr_ids (qr_id);

      DELETE FROM @extschema@.isok_results
        WHERE @extschema@.isok_results.iqname = this_iqname
              AND (@extschema@.isok_results.keep_until IS NULL
                   OR @extschema@.isok_results.keep_until
                       <= CURRENT_TIMESTAMP::TIMESTAMP(0))
              AND NOT EXISTS(
                  SELECT 1
                    FROM _riq_qr_ids
                    WHERE @extschema@.isok_results.qr_id
                          = _riq_qr_ids.qr_id);

      -- Cleanup
      DROP TABLE _riq_qr_ids CASCADE;

      -- Return the rows found with their new values.
      RETURN QUERY
        SELECT @extschema@.isok_results.irid
             , @extschema@.isok_results.iqname
             , @extschema@.isok_results.first_seen
             , @extschema@.isok_results.last_seen
             , @extschema@.isok_results.last_role
             , @extschema@.isok_results.last_schemas
             , @extschema@.isok_results.deferred_to
             , @extschema@.isok_results.type
             , @extschema@.isok_results.keep_until
             , @extschema@.isok_results.qr_id
             , @extschema@.isok_results.qr_message
             , @extschema@.isok_results.qr_extra
             , @extschema@.isok_results.notes
          FROM @extschema@.isok_results
          WHERE @extschema@.isok_results.iqname = this_iqname
                AND @extschema@.isok_results.resolved IS NULL
                AND (@extschema@.isok_results.deferred_to IS NULL
                     OR @extschema@.isok_results.deferred_to
                        <= CURRENT_TIMESTAMP::TIMESTAMP(0));
    END LOOP; -- Over iqname

  EXCEPTION
    -- We want all of these to trap most that could go wrong when a
    -- user-supplied bit of sql is executed
    WHEN feature_not_supported
             OR sql_statement_not_yet_complete
             OR data_exception
             OR integrity_constraint_violation
             OR invalid_sql_statement_name
             OR sql_routine_exception
             OR syntax_error_or_access_rule_violation THEN
      IF exception_caught THEN
        GET STACKED DIAGNOSTICS
            my_message_text = MESSAGE_TEXT
          , my_pg_exception_detail = PG_EXCEPTION_DETAIL;
        RAISE EXCEPTION data_exception USING
            MESSAGE = my_message_text
          , DETAIL = my_pg_exception_detail;
      ELSE
        
        GET STACKED DIAGNOSTICS
            my_message_text = MESSAGE_TEXT
          , my_pg_exception_detail = PG_EXCEPTION_DETAIL
          , my_pg_exception_hint = PG_EXCEPTION_HINT
          , my_pg_exception_context = PG_EXCEPTION_CONTEXT;
        
        my_message_text := E'\n' || my_message_text;
        IF my_pg_exception_detail <> '' THEN
           my_message_text := my_message_text || E'\n';
           my_pg_exception_detail := E'\nThe detail is:\n' || my_pg_exception_detail;
        END IF;
        IF my_pg_exception_hint <> '' THEN
           my_pg_exception_detail := my_pg_exception_detail || E'\n';
           my_pg_exception_hint := E'\nThe hint is:\n' || my_pg_exception_hint;
        END IF;
        my_pg_exception_hint := my_pg_exception_hint || E'\n';
        IF my_pg_exception_context <> '' THEN
           my_pg_exception_context := E'\n\nAdditional diagnostic information:\n' || my_pg_exception_context;
        END IF;

        RAISE EXCEPTION data_exception USING
          MESSAGE = 'run_isok_queries:'
                    || ' Error executing supplied query argument'
          , DETAIL = E'\nThe error is: ' || my_message_text
                     || my_pg_exception_detail
                     || my_pg_exception_hint
                     || E'\nThe query is:\n'
                     || query
                     || my_pg_exception_context;
      END IF;
   -- WHEN OTHERS THEN
   --   RAISE EXCEPTION data_exception USING
   --     MESSAGE = 'SQLSTATE ' || SQLSTATE || ': SQLERRM ' || SQLERRM;
  END;
$$;
