-- Project Management System Migration
-- Adds comprehensive project management with tasks, statuses, assignments, comments, and attachments

-- Project Categories (Web Design, Graphics, Sales, etc.)
CREATE TABLE IF NOT EXISTS project_categories (
    id INT PRIMARY KEY AUTO_INCREMENT,
    company_id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    color VARCHAR(7) DEFAULT '#3B82F6',
    icon VARCHAR(50) DEFAULT 'folder',
    sort_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    INDEX idx_company_category (company_id, sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Projects
CREATE TABLE IF NOT EXISTS projects (
    id INT PRIMARY KEY AUTO_INCREMENT,
    company_id INT NOT NULL,
    category_id INT NULL,
    client_id INT NULL,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    status ENUM('planning', 'active', 'on_hold', 'completed', 'archived') DEFAULT 'planning',
    priority ENUM('low', 'medium', 'high', 'urgent') DEFAULT 'medium',
    start_date DATE NULL,
    due_date DATE NULL,
    completed_at DATETIME NULL,
    public_link_token VARCHAR(64) UNIQUE NULL,
    public_link_enabled BOOLEAN DEFAULT FALSE,
    created_by INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    FOREIGN KEY (category_id) REFERENCES project_categories(id) ON DELETE SET NULL,
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE SET NULL,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_company_project (company_id, status),
    INDEX idx_category (category_id),
    INDEX idx_client (client_id),
    INDEX idx_public_link (public_link_token),
    INDEX idx_due_date (due_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Task Statuses (customizable per workspace)
CREATE TABLE IF NOT EXISTS task_statuses (
    id INT PRIMARY KEY AUTO_INCREMENT,
    company_id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    color VARCHAR(7) DEFAULT '#64748B',
    is_default BOOLEAN DEFAULT FALSE,
    is_completed BOOLEAN DEFAULT FALSE,
    sort_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    INDEX idx_company_status (company_id, sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Tasks
CREATE TABLE IF NOT EXISTS tasks (
    id INT PRIMARY KEY AUTO_INCREMENT,
    project_id INT NOT NULL,
    company_id INT NOT NULL,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    status_id INT NULL,
    priority ENUM('low', 'medium', 'high', 'urgent') DEFAULT 'medium',
    due_date DATETIME NULL,
    estimated_hours DECIMAL(10,2) NULL,
    actual_hours DECIMAL(10,2) NULL,
    sort_order INT DEFAULT 0,
    created_by INT NOT NULL,
    completed_at DATETIME NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    FOREIGN KEY (status_id) REFERENCES task_statuses(id) ON DELETE SET NULL,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_project_task (project_id, sort_order),
    INDEX idx_company_task (company_id),
    INDEX idx_status (status_id),
    INDEX idx_due_date (due_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Task Assignments
CREATE TABLE IF NOT EXISTS task_assignments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    task_id INT NOT NULL,
    user_id INT NOT NULL,
    assigned_by INT NOT NULL,
    assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (assigned_by) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY unique_task_user (task_id, user_id),
    INDEX idx_user_tasks (user_id),
    INDEX idx_task (task_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Task Comments
CREATE TABLE IF NOT EXISTS task_comments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    task_id INT NOT NULL,
    user_id INT NOT NULL,
    comment TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_task_comments (task_id, created_at DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Task Attachments
CREATE TABLE IF NOT EXISTS task_attachments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    task_id INT NOT NULL,
    uploaded_by INT NOT NULL,
    filename VARCHAR(255) NOT NULL,
    original_filename VARCHAR(255) NOT NULL,
    file_path VARCHAR(500) NOT NULL,
    file_size INT NOT NULL,
    mime_type VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE,
    FOREIGN KEY (uploaded_by) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_task_attachments (task_id, created_at DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Project Members (who can access this project)
CREATE TABLE IF NOT EXISTS project_members (
    id INT PRIMARY KEY AUTO_INCREMENT,
    project_id INT NOT NULL,
    user_id INT NOT NULL,
    role ENUM('owner', 'manager', 'member', 'viewer') DEFAULT 'member',
    added_by INT NOT NULL,
    added_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (added_by) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY unique_project_user (project_id, user_id),
    INDEX idx_user_projects (user_id),
    INDEX idx_project (project_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Insert default task statuses for existing workspaces
INSERT INTO task_statuses (company_id, name, color, is_default, is_completed, sort_order)
SELECT id, 'To Do', '#64748B', TRUE, FALSE, 1 FROM companies
WHERE NOT EXISTS (SELECT 1 FROM task_statuses WHERE company_id = companies.id);

INSERT INTO task_statuses (company_id, name, color, is_default, is_completed, sort_order)
SELECT id, 'In Progress', '#3B82F6', FALSE, FALSE, 2 FROM companies
WHERE NOT EXISTS (SELECT 1 FROM task_statuses WHERE company_id = companies.id AND name = 'In Progress');

INSERT INTO task_statuses (company_id, name, color, is_default, is_completed, sort_order)
SELECT id, 'Review', '#F59E0B', FALSE, FALSE, 3 FROM companies
WHERE NOT EXISTS (SELECT 1 FROM task_statuses WHERE company_id = companies.id AND name = 'Review');

INSERT INTO task_statuses (company_id, name, color, is_default, is_completed, sort_order)
SELECT id, 'Done', '#10B981', FALSE, TRUE, 4 FROM companies
WHERE NOT EXISTS (SELECT 1 FROM task_statuses WHERE company_id = companies.id AND name = 'Done');

-- Insert default project categories for existing workspaces
INSERT INTO project_categories (company_id, name, color, icon, sort_order)
SELECT id, 'Web Design', '#3B82F6', 'globe', 1 FROM companies
WHERE NOT EXISTS (SELECT 1 FROM project_categories WHERE company_id = companies.id);

INSERT INTO project_categories (company_id, name, color, icon, sort_order)
SELECT id, 'Graphics', '#8B5CF6', 'palette', 2 FROM companies
WHERE NOT EXISTS (SELECT 1 FROM project_categories WHERE company_id = companies.id AND name = 'Graphics');

INSERT INTO project_categories (company_id, name, color, icon, sort_order)
SELECT id, 'Sales', '#10B981', 'trending-up', 3 FROM companies
WHERE NOT EXISTS (SELECT 1 FROM project_categories WHERE company_id = companies.id AND name = 'Sales');

INSERT INTO project_categories (company_id, name, color, icon, sort_order)
SELECT id, 'Marketing', '#F59E0B', 'megaphone', 4 FROM companies
WHERE NOT EXISTS (SELECT 1 FROM project_categories WHERE company_id = companies.id AND name = 'Marketing');

INSERT INTO project_categories (company_id, name, color, icon, sort_order)
SELECT id, 'Development', '#06B6D4', 'code', 5 FROM companies
WHERE NOT EXISTS (SELECT 1 FROM project_categories WHERE company_id = companies.id AND name = 'Development');
