# -----------------------------------------------------------------------
# FOLDER
# -----------------------------------------------------------------------
drop table if exists FOLDER;

CREATE TABLE FOLDER
(
    FOLDER_ID MEDIUMINT NOT NULL,
    PARENT_ID MEDIUMINT,
    PATH VARCHAR(240) NOT NULL,
    NAME VARCHAR(80) NOT NULL,
    TITLE VARCHAR(100),
    SHORT_TITLE VARCHAR(40),
    IS_HIDDEN INTEGER NOT NULL,
    SKIN VARCHAR(80),
    DEFAULT_LAYOUT_DECORATOR VARCHAR(80),
    DEFAULT_PORTLET_DECORATOR VARCHAR(80),
    DEFAULT_PAGE_NAME VARCHAR(80),
    SUBSITE VARCHAR(40),
    USER_PRINCIPAL VARCHAR(40),
    ROLE_PRINCIPAL VARCHAR(40),
    GROUP_PRINCIPAL VARCHAR(40),
    MEDIATYPE VARCHAR(15),
    LOCALE VARCHAR(20),
    EXT_ATTR_NAME VARCHAR(15),
    EXT_ATTR_VALUE VARCHAR(40),
    OWNER_PRINCIPAL VARCHAR(40),
    PRIMARY KEY(FOLDER_ID),
    FOREIGN KEY (PARENT_ID) REFERENCES FOLDER (FOLDER_ID)
        ON DELETE CASCADE 
  ,
    UNIQUE (PATH),
    INDEX IX_FOLDER_1 (PARENT_ID));

# -----------------------------------------------------------------------
# FOLDER_METADATA
# -----------------------------------------------------------------------
drop table if exists FOLDER_METADATA;

CREATE TABLE FOLDER_METADATA
(
    METADATA_ID MEDIUMINT NOT NULL,
    FOLDER_ID MEDIUMINT NOT NULL,
    NAME VARCHAR(15) NOT NULL,
    LOCALE VARCHAR(20) NOT NULL,
    VALUE VARCHAR(100) NOT NULL,
    PRIMARY KEY(METADATA_ID),
    FOREIGN KEY (FOLDER_ID) REFERENCES FOLDER (FOLDER_ID)
        ON DELETE CASCADE 
  ,
    UNIQUE (FOLDER_ID, NAME, LOCALE, VALUE),
    INDEX IX_FOLDER_METADATA_1 (FOLDER_ID));

# -----------------------------------------------------------------------
# FOLDER_CONSTRAINT
# -----------------------------------------------------------------------
drop table if exists FOLDER_CONSTRAINT;

CREATE TABLE FOLDER_CONSTRAINT
(
    CONSTRAINT_ID MEDIUMINT NOT NULL,
    FOLDER_ID MEDIUMINT NOT NULL,
    APPLY_ORDER MEDIUMINT NOT NULL,
    USER_PRINCIPALS_ACL VARCHAR(120),
    ROLE_PRINCIPALS_ACL VARCHAR(120),
    GROUP_PRINCIPALS_ACL VARCHAR(120),
    PERMISSIONS_ACL VARCHAR(120),
    PRIMARY KEY(CONSTRAINT_ID),
    FOREIGN KEY (FOLDER_ID) REFERENCES FOLDER (FOLDER_ID)
        ON DELETE CASCADE 
  ,
    INDEX IX_FOLDER_CONSTRAINT_1 (FOLDER_ID));

# -----------------------------------------------------------------------
# FOLDER_CONSTRAINTS_REF
# -----------------------------------------------------------------------
drop table if exists FOLDER_CONSTRAINTS_REF;

CREATE TABLE FOLDER_CONSTRAINTS_REF
(
    CONSTRAINTS_REF_ID MEDIUMINT NOT NULL,
    FOLDER_ID MEDIUMINT NOT NULL,
    APPLY_ORDER MEDIUMINT NOT NULL,
    NAME VARCHAR(40) NOT NULL,
    PRIMARY KEY(CONSTRAINTS_REF_ID),
    FOREIGN KEY (FOLDER_ID) REFERENCES FOLDER (FOLDER_ID)
        ON DELETE CASCADE 
  ,
    UNIQUE (FOLDER_ID, NAME),
    INDEX IX_FOLDER_CONSTRAINTS_REF_1 (FOLDER_ID));

# -----------------------------------------------------------------------
# FOLDER_ORDER
# -----------------------------------------------------------------------
drop table if exists FOLDER_ORDER;

