ALTER TABLE sales_activities
    ADD COLUMN IF NOT EXISTS cancelled_at DATETIME NULL AFTER completed_at,
    ADD COLUMN IF NOT EXISTS cancel_reason_id BIGINT UNSIGNED NULL AFTER cancelled_at,
    ADD COLUMN IF NOT EXISTS cancel_note VARCHAR(255) NULL AFTER cancel_reason_id,
    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 recurrence_rule_id BIGINT UNSIGNED NULL AFTER account_id,
    ADD COLUMN IF NOT EXISTS source_event VARCHAR(80) NULL AFTER recurrence_rule_id,
    ADD COLUMN IF NOT EXISTS source_ref_id BIGINT UNSIGNED NULL AFTER source_event,
    ADD COLUMN IF NOT EXISTS auto_created TINYINT(1) NOT NULL DEFAULT 0 AFTER source_ref_id,
    ADD COLUMN IF NOT EXISTS workload_score TINYINT UNSIGNED NOT NULL DEFAULT 0 AFTER auto_created;

CREATE TABLE IF NOT EXISTS activity_cancel_reasons (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(140) NOT NULL,
    sort_order INT UNSIGNED NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_activity_cancel_reason_name (tenant_id, name),
    CONSTRAINT fk_activity_cancel_reasons_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 activity_sla_rules (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    type_id BIGINT UNSIGNED NULL,
    priority ENUM('low', 'medium', 'high') NULL,
    warn_minutes INT UNSIGNED NOT NULL DEFAULT 120,
    breach_minutes INT UNSIGNED NOT NULL DEFAULT 240,
    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_activity_sla_rules_tenant (tenant_id, is_active),
    CONSTRAINT fk_activity_sla_rules_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_activity_sla_rules_type FOREIGN KEY (type_id) REFERENCES activity_types(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS activity_sla_alerts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    activity_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_activity_sla_alerts_tenant (tenant_id, status, created_at),
    CONSTRAINT fk_activity_sla_alerts_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_activity_sla_alerts_activity FOREIGN KEY (activity_id) REFERENCES sales_activities(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS activity_recurrence_rules (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    frequency ENUM('daily', 'weekly', 'monthly') NOT NULL DEFAULT 'weekly',
    interval_value TINYINT UNSIGNED NOT NULL DEFAULT 1,
    by_weekday VARCHAR(30) NULL,
    by_month_day TINYINT UNSIGNED NULL,
    starts_on DATE NOT NULL,
    ends_on DATE NULL,
    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_activity_recurrence_tenant (tenant_id, is_active),
    CONSTRAINT fk_activity_recurrence_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 activity_dependencies (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    activity_id BIGINT UNSIGNED NOT NULL,
    depends_on_activity_id BIGINT UNSIGNED NOT NULL,
    dependency_type ENUM('finish_to_start', 'start_to_start') NOT NULL DEFAULT 'finish_to_start',
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_activity_dependency (tenant_id, activity_id, depends_on_activity_id),
    CONSTRAINT fk_activity_dependencies_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_activity_dependencies_activity FOREIGN KEY (activity_id) REFERENCES sales_activities(id) ON DELETE CASCADE,
    CONSTRAINT fk_activity_dependencies_depends FOREIGN KEY (depends_on_activity_id) REFERENCES sales_activities(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS activity_templates (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(140) NOT NULL,
    title_template VARCHAR(190) NOT NULL,
    details_template TEXT NULL,
    default_type_id BIGINT UNSIGNED NULL,
    default_priority ENUM('low', 'medium', 'high') NOT NULL DEFAULT 'medium',
    default_due_hours INT UNSIGNED NULL,
    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,
    UNIQUE KEY uq_activity_templates_name (tenant_id, name),
    CONSTRAINT fk_activity_templates_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_activity_templates_type FOREIGN KEY (default_type_id) REFERENCES activity_types(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS activity_template_stage_bindings (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    stage_id BIGINT UNSIGNED NOT NULL,
    template_id BIGINT UNSIGNED NOT NULL,
    trigger_event ENUM('enter', 'exit') NOT NULL DEFAULT 'enter',
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_activity_template_stage (tenant_id, stage_id, template_id, trigger_event),
    CONSTRAINT fk_activity_template_stage_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_activity_template_stage_stage FOREIGN KEY (stage_id) REFERENCES pipeline_stages(id) ON DELETE CASCADE,
    CONSTRAINT fk_activity_template_stage_template FOREIGN KEY (template_id) REFERENCES activity_templates(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS activity_automation_rules (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(140) NOT NULL,
    trigger_event VARCHAR(80) NOT NULL,
    conditions_json JSON NULL,
    action_template_id BIGINT UNSIGNED NULL,
    assign_strategy ENUM('owner', 'creator', 'least_load', 'fixed_user') NOT NULL DEFAULT 'owner',
    fixed_user_id BIGINT UNSIGNED 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_activity_automation_rules_tenant (tenant_id, is_active),
    CONSTRAINT fk_activity_automation_rules_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_activity_automation_rules_template FOREIGN KEY (action_template_id) REFERENCES activity_templates(id) ON DELETE SET NULL,
    CONSTRAINT fk_activity_automation_rules_user FOREIGN KEY (fixed_user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS activity_reassignment_runs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    strategy ENUM('least_load', 'manual') NOT NULL DEFAULT 'least_load',
    affected_activities INT UNSIGNED NOT NULL DEFAULT 0,
    started_at DATETIME NOT NULL,
    finished_at DATETIME NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_activity_reassign_runs_tenant (tenant_id, created_at),
    CONSTRAINT fk_activity_reassign_runs_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 activity_change_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    activity_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_activity_change_logs_tenant_activity (tenant_id, activity_id, created_at),
    CONSTRAINT fk_activity_change_logs_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_activity_change_logs_activity FOREIGN KEY (activity_id) REFERENCES sales_activities(id) ON DELETE CASCADE,
    CONSTRAINT fk_activity_change_logs_user FOREIGN KEY (actor_user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS activity_comments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    activity_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_activity_comments_activity (tenant_id, activity_id, created_at),
    CONSTRAINT fk_activity_comments_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_activity_comments_activity FOREIGN KEY (activity_id) REFERENCES sales_activities(id) ON DELETE CASCADE,
    CONSTRAINT fk_activity_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 activity_attachments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    activity_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_activity_attachments_activity (tenant_id, activity_id, created_at),
    CONSTRAINT fk_activity_attachments_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_activity_attachments_activity FOREIGN KEY (activity_id) REFERENCES sales_activities(id) ON DELETE CASCADE,
    CONSTRAINT fk_activity_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 activity_webhook_deliveries (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    endpoint_id BIGINT UNSIGNED NOT NULL,
    event_type VARCHAR(80) NOT NULL,
    payload_json JSON NULL,
    status ENUM('pending', 'sent', 'failed') NOT NULL DEFAULT 'pending',
    http_status SMALLINT NULL,
    response_body TEXT NULL,
    attempts TINYINT UNSIGNED NOT NULL DEFAULT 0,
    next_attempt_at DATETIME NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    sent_at DATETIME NULL,
    INDEX idx_activity_webhook_deliveries_tenant (tenant_id, status, created_at),
    CONSTRAINT fk_activity_webhook_deliveries_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_activity_webhook_deliveries_endpoint FOREIGN KEY (endpoint_id) REFERENCES activity_webhook_endpoints(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS activity_daily_snapshots (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    snapshot_date DATE NOT NULL,
    open_count INT UNSIGNED NOT NULL DEFAULT 0,
    overdue_count INT UNSIGNED NOT NULL DEFAULT 0,
    done_count INT UNSIGNED NOT NULL DEFAULT 0,
    sla_breaches INT UNSIGNED NOT NULL DEFAULT 0,
    on_time_rate DECIMAL(5,2) NOT NULL DEFAULT 0.00,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_activity_daily_snapshot (tenant_id, snapshot_date),
    CONSTRAINT fk_activity_daily_snapshots_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 activity_compliance_snapshots (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    period_start DATE NOT NULL,
    period_end DATE NOT NULL,
    assigned_count INT UNSIGNED NOT NULL DEFAULT 0,
    completed_on_time INT UNSIGNED NOT NULL DEFAULT 0,
    completed_late INT UNSIGNED NOT NULL DEFAULT 0,
    compliance_score DECIMAL(5,2) NOT NULL DEFAULT 0.00,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_activity_compliance_period (tenant_id, user_id, period_start, period_end),
    CONSTRAINT fk_activity_compliance_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_activity_compliance_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO activity_cancel_reasons (tenant_id, name, sort_order, is_active)
SELECT t.id, 'Cliente no responde', 1, 1 FROM tenants t WHERE t.slug = 'demo'
UNION ALL
SELECT t.id, 'Cambio de prioridad', 2, 1 FROM tenants t WHERE t.slug = 'demo'
UNION ALL
SELECT t.id, 'Registro duplicado', 3, 1 FROM tenants t WHERE t.slug = 'demo'
ON DUPLICATE KEY UPDATE sort_order = VALUES(sort_order), is_active = VALUES(is_active);
