-- HTTP-versio tietokantapohjaisena, Kokha, Vhp, Dokha

CREATE TABLE OFFICIAL_HANDLERS
(
    HANDLER_ID  VARCHAR(8) NOT NULL,
    LEVEL       INT UNSIGNED,
    NAME       	VARCHAR(50),
    ABREV     	VARCHAR(20),
    PRIMARY KEY (HANDLER_ID),
    INDEX idxOFFICIAL_HANDLERS1 (LEVEL)
);

CREATE TABLE OFFICIAL_DECISIONS
(
    DECISION_ID  INT UNSIGNED NOT NULL,
    HANDLER_ID   VARCHAR(8) NOT NULL,
    YEAR         INT UNSIGNED,
    PARAGRAPH    INT UNSIGNED,
    DATE         VARCHAR(10),
    TITLE        VARCHAR(254),
    FORMAT       VARCHAR(3),
    FILEINDEX    MEDIUMTEXT,
    PRIMARY KEY (DECISION_ID),
    INDEX idxOFFICIAL_DECISIONS1 (HANDLER_ID),
    INDEX idxOFFICIAL_DECISIONS2 (TITLE)
);

CREATE TABLE OFF_DEC_ATTACHEMENTS
(
    ATTACHEMENT_ID INT UNSIGNED NOT NULL,
    DECISION_ID    INT UNSIGNED NOT NULL,
    HANDLER_ID     VARCHAR(8) NOT NULL,
    TITLE          VARCHAR(254),
    FORMAT         VARCHAR(3),
    INDEX idxOFF_DEC_ATTACHEMENTS1 (ATTACHEMENT_ID),
    INDEX idxOFF_DEC_ATTACHEMENTS2 (DECISION_ID)
);


CREATE TABLE FOLDERS
(
    FOLDER_ID    INT UNSIGNED NOT NULL,
    PARENT_ID    INT UNSIGNED,
    LEVEL        INT UNSIGNED,
    NAME         VARCHAR(150),
    PRIMARY KEY (FOLDER_ID),
    INDEX idxFOLDERS1 (PARENT_ID, LEVEL)
);

CREATE TABLE DOCUMENTS
(
    DOCUMENT_ID  INT UNSIGNED NOT NULL,
    FOLDER_ID    INT UNSIGNED NOT NULL,
    TITLE        VARCHAR(254),
    DATE         VARCHAR(10),
    TYPE         VARCHAR(40),
    CREATOR      VARCHAR(40),
    FORMAT       VARCHAR(3),
    FILEINDEX    MEDIUMTEXT,
    PRIMARY KEY (DOCUMENT_ID),
    INDEX idxDOCUMENTS1 (FOLDER_ID),
    INDEX idxDOCUMENTS2 (TITLE)
);

CREATE TABLE DOCUMENT_ATTACHEMENTS
(
    ATTACHEMENT_ID INT UNSIGNED NOT NULL,
    DOCUMENT_ID    INT UNSIGNED NOT NULL,
    TITLE          VARCHAR(254),
    FORMAT         VARCHAR(3),
    INDEX idxDOCUMENT_ATTACHEMENTS1 (ATTACHEMENT_ID),
    INDEX idxDOCUMENT_ATTACHEMENTS2 (DOCUMENT_ID)
);


CREATE TABLE MEETING_HANDLERS
(
    HANDLER_ID  INT UNSIGNED NOT NULL,
    LEVEL       TINYINT UNSIGNED,
    NAME        VARCHAR(50),
    PRIMARY KEY (HANDLER_ID),
    INDEX idxMEETING_HANDLERS1 (LEVEL)
);

CREATE TABLE MEETINGS
(
    MEETING_ID   INT UNSIGNED NOT NULL,
    HANDLER_ID   INT UNSIGNED NOT NULL,
    YEAR         SMALLINT UNSIGNED,
    DATE         VARCHAR(10),
    TIME         VARCHAR(15),
    TYPE         TINYINT UNSIGNED,
    PRIMARY KEY (MEETING_ID),
    INDEX idxMEETINGS1 (HANDLER_ID),
    INDEX idxMEETINGS2 (DATE)
);

CREATE TABLE MEETING_ITEMS
(
    ITEM_ID      INT UNSIGNED NOT NULL,
    MEETING_ID   INT UNSIGNED NOT NULL,
    HANDLER_ID   INT UNSIGNED NOT NULL,
    PARAGRAPH    SMALLINT UNSIGNED,
    PUBLIC       VARCHAR(1),
    TITLE        VARCHAR(254),
    FORMAT       VARCHAR(3),
    ATTACHEMENTS TINYINT UNSIGNED,
    FILEINDEX    MEDIUMTEXT,
    INDEX idxMEETING_ITEMS1 (ITEM_ID),
    INDEX idxMEETING_ITEMS2 (MEETING_ID),
    INDEX idxMEETING_ITEMS3 (HANDLER_ID),
    INDEX idxMEETING_ITEMS4 (TITLE)
);

CREATE TABLE PARTICIPANTS
(
    MEETING_ID     INT UNSIGNED NOT NULL,
    NAME           VARCHAR(100),
    DUTY           VARCHAR(100),
    PRECENSE       VARCHAR(1),
    LEVEL          TINYINT UNSIGNED,
    INDEX idxPARTICIPANTS1 (MEETING_ID)
);

CREATE TABLE MEETING_ATTACHEMENTS
(
    ATTACHEMENT_ID INT UNSIGNED NOT NULL,
    ITEM_ID        INT UNSIGNED NOT NULL,
    MEETING_ID     INT UNSIGNED NOT NULL,
    HANDLER_ID     INT UNSIGNED NOT NULL,
    TITLE          VARCHAR(254),
    FORMAT         VARCHAR(3),
    INDEX idxMEETING_ATTACHEMENTS1 (ATTACHEMENT_ID),
    INDEX idxMEETING_ATTACHEMENTS2 (ITEM_ID)
);