CREATE TABLE FOLDER_ORDER
(
    ORDER_ID MEDIUMINT NOT NULL,
    FOLDER_ID MEDIUMINT NOT NULL,
    SORT_ORDER MEDIUMINT NOT NULL,
    NAME VARCHAR(80) NOT NULL,
    PRIMARY KEY(ORDER_ID),
    FOREIGN KEY (FOLDER_ID) REFERENCES FOLDER (FOLDER_ID)
        ON DELETE CASCADE 
  ,
    UNIQUE (FOLDER_ID, NAME),
    INDEX IX_FOLDER_ORDER_1 (FOLDER_ID));

# -----------------------------------------------------------------------
# FOLDER_MENU
# -----------------------------------------------------------------------
drop table if exists FOLDER_MENU;

CREATE TABLE FOLDER_MENU
(
    MENU_ID MEDIUMINT NOT NULL,
    CLASS_NAME VARCHAR(100) NOT NULL,
    PARENT_ID MEDIUMINT,
    FOLDER_ID MEDIUMINT,
    ELEMENT_ORDER MEDIUMINT,
    NAME VARCHAR(100),
    TITLE VARCHAR(100),
    SHORT_TITLE VARCHAR(40),
    TEXT VARCHAR(100),
    OPTIONS VARCHAR(255),
    DEPTH MEDIUMINT,
    IS_PATHS INTEGER,
    IS_REGEXP INTEGER,
    PROFILE VARCHAR(80),
    OPTIONS_ORDER VARCHAR(255),
    SKIN VARCHAR(80),
    IS_NEST INTEGER,
    PRIMARY KEY(MENU_ID),
    FOREIGN KEY (PARENT_ID) REFERENCES FOLDER_MENU (MENU_ID)
        ON DELETE CASCADE 
  ,
    FOREIGN KEY (FOLDER_ID) REFERENCES FOLDER (FOLDER_ID)
        ON DELETE CASCADE 
  ,
    INDEX IX_FOLDER_MENU_1 (PARENT_ID),
    INDEX UN_FOLDER_MENU_1 (FOLDER_ID, NAME));

# -----------------------------------------------------------------------
# FOLDER_MENU_METADATA
# -----------------------------------------------------------------------
drop table if exists FOLDER_MENU_METADATA;

CREATE TABLE FOLDER_MENU_METADATA
(
    METADATA_ID MEDIUMINT NOT NULL,
    MENU_ID MEDIUMINT NOT NULL,
    NAME VARCHAR(15) NOT NULL,
    LOCALE VARCHAR(20) NOT NULL,
    VALUE VARCHAR(100) NOT NULL,
    PRIMARY KEY(METADATA_ID),
    FOREIGN KEY (MENU_ID) REFERENCES FOLDER_MENU (MENU_ID)
        ON DELETE CASCADE 
  ,
    UNIQUE (MENU_ID, NAME, LOCALE, VALUE),
    INDEX IX_FOLDER_MENU_METADATA_1 (MENU_ID));

# -----------------------------------------------------------------------
# PAGE
# -----------------------------------------------------------------------
drop table if exists PAGE;

CREATE TABLE PAGE
(
    PAGE_ID MEDIUMINT NOT NULL,
    PARENT_ID MEDIUMINT NOT NULL,
    PATH VARCHAR(240) NOT NULL,
    NAME VARCHAR(80) NOT NULL,
    VERSION VARCHAR(40),
    TITLE VARCHAR(100),
    SHORT_TITLE VARCHAR(40),
    IS_HIDDEN INTEGER NOT NULL,
    SKIN VARCHAR(80),
    DEFAULT_LAYOUT_DECORATOR VARCHAR(80),
    DEFAULT_PORTLET_DECORATOR VARCHAR(80),
    SUBSITE VARCHAR(40),
    USER_PRINCIPAL VARCHAR(40),
    ROLE_PRINCIPAL VARCHAR(40),
    GROUP_PRINCIPAL VARCHAR(40),
    MEDIATYPE VARCHAR(15),
    LOCALE VARCHAR(20),
    EXT_ATTR_NAME VARCHAR(15),
    EXT_ATTR_VALUE VARCHAR(40),
    OWNER_PRINCIPAL VARCHAR(40),
    PRIMARY KEY(PAGE_ID),
    FOREIGN KEY (PARENT_ID) REFERENCES FOLDER (FOLDER_ID)
        ON DELETE CASCADE 
  ,
    UNIQUE (PATH),
    INDEX IX_PAGE_1 (PARENT_ID));

