765 lines
27 KiB
SQL
765 lines
27 KiB
SQL
-- Stage 1 demo seed for live Supabase-backed demo.
|
||
-- Represents grouped imported 1C orders as delivery sets.
|
||
-- Run this after the two auth users are created in Supabase Auth:
|
||
-- skylanguage@yandex.ru and mk7029953@yandex.ru
|
||
|
||
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;
|
||
|
||
do $$
|
||
declare
|
||
admin_auth_id uuid;
|
||
logistician_auth_id uuid;
|
||
admin_role_id uuid;
|
||
logistician_role_id uuid;
|
||
begin
|
||
select id into admin_auth_id from auth.users where email = 'skylanguage@yandex.ru' limit 1;
|
||
select id into logistician_auth_id from auth.users where email = 'mk7029953@yandex.ru' limit 1;
|
||
select id into admin_role_id from public.roles where name = 'admin' limit 1;
|
||
select id into logistician_role_id from public.roles where name = 'logistician' limit 1;
|
||
|
||
if admin_auth_id is not null then
|
||
insert into public.users (id, email, name, role_id, last_login)
|
||
values (
|
||
admin_auth_id,
|
||
'skylanguage@yandex.ru',
|
||
'Елена Родович',
|
||
admin_role_id,
|
||
timezone('utc', now())
|
||
)
|
||
on conflict (id) do update
|
||
set email = excluded.email,
|
||
name = excluded.name,
|
||
role_id = excluded.role_id,
|
||
last_login = excluded.last_login;
|
||
end if;
|
||
|
||
if logistician_auth_id is not null then
|
||
insert into public.users (id, email, name, role_id, last_login)
|
||
values (
|
||
logistician_auth_id,
|
||
'mk7029953@yandex.ru',
|
||
'Михаил Кучер',
|
||
logistician_role_id,
|
||
timezone('utc', now())
|
||
)
|
||
on conflict (id) do update
|
||
set email = excluded.email,
|
||
name = excluded.name,
|
||
role_id = excluded.role_id,
|
||
last_login = excluded.last_login;
|
||
end if;
|
||
end $$;
|
||
|
||
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;
|
||
|
||
delete from public.order_history where order_id in (
|
||
select id from public.orders where order_number in ('CD-240031', 'CD-240032', 'CD-240033', 'CD-240034', 'CD-240035', 'CD-240036', 'CD-240037')
|
||
);
|
||
delete from public.delivery_slots where order_id in (
|
||
select id from public.orders where order_number in ('CD-240031', 'CD-240032', 'CD-240033', 'CD-240034', 'CD-240035', 'CD-240036', 'CD-240037')
|
||
);
|
||
delete from public.delivery_invitations where order_id in (
|
||
select id from public.orders where order_number in ('CD-240031', 'CD-240032', 'CD-240033', 'CD-240034', 'CD-240035', 'CD-240036', 'CD-240037')
|
||
);
|
||
delete from public.integration_events where order_id in (
|
||
select id from public.orders where order_number in ('CD-240031', 'CD-240032', 'CD-240033', 'CD-240034', 'CD-240035', 'CD-240036', 'CD-240037')
|
||
);
|
||
delete from public.chat_messages where order_id in (
|
||
select id from public.orders where order_number in ('CD-240031', 'CD-240032', 'CD-240033', 'CD-240034', 'CD-240035', 'CD-240036', 'CD-240037')
|
||
);
|
||
delete from public.order_logisticians where order_id in (
|
||
select id from public.orders where order_number in ('CD-240031', 'CD-240032', 'CD-240033', 'CD-240034', 'CD-240035', 'CD-240036', 'CD-240037')
|
||
);
|
||
|
||
-- Delivery set: Волкова — approaching (one order accepted, one still in production)
|
||
-- CD-240031: kitchen set, accepted by QC, ready for delivery
|
||
-- CD-240036: countertop for same client, still in production (saw done)
|
||
|
||
insert into public.orders (
|
||
order_number,
|
||
customer,
|
||
status,
|
||
delivery_agreement_status,
|
||
manager_id,
|
||
logistician_id,
|
||
assigned_driver_id,
|
||
ready_for_delivery_at,
|
||
delivery_flow_started_at,
|
||
delivery_flow_source,
|
||
source_order_number,
|
||
source_order_date,
|
||
source_customer_name,
|
||
source_customer_phone,
|
||
source_customer_email,
|
||
source_customer_city,
|
||
source_total_sum,
|
||
source_paid_at,
|
||
source_gateway,
|
||
source_associated_bills_text,
|
||
source_production_at,
|
||
source_saw_at,
|
||
source_glue_at,
|
||
source_h_glue_at,
|
||
source_curve_at,
|
||
source_accept_at,
|
||
source_ship_at,
|
||
source_payload,
|
||
delivery_set_key,
|
||
delivery_set_name,
|
||
delivery_set_status,
|
||
delivery_set_ready_at
|
||
)
|
||
values
|
||
(
|
||
'CD-240031',
|
||
jsonb_build_object(
|
||
'name', 'Мария Волкова',
|
||
'phone', '+7 978 000-12-31',
|
||
'messenger', 'Телеграм',
|
||
'address', 'Симферополь, ул. Тургенева, 18',
|
||
'city', 'Симферополь',
|
||
'items', jsonb_build_array(
|
||
'Кухонный гарнитур | 1 комплект',
|
||
'Фурнитура Blum | 12 шт',
|
||
'Монтажный комплект | 1 набор'
|
||
),
|
||
'comments', jsonb_build_array('Клиент просит подтверждение за 2 часа до доставки'),
|
||
'tags', jsonb_build_array('срочно', 'кухня'),
|
||
'orderNotes', jsonb_build_array(
|
||
jsonb_build_object(
|
||
'id', 'note-1',
|
||
'authorName', 'Елена Родович',
|
||
'text', 'Проверить доступность подъезда для разгрузки.',
|
||
'createdAt', '2026-04-12T08:00:00Z'
|
||
)
|
||
),
|
||
'internalMessages', jsonb_build_array(
|
||
jsonb_build_object(
|
||
'id', 'ic-1',
|
||
'senderId', 'unknown',
|
||
'senderName', 'Логист',
|
||
'text', 'Клиент просил предварительный звонок перед доставкой.',
|
||
'sentAt', '2026-04-12T09:50:00Z'
|
||
)
|
||
),
|
||
'scheduledDelivery', '2026-04-14T08:00:00Z',
|
||
'deliveryDate', '2026-04-14',
|
||
'deliveryTime', 'Первая половина дня',
|
||
'exception', null
|
||
),
|
||
'Ожидает ответа клиента',
|
||
'Отправлено клиенту',
|
||
(select id from public.users where email = 'skylanguage@yandex.ru' limit 1),
|
||
(select id from public.users where email = 'mk7029953@yandex.ru' limit 1),
|
||
null,
|
||
'2026-04-13T08:30:00Z',
|
||
'2026-04-13T09:00:00Z',
|
||
'1c_xml',
|
||
'УН-00031',
|
||
'2026-04-10',
|
||
'Волкова Мария Александровна',
|
||
'+79780001231',
|
||
'volkova@example.com',
|
||
'Симферополь',
|
||
185400.00,
|
||
'2026-04-11T10:00:00Z',
|
||
'bank_transfer',
|
||
'Счет №31 от 10.04.2026',
|
||
'2026-04-11T09:00:00Z',
|
||
'2026-04-11T10:00:00Z',
|
||
'2026-04-12T08:00:00Z',
|
||
null,
|
||
null,
|
||
'2026-04-12T14:00:00Z',
|
||
null,
|
||
jsonb_build_object('source', '1c_xml', 'imported_at', '2026-04-13T08:00:00Z'),
|
||
'volkova-simferopol-2026-04',
|
||
'Волкова М.А. — Симферополь, апрель 2026',
|
||
'approaching',
|
||
null
|
||
),
|
||
(
|
||
'CD-240036',
|
||
jsonb_build_object(
|
||
'name', 'Мария Волкова',
|
||
'phone', '+7 978 000-12-31',
|
||
'messenger', 'Телеграм',
|
||
'address', 'Симферополь, ул. Тургенева, 18',
|
||
'city', 'Симферополь',
|
||
'items', jsonb_build_array('Столешница 3000x600 | 1 шт'),
|
||
'comments', jsonb_build_array(),
|
||
'tags', jsonb_build_array('столешница'),
|
||
'orderNotes', jsonb_build_array(),
|
||
'internalMessages', jsonb_build_array(),
|
||
'scheduledDelivery', null,
|
||
'deliveryDate', null,
|
||
'deliveryTime', null,
|
||
'exception', null
|
||
),
|
||
'В производстве',
|
||
'Не начато',
|
||
(select id from public.users where email = 'skylanguage@yandex.ru' limit 1),
|
||
null,
|
||
null,
|
||
null,
|
||
null,
|
||
null,
|
||
'УН-00036',
|
||
'2026-04-11',
|
||
'Волкова Мария Александровна',
|
||
'+79780001231',
|
||
'volkova@example.com',
|
||
'Симферополь',
|
||
42800.00,
|
||
'2026-04-11T10:00:00Z',
|
||
'bank_transfer',
|
||
'Счет №31 от 10.04.2026',
|
||
'2026-04-12T09:00:00Z',
|
||
'2026-04-12T14:00:00Z',
|
||
null,
|
||
null,
|
||
null,
|
||
null,
|
||
null,
|
||
jsonb_build_object('source', '1c_xml', 'imported_at', '2026-04-13T08:00:00Z'),
|
||
'volkova-simferopol-2026-04',
|
||
'Волкова М.А. — Симферополь, апрель 2026',
|
||
'approaching',
|
||
null
|
||
),
|
||
|
||
-- Delivery set: Савин — ready_to_launch (all orders accepted, none shipped)
|
||
|
||
(
|
||
'CD-240032',
|
||
jsonb_build_object(
|
||
'name', 'Александр Савин',
|
||
'phone', '+7 978 000-12-32',
|
||
'messenger', 'ВКонтакте',
|
||
'address', 'Ялта, ул. Чехова, 9',
|
||
'city', 'Ялта',
|
||
'items', jsonb_build_array(
|
||
'Стеклопакет 2400x1800 | 2 шт',
|
||
'Комплект крепежа | 1 набор'
|
||
),
|
||
'comments', jsonb_build_array('Нужен созвон перед отгрузкой'),
|
||
'tags', jsonb_build_array('стеклопакет'),
|
||
'orderNotes', jsonb_build_array(
|
||
jsonb_build_object(
|
||
'id', 'note-2',
|
||
'authorName', 'Елена Родович',
|
||
'text', 'Производство завершено, передаём логистике после фотофиксации.',
|
||
'createdAt', '2026-04-13T08:35:00Z'
|
||
)
|
||
),
|
||
'internalMessages', jsonb_build_array(
|
||
jsonb_build_object(
|
||
'id', 'ic-2',
|
||
'senderId', 'unknown',
|
||
'senderName', 'Производство',
|
||
'text', 'Можно запускать сообщение клиенту после 14:00.',
|
||
'sentAt', '2026-04-13T08:25:00Z'
|
||
)
|
||
),
|
||
'scheduledDelivery', '2026-04-15T13:00:00Z',
|
||
'deliveryDate', '2026-04-15',
|
||
'deliveryTime', 'Вторая половина дня',
|
||
'exception', null
|
||
),
|
||
'Доставка согласована',
|
||
'Подтверждено клиентом',
|
||
(select id from public.users where email = 'skylanguage@yandex.ru' limit 1),
|
||
(select id from public.users where email = 'mk7029953@yandex.ru' limit 1),
|
||
null,
|
||
'2026-04-13T07:10:00Z',
|
||
'2026-04-13T07:20:00Z',
|
||
'1c_xml',
|
||
'УН-00032',
|
||
'2026-04-09',
|
||
'Савин Александр Петрович',
|
||
'+79780001232',
|
||
'savin@example.com',
|
||
'Ялта',
|
||
124600.00,
|
||
'2026-04-10T12:00:00Z',
|
||
'bank_transfer',
|
||
'Счет №32 от 09.04.2026',
|
||
'2026-04-10T08:00:00Z',
|
||
'2026-04-10T14:00:00Z',
|
||
'2026-04-11T09:00:00Z',
|
||
null,
|
||
null,
|
||
'2026-04-12T16:00:00Z',
|
||
null,
|
||
jsonb_build_object('source', '1c_xml', 'imported_at', '2026-04-13T07:00:00Z'),
|
||
'savin-yalta-2026-04',
|
||
'Савин А.П. — Ялта, апрель 2026',
|
||
'ready_to_launch',
|
||
'2026-04-12T16:00:00Z'
|
||
),
|
||
|
||
-- Delivery set: Тарасова — awaiting_client (sent to client, waiting for response)
|
||
|
||
(
|
||
'CD-240033',
|
||
jsonb_build_object(
|
||
'name', 'Екатерина Тарасова',
|
||
'phone', '+7 978 000-12-33',
|
||
'messenger', 'Макс',
|
||
'address', 'Севастополь, пр. Октябрьской Революции, 51',
|
||
'city', 'Севастополь',
|
||
'items', jsonb_build_array('Столешница | 3 шт', 'Кромка | 8 рулонов'),
|
||
'comments', jsonb_build_array('Клиент просит вечерний слот'),
|
||
'tags', jsonb_build_array('розница'),
|
||
'orderNotes', jsonb_build_array(),
|
||
'internalMessages', jsonb_build_array(),
|
||
'scheduledDelivery', '2026-04-16T17:00:00Z',
|
||
'deliveryDate', '2026-04-16',
|
||
'deliveryTime', 'Первая половина дня',
|
||
'exception', null
|
||
),
|
||
'Передан логисту',
|
||
'Нет ответа',
|
||
(select id from public.users where email = 'skylanguage@yandex.ru' limit 1),
|
||
(select id from public.users where email = 'mk7029953@yandex.ru' limit 1),
|
||
null,
|
||
'2026-04-13T11:40:00Z',
|
||
'2026-04-13T12:10:00Z',
|
||
'1c_xml',
|
||
'УН-00033',
|
||
'2026-04-08',
|
||
'Тарасова Екатерина Игоревна',
|
||
'+79780001233',
|
||
'tarasova@example.com',
|
||
'Севастополь',
|
||
67200.00,
|
||
'2026-04-09T09:00:00Z',
|
||
'card',
|
||
'Счет №33 от 08.04.2026',
|
||
'2026-04-09T08:00:00Z',
|
||
'2026-04-09T14:00:00Z',
|
||
'2026-04-10T10:00:00Z',
|
||
null,
|
||
null,
|
||
'2026-04-11T15:00:00Z',
|
||
null,
|
||
jsonb_build_object('source', '1c_xml', 'imported_at', '2026-04-13T11:00:00Z'),
|
||
'tarasova-sevastopol-2026-04',
|
||
'Тарасова Е.И. — Севастополь, апрель 2026',
|
||
'awaiting_client',
|
||
'2026-04-11T15:00:00Z'
|
||
),
|
||
|
||
-- Delivery set: Фролова — manual_work (paid storage)
|
||
|
||
(
|
||
'CD-240034',
|
||
jsonb_build_object(
|
||
'name', 'Ирина Фролова',
|
||
'phone', '+7 978 000-12-34',
|
||
'messenger', 'СМС',
|
||
'address', 'Феодосия, ул. Крымская, 12',
|
||
'city', 'Феодосия',
|
||
'items', jsonb_build_array('ДСП | 12 листов', 'Кромка | 16 рулонов'),
|
||
'comments', jsonb_build_array('Клиент ждёт звонок утром'),
|
||
'tags', jsonb_build_array('опт'),
|
||
'orderNotes', jsonb_build_array(),
|
||
'internalMessages', jsonb_build_array(),
|
||
'scheduledDelivery', '2026-04-17T10:00:00Z',
|
||
'deliveryDate', '2026-04-17',
|
||
'deliveryTime', 'Вторая половина дня',
|
||
'exception', null
|
||
),
|
||
'Платное хранение',
|
||
'Нет ответа',
|
||
(select id from public.users where email = 'skylanguage@yandex.ru' limit 1),
|
||
(select id from public.users where email = 'mk7029953@yandex.ru' limit 1),
|
||
null,
|
||
'2026-04-13T10:00:00Z',
|
||
'2026-04-13T12:30:00Z',
|
||
'1c_xml',
|
||
'УН-00034',
|
||
'2026-04-07',
|
||
'Фролова Ирина Дмитриевна',
|
||
'+79780001234',
|
||
null,
|
||
'Феодосия',
|
||
98700.00,
|
||
'2026-04-08T11:00:00Z',
|
||
'cash',
|
||
'Счет №34 от 07.04.2026',
|
||
'2026-04-08T08:00:00Z',
|
||
'2026-04-08T13:00:00Z',
|
||
'2026-04-09T09:00:00Z',
|
||
null,
|
||
null,
|
||
'2026-04-10T11:00:00Z',
|
||
null,
|
||
jsonb_build_object('source', '1c_xml', 'imported_at', '2026-04-13T09:30:00Z'),
|
||
'frolova-feodosia-2026-04',
|
||
'Фролова И.Д. — Феодосия, апрель 2026',
|
||
'manual_work',
|
||
'2026-04-10T11:00:00Z'
|
||
),
|
||
|
||
-- Single order in set: Орлова — completed delivery
|
||
|
||
(
|
||
'CD-240035',
|
||
jsonb_build_object(
|
||
'name', 'Наталья Орлова',
|
||
'phone', '+7 978 000-12-35',
|
||
'messenger', 'Телеграм',
|
||
'address', 'Симферополь, ул. Жуковского, 4',
|
||
'city', 'Симферополь',
|
||
'items', jsonb_build_array('Фурнитура | 24 позиции'),
|
||
'comments', jsonb_build_array('Требуется подтверждение доставки в день заказа'),
|
||
'tags', jsonb_build_array('vip'),
|
||
'orderNotes', jsonb_build_array(),
|
||
'internalMessages', jsonb_build_array(),
|
||
'scheduledDelivery', '2026-04-13T15:00:00Z',
|
||
'deliveryDate', '2026-04-13',
|
||
'deliveryTime', 'Первая половина дня',
|
||
'exception', null
|
||
),
|
||
'Доставлен',
|
||
'Подтверждено клиентом',
|
||
(select id from public.users where email = 'skylanguage@yandex.ru' limit 1),
|
||
(select id from public.users where email = 'mk7029953@yandex.ru' limit 1),
|
||
null,
|
||
'2026-04-13T05:30:00Z',
|
||
'2026-04-13T06:00:00Z',
|
||
'1c_xml',
|
||
'УН-00035',
|
||
'2026-04-06',
|
||
'Орлова Наталья Сергеевна',
|
||
'+79780001235',
|
||
'orlova@example.com',
|
||
'Симферополь',
|
||
31500.00,
|
||
'2026-04-07T08:00:00Z',
|
||
'card',
|
||
'Счет №35 от 06.04.2026',
|
||
'2026-04-07T09:00:00Z',
|
||
'2026-04-07T14:00:00Z',
|
||
'2026-04-08T08:00:00Z',
|
||
null,
|
||
null,
|
||
'2026-04-10T12:00:00Z',
|
||
'2026-04-13T14:30:00Z',
|
||
jsonb_build_object('source', '1c_xml', 'imported_at', '2026-04-13T05:00:00Z'),
|
||
'orlova-simferopol-2026-04',
|
||
'Орлова Н.С. — Симферополь, апрель 2026',
|
||
'completed',
|
||
'2026-04-10T12:00:00Z'
|
||
),
|
||
|
||
-- New order with no source data: freshly imported from 1C, not yet grouped
|
||
(
|
||
'CD-240037',
|
||
jsonb_build_object(
|
||
'name', 'Дмитрий Козлов',
|
||
'phone', '+7 978 000-12-37',
|
||
'messenger', 'Телеграм',
|
||
'address', 'Алушта, ул. Ленина, 45',
|
||
'city', 'Алушта',
|
||
'items', jsonb_build_array('Душевая кабина | 1 шт', 'Монтажный комплект | 1 набор'),
|
||
'comments', jsonb_build_array('Въезд со двора, нужна узкая машина'),
|
||
'tags', jsonb_build_array('душевая', 'срочно'),
|
||
'orderNotes', jsonb_build_array(),
|
||
'internalMessages', jsonb_build_array(),
|
||
'scheduledDelivery', null,
|
||
'deliveryDate', null,
|
||
'deliveryTime', null,
|
||
'exception', null
|
||
),
|
||
'Новый',
|
||
'Не начато',
|
||
(select id from public.users where email = 'skylanguage@yandex.ru' limit 1),
|
||
null,
|
||
null,
|
||
null,
|
||
null,
|
||
null,
|
||
'УН-00037',
|
||
'2026-04-13',
|
||
'Козлов Дмитрий Викторович',
|
||
'+79780001237',
|
||
null,
|
||
'Алушта',
|
||
78000.00,
|
||
null,
|
||
null,
|
||
null,
|
||
null,
|
||
null,
|
||
null,
|
||
null,
|
||
null,
|
||
null,
|
||
null,
|
||
jsonb_build_object('source', '1c_xml', 'imported_at', '2026-04-13T14:00:00Z'),
|
||
null,
|
||
null,
|
||
null,
|
||
null
|
||
)
|
||
on conflict (order_number) do update set
|
||
customer = excluded.customer,
|
||
status = excluded.status,
|
||
delivery_agreement_status = excluded.delivery_agreement_status,
|
||
manager_id = excluded.manager_id,
|
||
logistician_id = excluded.logistician_id,
|
||
assigned_driver_id = excluded.assigned_driver_id,
|
||
ready_for_delivery_at = excluded.ready_for_delivery_at,
|
||
delivery_flow_started_at = excluded.delivery_flow_started_at,
|
||
delivery_flow_source = excluded.delivery_flow_source,
|
||
source_order_number = excluded.source_order_number,
|
||
source_order_date = excluded.source_order_date,
|
||
source_customer_name = excluded.source_customer_name,
|
||
source_customer_phone = excluded.source_customer_phone,
|
||
source_customer_email = excluded.source_customer_email,
|
||
source_customer_city = excluded.source_customer_city,
|
||
source_total_sum = excluded.source_total_sum,
|
||
source_paid_at = excluded.source_paid_at,
|
||
source_gateway = excluded.source_gateway,
|
||
source_associated_bills_text = excluded.source_associated_bills_text,
|
||
source_production_at = excluded.source_production_at,
|
||
source_saw_at = excluded.source_saw_at,
|
||
source_glue_at = excluded.source_glue_at,
|
||
source_h_glue_at = excluded.source_h_glue_at,
|
||
source_curve_at = excluded.source_curve_at,
|
||
source_accept_at = excluded.source_accept_at,
|
||
source_ship_at = excluded.source_ship_at,
|
||
source_payload = excluded.source_payload,
|
||
delivery_set_key = excluded.delivery_set_key,
|
||
delivery_set_name = excluded.delivery_set_name,
|
||
delivery_set_status = excluded.delivery_set_status,
|
||
delivery_set_ready_at = excluded.delivery_set_ready_at,
|
||
delivery_ready_reason = excluded.delivery_ready_reason,
|
||
source_sms_legacy_at = excluded.source_sms_legacy_at,
|
||
updated_at = timezone('utc', now());
|
||
|
||
insert into public.order_logisticians (order_id, logistician_id, assigned_by)
|
||
select o.id, u.id, u.id
|
||
from public.orders o
|
||
cross join lateral (
|
||
select id from public.users where email = 'mk7029953@yandex.ru' limit 1
|
||
) u
|
||
where o.order_number in ('CD-240031', 'CD-240032', 'CD-240033', 'CD-240034')
|
||
on conflict do nothing;
|
||
|
||
insert into public.delivery_slots (
|
||
order_id,
|
||
delivery_date,
|
||
delivery_time,
|
||
logistician_id,
|
||
status,
|
||
selected_by_client_at
|
||
)
|
||
select
|
||
o.id,
|
||
slot.delivery_date,
|
||
slot.delivery_time,
|
||
(select id from public.users where email = 'mk7029953@yandex.ru' limit 1),
|
||
'pending_confirmation',
|
||
null
|
||
from public.orders o
|
||
cross join (
|
||
values
|
||
((current_date + 1), 'До обеда'),
|
||
((current_date + 1), 'После обеда'),
|
||
((current_date + 2), 'До обеда'),
|
||
((current_date + 2), 'После обеда')
|
||
) as slot(delivery_date, delivery_time)
|
||
where o.order_number = 'CD-240031';
|
||
|
||
insert into public.delivery_slots (
|
||
order_id,
|
||
delivery_date,
|
||
delivery_time,
|
||
logistician_id,
|
||
status,
|
||
selected_by_client_at
|
||
)
|
||
select
|
||
o.id,
|
||
'2026-04-15'::date,
|
||
'Вторая половина дня',
|
||
(select id from public.users where email = 'mk7029953@yandex.ru' limit 1),
|
||
'confirmed',
|
||
timezone('utc', now())
|
||
from public.orders o
|
||
where o.order_number = 'CD-240032';
|
||
|
||
insert into public.delivery_invitations (
|
||
order_id,
|
||
token_hash,
|
||
state,
|
||
order_number,
|
||
customer_name,
|
||
customer_phone,
|
||
customer_messenger,
|
||
available_slots,
|
||
delivery_date,
|
||
delivery_time,
|
||
sent_at,
|
||
opened_at,
|
||
confirmed_at
|
||
)
|
||
select
|
||
o.id,
|
||
encode(digest('client-flow-1001', 'sha256'), 'hex'),
|
||
'awaiting_choice',
|
||
o.order_number,
|
||
o.customer ->> 'name',
|
||
o.customer ->> 'phone',
|
||
o.customer ->> 'messenger',
|
||
array[
|
||
to_char(current_date + 1, 'YYYY-MM-DD') || ', До обеда',
|
||
to_char(current_date + 1, 'YYYY-MM-DD') || ', После обеда',
|
||
to_char(current_date + 2, 'YYYY-MM-DD') || ', До обеда',
|
||
to_char(current_date + 2, 'YYYY-MM-DD') || ', После обеда'
|
||
],
|
||
(current_date + 1),
|
||
'До обеда',
|
||
timezone('utc', now()),
|
||
null,
|
||
null
|
||
from public.orders o
|
||
where o.order_number = 'CD-240031'
|
||
on conflict (order_id) do update set
|
||
token_hash = excluded.token_hash,
|
||
state = excluded.state,
|
||
order_number = excluded.order_number,
|
||
customer_name = excluded.customer_name,
|
||
customer_phone = excluded.customer_phone,
|
||
customer_messenger = excluded.customer_messenger,
|
||
available_slots = excluded.available_slots,
|
||
delivery_date = excluded.delivery_date,
|
||
delivery_time = excluded.delivery_time,
|
||
sent_at = excluded.sent_at,
|
||
opened_at = excluded.opened_at,
|
||
confirmed_at = excluded.confirmed_at,
|
||
updated_at = timezone('utc', now());
|
||
|
||
insert into public.integration_events (
|
||
order_id,
|
||
event_type,
|
||
direction,
|
||
source,
|
||
status,
|
||
payload,
|
||
error_message
|
||
)
|
||
select
|
||
o.id,
|
||
'delivery_invitation_created',
|
||
'outbound',
|
||
'seed',
|
||
'success',
|
||
jsonb_build_object(
|
||
'token', 'client-flow-1001',
|
||
'availableSlots', array[
|
||
to_char(current_date + 1, 'YYYY-MM-DD') || ', До обеда',
|
||
to_char(current_date + 1, 'YYYY-MM-DD') || ', После обеда',
|
||
to_char(current_date + 2, 'YYYY-MM-DD') || ', До обеда',
|
||
to_char(current_date + 2, 'YYYY-MM-DD') || ', После обеда'
|
||
]
|
||
),
|
||
null
|
||
from public.orders o
|
||
where o.order_number = 'CD-240031';
|
||
|
||
insert into public.order_history (order_id, action, old_status, new_status, user_id, metadata)
|
||
select o.id, 'Заказ импортирован из 1С', null, 'Новый', (select id from public.users where email = 'skylanguage@yandex.ru' limit 1), jsonb_build_object('source', '1c_xml_import')
|
||
from public.orders o where o.order_number = 'CD-240031';
|
||
|
||
insert into public.order_history (order_id, action, old_status, new_status, user_id, metadata)
|
||
select o.id, 'Заказ готов к отгрузке', 'В производстве', 'Готов к отгрузке', (select id from public.users where email = 'skylanguage@yandex.ru' limit 1), jsonb_build_object('source', '1c_xml')
|
||
from public.orders o where o.order_number = 'CD-240031';
|
||
|
||
insert into public.order_history (order_id, action, old_status, new_status, user_id, metadata)
|
||
select o.id, 'Запущено согласование доставки', 'Готов к отгрузке', 'Ожидает ответа клиента', (select id from public.users where email = 'mk7029953@yandex.ru' limit 1), jsonb_build_object('source', 'seed')
|
||
from public.orders o where o.order_number = 'CD-240031';
|
||
|
||
insert into public.order_history (order_id, action, old_status, new_status, user_id, metadata)
|
||
select o.id, 'Доставка согласована', 'Готов к отгрузке', 'Доставка согласована', (select id from public.users where email = 'mk7029953@yandex.ru' limit 1), jsonb_build_object('source', 'seed')
|
||
from public.orders o where o.order_number = 'CD-240032';
|
||
|
||
insert into public.order_history (order_id, action, old_status, new_status, user_id, metadata)
|
||
select o.id, 'Передан логисту', 'Ожидает ответа клиента', 'Передан логисту', (select id from public.users where email = 'mk7029953@yandex.ru' limit 1), jsonb_build_object('source', 'seed')
|
||
from public.orders o where o.order_number = 'CD-240033';
|
||
|
||
insert into public.order_history (order_id, action, old_status, new_status, user_id, metadata)
|
||
select o.id, 'Платное хранение', 'Передан логисту', 'Платное хранение', (select id from public.users where email = 'mk7029953@yandex.ru' limit 1), jsonb_build_object('source', 'seed')
|
||
from public.orders o where o.order_number = 'CD-240034';
|
||
|
||
insert into public.order_history (order_id, action, old_status, new_status, user_id, metadata)
|
||
select o.id, 'Доставка завершена', 'В пути', 'Доставлен', (select id from public.users where email = 'mk7029953@yandex.ru' limit 1), jsonb_build_object('source', 'seed')
|
||
from public.orders o where o.order_number = 'CD-240035';
|
||
|
||
insert into public.chat_messages (
|
||
order_id,
|
||
sender_name,
|
||
sender_type,
|
||
channel,
|
||
text,
|
||
payload
|
||
)
|
||
select
|
||
o.id,
|
||
'Система',
|
||
'bot',
|
||
'sms',
|
||
'Заказ CD-240031 готов. Выберите дату и половину дня доставки.',
|
||
jsonb_build_object('source', 'seed')
|
||
from public.orders o where o.order_number = 'CD-240031';
|
||
|
||
insert into public.chat_messages (
|
||
order_id,
|
||
sender_name,
|
||
sender_type,
|
||
channel,
|
||
text,
|
||
payload
|
||
)
|
||
select
|
||
o.id,
|
||
'Мария Волкова',
|
||
'client',
|
||
'telegram',
|
||
'Подтвержу позже, вернусь после 16:00.',
|
||
jsonb_build_object('source', 'seed')
|
||
from public.orders o where o.order_number = 'CD-240031';
|