-- Module 9 closure hardening (15 improvements)
-- Safe to run after 020/021. Designed to be idempotent.

-- 1) Board/column logical archive support
ALTER TABLE project_boards
    ADD COLUMN IF NOT EXISTS is_archived TINYINT(1) NOT NULL DEFAULT 0 AFTER is_active,
    ADD COLUMN IF NOT EXISTS archived_at DATETIME NULL AFTER is_archived;

ALTER TABLE project_columns
    ADD COLUMN IF NOT EXISTS is_archived TINYINT(1) NOT NULL DEFAULT 0 AFTER is_closed_stage,
    ADD COLUMN IF NOT EXISTS archived_at DATETIME NULL AFTER is_archived;

-- 2) Task lifecycle timestamps and stronger operational metadata
ALTER TABLE project_tasks
    ADD COLUMN IF NOT EXISTS created_by BIGINT UNSIGNED NULL AFTER reporter_user_id,
    ADD COLUMN IF NOT EXISTS started_work_at DATETIME NULL AFTER starts_at,
    ADD COLUMN IF NOT EXISTS first_moved_at DATETIME NULL AFTER started_work_at,
    ADD COLUMN IF NOT EXISTS last_moved_at DATETIME NULL AFTER first_moved_at,
    ADD COLUMN IF NOT EXISTS done_by BIGINT UNSIGNED NULL AFTER completed_at;

-- 3) High-value indexes for board/workload/sla screens
SET @idx_exists := (
    SELECT COUNT(*)
    FROM information_schema.statistics
    WHERE table_schema = DATABASE()
      AND table_name = 'project_tasks'
      AND index_name = 'idx_project_tasks_board_status_due'
);
SET @idx_sql := IF(
    @idx_exists = 0,
    'ALTER TABLE project_tasks ADD INDEX idx_project_tasks_board_status_due (tenant_id, board_id, status, due_at)',
    'SELECT 1'
);
PREPARE stmt FROM @idx_sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @idx_exists := (
    SELECT COUNT(*)
    FROM information_schema.statistics
    WHERE table_schema = DATABASE()
      AND table_name = 'project_tasks'
      AND index_name = 'idx_project_tasks_column_status_sort'
);
SET @idx_sql := IF(
    @idx_exists = 0,
    'ALTER TABLE project_tasks ADD INDEX idx_project_tasks_column_status_sort (tenant_id, board_id, column_id, status, sort_order)',
    'SELECT 1'
);
PREPARE stmt FROM @idx_sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @idx_exists := (
    SELECT COUNT(*)
    FROM information_schema.statistics
    WHERE table_schema = DATABASE()
      AND table_name = 'project_task_assignees'
      AND index_name = 'idx_project_task_assignees_task_primary'
);
SET @idx_sql := IF(
    @idx_exists = 0,
    'ALTER TABLE project_task_assignees ADD INDEX idx_project_task_assignees_task_primary (tenant_id, task_id, is_primary, user_id)',
    'SELECT 1'
);
PREPARE stmt FROM @idx_sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 4) Enforce single primary assignee per task (DB-level)
ALTER TABLE project_task_assignees
    ADD COLUMN IF NOT EXISTS primary_task_ref BIGINT UNSIGNED
        GENERATED ALWAYS AS (CASE WHEN is_primary = 1 THEN task_id ELSE NULL END) STORED;

