schema.sql

-- Enable foreign key constraints for SQLite
PRAGMA foreign_keys = ON;

-- Create the "developers" table to store developer information
CREATE TABLE developers (
    developer_id TEXT PRIMARY KEY,                -- developer_id is a String in the model, hence TEXT in SQLite
    username TEXT NOT NULL,                       -- username of the developer
    password TEXT NOT NULL                        -- password (ensure to store hashed passwords)
);

-- Create the "roles" table to store developer roles (admin or not)
CREATE TABLE roles (
    developer_id TEXT PRIMARY KEY,                -- developer reference (foreign key)
    is_admin BOOLEAN NOT NULL,                     -- if the developer is an admin or not
    FOREIGN KEY (developer_id) REFERENCES developers(developer_id) -- foreign key reference to developers table
);

-- Create the "assignments" table to store bug assignments to developers
CREATE TABLE assignments (
    bug_id TEXT,                                  -- reference to the bug being assigned (TEXT)
    developer_id TEXT,                             -- reference to the developer assigned to the bug (TEXT)
    PRIMARY KEY (bug_id, developer_id) ,            -- composite primary key to ensure uniqueness
    FOREIGN KEY (bug_id) REFERENCES bugs(bug_id) ON DELETE CASCADE, -- foreign key reference to the bugs table
    FOREIGN KEY (developer_id) REFERENCES developers(developer_id) -- foreign key reference to the developers table
);

-- Create the "bugs" table to store bug information
CREATE TABLE bugs (
    bug_id TEXT PRIMARY KEY,                      -- bug_id is a String in the model, hence TEXT in SQLite
    title TEXT NOT NULL,                          -- title of the bug
    description TEXT NOT NULL,                    -- detailed description of the bug
    reported_by TEXT NOT NULL,                    -- person who reported the bug
    severity TEXT NOT NULL,                       -- severity level of the bug (e.g., Critical, High, Medium, Low)
    assigned_to TEXT,                          -- developer assigned to the bug (optional, references developers)
    project_id INTEGER NOT NULL                  -- ID of the associated project
    -- FOREIGN KEY (assigned_to) REFERENCES developers(developer_id) -- foreign key to developers table
);