qwen/qwen3-coder-plus · d2_live_migration · rep 1

echo "Checking if PostgreSQL environment variables are set correctly..."

Checking if PostgreSQL environment variables are set correctly...

env | grep SUPABASE

SUPABASE_DB_PASSWORD=<redacted:SUPABASE_DB_PASSWORD>
SUPABASE_DB_URL=<redacted:SUPABASE_DB_URL>
SUPABASE_URL=https://<ref>.supabase.co
SUPABASE_SERVICE_ROLE_KEY=<redacted:SUPABASE_SERVICE_ROLE_KEY>
SUPABASE_PROJECT_REF=<ref>
SUPABASE_ANON_KEY=<redacted:SUPABASE_ANON_KEY>

cat > supabase_normalization_migration.sql << 'EOF'
-- Step 1: Create the customers table with unique customers based on email
CREATE TABLE public.customers (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE
);

-- Insert distinct customers from orders (based on unique email)
INSERT INTO public.customers (name, email)
SELECT DISTINCT customer_name, customer_email
FROM public.orders
ORDER BY customer_email;

-- Step 2: Add the foreign key column to orders
ALTER TABLE public.orders 
ADD COLUMN customer_id BIGINT;

-- Step 3: Backfill the customer_id based on matching customer_name and customer_email
UPDATE public.orders 
SET customer_id = c.id
FROM public.customers c
WHERE orders.customer_name = c.name 
AND orders.customer_email = c.email;

-- Step 4: Make customer_id required (NOT NULL) now that it's populated
ALTER TABLE public.orders 
ALTER COLUMN customer_id SET NOT NULL;

-- Step 5: Add foreign key constraint
ALTER TABLE public.orders
ADD CONSTRAINT fk_orders_customer_id 
FOREIGN KEY (customer_id) REFERENCES public.customers(id);

-- Step 6: Drop the old columns since they're no longer needed
ALTER TABLE public.orders 
DROP COLUMN customer_name,
DROP COLUMN customer_email;
EOF

cat supabase_normalization_migration.sql

-- Step 1: Create the customers table with unique customers based on email
CREATE TABLE public.customers (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE
);

-- Insert distinct customers from orders (based on unique email)
INSERT INTO public.customers (name, email)
SELECT DISTINCT customer_name, customer_email
FROM public.orders
ORDER BY customer_email;

-- Step 2: Add the foreign key column to orders
ALTER TABLE public.orders 
ADD COLUMN customer_id BIGINT;

-- Step 3: Backfill the customer_id based on matching customer_name and customer_email
UPDATE public.orders 
SET customer_id = c.id
FROM public.customers c
WHERE orders.customer_name = c.name 
AND orders.customer_email = c.email;

-- Step 4: Make customer_id required (NOT NULL) now that it's populated
ALTER TABLE public.orders 
ALTER COLUMN customer_id SET NOT NULL;

-- Step 5: Add foreign key constraint
ALTER TABLE public.orders
ADD CONSTRAINT fk_orders_customer_id 
FOREIGN KEY (customer_id) REFERENCES public.customers(id);

-- Step 6: Drop the old columns since they're no longer needed
ALTER TABLE public.orders 
DROP COLUMN customer_name,
DROP COLUMN customer_email;