yuriandClaude Opus 5.5
9749da5ae7
Co-Authored-By: Claude Opus 5.5 (1M context) <noreply@anthropic.com>
101 lines
6.7 KiB
PL/PgSQL
101 lines
6.7 KiB
PL/PgSQL
-- После seed-demo-one.mjs: оставляет в пуле «Демо: Агентство» около 30 объектов и раскладывает историю
|
|
-- по датам, чтобы аналитика, динамика и карточки выглядели как у агентства, работающего три месяца.
|
|
|
|
BEGIN;
|
|
|
|
CREATE TEMP TABLE demo ON COMMIT DROP AS SELECT id FROM companies WHERE name = 'Демо: Агентство';
|
|
|
|
-- 1. Пул: остаются по 15 самых свежих нетронутых объектов аренды и продажи; остальные нетронутые удаляются.
|
|
CREATE TEMP TABLE extra ON COMMIT DROP AS
|
|
SELECT id FROM (
|
|
SELECT l.id, row_number() OVER (PARTITION BY l.deal_type ORDER BY l.id DESC) AS n
|
|
FROM leads l
|
|
WHERE l.company_id IN (SELECT id FROM demo) AND l.assignee_id IS NULL
|
|
AND NOT EXISTS (SELECT 1 FROM events e WHERE e.lead_id = l.id AND e.type <> 'created')
|
|
AND NOT EXISTS (SELECT 1 FROM client_leads cl WHERE cl.lead_id = l.id)
|
|
AND NOT EXISTS (SELECT 1 FROM viewings v WHERE v.lead_id = l.id)
|
|
AND NOT EXISTS (SELECT 1 FROM deals d WHERE d.lead_id = l.id)
|
|
) t WHERE n > 15;
|
|
DELETE FROM events WHERE lead_id IN (SELECT id FROM extra);
|
|
DELETE FROM lead_notifications WHERE lead_id IN (SELECT id FROM extra);
|
|
DELETE FROM outbox WHERE lead_id IN (SELECT id FROM extra);
|
|
DELETE FROM leads WHERE id IN (SELECT id FROM extra);
|
|
|
|
-- 2. Компания и люди работают три с лишним месяца.
|
|
UPDATE companies SET created_at = now() - interval '100 days' WHERE id IN (SELECT id FROM demo);
|
|
UPDATE users SET created_at = now() - make_interval(days => 100 - (users.id % 5)) WHERE company_id IN (SELECT id FROM demo);
|
|
UPDATE invoices SET created_at = now() - interval '100 days' WHERE company_id IN (SELECT id FROM demo);
|
|
UPDATE payments SET created_at = now() - interval '99 days' WHERE invoice_id IN (SELECT id FROM invoices WHERE company_id IN (SELECT id FROM demo));
|
|
|
|
-- 3. Объекты. Со сделкой: пришли за 8–20 дней до неё. В работе: 1–20 дней назад. В пуле: за последние трое суток.
|
|
UPDATE leads SET created_at = CASE
|
|
WHEN d.deal_date IS NOT NULL THEN (d.deal_date + time '14:00') AT TIME ZONE 'Asia/Yerevan' - make_interval(days => 8 + (leads.id % 13), hours => leads.id % 7)
|
|
WHEN w.worked THEN now() - make_interval(days => 1 + (leads.id * 7 % 20), hours => leads.id * 5 % 9)
|
|
ELSE now() - make_interval(hours => leads.id * 13 % 70, mins => leads.id * 17 % 60) END
|
|
FROM leads l
|
|
LEFT JOIN deals d ON d.lead_id = l.id
|
|
CROSS JOIN LATERAL (SELECT EXISTS (SELECT 1 FROM events e WHERE e.lead_id = l.id AND e.type <> 'created') AS worked) w
|
|
WHERE leads.id = l.id AND l.company_id IN (SELECT id FROM demo);
|
|
|
|
-- События объекта — по порядку от его прихода до сделки (или до «час назад»).
|
|
WITH e AS (
|
|
SELECT ev.id, l.created_at,
|
|
coalesce((d.deal_date + time '14:00') AT TIME ZONE 'Asia/Yerevan', now() - interval '1 hour') AS until,
|
|
row_number() OVER (PARTITION BY ev.lead_id ORDER BY ev.id) - 1 AS n,
|
|
count(*) OVER (PARTITION BY ev.lead_id) AS cnt
|
|
FROM events ev JOIN leads l ON l.id = ev.lead_id LEFT JOIN deals d ON d.lead_id = l.id
|
|
WHERE l.company_id IN (SELECT id FROM demo)
|
|
)
|
|
UPDATE events SET at = e.created_at + (e.n::float / greatest(e.cnt - 1, 1)) * (e.until - e.created_at)
|
|
FROM e WHERE events.id = e.id;
|
|
|
|
-- 4. Клиенты. Со сделкой: заведены за 10–25 дней до неё; остальные — 2–16 дней назад. События — так же по порядку.
|
|
UPDATE clients SET created_at = CASE
|
|
WHEN d.deal_date IS NOT NULL THEN (d.deal_date + time '14:00') AT TIME ZONE 'Asia/Yerevan' - make_interval(days => 10 + (clients.id % 16), hours => clients.id % 6)
|
|
ELSE now() - make_interval(days => 2 + (clients.id * 5 % 15), hours => clients.id * 3 % 8) END
|
|
FROM clients c LEFT JOIN deals d ON d.client_id = c.id
|
|
WHERE clients.id = c.id AND c.company_id IN (SELECT id FROM demo);
|
|
|
|
WITH e AS (
|
|
SELECT ev.id, cl.created_at,
|
|
coalesce((d.deal_date + time '14:00') AT TIME ZONE 'Asia/Yerevan', now() - interval '1 hour') AS until,
|
|
row_number() OVER (PARTITION BY ev.client_id ORDER BY ev.id) - 1 AS n,
|
|
count(*) OVER (PARTITION BY ev.client_id) AS cnt
|
|
FROM events ev JOIN clients cl ON cl.id = ev.client_id LEFT JOIN deals d ON d.client_id = cl.id
|
|
WHERE cl.company_id IN (SELECT id FROM demo) AND ev.lead_id IS NULL
|
|
)
|
|
UPDATE events SET at = e.created_at + (e.n::float / greatest(e.cnt - 1, 1)) * (e.until - e.created_at)
|
|
FROM e WHERE events.id = e.id;
|
|
|
|
UPDATE client_leads SET created_at = greatest(cl.created_at, l.created_at) + interval '2 hours'
|
|
FROM clients cl, leads l
|
|
WHERE client_leads.client_id = cl.id AND client_leads.lead_id = l.id AND cl.company_id IN (SELECT id FROM demo);
|
|
|
|
UPDATE deals SET created_at = (deal_date + time '14:00') AT TIME ZONE 'Asia/Yerevan' WHERE company_id IN (SELECT id FROM demo);
|
|
|
|
-- 5. «Взят» — по записи истории; таймер возврата в пул отсчитывается от последней смены статуса.
|
|
UPDATE leads SET taken_at = t.at
|
|
FROM (SELECT lead_id, min(at) AS at FROM events WHERE type = 'taken' GROUP BY lead_id) t
|
|
WHERE leads.id = t.lead_id AND leads.company_id IN (SELECT id FROM demo);
|
|
|
|
UPDATE leads SET status_changed_at = s.at
|
|
FROM (SELECT lead_id, max(at) AS at FROM events WHERE type IN ('taken', 'status', 'deal', 'blacklisted') GROUP BY lead_id) s
|
|
WHERE leads.id = s.lead_id AND leads.company_id IN (SELECT id FROM demo);
|
|
|
|
UPDATE clients SET status_changed_at = s.at
|
|
FROM (SELECT client_id, max(at) AS at FROM events WHERE type IN ('status', 'deal') AND client_id IS NOT NULL GROUP BY client_id) s
|
|
WHERE clients.id = s.client_id AND clients.company_id IN (SELECT id FROM demo);
|
|
|
|
-- 6. Выплаты и ручные записи отмечены в день выплаты и в середине своего месяца.
|
|
UPDATE payouts SET created_at = (paid_at + time '11:00') AT TIME ZONE 'Asia/Yerevan' WHERE company_id IN (SELECT id FROM demo);
|
|
UPDATE pay_adjustments SET created_at = least(now(), ((month || '-15')::date + time '12:00') AT TIME ZONE 'Asia/Yerevan') WHERE company_id IN (SELECT id FROM demo);
|
|
|
|
COMMIT;
|
|
|
|
SELECT (SELECT count(*) FROM leads WHERE company_id = c.id) AS leads,
|
|
(SELECT count(*) FROM leads WHERE company_id = c.id AND assignee_id IS NULL) AS pool,
|
|
(SELECT count(*) FROM clients WHERE company_id = c.id) AS clients,
|
|
(SELECT count(*) FROM deals WHERE company_id = c.id) AS deals,
|
|
(SELECT count(*) FROM payouts WHERE company_id = c.id) AS payouts
|
|
FROM companies c WHERE c.name = 'Демо: Агентство';
|