-- filepath: /var/www/html/emerald/assets/tables/sql/alter_question_pools_add_owner.sql
BEGIN;

-- Add owner_id column if not exists
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns
        WHERE table_schema='public' AND table_name='question_pools' AND column_name='owner_id'
    ) THEN
        ALTER TABLE question_pools ADD COLUMN owner_id VARCHAR(32);
    END IF;
END$$;

-- If pool_owners mapping table exists, copy values
DO $$
BEGIN
    IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema='public' AND table_name='pool_owners') THEN
        UPDATE question_pools qp
        SET owner_id = po.owner_id
        FROM pool_owners po
        WHERE qp.pool_id = po.pool_id AND (qp.owner_id IS NULL OR qp.owner_id = '');
    END IF;
END$$;

-- If any rows still missing owner_id, set to a placeholder 'unknown' (optional step)
UPDATE question_pools SET owner_id = 'unknown' WHERE owner_id IS NULL;

-- Make owner_id NOT NULL to enforce relation
ALTER TABLE question_pools ALTER COLUMN owner_id SET NOT NULL;

COMMIT;
