supersam/supabase/schema.sql

create extension if not exists pgcrypto;

create or replace function public.next_order_group_sms_check_at(
  start_from timestamptz default now(),
  delay interval default interval '0 minutes'
)
returns timestamptz
language plpgsql
stable
as $$
declare
  v_timezone text := 'Europe/Simferopol';
  v_local_time timestamp;
  v_local_date date;
  v_work_start timestamp;
  v_work_end timestamp;
  v_candidate timestamp;
begin
  v_local_time := (start_from at time zone v_timezone) + delay;
  v_local_date := v_local_time::date;
  v_work_start := v_local_date + time '09:00';
  v_work_end := v_local_date + time '20:00';

  if v_local_time < v_work_start then
    v_candidate := v_work_start;
  elsif v_local_time >= v_work_end then
    v_candidate := (v_local_date + 1) + time '09:00';
  else
    v_candidate := v_local_time;
  end if;

  return v_candidate at time zone v_timezone;
end;
$$;

create table if not exists public.roles (
  id uuid primary key default gen_random_uuid(),
  name text not null unique,
  permissions jsonb not null default '[]'::jsonb,
  created_at timestamptz not null default timezone('utc', now())
);

create table if not exists public.users (
  id uuid primary key references auth.users (id) on delete cascade,
  email text not null unique,
  name text not null,
  role_id uuid not null references public.roles (id),
  last_login timestamptz,
  created_at timestamptz not null default timezone('utc', now())
);

create table if not exists public.orders (
  id uuid primary key default gen_random_uuid(),
  order_number text not null unique,
  customer jsonb not null,
  status text not null,
  delivery_agreement_status text not null default 'Не начато',
  manager_id uuid references public.users (id),
  logistician_id uuid references public.users (id),
  assigned_driver_id uuid references public.users (id),
  ready_for_delivery_at timestamptz,
  delivery_flow_started_at timestamptz,
  delivery_flow_source text,
  created_at timestamptz not null default timezone('utc', now()),
  updated_at timestamptz not null default timezone('utc', now())
);

create table if not exists public.order_logisticians (
  id uuid primary key default gen_random_uuid(),
  order_id uuid not null references public.orders (id) on delete cascade,
  logistician_id uuid not null references public.users (id) on delete cascade,
  assigned_at timestamptz not null default timezone('utc', now()),
  assigned_by uuid references public.users (id),
  unique (order_id, logistician_id)
);

create table if not exists public.order_history (
  id uuid primary key default gen_random_uuid(),
  order_id uuid not null references public.orders (id) on delete cascade,
  action text not null,
  old_status text,
  new_status text,
  user_id uuid references public.users (id),
  metadata jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default timezone('utc', now())
);

create table if not exists public.delivery_slots (
  id uuid primary key default gen_random_uuid(),
  order_id uuid not null references public.orders (id) on delete cascade,
  delivery_date date not null,
  delivery_time text not null,
  logistician_id uuid references public.users (id),
  status text not null default 'pending_confirmation',
  selected_by_client_at timestamptz,
  created_at timestamptz not null default timezone('utc', now())
);

create table if not exists public.chat_messages (
  id uuid primary key default gen_random_uuid(),
  order_id uuid not null references public.orders (id) on delete cascade,
  sender_name text,
  sender_type text not null check (sender_type in ('client', 'bot', 'operator', 'system')),
  channel text not null check (channel in ('telegram', 'vk', 'messenger_max', 'sms', 'email')),
  text text not null,
  external_message_id text,
  payload jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default timezone('utc', now())
);

create table if not exists public.error_logs (
  id uuid primary key default gen_random_uuid(),
  order_id uuid references public.orders (id) on delete set null,
  provider text,
  level text not null default 'error',
  message text not null,
  details jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default timezone('utc', now())
);

create table if not exists public.order_groups (
  id uuid primary key default gen_random_uuid(),
  group_key text not null,
  customer jsonb,
  order_numbers text[] not null default '{}',
  status text not null default 'ready_for_notification',
  delivery_status text not null default 'pending_confirmation',
  sms_sent_at timestamptz,
  created_at timestamptz not null default timezone('utc', now()),
  updated_at timestamptz not null default timezone('utc', now()),
  created_from_exchange_at timestamptz,
  source_key text,
  customer_name text,
  customer_phone text,
  customer_phone_normalized text,
  customer_date text,
  orders_total integer,
  orders_ready integer,
  orders_not_ready integer,
  source_orders jsonb,
  delivery_invitation_id uuid,
  delivery_link text,
  notification_status text not null default 'not_started',
  sms_attempts integer not null default 0,
  first_sms_sent_at timestamptz,
  second_sms_sent_at timestamptz,
  last_sms_error text,
  next_notification_check_at timestamptz,
  delivery_date date,
  delivery_time text,
  delivery_address text,
  manual_confirmation_at timestamptz,
  assigned_driver_id uuid references public.users (id)
);

create table if not exists public.delivery_invitations (
  id uuid primary key default gen_random_uuid(),
  order_id uuid references public.orders (id) on delete cascade unique,
  order_group_id uuid references public.order_groups (id) on delete cascade,
  token_hash text not null unique,
  state text not null default 'awaiting_choice',
  order_number text,
  customer_name text,
  customer_phone text,
  customer_messenger text,
  available_slots text[] not null default array['Первая половина дня', 'Вторая половина дня'],
  expires_at timestamptz,
  revoked_at timestamptz,
  access_count integer not null default 0,
  last_accessed_at timestamptz,
  delivery_date date,
  delivery_time text,
  sent_at timestamptz,
  opened_at timestamptz,
  confirmed_at timestamptz,
  logistics_transferred_at timestamptz,
  paid_storage_at timestamptz,
  delivered_at timestamptz,
  created_at timestamptz not null default timezone('utc', now()),
  updated_at timestamptz not null default timezone('utc', now())
);

