142 lines
5.5 KiB
SQL
142 lines
5.5 KiB
SQL
create table if not exists public.activity_categories (
|
|
id uuid primary key,
|
|
key text not null unique,
|
|
label_nl text not null,
|
|
sort_order integer not null check (sort_order >= 1),
|
|
is_active boolean not null default true,
|
|
created_at timestamptz not null default timezone('utc', now())
|
|
);
|
|
|
|
create table if not exists public.skip_reasons (
|
|
id uuid primary key,
|
|
key text not null unique,
|
|
label_nl text not null,
|
|
sort_order integer not null check (sort_order >= 1),
|
|
is_active boolean not null default true,
|
|
created_at timestamptz not null default timezone('utc', now())
|
|
);
|
|
|
|
create table if not exists public.activities (
|
|
id uuid primary key default gen_random_uuid(),
|
|
user_id uuid not null references auth.users (id) on delete cascade,
|
|
activity_date date not null,
|
|
source text not null default 'planned',
|
|
status text not null default 'planned',
|
|
name text not null,
|
|
category_id uuid not null references public.activity_categories (id),
|
|
duration_minutes integer not null,
|
|
impact_level text not null,
|
|
priority_level text not null,
|
|
skip_reason_id uuid references public.skip_reasons (id),
|
|
notes text,
|
|
created_at timestamptz not null default timezone('utc', now()),
|
|
updated_at timestamptz not null default timezone('utc', now()),
|
|
constraint activities_source_check
|
|
check (source in ('planned', 'ad_hoc')),
|
|
constraint activities_status_check
|
|
check (status in ('planned', 'completed', 'skipped', 'adjusted')),
|
|
constraint activities_name_check
|
|
check (char_length(trim(name)) between 1 and 120),
|
|
constraint activities_duration_minutes_check
|
|
check (duration_minutes > 0 and duration_minutes <= 720),
|
|
constraint activities_impact_level_check
|
|
check (impact_level in ('laag', 'midden', 'hoog')),
|
|
constraint activities_priority_level_check
|
|
check (priority_level in ('laag', 'normaal', 'hoog'))
|
|
);
|
|
|
|
create index if not exists activities_user_date_idx
|
|
on public.activities (user_id, activity_date);
|
|
|
|
create index if not exists activity_categories_sort_order_idx
|
|
on public.activity_categories (sort_order);
|
|
|
|
create index if not exists skip_reasons_sort_order_idx
|
|
on public.skip_reasons (sort_order);
|
|
|
|
grant select on table public.activity_categories to authenticated;
|
|
grant select on table public.skip_reasons to authenticated;
|
|
grant select, insert, update, delete on table public.activities to authenticated;
|
|
|
|
alter table public.activity_categories enable row level security;
|
|
alter table public.skip_reasons enable row level security;
|
|
alter table public.activities enable row level security;
|
|
|
|
drop trigger if exists set_activities_updated_at on public.activities;
|
|
create trigger set_activities_updated_at
|
|
before update on public.activities
|
|
for each row
|
|
execute function public.set_updated_at();
|
|
|
|
drop policy if exists "activity_categories_select_active" on public.activity_categories;
|
|
create policy "activity_categories_select_active"
|
|
on public.activity_categories
|
|
for select
|
|
to authenticated
|
|
using (is_active = true);
|
|
|
|
drop policy if exists "skip_reasons_select_active" on public.skip_reasons;
|
|
create policy "skip_reasons_select_active"
|
|
on public.skip_reasons
|
|
for select
|
|
to authenticated
|
|
using (is_active = true);
|
|
|
|
drop policy if exists "activities_select_own" on public.activities;
|
|
create policy "activities_select_own"
|
|
on public.activities
|
|
for select
|
|
to authenticated
|
|
using ((select auth.uid()) = user_id);
|
|
|
|
drop policy if exists "activities_insert_own" on public.activities;
|
|
create policy "activities_insert_own"
|
|
on public.activities
|
|
for insert
|
|
to authenticated
|
|
with check ((select auth.uid()) = user_id);
|
|
|
|
drop policy if exists "activities_update_own" on public.activities;
|
|
create policy "activities_update_own"
|
|
on public.activities
|
|
for update
|
|
to authenticated
|
|
using ((select auth.uid()) = user_id)
|
|
with check ((select auth.uid()) = user_id);
|
|
|
|
drop policy if exists "activities_delete_own" on public.activities;
|
|
create policy "activities_delete_own"
|
|
on public.activities
|
|
for delete
|
|
to authenticated
|
|
using ((select auth.uid()) = user_id);
|
|
|
|
insert into public.activity_categories (id, key, label_nl, sort_order, is_active)
|
|
values
|
|
('0d0d8b31-5e4c-4d1d-b5df-6b98df0a1001', 'huishouden', 'Huishouden', 1, true),
|
|
('0d0d8b31-5e4c-4d1d-b5df-6b98df0a1002', 'werk_studie', 'Werk of studie', 2, true),
|
|
('0d0d8b31-5e4c-4d1d-b5df-6b98df0a1003', 'administratie', 'Administratie', 3, true),
|
|
('0d0d8b31-5e4c-4d1d-b5df-6b98df0a1004', 'sociaal', 'Sociaal', 4, true),
|
|
('0d0d8b31-5e4c-4d1d-b5df-6b98df0a1005', 'beweging', 'Beweging', 5, true),
|
|
('0d0d8b31-5e4c-4d1d-b5df-6b98df0a1006', 'rust_herstel', 'Rust en herstel', 6, true),
|
|
('0d0d8b31-5e4c-4d1d-b5df-6b98df0a1007', 'reizen', 'Reizen', 7, true),
|
|
('0d0d8b31-5e4c-4d1d-b5df-6b98df0a1008', 'vrije_tijd', 'Vrije tijd', 8, true)
|
|
on conflict (key) do update
|
|
set
|
|
label_nl = excluded.label_nl,
|
|
sort_order = excluded.sort_order,
|
|
is_active = excluded.is_active;
|
|
|
|
insert into public.skip_reasons (id, key, label_nl, sort_order, is_active)
|
|
values
|
|
('9f4f1b75-f2a4-4d20-b80c-6f89e8142001', 'energie_te_laag', 'Energie te laag', 1, true),
|
|
('9f4f1b75-f2a4-4d20-b80c-6f89e8142002', 'prioriteit_veranderd', 'Prioriteit veranderde', 2, true),
|
|
('9f4f1b75-f2a4-4d20-b80c-6f89e8142003', 'praktische_belemmering', 'Praktische belemmering', 3, true),
|
|
('9f4f1b75-f2a4-4d20-b80c-6f89e8142004', 'duurde_langer_dan_verwacht', 'Vorige activiteit duurde langer', 4, true),
|
|
('9f4f1b75-f2a4-4d20-b80c-6f89e8142005', 'te_belastend', 'Te belastend', 5, true),
|
|
('9f4f1b75-f2a4-4d20-b80c-6f89e8142006', 'vergeten', 'Vergeten', 6, true)
|
|
on conflict (key) do update
|
|
set
|
|
label_nl = excluded.label_nl,
|
|
sort_order = excluded.sort_order,
|
|
is_active = excluded.is_active;
|