ALTER TABLE project_tasks
    ADD COLUMN IF NOT EXISTS blocked_reason VARCHAR(190) NULL AFTER status,
    ADD COLUMN IF NOT EXISTS risk_level ENUM('low', 'medium', 'high') NOT NULL DEFAULT 'low' AFTER blocked_reason,
    ADD COLUMN IF NOT EXISTS sla_due_at DATETIME NULL AFTER due_at,
    ADD COLUMN IF NOT EXISTS escalated_at DATETIME NULL AFTER sla_due_at,
    ADD COLUMN IF NOT EXISTS checklist_progress TINYINT UNSIGNED NOT NULL DEFAULT 0 AFTER progress_percent,
    ADD COLUMN IF NOT EXISTS wip_breach_count INT UNSIGNED NOT NULL DEFAULT 0 AFTER checklist_progress,
    ADD COLUMN IF NOT EXISTS duplicated_of_id BIGINT UNSIGNED NULL AFTER opportunity_id,
    ADD COLUMN IF NOT EXISTS is_archived TINYINT(1) NOT NULL DEFAULT 0 AFTER duplicated_of_id,
    ADD COLUMN IF NOT EXISTS archived_at DATETIME NULL AFTER is_archived,
    ADD CONSTRAINT fk_project_tasks_duplicate FOREIGN KEY (duplicated_of_id) REFERENCES project_tasks(id) ON DELETE SET NULL;

