-- =====================================================================
-- Controle Financeiro multiusuário — schema inicial
-- Rode no SQL Editor do Supabase (ou `supabase db push`).
-- =====================================================================

create extension if not exists "pgcrypto";

-- ---------------------------------------------------------------------
-- Perfis (espelho de auth.users)
-- ---------------------------------------------------------------------
create table public.profiles (
  id          uuid primary key references auth.users(id) on delete cascade,
  email       text not null,
  full_name   text,
  created_at  timestamptz not null default now()
);

create or replace function public.handle_new_user()
returns trigger language plpgsql security definer set search_path = public as $$
begin
  insert into public.profiles (id, email, full_name)
  values (new.id, new.email, coalesce(new.raw_user_meta_data->>'full_name', split_part(new.email, '@', 1)));
  return new;
end $$;

create trigger on_auth_user_created
  after insert on auth.users
  for each row execute function public.handle_new_user();

-- ---------------------------------------------------------------------
-- Carteiras (workspaces): ex. "Pessoal" e "Empresa"
-- ---------------------------------------------------------------------
create table public.workspaces (
  id          uuid primary key default gen_random_uuid(),
  name        text not null,
  kind        text not null default 'pessoal' check (kind in ('pessoal','empresa')),
  created_by  uuid not null default auth.uid() references public.profiles(id),
  created_at  timestamptz not null default now()
);

create table public.workspace_members (
  workspace_id uuid not null references public.workspaces(id) on delete cascade,
  user_id      uuid not null references public.profiles(id) on delete cascade,
  role         text not null default 'member' check (role in ('owner','member')),
  created_at   timestamptz not null default now(),
  primary key (workspace_id, user_id)
);

create table public.workspace_invites (
  id           uuid primary key default gen_random_uuid(),
  workspace_id uuid not null references public.workspaces(id) on delete cascade,
  email        text not null,
  invited_by   uuid not null default auth.uid() references public.profiles(id),
  accepted_at  timestamptz,
  created_at   timestamptz not null default now(),
  unique (workspace_id, email)
);

-- Helpers usados nas policies (security definer evita recursão de RLS)
create or replace function public.is_member(ws uuid)
returns boolean language sql stable security definer set search_path = public as $$
  select exists (select 1 from workspace_members where workspace_id = ws and user_id = auth.uid());
$$;

create or replace function public.is_owner(ws uuid)
returns boolean language sql stable security definer set search_path = public as $$
  select exists (select 1 from workspace_members where workspace_id = ws and user_id = auth.uid() and role = 'owner');
$$;

-- ---------------------------------------------------------------------
-- Contas (bancos, cartões, caixa)
-- ---------------------------------------------------------------------
create table public.accounts (
  id               uuid primary key default gen_random_uuid(),
  workspace_id     uuid not null references public.workspaces(id) on delete cascade,
  name             text not null,
  type             text not null default 'corrente'
                   check (type in ('corrente','poupanca','cartao','dinheiro','investimento')),
  initial_balance  numeric(14,2) not null default 0,
  -- Open Finance
  connection_id    uuid,
  external_id      text,
  archived         boolean not null default false,
  created_at       timestamptz not null default now(),
  unique (connection_id, external_id)
);

-- ---------------------------------------------------------------------
-- Categorias
-- ---------------------------------------------------------------------
create table public.categories (
  id           uuid primary key default gen_random_uuid(),
  workspace_id uuid not null references public.workspaces(id) on delete cascade,
  name         text not null,
  type         text not null check (type in ('receita','despesa')),
  color        text not null default '#64748b',
  created_at   timestamptz not null default now(),
  unique (workspace_id, name, type)
);

