-- Copyright (C) 2011-2013, 2024, 2025 The Meme Factory, Inc.,
--           http://www.karlpinc.com/
-- Copyright (C) 2004-2011, Karl O. Pinc <kop@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>

-- DO NOT EDIT THIS FILE.  It was automatically generated.  Edit
-- the *.m4 files instead.  (Files _should_ be re-created by
-- typing 'make', with the appropriate target, at the command line.)






--
-- Support Tables
--

-- The key, description support tables.


CREATE TABLE IQ_TYPES (
  IQType TEXT
                PRIMARY KEY NOT NULL            CONSTRAINT "IQType: Cannot be empty or only whitespace characters"
                CHECK(btrim(IQType, E' \r\n\t\f\x0B') <> '')
                             CONSTRAINT "IQType: Cannot contain any whitespace characters"
                CHECK(IQType IS NULL OR
                      IQType !~ '( |\r|\n|\t|\f|\x0B)'),
  Description TEXT NOT NULL
                   CONSTRAINT "Description: Cannot be empty or only whitespace characters"
                CHECK(btrim(Description, E' \r\n\t\f\x0B') <> '')
);



CREATE TABLE IR_TYPES (
  IRType TEXT
                PRIMARY KEY NOT NULL            CONSTRAINT "IRType: Cannot be empty or only whitespace characters"
                CHECK(btrim(IRType, E' \r\n\t\f\x0B') <> '')
                             CONSTRAINT "IRType: Cannot contain any whitespace characters"
                CHECK(IRType IS NULL OR
                      IRType !~ '( |\r|\n|\t|\f|\x0B)'),
  Description TEXT NOT NULL
                   CONSTRAINT "Description: Cannot be empty or only whitespace characters"
                CHECK(btrim(Description, E' \r\n\t\f\x0B') <> '')
);


--
-- The primary tables
-- 

-- isok_queries
CREATE TABLE isok_queries (
    iqname TEXT PRIMARY KEY
                      CONSTRAINT "IQName: Cannot be empty or only whitespace characters"
                CHECK(btrim(IQName, E' \r\n\t\f\x0B') <> '')
                      CONSTRAINT "IQName: Cannot contain any whitespace characters"
                CHECK(IQName IS NULL OR
                      IQName !~ '( |\r|\n|\t|\f|\x0B)')
    , 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
                       CONSTRAINT "Query: Cannot be empty or only whitespace characters"
                CHECK(btrim(Query, E' \r\n\t\f\x0B') <> '')
    , comment TEXT NOT NULL
                          CONSTRAINT "Comment: Cannot be only whitespace characters"
                CHECK(Comment IS NULL OR Comment = '' OR btrim(Comment, E' \r\n\t\f\x0B') <> '')
);

-- 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
                          CONSTRAINT "QR_ID: Cannot be empty or only whitespace characters"
                CHECK(btrim(QR_ID, E' \r\n\t\f\x0B') <> '')
  , qr_message TEXT NOT NULL
                          CONSTRAINT "QR_Message: Cannot be empty or only whitespace characters"
                CHECK(btrim(QR_Message, E' \r\n\t\f\x0B') <> '')
  , qr_extra JSONB
  , notes TEXT NOT NULL
                     CONSTRAINT "Notes: Cannot be only whitespace characters"
                CHECK(Notes IS NULL OR Notes = '' OR btrim(Notes, E' \r\n\t\f\x0B') <> '')
  , CONSTRAINT "The combination of IQName and QR_ID must be unique"
      UNIQUE (iqname, qr_id)
);