-- Database Schema Fix Script
-- Run this as the database owner (songlyric_app) or postgres superuser
-- psql -h 192.168.10.130 -U songlyric_app -d church_songlyric -f fix_schema.sql

\echo '============================================================'
\echo 'DATABASE SCHEMA FIX SCRIPT'
\echo '============================================================'

-- Add missing indexes on songs table
\echo ''
\echo '📊 Adding indexes on songs table...'
CREATE INDEX IF NOT EXISTS idx_song_title ON songs(title);
CREATE INDEX IF NOT EXISTS idx_song_artist ON songs(artist);
CREATE INDEX IF NOT EXISTS idx_song_band ON songs(band);
\echo '  ✅ Song indexes created'

-- Add missing indexes on plans table
\echo ''
\echo '📊 Adding indexes on plans table...'
CREATE INDEX IF NOT EXISTS idx_plan_date ON plans(date);
CREATE INDEX IF NOT EXISTS idx_plan_profile ON plans(profile_id);
\echo '  ✅ Plan indexes created'

-- Add missing index on profiles table
\echo ''
\echo '📊 Adding index on profiles table...'
CREATE INDEX IF NOT EXISTS idx_profile_name ON profiles(name);
\echo '  ✅ Profile index created'

-- Fix plans.date to NOT NULL
\echo ''
\echo '🔧 Fixing plans.date constraint...'
UPDATE plans SET date = '2025-01-01' WHERE date IS NULL;
ALTER TABLE plans ALTER COLUMN date SET NOT NULL;
\echo '  ✅ plans.date is now NOT NULL'

-- Fix profiles.name to NOT NULL
\echo ''
\echo '🔧 Fixing profiles.name constraint...'
UPDATE profiles SET name = 'Unnamed' WHERE name IS NULL OR name = '';
ALTER TABLE profiles ALTER COLUMN name SET NOT NULL;
\echo '  ✅ profiles.name is now NOT NULL'

-- Add unique constraint on plan_songs (drop if exists first to avoid error)
\echo ''
\echo '🔧 Adding unique constraint on plan_songs...'
DO $$ 
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint 
        WHERE conname = 'uq_plan_song'
    ) THEN
        ALTER TABLE plan_songs ADD CONSTRAINT uq_plan_song UNIQUE (plan_id, song_id);
        RAISE NOTICE '  ✅ uq_plan_song constraint created';
    ELSE
        RAISE NOTICE '  ✅ uq_plan_song constraint already exists';
    END IF;
END $$;

-- Add order index on plan_songs
\echo ''
\echo '📊 Adding order index on plan_songs...'
CREATE INDEX IF NOT EXISTS idx_plan_songs_order ON plan_songs(plan_id, order_index);
\echo '  ✅ idx_plan_songs_order created'

\echo ''
\echo '============================================================'
\echo '✅ DATABASE SCHEMA FIX COMPLETE!'
\echo '============================================================'
\echo ''
\echo 'Summary of changes:'
\echo '  • Added indexes on songs (title, artist, band)'
\echo '  • Added indexes on plans (date, profile_id)'
\echo '  • Added index on profiles (name)'
\echo '  • Fixed plans.date to NOT NULL'
\echo '  • Fixed profiles.name to NOT NULL'
\echo '  • Added unique constraint on plan_songs'
\echo '  • Added order index on plan_songs'
\echo ''

-- Show final schema
\echo 'Verification - Song indexes:'
\d songs

\echo ''
\echo 'Verification - Plan indexes:'
\d plans

\echo ''
\echo 'Verification - Plan Songs:'
\d plan_songs