DROP TABLE IF EXISTS WROX_FORUM_MESSAGE;
DROP TABLE IF EXISTS WROX_FORUM;

-- DROP TABLE IF EXISTS WROX_PENDING;
-- DROP TABLE IF EXISTS WROX_USER;

-- CREATE TABLE WROX_USER (
--     USER_ID     INTEGER UNSIGNED   NOT NULL  AUTO_INCREMENT,
--     USERNAME    VARCHAR(20)        NOT NULL,
--     PASSWORD    CHAR(40)           NOT NULL,
--     EMAIL_ADDR  VARCHAR(100)       NOT NULL,
--     IS_ACTIVE   TINYINT(1)         DEFAULT 0,
-- 
--     PRIMARY KEY (USER_ID)
-- )
-- ENGINE=InnoDB DEFAULT CHARACTER SET latin1
--     COLLATE latin1_general_cs AUTO_INCREMENT=0;
 
-- CREATE TABLE WROX_PENDING (
--     USER_ID  INTEGER UNSIGNED  NOT NULL,
--     TOKEN    CHAR(10)          NOT NULL,
--     CREATED  TIMESTAMP         NOT NULL  DEFAULT CURRENT_TIMESTAMP,
-- 
--     FOREIGN KEY (USER_ID)
--         REFERENCES WROX_USER(USER_ID)
-- )
-- ENGINE=InnoDB DEFAULT CHARACTER SET latin1
--     COLLATE latin1_general_cs;
 

ALTER TABLE WROX_USER
    ADD PERMISSION INTEGER UNSIGNED NOT NULL DEFAULT 0
AFTER
    IS_ACTIVE;

CREATE TABLE WROX_FORUM (
    FORUM_ID         INTEGER UNSIGNED  NOT NULL  AUTO_INCREMENT,
    FORUM_NAME       VARCHAR(50)       NOT NULL, 
    DESCRIPTION      VARCHAR(100)      NOT NULL,

    PRIMARY KEY (FORUM_ID)
)
ENGINE=MyISAM DEFAULT CHARACTER SET latin1
    COLLATE latin1_general_cs AUTO_INCREMENT=0;

CREATE TABLE WROX_FORUM_MESSAGE (
    MESSAGE_ID         BIGINT UNSIGNED   NOT NULL  AUTO_INCREMENT,
    PARENT_MESSAGE_ID  BIGINT UNSIGNED   NOT NULL DEFAULT 0,
    FORUM_ID           INTEGER UNSIGNED  NOT NULL,
    USER_ID            INTEGER UNSIGNED  NOT NULL,
    SUBJECT            VARCHAR(100)      NOT NULL,
    MESSAGE_TEXT       TEXT              NOT NULL,
    MESSAGE_DATE       TIMESTAMP         NOT NULL  DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (MESSAGE_ID),

    FOREIGN KEY (PARENT_MESSAGE_ID)
        REFERENCES WROX_FORUM_MESSAGE(MESSAGE_ID),

    FOREIGN KEY (USER_ID)
        REFERENCES WROX_USER(USER_ID)
)
ENGINE=MyISAM DEFAULT CHARACTER SET latin1
    COLLATE latin1_general_cs AUTO_INCREMENT=0;