-- ---------------------------------------------------------------------
-- Lançamentos (receitas/despesas). Pendentes = contas a pagar/receber.
-- ---------------------------------------------------------------------
create table public.transactions (
  id               uuid primary key default gen_random_uuid(),
  workspace_id     uuid not null references public.workspaces(id) on delete cascade,
  account_id       uuid references public.accounts(id) on delete set null,
  category_id      uuid references public.categories(id) on delete set null,
  type             text not null check (type in ('receita','despesa')),
  description      text not null,
  amount           numeric(14,2) not null check (amount > 0),
  date             date not null,                 -- data de competência / vencimento
  paid_at          date,                          -- null = pendente
  counterparty     text,                          -- cliente / fornecedor
  notes            text,
  recurrence_group uuid,                          -- parcelas/recorrências criadas juntas
  installment      int,
  installments     int,
  source           text not null default 'manual' check (source in ('manual','openfinance')),
  external_id      text,
  created_by       uuid default auth.uid() references public.profiles(id),
  created_at       timestamptz not null default now(),
  updated_at       timestamptz not null default now()
);

create index transactions_ws_date_idx on public.transactions (workspace_id, date);
create index transactions_ws_pending_idx on public.transactions (workspace_id, date) where paid_at is null;
alter table public.transactions add constraint transactions_external_key unique (account_id, external_id);

create or replace function public.touch_updated_at()
returns trigger language plpgsql as $$
begin new.updated_at = now(); return new; end $$;

create trigger transactions_touch before update on public.transactions
  for each row execute function public.touch_updated_at();

-- ---------------------------------------------------------------------
-- Orçamento mensal por categoria e metas
-- ---------------------------------------------------------------------
create table public.budgets (
  id           uuid primary key default gen_random_uuid(),
  workspace_id uuid not null references public.workspaces(id) on delete cascade,
  category_id  uuid not null references public.categories(id) on delete cascade,
  amount       numeric(14,2) not null check (amount > 0),
  unique (workspace_id, category_id)
);

create table public.goals (
  id             uuid primary key default gen_random_uuid(),
  workspace_id   uuid not null references public.workspaces(id) on delete cascade,
  name           text not null,
  target_amount  numeric(14,2) not null check (target_amount > 0),
  current_amount numeric(14,2) not null default 0,
  deadline       date,
  created_at     timestamptz not null default now()
);

-- ---------------------------------------------------------------------
-- Open Finance (conexões via agregador: Pluggy, Belvo, etc.)
-- ---------------------------------------------------------------------
create table public.openfinance_connections (
  id             uuid primary key default gen_random_uuid(),
  workspace_id   uuid not null references public.workspaces(id) on delete cascade,
  provider       text not null default 'pluggy',
  item_id        text not null,             -- id da conexão no agregador
  institution    text,
  status         text not null default 'ativo',
  last_sync_at   timestamptz,
  created_by     uuid default auth.uid() references public.profiles(id),
  created_at     timestamptz not null default now(),
  unique (provider, item_id)
);

alter table public.accounts
  add constraint accounts_connection_fk foreign key (connection_id)
  references public.openfinance_connections(id) on delete set null;

-- ---------------------------------------------------------------------
-- Saldo por conta (view respeita RLS do usuário)
-- ---------------------------------------------------------------------
create view public.account_balances with (security_invoker = true) as
select a.id, a.workspace_id, a.name, a.type, a.archived,
       a.initial_balance
       + coalesce(sum(case when t.type = 'receita' then t.amount else -t.amount end)
                  filter (where t.paid_at is not null), 0) as balance
from public.accounts a
left join public.transactions t on t.account_id = a.id
group by a.id;

