CREATE TABLE release (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL UNIQUE,
    long_name TEXT NOT NULL,
    id_prefix TEXT NOT NULL,
    dist_tag TEXT NOT NULL,
    locked TINYINT
);

CREATE TABLE package (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL UNIQUE,
    suggest_reboot TINYINT
);

CREATE TABLE bugzilla (
    id INTEGER PRIMARY KEY,
    bz_id INT NOT NULL UNIQUE,
    title TEXT,
    security TINYINT,
    parent TINYINT
);
CREATE TABLE bugzilla_package_update (
bugzilla_id INT NOT NULL,
package_update_id INT NOT NULL
);

CREATE TABLE package_update (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL UNIQUE,
    date_submitted TIMESTAMP NOT NULL,
    date_modified TIMESTAMP,
    date_pushed TIMESTAMP,
    submitter TEXT NOT NULL,
    update_id TEXT,
    type VARCHAR(11) CHECK (type in ('security', 'bugfix', 'enhancement')),
    release_id INT CONSTRAINT release_id_exists REFERENCES release(id) ,
    status VARCHAR(8) CHECK (status in ('pending', 'testing', 'stable', 'obsolete')),
    pushed TINYINT,
    notes TEXT,
    request VARCHAR(8) CHECK (request in ('testing', 'stable', 'obsolete', NULL)),
    karma INT,
    close_bugs TINYINT,
    nagged TEXT,
    approved TIMESTAMP
);

CREATE TABLE package_build (
    id INTEGER PRIMARY KEY,
    nvr TEXT NOT NULL UNIQUE,
    package_id INT CONSTRAINT package_id_exists REFERENCES package(id) ,
    update_id INT CONSTRAINT update_id_exists REFERENCES package_update(id) 
);

CREATE TABLE comment (
    id INTEGER PRIMARY KEY,
    timestamp TIMESTAMP,
    update_id INT NOT NULL CONSTRAINT update_id_exists REFERENCES package_update(id) ,
    author TEXT NOT NULL,
    karma INT,
    text TEXT,
    anonymous TINYINT
);