dnl Copyright (C) 2015 The Meme Factory, Inc., http://www.meme.com/
dnl
dnl    This file is part of Gombe-MI.
dnl
dnl    Gombe-MI is free software; you can redistribute it and/or modify
dnl    it under the terms of the GNU General Public License as published by
dnl    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 General Public License for more details.
dnl
dnl    You should have received a copy of the GNU General Public License
dnl    along with Gombe-MI.  If not, see <http://www.gnu.org/licenses/>.
dnl
dnl Karl O. Pinc <kop@meme.com>
dnl
dnl Remarks:
dnl   A separate file from maketables.m4 since it's purpose is orthogonal.
dnl
dnl m4 includes
include(`copyright.m4')
include(`constants.m4')
include(`grants.m4')
include(`types.m4')
include(`tablemacros.m4')


--
-- Support Tables
--

-- The key, descr support tables.

-- support`'_table(`foo', `fookey', `SMALLINT', `')
-- support`'_table(`bar', `barkey', `CHAR(1)'
--             , `empty_`'string_check(`barkey')')

support_table(`IQTYPES', `IQType', `VARCHAR(warn_iqtype_len)'
               , `empty_string_check(`IQType')
                  space_not_allowed_check(`IQType')')

support_table(`WARNING_REMARKS', `WRID', `VARCHAR(warn_wrid_len)'
               , `empty_string_check(`WRID')
                  space_not_allowed_check(`WRID')')

--
-- The primary tables
-- 

-- integrity_queries
CREATE TABLE integrity_queries (
    iqname VARCHAR(warn_iqname_len) PRIMARY KEY
           empty_string_check(`IQName')
           space_not_allowed_check(`IQName')
    , error BOOLEAN NOT NULL
    , type VARCHAR(warn_iqtype_len) NOT NULL
      CONSTRAINT "Type on IQTypes" REFERENCES iqtypes
    , first_run TIMESTAMP(0) WITHOUT TIME ZONE
    , last_run TIMESTAMP(0) WITHOUT TIME ZONE
               CONSTRAINT "Last_Run must be >= First_Run"
                 CHECK(last_run IS NULL
                       OR first_run IS NULL
                       OR first_run <= last_run)
    , query TEXT NOT NULL
            empty_string_check(`Query')
    , comment TEXT
               empty_string_check(`Comment')
);

grant_priv(`INTEGRITY_QUERIES')

-- integrity_warnings
CREATE TABLE integrity_warnings (
    iwid SERIAL PRIMARY KEY
  , iqname VARCHAR(warn_iqname_len) NOT NULL
  , first_seen TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL
  , last_seen TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL
               CONSTRAINT "Last_Seen must be >= First_Seen"
                 CHECK(first_seen <= last_seen)
  , resolved TIMESTAMP(0) WITHOUT TIME ZONE
             CONSTRAINT "Resolved must be >= First_Seen"
               CHECK(resolved IS NULL OR first_seen <= resolved)
  , deferred_to TIMESTAMP(0) WITHOUT TIME ZONE
            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 VARCHAR(warn_wrid_len)
             CONSTRAINT "Category on WARNING_REMARKS"
                        REFERENCES warning_remarks
  , warning_id TEXT NOT NULL
               empty_string_check(`Warning_ID')
  , warning_message TEXT NOT NULL
                    empty_string_check(`Warning_Message')
  , notes TEXT
          empty_string_check(`Notes')
);

-- Let editors update everything but warning_id and warning_message.
grant_priv(`INTEGRITY_WARNINGS')
grant_seq_priv(`INTEGRITY_WARNINGS', `IWID')