-- Central de Tarefas IA — migração inicial única
create extension if not exists pgcrypto;

do $$ begin
  create type public.task_status as enum ('inbox', 'todo', 'in_progress', 'done');
exception when duplicate_object then null; end $$;

do $$ begin
  create type public.task_priority as enum ('none', 'low', 'medium', 'high', 'urgent');
exception when duplicate_object then null; end $$;

do $$ begin
  create type public.time_sphere as enum ('importante', 'urgente', 'circunstancial');
exception when duplicate_object then null; end $$;

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

create table if not exists public.tasks (
  id uuid primary key default gen_random_uuid(),
  user_id uuid references auth.users(id) on delete cascade not null,
  title text not null check (char_length(btrim(title)) > 0),
  notes text,
  status public.task_status not null default 'inbox',
  priority public.task_priority not null default 'none',
  start_date date,
  due_date date,
  completed_at timestamptz,
  is_important boolean not null default false,
  is_urgent boolean not null default false,
  time_sphere public.time_sphere,
  parent_task_id uuid references public.tasks(id) on delete cascade,
  position double precision not null default 0,
  is_focus_today boolean not null default false,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create index if not exists tasks_user_id_idx on public.tasks(user_id);
create index if not exists tasks_parent_task_id_idx on public.tasks(parent_task_id);
create index if not exists tasks_user_due_date_idx on public.tasks(user_id, due_date);

drop trigger if exists tasks_updated on public.tasks;
create trigger tasks_updated before update on public.tasks
for each row execute function public.set_updated_at();

create table if not exists public.checklist_items (
  id uuid primary key default gen_random_uuid(),
  task_id uuid references public.tasks(id) on delete cascade not null,
  user_id uuid references auth.users(id) on delete cascade not null,
  title text not null check (char_length(btrim(title)) > 0),
  is_done boolean not null default false,
  position double precision not null default 0,
  created_at timestamptz not null default now()
);
create index if not exists checklist_items_task_id_idx on public.checklist_items(task_id);

create table if not exists public.tags (
  id uuid primary key default gen_random_uuid(),
  user_id uuid references auth.users(id) on delete cascade not null,
  name text not null check (char_length(btrim(name)) > 0),
  color text not null default 'slate',
  created_at timestamptz not null default now(),
  unique(user_id, name)
);
create index if not exists tags_user_id_idx on public.tags(user_id);

create table if not exists public.task_tags (
  task_id uuid references public.tasks(id) on delete cascade not null,
  tag_id uuid references public.tags(id) on delete cascade not null,
  user_id uuid references auth.users(id) on delete cascade not null,
  primary key(task_id, tag_id)
);
create index if not exists task_tags_user_id_idx on public.task_tags(user_id);

create table if not exists public.attachments (
  id uuid primary key default gen_random_uuid(),
  task_id uuid references public.tasks(id) on delete cascade not null,
  user_id uuid references auth.users(id) on delete cascade not null,
  file_name text not null,
  file_path text not null,
  file_size bigint,
  mime_type text,
  created_at timestamptz not null default now()
);
create index if not exists attachments_task_id_idx on public.attachments(task_id);

alter table public.tasks enable row level security;
alter table public.checklist_items enable row level security;
alter table public.tags enable row level security;
alter table public.task_tags enable row level security;
alter table public.attachments enable row level security;

drop policy if exists "own tasks" on public.tasks;
create policy "own tasks" on public.tasks for all to authenticated
using (auth.uid() = user_id) with check (auth.uid() = user_id);

drop policy if exists "own checklist" on public.checklist_items;
create policy "own checklist" on public.checklist_items for all to authenticated
using (auth.uid() = user_id) with check (auth.uid() = user_id);

drop policy if exists "own tags" on public.tags;
create policy "own tags" on public.tags for all to authenticated
using (auth.uid() = user_id) with check (auth.uid() = user_id);

drop policy if exists "own task tags" on public.task_tags;
create policy "own task tags" on public.task_tags for all to authenticated
using (
  auth.uid() = user_id
  and exists (select 1 from public.tasks t where t.id = task_id and t.user_id = auth.uid())
  and exists (select 1 from public.tags g where g.id = tag_id and g.user_id = auth.uid())
) with check (
  auth.uid() = user_id
  and exists (select 1 from public.tasks t where t.id = task_id and t.user_id = auth.uid())
  and exists (select 1 from public.tags g where g.id = tag_id and g.user_id = auth.uid())
);

drop policy if exists "own attachments" on public.attachments;
create policy "own attachments" on public.attachments for all to authenticated
using (
  auth.uid() = user_id
  and exists (select 1 from public.tasks t where t.id = task_id and t.user_id = auth.uid())
) with check (
  auth.uid() = user_id
  and exists (select 1 from public.tasks t where t.id = task_id and t.user_id = auth.uid())
);

grant select, insert, update, delete on public.tasks to authenticated;
grant select, insert, update, delete on public.checklist_items to authenticated;
grant select, insert, update, delete on public.tags to authenticated;
grant select, insert, update, delete on public.task_tags to authenticated;
grant select, insert, update, delete on public.attachments to authenticated;

grant all on public.tasks to service_role;
grant all on public.checklist_items to service_role;
grant all on public.tags to service_role;
grant all on public.task_tags to service_role;
grant all on public.attachments to service_role;

create or replace function public.seed_example_tasks()
returns trigger
language plpgsql
security definer
set search_path = public
as $$
declare
  welcome_id uuid;
begin
  insert into public.tasks(user_id, title, notes, status, position)
  values (new.id, 'Bem-vindo(a) ao TaskFlow 👋', 'Use este espaço para organizar tarefas, prioridades e sua rotina.', 'todo', 10)
  returning id into welcome_id;

  insert into public.checklist_items(task_id, user_id, title, position) values
    (welcome_id, new.id, 'Criar sua primeira tarefa', 10),
    (welcome_id, new.id, 'Definir uma prioridade', 20),
    (welcome_id, new.id, 'Marcar uma tarefa como concluída', 30);

  insert into public.tasks(user_id, title, status, priority, due_date, position)
  values (new.id, 'Planejar o dia de amanhã', 'todo', 'high', current_date + 1, 20);

  insert into public.tasks(user_id, title, status, priority, position)
  values (new.id, 'Ler artigo sobre produtividade', 'inbox', 'low', 30);

  return new;
end;
$$;

revoke execute on function public.seed_example_tasks() from public, anon, authenticated;
grant execute on function public.seed_example_tasks() to service_role;

drop trigger if exists on_auth_user_created_seed on auth.users;
create trigger on_auth_user_created_seed
after insert on auth.users
for each row execute function public.seed_example_tasks();