begin;

-- Allow chat messages to store new audio payloads.
alter table chat_messages
	drop constraint if exists chat_messages_content_type_check;

alter table chat_messages
	add constraint chat_messages_content_type_check
	check (content_type in ('text', 'markdown', 'json', 'image', 'file', 'audio'));

-- Track the ingress source for every message.
alter table chat_messages
	add column if not exists ingress_type text not null default 'manual'
		check (ingress_type in ('manual', 'bluetooth', 'school_gateway', 'system'));

comment on column chat_messages.ingress_type is
	'Source of message ingestion: manual app entry, bluetooth wearable sync, school gateway, or system automation.';

-- Refresh insert policies so authenticated users and gateways follow the new ingress contract.
drop policy if exists chat_messages_insert on chat_messages;
drop policy if exists chat_messages_insert_self on chat_messages;
drop policy if exists chat_messages_insert_gateway on chat_messages;

create policy chat_messages_insert_self on chat_messages
	for insert
	to authenticated
	with check (
		chat_messages.sender_id = auth.uid()
		and chat_messages.ingress_type in ('manual', 'bluetooth')
		and exists (
			select 1
			from chat_participants p
			where p.conversation_id = chat_messages.conversation_id
				and p.user_id = auth.uid()
		)
	);

create policy chat_messages_insert_gateway on chat_messages
	for insert
	to authenticated
	with check (
		coalesce((current_setting('request.jwt.claims', true)::jsonb ->> 'app_role'), '') in ('gateway_ingress', 'system_bot')
		and chat_messages.ingress_type in ('school_gateway', 'system')
	);

-- Fix chat participant policies to avoid recursive RLS evaluation.
create or replace function public.chat_user_is_manager(p_conversation_id uuid, p_user_id uuid)
returns boolean
language sql
security definer
set search_path = public
as $$
	select exists (
		select 1
		from chat_participants
		where conversation_id = p_conversation_id
			and user_id = p_user_id
			and role in ('owner', 'admin')
	);
$$;

revoke all on function public.chat_user_is_manager(uuid, uuid) from public;
grant execute on function public.chat_user_is_manager(uuid, uuid) to authenticated;

drop policy if exists chat_conversations_update on chat_conversations;
create policy chat_conversations_update on chat_conversations for update to authenticated using (
	public.chat_user_is_manager(chat_conversations.id, auth.uid())
) with check (
	public.chat_user_is_manager(chat_conversations.id, auth.uid())
);

drop policy if exists chat_participants_select on chat_participants;
drop policy if exists chat_participants_update on chat_participants;
drop policy if exists chat_participants_delete on chat_participants;
drop policy if exists chat_participants_insert on chat_participants;

create policy chat_participants_select on chat_participants for select to authenticated using (
	user_id = auth.uid() or public.chat_user_is_manager(chat_participants.conversation_id, auth.uid())
);

create policy chat_participants_update on chat_participants for update to authenticated using (
	user_id = auth.uid() or public.chat_user_is_manager(chat_participants.conversation_id, auth.uid())
) with check (
	user_id = auth.uid() or public.chat_user_is_manager(chat_participants.conversation_id, auth.uid())
);

create policy chat_participants_delete on chat_participants for delete to authenticated using (
	user_id = auth.uid() or public.chat_user_is_manager(chat_participants.conversation_id, auth.uid())
);

create policy chat_participants_insert on chat_participants for insert to authenticated with check (
	user_id = auth.uid() or public.chat_user_is_manager(chat_participants.conversation_id, auth.uid())
);

-- Ensure downlink queue has optional metadata payload for automation hints.
alter table chat_mqtt_downlinks
	add column if not exists metadata jsonb;

commit;
drop policy if exists chat_participants_all on chat_participants;

drop policy if exists chat_participants_select on chat_participants;
drop policy if exists chat_participants_update on chat_participants;
drop policy if exists chat_participants_delete on chat_participants;
drop policy if exists chat_participants_insert on chat_participants;

create policy chat_participants_select on chat_participants for select to authenticated using (
  user_id = auth.uid() or public.chat_user_is_manager(chat_participants.conversation_id, auth.uid())
);

create policy chat_participants_update on chat_participants for update to authenticated using (
  user_id = auth.uid() or public.chat_user_is_manager(chat_participants.conversation_id, auth.uid())
) with check (
  user_id = auth.uid() or public.chat_user_is_manager(chat_participants.conversation_id, auth.uid())
);

create policy chat_participants_delete on chat_participants for delete to authenticated using (
  user_id = auth.uid() or public.chat_user_is_manager(chat_participants.conversation_id, auth.uid())
);

create policy chat_participants_insert on chat_participants for insert to authenticated with check (
  user_id = auth.uid() or public.chat_user_is_manager(chat_participants.conversation_id, auth.uid())
);