# -----------------------------------------------------------------------
# PAGE_METADATA
# -----------------------------------------------------------------------
drop table if exists PAGE_METADATA;

CREATE TABLE PAGE_METADATA
(
    METADATA_ID MEDIUMINT NOT NULL,
    PAGE_ID MEDIUMINT NOT NULL,
    NAME VARCHAR(15) NOT NULL,
    LOCALE VARCHAR(20) NOT NULL,
    VALUE VARCHAR(100) NOT NULL,
    PRIMARY KEY(METADATA_ID),
    FOREIGN KEY (PAGE_ID) REFERENCES PAGE (PAGE_ID)
        ON DELETE CASCADE 
  ,
    UNIQUE (PAGE_ID, NAME, LOCALE, VALUE),
    INDEX IX_PAGE_METADATA_1 (PAGE_ID));

# -----------------------------------------------------------------------
# PAGE_CONSTRAINT
# -----------------------------------------------------------------------
drop table if exists PAGE_CONSTRAINT;

CREATE TABLE PAGE_CONSTRAINT
(
    CONSTRAINT_ID MEDIUMINT NOT NULL,
    PAGE_ID MEDIUMINT NOT NULL,
    APPLY_ORDER MEDIUMINT NOT NULL,
    USER_PRINCIPALS_ACL VARCHAR(120),
    ROLE_PRINCIPALS_ACL VARCHAR(120),
    GROUP_PRINCIPALS_ACL VARCHAR(120),
    PERMISSIONS_ACL VARCHAR(120),
    PRIMARY KEY(CONSTRAINT_ID),
    FOREIGN KEY (PAGE_ID) REFERENCES PAGE (PAGE_ID)
        ON DELETE CASCADE 
  ,
    INDEX IX_PAGE_CONSTRAINT_1 (PAGE_ID));

# -----------------------------------------------------------------------
# PAGE_CONSTRAINTS_REF
# -----------------------------------------------------------------------
drop table if exists PAGE_CONSTRAINTS_REF;

CREATE TABLE PAGE_CONSTRAINTS_REF
(
    CONSTRAINTS_REF_ID MEDIUMINT NOT NULL,
    PAGE_ID MEDIUMINT NOT NULL,
    APPLY_ORDER MEDIUMINT NOT NULL,
    NAME VARCHAR(40) NOT NULL,
    PRIMARY KEY(CONSTRAINTS_REF_ID),
    FOREIGN KEY (PAGE_ID) REFERENCES PAGE (PAGE_ID)
        ON DELETE CASCADE 
  ,
    UNIQUE (PAGE_ID, NAME),
    INDEX IX_PAGE_CONSTRAINTS_REF_1 (PAGE_ID));

# -----------------------------------------------------------------------
# PAGE_MENU
# -----------------------------------------------------------------------
drop table if exists PAGE_MENU;

CREATE TABLE PAGE_MENU
(
    MENU_ID MEDIUMINT NOT NULL,
    CLASS_NAME VARCHAR(100) NOT NULL,
    PARENT_ID MEDIUMINT,
    PAGE_ID MEDIUMINT,
    ELEMENT_ORDER MEDIUMINT,
    NAME VARCHAR(100),
    TITLE VARCHAR(100),
    SHORT_TITLE VARCHAR(40),
    TEXT VARCHAR(100),
    OPTIONS VARCHAR(255),
    DEPTH MEDIUMINT,
    IS_PATHS INTEGER,
    IS_REGEXP INTEGER,
    PROFILE VARCHAR(80),
    OPTIONS_ORDER VARCHAR(255),
    SKIN VARCHAR(80),
    IS_NEST INTEGER,
    PRIMARY KEY(MENU_ID),
    FOREIGN KEY (PARENT_ID) REFERENCES PAGE_MENU (MENU_ID)
        ON DELETE CASCADE 
  ,
    FOREIGN KEY (PAGE_ID) REFERENCES PAGE (PAGE_ID)
        ON DELETE CASCADE 
  ,
    INDEX IX_PAGE_MENU_1 (PARENT_ID),
    INDEX UN_PAGE_MENU_1 (PAGE_ID, NAME));

# -----------------------------------------------------------------------
# PAGE_MENU_METADATA
# -----------------------------------------------------------------------
drop table if exists PAGE_MENU_METADATA;

