allocator_system.sql

CREATE TABLE users (
    user_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    role ENUM('Manager', 'Project Leader', 'Team Member') NOT NULL,
    address TEXT NOT NULL,
    dob DATE NOT NULL,
    id_number VARCHAR(20) NOT NULL UNIQUE,
    telephone VARCHAR(15) NOT NULL,
    qualification VARCHAR(100) NOT NULL,
    skills TEXT NOT NULL,
    photo_path VARCHAR(255) DEFAULT NULL
);

CREATE TABLE projects (
    project_id VARCHAR(20) PRIMARY KEY, 
    title VARCHAR(100) NOT NULL,
    description TEXT NOT NULL,
    customer_name VARCHAR(100) NOT NULL,
    budget DECIMAL(10,2) NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    team_leader_id BIGINT NULL,
    FOREIGN KEY (team_leader_id) REFERENCES users(user_id) ON DELETE CASCADE 
);

CREATE TABLE project_documents (
    id INT AUTO_INCREMENT PRIMARY KEY,
    project_id VARCHAR(20) NOT NULL, 
    title VARCHAR(100) NOT NULL,
    file_path VARCHAR(255) NOT NULL,
    FOREIGN KEY (project_id) REFERENCES projects(project_id) ON DELETE CASCADE
);

CREATE TABLE tasks (
    task_id INT AUTO_INCREMENT PRIMARY KEY,
    task_name VARCHAR(100) NOT NULL,
    description TEXT NOT NULL,
    project_id VARCHAR(20), 
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    effort DECIMAL(10,2), 
    progress DECIMAL(5,2) DEFAULT 0, 
    status ENUM('Pending', 'In Progress', 'Completed') DEFAULT 'Pending',
    priority ENUM('Low', 'Medium', 'High') NOT NULL,
    FOREIGN KEY (project_id) REFERENCES projects(project_id) ON DELETE CASCADE
);

CREATE TABLE task_allocations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    task_id INT NOT NULL,
    user_id BIGINT NOT NULL, 
    role ENUM('Developer', 'Designer', 'Tester', 'Analyst', 'Support') NOT NULL,
    contribution_percentage DECIMAL(5,2) NOT NULL CHECK (contribution_percentage BETWEEN 1 AND 100),
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    FOREIGN KEY (task_id) REFERENCES tasks(task_id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
);

INSERT INTO users (user_id,full_name, email, username, password, role, address, dob, id_number, telephone, qualification, skills, photo_path)
VALUES
(1281907130,'Ramadan Manager', 'manager1@example.com', 'manager', 'pass1234', 'Manager', '10 Downing St, London, UK', '1980-05-15', 'ID0001', '1234567890', 'MBA', 'Leadership, Project Management', 'ramadan.png'),
(1281907131,'Wasem Leader', 'wasem.leader@example.com', 'wasemleader','pass1234', 'Project Leader', '15 Elm St, Boston, USA', '1985-09-25', 'ID0002', '9876543210', 'Bachelors Degree', 'Team Coordination, Communication', 'BMWM5CS.png'),
(1281907132,'sami Member', 'sami.member@example.com', 'samimember', 'pass1234', 'Team Member', '22 Pine Ave, Toronto, Canada', '1990-07-10', 'ID0003', '1122334455', 'Associate Degree', 'Coding, Testing', 'Mercedes-AMG.png'),
(1281907133,'Dana Developer', 'dana.dev@example.com', 'danadeveloper', 'pass1234', 'Team Member', '7 Maple Ln, Sydney, Australia', '1995-11-30', 'ID0004', '9988776655', 'Bachelor Degree', 'JavaScript, Python, Debugging', 'bmwX5.png'),
(1281907134,'Omar Leader', 'omar@example.com', 'omarmember','pass1234', 'Team Member', '3 Birch Rd, Mumbai, India', '1992-03-22', 'ID0005', '5544332211', 'Master Degree', 'Testing, Quality Assurance', 'mustang.png'),
(1281907135,'Ramadan Leader', 'ramadan.leader@example.com', 'ramadanleader','pass1234', 'Project Leader', '15 Elm St, Boston, USA', '1985-09-25', 'ID0006', '9876543210', 'Bachelors Degree', 'Team Coordination, Communication', 'ramadan.png'),
(1281907136,'Ayman Member', 'Ayman@example.com', 'aymanmember', 'pass1234', 'Team Member', '22 Pine Ave, Toronto, Canada', '1990-07-10', 'ID0007', '1122334455', 'Associate Degree', 'Coding, Testing', 'ayman.png'),
(1281907137,'Khalid Member', 'Khalid@example.com', 'khalid', 'pass1234', 'Team Member', '22 Pine Ave, Toronto, Canada', '1990-07-10', 'ID0008', '1122334455', 'Associate Degree', 'Coding, Testing', 'bmw m3.png');

