ALTER TABLE opportunities
    ADD COLUMN IF NOT EXISTS sort_order INT UNSIGNED NOT NULL DEFAULT 0 AFTER stage_id;

UPDATE opportunities
SET sort_order = id
WHERE sort_order = 0;

CREATE TABLE IF NOT EXISTS opportunity_stage_history (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    opportunity_id BIGINT UNSIGNED NOT NULL,
    from_stage_id BIGINT UNSIGNED NULL,
    to_stage_id BIGINT UNSIGNED NOT NULL,
    moved_by BIGINT UNSIGNED NULL,
    moved_at DATETIME NOT NULL,
    note VARCHAR(255) NULL,
    INDEX idx_opp_stage_history_tenant_opp (tenant_id, opportunity_id, moved_at),
    CONSTRAINT fk_opp_history_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_opp_history_opportunity FOREIGN KEY (opportunity_id) REFERENCES opportunities(id) ON DELETE CASCADE,
    CONSTRAINT fk_opp_history_from_stage FOREIGN KEY (from_stage_id) REFERENCES pipeline_stages(id) ON DELETE SET NULL,
    CONSTRAINT fk_opp_history_to_stage FOREIGN KEY (to_stage_id) REFERENCES pipeline_stages(id) ON DELETE CASCADE,
    CONSTRAINT fk_opp_history_user FOREIGN KEY (moved_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
