postgres-setup.sql

-- ========================================
-- Schema Setup for 'products' Table (CDC)
-- ========================================

-- Create table 'products' if it does not exist
CREATE TABLE IF NOT EXISTS products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    price NUMERIC(10, 2),
    is_active BOOLEAN,
    created TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Add UNIQUE constraint on 'name' column if it doesn't already exist
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint WHERE conname = 'products_name_unique'
    ) THEN
        ALTER TABLE products
        ADD CONSTRAINT products_name_unique UNIQUE (name);
    END IF;
END $$;

-- Insert sample data with ON CONFLICT DO NOTHING to avoid duplicates
INSERT INTO products (name, description, price, is_active) VALUES
    ('Product A', 'Description A', 10.00, TRUE),
    ('Product B', 'Description B', 20.00, FALSE)
ON CONFLICT (name) DO NOTHING;

-- Create publication for Debezium CDC if it doesn't already exist
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_publication WHERE pubname = 'dbz_publication'
    ) THEN
        CREATE PUBLICATION dbz_publication FOR TABLE products;
    END IF;
END $$;