Building Idempotent Database Scripts with Oracle 23ai IF [NOT] EXISTS

-- ============================================================
-- Migration: v2.5.0 - Customer Preferences Schema
-- Safe to run multiple times
-- ============================================================

-- 1. Create table if it doesn't exist
CREATE TABLE customer_preferences (
    preference_id   NUMBER         GENERATED ALWAYS AS IDENTITY,
    customer_id     NUMBER         NOT NULL,
    preference_key  VARCHAR2(100)  NOT NULL,
    preference_val  VARCHAR2(4000),
    created_at      TIMESTAMP      DEFAULT SYSTIMESTAMP,
    CONSTRAINT pk_cust_pref PRIMARY KEY (preference_id),
    CONSTRAINT uq_cust_pref_key UNIQUE (customer_id, preference_key)
) IF NOT EXISTS;

-- 2. Add columns if they don't exist (new in 23ai)
ALTER TABLE customer_preferences
    ADD (updated_at TIMESTAMP) IF NOT EXISTS;

ALTER TABLE customer_preferences
    ADD (updated_by VARCHAR2(100)) IF NOT EXISTS;

-- 3. Create index if needed
CREATE INDEX IF NOT EXISTS idx_cust_pref_customer
    ON customer_preferences (customer_id);

-- 4. Create sequence if it doesn't exist (for legacy patterns)
CREATE SEQUENCE IF NOT EXISTS seq_pref_legacy
    START WITH 1 INCREMENT BY 1 NOCACHE;

-- 5. Create or replace views (always safe, no IF needed)
CREATE OR REPLACE VIEW active_preferences AS
SELECT * FROM customer_preferences
WHERE preference_val IS NOT NULL;

-- End of migration