create table if not exists public.integration_events (
  id uuid primary key default gen_random_uuid(),
  order_id uuid references public.orders (id) on delete set null,
  event_type text not null,
  direction text not null default 'internal',
  source text not null default 'supabase-function',
  status text not null default 'success',
  payload jsonb not null default '{}'::jsonb,
  error_message text,
  created_at timestamptz not null default timezone('utc', now())
);

create table if not exists public.rate_limits (
  id uuid primary key default gen_random_uuid(),
  scope text not null,
  rate_key text not null,
  window_start timestamptz not null,
  count integer not null default 1,
  blocked_until timestamptz,
  created_at timestamptz not null default timezone('utc', now()),
  updated_at timestamptz not null default timezone('utc', now()),
  unique (scope, rate_key, window_start)
);

alter table public.orders add column if not exists delivery_agreement_status text not null default 'Не начато';
alter table public.orders add column if not exists assigned_driver_id uuid references public.users (id);
alter table public.orders add column if not exists ready_for_delivery_at timestamptz;
alter table public.orders add column if not exists delivery_flow_started_at timestamptz;
alter table public.orders add column if not exists delivery_flow_source text;
alter table public.chat_messages drop constraint if exists chat_messages_channel_check;
alter table public.chat_messages
  add constraint chat_messages_channel_check
  check (channel in ('telegram', 'vk', 'messenger_max', 'sms', 'email'));

alter table public.delivery_invitations add column if not exists state text not null default 'awaiting_choice';
alter table public.delivery_invitations alter column order_id drop not null;
alter table public.delivery_invitations add column if not exists order_group_id uuid references public.order_groups(id) on delete cascade;
alter table public.delivery_invitations add column if not exists expires_at timestamptz;
alter table public.delivery_invitations add column if not exists revoked_at timestamptz;
alter table public.delivery_invitations add column if not exists access_count integer not null default 0;
alter table public.delivery_invitations add column if not exists last_accessed_at timestamptz;
alter table public.delivery_invitations add column if not exists delivery_date date;
alter table public.delivery_invitations add column if not exists delivery_time text;
alter table public.delivery_invitations add column if not exists sent_at timestamptz;
alter table public.delivery_invitations add column if not exists opened_at timestamptz;
alter table public.delivery_invitations add column if not exists confirmed_at timestamptz;
alter table public.delivery_invitations add column if not exists logistics_transferred_at timestamptz;
alter table public.delivery_invitations add column if not exists paid_storage_at timestamptz;
alter table public.delivery_invitations add column if not exists delivered_at timestamptz;
alter table public.delivery_invitations add column if not exists updated_at timestamptz not null default timezone('utc', now());

alter table public.order_groups add column if not exists delivery_invitation_id uuid references public.delivery_invitations(id) on delete set null;
alter table public.order_groups add column if not exists delivery_link text;
alter table public.order_groups add column if not exists notification_status text not null default 'not_started';
alter table public.order_groups add column if not exists sms_attempts integer not null default 0;
alter table public.order_groups add column if not exists first_sms_sent_at timestamptz;
alter table public.order_groups add column if not exists second_sms_sent_at timestamptz;
alter table public.order_groups add column if not exists last_sms_error text;
alter table public.order_groups add column if not exists next_notification_check_at timestamptz;
alter table public.order_groups add column if not exists delivery_date date;
alter table public.order_groups add column if not exists delivery_time text;
alter table public.order_groups add column if not exists synced_to_1c_at timestamptz;

alter table public.orders add column if not exists source_order_number text;
alter table public.orders add column if not exists source_order_date date;
alter table public.orders add column if not exists source_customer_name text;
alter table public.orders add column if not exists source_customer_phone text;
alter table public.orders add column if not exists source_customer_email text;
alter table public.orders add column if not exists source_customer_city text;
alter table public.orders add column if not exists source_total_sum numeric;
alter table public.orders add column if not exists source_paid_at timestamptz;
alter table public.orders add column if not exists source_gateway text;
alter table public.orders add column if not exists source_associated_bills_text text;
alter table public.orders add column if not exists source_production_at timestamptz;
alter table public.orders add column if not exists source_saw_at timestamptz;
alter table public.orders add column if not exists source_glue_at timestamptz;
alter table public.orders add column if not exists source_h_glue_at timestamptz;
alter table public.orders add column if not exists source_curve_at timestamptz;
alter table public.orders add column if not exists source_accept_at timestamptz;
alter table public.orders add column if not exists source_ship_at timestamptz;
alter table public.orders add column if not exists source_payload jsonb;
alter table public.orders add column if not exists delivery_set_key text;
alter table public.orders add column if not exists delivery_set_name text;
alter table public.orders add column if not exists delivery_set_status text;
alter table public.orders add column if not exists delivery_set_ready_at timestamptz;
alter table public.orders add column if not exists delivery_ready_reason text;
alter table public.orders add column if not exists source_sms_legacy_at timestamptz;

comment on column public.orders.source_sms_legacy_at is 'Informational only: legacy 1C SMS timestamp. Must NOT be used to start new delivery automation scenarios.';

