-- team table
CREATE TABLE teams (
    team_id SERIAL PRIMARY KEY,
    name VARCHAR(25),
    section_id INT NOT NULL,
    team_num INT NOT NULL,
    locker_num INT UNIQUE
);

-- user table
CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    email_address TEXT NOT NULL UNIQUE,
    type VARCHAR(10) NOT NULL CHECK (type IN ('student', 'instructor', 'ta')), -- follows subtypes
    receive_emails VARCHAR(13) NOT NULL CHECK (receive_emails IN ('always', 'on_deliveries', 'on_custom')) -- delivieries is plus custom messages
);
-- student table
CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    user_id INT NOT NULL UNIQUE REFERENCES users(user_id) ON DELETE CASCADE,
    team_id INT REFERENCES teams(team_id),
    section_id INT
);
-- instructor table
CREATE TABLE instructors (
    instructor_id SERIAL PRIMARY KEY,
    user_id INT NOT NULL UNIQUE REFERENCES users(user_id) ON DELETE CASCADE,
    is_admin VARCHAR(8) NOT NULL CHECK (is_admin IN ('true', 'incoming', 'false')) DEFAULT 'false'
);
-- ta table
CREATE TABLE tas (
    ta_id SERIAL PRIMARY KEY,
    user_id INT NOT NULL UNIQUE REFERENCES users(user_id) ON DELETE CASCADE
);

-- supplier table
CREATE TABLE suppliers (
    supplier_id SERIAL PRIMARY KEY,
    url TEXT NOT NULL UNIQUE,
    name TEXT NOT NULL UNIQUE
);

-- part table
CREATE TABLE parts (
    part_id SERIAL PRIMARY KEY,
    url TEXT NOT NULL UNIQUE,
    part_num TEXT,
    supplier_id INT NOT NULL REFERENCES suppliers(supplier_id),
    description TEXT NOT NULL,
    price NUMERIC NOT NULL
);

-- order table
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    order_timestamp TIMESTAMPTZ NOT NULL,
    instructor_id INT NOT NULL REFERENCES instructors(instructor_id),
    supplier_id INT NOT NULL REFERENCES suppliers(supplier_id),
    is_fulfilled BOOLEAN NOT NULL,
    total NUMERIC NOT NULL
);

-- request table
CREATE TABLE requests (
    request_id SERIAL PRIMARY KEY,
    request_timestamp TIMESTAMPTZ NOT NULL,
    student_id INT NOT NULL REFERENCES students(student_id),
    team_id INT NOT NULL REFERENCES teams(team_id),
    supplier_id INT NOT NULL REFERENCES suppliers(supplier_id),
    part_id INT NOT NULL REFERENCES parts(part_id),
    order_id INT REFERENCES orders(order_id),
    status VARCHAR(9) NOT NULL CHECK (status IN ('requested', 'approved', 'ordered', 'delivered', 'delayed', 'reordered', 'on hand', 'cancelled', 'other')), -- follows project specs
    quantity INT NOT NULL,
    subtotal NUMERIC NOT NULL
);

CREATE TABLE notifications (
    notification_id SERIAL PRIMARY KEY,
    notification_timestamp TIMESTAMPTZ NOT NULL,
    request_id INT REFERENCES requests(request_id),
    team_id INT NOT NULL REFERENCES teams(team_id),
    generated_message TEXT NOT NULL,
    free_text TEXT,
    for_instructor BOOLEAN NOT NULL
);

CREATE TABLE read_notifications (
    read_notification_id SERIAL PRIMARY KEY,
    notification_id INT NOT NULL REFERENCES notifications(notification_id),
    user_id INT NOT NULL REFERENCES users(user_id)
);