supabase_schema.sql

create table if not exists public.retailcrm_orders (
    retailcrm_id bigint primary key,
    external_id text,
    number text,
    site text,
    status text,
    order_type text,
    order_method text,
    first_name text,
    last_name text,
    phone text,
    email text,
    created_at timestamptz,
    status_updated_at timestamptz,
    total_summ numeric,
    is_deleted boolean not null default false,
    deleted_at timestamptz,
    synced_at timestamptz not null,
    raw_order jsonb not null default '{}'::jsonb
);

create index if not exists retailcrm_orders_site_idx
    on public.retailcrm_orders (site);

create index if not exists retailcrm_orders_status_idx
    on public.retailcrm_orders (status);

create index if not exists retailcrm_orders_synced_at_idx
    on public.retailcrm_orders (synced_at desc);

create table if not exists public.retailcrm_sync_state (
    stream text primary key,
    last_since_id bigint not null,
    last_synced_at timestamptz not null
);

create table if not exists public.retailcrm_order_notifications (
    retailcrm_id bigint primary key,
    notification_type text not null,
    threshold numeric not null,
    sent_at timestamptz not null,
    telegram_chat_id text not null,
    payload jsonb not null default '{}'::jsonb
);

create index if not exists retailcrm_order_notifications_type_idx
    on public.retailcrm_order_notifications (notification_type);

create or replace view public.order_chart_daily as
select
    created_at::date as day,
    count(*)::bigint as order_count,
    coalesce(sum(total_summ), 0)::numeric as revenue
from public.retailcrm_orders
where is_deleted = false
  and created_at is not null
group by created_at::date;

grant usage on schema public to anon, authenticated;
grant select on public.order_chart_daily to anon, authenticated;