create extension if not exists vector;

create table if not exists public.coach_conversations (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null references auth.users(id) on delete cascade,
  company_member_id uuid not null references public.company_members(id) on delete cascade,
  title text not null default 'Nouvelle conversation',
  message_count integer not null default 0,
  last_message_at timestamptz,
  created_at timestamptz not null default timezone('utc', now()),
  updated_at timestamptz not null default timezone('utc', now()),
  constraint coach_conversations_title_length check (char_length(title) between 1 and 160),
  constraint coach_conversations_message_count_non_negative check (message_count >= 0)
);

create index if not exists coach_conversations_user_updated_idx
  on public.coach_conversations (user_id, updated_at desc);

create index if not exists coach_conversations_member_updated_idx
  on public.coach_conversations (company_member_id, updated_at desc);

drop trigger if exists set_coach_conversations_updated_at on public.coach_conversations;
create trigger set_coach_conversations_updated_at
before update on public.coach_conversations
for each row
execute function public.set_updated_at();

create table if not exists public.coach_messages (
  id uuid primary key default gen_random_uuid(),
  conversation_id uuid not null references public.coach_conversations(id) on delete cascade,
  role text not null,
  content text not null,
  sources jsonb not null default '[]'::jsonb,
  created_at timestamptz not null default timezone('utc', now()),
  constraint coach_messages_role_check check (role in ('user', 'assistant')),
  constraint coach_messages_content_length check (char_length(content) between 1 and 12000),
  constraint coach_messages_sources_array check (jsonb_typeof(sources) = 'array')
);

create index if not exists coach_messages_conversation_created_idx
  on public.coach_messages (conversation_id, created_at asc);

create table if not exists public.knowledge_documents (
  id uuid primary key default gen_random_uuid(),
  title text not null,
  source_label text,
  file_name text not null,
  content_type text not null,
  file_size bigint not null default 0,
  storage_bucket text not null default 'coach-knowledge',
  storage_path text not null,
  status text not null default 'processing',
  chunk_count integer not null default 0,
  content_length integer not null default 0,
  error_message text,
  uploaded_by_user_id uuid references auth.users(id) on delete set null,
  uploaded_by_member_id uuid references public.company_members(id) on delete set null,
  created_at timestamptz not null default timezone('utc', now()),
  updated_at timestamptz not null default timezone('utc', now()),
  constraint knowledge_documents_title_length check (char_length(title) between 1 and 200),
  constraint knowledge_documents_status_check check (status in ('processing', 'ready', 'failed')),
  constraint knowledge_documents_chunk_count_non_negative check (chunk_count >= 0),
  constraint knowledge_documents_content_length_non_negative check (content_length >= 0)
);

create index if not exists knowledge_documents_status_updated_idx
  on public.knowledge_documents (status, updated_at desc);

drop trigger if exists set_knowledge_documents_updated_at on public.knowledge_documents;
create trigger set_knowledge_documents_updated_at
before update on public.knowledge_documents
for each row
execute function public.set_updated_at();

create table if not exists public.knowledge_document_chunks (
  id uuid primary key default gen_random_uuid(),
  document_id uuid not null references public.knowledge_documents(id) on delete cascade,
  chunk_index integer not null,
  content text not null,
  metadata jsonb not null default '{}'::jsonb,
  embedding vector(1536) not null,
  created_at timestamptz not null default timezone('utc', now()),
  constraint knowledge_document_chunks_index_non_negative check (chunk_index >= 0),
  constraint knowledge_document_chunks_metadata_object check (jsonb_typeof(metadata) = 'object')
);

create index if not exists knowledge_document_chunks_document_idx
  on public.knowledge_document_chunks (document_id, chunk_index asc);

create index if not exists knowledge_document_chunks_embedding_idx
  on public.knowledge_document_chunks
  using ivfflat (embedding vector_cosine_ops)
  with (lists = 100);

create or replace function public.match_knowledge_document_chunks(
  query_embedding vector(1536),
  match_count integer default 6,
  min_similarity float default 0.22
)
returns table (
  chunk_id uuid,
  document_id uuid,
  document_title text,
  source_label text,
  chunk_index integer,
  content text,
  metadata jsonb,
  similarity float
)
language sql
stable
as $$
  select
    chunks.id as chunk_id,
    chunks.document_id,
    documents.title as document_title,
    documents.source_label,
    chunks.chunk_index,
    chunks.content,
    chunks.metadata,
    1 - (chunks.embedding <=> query_embedding) as similarity
  from public.knowledge_document_chunks as chunks
  inner join public.knowledge_documents as documents
    on documents.id = chunks.document_id
  where documents.status = 'ready'
    and (1 - (chunks.embedding <=> query_embedding)) >= min_similarity
  order by chunks.embedding <=> query_embedding
  limit greatest(match_count, 1);
$$;

insert into storage.buckets (
  id,
  name,
  public,
  file_size_limit,
  allowed_mime_types
)
values (
  'coach-knowledge',
  'coach-knowledge',
  false,
  10485760,
  array[
    'application/pdf',
    'text/plain',
    'text/markdown',
    'text/x-markdown'
  ]
)
on conflict (id) do nothing;

alter table public.coach_conversations enable row level security;
alter table public.coach_messages enable row level security;
alter table public.knowledge_documents enable row level security;
alter table public.knowledge_document_chunks enable row level security;

drop policy if exists "coach_conversations_select_owner" on public.coach_conversations;
create policy "coach_conversations_select_owner"
on public.coach_conversations
for select
to authenticated
using (user_id = auth.uid());

drop policy if exists "coach_conversations_insert_owner" on public.coach_conversations;
create policy "coach_conversations_insert_owner"
on public.coach_conversations
for insert
to authenticated
with check (
  user_id = auth.uid()
  and exists (
    select 1
    from public.company_members as members
    where members.id = coach_conversations.company_member_id
      and members.auth_user_id = auth.uid()
      and members.status = 'active'
  )
);

drop policy if exists "coach_conversations_update_owner" on public.coach_conversations;
create policy "coach_conversations_update_owner"
on public.coach_conversations
for update
to authenticated
using (user_id = auth.uid())
with check (
  user_id = auth.uid()
  and exists (
    select 1
    from public.company_members as members
    where members.id = coach_conversations.company_member_id
      and members.auth_user_id = auth.uid()
      and members.status = 'active'
  )
);

drop policy if exists "coach_messages_select_owner" on public.coach_messages;
create policy "coach_messages_select_owner"
on public.coach_messages
for select
to authenticated
using (
  exists (
    select 1
    from public.coach_conversations as conversations
    where conversations.id = coach_messages.conversation_id
      and conversations.user_id = auth.uid()
  )
);

drop policy if exists "coach_messages_insert_owner" on public.coach_messages;
create policy "coach_messages_insert_owner"
on public.coach_messages
for insert
to authenticated
with check (
  exists (
    select 1
    from public.coach_conversations as conversations
    where conversations.id = coach_messages.conversation_id
      and conversations.user_id = auth.uid()
  )
);

grant select, insert, update, delete on public.coach_conversations to authenticated;
grant select, insert, update, delete on public.coach_messages to authenticated;
grant execute on function public.match_knowledge_document_chunks(vector, integer, float) to authenticated;

grant select, insert, update, delete on public.coach_conversations to service_role;
grant select, insert, update, delete on public.coach_messages to service_role;
grant select, insert, update, delete on public.knowledge_documents to service_role;
grant select, insert, update, delete on public.knowledge_document_chunks to service_role;
grant execute on function public.match_knowledge_document_chunks(vector, integer, float) to service_role;