-- ---------------------------------------------------------------------
-- Ao criar carteira: criador vira owner + categorias padrão
-- ---------------------------------------------------------------------
create or replace function public.handle_new_workspace()
returns trigger language plpgsql security definer set search_path = public as $$
begin
  insert into workspace_members (workspace_id, user_id, role) values (new.id, new.created_by, 'owner');

  insert into accounts (workspace_id, name, type) values (new.id, 'Conta principal', 'corrente');

  if new.kind = 'empresa' then
    insert into categories (workspace_id, name, type, color) values
      (new.id, 'Serviços / Fee mensal', 'receita', '#16a34a'),
      (new.id, 'Projetos', 'receita', '#22c55e'),
      (new.id, 'Outras receitas', 'receita', '#86efac'),
      (new.id, 'Folha e pró-labore', 'despesa', '#dc2626'),
      (new.id, 'Impostos', 'despesa', '#ea580c'),
      (new.id, 'Ferramentas e software', 'despesa', '#7c3aed'),
      (new.id, 'Mídia paga', 'despesa', '#2563eb'),
      (new.id, 'Freelancers', 'despesa', '#0891b2'),
      (new.id, 'Aluguel e escritório', 'despesa', '#ca8a04'),
      (new.id, 'Outras despesas', 'despesa', '#64748b');
  else
    insert into categories (workspace_id, name, type, color) values
      (new.id, 'Salário', 'receita', '#16a34a'),
      (new.id, 'Outras receitas', 'receita', '#86efac'),
      (new.id, 'Moradia', 'despesa', '#ca8a04'),
      (new.id, 'Alimentação', 'despesa', '#ea580c'),
      (new.id, 'Transporte', 'despesa', '#2563eb'),
      (new.id, 'Saúde', 'despesa', '#dc2626'),
      (new.id, 'Educação', 'despesa', '#7c3aed'),
      (new.id, 'Lazer', 'despesa', '#db2777'),
      (new.id, 'Assinaturas', 'despesa', '#0891b2'),
      (new.id, 'Outras despesas', 'despesa', '#64748b');
  end if;
  return new;
end $$;

create trigger on_workspace_created
  after insert on public.workspaces
  for each row execute function public.handle_new_workspace();

-- Aceita convites pendentes para o e-mail do usuário logado
create or replace function public.accept_pending_invites()
returns int language plpgsql security definer set search_path = public as $$
declare n int;
begin
  insert into workspace_members (workspace_id, user_id, role)
  select i.workspace_id, auth.uid(), 'member'
  from workspace_invites i
  where lower(i.email) = lower(auth.jwt()->>'email') and i.accepted_at is null
  on conflict do nothing;
  get diagnostics n = row_count;

  update workspace_invites set accepted_at = now()
  where lower(email) = lower(auth.jwt()->>'email') and accepted_at is null;
  return n;
end $$;

-- =====================================================================
-- RLS
-- =====================================================================
alter table public.profiles               enable row level security;
alter table public.workspaces             enable row level security;
alter table public.workspace_members      enable row level security;
alter table public.workspace_invites      enable row level security;
alter table public.accounts               enable row level security;
alter table public.categories             enable row level security;
alter table public.transactions           enable row level security;
alter table public.budgets                enable row level security;
alter table public.goals                  enable row level security;
alter table public.openfinance_connections enable row level security;

-- Perfis: vejo o meu e o de quem divide carteira comigo
create policy profiles_select on public.profiles for select using (
  id = auth.uid() or exists (
    select 1 from workspace_members m1
    join workspace_members m2 on m1.workspace_id = m2.workspace_id
    where m1.user_id = auth.uid() and m2.user_id = profiles.id)
);
create policy profiles_update on public.profiles for update using (id = auth.uid());

-- Carteiras
create policy ws_select on public.workspaces for select using (is_member(id) or created_by = auth.uid());
create policy ws_insert on public.workspaces for insert with check (created_by = auth.uid());
create policy ws_update on public.workspaces for update using (is_owner(id));
create policy ws_delete on public.workspaces for delete using (is_owner(id));

-- Membros
create policy wm_select on public.workspace_members for select using (is_member(workspace_id));
create policy wm_update on public.workspace_members for update using (is_owner(workspace_id));
create policy wm_delete on public.workspace_members for delete
  using (is_owner(workspace_id) or user_id = auth.uid());

-- Convites
create policy wi_select on public.workspace_invites for select using (is_member(workspace_id));
create policy wi_insert on public.workspace_invites for insert with check (is_owner(workspace_id));
create policy wi_delete on public.workspace_invites for delete using (is_owner(workspace_id));

-- Dados financeiros: todo membro vê e edita tudo da carteira
do $$
declare t text;
begin
  foreach t in array array['accounts','categories','transactions','budgets','goals','openfinance_connections'] loop
    execute format('create policy %1$s_all on public.%1$s for all using (is_member(workspace_id)) with check (is_member(workspace_id))', t);
  end loop;
end $$;