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

--
-- Support Tables
--

-- The key, description support tables.

support_table(`IQ_TYPES', `IQType', `TEXT'
               , `empty_string_check(`IQType')
                  no_spaces_check(`IQType')')

support_table(`IR_TYPES', `IRType', `TEXT'
               , `empty_string_check(`IRType')
                  no_spaces_check(`IRType')')

--
-- The primary tables
-- 

-- isok_queries
CREATE TABLE isok_queries (
    iqname TEXT PRIMARY KEY
           empty_string_check(`IQName')
           no_spaces_check(`IQName')
    , error BOOLEAN NOT NULL
    , type TEXT NOT NULL
      CONSTRAINT "Type must be an IQ_TYPES.IQType value"
                 REFERENCES iq_types
    , keep BOOLEAN NOT NULL
    , first_run TIMESTAMP(0)
    , last_run TIMESTAMP(0)
               CONSTRAINT "Last_Run must be >= First_Run"
                 CHECK(last_run IS NULL
                       OR first_run IS NULL
                       OR first_run <= last_run)
    , role name
    , search_path TEXT
    , query TEXT NOT NULL
            empty_string_check(`Query')
    , comment TEXT NOT NULL
               not_only_spaces_check(`Comment')
);

-- isok_results
CREATE TABLE isok_results (
    irid BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
  , iqname TEXT NOT NULL
           CONSTRAINT "IQName must be an ISOK_QUERIES.IQName"
                       REFERENCES isok_queries
  , first_seen TIMESTAMP(0) NOT NULL
  , last_seen TIMESTAMP(0) NOT NULL
               CONSTRAINT "Last_Seen must be >= First_Seen"
                 CHECK(first_seen <= last_seen)
  , last_role name NOT NULL
  , last_schemas name[] NOT NULL
  , resolved TIMESTAMP(0)
             CONSTRAINT "Resolved must be >= First_Seen"
               CHECK(resolved IS NULL OR first_seen <= resolved)
  , deferred_to TIMESTAMP(0)
            CONSTRAINT "Either Resolved or Deferred_To or both must be NULL"
              CHECK(NOT(resolved IS NOT NULL AND deferred_to IS NOT NULL))
            CONSTRAINT "Deferred_To must be >= First_Seen"
               CHECK(deferred_to IS NULL OR first_seen <= deferred_to)
  , category TEXT
             CONSTRAINT "Category must be an IR_TYPES.IRType value"
                        REFERENCES ir_types
  , keep_until TIMESTAMP(0)
  , qr_id TEXT NOT NULL
               empty_string_check(`QR_ID')
  , qr_message TEXT NOT NULL
               empty_string_check(`QR_Message')
  , qr_extra JSONB
  , notes TEXT NOT NULL
          not_only_spaces_check(`Notes')
  , CONSTRAINT "isok_unique_result_constraint"
      UNIQUE (iqname, qr_id)
);