-- HTTP-versio tietokantapohjaisena, Asha

CREATE TABLE ISSUEBASES
(
    ISSUEBASE_ID    VARCHAR(5) NOT NULL,
    NAME            VARCHAR(50),
    PRIMARY KEY     (ISSUEBASE_ID)
);

CREATE TABLE ISSUES
(
    ISSUE_ID        INT UNSIGNED NOT NULL,
    ISSUEBASE_ID    VARCHAR(5) NOT NULL,
    NRO             SMALLINT UNSIGNED,
    YEAR            SMALLINT UNSIGNED,
    CATEGORY        VARCHAR(10),
    DATE            VARCHAR(10),
    TITLE           VARCHAR(254),
    PRIMARY KEY     (ISSUE_ID)
);

CREATE TABLE ACTIVITIES
(
    ISSUE_ID        INT UNSIGNED NOT NULL,
    ACTIVITY_NRO    INT UNSIGNED NOT NULL,
    TYPE            VARCHAR(3),
    DATE            VARCHAR(10),
    TITLE           VARCHAR(254),
    FORMAT          VARCHAR(3),
    CLOSER          VARCHAR(1),
    HANDLER         VARCHAR(50),
    INDEX idxACTIVITIES2 (ISSUE_ID)
);

CREATE TABLE ISSUE_ATTACHEMENTS
(
    ATTACHEMENT_ID  INT UNSIGNED NOT NULL AUTO_INCREMENT,
    ISSUE_ID        INT UNSIGNED NOT NULL,
    ACTIVITY_NRO    INT UNSIGNED,
    DOCUMENT_NRO    INT UNSIGNED,
    TYPE            VARCHAR(3),
    TITLE           VARCHAR(254),
    FORMAT          VARCHAR(3),
    INDEX idxISSUE_ATTACHEMENTS1 (ATTACHEMENT_ID),
    INDEX idxISSUE_ATTACHEMENTS2 (ISSUE_ID)
);