CREATE TABLE PAGE_MENU_METADATA
(
    METADATA_ID MEDIUMINT NOT NULL,
    MENU_ID MEDIUMINT NOT NULL,
    NAME VARCHAR(15) NOT NULL,
    LOCALE VARCHAR(20) NOT NULL,
    VALUE VARCHAR(100) NOT NULL,
    PRIMARY KEY(METADATA_ID),
    FOREIGN KEY (MENU_ID) REFERENCES PAGE_MENU (MENU_ID)
        ON DELETE CASCADE 
  ,
    UNIQUE (MENU_ID, NAME, LOCALE, VALUE),
    INDEX IX_PAGE_MENU_METADATA_1 (MENU_ID));

# -----------------------------------------------------------------------
# FRAGMENT
# -----------------------------------------------------------------------
drop table if exists FRAGMENT;

CREATE TABLE FRAGMENT
(
    FRAGMENT_ID MEDIUMINT NOT NULL,
    PARENT_ID MEDIUMINT,
    PAGE_ID MEDIUMINT,
    NAME VARCHAR(100),
    TITLE VARCHAR(100),
    SHORT_TITLE VARCHAR(40),
    TYPE VARCHAR(40),
    SKIN VARCHAR(80),
    DECORATOR VARCHAR(80),
    STATE VARCHAR(10),
    PMODE VARCHAR(10),
    LAYOUT_ROW MEDIUMINT,
    LAYOUT_COLUMN MEDIUMINT,
    LAYOUT_SIZES VARCHAR(20),
    LAYOUT_X REAL,
    LAYOUT_Y REAL,
    LAYOUT_Z REAL,
    LAYOUT_WIDTH REAL,
    LAYOUT_HEIGHT REAL,
    EXT_PROP_NAME_1 VARCHAR(40),
    EXT_PROP_VALUE_1 VARCHAR(80),
    EXT_PROP_NAME_2 VARCHAR(40),
    EXT_PROP_VALUE_2 VARCHAR(80),
    OWNER_PRINCIPAL VARCHAR(40),
    PRIMARY KEY(FRAGMENT_ID),
    FOREIGN KEY (PARENT_ID) REFERENCES FRAGMENT (FRAGMENT_ID)
        ON DELETE CASCADE 
  ,
    FOREIGN KEY (PAGE_ID) REFERENCES PAGE (PAGE_ID)
        ON DELETE CASCADE 
  ,
    INDEX IX_FRAGMENT_1 (PARENT_ID),
    INDEX UN_FRAGMENT_1 (PAGE_ID));

# -----------------------------------------------------------------------
# FRAGMENT_CONSTRAINT
# -----------------------------------------------------------------------
drop table if exists FRAGMENT_CONSTRAINT;

CREATE TABLE FRAGMENT_CONSTRAINT
(
    CONSTRAINT_ID MEDIUMINT NOT NULL,
    FRAGMENT_ID MEDIUMINT NOT NULL,
    APPLY_ORDER MEDIUMINT NOT NULL,
    USER_PRINCIPALS_ACL VARCHAR(120),
    ROLE_PRINCIPALS_ACL VARCHAR(120),
    GROUP_PRINCIPALS_ACL VARCHAR(120),
    PERMISSIONS_ACL VARCHAR(120),
    PRIMARY KEY(CONSTRAINT_ID),
    FOREIGN KEY (FRAGMENT_ID) REFERENCES FRAGMENT (FRAGMENT_ID)
        ON DELETE CASCADE 
  ,
    INDEX IX_FRAGMENT_CONSTRAINT_1 (FRAGMENT_ID));

# -----------------------------------------------------------------------
# FRAGMENT_CONSTRAINTS_REF
# -----------------------------------------------------------------------
drop table if exists FRAGMENT_CONSTRAINTS_REF;

CREATE TABLE FRAGMENT_CONSTRAINTS_REF
(
    CONSTRAINTS_REF_ID MEDIUMINT NOT NULL,
    FRAGMENT_ID MEDIUMINT NOT NULL,
    APPLY_ORDER MEDIUMINT NOT NULL,
    NAME VARCHAR(40) NOT NULL,
    PRIMARY KEY(CONSTRAINTS_REF_ID),
    FOREIGN KEY (FRAGMENT_ID) REFERENCES FRAGMENT (FRAGMENT_ID)
        ON DELETE CASCADE 
  ,
    UNIQUE (FRAGMENT_ID, NAME),
    INDEX IX_FRAGMENT_CONSTRAINTS_REF_1 (FRAGMENT_ID));