CREATE TABLE IF NOT EXISTS project_task_dependencies (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    task_id BIGINT UNSIGNED NOT NULL,
    depends_on_task_id BIGINT UNSIGNED NOT NULL,
    dependency_type ENUM('finish_to_start', 'start_to_start', 'finish_to_finish', 'start_to_finish') NOT NULL DEFAULT 'finish_to_start',
    lag_hours INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_project_task_dependency (tenant_id, task_id, depends_on_task_id),
    INDEX idx_project_task_dependencies_tenant (tenant_id, task_id),
    CONSTRAINT fk_project_task_dependencies_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_project_task_dependencies_task FOREIGN KEY (task_id) REFERENCES project_tasks(id) ON DELETE CASCADE,
    CONSTRAINT fk_project_task_dependencies_depends FOREIGN KEY (depends_on_task_id) REFERENCES project_tasks(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS project_templates (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(150) NOT NULL,
    description TEXT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_by BIGINT UNSIGNED NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_project_templates_name (tenant_id, name),
    CONSTRAINT fk_project_templates_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_project_templates_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS project_template_columns (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    template_id BIGINT UNSIGNED NOT NULL,
    code VARCHAR(60) NOT NULL,
    name VARCHAR(120) NOT NULL,
    color VARCHAR(20) NULL,
    is_closed_stage TINYINT(1) NOT NULL DEFAULT 0,
    sort_order INT UNSIGNED NOT NULL DEFAULT 0,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_project_template_columns (template_id, code),
    CONSTRAINT fk_project_template_columns_template FOREIGN KEY (template_id) REFERENCES project_templates(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS project_template_tasks (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    template_id BIGINT UNSIGNED NOT NULL,
    column_code VARCHAR(60) NOT NULL,
    title VARCHAR(190) NOT NULL,
    details TEXT NULL,
    priority ENUM('low', 'medium', 'high', 'critical') NOT NULL DEFAULT 'medium',
    default_due_days INT UNSIGNED NULL,
    sort_order INT UNSIGNED NOT NULL DEFAULT 0,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_project_template_tasks_template (template_id, column_code, sort_order),
    CONSTRAINT fk_project_template_tasks_template FOREIGN KEY (template_id) REFERENCES project_templates(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS project_task_checklist_items (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    task_id BIGINT UNSIGNED NOT NULL,
    item_text VARCHAR(255) NOT NULL,
    is_done TINYINT(1) NOT NULL DEFAULT 0,
    done_at DATETIME NULL,
    done_by BIGINT UNSIGNED NULL,
    sort_order INT UNSIGNED NOT NULL DEFAULT 0,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_project_task_checklist_tenant_task (tenant_id, task_id, sort_order),
    CONSTRAINT fk_project_task_checklist_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_project_task_checklist_task FOREIGN KEY (task_id) REFERENCES project_tasks(id) ON DELETE CASCADE,
    CONSTRAINT fk_project_task_checklist_user FOREIGN KEY (done_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS project_sla_rules (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    board_id BIGINT UNSIGNED NULL,
    column_id BIGINT UNSIGNED NULL,
    priority ENUM('low', 'medium', 'high', 'critical') NULL,
    warn_minutes INT UNSIGNED NOT NULL DEFAULT 240,
    breach_minutes INT UNSIGNED NOT NULL DEFAULT 480,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_project_sla_rules_tenant (tenant_id, is_active),
    CONSTRAINT fk_project_sla_rules_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_project_sla_rules_board FOREIGN KEY (board_id) REFERENCES project_boards(id) ON DELETE CASCADE,
    CONSTRAINT fk_project_sla_rules_column FOREIGN KEY (column_id) REFERENCES project_columns(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS project_sla_alerts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    task_id BIGINT UNSIGNED NOT NULL,
    alert_type ENUM('warning', 'breach') NOT NULL,
    status ENUM('open', 'resolved') NOT NULL DEFAULT 'open',
    message VARCHAR(255) NOT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    resolved_at DATETIME NULL,
    INDEX idx_project_sla_alerts_tenant (tenant_id, status, created_at),
    CONSTRAINT fk_project_sla_alerts_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_project_sla_alerts_task FOREIGN KEY (task_id) REFERENCES project_tasks(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS project_automation_rules (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    board_id BIGINT UNSIGNED NULL,
    name VARCHAR(150) NOT NULL,
    trigger_event VARCHAR(80) NOT NULL,
    conditions_json JSON NULL,
    action_type VARCHAR(80) NOT NULL,
    action_payload JSON NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    last_run_at DATETIME NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_project_automation_rules_tenant (tenant_id, is_active),
    CONSTRAINT fk_project_automation_rules_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_project_automation_rules_board FOREIGN KEY (board_id) REFERENCES project_boards(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS project_automation_runs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    rule_id BIGINT UNSIGNED NULL,
    task_id BIGINT UNSIGNED NULL,
    status ENUM('success', 'failed', 'skipped') NOT NULL DEFAULT 'success',
    message VARCHAR(255) NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_project_automation_runs_tenant (tenant_id, created_at),
    CONSTRAINT fk_project_automation_runs_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_project_automation_runs_rule FOREIGN KEY (rule_id) REFERENCES project_automation_rules(id) ON DELETE SET NULL,
    CONSTRAINT fk_project_automation_runs_task FOREIGN KEY (task_id) REFERENCES project_tasks(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS project_tags (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(80) NOT NULL,
    color VARCHAR(20) NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_project_tags_name (tenant_id, name),
    CONSTRAINT fk_project_tags_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS project_task_tags (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    task_id BIGINT UNSIGNED NOT NULL,
    tag_id BIGINT UNSIGNED NOT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_project_task_tags (tenant_id, task_id, tag_id),
    INDEX idx_project_task_tags_tenant_task (tenant_id, task_id),
    CONSTRAINT fk_project_task_tags_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_project_task_tags_task FOREIGN KEY (task_id) REFERENCES project_tasks(id) ON DELETE CASCADE,
    CONSTRAINT fk_project_task_tags_tag FOREIGN KEY (tag_id) REFERENCES project_tags(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS project_mentions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    task_id BIGINT UNSIGNED NOT NULL,
    comment_id BIGINT UNSIGNED NULL,
    mentioned_user_id BIGINT UNSIGNED NOT NULL,
    status ENUM('pending', 'sent', 'read') NOT NULL DEFAULT 'pending',
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    read_at DATETIME NULL,
    INDEX idx_project_mentions_tenant_user (tenant_id, mentioned_user_id, status, created_at),
    CONSTRAINT fk_project_mentions_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_project_mentions_task FOREIGN KEY (task_id) REFERENCES project_tasks(id) ON DELETE CASCADE,
    CONSTRAINT fk_project_mentions_comment FOREIGN KEY (comment_id) REFERENCES project_task_comments(id) ON DELETE SET NULL,
    CONSTRAINT fk_project_mentions_user FOREIGN KEY (mentioned_user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS project_task_attachments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    task_id BIGINT UNSIGNED NOT NULL,
    file_name VARCHAR(190) NOT NULL,
    file_path VARCHAR(255) NOT NULL,
    mime_type VARCHAR(120) NULL,
    file_size BIGINT UNSIGNED NULL,
    uploaded_by BIGINT UNSIGNED NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_project_task_attachments_tenant_task (tenant_id, task_id, created_at),
    CONSTRAINT fk_project_task_attachments_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_project_task_attachments_task FOREIGN KEY (task_id) REFERENCES project_tasks(id) ON DELETE CASCADE,
    CONSTRAINT fk_project_task_attachments_user FOREIGN KEY (uploaded_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS project_workload_snapshots (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    board_id BIGINT UNSIGNED NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    open_tasks INT UNSIGNED NOT NULL DEFAULT 0,
    in_progress_tasks INT UNSIGNED NOT NULL DEFAULT 0,
    blocked_tasks INT UNSIGNED NOT NULL DEFAULT 0,
    overdue_tasks INT UNSIGNED NOT NULL DEFAULT 0,
    utilization_score DECIMAL(5,2) NOT NULL DEFAULT 0.00,
    snapshot_at DATETIME NOT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_project_workload_snapshots_tenant_user (tenant_id, user_id, snapshot_at),
    CONSTRAINT fk_project_workload_snapshots_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_project_workload_snapshots_board FOREIGN KEY (board_id) REFERENCES project_boards(id) ON DELETE SET NULL,
    CONSTRAINT fk_project_workload_snapshots_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS project_daily_snapshots (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    board_id BIGINT UNSIGNED NOT NULL,
    snapshot_date DATE NOT NULL,
    open_count INT UNSIGNED NOT NULL DEFAULT 0,
    done_count INT UNSIGNED NOT NULL DEFAULT 0,
    blocked_count INT UNSIGNED NOT NULL DEFAULT 0,
    overdue_count INT UNSIGNED NOT NULL DEFAULT 0,
    throughput_count INT UNSIGNED NOT NULL DEFAULT 0,
    avg_cycle_time_days DECIMAL(8,2) NOT NULL DEFAULT 0.00,
    avg_lead_time_days DECIMAL(8,2) NOT NULL DEFAULT 0.00,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_project_daily_snapshots (tenant_id, board_id, snapshot_date),
    CONSTRAINT fk_project_daily_snapshots_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_project_daily_snapshots_board FOREIGN KEY (board_id) REFERENCES project_boards(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS project_board_permissions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    board_id BIGINT UNSIGNED NOT NULL,
    role VARCHAR(50) NOT NULL,
    can_view TINYINT(1) NOT NULL DEFAULT 1,
    can_create_task TINYINT(1) NOT NULL DEFAULT 1,
    can_edit_task TINYINT(1) NOT NULL DEFAULT 1,
    can_move_task TINYINT(1) NOT NULL DEFAULT 1,
    can_manage_board TINYINT(1) NOT NULL DEFAULT 0,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_project_board_permissions (tenant_id, board_id, role),
    CONSTRAINT fk_project_board_permissions_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_project_board_permissions_board FOREIGN KEY (board_id) REFERENCES project_boards(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS project_task_duplicates (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    task_id BIGINT UNSIGNED NOT NULL,
    duplicate_of_id BIGINT UNSIGNED NOT NULL,
    confidence_score TINYINT UNSIGNED NOT NULL DEFAULT 80,
    reason VARCHAR(140) NULL,
    status ENUM('pending', 'confirmed', 'ignored') NOT NULL DEFAULT 'pending',
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_project_task_duplicates_tenant (tenant_id, status, created_at),
    CONSTRAINT fk_project_task_duplicates_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_project_task_duplicates_task FOREIGN KEY (task_id) REFERENCES project_tasks(id) ON DELETE CASCADE,
    CONSTRAINT fk_project_task_duplicates_dup FOREIGN KEY (duplicate_of_id) REFERENCES project_tasks(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS project_wip_violations (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    board_id BIGINT UNSIGNED NOT NULL,
    column_id BIGINT UNSIGNED NOT NULL,
    task_id BIGINT UNSIGNED NULL,
    limit_value INT UNSIGNED NOT NULL,
    current_value INT UNSIGNED NOT NULL,
    status ENUM('open', 'resolved') NOT NULL DEFAULT 'open',
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    resolved_at DATETIME NULL,
    INDEX idx_project_wip_violations_tenant (tenant_id, status, created_at),
    CONSTRAINT fk_project_wip_violations_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_project_wip_violations_board FOREIGN KEY (board_id) REFERENCES project_boards(id) ON DELETE CASCADE,
    CONSTRAINT fk_project_wip_violations_column FOREIGN KEY (column_id) REFERENCES project_columns(id) ON DELETE CASCADE,
    CONSTRAINT fk_project_wip_violations_task FOREIGN KEY (task_id) REFERENCES project_tasks(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS project_milestones (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    board_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(160) NOT NULL,
    due_on DATE NOT NULL,
    status ENUM('pending', 'done', 'late') NOT NULL DEFAULT 'pending',
    owner_user_id BIGINT UNSIGNED NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_project_milestones_tenant_board (tenant_id, board_id, due_on),
    CONSTRAINT fk_project_milestones_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_project_milestones_board FOREIGN KEY (board_id) REFERENCES project_boards(id) ON DELETE CASCADE,
    CONSTRAINT fk_project_milestones_user FOREIGN KEY (owner_user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO project_tags (tenant_id, name, color)
SELECT t.id, 'Urgente', '#dc2626' FROM tenants t WHERE t.slug = 'demo'
UNION ALL
SELECT t.id, 'Bloqueada', '#f59e0b' FROM tenants t WHERE t.slug = 'demo'
UNION ALL
SELECT t.id, 'Backlog', '#2563eb' FROM tenants t WHERE t.slug = 'demo'
ON DUPLICATE KEY UPDATE color = VALUES(color);

INSERT INTO project_sla_rules (tenant_id, priority, warn_minutes, breach_minutes, is_active)
SELECT t.id, 'high', 180, 360, 1
FROM tenants t
WHERE t.slug = 'demo'
ON DUPLICATE KEY UPDATE warn_minutes = VALUES(warn_minutes), breach_minutes = VALUES(breach_minutes), is_active = VALUES(is_active);

INSERT INTO project_templates (tenant_id, name, description, is_active)
SELECT t.id, 'Implementacion Cliente', 'Plantilla para implementaciones estandar', 1
FROM tenants t
WHERE t.slug = 'demo'
ON DUPLICATE KEY UPDATE description = VALUES(description), is_active = VALUES(is_active);

INSERT INTO project_automation_rules (tenant_id, name, trigger_event, action_type, is_active)
SELECT t.id, 'Auto mover a done por checklist completo', 'task.checklist.completed', 'move_to_closed_column', 1
FROM tenants t
WHERE t.slug = 'demo'
ON DUPLICATE KEY UPDATE is_active = VALUES(is_active);

INSERT INTO project_milestones (tenant_id, board_id, name, due_on, status)
SELECT pb.tenant_id, pb.id, 'Primer hito demo', DATE_ADD(CURDATE(), INTERVAL 14 DAY), 'pending'
FROM project_boards pb
INNER JOIN tenants t ON t.id = pb.tenant_id
WHERE t.slug = 'demo' AND pb.name = 'Proyecto Demo'
ON DUPLICATE KEY UPDATE due_on = VALUES(due_on), status = VALUES(status);

INSERT INTO project_board_permissions (tenant_id, board_id, role, can_view, can_create_task, can_edit_task, can_move_task, can_manage_board)
SELECT pb.tenant_id, pb.id, 'manager', 1, 1, 1, 1, 1
FROM project_boards pb
INNER JOIN tenants t ON t.id = pb.tenant_id
WHERE t.slug = 'demo' AND pb.name = 'Proyecto Demo'
ON DUPLICATE KEY UPDATE can_view = VALUES(can_view), can_create_task = VALUES(can_create_task), can_edit_task = VALUES(can_edit_task), can_move_task = VALUES(can_move_task), can_manage_board = VALUES(can_manage_board);

INSERT INTO project_board_permissions (tenant_id, board_id, role, can_view, can_create_task, can_edit_task, can_move_task, can_manage_board)
SELECT pb.tenant_id, pb.id, 'agent', 1, 1, 1, 1, 0
FROM project_boards pb
INNER JOIN tenants t ON t.id = pb.tenant_id
WHERE t.slug = 'demo' AND pb.name = 'Proyecto Demo'
ON DUPLICATE KEY UPDATE can_view = VALUES(can_view), can_create_task = VALUES(can_create_task), can_edit_task = VALUES(can_edit_task), can_move_task = VALUES(can_move_task), can_manage_board = VALUES(can_manage_board);

INSERT INTO project_board_permissions (tenant_id, board_id, role, can_view, can_create_task, can_edit_task, can_move_task, can_manage_board)
SELECT pb.tenant_id, pb.id, 'viewer', 1, 0, 0, 0, 0
FROM project_boards pb
INNER JOIN tenants t ON t.id = pb.tenant_id
WHERE t.slug = 'demo' AND pb.name = 'Proyecto Demo'
ON DUPLICATE KEY UPDATE can_view = VALUES(can_view), can_create_task = VALUES(can_create_task), can_edit_task = VALUES(can_edit_task), can_move_task = VALUES(can_move_task), can_manage_board = VALUES(can_manage_board);
