/*
 * Copyright 2001-2005 The Apache Software Foundation.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
CREATE TABLE CATALOG
(
  ID SERIAL8  UNIQUE PRIMARY KEY,
  LAST_UPDATE TIMESTAMP NOT NULL
);

CREATE TABLE EVENT_ATTRIBUTE
(
  ID SERIAL8  UNIQUE PRIMARY KEY,
  CATALOG_ID VARCHAR(64) NOT NULL,
  NAME VARCHAR(64) NOT NULL,
  DISPLAY_NAME VARCHAR(64) NOT NULL,
  DESCRIPTION VARCHAR(1024),
  DATATYPE VARCHAR(64),
  INDEXED CHAR,
  SORTABLE CHAR,
  REQUIRED CHAR,
  REQUEST_CONTEXT CHAR
);

CREATE INDEX ATTRIBUTE_NAME ON EVENT_ATTRIBUTE (NAME);

CREATE UNIQUE INDEX IDX_ATTR_CATID_NAME ON EVENT_ATTRIBUTE (CATALOG_ID, NAME);

CREATE TABLE ATTRIBUTE_EXAMPLES
(
  ATTRIBUTE_ID BIGINT REFERENCES EVENT_ATTRIBUTE (ID),
  EXAMPLE VARCHAR(64)
);

CREATE TABLE ATTRIBUTE_ALIASES
(
  ATTRIBUTE_ID BIGINT REFERENCES EVENT_ATTRIBUTE (ID),
  ALIAS VARCHAR(64)
);

CREATE TABLE ATTRIBUTE_CONSTRAINT
(
  ID SERIAL8  UNIQUE  PRIMARY KEY,
  ATTRIBUTE_ID BIGINT REFERENCES EVENT_ATTRIBUTE (ID),
  CONSTRAINT_TYPE VARCHAR(32) NOT NULL,
  VALUE VARCHAR(256)
);

CREATE TABLE CATALOG_EVENT
(
  ID SERIAL8  UNIQUE  PRIMARY KEY,
  CATALOG_ID VARCHAR(64) NOT NULL,
  NAME VARCHAR(64) NOT NULL,
  DISPLAY_NAME VARCHAR(64) NOT NULL,
  DESCRIPTION VARCHAR(1024)
);

CREATE INDEX EVENT_NAME ON CATALOG_EVENT (NAME);

CREATE UNIQUE INDEX IDX_EVENT_CATID_NAME ON CATALOG_EVENT (CATALOG_ID, NAME);

CREATE TABLE EVENT_ALIASES
(
  EVENT_ID BIGINT REFERENCES CATALOG_EVENT (ID),
  ALIAS VARCHAR(64) NOT NULL
);

CREATE TABLE EVENT_ATTRIBUTES
(
  ID SERIAL8  UNIQUE  PRIMARY KEY,
  EVENT_ID BIGINT REFERENCES CATALOG_EVENT (ID),
  ATTRIBUTE_ID BIGINT REFERENCES EVENT_ATTRIBUTE (ID),
  IS_REQUIRED CHAR
);

CREATE TABLE CATALOG_CATEGORY
(
  ID SERIAL8  UNIQUE  PRIMARY KEY,
  CATALOG_ID VARCHAR(64) NOT NULL,
  NAME VARCHAR(64) NOT NULL,
  DISPLAY_NAME VARCHAR(64) NOT NULL,
  DESCRIPTION VARCHAR(1024)

);

CREATE INDEX CATEGORY_NAME ON CATALOG_CATEGORY(NAME);

CREATE UNIQUE INDEX IDX_CATEGORY_CATID_NAME ON CATALOG_CATEGORY (CATALOG_ID, NAME);

CREATE TABLE CATEGORY_EVENTS
(
  CATEGORY_ID BIGINT REFERENCES CATALOG_CATEGORY (ID),
  EVENT_ID BIGINT REFERENCES CATALOG_EVENT (ID)
);

CREATE TABLE CATALOG_PRODUCT
(
  ID SERIAL8  UNIQUE  PRIMARY KEY,
  CATALOG_ID VARCHAR(64) NOT NULL,
  NAME VARCHAR(64) NOT NULL,
  DISPLAY_NAME VARCHAR(64) NOT NULL,
  DESCRIPTION VARCHAR(1024)

);

CREATE INDEX PRODUCT_NAME ON CATALOG_PRODUCT(NAME);

CREATE UNIQUE INDEX IDX_PRODUCT_CATID_NAME ON CATALOG_PRODUCT (CATALOG_ID, NAME);

CREATE TABLE PRODUCT_EVENTS
(
  PRODUCT_ID BIGINT REFERENCES CATALOG_PRODUCT (ID),
  EVENT_ID BIGINT REFERENCES CATALOG_EVENT (ID)
);