-- pg_isok  A query centered monitoring tool for PostgreSQL
-- Copyright (C) 2015, 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>
--
-- Remarks:
--

-- Create indexes on all the Isok tables.

-- Remarks:

-- isok_queries
-- This first index is a simple way to enforce case-insensitive
-- uniqueness, but is not otherwise useful.
-- "lower() is not exactly the same as case-folding, because Unicode
-- and human language, but it is close enough.
CREATE UNIQUE INDEX "ISOK_QUERIES.IQName is case-insensitively unique"
  ON isok_queries (lower(iqname));
CREATE INDEX isok_queries_type
  ON isok_queries (type);
CREATE INDEX isok_queries_last_run
  ON isok_queries (last_run);

-- isok_results
CREATE INDEX isok_results_first_seen
  ON isok_results (first_seen);
CREATE INDEX isok_results_last_seen
  ON isok_results (last_seen);
CREATE INDEX isok_results_resolved
  ON isok_results (resolved)
  WHERE resolved IS NOT NULL;
CREATE INDEX isok_results_deferred_to
  ON isok_results (deferred_to)
  WHERE deferred_to IS NOT NULL;
CREATE INDEX isok_results_category
  ON isok_results (category);
CREATE INDEX isok_results_qr_id
  ON isok_results (qr_id);

--
-- Support tables
--

-- All support tables require their keys be case-insensitive unique.
-- See the comment on INDEX isok_queries_caseunique_iqname, above.


-- iq_types
CREATE UNIQUE INDEX "IQ_TYPES.IQType is case-insensitively unique"
  ON iq_types (lower(iqtype));
CREATE UNIQUE INDEX "IQ_TYPES.Description is case-insensitively unique"
  ON iq_types (lower(description));

-- ir_types
CREATE UNIQUE INDEX "IR_TYPES.IRType is case-insensitively unique"
  ON ir_types (lower(irtype));
CREATE UNIQUE INDEX "IR_TYPES.Description is case-insensitively unique"
  ON ir_types (lower(description));