package consult

import (
	"context"
	"encoding/json"
	"errors"
	"fmt"
	"strings"
	"time"

	"github.com/google/uuid"
	"github.com/jackc/pgx/v5"
	"github.com/jackc/pgx/v5/pgxpool"

	"github.com/yuxingu/digital-psychology/apps/api/internal/wechatpay"
)

// Service implements miniprogram consult APIs on PostgreSQL.
type Service struct {
	Pool *pgxpool.Pool
	Pay  wechatpay.Config
}

// Registered reports users.phone present.
func (s *Service) Registered(ctx context.Context, userID uuid.UUID) bool {
	var ok bool
	_ = s.Pool.QueryRow(ctx, `SELECT EXISTS(SELECT 1 FROM users WHERE id=$1 AND phone IS NOT NULL AND deleted_at IS NULL)`, userID).Scan(&ok)
	return ok
}

func (s *Service) Banners(ctx context.Context) ([]map[string]any, error) {
	rows, err := s.Pool.Query(ctx, `SELECT id, banner_name, banner_image, click_url, describe, order_index, created_at, jump_type FROM consult_banners ORDER BY order_index, id`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	out := []map[string]any{}
	for rows.Next() {
		var id, order, jump int64
		var name, img, url, desc string
		var created time.Time
		if err := rows.Scan(&id, &name, &img, &url, &desc, &order, &created, &jump); err != nil {
			return nil, err
		}
		out = append(out, map[string]any{
			"id": id, "bannerName": name, "bannerImage": img, "clickUrl": url,
			"describe": desc, "orderIndex": order, "createTime": created.Format(time.RFC3339), "jumpType": jump,
		})
	}
	return out, rows.Err()
}

func (s *Service) NewsPage(ctx context.Context, typ, showMain, pageNo, pageSize int) (map[string]any, error) {
	if pageNo <= 0 {
		pageNo = 1
	}
	if pageSize <= 0 {
		pageSize = 20
	}
	var total int
	q := `SELECT COUNT(*) FROM consult_news WHERE ($1=0 OR type=$1) AND ($2<0 OR show_main=$2)`
	if err := s.Pool.QueryRow(ctx, q, typ, showMain).Scan(&total); err != nil {
		return nil, err
	}
	rows, err := s.Pool.Query(ctx, `
		SELECT id, type, title, show_image, content, show_main
		FROM consult_news WHERE ($1=0 OR type=$1) AND ($2<0 OR show_main=$2)
		ORDER BY id DESC OFFSET $3 LIMIT $4`, typ, showMain, (pageNo-1)*pageSize, pageSize)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	list := []map[string]any{}
	for rows.Next() {
		var id, t, sm int64
		var title, img, content string
		if err := rows.Scan(&id, &t, &title, &img, &content, &sm); err != nil {
			return nil, err
		}
		list = append(list, map[string]any{"id": id, "type": t, "title": title, "showImage": img, "content": content, "showMain": sm})
	}
	return map[string]any{"list": list, "total": total}, rows.Err()
}

func (s *Service) NewsGet(ctx context.Context, id int64) (map[string]any, error) {
	var t, sm int64
	var title, img, content string
	err := s.Pool.QueryRow(ctx, `SELECT type, title, show_image, content, show_main FROM consult_news WHERE id=$1`, id).
		Scan(&t, &title, &img, &content, &sm)
	if errors.Is(err, pgx.ErrNoRows) {
		return nil, errors.New("资讯不存在")
	}
	if err != nil {
		return nil, err
	}
	return map[string]any{"id": id, "type": t, "title": title, "showImage": img, "content": content, "showMain": sm}, nil
}

func (s *Service) ProtocolByCode(ctx context.Context, code string) (map[string]any, error) {
	var id int64
	var title, contextText string
	err := s.Pool.QueryRow(ctx, `SELECT id, title, context FROM consult_protocols WHERE code=$1 AND status=1`, code).
		Scan(&id, &title, &contextText)
	if errors.Is(err, pgx.ErrNoRows) {
		return nil, errors.New("协议不存在")
	}
	if err != nil {
		return nil, err
	}
	return map[string]any{"id": id, "code": code, "title": title, "context": contextText}, nil
}

func (s *Service) TestList(ctx context.Context, showMain int) ([]map[string]any, error) {
	rows, err := s.Pool.Query(ctx, `
		SELECT id, test_name, sub_title, test_pic, total_num, show_main
		FROM consult_tests WHERE status=1 AND ($1<0 OR show_main=$1) ORDER BY id`, showMain)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	out := []map[string]any{}
	for rows.Next() {
		var id, total, sm int64
		var name, sub, pic string
		if err := rows.Scan(&id, &name, &sub, &pic, &total, &sm); err != nil {
			return nil, err
		}
		out = append(out, map[string]any{"id": id, "testName": name, "subTitle": sub, "testPic": pic, "totalNum": total, "showMain": sm})
	}
	return out, rows.Err()
}

func (s *Service) TestDetail(ctx context.Context, studyID int64) (map[string]any, error) {
	var name, sub, pic, intro, notice string
	var total int64
	err := s.Pool.QueryRow(ctx, `
		SELECT test_name, sub_title, test_pic, test_introduction, total_num, test_notice
		FROM consult_tests WHERE id=$1 AND status=1`, studyID).
		Scan(&name, &sub, &pic, &intro, &total, &notice)
	if errors.Is(err, pgx.ErrNoRows) {
		return nil, errors.New("测评不存在")
	}
	if err != nil {
		return nil, err
	}
	qrows, err := s.Pool.Query(ctx, `
		SELECT id, question_type, question_text, question_image, required, order_index
		FROM consult_questions WHERE test_id=$1 ORDER BY order_index, id`, studyID)
	if err != nil {
		return nil, err
	}
	defer qrows.Close()
	questions := []map[string]any{}
	for qrows.Next() {
		var qid, qt, req, ord int64
		var text, img string
		if err := qrows.Scan(&qid, &qt, &text, &img, &req, &ord); err != nil {
			return nil, err
		}
		orows, err := s.Pool.Query(ctx, `SELECT id, option_text, order_index FROM consult_options WHERE question_id=$1 ORDER BY order_index, id`, qid)
		if err != nil {
			return nil, err
		}
		opts := []map[string]any{}
		for orows.Next() {
			var oid, oord int64
			var ot string
			if err := orows.Scan(&oid, &ot, &oord); err != nil {
				orows.Close()
				return nil, err
			}
			opts = append(opts, map[string]any{"id": oid, "optionText": ot, "orderIndex": oord})
		}
		orows.Close()
		questions = append(questions, map[string]any{
			"id": qid, "questionType": qt, "questionText": text, "questionImgae": img,
			"required": req, "orderIndex": ord, "questionOptionVOList": opts,
		})
	}
	return map[string]any{
		"id": studyID, "testName": name, "subTitle": sub, "testPic": pic,
		"testIntroduction": intro, "totalNum": total, "testNotice": notice,
		"questionDetailVOList": questions,
	}, nil
}

type ChoiceIn struct {
	ID                     int64 `json:"id"`
	TotalTime              int   `json:"totalTime"`
	UserChoiceOptionVOList []struct {
		QuestionID int64 `json:"questionId"`
		OptionID   int64 `json:"optionId"`
	} `json:"userChoiceOptionVOList"`
}

func (s *Service) SaveChoice(ctx context.Context, userID uuid.UUID, in ChoiceIn) (int64, error) {
	if in.ID == 0 {
		return 0, errors.New("缺少测评")
	}
	score := 0
	for _, it := range in.UserChoiceOptionVOList {
		var sc int
		_ = s.Pool.QueryRow(ctx, `SELECT option_score FROM consult_options WHERE id=$1`, it.OptionID).Scan(&sc)
		score += sc
	}
	var resultID int64
	err := s.Pool.QueryRow(ctx, `
		SELECT id FROM consult_test_results
		WHERE test_id=$1 AND $2 BETWEEN min_score AND max_score
		ORDER BY id LIMIT 1`, in.ID, score).Scan(&resultID)
	if errors.Is(err, pgx.ErrNoRows) {
		err = s.Pool.QueryRow(ctx, `SELECT id FROM consult_test_results WHERE test_id=$1 ORDER BY min_score LIMIT 1`, in.ID).Scan(&resultID)
	}
	if err != nil {
		return 0, errors.New("暂无匹配结果")
	}
	raw, _ := json.Marshal(in.UserChoiceOptionVOList)
	var choiceID int64
	err = s.Pool.QueryRow(ctx, `
		INSERT INTO consult_user_choices(user_id, test_id, result_id, total_time, choice_info)
		VALUES ($1,$2,$3,$4,$5) RETURNING id`, userID, in.ID, resultID, in.TotalTime, raw).Scan(&choiceID)
	if err != nil {
		return 0, err
	}
	_, _ = s.Pool.Exec(ctx, `UPDATE consult_tests SET total_num=total_num+1, actual_num=actual_num+1 WHERE id=$1`, in.ID)
	return resultID, nil
}

func (s *Service) GetResult(ctx context.Context, resultID int64) (map[string]any, error) {
	var testID int64
	var desc, analysis, plan, name, sub, pic string
	err := s.Pool.QueryRow(ctx, `
		SELECT r.test_id, r.result_desc, r.result_analysis, r.treat_plan, t.test_name, t.sub_title, t.test_pic
		FROM consult_test_results r JOIN consult_tests t ON t.id=r.test_id
		WHERE r.id=$1`, resultID).Scan(&testID, &desc, &analysis, &plan, &name, &sub, &pic)
	if errors.Is(err, pgx.ErrNoRows) {
		return nil, errors.New("结果不存在")
	}
	if err != nil {
		return nil, err
	}
	return map[string]any{
		"id": resultID, "testId": testID, "testName": name, "subTitle": sub, "testPic": pic,
		"resultDesc": desc, "resultAnalysis": analysis, "treatPlan": plan,
	}, nil
}

func (s *Service) MyTests(ctx context.Context, userID uuid.UUID, pageNo, pageSize int) (map[string]any, error) {
	if pageNo <= 0 {
		pageNo = 1
	}
	if pageSize <= 0 {
		pageSize = 10
	}
	var total int
	_ = s.Pool.QueryRow(ctx, `SELECT COUNT(*) FROM consult_user_choices WHERE user_id=$1`, userID).Scan(&total)
	rows, err := s.Pool.Query(ctx, `
		SELECT c.id, t.test_name, t.test_pic, t.sub_title, c.result_id, to_char(c.created_at,'YYYY-MM-DD HH24:MI')
		FROM consult_user_choices c JOIN consult_tests t ON t.id=c.test_id
		WHERE c.user_id=$1 ORDER BY c.id DESC OFFSET $2 LIMIT $3`, userID, (pageNo-1)*pageSize, pageSize)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	list := []map[string]any{}
	for rows.Next() {
		var id, rid int64
		var name, pic, sub, start string
		if err := rows.Scan(&id, &name, &pic, &sub, &rid, &start); err != nil {
			return nil, err
		}
		list = append(list, map[string]any{"id": id, "testName": name, "testPic": pic, "subTitle": sub, "testResultId": rid, "startTime": start})
	}
	return map[string]any{"list": list, "total": total}, rows.Err()
}

func (s *Service) Scopes(ctx context.Context) ([]map[string]any, error) {
	rows, err := s.Pool.Query(ctx, `SELECT code, name FROM consult_business_scopes ORDER BY code`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	out := []map[string]any{}
	for rows.Next() {
		var code, name string
		if err := rows.Scan(&code, &name); err != nil {
			return nil, err
		}
		out = append(out, map[string]any{"code": code, "name": name})
	}
	return out, rows.Err()
}

func (s *Service) DoctorPage(ctx context.Context, scope string, isTop, pageNo, pageSize int) (map[string]any, error) {
	if pageNo <= 0 {
		pageNo = 1
	}
	if pageSize <= 0 {
		pageSize = 20
	}
	scope = strings.TrimSpace(scope)
	var total int
	_ = s.Pool.QueryRow(ctx, `
		SELECT COUNT(*) FROM consult_doctors
		WHERE status=1 AND ($1='' OR business_scope LIKE '%'||$1||'%') AND ($2<0 OR is_top=$2)`, scope, isTop).Scan(&total)
	rows, err := s.Pool.Query(ctx, `
		SELECT id, name, avatar, business_scope, tags, consultation_method, introduction, price
		FROM consult_doctors
		WHERE status=1 AND ($1='' OR business_scope LIKE '%'||$1||'%') AND ($2<0 OR is_top=$2)
		ORDER BY is_top DESC, id OFFSET $3 LIMIT $4`, scope, isTop, (pageNo-1)*pageSize, pageSize)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	list := []map[string]any{}
	for rows.Next() {
		var id, price int64
		var name, avatar, bs, tags, method, intro string
		if err := rows.Scan(&id, &name, &avatar, &bs, &tags, &method, &intro, &price); err != nil {
			return nil, err
		}
		list = append(list, map[string]any{
			"id": id, "name": name, "avatar": avatar, "businessScope": bs, "tags": tags,
			"consultationMethod": method, "introduction": intro, "price": price, "availableDate": "",
		})
	}
	return map[string]any{"list": list, "total": total}, rows.Err()
}

func (s *Service) DoctorGet(ctx context.Context, id int64, userID uuid.UUID) (map[string]any, error) {
	var name, avatar, bs, cover, method, edu, intro, resume, notice, tags, exp, addr, addrD string
	var price, status, showN, top int64
	var workStart *time.Time
	var created time.Time
	err := s.Pool.QueryRow(ctx, `
		SELECT name, avatar, business_scope, cover_url, consultation_method, education, introduction, resume, notice, tags,
			work_experience, work_start_time, price, status, address, address_detail, show_service_num, is_top, created_at
		FROM consult_doctors WHERE id=$1`, id).Scan(
		&name, &avatar, &bs, &cover, &method, &edu, &intro, &resume, &notice, &tags,
		&exp, &workStart, &price, &status, &addr, &addrD, &showN, &top, &created)
	if errors.Is(err, pgx.ErrNoRows) {
		return nil, errors.New("咨询师不存在")
	}
	if err != nil {
		return nil, err
	}
	focus := false
	if userID != uuid.Nil {
		_ = s.Pool.QueryRow(ctx, `SELECT EXISTS(SELECT 1 FROM consult_focus WHERE user_id=$1 AND doctor_id=$2 AND status=1)`, userID, id).Scan(&focus)
	}
	ws := ""
	if workStart != nil {
		ws = workStart.Format("2006-01-02")
	}
	if strings.TrimSpace(cover) == "" {
		cover = avatar
	}
	return map[string]any{
		"id": id, "userId": 0, "name": name, "avatar": avatar, "businessScope": bs, "coverUrl": cover,
		"consultationMethod": method, "education": edu, "introduction": intro, "resume": resume,
		"notice": notice, "tags": tags, "workExperience": exp, "workStartTime": ws, "price": price,
		"createTime": created.Format(time.RFC3339), "focus": focus, "address": addr, "addressDetail": addrD,
	}, nil
}

func (s *Service) SetFocus(ctx context.Context, userID uuid.UUID, doctorID int64, on bool) error {
	st := 0
	if on {
		st = 1
	}
	_, err := s.Pool.Exec(ctx, `
		INSERT INTO consult_focus(user_id, doctor_id, status, read_status)
		VALUES ($1,$2,$3,0)
		ON CONFLICT (user_id, doctor_id) DO UPDATE SET status=$3, read_status=0`, userID, doctorID, st)
	return err
}

func (s *Service) ShowInfo(ctx context.Context, doctorID int64, userID uuid.UUID) (map[string]any, error) {
	d, err := s.DoctorGet(ctx, doctorID, userID)
	if err != nil {
		return nil, err
	}
	out := map[string]any{
		"id": d["id"], "name": d["name"], "avatar": d["avatar"],
		"consultationMethod": d["consultationMethod"], "price": d["price"], "address": d["address"],
	}
	if userID != uuid.Nil {
		var name, phone string
		var sex int
		var bday *time.Time
		var em string
		err := s.Pool.QueryRow(ctx, `
			SELECT appointment_name, birthday, phone, sex, emergency_contact_info
			FROM consult_orders WHERE user_id=$1 AND user_deleted=0 ORDER BY id DESC LIMIT 1`, userID).
			Scan(&name, &bday, &phone, &sex, &em)
		if err == nil {
			bs := ""
			if bday != nil {
				bs = bday.Format("2006-01-02")
			}
			emPhone, emType := "", 1
			var obj map[string]any
			if json.Unmarshal([]byte(em), &obj) == nil {
				if v, ok := obj["phone"].(string); ok {
					emPhone = v
				}
				switch v := obj["type"].(type) {
				case float64:
					emType = int(v)
				}
			}
			out["appointmentInfoAppRespVO"] = map[string]any{
				"appointmentName": name, "birthday": bs, "phone": phone, "sex": sex,
				"emergencyContactPhone": emPhone, "emergencyContactType": emType,
			}
		}
	}
	return out, nil
}

func (s *Service) RemainList(ctx context.Context, doctorID int64) ([]map[string]any, error) {
	rows, err := s.Pool.Query(ctx, `
		SELECT d.schedule_date, d.id,
			(SELECT COUNT(*) FROM consult_slots s WHERE s.schedule_id=d.id AND s.status=0)
		FROM consult_schedule_days d
		WHERE d.doctor_id=$1 AND d.schedule_date >= CURRENT_DATE AND d.schedule_date < CURRENT_DATE+30
		ORDER BY d.schedule_date`, doctorID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	out := []map[string]any{}
	for rows.Next() {
		var day time.Time
		var sid, remain int64
		if err := rows.Scan(&day, &sid, &remain); err != nil {
			return nil, err
		}
		out = append(out, map[string]any{"scheduleDate": day.Format("2006-01-02"), "remainNum": remain, "scheduleDateId": sid})
	}
	return out, rows.Err()
}

func (s *Service) DateDetail(ctx context.Context, doctorID int64) ([]map[string]any, error) {
	rows, err := s.Pool.Query(ctx, `
		SELECT d.id, d.schedule_date FROM consult_schedule_days d
		WHERE d.doctor_id=$1 AND d.schedule_date >= CURRENT_DATE AND d.schedule_date < CURRENT_DATE+13
		ORDER BY d.schedule_date`, doctorID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	out := []map[string]any{}
	for rows.Next() {
		var sid int64
		var day time.Time
		if err := rows.Scan(&sid, &day); err != nil {
			return nil, err
		}
		srows, err := s.Pool.Query(ctx, `
			SELECT id, start_time, end_time, consultation_method FROM consult_slots
			WHERE schedule_id=$1 AND status=0 ORDER BY start_time`, sid)
		if err != nil {
			return nil, err
		}
		slots := []map[string]any{}
		for srows.Next() {
			var id int64
			var st, et time.Time
			var method string
			if err := srows.Scan(&id, &st, &et, &method); err != nil {
				srows.Close()
				return nil, err
			}
			slots = append(slots, map[string]any{
				"slotId": id, "startTime": st.Format("15:04:05"), "endTime": et.Format("15:04:05"),
				"consultationMethod": method,
			})
		}
		srows.Close()
		out = append(out, map[string]any{
			"scheduleId": sid, "scheduleDate": day.Format("2006-01-02"),
			"weekStr": weekdayCN(day), "slotVOList": slots,
		})
	}
	return out, rows.Err()
}

type OrderIn struct {
	SlotID               int64  `json:"slotId"`
	AppointmentName      string `json:"appointmentName"`
	Birthday             string `json:"birthday"`
	Phone                string `json:"phone"`
	Sex                  int    `json:"sex"`
	EmergencyContactInfo string `json:"emergencyContactInfo"`
	TotalAmount          int    `json:"totalAmount"`
	ConsultationMethod   string `json:"consultationMethod"`
}

func (s *Service) CreateOrder(ctx context.Context, userID uuid.UUID, in OrderIn) (wechatpay.JSAPIParams, error) {
	var doctorID int64
	var day time.Time
	var st, et time.Time
	var status int
	var price int
	err := s.Pool.QueryRow(ctx, `
		SELECT s.doctor_id, d.schedule_date, s.start_time, s.end_time, s.status, doc.price
		FROM consult_slots s
		JOIN consult_schedule_days d ON d.id=s.schedule_id
		JOIN consult_doctors doc ON doc.id=s.doctor_id
		WHERE s.id=$1`, in.SlotID).Scan(&doctorID, &day, &st, &et, &status, &price)
	if errors.Is(err, pgx.ErrNoRows) {
		return wechatpay.JSAPIParams{}, errors.New("时段不存在")
	}
	if err != nil {
		return wechatpay.JSAPIParams{}, err
	}
	if status != 0 {
		return wechatpay.JSAPIParams{}, errors.New("该时段已被预约")
	}
	if in.TotalAmount > 0 && in.TotalAmount != price {
		return wechatpay.JSAPIParams{}, errors.New("价格已变化，请刷新")
	}
	tx, err := s.Pool.Begin(ctx)
	if err != nil {
		return wechatpay.JSAPIParams{}, err
	}
	defer tx.Rollback(ctx)
	tag, err := tx.Exec(ctx, `UPDATE consult_slots SET status=1 WHERE id=$1 AND status=0`, in.SlotID)
	if err != nil || tag.RowsAffected() == 0 {
		return wechatpay.JSAPIParams{}, errors.New("该时段已被预约")
	}
	sn := fmt.Sprintf("YG%s%06d", time.Now().Format("20060102150405"), in.SlotID%1000000)
	method := in.ConsultationMethod
	if method == "" {
		method = "online"
	}
	var bday *time.Time
	if in.Birthday != "" {
		if t, e := time.Parse("2006-01-02", in.Birthday); e == nil {
			bday = &t
		}
	}
	valid := time.Now().Add(15 * time.Minute)
	var oid int64
	err = tx.QueryRow(ctx, `
		INSERT INTO consult_orders(order_sn, slot_id, doctor_id, user_id, appointment_date, start_time, end_time, status,
			appointment_name, birthday, phone, sex, emergency_contact_info, total_amount, valid_time, consultation_method)
		VALUES ($1,$2,$3,$4,$5,$6,$7,0,$8,$9,$10,$11,$12,$13,$14,$15) RETURNING id`,
		sn, in.SlotID, doctorID, userID, day, st, et, in.AppointmentName, bday, in.Phone, in.Sex,
		in.EmergencyContactInfo, price, valid, method).Scan(&oid)
	if err != nil {
		return wechatpay.JSAPIParams{}, err
	}
	openid := ""
	_ = tx.QueryRow(ctx, `SELECT COALESCE(wx_openid,'') FROM users WHERE id=$1`, userID).Scan(&openid)
	pay, err := wechatpay.UnifiedOrder(s.Pay, openid, sn, price)
	if err != nil {
		return wechatpay.JSAPIParams{}, err
	}
	raw, _ := json.Marshal(pay)
	_, _ = tx.Exec(ctx, `UPDATE consult_orders SET pay_param=$2 WHERE id=$1`, oid, raw)
	if err := tx.Commit(ctx); err != nil {
		return wechatpay.JSAPIParams{}, err
	}
	return pay, nil
}

func (s *Service) PayParam(ctx context.Context, userID uuid.UUID, orderSn string) (wechatpay.JSAPIParams, error) {
	var raw []byte
	var owner uuid.UUID
	err := s.Pool.QueryRow(ctx, `SELECT user_id, pay_param FROM consult_orders WHERE order_sn=$1 AND user_deleted=0`, orderSn).
		Scan(&owner, &raw)
	if err != nil {
		return wechatpay.JSAPIParams{}, errors.New("订单不存在")
	}
	if owner != userID {
		return wechatpay.JSAPIParams{}, errors.New("无权查看")
	}
	var p wechatpay.JSAPIParams
	if len(raw) > 0 {
		_ = json.Unmarshal(raw, &p)
	}
	if p.PaySign == "" {
		p = wechatpay.MockJSAPI()
	}
	return p, nil
}

func (s *Service) OrderList(ctx context.Context, userID uuid.UUID, pageNo, pageSize int) (map[string]any, error) {
	if pageNo <= 0 {
		pageNo = 1
	}
	if pageSize <= 0 {
		pageSize = 10
	}
	var total int
	_ = s.Pool.QueryRow(ctx, `SELECT COUNT(*) FROM consult_orders WHERE user_id=$1 AND user_deleted=0`, userID).Scan(&total)
	rows, err := s.Pool.Query(ctx, `
		SELECT o.id, o.doctor_id, o.order_sn, d.name, d.avatar, o.consultation_method, o.status, o.total_amount,
			to_char(o.start_time,'HH24:MI:SS'), to_char(o.end_time,'HH24:MI:SS'), o.valid_time, o.created_at, o.cancel_time, o.cancel_flag
		FROM consult_orders o JOIN consult_doctors d ON d.id=o.doctor_id
		WHERE o.user_id=$1 AND o.user_deleted=0
		ORDER BY o.id DESC OFFSET $2 LIMIT $3`, userID, (pageNo-1)*pageSize, pageSize)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	list := []map[string]any{}
	for rows.Next() {
		var id, did, status, amount, cancelFlag int64
		var sn, name, avatar, method, st, et string
		var valid, created *time.Time
		var cancel *time.Time
		if err := rows.Scan(&id, &did, &sn, &name, &avatar, &method, &status, &amount, &st, &et, &valid, &created, &cancel, &cancelFlag); err != nil {
			return nil, err
		}
		item := map[string]any{
			"id": id, "doctorId": did, "orderSn": sn, "name": name, "avatar": avatar,
			"consultationMethod": method, "status": status, "totalAmount": amount,
			"startTime": st, "endTime": et, "cancelFlag": cancelFlag,
		}
		if valid != nil {
			item["validTime"] = valid.Format(time.RFC3339)
		}
		if created != nil {
			item["createTime"] = created.Format(time.RFC3339)
		}
		if cancel != nil {
			item["cancelTime"] = cancel.Format(time.RFC3339)
		}
		list = append(list, item)
	}
	return map[string]any{"list": list, "total": total}, rows.Err()
}

func (s *Service) OrderDetail(ctx context.Context, userID uuid.UUID, orderID int64) (map[string]any, error) {
	var did int64
	var sn, aname, phone, method, em, addrD string
	var status, sex, amount, cancelFlag int64
	var bday *time.Time
	var st, et time.Time
	var day time.Time
	var name, avatar string
	err := s.Pool.QueryRow(ctx, `
		SELECT o.doctor_id, o.order_sn, o.appointment_name, o.birthday, o.phone, o.sex, o.emergency_contact_info,
			o.status, o.total_amount, o.consultation_method, o.cancel_flag, o.appointment_date, o.start_time, o.end_time,
			d.name, d.avatar, d.address_detail
		FROM consult_orders o JOIN consult_doctors d ON d.id=o.doctor_id
		WHERE o.id=$1 AND o.user_id=$2 AND o.user_deleted=0`, orderID, userID).
		Scan(&did, &sn, &aname, &bday, &phone, &sex, &em, &status, &amount, &method, &cancelFlag, &day, &st, &et, &name, &avatar, &addrD)
	if err != nil {
		return nil, errors.New("订单不存在")
	}
	bs := ""
	if bday != nil {
		bs = bday.Format("2006-01-02")
	}
	return map[string]any{
		"id": orderID, "doctorId": did, "orderSn": sn, "name": name, "avatar": avatar,
		"appointmentName": aname, "birthday": bs, "phone": phone, "sex": sex,
		"emergencyContactInfo": em, "status": status, "totalAmount": amount,
		"consultationMethod": method, "cancelFlag": cancelFlag, "addressDetail": addrD,
		"appointmentDate": day.Format("2006-01-02"),
		"startTime":       st.Format("15:04:05"), "endTime": et.Format("15:04:05"),
	}, nil
}

func (s *Service) CancelOrder(ctx context.Context, userID uuid.UUID, orderID int64) error {
	var slotID int64
	var status int
	err := s.Pool.QueryRow(ctx, `SELECT slot_id, status FROM consult_orders WHERE id=$1 AND user_id=$2 AND user_deleted=0`, orderID, userID).
		Scan(&slotID, &status)
	if err != nil {
		return errors.New("订单不存在")
	}
	if status != 0 {
		return errors.New("当前状态不可取消")
	}
	_, _ = s.Pool.Exec(ctx, `UPDATE consult_slots SET status=0 WHERE id=$1`, slotID)
	_, err = s.Pool.Exec(ctx, `UPDATE consult_orders SET status=9, cancel_time=now(), cancel_flag=2 WHERE id=$1`, orderID)
	return err
}

func (s *Service) DeleteOrder(ctx context.Context, userID uuid.UUID, orderID int64) error {
	tag, err := s.Pool.Exec(ctx, `UPDATE consult_orders SET user_deleted=1 WHERE id=$1 AND user_id=$2`, orderID, userID)
	if err != nil {
		return err
	}
	if tag.RowsAffected() == 0 {
		return errors.New("订单不存在")
	}
	return nil
}

func (s *Service) MarkPaid(ctx context.Context, orderSn string) error {
	_, err := s.Pool.Exec(ctx, `UPDATE consult_orders SET status=1, pay_time=now() WHERE order_sn=$1 AND status=0`, orderSn)
	return err
}

func (s *Service) DemoPay(ctx context.Context, userID uuid.UUID) (wechatpay.JSAPIParams, error) {
	openid := ""
	_ = s.Pool.QueryRow(ctx, `SELECT COALESCE(wx_openid,'') FROM users WHERE id=$1`, userID).Scan(&openid)
	return wechatpay.UnifiedOrder(s.Pay, openid, fmt.Sprintf("DEMO%d", time.Now().Unix()), 1)
}

func (s *Service) SelfInfo(ctx context.Context, userID uuid.UUID) (map[string]any, error) {
	var nick, avatar, phone, status string
	var created time.Time
	err := s.Pool.QueryRow(ctx, `SELECT COALESCE(nickname,''), COALESCE(avatar_url,''), COALESCE(phone,''), status, created_at FROM users WHERE id=$1`, userID).
		Scan(&nick, &avatar, &phone, &status, &created)
	if err != nil {
		return nil, errors.New("请先登录")
	}
	var stay, idCard, realName string
	_ = s.Pool.QueryRow(ctx, `SELECT COALESCE(stay_period,''), COALESCE(id_card,''), COALESCE(real_name,'') FROM consult_user_ext WHERE user_id=$1`, userID).
		Scan(&stay, &idCard, &realName)
	st := 1
	if status != "active" {
		st = 0
	}
	return map[string]any{
		"id": userID.String(), "status": st, "idCard": idCard, "realName": realName,
		"createTime": created.Format(time.RFC3339), "nickName": nick, "avatarUrl": avatar, "stayPeriod": stay, "phone": phone,
	}, nil
}

func (s *Service) UpdateSelf(ctx context.Context, userID uuid.UUID, nick, avatar, stay string) error {
	if nick != "" {
		_, _ = s.Pool.Exec(ctx, `UPDATE users SET nickname=$2, updated_at=now() WHERE id=$1`, userID, nick)
	}
	if avatar != "" {
		_, _ = s.Pool.Exec(ctx, `UPDATE users SET avatar_url=$2, updated_at=now() WHERE id=$1`, userID, avatar)
	}
	_, err := s.Pool.Exec(ctx, `
		INSERT INTO consult_user_ext(user_id, stay_period) VALUES ($1,$2)
		ON CONFLICT (user_id) DO UPDATE SET stay_period=COALESCE(NULLIF($2,''), consult_user_ext.stay_period), updated_at=now()`,
		userID, stay)
	return err
}

func (s *Service) NotRead(ctx context.Context, userID uuid.UUID) (map[string]any, error) {
	var focus, order int
	_ = s.Pool.QueryRow(ctx, `SELECT COUNT(*) FROM consult_focus WHERE user_id=$1 AND status=1 AND read_status=0`, userID).Scan(&focus)
	_ = s.Pool.QueryRow(ctx, `SELECT COUNT(*) FROM consult_orders WHERE user_id=$1 AND user_deleted=0 AND user_read=0`, userID).Scan(&order)
	return map[string]any{"focusNum": focus, "orderNum": order}, nil
}

func (s *Service) FocusToRead(ctx context.Context, userID uuid.UUID) error {
	_, err := s.Pool.Exec(ctx, `UPDATE consult_focus SET read_status=1 WHERE user_id=$1`, userID)
	return err
}

func (s *Service) OrderToRead(ctx context.Context, userID uuid.UUID) error {
	_, err := s.Pool.Exec(ctx, `UPDATE consult_orders SET user_read=1 WHERE user_id=$1`, userID)
	return err
}

func (s *Service) FocusAll(ctx context.Context, userID uuid.UUID) ([]map[string]any, error) {
	rows, err := s.Pool.Query(ctx, `
		SELECT d.id, d.name, d.avatar, d.show_service_num, d.introduction, d.price, d.work_start_time
		FROM consult_focus f JOIN consult_doctors d ON d.id=f.doctor_id
		WHERE f.user_id=$1 AND f.status=1 ORDER BY f.id DESC`, userID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	out := []map[string]any{}
	for rows.Next() {
		var id, show, price int64
		var name, avatar, intro string
		var ws *time.Time
		if err := rows.Scan(&id, &name, &avatar, &show, &intro, &price, &ws); err != nil {
			return nil, err
		}
		workNum := 0
		if ws != nil {
			workNum = time.Now().Year() - ws.Year()
			if workNum < 0 {
				workNum = 0
			}
		}
		out = append(out, map[string]any{
			"id": id, "name": name, "avatar": avatar, "workNum": workNum,
			"showServiceNum": show, "introduction": intro, "price": price,
			"workStartTime": timeOrEmpty(ws),
		})
	}
	return out, rows.Err()
}

func (s *Service) Feedback(ctx context.Context, userID uuid.UUID, text string) error {
	if strings.TrimSpace(text) == "" {
		return errors.New("请填写内容")
	}
	phone := ""
	_ = s.Pool.QueryRow(ctx, `SELECT COALESCE(phone,'') FROM users WHERE id=$1`, userID).Scan(&phone)
	_, err := s.Pool.Exec(ctx, `INSERT INTO consult_feedback(user_id, content_text, contact) VALUES ($1,$2,$3)`, userID, text, phone)
	return err
}

func (s *Service) FeedbackFlag(ctx context.Context, userID uuid.UUID) (bool, error) {
	var n int
	_ = s.Pool.QueryRow(ctx, `SELECT COUNT(*) FROM consult_feedback WHERE user_id=$1 AND created_at > now()-interval '5 minutes'`, userID).Scan(&n)
	return n == 0, nil
}

func weekdayCN(t time.Time) string {
	names := []string{"星期日", "星期一", "星期二", "星期三", "星期四", "星期五", "星期六"}
	return names[int(t.Weekday())]
}

func timeOrEmpty(t *time.Time) string {
	if t == nil {
		return ""
	}
	return t.Format("2006-01-02")
}