# -----------------------------------------------------------------------
# FRAGMENT_PREF
# -----------------------------------------------------------------------
drop table if exists FRAGMENT_PREF;

CREATE TABLE FRAGMENT_PREF
(
    PREF_ID MEDIUMINT NOT NULL,
    FRAGMENT_ID MEDIUMINT NOT NULL,
    NAME VARCHAR(40) NOT NULL,
    IS_READ_ONLY INTEGER NOT NULL,
    PRIMARY KEY(PREF_ID),
    FOREIGN KEY (FRAGMENT_ID) REFERENCES FRAGMENT (FRAGMENT_ID)
        ON DELETE CASCADE 
  ,
    UNIQUE (FRAGMENT_ID, NAME),
    INDEX IX_FRAGMENT_PREF_1 (FRAGMENT_ID));

# -----------------------------------------------------------------------
# FRAGMENT_PREF_VALUE
# -----------------------------------------------------------------------
drop table if exists FRAGMENT_PREF_VALUE;

CREATE TABLE FRAGMENT_PREF_VALUE
(
    PREF_VALUE_ID MEDIUMINT NOT NULL,
    PREF_ID MEDIUMINT NOT NULL,
    VALUE_ORDER MEDIUMINT NOT NULL,
    VALUE VARCHAR(100) NOT NULL,
    PRIMARY KEY(PREF_VALUE_ID),
    FOREIGN KEY (PREF_ID) REFERENCES FRAGMENT_PREF (PREF_ID)
        ON DELETE CASCADE 
  ,
    INDEX IX_FRAGMENT_PREF_VALUE_1 (PREF_ID));

# -----------------------------------------------------------------------
# LINK
# -----------------------------------------------------------------------
drop table if exists LINK;

CREATE TABLE LINK
(
    LINK_ID MEDIUMINT NOT NULL,
    PARENT_ID MEDIUMINT NOT NULL,
    PATH VARCHAR(240) NOT NULL,
    NAME VARCHAR(80) NOT NULL,
    VERSION VARCHAR(40),
    TITLE VARCHAR(100),
    SHORT_TITLE VARCHAR(40),
    IS_HIDDEN INTEGER NOT NULL,
    SKIN VARCHAR(80),
    TARGET VARCHAR(80),
    URL VARCHAR(255),
    SUBSITE VARCHAR(40),
    USER_PRINCIPAL VARCHAR(40),
    ROLE_PRINCIPAL VARCHAR(40),
    GROUP_PRINCIPAL VARCHAR(40),
    MEDIATYPE VARCHAR(15),
    LOCALE VARCHAR(20),
    EXT_ATTR_NAME VARCHAR(15),
    EXT_ATTR_VALUE VARCHAR(40),
    OWNER_PRINCIPAL VARCHAR(40),
    PRIMARY KEY(LINK_ID),
    FOREIGN KEY (PARENT_ID) REFERENCES FOLDER (FOLDER_ID)
        ON DELETE CASCADE 
  ,
    UNIQUE (PATH),
    INDEX IX_LINK_1 (PARENT_ID));

# -----------------------------------------------------------------------
# LINK_METADATA
# -----------------------------------------------------------------------
drop table if exists LINK_METADATA;

CREATE TABLE LINK_METADATA
(
    METADATA_ID MEDIUMINT NOT NULL,
    LINK_ID MEDIUMINT NOT NULL,
    NAME VARCHAR(15) NOT NULL,
    LOCALE VARCHAR(20) NOT NULL,
    VALUE VARCHAR(100) NOT NULL,
    PRIMARY KEY(METADATA_ID),
    FOREIGN KEY (LINK_ID) REFERENCES LINK (LINK_ID)
        ON DELETE CASCADE 
  ,
    UNIQUE (LINK_ID, NAME, LOCALE, VALUE),
    INDEX IX_LINK_METADATA_1 (LINK_ID));

# -----------------------------------------------------------------------
# LINK_CONSTRAINT
# -----------------------------------------------------------------------
drop table if exists LINK_CONSTRAINT;

