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,
       remote_key VARCHAR(8192) 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
);

CREATE TABLE IF NOT EXISTS translation_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,
       archived BOOLEAN NOT NULL DEFAULT 0,
       base VARCHAR(40) NOT NULL,
       head VARCHAR(40) NOT NULL,

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

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

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

       CONSTRAINT `fk_translation_review_users_translation_review`
         FOREIGN KEY (translation_review) REFERENCES translation_reviews (id)
         ON DELETE CASCADE
         ON UPDATE RESTRICT,

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

CREATE TABLE IF NOT EXISTS localization_strings (
       id BIGINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
       translation_review BIGINT UNSIGNED NOT NULL,
       name VARCHAR(512) NOT NULL,
       file VARCHAR(512) NOT NULL,
       description MEDIUMTEXT NOT NULL DEFAULT '',
       meaning VARCHAR(512) NOT NULL DEFAULT '',
       source MEDIUMTEXT NOT NULL,
       placeholder_offsets VARCHAR(512) NOT NULL DEFAULT '',

       CONSTRAINT `fk_localization_strings_translation_review`
         FOREIGN KEY (translation_review) REFERENCES translation_reviews (id)
         ON DELETE CASCADE
         ON UPDATE RESTRICT
);

CREATE TABLE IF NOT EXISTS localization_placeholders (
       id BIGINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
       localization_string BIGINT UNSIGNED NOT NULL,
       name VARCHAR(128) NOT NULL,
       content VARCHAR(256) NOT NULL,
       example VARCHAR(256) NOT NULL DEFAULT '',

       CONSTRAINT `fk_localization_placeholder_localization_string`
         FOREIGN KEY (localization_string) REFERENCES localization_strings (id)
         ON DELETE CASCADE
         ON UPDATE RESTRICT
);

CREATE TABLE IF NOT EXISTS translation_strings (
       localization_string BIGINT UNSIGNED NOT NULL,
       language VARCHAR(10) NOT NULL,
       base_translation MEDIUMTEXT,
       base_placeholder_offsets VARCHAR(512),
       head_translation MEDIUMTEXT NOT NULL,
       head_placeholder_offsets VARCHAR(512) NOT NULL DEFAULT '',
       state TINYINT UNSIGNED NOT NULL DEFAULT 0,
       comment MEDIUMTEXT NOT NULL DEFAULT '',
       reviewer VARCHAR(128),

       CONSTRAINT `fk_translation_string_localization_string`
         FOREIGN KEY (localization_string) REFERENCES localization_strings (id)
         ON DELETE CASCADE
         ON UPDATE RESTRICT,

       PRIMARY KEY (localization_string, language)
);