SET @idx_exists := (
    SELECT COUNT(*)
    FROM information_schema.statistics
    WHERE table_schema = DATABASE()
      AND table_name = 'project_task_assignees'
      AND index_name = 'uq_project_task_primary_assignee'
);
SET @idx_sql := IF(
    @idx_exists = 0,
    'ALTER TABLE project_task_assignees ADD UNIQUE KEY uq_project_task_primary_assignee (tenant_id, primary_task_ref)',
    'SELECT 1'
);
PREPARE stmt FROM @idx_sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 5) Column/status governance map for consistent workflow state
CREATE TABLE IF NOT EXISTS project_column_status_rules (
    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,
    allowed_status ENUM('open', 'in_progress', 'blocked', 'done', 'cancelled') NOT NULL,
    is_default TINYINT(1) NOT NULL DEFAULT 0,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_project_column_status_rule (tenant_id, column_id, allowed_status),
    INDEX idx_project_column_status_rules_tenant_board (tenant_id, board_id, column_id),
    CONSTRAINT fk_project_column_status_rules_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_project_column_status_rules_board FOREIGN KEY (board_id) REFERENCES project_boards(id) ON DELETE CASCADE,
    CONSTRAINT fk_project_column_status_rules_column FOREIGN KEY (column_id) REFERENCES project_columns(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 6) Dependency integrity hardening
SET @chk_exists := (
    SELECT COUNT(*)
    FROM information_schema.table_constraints
    WHERE table_schema = DATABASE()
      AND table_name = 'project_task_dependencies'
      AND constraint_type = 'CHECK'
      AND constraint_name = 'chk_project_task_dependencies_not_self'
);
SET @chk_sql := IF(
    @chk_exists = 0,
    'ALTER TABLE project_task_dependencies ADD CONSTRAINT chk_project_task_dependencies_not_self CHECK (task_id <> depends_on_task_id)',
    'SELECT 1'
);
PREPARE stmt FROM @chk_sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @idx_exists := (
    SELECT COUNT(*)
    FROM information_schema.statistics
    WHERE table_schema = DATABASE()
      AND table_name = 'project_task_dependencies'
      AND index_name = 'idx_project_task_dependencies_reverse'
);
SET @idx_sql := IF(
    @idx_exists = 0,
    'ALTER TABLE project_task_dependencies ADD INDEX idx_project_task_dependencies_reverse (tenant_id, depends_on_task_id)',
    'SELECT 1'
);
PREPARE stmt FROM @idx_sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 7) Movement history (column/status transitions)
CREATE TABLE IF NOT EXISTS project_task_movements (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    task_id BIGINT UNSIGNED NOT NULL,
    from_column_id BIGINT UNSIGNED NULL,
    to_column_id BIGINT UNSIGNED NULL,
    from_status ENUM('open', 'in_progress', 'blocked', 'done', 'cancelled') NULL,
    to_status ENUM('open', 'in_progress', 'blocked', 'done', 'cancelled') NULL,
    moved_by BIGINT UNSIGNED NULL,
    moved_at DATETIME NOT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_project_task_movements_tenant_task (tenant_id, task_id, moved_at),
    CONSTRAINT fk_project_task_movements_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_project_task_movements_task FOREIGN KEY (task_id) REFERENCES project_tasks(id) ON DELETE CASCADE,
    CONSTRAINT fk_project_task_movements_from_column FOREIGN KEY (from_column_id) REFERENCES project_columns(id) ON DELETE SET NULL,
    CONSTRAINT fk_project_task_movements_to_column FOREIGN KEY (to_column_id) REFERENCES project_columns(id) ON DELETE SET NULL,
    CONSTRAINT fk_project_task_movements_user FOREIGN KEY (moved_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 8) Stronger SLA tracking on tasks
ALTER TABLE project_tasks
    ADD COLUMN IF NOT EXISTS sla_warning_at DATETIME NULL AFTER sla_due_at,
    ADD COLUMN IF NOT EXISTS sla_breached_at DATETIME NULL AFTER sla_warning_at,
    ADD COLUMN IF NOT EXISTS sla_resolved_at DATETIME NULL AFTER sla_breached_at;

SET @idx_exists := (
    SELECT COUNT(*)
    FROM information_schema.statistics
    WHERE table_schema = DATABASE()
      AND table_name = 'project_tasks'
      AND index_name = 'idx_project_tasks_sla_monitor'
);
SET @idx_sql := IF(
    @idx_exists = 0,
    'ALTER TABLE project_tasks ADD INDEX idx_project_tasks_sla_monitor (tenant_id, status, sla_due_at)',
    'SELECT 1'
);
PREPARE stmt FROM @idx_sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- 9) Internal notifications queue (mentions/SLA/WIP/etc.)
CREATE TABLE IF NOT EXISTS project_notifications (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    recipient_user_id BIGINT UNSIGNED NOT NULL,
    notification_type VARCHAR(80) NOT NULL,
    entity_type VARCHAR(60) NOT NULL,
    entity_id BIGINT UNSIGNED NOT NULL,
    payload_json JSON NULL,
    status ENUM('pending', 'sent', 'read') NOT NULL DEFAULT 'pending',
    sent_at DATETIME NULL,
    read_at DATETIME NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_project_notifications_tenant_user (tenant_id, recipient_user_id, status, created_at),
    CONSTRAINT fk_project_notifications_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_project_notifications_user FOREIGN KEY (recipient_user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 10) Template versioning for backward-compatible rollouts
ALTER TABLE project_templates
    ADD COLUMN IF NOT EXISTS version INT UNSIGNED NOT NULL DEFAULT 1 AFTER name,
    ADD COLUMN IF NOT EXISTS parent_template_id BIGINT UNSIGNED NULL AFTER version;

SET @fk_exists := (
    SELECT COUNT(*)
    FROM information_schema.table_constraints
    WHERE table_schema = DATABASE()
      AND table_name = 'project_templates'
      AND constraint_name = 'fk_project_templates_parent'
);
SET @fk_sql := IF(
    @fk_exists = 0,
    'ALTER TABLE project_templates ADD CONSTRAINT fk_project_templates_parent FOREIGN KEY (parent_template_id) REFERENCES project_templates(id) ON DELETE SET NULL',
    'SELECT 1'
);
PREPARE stmt FROM @fk_sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

CREATE TABLE IF NOT EXISTS project_template_versions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    template_id BIGINT UNSIGNED NOT NULL,
    version INT UNSIGNED NOT NULL,
    snapshot_json JSON NOT NULL,
    created_by BIGINT UNSIGNED NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_project_template_versions (tenant_id, template_id, version),
    CONSTRAINT fk_project_template_versions_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_project_template_versions_template FOREIGN KEY (template_id) REFERENCES project_templates(id) ON DELETE CASCADE,
    CONSTRAINT fk_project_template_versions_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 11) Watchers/collaboration subscriptions
