-- FloChief Native Task Management System
-- Comprehensive task management with modern features

USE freedb_test45;

-- Task Labels/Tags for organization
CREATE TABLE IF NOT EXISTS task_labels (
    id INT PRIMARY KEY AUTO_INCREMENT,
    organization_id INT NOT NULL,
    name VARCHAR(50) NOT NULL,
    color VARCHAR(7) DEFAULT '#6B7280', -- Hex color
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_org_labels (organization_id),
    FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
    UNIQUE KEY unique_org_label (organization_id, name)
);

-- Enhanced Tasks Table for FloChief system
CREATE TABLE IF NOT EXISTS flochief_tasks (
    id INT PRIMARY KEY AUTO_INCREMENT,
    organization_id INT NOT NULL,
    project_id INT NOT NULL,
    
    -- Basic Info
    title VARCHAR(500) NOT NULL,
    description TEXT,
    task_number VARCHAR(20), -- Auto-generated: PROJ-001, PROJ-002, etc.
    
    -- Assignment & Ownership
    created_by INT NOT NULL,
    assigned_to INT,
    watchers JSON, -- Array of user IDs watching this task
    
    -- Status & Priority
    status ENUM('backlog', 'todo', 'in_progress', 'review', 'testing', 'done', 'cancelled') DEFAULT 'backlog',
    priority ENUM('lowest', 'low', 'medium', 'high', 'highest') DEFAULT 'medium',
    
    -- Time Management
    due_date DATETIME,
    start_date DATETIME,
    estimated_hours DECIMAL(6,2),
    actual_hours DECIMAL(6,2) DEFAULT 0,
    
    -- Task Organization
    task_type ENUM('task', 'bug', 'feature', 'epic', 'story') DEFAULT 'task',
    parent_task_id INT, -- For subtasks
    epic_id INT, -- Link to epic tasks
    sprint_id INT, -- For agile workflows
    
    -- External Links
    external_links JSON, -- Array of {url, title, type}
    
    -- Recurring Tasks
    is_recurring BOOLEAN DEFAULT FALSE,
    recurrence_pattern VARCHAR(255), -- Cron-like pattern
    recurrence_end_date DATE,
    original_task_id INT, -- For recurring task instances
    
    -- Collaboration
    comments_count INT DEFAULT 0,
    attachments_count INT DEFAULT 0,
    
    -- Slack Integration
    slack_thread_ts VARCHAR(255),
    slack_channel_id VARCHAR(255),
    
    -- Tracking
    completed_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- Indexes for performance
    INDEX idx_org_tasks (organization_id, status),
    INDEX idx_project_tasks (project_id, status),
    INDEX idx_assigned_tasks (assigned_to, status),
    INDEX idx_created_tasks (created_by),
    INDEX idx_due_date (due_date),
    INDEX idx_parent_tasks (parent_task_id),
    INDEX idx_epic_tasks (epic_id),
    INDEX idx_task_number (task_number),
    
    -- Foreign Keys
    FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE RESTRICT,
    FOREIGN KEY (assigned_to) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (parent_task_id) REFERENCES flochief_tasks(id) ON DELETE CASCADE,
    FOREIGN KEY (epic_id) REFERENCES flochief_tasks(id) ON DELETE SET NULL
);

-- Task Labels Association (Many-to-Many)
CREATE TABLE IF NOT EXISTS task_label_associations (
    id INT PRIMARY KEY AUTO_INCREMENT,
    task_id INT NOT NULL,
    label_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_task_labels (task_id),
    INDEX idx_label_tasks (label_id),
    
    FOREIGN KEY (task_id) REFERENCES flochief_tasks(id) ON DELETE CASCADE,
    FOREIGN KEY (label_id) REFERENCES task_labels(id) ON DELETE CASCADE,
    UNIQUE KEY unique_task_label (task_id, label_id)
);

