-- Private, reusable valuation references. This migration does not fetch external
-- URLs, change prices, execute trades, or expose credentials to public projects.
begin;
create table if not exists marketplace_private.price_sources(
 id uuid primary key default gen_random_uuid(),
 label text not null check(length(label) between 2 and 100),
 kind text not null check(kind in('api','oracle','reference')),
 provider text not null default '' check(length(provider)<=100),
 url text not null check(length(url) between 8 and 500 and url ~ '^https://[^/?#@:]+\.[^/?#@:]+(/[^?#]*)?$'),
 asset_symbol text not null default '' check(length(asset_symbol)<=60),
 quote_currency text not null check(quote_currency ~ '^[A-Z0-9]{2,12}$'),
 price_path text not null default '' check(length(price_path)<=120),
 network text not null default '' check(length(network)<=100),
 contract_address text not null default '' check(length(contract_address)<=160),
 decimals integer check(decimals between 0 and 36),
 enabled boolean not null default true,
 vault_secret_id uuid,
 version integer not null default 1,
 created_at timestamptz not null default clock_timestamp(),
 updated_at timestamptz not null default clock_timestamp(),
 updated_by uuid references auth.users(id),
 check(kind<>'oracle' or (length(network)>0 and length(contract_address)>=4)),
 check(kind<>'reference' or vault_secret_id is null)
);
create unique index if not exists price_sources_label_unique on marketplace_private.price_sources(lower(label));
alter table marketplace_private.price_sources enable row level security;
revoke all on marketplace_private.price_sources from public,anon,authenticated,service_role;

create or replace function marketplace_private.price_source_json(s marketplace_private.price_sources) returns jsonb
language sql immutable set search_path='' as $$
 select jsonb_build_object('id',s.id,'label',s.label,'kind',s.kind,'provider',s.provider,'url',s.url,
 'asset_symbol',s.asset_symbol,'quote_currency',s.quote_currency,'price_path',s.price_path,
 'network',s.network,'contract_address',s.contract_address,'decimals',s.decimals,'enabled',s.enabled,
 'has_key',s.vault_secret_id is not null,'version',s.version,'updated_at',s.updated_at);
$$;
revoke all on function marketplace_private.price_source_json(marketplace_private.price_sources) from public,anon,authenticated,service_role;

create or replace function public.marketplace_price_sources_list() returns jsonb
language plpgsql stable security definer set search_path='' as $$
begin
 if coalesce(public.marketplace_role(),'') not in('owner','publisher','editor') then raise exception 'not_authorized' using errcode='42501';end if;
 return jsonb_build_object('sources',coalesce((select jsonb_agg(marketplace_private.price_source_json(s) order by s.label) from marketplace_private.price_sources s),'[]'::jsonb));
end$$;

create or replace function public.marketplace_price_sources_save(
 p_id uuid,p_version integer,p_config jsonb,p_secret text default null,p_clear_secret boolean default false
) returns jsonb language plpgsql security definer set search_path='' as $$
declare old_row marketplace_private.price_sources;current_row marketplace_private.price_sources;secret_id uuid;
 source_id uuid:=coalesce(p_id,gen_random_uuid());title text;kind_value text;provider_value text;url_value text;asset_value text;currency_value text;path_value text;network_value text;contract_value text;decimal_value integer;enabled_value boolean;