CREATE TABLE LINK_CONSTRAINT
(
    CONSTRAINT_ID MEDIUMINT NOT NULL,
    LINK_ID MEDIUMINT NOT NULL,
    APPLY_ORDER MEDIUMINT NOT NULL,
    USER_PRINCIPALS_ACL VARCHAR(120),
    ROLE_PRINCIPALS_ACL VARCHAR(120),
    GROUP_PRINCIPALS_ACL VARCHAR(120),
    PERMISSIONS_ACL VARCHAR(120),
    PRIMARY KEY(CONSTRAINT_ID),
    FOREIGN KEY (LINK_ID) REFERENCES LINK (LINK_ID)
        ON DELETE CASCADE 
  ,
    INDEX IX_LINK_CONSTRAINT_1 (LINK_ID));

# -----------------------------------------------------------------------
# LINK_CONSTRAINTS_REF
# -----------------------------------------------------------------------
drop table if exists LINK_CONSTRAINTS_REF;

CREATE TABLE LINK_CONSTRAINTS_REF
(
    CONSTRAINTS_REF_ID MEDIUMINT NOT NULL,
    LINK_ID MEDIUMINT NOT NULL,
    APPLY_ORDER MEDIUMINT NOT NULL,
    NAME VARCHAR(40) NOT NULL,
    PRIMARY KEY(CONSTRAINTS_REF_ID),
    FOREIGN KEY (LINK_ID) REFERENCES LINK (LINK_ID)
        ON DELETE CASCADE 
  ,
    UNIQUE (LINK_ID, NAME),
    INDEX IX_LINK_CONSTRAINTS_REF_1 (LINK_ID));

# -----------------------------------------------------------------------
# PAGE_SECURITY
# -----------------------------------------------------------------------
drop table if exists PAGE_SECURITY;

CREATE TABLE PAGE_SECURITY
(
    PAGE_SECURITY_ID MEDIUMINT NOT NULL,
    PARENT_ID MEDIUMINT NOT NULL,
    PATH VARCHAR(240) NOT NULL,
    NAME VARCHAR(80) NOT NULL,
    VERSION VARCHAR(40),
    SUBSITE VARCHAR(40),
    USER_PRINCIPAL VARCHAR(40),
    ROLE_PRINCIPAL VARCHAR(40),
    GROUP_PRINCIPAL VARCHAR(40),
    MEDIATYPE VARCHAR(15),
    LOCALE VARCHAR(20),
    EXT_ATTR_NAME VARCHAR(15),
    EXT_ATTR_VALUE VARCHAR(40),
    PRIMARY KEY(PAGE_SECURITY_ID),
    FOREIGN KEY (PARENT_ID) REFERENCES FOLDER (FOLDER_ID)
        ON DELETE CASCADE 
  ,
    UNIQUE (PARENT_ID),
    UNIQUE (PATH));

# -----------------------------------------------------------------------
# PAGE_SEC_CONSTRAINTS_DEF
# -----------------------------------------------------------------------
drop table if exists PAGE_SEC_CONSTRAINTS_DEF;

CREATE TABLE PAGE_SEC_CONSTRAINTS_DEF
(
    CONSTRAINTS_DEF_ID MEDIUMINT NOT NULL,
    PAGE_SECURITY_ID MEDIUMINT NOT NULL,
    NAME VARCHAR(40) NOT NULL,
    PRIMARY KEY(CONSTRAINTS_DEF_ID),
    FOREIGN KEY (PAGE_SECURITY_ID) REFERENCES PAGE_SECURITY (PAGE_SECURITY_ID)
        ON DELETE CASCADE 
  ,
    UNIQUE (PAGE_SECURITY_ID, NAME),
    INDEX IX_PAGE_SEC_CONSTRAINTS_DEF_1 (PAGE_SECURITY_ID));

# -----------------------------------------------------------------------
# PAGE_SEC_CONSTRAINT_DEF
# -----------------------------------------------------------------------
drop table if exists PAGE_SEC_CONSTRAINT_DEF;

CREATE TABLE PAGE_SEC_CONSTRAINT_DEF
(
    CONSTRAINT_DEF_ID MEDIUMINT NOT NULL,
    CONSTRAINTS_DEF_ID MEDIUMINT NOT NULL,
    APPLY_ORDER MEDIUMINT NOT NULL,
    USER_PRINCIPALS_ACL VARCHAR(120),
    ROLE_PRINCIPALS_ACL VARCHAR(120),
    GROUP_PRINCIPALS_ACL VARCHAR(120),
    PERMISSIONS_ACL VARCHAR(120),
    PRIMARY KEY(CONSTRAINT_DEF_ID),
    FOREIGN KEY (CONSTRAINTS_DEF_ID) REFERENCES PAGE_SEC_CONSTRAINTS_DEF (CONSTRAINTS_DEF_ID)
        ON DELETE CASCADE 
  ,
    INDEX IX_PAGE_SEC_CONSTRAINT_DEF_1 (CONSTRAINTS_DEF_ID));

