-- Convert tenant_key from an opaque authentication value into a real tenant
-- relationship. Preserve legacy rows and create placeholders for historical
-- key IDs before adding foreign keys.
insert into tenants (id, name)
select tenant_key, tenant_key
from (
    select tenant_key from projects
    union select tenant_key from thoughts
    union select tenant_key from stored_files
    union select tenant_key from learnings
    union select tenant_key from plans
    union select tenant_key from chat_histories
) tenant_keys
where tenant_key is not null and tenant_key <> ''
on conflict (id) do nothing;

alter table agent_skills add column if not exists tenant_key text references tenants(id);
alter table agent_guardrails add column if not exists tenant_key text references tenants(id);
alter table agent_personas add column if not exists tenant_key text references tenants(id);
alter table agent_parts add column if not exists tenant_key text references tenants(id);
alter table agent_traits add column if not exists tenant_key text references tenants(id);
alter table character_arcs add column if not exists tenant_key text references tenants(id);

alter table agent_skills drop constraint if exists ukey_agent_skills_name;
alter table agent_guardrails drop constraint if exists ukey_agent_guardrails_name;
alter table agent_personas drop constraint if exists ukey_agent_personas_name;
alter table agent_parts drop constraint if exists ukey_agent_parts_name;
alter table agent_traits drop constraint if exists ukey_agent_traits_name;
alter table character_arcs drop constraint if exists ukey_character_arcs_name;

create unique index if not exists agent_skills_tenant_key_name_idx on agent_skills (coalesce(tenant_key, ''), name);
create unique index if not exists agent_guardrails_tenant_key_name_idx on agent_guardrails (coalesce(tenant_key, ''), name);
create unique index if not exists agent_personas_tenant_key_name_idx on agent_personas (coalesce(tenant_key, ''), name);
create unique index if not exists agent_parts_tenant_key_name_idx on agent_parts (coalesce(tenant_key, ''), name);
create unique index if not exists agent_traits_tenant_key_name_idx on agent_traits (coalesce(tenant_key, ''), name);
create unique index if not exists character_arcs_tenant_key_name_idx on character_arcs (coalesce(tenant_key, ''), name);

create index if not exists agent_skills_tenant_key_idx on agent_skills (tenant_key);
create index if not exists agent_guardrails_tenant_key_idx on agent_guardrails (tenant_key);
create index if not exists agent_personas_tenant_key_idx on agent_personas (tenant_key);
create index if not exists agent_parts_tenant_key_idx on agent_parts (tenant_key);
create index if not exists agent_traits_tenant_key_idx on agent_traits (tenant_key);
create index if not exists character_arcs_tenant_key_idx on character_arcs (tenant_key);

do $$
declare table_name text;
begin
  foreach table_name in array array['projects', 'thoughts', 'stored_files', 'learnings', 'plans', 'chat_histories']
  loop
    execute format('alter table %I drop constraint if exists %I', table_name, table_name || '_tenant_key_fkey');
    execute format('alter table %I add constraint %I foreign key (tenant_key) references tenants(id)', table_name, table_name || '_tenant_key_fkey');
  end loop;
end $$;