INSERT INTO projects (project_id, title, description, customer_name, budget, start_date, end_date, team_leader_id)
VALUES
('PROJ-10001', 'Website Redesign', 'Redesign the company website for better user experience.', 'Tech Corp', 10000.00, '2024-01-01', '2024-03-01', 1281907130),
('PROJ-10002', 'Mobile App Development', 'Develop a cross-platform mobile app for e-commerce.', 'ShopEase', 25000.00, '2024-02-15', '2024-05-15', 1281907132),
('PROJ-10003', 'Database Migration', 'Migrate the legacy database to a cloud-based solution.', 'DataWorks', 15000.00, '2024-03-01', '2024-06-01', NULL),
('PROJ-10004', 'SEO Optimization', 'Improve SEO rankings for the client’s website.', 'RankBoost', 8000.00, '2024-02-01', '2024-02-28', 1281907131),
('PROJ-10005', 'Cybersecurity Audit', 'Conduct a full audit of cybersecurity protocols.', 'SecureNet', 20000.00, '2024-04-01', '2024-07-01', NULL);

INSERT INTO tasks (task_name, description, project_id, start_date, end_date, effort, progress, status, priority)
VALUES
('Design Homepage', 'Create a modern homepage design for the website.', 'PROJ-10001', '2024-01-01', '2024-01-15', 2.5, 0, 'Pending', 'High'),
('Database Schema Update', 'Update the database schema for compatibility with new features.', 'PROJ-10002', '2024-02-20', '2024-03-05', 3.0, 50, 'In Progress', 'Medium'),
('SEO Analysis', 'Perform an analysis of the client’s website SEO.', 'PROJ-10004', '2024-02-01', '2024-02-10', 1.5, 100, 'Completed', 'Low'),
('Cybersecurity Assessment', 'Conduct a cybersecurity risk assessment.', 'PROJ-10005', '2024-04-01', '2024-04-20', 4.0, 0, 'Pending', 'High'),
('Migration Testing', 'Test the database after migration to ensure data integrity.', 'PROJ-10003', '2024-03-10', '2024-03-25', 2.5, 0, 'Pending', 'Medium');

INSERT INTO project_documents (project_id, title, file_path)
VALUES
('PROJ-10001', 'Contract Agreement', '/documents/proj1.docx'),
('PROJ-10002', 'Project Plan', '/documents/proj2.docx'),
('PROJ-10003', 'Design Mockups', '/documents/proj3.docx'),
('PROJ-10004', 'Migration Strategy', '/documents/pro4.docx'),
('PROJ-10005', 'SEO Report', '/documents/proj5.docx');

INSERT INTO task_allocations (task_id, user_id, role, contribution_percentage, start_date,end_date)
VALUES 
(1, 1281907136, 'Designer', 50.00, '2024-01-01','2024-01-03'),
(1, 1281907133, 'Developer', 50.00, '2024-01-01','2024-01-04'),
(2, 1281907132, 'Tester', 100.00, '2024-02-20','2024-02-26');