CREATE TABLE IF NOT EXISTS opportunity_loss_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,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_opp_loss_reason_name (tenant_id, name),
    INDEX idx_opp_loss_reason_tenant_active (tenant_id, is_active, sort_order),
    CONSTRAINT fk_opp_loss_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 COLUMN IF NOT EXISTS loss_reason_id BIGINT UNSIGNED NULL AFTER status,
    ADD COLUMN IF NOT EXISTS loss_note VARCHAR(255) NULL AFTER loss_reason_id,
    ADD COLUMN IF NOT EXISTS closed_at DATETIME NULL AFTER loss_note,
    ADD COLUMN IF NOT EXISTS forecast_category ENUM('pipeline', 'best_case', 'commit', 'omitted') NOT NULL DEFAULT 'pipeline' AFTER closed_at,
    ADD CONSTRAINT fk_opportunities_loss_reason FOREIGN KEY (loss_reason_id) REFERENCES opportunity_loss_reasons(id) ON DELETE SET NULL;

INSERT INTO opportunity_loss_reasons (tenant_id, name, sort_order, is_active)
SELECT t.id, 'Precio', 1, 1 FROM tenants t WHERE t.slug = 'demo'
UNION ALL
SELECT t.id, 'Sin presupuesto', 2, 1 FROM tenants t WHERE t.slug = 'demo'
UNION ALL
SELECT t.id, 'Competencia', 3, 1 FROM tenants t WHERE t.slug = 'demo'
UNION ALL
SELECT t.id, 'Sin decision', 4, 1 FROM tenants t WHERE t.slug = 'demo'
ON DUPLICATE KEY UPDATE sort_order = VALUES(sort_order), is_active = VALUES(is_active);
