ALTER TABLE opportunities
    ADD COLUMN IF NOT EXISTS win_reason_id BIGINT UNSIGNED NULL AFTER loss_reason_id,
    ADD COLUMN IF NOT EXISTS win_note VARCHAR(255) NULL AFTER win_reason_id,
    ADD COLUMN IF NOT EXISTS competitor_id BIGINT UNSIGNED NULL AFTER win_note,
    ADD COLUMN IF NOT EXISTS stage_entered_at DATETIME NULL AFTER competitor_id,
    ADD COLUMN IF NOT EXISTS stage_sla_due_at DATETIME NULL AFTER stage_entered_at,
    ADD COLUMN IF NOT EXISTS dynamic_win_probability TINYINT UNSIGNED NULL AFTER stage_sla_due_at,
    ADD COLUMN IF NOT EXISTS confidence_score TINYINT UNSIGNED NOT NULL DEFAULT 0 AFTER dynamic_win_probability,
    ADD COLUMN IF NOT EXISTS duplicated_of_id BIGINT UNSIGNED NULL AFTER confidence_score;

CREATE TABLE IF NOT EXISTS opportunity_win_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_opp_win_reason_name (tenant_id, name),
    CONSTRAINT fk_opp_win_reason_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE opportunities
    ADD CONSTRAINT fk_opportunities_win_reason FOREIGN KEY (win_reason_id) REFERENCES opportunity_win_reasons(id) ON DELETE SET NULL;