alter table public.integration_events add column if not exists direction text not null default 'internal';
alter table public.integration_events add column if not exists source text not null default 'supabase-function';
alter table public.integration_events add column if not exists status text not null default 'success';
alter table public.integration_events add column if not exists payload jsonb not null default '{}'::jsonb;
alter table public.integration_events add column if not exists error_message text;
alter table public.rate_limits add column if not exists scope text not null;
alter table public.rate_limits add column if not exists rate_key text not null;
alter table public.rate_limits add column if not exists window_start timestamptz not null;
alter table public.rate_limits add column if not exists count integer not null default 1;
alter table public.rate_limits add column if not exists blocked_until timestamptz;
alter table public.rate_limits add column if not exists created_at timestamptz not null default timezone('utc', now());
alter table public.rate_limits add column if not exists updated_at timestamptz not null default timezone('utc', now());

create index if not exists idx_orders_delivery_set_key on public.orders (delivery_set_key);
create index if not exists idx_orders_delivery_set_status on public.orders (delivery_set_status);
create index if not exists idx_orders_source_accept_at on public.orders (source_accept_at);
create index if not exists idx_orders_source_ship_at on public.orders (source_ship_at);

insert into public.roles (name, permissions)
values
  (
    'manager',
    '["orders.create","orders.update.own","orders.read.own","comments.manage"]'::jsonb
  ),
  (
    'production_lead',
    '["orders.read.all","production.queue.manage","orders.status.production"]'::jsonb
  ),
  (
    'logistician',
    '["orders.read.assigned","delivery.manage","chatbots.manage"]'::jsonb
  ),
  (
    'driver',
    '["orders.read.assigned_driver","orders.status.driver"]'::jsonb
  ),
  (
    'admin',
    '["*"]'::jsonb
  )
on conflict (name) do nothing;

create or replace function public.set_updated_at()
returns trigger
language plpgsql
as $$
begin
  new.updated_at = timezone('utc', now());
  return new;
end;
$$;

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

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

create or replace function public.current_role_name()
returns text
language sql
stable
security definer
set search_path = public
as $$
  select r.name
  from public.users u
  join public.roles r on r.id = u.role_id
  where u.id = auth.uid()
$$;
-- Disable row-level security for this function so it can read users
-- without triggering infinite recursion via users RLS policies.
alter function public.current_role_name() set row_security = off;

create or replace function public.handle_new_user()
returns trigger
language plpgsql
security definer
set search_path = public
as $$
declare
  default_role_id uuid;
begin
  select id
  into default_role_id
  from public.roles
  where name = coalesce(new.raw_user_meta_data ->> 'role', 'manager')
  limit 1;

  if default_role_id is null then
    select id into default_role_id from public.roles where name = 'manager' limit 1;
  end if;

  insert into public.users (id, email, name, role_id, last_login)
  values (
    new.id,
    new.email,
    coalesce(new.raw_user_meta_data ->> 'name', split_part(new.email, '@', 1)),
    default_role_id,
    timezone('utc', now())
  )
  on conflict (id) do update
  set email = excluded.email,
      last_login = timezone('utc', now());

  return new;
end;
$$;

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

create or replace function public.log_order_status_change()
returns trigger
language plpgsql
security definer
as $$
begin
  if tg_op = 'INSERT' then
    insert into public.order_history (order_id, action, old_status, new_status, user_id)
    values (new.id, 'Создан заказ', null, new.status, auth.uid());
    return new;
  end if;

  if old.status is distinct from new.status then
    insert into public.order_history (order_id, action, old_status, new_status, user_id)
    values (new.id, 'Изменение статуса', old.status, new.status, auth.uid());
  end if;

  if old.delivery_agreement_status is distinct from new.delivery_agreement_status then
    insert into public.order_history (order_id, action, old_status, new_status, user_id, metadata)
    values (
      new.id,
      'Изменение согласования доставки',
      old.delivery_agreement_status,
      new.delivery_agreement_status,
      auth.uid(),
      jsonb_build_object('scope', 'delivery_agreement')
    );
  end if;

  return new;
end;
$$;

drop trigger if exists orders_history_insert on public.orders;
create trigger orders_history_insert
after insert or update on public.orders
for each row
execute function public.log_order_status_change();

create index if not exists idx_users_role_id on public.users (role_id);
create index if not exists idx_orders_status on public.orders (status);
create index if not exists idx_orders_manager_id on public.orders (manager_id);
create index if not exists idx_orders_logistician_id on public.orders (logistician_id);
create index if not exists idx_orders_assigned_driver_id on public.orders (assigned_driver_id);
create index if not exists idx_orders_ready_for_delivery_at on public.orders (ready_for_delivery_at);
create index if not exists idx_orders_delivery_flow_started_at on public.orders (delivery_flow_started_at);
create index if not exists idx_orders_created_at on public.orders (created_at desc);
create index if not exists idx_order_logisticians_order_id on public.order_logisticians (order_id);
create index if not exists idx_order_logisticians_logistician_id on public.order_logisticians (logistician_id);
create index if not exists idx_order_history_order_id on public.order_history (order_id, created_at desc);
create index if not exists idx_delivery_slots_order_id on public.delivery_slots (order_id);
create index if not exists idx_delivery_slots_logistician_id on public.delivery_slots (logistician_id);
create index if not exists idx_delivery_slots_selected_by_client_at on public.delivery_slots (selected_by_client_at);
create index if not exists idx_chat_messages_order_id on public.chat_messages (order_id, created_at desc);
create index if not exists idx_chat_messages_external_message_id on public.chat_messages (external_message_id);
create unique index if not exists idx_chat_messages_channel_external_unique
on public.chat_messages (channel, external_message_id)
where external_message_id is not null;
create index if not exists idx_orders_search on public.orders using gin (
  to_tsvector(
    'simple',
    coalesce(order_number, '') || ' ' || coalesce(customer ->> 'name', '') || ' ' || coalesce(customer ->> 'phone', '')
  )
);
create index if not exists idx_chat_messages_search on public.chat_messages using gin (
  to_tsvector('russian', coalesce(text, ''))
);
create index if not exists idx_delivery_invitations_order_id on public.delivery_invitations (order_id);
create index if not exists idx_delivery_invitations_order_group_id on public.delivery_invitations (order_group_id);
create index if not exists idx_delivery_invitations_token_hash on public.delivery_invitations (token_hash);
create index if not exists idx_delivery_invitations_state on public.delivery_invitations (state);
create index if not exists idx_delivery_invitations_expires_at on public.delivery_invitations (expires_at);
create index if not exists idx_order_groups_status on public.order_groups (status);
create index if not exists idx_order_groups_delivery_status on public.order_groups (delivery_status);
create index if not exists idx_order_groups_notification_status on public.order_groups (notification_status, next_notification_check_at);
create index if not exists idx_order_groups_synced_to_1c on public.order_groups (synced_to_1c_at);
create index if not exists idx_integration_events_order_id on public.integration_events (order_id, created_at desc);
create index if not exists idx_integration_events_event_type on public.integration_events (event_type);
create index if not exists idx_rate_limits_scope_key_window on public.rate_limits (scope, rate_key, window_start desc);
create index if not exists idx_rate_limits_blocked_until on public.rate_limits (blocked_until);

