-- NEOLINK INSIGHT v2 · Dokumente — Register, Revisionen, Datei-Uploads
create table if not exists public.form_documents (
  id uuid primary key default gen_random_uuid(),
  doc_type text not null default 'loan_agreement',
  title text, tenant text not null default 'LOGFRET_DE',
  status text not null default 'draft',
  lang_left text, lang_right text, lang_prevailing text,
  counterparties text, amount numeric, currency text,
  variables jsonb not null default '{}'::jsonb,
  rev integer not null default 1,
  created_by uuid default auth.uid(), created_at timestamptz not null default now(),
  updated_by uuid default auth.uid(), updated_at timestamptz not null default now()
);
create table if not exists public.form_document_revisions (
  id uuid primary key default gen_random_uuid(),
  document_id uuid not null references public.form_documents(id) on delete cascade,
  rev integer not null, status text, variables jsonb not null, note text,
  created_by uuid default auth.uid(), created_at timestamptz not null default now()
);
create table if not exists public.finance_documents (
  id uuid primary key default gen_random_uuid(),
  category text not null default 'other',
  title text, tenant text not null default 'LOGFRET_DE',
  org_code text, file_name text, mime_type text, file_size bigint,
  storage_path text not null, status text not null default 'active', note text,
  uploaded_by uuid default auth.uid(), uploaded_at timestamptz not null default now()
);
create index if not exists idx_form_documents_updated on public.form_documents (updated_at desc);
create index if not exists idx_form_doc_revisions_doc on public.form_document_revisions (document_id, rev desc);
create index if not exists idx_finance_documents_uploaded on public.finance_documents (uploaded_at desc);
create index if not exists idx_finance_documents_cat on public.finance_documents (category);
alter table public.form_documents enable row level security;
alter table public.form_document_revisions enable row level security;
alter table public.finance_documents enable row level security;
drop policy if exists form_documents_authenticated_all on public.form_documents;
create policy form_documents_authenticated_all on public.form_documents for all to authenticated using (true) with check (true);
drop policy if exists form_doc_revisions_authenticated_all on public.form_document_revisions;
create policy form_doc_revisions_authenticated_all on public.form_document_revisions for all to authenticated using (true) with check (true);
drop policy if exists finance_documents_authenticated_all on public.finance_documents;
create policy finance_documents_authenticated_all on public.finance_documents for all to authenticated using (true) with check (true);
grant select, insert, update, delete on public.form_documents to authenticated;
grant select, insert, update, delete on public.form_document_revisions to authenticated;
grant select, insert, update, delete on public.finance_documents to authenticated;
create or replace function public.tg_form_documents_touch() returns trigger language plpgsql as $$
begin new.updated_at := now(); new.updated_by := auth.uid(); return new; end $$;
drop trigger if exists trg_form_documents_touch on public.form_documents;
create trigger trg_form_documents_touch before update on public.form_documents for each row execute function public.tg_form_documents_touch();
-- Storage-Bucket für hochgeladene Dateien
insert into storage.buckets (id, name, public) values ('finance-docs','finance-docs', false) on conflict (id) do nothing;
drop policy if exists finance_docs_objects_auth on storage.objects;
create policy finance_docs_objects_auth on storage.objects for all to authenticated
  using (bucket_id = 'finance-docs') with check (bucket_id = 'finance-docs');
-- ── Signatur-Backend (erzeugte Dokumente + hochgeladene Dateien) ──
alter table public.form_documents add column if not exists content_html text;
alter table public.form_documents add column if not exists envelope_id text;
alter table public.finance_documents add column if not exists envelope_id text;
alter table public.finance_documents add column if not exists sign_status text;
create table if not exists public.form_signatures (
  id uuid primary key default gen_random_uuid(),
  doc_kind text not null default 'generated',
  document_id uuid references public.form_documents(id) on delete cascade,
  file_id uuid references public.finance_documents(id) on delete cascade,
  envelope_id text not null,
  title text, content_html text, file_url text, file_mime text,
  sign_lang text default 'de',
  signer_role text, signer_label text, signer_name text, signer_email text,
  token text not null unique, status text not null default 'pending',
  signature_type text, signed_name text, signature_image text,
  signed_at timestamptz, user_agent text,
  created_at timestamptz not null default now()
);
create index if not exists idx_form_signatures_token on public.form_signatures (token);
create index if not exists idx_form_signatures_env on public.form_signatures (envelope_id);
alter table public.form_signatures enable row level security;
drop policy if exists form_signatures_auth_all on public.form_signatures;
create policy form_signatures_auth_all on public.form_signatures for all to authenticated using (true) with check (true);
grant select, insert, update, delete on public.form_signatures to authenticated;
-- Anon bekommt KEINEN direkten Tabellenzugriff — nur über die beiden RPCs:
create or replace function public.form_sign_get(p_token text)
returns jsonb language plpgsql security definer set search_path = public as $func$
declare s record; others jsonb;
begin
  select * into s from public.form_signatures where token = p_token;
  if not found then return null; end if;
  select jsonb_agg(jsonb_build_object('role',signer_role,'label',signer_label,'status',status,'signed_at',signed_at,'signed_name',signed_name,'signature_type',signature_type,'signature_image',signature_image) order by created_at)
    into others from public.form_signatures where envelope_id = s.envelope_id;
  return jsonb_build_object('token',s.token,'doc_kind',s.doc_kind,'role',s.signer_role,'label',s.signer_label,'signer_name',s.signer_name,
    'sign_lang',coalesce(s.sign_lang,'de'),'status',s.status,'signed_at',s.signed_at,'signed_name',s.signed_name,
    'signature_type',s.signature_type,'signature_image',s.signature_image,
    'title',s.title,'content_html',s.content_html,'file_url',s.file_url,'file_mime',s.file_mime,'envelope_id',s.envelope_id,'parties',others);
end $func$;
create or replace function public.form_sign_submit(p_token text, p_name text, p_type text default 'typed', p_image text default null, p_user_agent text default null)
returns jsonb language plpgsql security definer set search_path = public as $func$
declare s record; remaining int;
begin
  select * into s from public.form_signatures where token = p_token for update;
  if not found then return jsonb_build_object('ok',false,'error','not_found'); end if;
  if s.status = 'signed' then return jsonb_build_object('ok',true,'already',true); end if;
  update public.form_signatures set status='signed', signed_name = coalesce(nullif(p_name,''), signer_name),
    signature_type = p_type, signature_image = p_image, signed_at = now(), user_agent = p_user_agent where token = p_token;
  select count(*) into remaining from public.form_signatures where envelope_id = s.envelope_id and status <> 'signed';
  if remaining = 0 then
    if s.doc_kind = 'file' and s.file_id is not null then update public.finance_documents set sign_status='signed' where id = s.file_id;
    elsif s.document_id is not null then update public.form_documents set status='signed' where id = s.document_id; end if;
  end if;
  return jsonb_build_object('ok',true,'all_signed', remaining=0);
end $func$;
revoke all on function public.form_sign_get(text) from public;
revoke all on function public.form_sign_submit(text,text,text,text,text) from public;
grant execute on function public.form_sign_get(text) to anon, authenticated;
grant execute on function public.form_sign_submit(text,text,text,text,text) to anon, authenticated;