# -----------------------------------------------------------------------
# MEDIA_TYPE
# -----------------------------------------------------------------------
drop table if exists MEDIA_TYPE;

CREATE TABLE MEDIA_TYPE
(
    MEDIATYPE_ID MEDIUMINT NOT NULL,
    NAME VARCHAR(80) NOT NULL,
    CHARACTER_SET VARCHAR(40),
    TITLE VARCHAR(80),
    DESCRIPTION MEDIUMTEXT,
    PRIMARY KEY(MEDIATYPE_ID));

# -----------------------------------------------------------------------
# CLIENT
# -----------------------------------------------------------------------
drop table if exists CLIENT;

CREATE TABLE CLIENT
(
    CLIENT_ID MEDIUMINT NOT NULL,
    EVAL_ORDER MEDIUMINT NOT NULL,
    NAME VARCHAR(80) NOT NULL,
    USER_AGENT_PATTERN VARCHAR(128),
    MANUFACTURER VARCHAR(80),
    MODEL VARCHAR(80),
    VERSION VARCHAR(40),
    PREFERRED_MIMETYPE_ID MEDIUMINT NOT NULL,
    PRIMARY KEY(CLIENT_ID));

# -----------------------------------------------------------------------
# MIMETYPE
# -----------------------------------------------------------------------
drop table if exists MIMETYPE;

CREATE TABLE MIMETYPE
(
    MIMETYPE_ID MEDIUMINT NOT NULL,
    NAME VARCHAR(80) NOT NULL,
    PRIMARY KEY(MIMETYPE_ID));

# -----------------------------------------------------------------------
# CAPABILITY
# -----------------------------------------------------------------------
drop table if exists CAPABILITY;

CREATE TABLE CAPABILITY
(
    CAPABILITY_ID MEDIUMINT NOT NULL,
    CAPABILITY VARCHAR(80) NOT NULL,
    PRIMARY KEY(CAPABILITY_ID));

# -----------------------------------------------------------------------
# CLIENT_TO_CAPABILITY
# -----------------------------------------------------------------------
drop table if exists CLIENT_TO_CAPABILITY;

CREATE TABLE CLIENT_TO_CAPABILITY
(
    CLIENT_ID MEDIUMINT NOT NULL,
    CAPABILITY_ID MEDIUMINT NOT NULL);

# -----------------------------------------------------------------------
# CLIENT_TO_MIMETYPE
# -----------------------------------------------------------------------
drop table if exists CLIENT_TO_MIMETYPE;

CREATE TABLE CLIENT_TO_MIMETYPE
(
    CLIENT_ID MEDIUMINT NOT NULL,
    MIMETYPE_ID MEDIUMINT NOT NULL);

# -----------------------------------------------------------------------
# MEDIATYPE_TO_CAPABILITY
# -----------------------------------------------------------------------
drop table if exists MEDIATYPE_TO_CAPABILITY;

CREATE TABLE MEDIATYPE_TO_CAPABILITY
(
    MEDIATYPE_ID MEDIUMINT NOT NULL,
    CAPABILITY_ID MEDIUMINT NOT NULL);

# -----------------------------------------------------------------------
# MEDIATYPE_TO_MIMETYPE
# -----------------------------------------------------------------------
drop table if exists MEDIATYPE_TO_MIMETYPE;

CREATE TABLE MEDIATYPE_TO_MIMETYPE
(
    MEDIATYPE_ID MEDIUMINT NOT NULL,
    MIMETYPE_ID MEDIUMINT NOT NULL);

# -----------------------------------------------------------------------
# PORTLET_STATISTICS
# -----------------------------------------------------------------------
drop table if exists PORTLET_STATISTICS;

CREATE TABLE PORTLET_STATISTICS
(
    IPADDRESS VARCHAR(80),
    USER_NAME VARCHAR(80),
    TIME_STAMP TIMESTAMP,
    PAGE VARCHAR(80),
    PORTLET VARCHAR(255),
    STATUS MEDIUMINT,
    ELAPSED_TIME BIGINT);

# -----------------------------------------------------------------------
# PAGE_STATISTICS
# -----------------------------------------------------------------------
drop table if exists PAGE_STATISTICS;

CREATE TABLE PAGE_STATISTICS
(
    IPADDRESS VARCHAR(80),
    USER_NAME VARCHAR(80),
    TIME_STAMP TIMESTAMP,
    PAGE VARCHAR(80),
    STATUS MEDIUMINT,
    ELAPSED_TIME BIGINT);

# -----------------------------------------------------------------------
# USER_STATISTICS
# -----------------------------------------------------------------------
drop table if exists USER_STATISTICS;

CREATE TABLE USER_STATISTICS
(
    IPADDRESS VARCHAR(80),
    USER_NAME VARCHAR(80),
    TIME_STAMP TIMESTAMP,
    STATUS MEDIUMINT,
    ELAPSED_TIME BIGINT);

# -----------------------------------------------------------------------
# ADMIN_ACTIVITY
# -----------------------------------------------------------------------
drop table if exists ADMIN_ACTIVITY;

CREATE TABLE ADMIN_ACTIVITY
(
    ACTIVITY VARCHAR(40),
    CATEGORY VARCHAR(40),
    ADMIN VARCHAR(80),
    USER_NAME VARCHAR(80),
    TIME_STAMP TIMESTAMP,
    IPADDRESS VARCHAR(80),
    ATTR_NAME VARCHAR(40),
    ATTR_VALUE_BEFORE VARCHAR(80),
    ATTR_VALUE_AFTER VARCHAR(80),
    DESCRIPTION VARCHAR(128));

# -----------------------------------------------------------------------
# USER_ACTIVITY
# -----------------------------------------------------------------------
drop table if exists USER_ACTIVITY;

CREATE TABLE USER_ACTIVITY
(
    ACTIVITY VARCHAR(40),
    CATEGORY VARCHAR(40),
    USER_NAME VARCHAR(80),
    TIME_STAMP TIMESTAMP,
    IPADDRESS VARCHAR(80),
    ATTR_NAME VARCHAR(40),
    ATTR_VALUE_BEFORE VARCHAR(80),
    ATTR_VALUE_AFTER VARCHAR(80),
    DESCRIPTION VARCHAR(128));