create or replace function public.check_rate_limit(
  p_scope text,
  p_key text,
  p_max_count integer,
  p_window_seconds integer,
  p_block_seconds integer default 0
)
returns table (
  allowed boolean,
  current_count integer,
  limit_count integer,
  blocked_until timestamptz,
  window_start timestamptz
)
language plpgsql
security definer
set search_path = public
as $$
declare
  v_now timestamptz := timezone('utc', now());
  v_window_start timestamptz;
  v_count integer;
  v_blocked_until timestamptz;
begin
  if p_max_count <= 0 then
    raise exception 'max_count must be positive';
  end if;

  if p_window_seconds <= 0 then
    raise exception 'window_seconds must be positive';
  end if;

  v_window_start := to_timestamp(floor(extract(epoch from v_now) / p_window_seconds) * p_window_seconds);

  select rl.blocked_until
  into v_blocked_until
  from public.rate_limits rl
  where rl.scope = p_scope
    and rl.rate_key = p_key
    and rl.blocked_until is not null
    and rl.blocked_until > v_now
  order by rl.blocked_until desc
  limit 1;

  if v_blocked_until is not null then
    return query
    select false, 0, p_max_count, v_blocked_until, v_window_start;
    return;
  end if;

  insert into public.rate_limits (scope, rate_key, window_start, count, blocked_until)
  values (p_scope, p_key, v_window_start, 1, null)
  on conflict (scope, rate_key, window_start)
  do update set
    count = public.rate_limits.count + 1,
    blocked_until = case
      when public.rate_limits.count + 1 > p_max_count and p_block_seconds > 0 then greatest(
        coalesce(public.rate_limits.blocked_until, v_now),
        v_now + make_interval(secs => p_block_seconds)
      )
      else public.rate_limits.blocked_until
    end,
    updated_at = v_now
  returning count, blocked_until
  into v_count, v_blocked_until;

  return query
  select
    v_count <= p_max_count and (v_blocked_until is null or v_blocked_until <= v_now),
    v_count,
    p_max_count,
    v_blocked_until,
    v_window_start;
end;
$$;

create or replace function public.get_delivery_invitation_by_token(p_token text)
returns jsonb
language plpgsql
security definer
set search_path = public, extensions
as $$
declare
  v_invitation public.delivery_invitations%rowtype;
  v_group public.order_groups%rowtype;
  v_order record;
  v_token_hash text;
  v_state text;
  v_order_number text;
  v_customer_name text;
  v_customer_phone text;
  v_order_items jsonb;
  v_order_numbers jsonb;
  v_now timestamptz := timezone('utc', now());
