CREATE TABLE task_statuses (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(60) NOT NULL UNIQUE,
    name_ar VARCHAR(120) NOT NULL,
    name_en VARCHAR(120) NULL,
    category VARCHAR(30) NOT NULL DEFAULT 'open',
    color CHAR(7) NOT NULL DEFAULT '#64748b',
    sort_order INT NOT NULL DEFAULT 0,
    wip_limit INT UNSIGNED NULL,
    is_initial TINYINT(1) NOT NULL DEFAULT 0,
    is_completed TINYINT(1) NOT NULL DEFAULT 0,
    is_closed TINYINT(1) NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY idx_task_statuses_board (is_active, sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE task_status_transitions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    from_status_id BIGINT UNSIGNED NOT NULL,
    to_status_id BIGINT UNSIGNED NOT NULL,
    name_ar VARCHAR(120) NOT NULL,
    required_permission_code VARCHAR(190) NULL,
    requires_comment TINYINT(1) NOT NULL DEFAULT 0,
    sort_order INT NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    UNIQUE KEY uq_task_status_transition (from_status_id, to_status_id),
    CONSTRAINT fk_task_transition_from FOREIGN KEY (from_status_id) REFERENCES task_statuses(id) ON DELETE CASCADE,
    CONSTRAINT fk_task_transition_to FOREIGN KEY (to_status_id) REFERENCES task_statuses(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE tasks (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    public_id CHAR(26) NOT NULL UNIQUE,
    task_number VARCHAR(60) NOT NULL UNIQUE,
    project_id BIGINT UNSIGNED NOT NULL,
    parent_task_id BIGINT UNSIGNED NULL,
    wbs_node_id BIGINT UNSIGNED NULL,
    activity_id BIGINT UNSIGNED NULL,
    status_id BIGINT UNSIGNED NOT NULL,
    title VARCHAR(255) NOT NULL,
    description TEXT NULL,
    priority VARCHAR(20) NOT NULL DEFAULT 'medium',
    original_start_date DATE NULL,
    original_due_date DATE NULL,
    current_start_date DATE NULL,
    current_due_date DATE NULL,
    actual_start_date DATE NULL,
    actual_finish_date DATE NULL,
    estimated_hours DECIMAL(10,2) NULL,
    actual_hours DECIMAL(10,2) NULL,
    progress_percentage DECIMAL(8,4) NOT NULL DEFAULT 0,
    progress_mode VARCHAR(20) NOT NULL DEFAULT 'manual',
    task_weight DECIMAL(12,6) NOT NULL DEFAULT 1,
    sync_activity_progress TINYINT(1) NOT NULL DEFAULT 0,
    sort_order INT NOT NULL DEFAULT 0,
    created_by BIGINT UNSIGNED NULL,
    updated_by BIGINT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    archived_at DATETIME NULL,
    KEY idx_tasks_project_status (project_id, status_id, archived_at),
    KEY idx_tasks_parent (parent_task_id, archived_at),
    KEY idx_tasks_activity (activity_id, sync_activity_progress, archived_at),
    KEY idx_tasks_due (current_due_date, archived_at),
    CONSTRAINT fk_tasks_project FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE RESTRICT,
    CONSTRAINT fk_tasks_parent FOREIGN KEY (parent_task_id) REFERENCES tasks(id) ON DELETE RESTRICT,
    CONSTRAINT fk_tasks_wbs FOREIGN KEY (wbs_node_id) REFERENCES wbs_nodes(id) ON DELETE SET NULL,
    CONSTRAINT fk_tasks_activity FOREIGN KEY (activity_id) REFERENCES schedule_activities(id) ON DELETE SET NULL,
    CONSTRAINT fk_tasks_status FOREIGN KEY (status_id) REFERENCES task_statuses(id),
    CONSTRAINT fk_tasks_creator FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
    CONSTRAINT fk_tasks_updater FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE task_assignees (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    task_id BIGINT UNSIGNED NOT NULL,
    employee_id BIGINT UNSIGNED NOT NULL,
    assignment_role VARCHAR(30) NOT NULL DEFAULT 'participant',
    assigned_by BIGINT UNSIGNED NULL,
    assigned_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    removed_at DATETIME NULL,
    UNIQUE KEY uq_task_assignee (task_id, employee_id),
    KEY idx_task_assignees_employee (employee_id, removed_at),
    CONSTRAINT fk_task_assignees_task FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE,
    CONSTRAINT fk_task_assignees_employee FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE RESTRICT,
    CONSTRAINT fk_task_assignees_user FOREIGN KEY (assigned_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE task_checklist_items (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    task_id BIGINT UNSIGNED NOT NULL,
    title VARCHAR(255) NOT NULL,
    is_completed TINYINT(1) NOT NULL DEFAULT 0,
    completed_by BIGINT UNSIGNED NULL,
    completed_at DATETIME NULL,
    sort_order INT NOT NULL DEFAULT 0,
    created_by BIGINT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY idx_task_checklist_order (task_id, sort_order),
    CONSTRAINT fk_task_checklist_task FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE,
    CONSTRAINT fk_task_checklist_completed_by FOREIGN KEY (completed_by) REFERENCES users(id) ON DELETE SET NULL,
    CONSTRAINT fk_task_checklist_creator FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE task_comments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    task_id BIGINT UNSIGNED NOT NULL,
    parent_comment_id BIGINT UNSIGNED NULL,
    body TEXT NOT NULL,
    created_by BIGINT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    edited_at DATETIME NULL,
    deleted_at DATETIME NULL,
    KEY idx_task_comments_task (task_id, created_at),
    CONSTRAINT fk_task_comments_task FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE,
    CONSTRAINT fk_task_comments_parent FOREIGN KEY (parent_comment_id) REFERENCES task_comments(id) ON DELETE SET NULL,
    CONSTRAINT fk_task_comments_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE task_status_history (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    task_id BIGINT UNSIGNED NOT NULL,
    from_status_id BIGINT UNSIGNED NULL,
    to_status_id BIGINT UNSIGNED NOT NULL,
    progress_before DECIMAL(8,4) NOT NULL DEFAULT 0,
    progress_after DECIMAL(8,4) NOT NULL DEFAULT 0,
    changed_by BIGINT UNSIGNED NULL,
    comment TEXT NULL,
    changed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_task_status_history (task_id, changed_at),
    CONSTRAINT fk_task_history_task FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE,
    CONSTRAINT fk_task_history_from FOREIGN KEY (from_status_id) REFERENCES task_statuses(id) ON DELETE SET NULL,
    CONSTRAINT fk_task_history_to FOREIGN KEY (to_status_id) REFERENCES task_statuses(id),
    CONSTRAINT fk_task_history_user FOREIGN KEY (changed_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE task_labels (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name_ar VARCHAR(80) NOT NULL UNIQUE,
    color CHAR(7) NOT NULL DEFAULT '#64748b',
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_by BIGINT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_task_labels_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE task_label_assignments (
    task_id BIGINT UNSIGNED NOT NULL,
    label_id BIGINT UNSIGNED NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (task_id, label_id),
    CONSTRAINT fk_task_label_assignment_task FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE,
    CONSTRAINT fk_task_label_assignment_label FOREIGN KEY (label_id) REFERENCES task_labels(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO task_statuses
    (code, name_ar, name_en, category, color, sort_order, is_initial, is_completed, is_closed)
VALUES
    ('backlog', 'قائمة الانتظار', 'Backlog', 'backlog', '#64748b', 10, 1, 0, 0),
    ('planned', 'مخطط لها', 'Planned', 'open', '#2563eb', 20, 0, 0, 0),
    ('in_progress', 'قيد التنفيذ', 'In Progress', 'in_progress', '#d97706', 30, 0, 0, 0),
    ('under_review', 'تحت المراجعة', 'Under Review', 'review', '#7c3aed', 40, 0, 0, 0),
    ('completed', 'مكتملة', 'Completed', 'completed', '#059669', 50, 0, 1, 1),
    ('cancelled', 'ملغاة', 'Cancelled', 'cancelled', '#dc2626', 60, 0, 0, 1);

INSERT INTO task_status_transitions
    (from_status_id, to_status_id, name_ar, required_permission_code, requires_comment, sort_order)
SELECT source_status.id, target_status.id, transition_data.name_ar, 'tasks.change_status', transition_data.requires_comment, transition_data.sort_order
FROM (
    SELECT 'backlog' AS from_code, 'planned' AS to_code, 'جدولة المهمة' AS name_ar, 0 AS requires_comment, 10 AS sort_order
    UNION ALL SELECT 'planned', 'in_progress', 'بدء التنفيذ', 0, 20
    UNION ALL SELECT 'in_progress', 'under_review', 'إرسال للمراجعة', 0, 30
    UNION ALL SELECT 'under_review', 'in_progress', 'إعادة للتنفيذ', 1, 40
    UNION ALL SELECT 'under_review', 'completed', 'اعتماد الإكمال', 0, 50
    UNION ALL SELECT 'in_progress', 'completed', 'إكمال المهمة', 0, 60
    UNION ALL SELECT 'completed', 'in_progress', 'إعادة فتح المهمة', 1, 70
    UNION ALL SELECT 'backlog', 'cancelled', 'إلغاء المهمة', 1, 80
    UNION ALL SELECT 'planned', 'cancelled', 'إلغاء المهمة', 1, 90
    UNION ALL SELECT 'in_progress', 'cancelled', 'إلغاء المهمة', 1, 100
    UNION ALL SELECT 'cancelled', 'backlog', 'استعادة المهمة', 1, 110
) transition_data
JOIN task_statuses source_status ON source_status.code = transition_data.from_code
JOIN task_statuses target_status ON target_status.code = transition_data.to_code;

INSERT INTO numbering_schemes
    (entity_type, name_ar, pattern, prefix, padding, reset_frequency)
VALUES ('task', 'ترقيم المهام', '{PREFIX}-{YEAR}-{NUMBER}', 'TSK', 5, 'yearly');

INSERT INTO permissions (module_code, code, name_ar) VALUES
('tasks', 'tasks.view', 'عرض المهام المتاحة'),
('tasks', 'tasks.view_all', 'عرض جميع المهام'),
('tasks', 'tasks.create', 'إنشاء المهام'),
('tasks', 'tasks.update', 'تعديل المهام'),
('tasks', 'tasks.assign', 'إسناد المهام'),
('tasks', 'tasks.change_status', 'تغيير حالة المهام'),
('tasks', 'tasks.comment', 'إضافة التعليقات'),
('tasks', 'tasks.manage_checklist', 'إدارة قوائم التحقق'),
('tasks', 'tasks.attach', 'إضافة مرفقات المهام'),
('tasks', 'tasks.archive', 'أرشفة المهام');

INSERT INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM roles r CROSS JOIN permissions p
WHERE r.code = 'administrator' AND r.scope_type = 'global' AND p.module_code = 'tasks';

INSERT INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM roles r JOIN permissions p ON p.code IN (
    'tasks.view', 'tasks.view_all', 'tasks.create', 'tasks.update', 'tasks.assign',
    'tasks.change_status', 'tasks.comment', 'tasks.manage_checklist', 'tasks.attach', 'tasks.archive'
) WHERE r.code = 'general_manager' AND r.scope_type = 'global';

INSERT INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM roles r JOIN permissions p ON p.code IN (
    'tasks.view', 'tasks.create', 'tasks.update', 'tasks.assign', 'tasks.change_status',
    'tasks.comment', 'tasks.manage_checklist', 'tasks.attach', 'tasks.archive'
) WHERE r.code = 'project_manager' AND r.scope_type = 'project';

INSERT INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM roles r JOIN permissions p ON p.code IN (
    'tasks.view', 'tasks.create', 'tasks.update', 'tasks.change_status', 'tasks.comment', 'tasks.manage_checklist', 'tasks.attach'
) WHERE r.code = 'project_member' AND r.scope_type = 'project';

INSERT INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM roles r JOIN permissions p ON p.code IN (
    'tasks.view', 'tasks.create', 'tasks.comment', 'tasks.manage_checklist', 'tasks.attach'
) WHERE r.code = 'employee' AND r.scope_type = 'global';

UPDATE modules SET version = '0.3.0', is_enabled = 1 WHERE code = 'tasks';
UPDATE modules SET version = '0.3.0' WHERE code IN ('core', 'projects', 'planning');