CREATE TABLE IF NOT EXISTS projects (
       id VARCHAR(128) NOT NULL PRIMARY KEY,
       title VARCHAR(1024) NOT NULL,
       description MEDIUMTEXT NOT NULL DEFAULT '',
       remote VARCHAR(2048) NOT NULL,
       main_branch VARCHAR(1024) NOT NULL
);

CREATE TABLE IF NOT EXISTS users (
       id VARCHAR(128) NOT NULL PRIMARY KEY,
       name VARCHAR(1024) NOT NULL DEFAULT '',
       dn VARCHAR(256) NULL
);

CREATE TABLE IF NOT EXISTS project_users (
       project VARCHAR(128) NOT NULL,
       user VARCHAR(128) NOT NULL,
       default_role TINYINT UNSIGNED NOT NULL,
       maintainer BOOLEAN NOT NULL DEFAULT 0,
       PRIMARY KEY (project, user),

       CONSTRAINT `fk_project_users_project`
         FOREIGN KEY (project) REFERENCES projects (id)
         ON DELETE CASCADE
         ON UPDATE RESTRICT,

       CONSTRAINT `fk_project_users_user`
         FOREIGN KEY (user) REFERENCES users (id)
         ON DELETE CASCADE
         ON UPDATE RESTRICT
);

CREATE TABLE IF NOT EXISTS reviews (
       id BIGINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
       project VARCHAR(128) NOT NULL,
       owner VARCHAR(128) NOT NULL,
       title VARCHAR(1024) NOT NULL,
       description MEDIUMTEXT NOT NULL DEFAULT '',
       state TINYINT UNSIGNED NOT NULL DEFAULT 0,
       progress FLOAT NOT NULL DEFAULT 0,
       branch VARCHAR(1024) NOT NULL,
       archived BOOLEAN NOT NULL DEFAULT 0,
       rewrite TINYINT UNSIGNED NOT NULL DEFAULT 0,

       CONSTRAINT `fk_reviews_project`
         FOREIGN KEY (project) REFERENCES projects (id)
         ON DELETE CASCADE
         ON UPDATE RESTRICT,

       CONSTRAINT `fk_reviews_owner`
         FOREIGN KEY (owner) REFERENCES users (id)
         ON DELETE CASCADE
         ON UPDATE RESTRICT
);

CREATE TABLE IF NOT EXISTS review_users (
       review BIGINT UNSIGNED NOT NULL,
       user VARCHAR(128) NOT NULL,
       role TINYINT UNSIGNED NOT NULL DEFAULT 0,
       PRIMARY KEY (review, user),

       CONSTRAINT `fk_review_users_review`
         FOREIGN KEY (review) REFERENCES reviews (id)
         ON DELETE CASCADE
         ON UPDATE RESTRICT,

       CONSTRAINT `fk_review_users_user`
         FOREIGN KEY (user) REFERENCES users (id)
         ON DELETE CASCADE
         ON UPDATE RESTRICT
);

CREATE TABLE IF NOT EXISTS user_keys (
       id BIGINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
       user VARCHAR(128) NOT NULL,
       kind VARCHAR(128) NOT NULL,
       data VARCHAR(8192) NOT NULL,
       comment VARCHAR(1024) NOT NULL,

       CONSTRAINT `fk_user_keys_user`
         FOREIGN KEY (user) REFERENCES users (id)
         ON DELETE CASCADE
         ON UPDATE RESTRICT
);