# -----------------------------------------------------------------------
# PAGE_SEC_CONSTRAINTS_REF
# -----------------------------------------------------------------------
drop table if exists PAGE_SEC_CONSTRAINTS_REF;

CREATE TABLE PAGE_SEC_CONSTRAINTS_REF
(
    CONSTRAINTS_REF_ID MEDIUMINT NOT NULL,
    PAGE_SECURITY_ID MEDIUMINT NOT NULL,
    APPLY_ORDER MEDIUMINT NOT NULL,
    NAME VARCHAR(40) NOT NULL,
    PRIMARY KEY(CONSTRAINTS_REF_ID),
    FOREIGN KEY (PAGE_SECURITY_ID) REFERENCES PAGE_SECURITY (PAGE_SECURITY_ID)
        ON DELETE CASCADE 
  ,
    UNIQUE (PAGE_SECURITY_ID, NAME),
    INDEX IX_PAGE_SEC_CONSTRAINTS_REF_1 (PAGE_SECURITY_ID));

# -----------------------------------------------------------------------
# PROFILING_RULE
# -----------------------------------------------------------------------
drop table if exists PROFILING_RULE;

CREATE TABLE PROFILING_RULE
(
    RULE_ID VARCHAR(80) NOT NULL,
    CLASS_NAME VARCHAR(100) NOT NULL,
    TITLE VARCHAR(100),
    PRIMARY KEY(RULE_ID));

# -----------------------------------------------------------------------
# RULE_CRITERION
# -----------------------------------------------------------------------
drop table if exists RULE_CRITERION;

CREATE TABLE RULE_CRITERION
(
    CRITERION_ID VARCHAR(80) NOT NULL,
    RULE_ID VARCHAR(80) NOT NULL,
    FALLBACK_ORDER MEDIUMINT NOT NULL,
    REQUEST_TYPE VARCHAR(40) NOT NULL,
    NAME VARCHAR(80) NOT NULL,
    COLUMN_VALUE VARCHAR(128),
    FALLBACK_TYPE MEDIUMINT default 1,
    PRIMARY KEY(CRITERION_ID),
    FOREIGN KEY (RULE_ID) REFERENCES PROFILING_RULE (RULE_ID)
        ON DELETE CASCADE 
  ,
    INDEX IX_RULE_CRITERION_1 (RULE_ID, FALLBACK_ORDER));

# -----------------------------------------------------------------------
# PRINCIPAL_RULE_ASSOC
# -----------------------------------------------------------------------
drop table if exists PRINCIPAL_RULE_ASSOC;

CREATE TABLE PRINCIPAL_RULE_ASSOC
(
    PRINCIPAL_NAME VARCHAR(80) NOT NULL,
    LOCATOR_NAME VARCHAR(80) NOT NULL,
    RULE_ID VARCHAR(80) NOT NULL,
    PRIMARY KEY(PRINCIPAL_NAME,LOCATOR_NAME));

# -----------------------------------------------------------------------
# PROFILE_PAGE_ASSOC
# -----------------------------------------------------------------------
drop table if exists PROFILE_PAGE_ASSOC;

CREATE TABLE PROFILE_PAGE_ASSOC
(
    LOCATOR_HASH VARCHAR(40) NOT NULL,
    PAGE_ID VARCHAR(80) NOT NULL,
    UNIQUE (LOCATOR_HASH, PAGE_ID));

# -----------------------------------------------------------------------
# CLUBS
# -----------------------------------------------------------------------
drop table if exists CLUBS;

CREATE TABLE CLUBS
(
    NAME VARCHAR(80) NOT NULL,
    COUNTRY VARCHAR(40) NOT NULL,
    CITY VARCHAR(40) NOT NULL,
    STADIUM VARCHAR(80) NOT NULL,
    CAPACITY MEDIUMINT,
    FOUNDED MEDIUMINT,
    PITCH VARCHAR(40),
    NICKNAME VARCHAR(40),
    PRIMARY KEY(NAME));