-- 004_fix_uuid.sql
-- Fixes UUID generation on PostgreSQL installations without uuid-ossp/pgcrypto
-- and without built-in gen_random_uuid() (PostgreSQL < 13).
-- Defines a pure-SQL UUIDv4 generator and restores DEFAULT on all id columns.

-- Pure-SQL UUID v4 generator (no extensions required)
CREATE OR REPLACE FUNCTION gen_random_uuid()
RETURNS uuid AS $$
  SELECT (
    lpad(to_hex((floor(random() * 4294967296))::bigint), 8, '0') || '-' ||
    lpad(to_hex((floor(random() * 65536))::int), 4, '0') || '-' ||
    '4' || lpad(to_hex((floor(random() * 4096))::int), 3, '0') || '-' ||
    substr('89ab', (floor(random() * 4))::int + 1, 1) ||
    lpad(to_hex((floor(random() * 4096))::int), 3, '0') || '-' ||
    lpad(to_hex((floor(random() * 4294967296))::bigint), 8, '0') ||
    lpad(to_hex((floor(random() * 65536))::int), 4, '0')
  )::uuid
$$ LANGUAGE sql VOLATILE;

-- Restore DEFAULT on all UUID primary key columns
ALTER TABLE users            ALTER COLUMN id SET DEFAULT gen_random_uuid();
ALTER TABLE user_api_keys    ALTER COLUMN id SET DEFAULT gen_random_uuid();
ALTER TABLE pipeline_runs    ALTER COLUMN id SET DEFAULT gen_random_uuid();
ALTER TABLE generated_content ALTER COLUMN id SET DEFAULT gen_random_uuid();
ALTER TABLE usage_logs       ALTER COLUMN id SET DEFAULT gen_random_uuid();
ALTER TABLE performance_data ALTER COLUMN id SET DEFAULT gen_random_uuid();
ALTER TABLE audit_logs       ALTER COLUMN id SET DEFAULT gen_random_uuid();
ALTER TABLE niche_profiles   ALTER COLUMN id SET DEFAULT gen_random_uuid();
ALTER TABLE topic_proposals  ALTER COLUMN id SET DEFAULT gen_random_uuid();