CREATE TABLE IF NOT EXISTS project_task_watchers (
    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,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_project_task_watcher (tenant_id, task_id, user_id),
    INDEX idx_project_task_watchers_user (tenant_id, user_id),
    CONSTRAINT fk_project_task_watchers_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_project_task_watchers_task FOREIGN KEY (task_id) REFERENCES project_tasks(id) ON DELETE CASCADE,
    CONSTRAINT fk_project_task_watchers_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 12) Fine-grained board permissions
CREATE TABLE IF NOT EXISTS project_board_permission_actions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    board_id BIGINT UNSIGNED NOT NULL,
    role VARCHAR(50) NOT NULL,
    action_key VARCHAR(80) NOT NULL,
    is_allowed TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_project_board_permission_action (tenant_id, board_id, role, action_key),
    INDEX idx_project_board_permission_actions_tenant (tenant_id, board_id, role),
    CONSTRAINT fk_project_board_permission_actions_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_project_board_permission_actions_board FOREIGN KEY (board_id) REFERENCES project_boards(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 13) Advanced analytics snapshots (aging + flow metrics)
CREATE TABLE IF NOT EXISTS project_analytics_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,
    avg_task_age_days DECIMAL(8,2) NOT NULL DEFAULT 0.00,
    p50_cycle_time_days DECIMAL(8,2) NOT NULL DEFAULT 0.00,
    p90_cycle_time_days DECIMAL(8,2) NOT NULL DEFAULT 0.00,
    throughput_7d INT UNSIGNED NOT NULL DEFAULT 0,
    throughput_30d INT UNSIGNED NOT NULL DEFAULT 0,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_project_analytics_snapshot (tenant_id, board_id, snapshot_date),
    CONSTRAINT fk_project_analytics_snapshots_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_project_analytics_snapshots_board FOREIGN KEY (board_id) REFERENCES project_boards(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 14) Ops scheduler registry for jobs (SLA/snapshots/duplicates/WIP)