begin
 if public.marketplace_role() is distinct from 'owner' then raise exception 'not_authorized' using errcode='42501';end if;
 if jsonb_typeof(p_config) is distinct from 'object' or p_version is null or p_version<0 or p_clear_secret is null then raise exception 'invalid_price_source';end if;
 if exists(select 1 from jsonb_each(p_config) x where x.key in('label','kind','provider','url','asset_symbol','quote_currency','price_path','network','contract_address') and jsonb_typeof(x.value)<>'string') then raise exception 'invalid_price_source';end if;
 title:=btrim(p_config->>'label');kind_value:=p_config->>'kind';provider_value:=btrim(p_config->>'provider');url_value:=btrim(p_config->>'url');asset_value:=btrim(p_config->>'asset_symbol');currency_value:=upper(btrim(p_config->>'quote_currency'));
 path_value:=case when kind_value='api' then btrim(p_config->>'price_path') else '' end;
 network_value:=case when kind_value='oracle' then btrim(p_config->>'network') else '' end;
 contract_value:=case when kind_value='oracle' then btrim(p_config->>'contract_address') else '' end;
 if jsonb_typeof(p_config->'enabled') is distinct from 'boolean' then raise exception 'invalid_price_source';end if;enabled_value:=(p_config->>'enabled')::boolean;
 if kind_value='oracle' and p_config->'decimals' is not null and p_config->'decimals'<>'null'::jsonb then
  if jsonb_typeof(p_config->'decimals')<>'number' or (p_config->>'decimals')!~'^[0-9]{1,2}$' then raise exception 'invalid_price_source';end if;
  decimal_value:=(p_config->>'decimals')::integer;
 end if;
 if title is null or length(title) not between 2 and 100 or kind_value is null or kind_value not in('api','oracle','reference')
 or provider_value is null or length(provider_value)>100 or asset_value is null or length(asset_value)>60
 or currency_value is null or currency_value!~'^[A-Z0-9]{2,12}$'
 or url_value is null or length(url_value)>500 or url_value!~'^https://[^/?#@:]+\.[^/?#@:]+(/[^?#]*)?$'
 or url_value~*'^https://(localhost|127\.|0\.|10\.|192\.168\.|169\.254\.|172\.(1[6-9]|2[0-9]|3[01])\.)'
 or split_part(split_part(url_value,'://',2),'/',1)~*'\.(local|internal|localhost)$'
 or concat(title,provider_value,asset_value,url_value,network_value,contract_value)~'[[:cntrl:]]'
 or path_value is null or length(path_value)>120 or (path_value<>'' and path_value!~'^[A-Za-z_][A-Za-z0-9_]*(\.([A-Za-z_][A-Za-z0-9_]*|[0-9]+))*$')
 or network_value is null or length(network_value)>100 or contract_value is null or length(contract_value)>160
 or (kind_value='oracle' and (network_value='' or length(contract_value)<4 or contract_value!~'^[A-Za-z0-9_:.-]+$'))
 or (decimal_value is not null and decimal_value not between 0 and 36)
 then raise exception 'invalid_price_source';end if;
 if p_secret is not null and (length(p_secret) not between 8 and 8192 or p_secret!~'^[!-~]+$' or kind_value='reference' or p_clear_secret) then raise exception 'invalid_api_key';end if;
 perform pg_advisory_xact_lock(hashtextextended('price_source:'||source_id::text,0));
 select * into old_row from marketplace_private.price_sources where id=source_id for update;
 if (p_id is null and p_version<>0) or (p_id is not null and (old_row.id is null or old_row.version<>p_version)) then raise exception 'price_source_conflict';end if;
 if exists(select 1 from marketplace_private.price_sources where lower(label)=lower(title) and id<>source_id) then raise exception 'price_source_label_exists';end if;
 secret_id:=old_row.vault_secret_id;
 -- A credential must never follow a changed provider/destination automatically.
 if p_clear_secret or kind_value='reference' or old_row.kind is distinct from kind_value or old_row.provider is distinct from provider_value or old_row.url is distinct from url_value then secret_id:=null;end if;
 if p_secret is not null then
  if to_regprocedure('vault.create_secret(text,text,text,uuid)') is null and to_regprocedure('vault.create_secret(text,text,text)') is null then raise exception 'vault_unavailable';end if;
  if secret_id is null then execute 'select vault.create_secret($1,$2,$3)' into secret_id using p_secret,'planck_price_source_'||source_id::text||'_'||gen_random_uuid()::text,'Planck external valuation read-only credential';
  else execute 'select vault.update_secret($1,$2)' using secret_id,p_secret;end if;
 end if;
 insert into marketplace_private.price_sources(id,label,kind,provider,url,asset_symbol,quote_currency,price_path,network,contract_address,decimals,enabled,vault_secret_id,updated_by)
 values(source_id,title,kind_value,provider_value,url_value,asset_value,currency_value,path_value,network_value,contract_value,decimal_value,enabled_value,secret_id,auth.uid())
 on conflict(id) do update set label=excluded.label,kind=excluded.kind,provider=excluded.provider,url=excluded.url,asset_symbol=excluded.asset_symbol,
 quote_currency=excluded.quote_currency,price_path=excluded.price_path,network=excluded.network,contract_address=excluded.contract_address,decimals=excluded.decimals,
 enabled=excluded.enabled,vault_secret_id=excluded.vault_secret_id,version=price_sources.version+1,updated_at=clock_timestamp(),updated_by=auth.uid()
 returning * into current_row;
 insert into marketplace_private.audit_log(actor,entity,record_id,operation,before_data,after_data)
 values(auth.uid(),'price_sources',source_id::text,case when p_id is null then 'CREATE' else 'UPDATE' end,
 case when old_row.id is not null then marketplace_private.price_source_json(old_row) end,marketplace_private.price_source_json(current_row));
 return jsonb_build_object('source',marketplace_private.price_source_json(current_row));
exception when unique_violation then raise exception 'price_source_label_exists';
end$$;

alter table public.projects add column if not exists price_source_id uuid references marketplace_private.price_sources(id);
create or replace function marketplace_private.project_price_source_guard() returns trigger
language plpgsql security definer set search_path='' as $$
begin
 if new.price_source_id is not null and (tg_op='INSERT' or new.price_source_id is distinct from old.price_source_id)
 and not exists(select 1 from marketplace_private.price_sources where id=new.price_source_id and enabled) then raise exception 'price_source_unavailable';end if;
 return new;
end$$;
revoke all on function marketplace_private.project_price_source_guard() from public,anon,authenticated,service_role;
drop trigger if exists project_price_source_guard on public.projects;
create trigger project_price_source_guard before insert or update of price_source_id on public.projects for each row execute function marketplace_private.project_price_source_guard();
revoke all on function public.marketplace_price_sources_list(),public.marketplace_price_sources_save(uuid,integer,jsonb,text,boolean) from public,anon,service_role;
grant execute on function public.marketplace_price_sources_list(),public.marketplace_price_sources_save(uuid,integer,jsonb,text,boolean) to authenticated;
notify pgrst,'reload schema';
commit;