CREATE TABLE IF NOT EXISTS users (
    id BIGSERIAL PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    verified_email BOOLEAN NOT NULL,
    username TEXT NOT NULL UNIQUE,
    pw_hash TEXT NOT NULL,
    pw_salt TEXT NOT NULL,
    pfp_id BIGINT NOT NULL,
    rank_id BIGINT NOT NULL,
    group_id BIGINT NOT NULL
    
    -- NOTE: Constraint added later to deal with circular dependency
    -- CONSTRAINT fk_group FOREIGN KEY (group_id) REFERENCES roster_group(id)
    -- CONSTRAINT fk_rank FOREIGN KEY (rank_id) REFERENCES ranks(id)
);

CREATE TABLE IF NOT EXISTS sessions (
    id BIGSERIAL NOT NULL PRIMARY KEY,
    user_id BIGINT NOT NULL,
    session_key TEXT NOT NULL UNIQUE,
    expires BIGINT NOT NULL,
    CONSTRAINT fk_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS ranks (
    id BIGSERIAL PRIMARY KEY NOT NULL,
    name TEXT NOT NULL,
    description TEXT NOT NULL,
    created_at BIGINT NOT NULL,
    modified_at BIGINT NOT NULL
);
CREATE TABLE IF NOT EXISTS awards (
    id BIGSERIAL PRIMARY KEY NOT NULL,
    name TEXT NOT NULL,
    description TEXT NOT NULL,
    created_at BIGINT NOT NULL,
    modified_at BIGINT NOT NULL
);
CREATE TABLE IF NOT EXISTS trainings (
    id BIGSERIAL PRIMARY KEY NOT NULL,
    name TEXT NOT NULL,
    description TEXT NOT NULL,
    created_at BIGINT NOT NULL,
    modified_at BIGINT NOT NULL
);
CREATE TABLE IF NOT EXISTS missions (
    id BIGSERIAL PRIMARY KEY NOT NULL,
    name TEXT NOT NULL,
    description TEXT NOT NULL,
    starting_at BIGINT NOT NULL,
    estimated_length BIGINT NOT NULL,
    created_at BIGINT NOT NULL,
    modified_at BIGINT NOT NULL
);
CREATE TABLE IF NOT EXISTS qualifications (
    id BIGSERIAL PRIMARY KEY NOT NULL,
    name TEXT NOT NULL,
    description TEXT NOT NULL,
    created_at BIGINT NOT NULL,
    modified_at BIGINT NOT NULL
);

CREATE TABLE IF NOT EXISTS attendance (
    id BIGSERIAL PRIMARY KEY NOT NULL,
    user_id BIGINT NOT NULL,
    mission_id BIGINT NOT NULL,
    confirmed_at BIGINT NOT NULL,
    attending BOOL NOT NULL,

    CONSTRAINT fk_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL,
    CONSTRAINT fk_mission FOREIGN KEY(mission_id) REFERENCES missions(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS roster_groups (
    id BIGSERIAL NOT NULL PRIMARY KEY,
    name TEXT NOT NULL,
    sort_order BIGINT NOT NULL,
    manager_id BIGINT NOT NULL,

    CONSTRAINT fk_manager FOREIGN KEY(manager_id) REFERENCES users(id) ON DELETE SET NULL
);


-- NOTE: For table 'users'
ALTER TABLE users ADD FOREIGN KEY (group_id) REFERENCES roster_groups(id) ON DELETE SET NULL;
ALTER TABLE users ADD FOREIGN KEY (rank_id) REFERENCES ranks(id) ON DELETE SET NULL;

--------------------
-- NOTE: Records
--------------------


CREATE TABLE IF NOT EXISTS records_ranks (
    id BIGSERIAL PRIMARY KEY NOT NULL,
    user_id BIGINT NOT NULL,
    rank_id BIGINT NOT NULL,
    author_id BIGINT NOT NULL,
    created_at BIGINT NOT NULL,

    CONSTRAINT fk_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_author FOREIGN KEY(author_id) REFERENCES users(id) ON DELETE SET NULL,
    CONSTRAINT fk_rank FOREIGN KEY (rank_id) REFERENCES ranks(id) ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS records_awards (
    id BIGSERIAL PRIMARY KEY NOT NULL,
    user_id BIGINT NOT NULL,
    award_id BIGINT NOT NULL,
    author_id BIGINT NOT NULL,
    created_at BIGINT NOT NULL,

    CONSTRAINT fk_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_author FOREIGN KEY(author_id) REFERENCES users(id) ON DELETE SET NULL,
    CONSTRAINT fk_award FOREIGN KEY (award_id) REFERENCES awards(id) ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS records_trainings (
    id BIGSERIAL PRIMARY KEY NOT NULL,
    user_id BIGINT NOT NULL,
    training_id BIGINT NOT NULL,
    author_id BIGINT NOT NULL,
    created_at BIGINT NOT NULL,

    CONSTRAINT fk_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_author FOREIGN KEY(author_id) REFERENCES users(id) ON DELETE SET NULL,
    CONSTRAINT fk_training FOREIGN KEY (training_id) REFERENCES trainings(id) ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS records_qualifications (
    id BIGSERIAL PRIMARY KEY NOT NULL,
    user_id BIGINT NOT NULL,
    qualification_id BIGINT NOT NULL,
    author_id BIGINT NOT NULL,
    created_at BIGINT NOT NULL,

    CONSTRAINT fk_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_author FOREIGN KEY(author_id) REFERENCES users(id) ON DELETE SET NULL,
    CONSTRAINT fk_qualification FOREIGN KEY (qualification_id) REFERENCES qualifications(id) ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS records_missions (
    id BIGSERIAL PRIMARY KEY NOT NULL,
    user_id BIGINT NOT NULL,
    mission_id BIGINT NOT NULL,
    author_id BIGINT NOT NULL,
    created_at BIGINT NOT NULL,

    CONSTRAINT fk_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_author FOREIGN KEY(author_id) REFERENCES users(id) ON DELETE SET NULL,
    CONSTRAINT fk_mission FOREIGN KEY (mission_id) REFERENCES missions(id) ON DELETE CASCADE
);