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 notes text;
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
);

create table if not exists public.cash_opening_balances (
  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,
  reference_month text not null,
  amount numeric(14,2) not null default 0,
  notes text
);

create unique index if not exists cash_opening_balances_user_month_idx
  on public.cash_opening_balances (user_id, reference_month);

create table if not exists public.seller_payment_proofs (
  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,
  seller_name text not null,
  reference_month text not null,
  payment_date date not null default current_date,
  amount numeric(14,2) not null default 0,
  file_path text not null,
  file_name text not null,
  file_type text,
  file_size bigint not null default 0,
  notes text
);

create index if not exists seller_payment_proofs_user_seller_date_idx
  on public.seller_payment_proofs (user_id, seller_name, payment_date desc);

insert into storage.buckets (id, name, public)
values ('seller-payment-proofs', 'seller-payment-proofs', false)
on conflict (id) do update
set public = false;

alter table public.financial_projects enable row level security;
alter table public.cash_entries enable row level security;
alter table public.cash_opening_balances enable row level security;
alter table public.seller_payment_proofs 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);

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

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

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

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

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

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

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

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

drop policy if exists "seller_payment_proofs_storage_select_own" on storage.objects;
create policy "seller_payment_proofs_storage_select_own"
  on storage.objects
  for select
  to authenticated
  using (
    bucket_id = 'seller-payment-proofs'
    and owner_id = (select auth.uid()::text)
  );

drop policy if exists "seller_payment_proofs_storage_insert_own" on storage.objects;
create policy "seller_payment_proofs_storage_insert_own"
  on storage.objects
  for insert
  to authenticated
  with check (
    bucket_id = 'seller-payment-proofs'
    and (storage.foldername(name))[1] = (select auth.uid()::text)
  );

drop policy if exists "seller_payment_proofs_storage_delete_own" on storage.objects;
create policy "seller_payment_proofs_storage_delete_own"
  on storage.objects
  for delete
  to authenticated
  using (
    bucket_id = 'seller-payment-proofs'
    and owner_id = (select auth.uid()::text)
  );