241 lines
8.0 KiB
Go
241 lines
8.0 KiB
Go
package storage
|
||
|
||
import (
|
||
"context"
|
||
"encoding/json"
|
||
"time"
|
||
)
|
||
|
||
// ---------- Заявки ----------
|
||
|
||
type AdminLead struct {
|
||
ID int64 `json:"id"`
|
||
FormCode string `json:"form_code,omitempty"`
|
||
FormTitle string `json:"form_title,omitempty"`
|
||
Name string `json:"name"`
|
||
Phone string `json:"phone"`
|
||
Email string `json:"email,omitempty"`
|
||
ServiceName string `json:"service_name,omitempty"`
|
||
DoctorName string `json:"doctor_name,omitempty"`
|
||
Message string `json:"message,omitempty"`
|
||
Payload json.RawMessage `json:"payload,omitempty"`
|
||
Status string `json:"status"`
|
||
Comment string `json:"comment,omitempty"`
|
||
PageURL string `json:"page_url,omitempty"`
|
||
UTM json.RawMessage `json:"utm,omitempty"`
|
||
CreatedAt time.Time `json:"created_at"`
|
||
}
|
||
|
||
// AdminLeads: постранично, с фильтрами по статусу, тексту и календарным датам.
|
||
func (s *Storage) AdminLeads(ctx context.Context, status, q string, from, to *time.Time, limit, offset int) ([]AdminLead, int, error) {
|
||
var total int
|
||
if err := s.pool.QueryRow(ctx, `
|
||
SELECT count(*) FROM leads l
|
||
WHERE ($1 = '' OR l.status = $1)
|
||
AND ($2 = '' OR l.name ILIKE '%' || $2 || '%' OR l.phone ILIKE '%' || $2 || '%' OR l.message ILIKE '%' || $2 || '%')
|
||
AND ($3::timestamptz IS NULL OR l.created_at >= $3)
|
||
AND ($4::timestamptz IS NULL OR l.created_at < $4)`,
|
||
status, q, from, to).Scan(&total); err != nil {
|
||
return nil, 0, err
|
||
}
|
||
|
||
rows, err := s.pool.Query(ctx, `
|
||
SELECT l.id, COALESCE(f.code, ''), COALESCE(f.title, ''),
|
||
l.name, l.phone, l.email,
|
||
COALESCE(sv.name, ''), COALESCE(d.last_name || ' ' || d.first_name, ''),
|
||
l.message, l.payload, l.status, l.comment, l.page_url, l.utm, l.created_at
|
||
FROM leads l
|
||
LEFT JOIN forms f ON f.id = l.form_id
|
||
LEFT JOIN services sv ON sv.id = l.service_id
|
||
LEFT JOIN doctors d ON d.id = l.doctor_id
|
||
WHERE ($1 = '' OR l.status = $1)
|
||
AND ($2 = '' OR l.name ILIKE '%' || $2 || '%' OR l.phone ILIKE '%' || $2 || '%' OR l.message ILIKE '%' || $2 || '%')
|
||
AND ($3::timestamptz IS NULL OR l.created_at >= $3)
|
||
AND ($4::timestamptz IS NULL OR l.created_at < $4)
|
||
ORDER BY l.created_at DESC
|
||
LIMIT $5 OFFSET $6`, status, q, from, to, limit, offset)
|
||
if err != nil {
|
||
return nil, 0, err
|
||
}
|
||
defer rows.Close()
|
||
|
||
var out []AdminLead
|
||
for rows.Next() {
|
||
var a AdminLead
|
||
if err := rows.Scan(&a.ID, &a.FormCode, &a.FormTitle, &a.Name, &a.Phone, &a.Email,
|
||
&a.ServiceName, &a.DoctorName, &a.Message, &a.Payload,
|
||
&a.Status, &a.Comment, &a.PageURL, &a.UTM, &a.CreatedAt); err != nil {
|
||
return nil, 0, err
|
||
}
|
||
out = append(out, a)
|
||
}
|
||
return out, total, rows.Err()
|
||
}
|
||
|
||
// LeadCounts — сколько заявок в каждом статусе, для бейджей в интерфейсе.
|
||
func (s *Storage) LeadCounts(ctx context.Context) (map[string]int, error) {
|
||
rows, err := s.pool.Query(ctx, `SELECT status, count(*) FROM leads GROUP BY status`)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
defer rows.Close()
|
||
out := map[string]int{}
|
||
for rows.Next() {
|
||
var st string
|
||
var n int
|
||
if err := rows.Scan(&st, &n); err != nil {
|
||
return nil, err
|
||
}
|
||
out[st] = n
|
||
}
|
||
return out, rows.Err()
|
||
}
|
||
|
||
func (s *Storage) LeadTodayCount(ctx context.Context) (int, error) {
|
||
var count int
|
||
now := time.Now().In(time.Local)
|
||
start := time.Date(now.Year(), now.Month(), now.Day(), 0, 0, 0, 0, time.Local)
|
||
err := s.pool.QueryRow(ctx, `SELECT count(*) FROM leads WHERE created_at >= $1 AND created_at < $2`, start, start.AddDate(0, 0, 1)).Scan(&count)
|
||
return count, err
|
||
}
|
||
|
||
type LeadUpdate struct {
|
||
Status string `json:"status"`
|
||
Comment string `json:"comment"`
|
||
}
|
||
|
||
func (s *Storage) AdminUpdateLead(ctx context.Context, id int64, in LeadUpdate) error {
|
||
tag, err := s.pool.Exec(ctx, `
|
||
UPDATE leads SET status=$2, comment=$3, updated_at=now() WHERE id=$1`,
|
||
id, in.Status, in.Comment)
|
||
if err != nil {
|
||
return err
|
||
}
|
||
if tag.RowsAffected() == 0 {
|
||
return ErrNotFound
|
||
}
|
||
return nil
|
||
}
|
||
|
||
// ---------- Отзывы (включая непроверенные) ----------
|
||
|
||
type AdminReview struct {
|
||
ID int64 `json:"id"`
|
||
AuthorName string `json:"author_name"`
|
||
Rating *int `json:"rating"`
|
||
Text string `json:"text"`
|
||
VideoURL string `json:"video_url"`
|
||
DoctorID *int64 `json:"doctor_id"`
|
||
DoctorName string `json:"doctor_name,omitempty"`
|
||
Source string `json:"source"`
|
||
Status string `json:"status"`
|
||
Reply string `json:"reply"`
|
||
CreatedAt time.Time `json:"created_at"`
|
||
}
|
||
|
||
func (s *Storage) AdminReviews(ctx context.Context, status, q string, limit, offset int) ([]AdminReview, int, error) {
|
||
var total int
|
||
if err := s.pool.QueryRow(ctx, `
|
||
SELECT count(*) FROM reviews r
|
||
WHERE ($1 = '' OR r.status = $1)
|
||
AND ($2 = '' OR r.author_name ILIKE '%' || $2 || '%' OR r.text ILIKE '%' || $2 || '%')`,
|
||
status, q).Scan(&total); err != nil {
|
||
return nil, 0, err
|
||
}
|
||
|
||
rows, err := s.pool.Query(ctx, `
|
||
SELECT r.id, r.author_name, r.rating, r.text, r.video_url, r.doctor_id,
|
||
COALESCE(d.last_name || ' ' || d.first_name, ''),
|
||
r.source, r.status, r.reply, r.created_at
|
||
FROM reviews r
|
||
LEFT JOIN doctors d ON d.id = r.doctor_id
|
||
WHERE ($1 = '' OR r.status = $1)
|
||
AND ($2 = '' OR r.author_name ILIKE '%' || $2 || '%' OR r.text ILIKE '%' || $2 || '%')
|
||
ORDER BY
|
||
CASE r.status WHEN 'pending' THEN 0 ELSE 1 END, -- непроверенные сверху
|
||
r.created_at DESC
|
||
LIMIT $3 OFFSET $4`, status, q, limit, offset)
|
||
if err != nil {
|
||
return nil, 0, err
|
||
}
|
||
defer rows.Close()
|
||
|
||
var out []AdminReview
|
||
for rows.Next() {
|
||
var a AdminReview
|
||
if err := rows.Scan(&a.ID, &a.AuthorName, &a.Rating, &a.Text, &a.VideoURL, &a.DoctorID,
|
||
&a.DoctorName, &a.Source, &a.Status, &a.Reply, &a.CreatedAt); err != nil {
|
||
return nil, 0, err
|
||
}
|
||
out = append(out, a)
|
||
}
|
||
return out, total, rows.Err()
|
||
}
|
||
|
||
func (s *Storage) ReviewCounts(ctx context.Context) (map[string]int, error) {
|
||
rows, err := s.pool.Query(ctx, `SELECT status, count(*) FROM reviews GROUP BY status`)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
defer rows.Close()
|
||
out := map[string]int{}
|
||
for rows.Next() {
|
||
var st string
|
||
var n int
|
||
if err := rows.Scan(&st, &n); err != nil {
|
||
return nil, err
|
||
}
|
||
out[st] = n
|
||
}
|
||
return out, rows.Err()
|
||
}
|
||
|
||
type ReviewUpdate struct {
|
||
AuthorName string `json:"author_name"`
|
||
Rating *int `json:"rating"`
|
||
Text string `json:"text"`
|
||
VideoURL string `json:"video_url"`
|
||
DoctorID *int64 `json:"doctor_id"`
|
||
Status string `json:"status"`
|
||
Reply string `json:"reply"`
|
||
}
|
||
|
||
func (s *Storage) AdminUpdateReview(ctx context.Context, id int64, in ReviewUpdate) error {
|
||
// moderated_at проставляется, когда отзыв уходит из ожидания проверки
|
||
tag, err := s.pool.Exec(ctx, `
|
||
UPDATE reviews SET author_name=$2, rating=$3, text=$4, video_url=$5, doctor_id=$6,
|
||
status=$7, reply=$8,
|
||
moderated_at = CASE WHEN $7 <> 'pending' THEN now() ELSE moderated_at END,
|
||
updated_at = now()
|
||
WHERE id=$1`,
|
||
id, in.AuthorName, in.Rating, in.Text, in.VideoURL, in.DoctorID, in.Status, in.Reply)
|
||
if err != nil {
|
||
return wrapConflict(err)
|
||
}
|
||
if tag.RowsAffected() == 0 {
|
||
return ErrNotFound
|
||
}
|
||
return nil
|
||
}
|
||
|
||
func (s *Storage) AdminCreateReview(ctx context.Context, in ReviewUpdate) (int64, error) {
|
||
var id int64
|
||
err := s.pool.QueryRow(ctx, `
|
||
INSERT INTO reviews (author_name, rating, text, video_url, doctor_id, source, status, reply, moderated_at)
|
||
VALUES ($1,$2,$3,$4,$5,'manual',$6,$7, CASE WHEN $6 <> 'pending' THEN now() END)
|
||
RETURNING id`,
|
||
in.AuthorName, in.Rating, in.Text, in.VideoURL, in.DoctorID, in.Status, in.Reply).Scan(&id)
|
||
return id, wrapConflict(err)
|
||
}
|
||
|
||
func (s *Storage) AdminDeleteReview(ctx context.Context, id int64) error {
|
||
tag, err := s.pool.Exec(ctx, `DELETE FROM reviews WHERE id=$1`, id)
|
||
if err != nil {
|
||
return err
|
||
}
|
||
if tag.RowsAffected() == 0 {
|
||
return ErrNotFound
|
||
}
|
||
return nil
|
||
}
|