begin
  if nullif(trim(coalesce(p_token, '')), '') is null then
    raise exception 'token is required';
  end if;

  v_token_hash := encode(digest(p_token, 'sha256'), 'hex');

  select *
  into v_invitation
  from public.delivery_invitations
  where token_hash = v_token_hash;

  if not found then
    raise exception 'Invitation not found';
  end if;

  if v_invitation.revoked_at is not null then
    raise exception 'Invitation expired';
  end if;

  if v_invitation.expires_at is not null and v_invitation.expires_at <= v_now then
    raise exception 'Invitation expired';
  end if;

  if v_invitation.order_group_id is not null then
    select *
    into v_group
    from public.order_groups
    where id = v_invitation.order_group_id;

    if not found then
      raise exception 'Order group not found';
    end if;

    v_state := case
      when v_group.delivery_status = 'agreed' then 'agreed'
      when v_group.delivery_status = 'delivered' then 'delivered'
      when v_invitation.state in ('awaiting_choice', 'opened', 'reminder_sent') then v_invitation.state
      else 'default'
    end;

    update public.delivery_invitations
    set
      opened_at = case
        when v_state in ('awaiting_choice', 'opened', 'reminder_sent') and opened_at is null then v_now
        else opened_at
      end,
      access_count = coalesce(access_count, 0) + 1,
      last_accessed_at = v_now
    where id = v_invitation.id
    returning * into v_invitation;

    v_order_number := coalesce(
      nullif(v_invitation.order_number, ''),
      to_jsonb(v_group.order_numbers) ->> 0,
      nullif(v_group.group_key, '')
    );
    v_customer_name := case
      when length(coalesce(nullif(v_group.customer_name, ''), nullif(v_invitation.customer_name, ''))) > 0
        then left(coalesce(nullif(v_group.customer_name, ''), nullif(v_invitation.customer_name, '')), 1) || '.'
        else null
    end;
    v_customer_phone := case
      when length(coalesce(nullif(v_group.customer_phone, ''), nullif(v_group.customer_phone_normalized, ''), nullif(v_invitation.customer_phone, ''))) >= 4
        then '+7 *** ***-' || right(coalesce(nullif(v_group.customer_phone, ''), nullif(v_group.customer_phone_normalized, ''), nullif(v_invitation.customer_phone, '')), 2)
        else coalesce(nullif(v_group.customer_phone, ''), nullif(v_group.customer_phone_normalized, ''), nullif(v_invitation.customer_phone, ''))
    end;
    select coalesce(
      jsonb_agg(jsonb_build_object('name', order_number, 'quantity', '')),
      '[]'::jsonb
    )
    into v_order_items
    from jsonb_array_elements_text(
      case
        when jsonb_typeof(to_jsonb(v_group.order_numbers)) = 'array' then to_jsonb(v_group.order_numbers)
        else '[]'::jsonb
      end
    ) as order_number;

    return jsonb_build_object(
      'ok', true,
      'invitation', jsonb_build_object(
        'orderId', coalesce(v_invitation.order_group_id, v_group.id)::text,
        'orderGroupId', coalesce(v_invitation.order_group_id, v_group.id)::text,
        'state', v_state,
        'token', p_token,
        'orderNumber', v_order_number,
        'customerName', v_customer_name,
        'customerPhone', v_customer_phone,
        'orderItems', v_order_items,
        'availableSlots', coalesce(to_jsonb(v_invitation.available_slots), '[]'::jsonb),
        'deliveryDate', v_invitation.delivery_date,
        'deliveryTime', v_invitation.delivery_time,
        'orderStatus', null,
        'deliveryAgreementStatus', null
      )
    );
  end if;

  select id, order_number, status, delivery_agreement_status, customer
  into v_order
  from public.orders
  where id = v_invitation.order_id;

  if not found then
    raise exception 'Order not found';
  end if;

  v_state := case v_order.status
    when 'Ожидает ответа клиента' then 'awaiting_choice'
    when 'Ожидает согласования доставки' then 'opened'
    when 'Напоминание отправлено' then 'reminder_sent'
    when 'Переход отправлен' then 'reminder_sent'
    when 'Передан логисту' then 'transferred_to_logistics'
    when 'Платное хранение' then 'paid_storage'
    when 'Доставлен' then 'delivered'
    when 'Доставка согласована' then 'agreed'
    else 'default'
  end;

  update public.delivery_invitations
  set
    opened_at = case
      when v_state in ('awaiting_choice', 'opened', 'reminder_sent') and opened_at is null then v_now
      else opened_at
    end,
    access_count = coalesce(access_count, 0) + 1,
    last_accessed_at = v_now
  where id = v_invitation.id
  returning * into v_invitation;

  v_order_items := case
    when jsonb_typeof(v_order.customer -> 'items') = 'array' then v_order.customer -> 'items'
    else '[]'::jsonb
  end;

  return jsonb_build_object(
    'ok', true,
    'invitation', jsonb_build_object(
      'orderId', v_invitation.order_id::text,
      'state', v_state,
      'token', p_token,
      'orderNumber', coalesce(nullif(v_order.order_number, ''), nullif(v_invitation.order_number, '')),
      'customerName', coalesce(nullif(v_order.customer ->> 'name', ''), nullif(v_invitation.customer_name, '')),
      'customerPhone', coalesce(nullif(v_order.customer ->> 'phone', ''), nullif(v_invitation.customer_phone, '')),
      'orderItems', v_order_items,
      'availableSlots', coalesce(to_jsonb(v_invitation.available_slots), '[]'::jsonb),
      'deliveryDate', v_invitation.delivery_date,
      'deliveryTime', v_invitation.delivery_time,
      'orderStatus', v_order.status,
      'deliveryAgreementStatus', v_order.delivery_agreement_status
    )
  );
end;
$$;

create or replace function public.confirm_delivery_choice_by_token(
  p_token text,
  p_delivery_date date,
  p_delivery_time text
)
returns jsonb
language plpgsql
security definer
set search_path = public, extensions
as $$
declare
  v_invitation public.delivery_invitations%rowtype;
  v_group public.order_groups%rowtype;
  v_order record;
  v_token_hash text;
  v_slot_label text;
  v_now timestamptz := timezone('utc', now());
