create extension if not exists pgcrypto;

create table if not exists public.financial_projects (
  id uuid primary key default gen_random_uuid(),
  created_at timestamptz not null default timezone('utc', now()),
  user_id uuid not null default auth.uid() references auth.users (id) on delete cascade,
  cliente text not null,
  mes text not null,
  placas integer not null default 0,
  valor_total numeric(14,2) not null default 0,
  unidade text not null default 'Matriz',
  pagamento_status text not null default 'Pago completo',
  valor_pago numeric(14,2) not null default 0,
  diferenca_pagamento numeric(14,2) not null default 0,
  kit numeric(14,2) not null default 0,
  mao_obra numeric(14,2) not null default 0,
  engenheiro numeric(14,2) not null default 0,
  imposto numeric(14,2) not null default 0,
  pg_instalacao numeric(14,2) not null default 0,
  material_ca numeric(14,2) not null default 0,
  vendedor numeric(14,2) not null default 0,
  sobra numeric(14,2) not null default 0,
  a_receber numeric(14,2) not null default 0,
  responsavel text not null,
  fornecedora text not null,
  instalacao text not null,
  status text not null default 'A Receber'
);

alter table public.financial_projects add column if not exists unidade text not null default 'Matriz';
alter table public.financial_projects add column if not exists pagamento_status text not null default 'Pago completo';
alter table public.financial_projects add column if not exists valor_pago numeric(14,2) not null default 0;
alter table public.financial_projects add column if not exists diferenca_pagamento numeric(14,2) not null default 0;

update public.financial_projects
set unidade = 'Matriz'
where unidade is null or unidade = '';

update public.financial_projects
set pagamento_status = 'Pago completo'
where pagamento_status is null or pagamento_status = '';

update public.financial_projects
set valor_pago = valor_total
where (valor_pago is null or valor_pago = 0)
  and pagamento_status = 'Pago completo';

update public.financial_projects
set diferenca_pagamento = case
  when pagamento_status = 'Pendente de pagamento' then valor_total
  when pagamento_status = 'Pagamento parcial' then greatest(valor_total - coalesce(valor_pago, 0), 0)
  else 0
end;

create table if not exists public.cash_entries (
  id uuid primary key default gen_random_uuid(),
  created_at timestamptz not null default timezone('utc', now()),
  user_id uuid not null default auth.uid() references auth.users (id) on delete cascade,
  cash_box text not null,
  movement_type text not null,
  company text not null,
  category text not null,
  description text not null,
  amount numeric(14,2) not null default 0,
  payment_date date not null default current_date,
  client_name text,
  plan_name text,
  notes text
);

alter table public.financial_projects enable row level security;
alter table public.cash_entries enable row level security;

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

drop policy if exists "financial_projects_insert_own" on public.financial_projects;
create policy "financial_projects_insert_own"
  on public.financial_projects
  for insert
  to authenticated
  with check (auth.uid() = user_id);

drop policy if exists "financial_projects_update_own" on public.financial_projects;
create policy "financial_projects_update_own"
  on public.financial_projects
  for update
  to authenticated
  using (auth.uid() = user_id)
  with check (auth.uid() = user_id);

drop policy if exists "financial_projects_delete_own" on public.financial_projects;
create policy "financial_projects_delete_own"
  on public.financial_projects
  for delete
  to authenticated
  using (auth.uid() = user_id);

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

drop policy if exists "cash_entries_insert_own" on public.cash_entries;
create policy "cash_entries_insert_own"
  on public.cash_entries
  for insert
  to authenticated
  with check (auth.uid() = user_id);

drop policy if exists "cash_entries_update_own" on public.cash_entries;
create policy "cash_entries_update_own"
  on public.cash_entries
  for update
  to authenticated
  using (auth.uid() = user_id)
  with check (auth.uid() = user_id);

drop policy if exists "cash_entries_delete_own" on public.cash_entries;
create policy "cash_entries_delete_own"
  on public.cash_entries
  for delete
  to authenticated
  using (auth.uid() = user_id);