-- 1) Таблицы
create extension if not exists pgcrypto;
create table if not exists public.admins (
id uuid primary key default gen_random_uuid(),
user_id uuid unique not null,
email text unique not null,
name text not null,
is_super_admin boolean not null default false,
created_at timestamptz not null default now()
);
create table if not exists public.points (
id text primary key,
name text not null,
city text not null,
type text not null check (type in ('cart','promo')),
capacity integer not null default 1,
days integer[] not null default array[1,2,3,4,5,6,0],
notes text,
created_at timestamptz not null default now()
);
create table if not exists public.employees (
id uuid primary key default gen_random_uuid(),
employee_id text unique not null,
fio text not null,
phone text,
telegram text,
status text not null default 'active' check (status in ('active','inactive')),
type text not null default 'promo' check (type in ('cart','promo')),
notes text,
created_at timestamptz not null default now()
);
create table if not exists public.assignments (
id uuid primary key default gen_random_uuid(),
employee_id text not null,
point_id text,
date date not null,
type text not null default 'shift' check (type in ('shift','absence')),
source text,
note text,
created_at timestamptz not null default now()
);
create table if not exists public.sales (
id uuid primary key default gen_random_uuid(),
employee_id text not null,
point_id text not null,
date date not null,
virtual_cards integer not null default 0,
plastic_cards integer not null default 0,
bot_registrations integer not null default 0,
created_at timestamptz not null default now()
);
create table if not exists public.portal_requests (
id uuid primary key default gen_random_uuid(),
employee_id text not null,
fio text not null,
phone text,
telegram text,
point_id text not null,
requested_dates jsonb not null default '[]'::jsonb,
emp_type text not null default 'promo' check (emp_type in ('cart','promo')),
comment text,
status text not null default 'new' check (status in ('new','approved','rejected')),
processed_at timestamptz,
created_at timestamptz not null default now()
);
create index if not exists idx_assignments_employee_id on public.assignments(employee_id);
create index if not exists idx_assignments_point_id on public.assignments(point_id);
create index if not exists idx_assignments_date on public.assignments(date);
create index if not exists idx_sales_employee_id on public.sales(employee_id);
create index if not exists idx_sales_point_id on public.sales(point_id);
create index if not exists idx_sales_date on public.sales(date);
create index if not exists idx_portal_requests_status on public.portal_requests(status);
-- 2) RLS
alter table public.admins enable row level security;
alter table public.points enable row level security;
alter table public.employees enable row level security;
alter table public.assignments enable row level security;
alter table public.sales enable row level security;
alter table public.portal_requests enable row level security;
drop policy if exists "admins_select_self" on public.admins;
drop policy if exists "admins_manage_all" on public.admins;
drop policy if exists "points_public_select" on public.points;
drop policy if exists "points_admin_manage" on public.points;
drop policy if exists "employees_public_select" on public.employees;
drop policy if exists "employees_admin_manage" on public.employees;
drop policy if exists "assignments_admin_manage" on public.assignments;
drop policy if exists "sales_public_select" on public.sales;
drop policy if exists "sales_admin_manage" on public.sales;
drop policy if exists "portal_requests_public_insert" on public.portal_requests;
drop policy if exists "portal_requests_admin_manage" on public.portal_requests;
create policy "admins_select_self"
on public.admins
for select
to authenticated
using (auth.uid() = user_id);
create policy "admins_manage_all"
on public.admins
for all
to authenticated
using (
exists (
select 1 from public.admins a
where a.user_id = auth.uid() and a.is_super_admin = true
)
)
with check (
exists (
select 1 from public.admins a
where a.user_id = auth.uid() and a.is_super_admin = true
)
);
create policy "points_public_select"
on public.points
for select
to anon, authenticated
using (true);
create policy "points_admin_manage"
on public.points
for all
to authenticated
using (
exists (
select 1 from public.admins a
where a.user_id = auth.uid()
)
)
with check (
exists (
select 1 from public.admins a
where a.user_id = auth.uid()
)
);
create policy "employees_public_select"
on public.employees
for select
to anon, authenticated
using (true);
create policy "employees_admin_manage"
on public.employees
for all
to authenticated
using (
exists (
select 1 from public.admins a
where a.user_id = auth.uid()
)
)
with check (
exists (
select 1 from public.admins a
where a.user_id = auth.uid()
)
);
create policy "assignments_admin_manage"
on public.assignments
for all
to authenticated
using (
exists (
select 1 from public.admins a
where a.user_id = auth.uid()
)
)
with check (
exists (
select 1 from public.admins a
where a.user_id = auth.uid()
)
);
create policy "sales_public_select"
on public.sales
for select
to anon, authenticated
using (true);
create policy "sales_admin_manage"
on public.sales
for all
to authenticated
using (
exists (
select 1 from public.admins a
where a.user_id = auth.uid()
)
)
with check (
exists (
select 1 from public.admins a
where a.user_id = auth.uid()
)
);
create policy "portal_requests_public_insert"
on public.portal_requests
for insert
to anon, authenticated
with check (status = 'new');
create policy "portal_requests_admin_manage"
on public.portal_requests
for all
to authenticated
using (
exists (
select 1 from public.admins a
where a.user_id = auth.uid()
)
)
with check (
exists (
select 1 from public.admins a
where a.user_id = auth.uid()
)
);
-- 3) Стартовые точки
insert into public.points (id, name, city, type, capacity, days, notes)
values
('pt-cart-tashkent', 'Картомобиль Ташкент', 'Ташкент', 'cart', 50, array[1,2,3,4,5,6,0], 'Основная точка'),
('pt-promo-gl', 'Golden Life', 'Ташкент', 'promo', 4, array[1,2,3,4,5,6,0], 'Промо-точка')
on conflict (id) do nothing;