begin
  if nullif(trim(coalesce(p_token, '')), '') is null then
    raise exception 'token is required';
  end if;

  if p_delivery_date is null or nullif(trim(coalesce(p_delivery_time, '')), '') is null then
    raise exception 'Selected slot is not available';
  end if;

  v_token_hash := encode(digest(p_token, 'sha256'), 'hex');
  v_slot_label := concat(p_delivery_date::text, ', ', trim(p_delivery_time));

  select *
  into v_invitation
  from public.delivery_invitations
  where token_hash = v_token_hash
  for update;

  if not found then
    raise exception 'Invitation not found';
  end if;

  if v_invitation.revoked_at is not null then
    raise exception 'Invitation expired';
  end if;

  if v_invitation.expires_at is not null and v_invitation.expires_at <= v_now then
    raise exception 'Invitation expired';
  end if;

  if v_invitation.state not in ('awaiting_choice', 'opened', 'reminder_sent') then
    raise exception 'Invitation is no longer active';
  end if;

  if cardinality(v_invitation.available_slots) > 0 and not (v_slot_label = any(v_invitation.available_slots)) then
    raise exception 'Selected slot is not available';
  end if;

  if v_invitation.order_group_id is not null then
    select *
    into v_group
    from public.order_groups
    where id = v_invitation.order_group_id
    for update;

    if not found then
      raise exception 'Order group not found';
    end if;

    if v_group.delivery_status <> 'pending_confirmation' then
      raise exception 'Invitation is no longer active';
    end if;

    update public.delivery_invitations
    set
      state = 'agreed',
      delivery_date = p_delivery_date,
      delivery_time = trim(p_delivery_time),
      confirmed_at = v_now,
      access_count = coalesce(access_count, 0) + 1,
      last_accessed_at = v_now
    where id = v_invitation.id;

    update public.order_groups
    set
      delivery_status = 'agreed',
      delivery_date = p_delivery_date,
      delivery_time = trim(p_delivery_time),
      notification_status = 'confirmed',
      updated_at = v_now
    where id = v_group.id;

    insert into public.integration_events (
      order_id,
      event_type,
      direction,
      status,
      payload
    )
    values (
      null,
      'delivery_choice_confirmed',
      'inbound',
      'success',
      jsonb_build_object(
        'order_group_id', v_group.id,
        'delivery_invitation_id', v_invitation.id,
        'delivery_date', p_delivery_date,
        'delivery_time', trim(p_delivery_time)
      )
    );

    return jsonb_build_object(
      'ok', true,
      'orderGroupId', v_group.id,
      'deliveryStatus', 'agreed'
    );
  end if;

  select id, status, delivery_agreement_status
  into v_order
  from public.orders
  where id = v_invitation.order_id
  for update;

  if not found then
    raise exception 'Order not found';
  end if;

  if v_order.status not in ('Ожидает ответа клиента', 'Ожидает согласования доставки') then
    raise exception 'Invitation is no longer active';
  end if;

  update public.delivery_invitations
  set
    state = 'agreed',
    delivery_date = p_delivery_date,
    delivery_time = trim(p_delivery_time),
    confirmed_at = v_now,
    access_count = coalesce(access_count, 0) + 1,
    last_accessed_at = v_now
  where id = v_invitation.id;

  update public.orders
  set
    status = 'Доставка согласована',
    delivery_agreement_status = 'Подтверждено клиентом'
  where id = v_order.id;

  insert into public.delivery_slots (
    order_id,
    delivery_date,
    delivery_time,
    logistician_id,
    status
  )
  values (
    v_order.id,
    p_delivery_date,
    trim(p_delivery_time),
    null,
    'confirmed_by_client'
  );

  insert into public.order_history (
    order_id,
    action,
    old_status,
    new_status,
    metadata
  )
  values (
    v_order.id,
    'Подтверждение выбора доставки клиентом',
    v_order.status,
    'Доставка согласована',
    jsonb_build_object(
      'old_delivery_agreement_status', v_order.delivery_agreement_status,
      'new_delivery_agreement_status', 'Подтверждено клиентом',
      'delivery_date', p_delivery_date,
      'delivery_time', trim(p_delivery_time)
    )
  );

  insert into public.integration_events (
    order_id,
    event_type,
    direction,
    status,
    payload
  )
  values (
    v_order.id,
    'delivery_choice_confirmed',
    'inbound',
    'success',
    jsonb_build_object(
      'delivery_date', p_delivery_date,
      'delivery_time', trim(p_delivery_time)
    )
  );

  return jsonb_build_object(
    'ok', true,
    'orderId', v_order.id,
    'status', 'Доставка согласована',
    'deliveryAgreementStatus', 'Подтверждено клиентом'
  );
end;
$$;

revoke all on function public.get_delivery_invitation_by_token(text) from public;
grant execute on function public.get_delivery_invitation_by_token(text) to anon, authenticated;

revoke all on function public.confirm_delivery_choice_by_token(text, date, text) from public;
grant execute on function public.confirm_delivery_choice_by_token(text, date, text) to anon, authenticated;

alter table public.roles enable row level security;
alter table public.users enable row level security;
alter table public.orders enable row level security;
alter table public.order_logisticians enable row level security;
alter table public.order_history enable row level security;
alter table public.delivery_slots enable row level security;
alter table public.chat_messages enable row level security;
alter table public.error_logs enable row level security;
alter table public.order_groups enable row level security;
alter table public.delivery_invitations enable row level security;
alter table public.integration_events enable row level security;

drop policy if exists "roles select authenticated" on public.roles;
create policy "roles select authenticated" on public.roles
for select
using (public.current_role_name() is not null);

drop policy if exists "roles admin mutate" on public.roles;
create policy "roles admin mutate" on public.roles
for insert
with check (public.current_role_name() = 'admin');

drop policy if exists "roles admin update" on public.roles;
create policy "roles admin update" on public.roles
for update
using (public.current_role_name() = 'admin')
with check (public.current_role_name() = 'admin');

drop policy if exists "roles admin delete" on public.roles;
create policy "roles admin delete" on public.roles
for delete
using (public.current_role_name() = 'admin');

drop policy if exists "users self or admin" on public.users;
create policy "users self or admin" on public.users
for select
using (id = auth.uid());