-- Task Attachments for media support
CREATE TABLE IF NOT EXISTS task_attachments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    task_id INT NOT NULL,
    uploaded_by INT NOT NULL,
    
    -- File Info
    original_filename VARCHAR(500) NOT NULL,
    stored_filename VARCHAR(500) NOT NULL,
    file_path VARCHAR(1000) NOT NULL,
    file_size BIGINT NOT NULL, -- Size in bytes
    mime_type VARCHAR(100),
    
    -- Metadata
    file_type ENUM('image', 'video', 'audio', 'document', 'archive', 'other') DEFAULT 'other',
    thumbnail_path VARCHAR(1000), -- For images/videos
    
    -- Organization
    is_public BOOLEAN DEFAULT FALSE,
    description TEXT,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_task_attachments (task_id),
    INDEX idx_user_uploads (uploaded_by),
    
    FOREIGN KEY (task_id) REFERENCES flochief_tasks(id) ON DELETE CASCADE,
    FOREIGN KEY (uploaded_by) REFERENCES users(id) ON DELETE RESTRICT
);

-- Task Comments for collaboration
CREATE TABLE IF NOT EXISTS task_comments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    task_id INT NOT NULL,
    user_id INT NOT NULL,
    
    content TEXT NOT NULL,
    mentions JSON, -- Array of mentioned user IDs
    
    -- Threading support
    parent_comment_id INT,
    
    -- Edit tracking
    is_edited BOOLEAN DEFAULT FALSE,
    edited_at TIMESTAMP NULL,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_task_comments (task_id, created_at),
    INDEX idx_user_comments (user_id),
    INDEX idx_parent_comments (parent_comment_id),
    
    FOREIGN KEY (task_id) REFERENCES flochief_tasks(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT,
    FOREIGN KEY (parent_comment_id) REFERENCES task_comments(id) ON DELETE CASCADE
);

