-- Tasks Management System Database Schema

-- Main tasks table
CREATE TABLE IF NOT EXISTS tasks (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    project_id INT NULL,
    assigned_to INT NOT NULL,
    assigned_by INT NOT NULL,
    priority ENUM('low', 'medium', 'high', 'urgent') DEFAULT 'medium',
    status ENUM('pending', 'in_progress', 'review', 'completed', 'cancelled') DEFAULT 'pending',
    category VARCHAR(100),
    estimated_hours DECIMAL(10,2) DEFAULT 0,
    actual_hours DECIMAL(10,2) DEFAULT 0,
    progress INT DEFAULT 0,
    start_date DATE,
    due_date DATE,
    completed_date DATETIME NULL,
    parent_task_id INT NULL,
    attachments TEXT NULL,
    tags VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (assigned_to) REFERENCES staff_members(id) ON DELETE CASCADE,
    FOREIGN KEY (parent_task_id) REFERENCES tasks(id) ON DELETE CASCADE,
    INDEX idx_assigned_to (assigned_to),
    INDEX idx_status (status),
    INDEX idx_priority (priority),
    INDEX idx_due_date (due_date),
    INDEX idx_parent_task (parent_task_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Task comments/activity log
CREATE TABLE IF NOT EXISTS task_comments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    task_id INT NOT NULL,
    user_id INT NOT NULL,
    user_type ENUM('staff', 'admin') NOT NULL,
    comment TEXT NOT NULL,
    is_system_message BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE,
    INDEX idx_task_id (task_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Task time logs
CREATE TABLE IF NOT EXISTS task_time_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    task_id INT NOT NULL,
    staff_id INT NOT NULL,
    start_time DATETIME NOT NULL,
    end_time DATETIME NULL,
    duration_minutes INT DEFAULT 0,
    description TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE,
    FOREIGN KEY (staff_id) REFERENCES staff_members(id) ON DELETE CASCADE,
    INDEX idx_task_id (task_id),
    INDEX idx_staff_id (staff_id),
    INDEX idx_is_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Task checklist items
CREATE TABLE IF NOT EXISTS task_checklist (
    id INT AUTO_INCREMENT PRIMARY KEY,
    task_id INT NOT NULL,
    item_text VARCHAR(500) NOT NULL,
    is_completed BOOLEAN DEFAULT FALSE,
    completed_by INT NULL,
    completed_at DATETIME NULL,
    sort_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE,
    INDEX idx_task_id (task_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Task attachments
CREATE TABLE IF NOT EXISTS task_attachments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    task_id INT NOT NULL,
    file_name VARCHAR(255) NOT NULL,
    file_path VARCHAR(500) NOT NULL,
    file_size INT,
    file_type VARCHAR(50),
    uploaded_by INT NOT NULL,
    uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE,
    INDEX idx_task_id (task_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Insert sample tasks
INSERT INTO tasks (title, description, assigned_to, assigned_by, priority, status, category, estimated_hours, start_date, due_date, progress) VALUES
('Complete Website Redesign', 'Redesign the company website with modern UI/UX principles', 1, 1, 'high', 'in_progress', 'Development', 40.00, '2025-12-20', '2026-01-15', 45),
('Database Optimization', 'Optimize database queries and add proper indexing', 1, 1, 'urgent', 'pending', 'Backend', 16.00, '2025-12-28', '2026-01-05', 0),
('Mobile App Bug Fixes', 'Fix critical bugs reported by users', 1, 1, 'high', 'in_progress', 'Mobile', 8.00, '2025-12-25', '2025-12-30', 60);

-- Insert sample subtasks
INSERT INTO tasks (title, description, assigned_to, assigned_by, priority, status, parent_task_id, estimated_hours, progress) VALUES
('Design Homepage Mockup', 'Create mockup for the new homepage design', 1, 1, 'high', 'completed', 1, 8.00, 100),
('Implement Header Component', 'Build responsive header with navigation', 1, 1, 'medium', 'in_progress', 1, 6.00, 70),
('Create Footer Section', 'Design and implement website footer', 1, 1, 'medium', 'pending', 1, 4.00, 0);

-- Insert sample comments
INSERT INTO task_comments (task_id, user_id, user_type, comment) VALUES
(1, 1, 'admin', 'Task assigned. Please start with the homepage mockup.'),
(1, 1, 'staff', 'Started working on the design. Will share mockup by EOD.'),
(2, 1, 'admin', 'Priority task. Please focus on the slow running queries first.');

-- Insert sample time logs
INSERT INTO task_time_logs (task_id, staff_id, start_time, end_time, duration_minutes, description) VALUES
(1, 1, '2025-12-28 09:00:00', '2025-12-28 12:30:00', 210, 'Working on homepage mockup design'),
(1, 1, '2025-12-28 14:00:00', '2025-12-28 17:00:00', 180, 'Implementing header component'),
(3, 1, '2025-12-27 10:00:00', '2025-12-27 14:00:00', 240, 'Fixing login screen issues');