-- Helper to check admin role without RLS recursion.
create or replace function public.is_admin()
returns boolean
language sql
stable
security definer
set search_path = public
as 81937
  select exists (
    select 1 from public.users u
    join public.roles r on r.id = u.role_id
    where u.id = auth.uid() and r.name = 'admin'
  )
81937;
alter function public.is_admin() set row_security = off;

drop policy if exists "users admin update" on public.users;
create policy "users admin update" on public.users
for all
using (public.is_admin())
with check (public.is_admin());

drop policy if exists "users readable by logistics" on public.users;
create policy "users readable by logistics" on public.users
for select
using (
  auth.role() in ('authenticated', 'service_role')
);

drop policy if exists "orders select by role" on public.orders;
create policy "orders select by role" on public.orders
for select
using (
  public.current_role_name() = 'admin'
  or public.current_role_name() = 'production_lead'
  or (public.current_role_name() = 'manager' and manager_id = auth.uid())
  or (public.current_role_name() = 'driver' and assigned_driver_id = auth.uid())
  or (
    public.current_role_name() = 'logistician'
    and (
      logistician_id = auth.uid()
      or exists (
        select 1
        from public.order_logisticians ol
        where ol.order_id = orders.id and ol.logistician_id = auth.uid()
      )
    )
  )
);

drop policy if exists "orders insert managers admin" on public.orders;
create policy "orders insert managers admin" on public.orders
for insert
with check (public.current_role_name() in ('manager', 'admin'));

drop policy if exists "orders update by workflow role" on public.orders;
create policy "orders update by workflow role" on public.orders
for update
using (
  public.current_role_name() = 'admin'
  or (public.current_role_name() = 'manager' and manager_id = auth.uid())
  or (public.current_role_name() = 'driver' and assigned_driver_id = auth.uid())
  or public.current_role_name() = 'production_lead'
  or (
    public.current_role_name() = 'logistician'
    and (
      logistician_id = auth.uid()
      or exists (
        select 1
        from public.order_logisticians ol
        where ol.order_id = orders.id and ol.logistician_id = auth.uid()
      )
    )
  )
)
with check (
  public.current_role_name() = 'admin'
  or (public.current_role_name() = 'manager' and manager_id = auth.uid())
  or (public.current_role_name() = 'driver' and assigned_driver_id = auth.uid())
  or public.current_role_name() = 'production_lead'
  or (
    public.current_role_name() = 'logistician'
    and (
      logistician_id = auth.uid()
      or exists (
        select 1
        from public.order_logisticians ol
        where ol.order_id = orders.id and ol.logistician_id = auth.uid()
      )
    )
  )
);

drop policy if exists "history select by order role" on public.order_history;
create policy "history select by order role" on public.order_history
for select
using (
  exists (
    select 1
    from public.orders o
    where o.id = order_history.order_id
      and (
        public.current_role_name() = 'admin'
        or public.current_role_name() = 'production_lead'
        or (public.current_role_name() = 'manager' and o.manager_id = auth.uid())
        or (public.current_role_name() = 'driver' and o.assigned_driver_id = auth.uid())
        or (
          public.current_role_name() = 'logistician'
          and (
            o.logistician_id = auth.uid()
            or exists (
              select 1
              from public.order_logisticians ol
              where ol.order_id = o.id and ol.logistician_id = auth.uid()
            )
          )
        )
      )
  )
);

drop policy if exists "history insert workflow" on public.order_history;
create policy "history insert workflow" on public.order_history
for insert
with check (public.current_role_name() in ('manager', 'production_lead', 'logistician', 'driver', 'admin'));

drop policy if exists "order groups select by role" on public.order_groups;
create policy "order groups select by role" on public.order_groups
for select
using (
  public.current_role_name() in ('manager', 'logistician', 'driver', 'admin')
  or exists (
    select 1 from public.delivery_invitations di
    where di.order_group_id = order_groups.id
    and di.state in ('awaiting_choice', 'opened', 'reminder_sent')
  )
);

drop policy if exists "order groups update coordination roles" on public.order_groups;
create policy "order groups update coordination roles" on public.order_groups
for update
using (public.current_role_name() in ('manager', 'logistician', 'admin'))
with check (public.current_role_name() in ('manager', 'logistician', 'admin') or (auth.jwt()->>'role') = 'service_role');

drop policy if exists "order groups insert service roles" on public.order_groups;
create policy "order groups insert service roles" on public.order_groups
for insert
with check (public.current_role_name() in ('manager', 'logistician', 'admin') or (auth.jwt()->>'role') = 'service_role');

drop policy if exists "slots by order role" on public.delivery_slots;
create policy "slots by order role" on public.delivery_slots
for all
using (
  exists (
    select 1
    from public.orders o
    where o.id = delivery_slots.order_id
      and (
        public.current_role_name() = 'admin'
        or public.current_role_name() = 'production_lead'
        or (public.current_role_name() = 'manager' and o.manager_id = auth.uid())
        or (public.current_role_name() = 'driver' and o.assigned_driver_id = auth.uid())
        or (
          public.current_role_name() = 'logistician'
          and (
            o.logistician_id = auth.uid()
            or exists (
              select 1
              from public.order_logisticians ol
              where ol.order_id = o.id and ol.logistician_id = auth.uid()
            )
          )
        )
      )
  )
)
with check (public.current_role_name() in ('logistician', 'admin'));