-- Task Activity Log for audit trail
CREATE TABLE IF NOT EXISTS task_activities (
    id INT PRIMARY KEY AUTO_INCREMENT,
    task_id INT NOT NULL,
    user_id INT,
    
    -- Activity Details
    activity_type ENUM(
        'created', 'updated', 'assigned', 'unassigned', 'status_changed', 
        'priority_changed', 'due_date_changed', 'commented', 'attachment_added',
        'attachment_removed', 'label_added', 'label_removed', 'deleted'
    ) NOT NULL,
    
    old_value TEXT,
    new_value TEXT,
    field_name VARCHAR(100), -- Which field was changed
    
    -- Context
    description TEXT, -- Human readable description
    metadata JSON, -- Additional context data
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_task_activities (task_id, created_at),
    INDEX idx_user_activities (user_id, created_at),
    INDEX idx_activity_type (activity_type),
    
    FOREIGN KEY (task_id) REFERENCES flochief_tasks(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
);

-- Task Time Tracking
CREATE TABLE IF NOT EXISTS task_time_entries (
    id INT PRIMARY KEY AUTO_INCREMENT,
    task_id INT NOT NULL,
    user_id INT NOT NULL,
    
    -- Time Details
    start_time TIMESTAMP NOT NULL,
    end_time TIMESTAMP,
    duration_minutes INT, -- Calculated or manual entry
    
    -- Description
    description TEXT,
    is_billable BOOLEAN DEFAULT FALSE,
    
    -- Auto vs Manual
    entry_type ENUM('auto', 'manual') DEFAULT 'manual',
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_task_time (task_id),
    INDEX idx_user_time (user_id, start_time),
    
    FOREIGN KEY (task_id) REFERENCES flochief_tasks(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Task Dependencies
CREATE TABLE IF NOT EXISTS task_dependencies (
    id INT PRIMARY KEY AUTO_INCREMENT,
    task_id INT NOT NULL, -- The dependent task
    depends_on_task_id INT NOT NULL, -- The task it depends on
    dependency_type ENUM('blocks', 'subtask', 'related') DEFAULT 'blocks',
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_by INT NOT NULL,
    
    INDEX idx_task_deps (task_id),
    INDEX idx_blocking_tasks (depends_on_task_id),
    
    FOREIGN KEY (task_id) REFERENCES flochief_tasks(id) ON DELETE CASCADE,
    FOREIGN KEY (depends_on_task_id) REFERENCES flochief_tasks(id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE RESTRICT,
    
    UNIQUE KEY unique_dependency (task_id, depends_on_task_id)
);

-- Sprints/Iterations for Agile workflows
CREATE TABLE IF NOT EXISTS project_sprints (
    id INT PRIMARY KEY AUTO_INCREMENT,
    project_id INT NOT NULL,
    organization_id INT NOT NULL,
    
    name VARCHAR(255) NOT NULL,
    description TEXT,
    
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    
    status ENUM('planned', 'active', 'completed', 'cancelled') DEFAULT 'planned',
    
    -- Goals and Metrics
    goal TEXT,
    story_points_committed INT DEFAULT 0,
    story_points_completed INT DEFAULT 0,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_project_sprints (project_id, status),
    INDEX idx_org_sprints (organization_id),
    
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE
);

-- Task Notifications/Reminders
CREATE TABLE IF NOT EXISTS task_notifications (
    id INT PRIMARY KEY AUTO_INCREMENT,
    task_id INT NOT NULL,
    user_id INT NOT NULL,
    
    -- Notification Details
    notification_type ENUM(
        'due_soon', 'overdue', 'assigned', 'mentioned', 'status_changed',
        'comment_added', 'attachment_added', 'dependency_resolved'
    ) NOT NULL,
    
    title VARCHAR(500) NOT NULL,
    message TEXT,
    
    -- Delivery
    is_read BOOLEAN DEFAULT FALSE,
    sent_via_email BOOLEAN DEFAULT FALSE,
    sent_via_slack BOOLEAN DEFAULT FALSE,
    
    -- Scheduling
    scheduled_for TIMESTAMP,
    sent_at TIMESTAMP NULL,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_user_notifications (user_id, is_read, created_at),
    INDEX idx_task_notifications (task_id),
    INDEX idx_scheduled_notifications (scheduled_for),
    
    FOREIGN KEY (task_id) REFERENCES flochief_tasks(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Update projects table to support FloChief task management
-- ALTER TABLE projects 
-- ADD COLUMN task_management_type ENUM('sheets', 'trello', 'flochief') DEFAULT 'sheets' AFTER integration_type;
-- (Column may already exist from previous migrations)

-- Create initial task labels for new organizations
INSERT INTO task_labels (organization_id, name, color) 
SELECT id, 'Bug', '#EF4444' FROM organizations WHERE id NOT IN (SELECT DISTINCT organization_id FROM task_labels WHERE name = 'Bug');

INSERT INTO task_labels (organization_id, name, color) 
SELECT id, 'Feature', '#3B82F6' FROM organizations WHERE id NOT IN (SELECT DISTINCT organization_id FROM task_labels WHERE name = 'Feature');

INSERT INTO task_labels (organization_id, name, color) 
SELECT id, 'Enhancement', '#10B981' FROM organizations WHERE id NOT IN (SELECT DISTINCT organization_id FROM task_labels WHERE name = 'Enhancement');

INSERT INTO task_labels (organization_id, name, color) 
SELECT id, 'Documentation', '#8B5CF6' FROM organizations WHERE id NOT IN (SELECT DISTINCT organization_id FROM task_labels WHERE name = 'Documentation');

INSERT INTO task_labels (organization_id, name, color) 
SELECT id, 'Urgent', '#F59E0B' FROM organizations WHERE id NOT IN (SELECT DISTINCT organization_id FROM task_labels WHERE name = 'Urgent');

-- Success message
SELECT 'FloChief Task Management System created successfully!' as status;
SELECT 'Tables created: task_labels, flochief_tasks, task_label_associations, task_attachments, task_comments, task_activities, task_time_entries, task_dependencies, project_sprints, task_notifications' as tables_created;