CREATE TABLE IF NOT EXISTS competitors (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(140) NOT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_competitors_name (tenant_id, name),
    CONSTRAINT fk_competitors_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE opportunities
    ADD CONSTRAINT fk_opportunities_competitor FOREIGN KEY (competitor_id) REFERENCES competitors(id) ON DELETE SET NULL;

CREATE TABLE IF NOT EXISTS pipeline_stage_requirements (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    stage_id BIGINT UNSIGNED NOT NULL,
    requirement_key VARCHAR(80) NOT NULL,
    label VARCHAR(140) NOT NULL,
    is_required TINYINT(1) NOT NULL DEFAULT 1,
    sort_order INT UNSIGNED NOT NULL DEFAULT 0,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_stage_requirement (tenant_id, stage_id, requirement_key),
    CONSTRAINT fk_stage_req_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_stage_req_stage FOREIGN KEY (stage_id) REFERENCES pipeline_stages(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS opportunity_stage_checklists (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    opportunity_id BIGINT UNSIGNED NOT NULL,
    stage_id BIGINT UNSIGNED NOT NULL,
    requirement_id BIGINT UNSIGNED NOT NULL,
    is_done TINYINT(1) NOT NULL DEFAULT 0,
    done_at DATETIME NULL,
    done_by BIGINT UNSIGNED NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_opp_stage_check (tenant_id, opportunity_id, stage_id, requirement_id),
    CONSTRAINT fk_opp_stage_check_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_opp_stage_check_opp FOREIGN KEY (opportunity_id) REFERENCES opportunities(id) ON DELETE CASCADE,
    CONSTRAINT fk_opp_stage_check_stage FOREIGN KEY (stage_id) REFERENCES pipeline_stages(id) ON DELETE CASCADE,
    CONSTRAINT fk_opp_stage_check_req FOREIGN KEY (requirement_id) REFERENCES pipeline_stage_requirements(id) ON DELETE CASCADE,
    CONSTRAINT fk_opp_stage_check_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 pipeline_stage_rules (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    stage_id BIGINT UNSIGNED NOT NULL,
    rule_type ENUM('entry', 'exit') NOT NULL DEFAULT 'entry',
    condition_json JSON NOT NULL,
    error_message VARCHAR(190) NOT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_stage_rules_tenant_stage (tenant_id, stage_id, rule_type, is_active),
    CONSTRAINT fk_stage_rules_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_stage_rules_stage FOREIGN KEY (stage_id) REFERENCES pipeline_stages(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pipeline_stage_sla (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    stage_id BIGINT UNSIGNED NOT NULL,
    max_hours INT UNSIGNED NOT NULL DEFAULT 72,
    warn_hours INT UNSIGNED NOT NULL DEFAULT 48,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_stage_sla (tenant_id, stage_id),
    CONSTRAINT fk_stage_sla_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_stage_sla_stage FOREIGN KEY (stage_id) REFERENCES pipeline_stages(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS opportunity_sla_alerts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    opportunity_id BIGINT UNSIGNED NOT NULL,
    stage_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_opp_sla_alerts_tenant (tenant_id, status, created_at),
    CONSTRAINT fk_opp_sla_alerts_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_opp_sla_alerts_opp FOREIGN KEY (opportunity_id) REFERENCES opportunities(id) ON DELETE CASCADE,
    CONSTRAINT fk_opp_sla_alerts_stage FOREIGN KEY (stage_id) REFERENCES pipeline_stages(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS opportunity_value_history (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    opportunity_id BIGINT UNSIGNED NOT NULL,
    changed_by BIGINT UNSIGNED NULL,
    old_amount DECIMAL(12,2) NULL,
    new_amount DECIMAL(12,2) NULL,
    old_forecast_category VARCHAR(40) NULL,
    new_forecast_category VARCHAR(40) NULL,
    changed_at DATETIME NOT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_opp_value_history_tenant_opp (tenant_id, opportunity_id, changed_at),
    CONSTRAINT fk_opp_value_history_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_opp_value_history_opp FOREIGN KEY (opportunity_id) REFERENCES opportunities(id) ON DELETE CASCADE,
    CONSTRAINT fk_opp_value_history_user FOREIGN KEY (changed_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pipeline_stage_permissions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    stage_id BIGINT UNSIGNED NOT NULL,
    role VARCHAR(50) NOT NULL,
    can_enter TINYINT(1) NOT NULL DEFAULT 1,
    can_edit TINYINT(1) NOT NULL DEFAULT 1,
    can_close 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_stage_perm (tenant_id, stage_id, role),
    CONSTRAINT fk_stage_perm_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_stage_perm_stage FOREIGN KEY (stage_id) REFERENCES pipeline_stages(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS opportunity_custom_field_definitions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    field_key VARCHAR(80) NOT NULL,
    label VARCHAR(120) NOT NULL,
    field_type ENUM('text', 'number', 'date', 'boolean', 'json') NOT NULL DEFAULT 'text',
    is_required 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_opp_custom_field_def (tenant_id, field_key),
    CONSTRAINT fk_opp_custom_field_def_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 opportunity_custom_field_values (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    opportunity_id BIGINT UNSIGNED NOT NULL,
    definition_id BIGINT UNSIGNED NOT NULL,
    value_text TEXT NULL,
    value_number DECIMAL(18,4) NULL,
    value_date DATE NULL,
    value_bool TINYINT(1) NULL,
    value_json JSON NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_opp_custom_value (tenant_id, opportunity_id, definition_id),
    CONSTRAINT fk_opp_custom_value_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_opp_custom_value_opp FOREIGN KEY (opportunity_id) REFERENCES opportunities(id) ON DELETE CASCADE,
    CONSTRAINT fk_opp_custom_value_def FOREIGN KEY (definition_id) REFERENCES opportunity_custom_field_definitions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pipeline_stage_playbooks (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    stage_id BIGINT UNSIGNED NOT NULL,
    action_type VARCHAR(80) NOT NULL,
    config_json JSON NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_stage_playbooks_tenant_stage (tenant_id, stage_id, is_active),
    CONSTRAINT fk_stage_playbooks_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_stage_playbooks_stage FOREIGN KEY (stage_id) REFERENCES pipeline_stages(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pipeline_stage_playbook_runs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    opportunity_id BIGINT UNSIGNED NOT NULL,
    stage_id BIGINT UNSIGNED NOT NULL,
    playbook_id BIGINT UNSIGNED NOT NULL,
    status ENUM('success', 'failed') NOT NULL DEFAULT 'success',
    message VARCHAR(255) NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_stage_playbook_runs_tenant (tenant_id, created_at),
    CONSTRAINT fk_stage_playbook_runs_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_stage_playbook_runs_opp FOREIGN KEY (opportunity_id) REFERENCES opportunities(id) ON DELETE CASCADE,
    CONSTRAINT fk_stage_playbook_runs_stage FOREIGN KEY (stage_id) REFERENCES pipeline_stages(id) ON DELETE CASCADE,
    CONSTRAINT fk_stage_playbook_runs_playbook FOREIGN KEY (playbook_id) REFERENCES pipeline_stage_playbooks(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS opportunity_duplicates (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    opportunity_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_opp_duplicates_tenant (tenant_id, status, created_at),
    CONSTRAINT fk_opp_duplicates_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_opp_duplicates_opp FOREIGN KEY (opportunity_id) REFERENCES opportunities(id) ON DELETE CASCADE,
    CONSTRAINT fk_opp_duplicates_dup FOREIGN KEY (duplicate_of_id) REFERENCES opportunities(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pipeline_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,
    won_count INT UNSIGNED NOT NULL DEFAULT 0,
    lost_count INT UNSIGNED NOT NULL DEFAULT 0,
    open_amount DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    weighted_forecast DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    avg_sales_cycle_days DECIMAL(8,2) NOT NULL DEFAULT 0.00,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_pipeline_daily_snapshot (tenant_id, snapshot_date),
    CONSTRAINT fk_pipeline_daily_snapshot_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO opportunity_win_reasons (tenant_id, name, sort_order, is_active)
SELECT t.id, 'Mejor fit funcional', 1, 1 FROM tenants t WHERE t.slug = 'demo'
UNION ALL
SELECT t.id, 'Rapidez de implementacion', 2, 1 FROM tenants t WHERE t.slug = 'demo'
UNION ALL
SELECT t.id, 'Relación precio/valor', 3, 1 FROM tenants t WHERE t.slug = 'demo'
ON DUPLICATE KEY UPDATE sort_order = VALUES(sort_order), is_active = VALUES(is_active);

INSERT INTO competitors (tenant_id, name, is_active)
SELECT t.id, 'Competidor A', 1 FROM tenants t WHERE t.slug = 'demo'
UNION ALL
SELECT t.id, 'Competidor B', 1 FROM tenants t WHERE t.slug = 'demo'
ON DUPLICATE KEY UPDATE is_active = VALUES(is_active);