drop policy if exists "chat by order role" on public.chat_messages;
create policy "chat by order role" on public.chat_messages
for select
using (
  exists (
    select 1
    from public.orders o
    where o.id = chat_messages.order_id
      and (
        public.current_role_name() = 'admin'
        or public.current_role_name() = 'production_lead'
        or (public.current_role_name() = 'manager' and o.manager_id = auth.uid())
        or (public.current_role_name() = 'driver' and o.assigned_driver_id = auth.uid())
        or (
          public.current_role_name() = 'logistician'
          and (
            o.logistician_id = auth.uid()
            or exists (
              select 1
              from public.order_logisticians ol
              where ol.order_id = o.id and ol.logistician_id = auth.uid()
            )
          )
        )
      )
  )
);

drop policy if exists "chat insert workflow" on public.chat_messages;
create policy "chat insert workflow" on public.chat_messages
for insert
with check (public.current_role_name() in ('manager', 'logistician', 'admin'));

drop policy if exists "order logisticians by role" on public.order_logisticians;
create policy "order logisticians by role" on public.order_logisticians
for all
using (public.current_role_name() in ('logistician', 'admin'))
with check (public.current_role_name() in ('logistician', 'admin'));

drop policy if exists "error logs admin only" on public.error_logs;
create policy "error logs admin only" on public.error_logs
for all
using (public.current_role_name() = 'admin')
with check (public.current_role_name() = 'admin');

drop policy if exists "delivery invitations admin only" on public.delivery_invitations;
create policy "delivery invitations admin only" on public.delivery_invitations
for all
using (public.current_role_name() = 'admin')
with check (public.current_role_name() = 'admin');

alter table public.rate_limits enable row level security;
drop policy if exists "rate limits admin only" on public.rate_limits;
create policy "rate limits admin only" on public.rate_limits
for all
using (public.current_role_name() = 'admin')
with check (public.current_role_name() = 'admin');

drop policy if exists "integration events admin only" on public.integration_events;
create policy "integration events admin only" on public.integration_events
for all
using (public.current_role_name() = 'admin')
with check (public.current_role_name() = 'admin');

-- RPC для получения списка водителей (обход RLS)
create or replace function public.get_drivers()
returns table (
  id uuid,
  email text,
  name text
)
language plpgsql
security definer
set search_path = public
as $$
begin
  return query
  select u.id, u.email, u.name
  from public.users u
  join public.roles r on r.id = u.role_id
  where r.name = 'driver'
  order by u.name;
end;
$$;

revoke execute on function public.get_drivers() from anon;
grant execute on function public.get_drivers() to authenticated;


-- Audit log for admin actions
create table if not exists public.audit_log (
  id uuid primary key default gen_random_uuid(),
  actor_id uuid references auth.users (id) on delete set null,
  action text not null,
  target_type text,
  target_id text,
  metadata jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default timezone('utc', now())
);

alter table public.audit_log enable row level security;

create policy "audit admin only" on public.audit_log
for all
using (public.current_role_name() = 'admin')
with check (public.current_role_name() = 'admin');

create index if not exists idx_audit_log_actor_id on public.audit_log (actor_id);
create index if not exists idx_audit_log_action on public.audit_log (action);
create index if not exists idx_audit_log_target on public.audit_log (target_type, target_id);
create index if not exists idx_audit_log_created_at on public.audit_log (created_at desc);

-- Trigger: log role changes
create or replace function public.log_role_change()
returns trigger
language plpgsql
security definer
set search_path = public
as $$
begin
  insert into public.audit_log (actor_id, action, target_type, target_id, metadata)
  values (
    auth.uid(),
    tg_op = 'INSERT' then 'role_created'::text else 'role_updated'::text end,
    'role',
    new.id::text,
    jsonb_build_object(
      'name', new.name,
      'permissions', new.permissions,
      'old_name', case when tg_op = 'UPDATE' then old.name else null end
    )
  );
  return new;
end;
$$;

drop trigger if exists on_role_change on public.roles;
create trigger on_role_change
after insert or update on public.roles
for each row
execute function public.log_role_change();

-- Trigger: log user changes
create or replace function public.log_user_change()
returns trigger
language plpgsql
security definer
set search_path = public
as $$
begin
  insert into public.audit_log (actor_id, action, target_type, target_id, metadata)
  values (
    auth.uid(),
    case tg_op
      when 'INSERT' then 'user_created'
      when 'UPDATE' then 'user_updated'
      when 'DELETE' then 'user_deleted'
    end,
    'user',
    coalesce(new.id, old.id)::text,
    jsonb_build_object(
      'email', coalesce(new.email, old.email),
      'name', coalesce(new.name, old.name),
      'role_id', coalesce(new.role_id, old.role_id)
    )
  );
  return coalesce(new, old);
end;
$$;

drop trigger if exists on_user_change on public.users;
create trigger on_user_change
after insert or update or delete on public.users
for each row
execute function public.log_user_change();

-- RPC for driver to update delivery status
-- Validates that the requesting user is the assigned driver
create or replace function public.update_delivery_status(
  p_order_group_id uuid,
  p_status text
)
returns boolean
language plpgsql
security definer
set search_path = public
as $$
declare
  v_assigned_driver_id uuid;
  v_current_status text;
begin
  select assigned_driver_id, delivery_status
  into v_assigned_driver_id, v_current_status
  from public.order_groups
  where id = p_order_group_id;

  if v_assigned_driver_id is null then
    raise exception 'Группа не назначена водителю';
  end if;

  if v_assigned_driver_id != auth.uid() then
    raise exception 'Вы не назначены на эту доставку';
  end if;

  update public.order_groups
  set delivery_status = p_status,
      updated_at = timezone('utc', now())
  where id = p_order_group_id;

  return true;
end;
$$;

revoke execute on function public.update_delivery_status(uuid, text) from anon;
grant execute on function public.update_delivery_status(uuid, text) to authenticated;