CREATE TABLE IF NOT EXISTS project_boards (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(160) NOT NULL,
    description TEXT NULL,
    color VARCHAR(20) NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    owner_user_id BIGINT UNSIGNED NULL,
    starts_on DATE NULL,
    due_on DATE NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_project_boards_tenant_active (tenant_id, is_active, created_at),
    UNIQUE KEY uq_project_boards_tenant_name (tenant_id, name),
    CONSTRAINT fk_project_boards_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_project_boards_owner FOREIGN KEY (owner_user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS project_board_members (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    board_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    role ENUM('viewer', 'editor', 'manager') NOT NULL DEFAULT 'viewer',
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_project_board_member (tenant_id, board_id, user_id),
    INDEX idx_project_board_members_tenant_user (tenant_id, user_id),
    CONSTRAINT fk_project_board_members_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_project_board_members_board FOREIGN KEY (board_id) REFERENCES project_boards(id) ON DELETE CASCADE,
    CONSTRAINT fk_project_board_members_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_columns (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    board_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,
    wip_limit INT UNSIGNED NULL,
    sort_order INT UNSIGNED 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_columns_code (tenant_id, board_id, code),
    UNIQUE KEY uq_project_columns_name (tenant_id, board_id, name),
    INDEX idx_project_columns_tenant_board (tenant_id, board_id, sort_order),
    CONSTRAINT fk_project_columns_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_project_columns_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_tasks (
    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,
    parent_task_id BIGINT UNSIGNED NULL,
    title VARCHAR(190) NOT NULL,
    details TEXT NULL,
    priority ENUM('low', 'medium', 'high', 'critical') NOT NULL DEFAULT 'medium',
    status ENUM('open', 'in_progress', 'blocked', 'done', 'cancelled') NOT NULL DEFAULT 'open',
    starts_at DATETIME NULL,
    due_at DATETIME NULL,
    completed_at DATETIME NULL,
    sort_order INT UNSIGNED NOT NULL DEFAULT 0,
    progress_percent TINYINT UNSIGNED NOT NULL DEFAULT 0,
    estimate_hours DECIMAL(8,2) NULL,
    spent_hours DECIMAL(8,2) NULL,
    reporter_user_id BIGINT UNSIGNED NULL,
    account_id BIGINT UNSIGNED NULL,
    contact_id BIGINT UNSIGNED NULL,
    opportunity_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_tasks_tenant_board_column (tenant_id, board_id, column_id, sort_order),
    INDEX idx_project_tasks_tenant_status_due (tenant_id, status, due_at),
    INDEX idx_project_tasks_tenant_parent (tenant_id, parent_task_id),
    CONSTRAINT fk_project_tasks_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_project_tasks_board FOREIGN KEY (board_id) REFERENCES project_boards(id) ON DELETE CASCADE,
    CONSTRAINT fk_project_tasks_column FOREIGN KEY (column_id) REFERENCES project_columns(id) ON DELETE CASCADE,
    CONSTRAINT fk_project_tasks_parent FOREIGN KEY (parent_task_id) REFERENCES project_tasks(id) ON DELETE CASCADE,
    CONSTRAINT fk_project_tasks_reporter FOREIGN KEY (reporter_user_id) REFERENCES users(id) ON DELETE SET NULL,
    CONSTRAINT fk_project_tasks_account FOREIGN KEY (account_id) REFERENCES accounts(id) ON DELETE SET NULL,
    CONSTRAINT fk_project_tasks_contact FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE SET NULL,
    CONSTRAINT fk_project_tasks_opportunity FOREIGN KEY (opportunity_id) REFERENCES opportunities(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS project_task_assignees (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    task_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    is_primary TINYINT(1) NOT NULL DEFAULT 0,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_project_task_assignee (tenant_id, task_id, user_id),
    INDEX idx_project_task_assignees_tenant_user (tenant_id, user_id, is_primary),
    CONSTRAINT fk_project_task_assignees_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_project_task_assignees_task FOREIGN KEY (task_id) REFERENCES project_tasks(id) ON DELETE CASCADE,
    CONSTRAINT fk_project_task_assignees_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_task_subtasks (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    task_id BIGINT UNSIGNED NOT NULL,
    title VARCHAR(190) NOT NULL,
    status ENUM('open', 'in_progress', 'done', 'cancelled') NOT NULL DEFAULT 'open',
    assigned_to BIGINT UNSIGNED NULL,
    due_at DATETIME NULL,
    sort_order INT UNSIGNED NOT NULL DEFAULT 0,
    completed_at DATETIME NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_project_task_subtasks_tenant_task (tenant_id, task_id, sort_order),
    CONSTRAINT fk_project_task_subtasks_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_project_task_subtasks_task FOREIGN KEY (task_id) REFERENCES project_tasks(id) ON DELETE CASCADE,
    CONSTRAINT fk_project_task_subtasks_user FOREIGN KEY (assigned_to) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS project_task_comments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    task_id BIGINT UNSIGNED NOT NULL,
    content TEXT NOT NULL,
    mentions_json JSON NULL,
    created_by BIGINT UNSIGNED NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_project_task_comments_tenant_task (tenant_id, task_id, created_at),
    CONSTRAINT fk_project_task_comments_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_project_task_comments_task FOREIGN KEY (task_id) REFERENCES project_tasks(id) ON DELETE CASCADE,
    CONSTRAINT fk_project_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 IF NOT EXISTS project_task_history (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    task_id BIGINT UNSIGNED NOT NULL,
    actor_user_id BIGINT UNSIGNED NULL,
    change_type VARCHAR(80) NOT NULL,
    old_value JSON NULL,
    new_value JSON NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_project_task_history_tenant_task (tenant_id, task_id, created_at),
    CONSTRAINT fk_project_task_history_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_project_task_history_task FOREIGN KEY (task_id) REFERENCES project_tasks(id) ON DELETE CASCADE,
    CONSTRAINT fk_project_task_history_user FOREIGN KEY (actor_user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO project_boards (tenant_id, name, description, color, is_active)
SELECT t.id, 'Proyecto Demo', 'Tablero inicial para operacion colaborativa', '#2563eb', 1
FROM tenants t
WHERE t.slug = 'demo'
ON DUPLICATE KEY UPDATE description = VALUES(description), color = VALUES(color), is_active = VALUES(is_active);

INSERT INTO project_columns (tenant_id, board_id, code, name, color, is_closed_stage, sort_order)
SELECT pb.tenant_id, pb.id, 'todo', 'Por hacer', '#94a3b8', 0, 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 name = VALUES(name), color = VALUES(color), sort_order = VALUES(sort_order);

INSERT INTO project_columns (tenant_id, board_id, code, name, color, is_closed_stage, sort_order)
SELECT pb.tenant_id, pb.id, 'doing', 'En curso', '#2563eb', 0, 2
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 name = VALUES(name), color = VALUES(color), sort_order = VALUES(sort_order);

INSERT INTO project_columns (tenant_id, board_id, code, name, color, is_closed_stage, sort_order)
SELECT pb.tenant_id, pb.id, 'review', 'En revision', '#f59e0b', 0, 3
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 name = VALUES(name), color = VALUES(color), sort_order = VALUES(sort_order);

INSERT INTO project_columns (tenant_id, board_id, code, name, color, is_closed_stage, sort_order)
SELECT pb.tenant_id, pb.id, 'done', 'Hecho', '#16a34a', 1, 4
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 name = VALUES(name), color = VALUES(color), is_closed_stage = VALUES(is_closed_stage), sort_order = VALUES(sort_order);