CREATE TABLE IF NOT EXISTS project_job_schedules (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    job_key VARCHAR(80) NOT NULL,
    cron_expr VARCHAR(80) NOT NULL,
    timezone VARCHAR(60) NOT NULL DEFAULT 'UTC',
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    last_run_at DATETIME NULL,
    next_run_at DATETIME NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_project_job_schedule (tenant_id, job_key),
    CONSTRAINT fk_project_job_schedules_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_job_runs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    schedule_id BIGINT UNSIGNED NULL,
    job_key VARCHAR(80) NOT NULL,
    status ENUM('success', 'failed', 'skipped') NOT NULL DEFAULT 'success',
    message VARCHAR(255) NULL,
    started_at DATETIME NOT NULL,
    finished_at DATETIME NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_project_job_runs_tenant (tenant_id, job_key, created_at),
    CONSTRAINT fk_project_job_runs_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_project_job_runs_schedule FOREIGN KEY (schedule_id) REFERENCES project_job_schedules(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 15) Data quality checks and enforceable checks table
CREATE TABLE IF NOT EXISTS project_data_quality_checks (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    check_key VARCHAR(80) NOT NULL,
    severity ENUM('low', 'medium', 'high') NOT NULL DEFAULT 'medium',
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    config_json JSON NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_project_data_quality_check (tenant_id, check_key),
    CONSTRAINT fk_project_data_quality_checks_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_data_quality_issues (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    check_id BIGINT UNSIGNED NOT NULL,
    task_id BIGINT UNSIGNED NULL,
    issue_key VARCHAR(120) NOT NULL,
    issue_detail VARCHAR(255) NULL,
    status ENUM('open', 'resolved', 'ignored') NOT NULL DEFAULT 'open',
    detected_at DATETIME NOT NULL,
    resolved_at DATETIME NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_project_data_quality_issues_tenant (tenant_id, status, detected_at),
    CONSTRAINT fk_project_data_quality_issues_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_project_data_quality_issues_check FOREIGN KEY (check_id) REFERENCES project_data_quality_checks(id) ON DELETE CASCADE,
    CONSTRAINT fk_project_data_quality_issues_task FOREIGN KEY (task_id) REFERENCES project_tasks(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Seed defaults for demo tenant
INSERT INTO project_job_schedules (tenant_id, job_key, cron_expr, timezone, is_active)
SELECT t.id, 'project.sla.monitor', '*/15 * * * *', 'UTC', 1
FROM tenants t
WHERE t.slug = 'demo'
ON DUPLICATE KEY UPDATE cron_expr = VALUES(cron_expr), timezone = VALUES(timezone), is_active = VALUES(is_active);

INSERT INTO project_job_schedules (tenant_id, job_key, cron_expr, timezone, is_active)
SELECT t.id, 'project.snapshots.daily', '5 0 * * *', 'UTC', 1
FROM tenants t
WHERE t.slug = 'demo'
ON DUPLICATE KEY UPDATE cron_expr = VALUES(cron_expr), timezone = VALUES(timezone), is_active = VALUES(is_active);

INSERT INTO project_job_schedules (tenant_id, job_key, cron_expr, timezone, is_active)
SELECT t.id, 'project.duplicates.scan', '0 * * * *', 'UTC', 1
FROM tenants t
WHERE t.slug = 'demo'
ON DUPLICATE KEY UPDATE cron_expr = VALUES(cron_expr), timezone = VALUES(timezone), is_active = VALUES(is_active);

INSERT INTO project_data_quality_checks (tenant_id, check_key, severity, is_active, config_json)
SELECT t.id, 'task.without_assignee', 'high', 1, JSON_OBJECT('max_unassigned_hours', 24)
FROM tenants t
WHERE t.slug = 'demo'
ON DUPLICATE KEY UPDATE severity = VALUES(severity), is_active = VALUES(is_active), config_json = VALUES(config_json);

INSERT INTO project_data_quality_checks (tenant_id, check_key, severity, is_active, config_json)
SELECT t.id, 'task.overdue.open', 'high', 1, JSON_OBJECT('include_status', JSON_ARRAY('open', 'in_progress', 'blocked'))
FROM tenants t
WHERE t.slug = 'demo'
ON DUPLICATE KEY UPDATE severity = VALUES(severity), is_active = VALUES(is_active), config_json = VALUES(config_json);

INSERT INTO project_data_quality_checks (tenant_id, check_key, severity, is_active, config_json)
SELECT t.id, 'task.no_estimate', 'medium', 1, JSON_OBJECT('priority_min', 'high')
FROM tenants t
WHERE t.slug = 'demo'
ON DUPLICATE KEY UPDATE severity = VALUES(severity), is_active = VALUES(is_active), config_json = VALUES(config_json);
