Database Setup

create table public.profiles (
  id uuid primary key references auth.users (id) on delete cascade,
  display_name text not null default 'User',
  avatar_url text,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create table public.tenants (
  id uuid primary key default gen_random_uuid(),
  name text not null check (char_length(name) between 1 and 120),
  slug text not null unique check (slug ~ '^[a-z0-9]+(?:-[a-z0-9]+)*$'),
  created_by uuid not null references public.profiles (id) on delete restrict,
  created_at timestamptz not null default now(),
  archived_at timestamptz
);

create table public.projects (
  id uuid primary key default gen_random_uuid(),
  tenant_id uuid not null references public.tenants (id) on delete cascade,
  name text not null check (char_length(name) between 1 and 120),
  description text,
  created_by uuid not null references public.profiles (id) on delete restrict,
  created_at timestamptz not null default now(),
  archived_at timestamptz
);

create table public.tasks (
  id uuid primary key default gen_random_uuid(),
  project_id uuid not null references public.projects (id) on delete cascade,
  title text not null check (char_length(title) between 1 and 240),
  status_column_id uuid not null references public.workflow_columns (id),
  start_date date,
  due_date date,
  priority public.task_priority not null default 'p1',
  order_rank numeric(18, 6) not null default 1000,
  description text,
  estimate numeric(10, 2),
  is_milestone boolean not null default false,
  completed_at timestamptz,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),
  constraint task_dates_valid check (
    due_date is null or start_date is null or due_date >= start_date
  )
);

create table public.workflow_columns (
  id uuid primary key default gen_random_uuid(),
  project_id uuid not null references public.projects (id) on delete cascade,
  name text not null check (char_length(name) between 1 and 80),
  kind public.workflow_column_kind not null default 'custom',
  position integer not null,
  wip_limit integer,
  definition_of_done text,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

create type public.project_role as enum ('owner', 'editor', 'viewer');
create type public.tenant_role as enum ('owner', 'admin', 'member');
create type public.task_priority as enum ('p0', 'p1', 'p2');
create type public.workflow_column_kind as enum (
  'backlog', 'todo', 'in_progress', 'done', 'custom'
);
create type public.dependency_type as enum ('FS');

-- Tenant permissions
select public.is_tenant_member(tenant_id);
select public.is_tenant_owner(tenant_id);
select public.current_tenant_role(tenant_id);

-- Project permissions
select public.is_project_member(project_id);
select public.can_edit_project(project_id);
select public.is_project_owner(project_id);
select public.current_project_role(project_id);

-- Get project metrics for dashboard
select public.dashboard_metrics(
  p_project_id := '44444444-4444-4444-4444-444444444444',
  p_days_window := 14
);

-- Example: Projects table policies
create policy "projects_select_members"
on public.projects for select to authenticated
using (public.is_project_member(id));

create policy "projects_insert_owner"
on public.projects for insert to authenticated
with check (
  created_by = auth.uid()
  and public.is_tenant_member(tenant_id)
);

create policy "projects_update_owner"
on public.projects for update to authenticated
using (public.is_project_owner(id));

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

create trigger touch_tasks_updated_at
before update on public.tasks
for each row execute procedure public.touch_updated_at();

-- Task queries
create index tasks_project_column_rank_idx
  on public.tasks (project_id, status_column_id, order_rank);

create index tasks_project_due_date_idx
  on public.tasks (project_id, due_date);

-- Lookups
create index task_assignees_user_idx
  on public.task_assignees (user_id);

create index project_members_user_idx
  on public.project_members (user_id);

create index tenant_members_user_idx
  on public.tenant_members (user_id);