Configurações

-- Aria — esquema inicial (com autenticação)
create table if not exists products (
  id uuid default gen_random_uuid() primary key,
  user_id uuid references auth.users(id) on delete cascade,
  name text not null,
  url text not null,
  summary text not null,
  created_at timestamp with time zone default now(),
  updated_at timestamp with time zone default now()
);

create table if not exists publications (
  id uuid default gen_random_uuid() primary key,
  user_id uuid references auth.users(id) on delete cascade,
  content text not null,
  image_url text,
  networks text[] not null,
  status text not null default 'draft',
  scheduled_at timestamp with time zone,
  published_at timestamp with time zone,
  post_ids jsonb,
  created_at timestamp with time zone default now(),
  updated_at timestamp with time zone default now()
);

create table if not exists conversations (
  id uuid default gen_random_uuid() primary key,
  user_id uuid references auth.users(id) on delete cascade,
  title text,
  created_at timestamp with time zone default now(),
  updated_at timestamp with time zone default now()
);

create table if not exists messages (
  id uuid default gen_random_uuid() primary key,
  conversation_id uuid references conversations(id) on delete cascade,
  role text not null,
  content text not null,
  created_at timestamp with time zone default now()
);

create table if not exists app_config (
  id uuid default gen_random_uuid() primary key,
  user_id uuid references auth.users(id) on delete cascade,
  key text not null,
  value text not null,
  updated_at timestamp with time zone default now(),
  unique (user_id, key)
);

-- Para tabelas existentes (re-correr é seguro)
alter table products       add column if not exists user_id uuid references auth.users(id) on delete cascade;
alter table publications   add column if not exists user_id uuid references auth.users(id) on delete cascade;
alter table conversations  add column if not exists user_id uuid references auth.users(id) on delete cascade;
alter table app_config     add column if not exists user_id uuid references auth.users(id) on delete cascade;

alter table products enable row level security;
alter table publications enable row level security;
alter table conversations enable row level security;
alter table messages enable row level security;
alter table app_config enable row level security;

-- Remover policies antigas permissivas se existirem
drop policy if exists "open_products"      on products;
drop policy if exists "open_publications"  on publications;
drop policy if exists "open_conversations" on conversations;
drop policy if exists "open_messages"      on messages;
drop policy if exists "open_app_config"    on app_config;

-- Policies isoladas por utilizador
do $$ begin
  create policy "products_user" on products for all
    using (auth.uid() = user_id) with check (auth.uid() = user_id);
exception when duplicate_object then null; end $$;
do $$ begin
  create policy "publications_user" on publications for all
    using (auth.uid() = user_id) with check (auth.uid() = user_id);
exception when duplicate_object then null; end $$;
do $$ begin
  create policy "conversations_user" on conversations for all
    using (auth.uid() = user_id) with check (auth.uid() = user_id);
exception when duplicate_object then null; end $$;
do $$ begin
  create policy "messages_user" on messages for all
    using (
      exists (select 1 from conversations c
        where c.id = messages.conversation_id and c.user_id = auth.uid())
    ) with check (
      exists (select 1 from conversations c
        where c.id = messages.conversation_id and c.user_id = auth.uid())
    );
exception when duplicate_object then null; end $$;
do $$ begin
  create policy "app_config_user" on app_config for all
    using (auth.uid() = user_id) with check (auth.uid() = user_id);
exception when duplicate_object then null; end $$;

-- =====================================================
-- COMENTÁRIOS
-- =====================================================
create table if not exists comments (
  id uuid default gen_random_uuid() primary key,
  user_id uuid references auth.users(id) on delete cascade,
  network text not null,
  post_id text not null,
  author_name text not null,
  author_avatar text,
  content text not null,
  sentiment text default 'neutral',
  category text default 'other',
  status text default 'pending',
  suggested_reply text,
  published_reply text,
  replied_at timestamp with time zone,
  received_at timestamp with time zone default now(),
  created_at timestamp with time zone default now()
);
alter table comments enable row level security;
do $$ begin
  create policy "comments_user" on comments for all
    using (auth.uid() = user_id) with check (auth.uid() = user_id);
exception when duplicate_object then null; end $$;

-- =====================================================
-- MÉTRICAS
-- =====================================================
create table if not exists metrics (
  id uuid default gen_random_uuid() primary key,
  user_id uuid references auth.users(id) on delete cascade,
  publication_id uuid references publications(id) on delete cascade,
  network text not null,
  post_id text not null,
  impressions integer default 0,
  reach integer default 0,
  likes integer default 0,
  comments integer default 0,
  shares integer default 0,
  clicks integer default 0,
  engagement_rate numeric(5,2) default 0,
  fetched_at timestamp with time zone default now(),
  created_at timestamp with time zone default now()
);
alter table metrics enable row level security;
do $$ begin
  create policy "metrics_user" on metrics for all
    using (auth.uid() = user_id) with check (auth.uid() = user_id);
exception when duplicate_object then null; end $$;

-- =====================================================
-- EQUIPA / MULTI-UTILIZADOR
-- =====================================================
do $$ begin
  create type user_role as enum ('owner','admin','editor','viewer');
exception when duplicate_object then null; end $$;

create table if not exists team_members (
  id uuid default gen_random_uuid() primary key,
  owner_id uuid references auth.users(id) on delete cascade,
  member_id uuid references auth.users(id) on delete cascade,
  role user_role not null default 'viewer',
  status text not null default 'pending',
  invited_email text not null,
  invited_at timestamp with time zone default now(),
  accepted_at timestamp with time zone,
  created_at timestamp with time zone default now(),
  unique (owner_id, invited_email)
);
alter table team_members enable row level security;
do $$ begin
  create policy "team_owner_all" on team_members for all
    using (auth.uid() = owner_id) with check (auth.uid() = owner_id);
exception when duplicate_object then null; end $$;
do $$ begin
  create policy "team_member_read" on team_members for select
    using (auth.uid() = member_id);
exception when duplicate_object then null; end $$;

-- Função utilitária: se for membro, devolve o owner_id; senão devolve o próprio uid
create or replace function public.workspace_owner(_uid uuid)
returns uuid language sql stable security definer set search_path = public as $$
  select coalesce(
    (select owner_id from team_members
      where member_id = _uid and status = 'accepted' limit 1),
    _uid
  );
$$;

-- Liga convites pendentes ao novo utilizador quando ele se regista
create or replace function public.link_pending_invites()
returns trigger language plpgsql security definer set search_path = public as $$
begin
  update team_members
    set member_id = NEW.id, status = 'accepted', accepted_at = now()
    where lower(invited_email) = lower(NEW.email) and member_id is null;
  return NEW;
end;
$$;
do $$ begin
  create trigger link_pending_invites_trg
    after insert on auth.users
    for each row execute function public.link_pending_invites();
exception when duplicate_object then null; end $$;