project.sql

CREATE TABLE Users (
    user_id SERIAL PRIMARY KEY,
    full_name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    role VARCHAR(50) DEFAULT 'member', -- e.g. admin, manager, member
    date_joined TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_active BOOLEAN DEFAULT TRUE
);

CREATE TABLE Teams (
    team_id SERIAL PRIMARY KEY,
    team_name VARCHAR(100) NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE TeamMembers (
    team_id INT REFERENCES Teams(team_id) ON DELETE CASCADE,
    user_id INT REFERENCES Users(user_id) ON DELETE CASCADE,
    role VARCHAR(50) DEFAULT 'member',
    PRIMARY KEY (team_id, user_id)
);

CREATE TABLE Projects (
    project_id SERIAL PRIMARY KEY,
    project_name VARCHAR(150) NOT NULL,
    description TEXT,
    start_date DATE,
    end_date DATE,
    status VARCHAR(50) DEFAULT 'Planning',
    priority VARCHAR(20) DEFAULT 'Medium',
    team_id INT REFERENCES Teams(team_id) ON DELETE SET NULL,
    created_by INT REFERENCES Users(user_id),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE ProjectMembers (
    project_id INT REFERENCES Projects(project_id) ON DELETE CASCADE,
    user_id INT REFERENCES Users(user_id) ON DELETE CASCADE,
    role VARCHAR(50) DEFAULT 'Contributor',
    PRIMARY KEY (project_id, user_id)
);

CREATE TABLE Tasks (
    task_id SERIAL PRIMARY KEY,
    project_id INT REFERENCES Projects(project_id) ON DELETE CASCADE,
    assigned_to INT REFERENCES Users(user_id),
    title VARCHAR(150) NOT NULL,
    description TEXT,
    status VARCHAR(50) DEFAULT 'To Do',
    priority VARCHAR(20) DEFAULT 'Medium',
    start_date DATE,
    due_date DATE,
    completed_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE Comments (
    comment_id SERIAL PRIMARY KEY,
    task_id INT REFERENCES Tasks(task_id) ON DELETE CASCADE,
    user_id INT REFERENCES Users(user_id) ON DELETE CASCADE,
    content TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE Attachments (
    attachment_id SERIAL PRIMARY KEY,
    task_id INT REFERENCES Tasks(task_id) ON DELETE CASCADE,
    file_path VARCHAR(255),
    uploaded_by INT REFERENCES Users(user_id),
    uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE TimeLogs (
    timelog_id SERIAL PRIMARY KEY,
    task_id INT REFERENCES Tasks(task_id) ON DELETE CASCADE,
    user_id INT REFERENCES Users(user_id),
    hours DECIMAL(5,2),
    log_date DATE DEFAULT CURRENT_DATE,
    notes TEXT
);


-- | Entity                       | Description                                                       |
-- | ---------------------------- | ----------------------------------------------------------------- |
-- | **Users**                    | People using the system — can be managers, team members, clients. |
-- | **Projects**                 | Top-level work items that group related tasks.                    |
-- | **Tasks**                    | Individual units of work under projects.                          |
-- | **Teams**                    | Groups of users working together.                                 |
-- | **ProjectMembers**           | Relationship between users and projects (with roles).             |
-- | **Comments**                 | Notes or discussion threads attached to tasks or projects.        |
-- | **Attachments**              | Files or links associated with tasks.                             |
-- | **TimeLogs**                 | Records of time spent on tasks.                                   |
-- | **Status / Priority / Tags** | Optional lookup tables for categorization.                        |


-- | Relationship       | Type | Description                            |
-- | ------------------ | ---- | -------------------------------------- |
-- | User → TeamMembers | 1:N  | A user can belong to many teams.       |
-- | Team → Projects    | 1:N  | A team can own multiple projects.      |
-- | Project → Tasks    | 1:N  | A project has many tasks.              |
-- | Task → Comments    | 1:N  | Tasks can have many comments.          |
-- | Task → Attachments | 1:N  | Tasks can have many attachments.       |
-- | Task → TimeLogs    | 1:N  | Tasks can have multiple time logs.     |
-- | User → Tasks       | 1:N  | A user can be assigned multiple tasks. |