-- 012_rename_to_identities.sql
-- Rename 'talents' table to 'identities' and 'talent_id' to 'identity_id'
-- This reflects the broader scope: identities can be actors, news subjects, family members, etc.

-- 1. Rename 'talents' table to 'identities'
ALTER TABLE public.talents RENAME TO identities;
ALTER TABLE dev.talents RENAME TO identities;

-- 2. Rename 'talent_id' column in 'identity_bindings' to 'identity_id'
-- We check if the column exists to avoid errors if already renamed
DO $$ 
BEGIN
    -- Public schema
    IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema = 'public' AND table_name = 'identity_bindings' AND column_name = 'talent_id') THEN
        ALTER TABLE public.identity_bindings RENAME COLUMN talent_id TO identity_id;
    END IF;

    -- Dev schema
    IF EXISTS (SELECT 1 FROM information_schema.columns WHERE table_schema = 'dev' AND table_name = 'identity_bindings' AND column_name = 'talent_id') THEN
        ALTER TABLE dev.identity_bindings RENAME COLUMN talent_id TO identity_id;
    END IF;
END $$;

-- 3. Create index on the new column
CREATE INDEX IF NOT EXISTS idx_identity_bindings_identity_id ON public.identity_bindings(identity_id);
CREATE INDEX IF NOT EXISTS idx_identity_bindings_identity_id ON dev.identity_bindings(identity_id);