package service

import (
	"fmt"
	"regexp"
	"strings"

	"gorm.io/gorm"

	"nl-pms-api/internal/commonservice"
	"nl-pms-api/internal/model"
)

// TeamService 团队协作：团队/成员/任务/共享/日报/摘要。
type TeamService struct {
	DB *gorm.DB
}

var teamDateRe = regexp.MustCompile(`^\d{4}-\d{2}-\d{2}$`)

const teamDisplayExpr = `COALESCE(NULLIF(p.nickname,''), u.username)`

// ---------- DTO ----------

type TeamInfo struct {
	ID         int64  `json:"id"`
	Name       string `json:"name"`
	OwnerID    int64  `json:"ownerId"`
	DigestTime string `json:"digestTime"`
	Role       string `json:"role"`
	Members    int    `json:"members"`
}

type TeamMemberDTO struct {
	UserID   int64    `json:"userId"`
	Username string   `json:"username"`
	Nickname string   `json:"nickname"`
	Title    string   `json:"title"`
	Bio      string   `json:"bio"`
	TechTags []string `json:"techTags"`
	Avatar   string   `json:"avatar"`
	Role     string   `json:"role"`
	JoinedAt string   `json:"joinedAt"`
}

type TeamTaskDTO struct {
	ID          int64  `json:"id"`
	TeamID      int64  `json:"teamId"`
	Kind        string `json:"kind"`
	Title       string `json:"title"`
	Description string `json:"description"`
	Priority    string `json:"priority"`
	Status      string `json:"status"`
	CreatorID   int64  `json:"creatorId"`
	Creator     string `json:"creator"`
	AssigneeID  int64  `json:"assigneeId"`
	Assignee    string `json:"assignee"`
	StartAt     string `json:"startAt"`
	DueAt       string `json:"dueAt"`
	UrgedAt     string `json:"urgedAt"`
	History     string `json:"history"`
	UpdatedAt   string `json:"updatedAt"`
}

type TeamSharedItem struct {
	Kind      string `json:"kind"`
	UUID      string `json:"uuid"`
	UserID    int64  `json:"userId"`
	Owner     string `json:"owner"`
	Title     string `json:"title"`
	Status    string `json:"status"`
	Priority  string `json:"priority"`
	DueAt     string `json:"dueAt"`
	UpdatedAt string `json:"updatedAt"`
}

type TeamReportDTO struct {
	UserID      int64  `json:"userId"`
	User        string `json:"user"`
	Date        string `json:"date"`
	Content     string `json:"content"`
	SubmittedAt string `json:"submittedAt"`
}

type TeamDigestDTO struct {
	Date        string `json:"date"`
	Content     string `json:"content"`
	Provider    string `json:"provider"`
	GeneratedAt string `json:"generatedAt"`
}

type TeamReportBoard struct {
	Date    string          `json:"date"`
	Role    string          `json:"role"`
	Reports []TeamReportDTO `json:"reports"`
	Missing []TeamMemberDTO `json:"missing"`
	Digest  *TeamDigestDTO  `json:"digest"`
}

// ---------- helpers ----------

func (s *TeamService) teamNotice(teamID, toUser, fromUser int64, kind, refID, content string) {
	if toUser <= 0 || toUser == fromUser {
		return
	}
	_ = s.DB.Exec(`INSERT INTO team_notices(team_id,to_user,from_user,kind,ref_id,content,created_at) VALUES(?,?,?,?,?,?,?)`,
		teamID, toUser, fromUser, kind, refID, content, commonservice.NowRFC())
}

func (s *TeamService) teamUserName(userID int64) string {
	var name string
	_ = s.DB.Raw(`SELECT `+teamDisplayExpr+` FROM users u LEFT JOIN user_profiles p ON p.user_id=u.id WHERE u.id=?`, userID).Scan(&name)
	return name
}

func parseClockOK(at string) bool {
	var h, m int
	if _, e := fmt.Sscanf(at, "%d:%d", &h, &m); e != nil || h < 0 || h > 23 || m < 0 || m > 59 {
		return false
	}
	parts := strings.SplitN(at, ":", 2)
	return len(parts) == 2
}

func teamTaskKindLabel(kind string) string {
	if kind == "ticket" {
		return "工单"
	}
	return "任务"
}

func teamStatusLabel(st string) string {
	switch st {
	case "doing":
		return "进行中"
	case "done":
		return "已完成"
	case "closed":
		return "已关闭"
	}
	return "待处理"
}

func appendHistoryNode(history, status, at string) string {
	node := fmt.Sprintf(`{"status":%q,"at":%q}`, status, at)
	h := strings.TrimSpace(history)
	if h == "" || h == "[]" {
		return "[" + node + "]"
	}
	if strings.HasSuffix(h, "]") {
		return h[:len(h)-1] + "," + node + "]"
	}
	return "[" + node + "]"
}

func stripFence(s string) string {
	s = strings.TrimSpace(s)
	if strings.HasPrefix(s, "```") {
		s = strings.TrimPrefix(s, "```")
		if i := strings.IndexByte(s, '\n'); i >= 0 {
			s = s[i+1:]
		}
		s = strings.TrimSuffix(strings.TrimSpace(s), "```")
	}
	return strings.TrimSpace(s)
}

// ---------- 团队 CRUD ----------

func (s *TeamService) Create(userID int64, name string) (*TeamInfo, error) {
	name = strings.TrimSpace(name)
	if name == "" || len([]rune(name)) > 64 {
		return nil, commonservice.BadRequest("TEAM_NAME_INVALID")
	}
	now := commonservice.NowRFC()
	team := model.Team{Name: name, OwnerID: userID, DigestTime: "21:00", CreatedAt: now}
	if err := s.DB.Create(&team).Error; err != nil {
		return nil, commonservice.Internal("TEAM_CREATE_FAILED")
	}
	if err := s.DB.Exec(`INSERT INTO team_members(team_id,user_id,role,joined_at) VALUES(?,?,?,?)`, team.ID, userID, "owner", now).Error; err != nil {
		return nil, commonservice.Internal("TEAM_CREATE_FAILED")
	}
	return &TeamInfo{ID: team.ID, Name: name, OwnerID: userID, DigestTime: "21:00", Role: "owner", Members: 1}, nil
}

func (s *TeamService) List(userID int64) ([]TeamInfo, error) {
	rows, err := s.DB.Raw(`SELECT t.id,t.name,t.owner_id,t.digest_time,m.role,
		(SELECT COUNT(*) FROM team_members x WHERE x.team_id=t.id)
		FROM teams t JOIN team_members m ON m.team_id=t.id AND m.user_id=? ORDER BY t.id`, userID).Rows()
	if err != nil {
		return nil, commonservice.Internal("QUERY_FAILED")
	}
	defer rows.Close()
	out := []TeamInfo{}
	for rows.Next() {
		var t TeamInfo
		if err := rows.Scan(&t.ID, &t.Name, &t.OwnerID, &t.DigestTime, &t.Role, &t.Members); err != nil {
			return nil, commonservice.Internal("QUERY_FAILED")
		}
		out = append(out, t)
	}
	return out, nil
}

func (s *TeamService) Rename(teamID, userID int64, name string) error {
	name = strings.TrimSpace(name)
	if name == "" || len([]rune(name)) > 64 {
		return commonservice.BadRequest("TEAM_NAME_INVALID")
	}
	if _, err := commonservice.RequireTeamRole(s.DB, teamID, userID, "owner"); err != nil {
		return err
	}
	return s.DB.Exec(`UPDATE teams SET name=? WHERE id=?`, name, teamID).Error
}

func (s *TeamService) SetDigestTime(teamID, userID int64, at string) error {
	if !parseClockOK(at) {
		return commonservice.BadRequest("TEAM_TIME_INVALID")
	}
	if _, err := commonservice.RequireTeamRole(s.DB, teamID, userID, "admin"); err != nil {
		return err
	}
	return s.DB.Exec(`UPDATE teams SET digest_time=? WHERE id=?`, at, teamID).Error
}

func (s *TeamService) Dissolve(teamID, userID int64) error {
	if _, err := commonservice.RequireTeamRole(s.DB, teamID, userID, "owner"); err != nil {
		return err
	}
	var name string
	_ = s.DB.Raw(`SELECT name FROM teams WHERE id=?`, teamID).Scan(&name)
	var members []int64
	_ = s.DB.Raw(`SELECT user_id FROM team_members WHERE team_id=?`, teamID).Scan(&members)
	for _, m := range members {
		s.teamNotice(teamID, m, userID, "member", "", fmt.Sprintf("团队「%s」已被解散", name))
	}
	for _, q := range []string{
		`DELETE FROM team_tasks WHERE team_id=?`,
		`DELETE FROM team_reports WHERE team_id=?`,
		`DELETE FROM team_digests WHERE team_id=?`,
		`DELETE FROM team_members WHERE team_id=?`,
		`DELETE FROM teams WHERE id=?`,
	} {
		if err := s.DB.Exec(q, teamID).Error; err != nil {
			return commonservice.Internal("TEAM_DISSOLVE_FAILED")
		}
	}
	return nil
}

func (s *TeamService) Leave(teamID, userID int64) error {
	role, err := commonservice.RequireTeamRole(s.DB, teamID, userID, "member")
	if err != nil {
		return err
	}
	if role == "owner" {
		return commonservice.BadRequest("TEAM_OWNER_CANNOT_LEAVE")
	}
	if err := s.DB.Exec(`DELETE FROM team_members WHERE team_id=? AND user_id=?`, teamID, userID).Error; err != nil {
		return commonservice.Internal("TEAM_LEAVE_FAILED")
	}
	var ownerID int64
	_ = s.DB.Raw(`SELECT owner_id FROM teams WHERE id=?`, teamID).Scan(&ownerID)
	s.teamNotice(teamID, ownerID, userID, "member", "", fmt.Sprintf("%s 退出了团队", s.teamUserName(userID)))
	return nil
}

// ---------- 成员 ----------

func (s *TeamService) Members(teamID, userID int64) ([]TeamMemberDTO, error) {
	if _, err := commonservice.RequireTeamRole(s.DB, teamID, userID, "member"); err != nil {
		return nil, err
	}
	return s.memberRows(teamID)
}

func (s *TeamService) memberRows(teamID int64) ([]TeamMemberDTO, error) {
	rows, err := s.DB.Raw(`SELECT m.user_id,u.username,COALESCE(p.nickname,''),COALESCE(p.title,''),COALESCE(p.bio,''),
		COALESCE(p.tech_tags,'[]'),COALESCE(p.avatar_thumb,''),m.role,m.joined_at
		FROM team_members m JOIN users u ON u.id=m.user_id LEFT JOIN user_profiles p ON p.user_id=m.user_id
		WHERE m.team_id=? ORDER BY CASE m.role WHEN 'owner' THEN 1 WHEN 'admin' THEN 2 ELSE 3 END, m.joined_at`, teamID).Rows()
	if err != nil {
		return nil, commonservice.Internal("QUERY_FAILED")
	}
	defer rows.Close()
	out := []TeamMemberDTO{}
	for rows.Next() {
		var m TeamMemberDTO
		var tags string
		if err := rows.Scan(&m.UserID, &m.Username, &m.Nickname, &m.Title, &m.Bio, &tags, &m.Avatar, &m.Role, &m.JoinedAt); err != nil {
			return nil, commonservice.Internal("QUERY_FAILED")
		}
		m.TechTags = parseTechTags(tags)
		out = append(out, m)
	}
	return out, nil
}

func (s *TeamService) Invite(teamID, userID int64, username, role string) error {
	if role != "member" && role != "admin" {
		role = "member"
	}
	username = strings.TrimSpace(username)
	if _, err := commonservice.RequireTeamRole(s.DB, teamID, userID, "admin"); err != nil {
		return err
	}
	var target int64
	if err := s.DB.Raw(`SELECT id FROM users WHERE username=?`, username).Scan(&target).Error; err != nil || target == 0 {
		return commonservice.NotFound("TEAM_USER_NOT_FOUND")
	}
	res := s.DB.Exec(`INSERT IGNORE INTO team_members(team_id,user_id,role,joined_at) VALUES(?,?,?,?)`,
		teamID, target, role, commonservice.NowRFC())
	if res.Error != nil {
		return commonservice.Internal("TEAM_INVITE_FAILED")
	}
	if res.RowsAffected == 0 {
		return commonservice.Conflict("TEAM_ALREADY_MEMBER")
	}
	var name string
	_ = s.DB.Raw(`SELECT name FROM teams WHERE id=?`, teamID).Scan(&name)
	s.teamNotice(teamID, target, userID, "member", "", fmt.Sprintf("%s 把你加入了团队「%s」", s.teamUserName(userID), name))
	return nil
}

func (s *TeamService) SetRole(teamID, actorID, targetUserID int64, role string) error {
	if role != "member" && role != "admin" {
		return commonservice.BadRequest("TEAM_ROLE_INVALID")
	}
	if _, err := commonservice.RequireTeamRole(s.DB, teamID, actorID, "owner"); err != nil {
		return err
	}
	target, err := commonservice.TeamRole(s.DB, teamID, targetUserID)
	if err != nil {
		return err
	}
	if target == "" || target == "owner" {
		return commonservice.BadRequest("TEAM_ROLE_INVALID")
	}
	if err := s.DB.Exec(`UPDATE team_members SET role=? WHERE team_id=? AND user_id=?`, role, teamID, targetUserID).Error; err != nil {
		return commonservice.Internal("SAVE_FAILED")
	}
	label := "成员"
	if role == "admin" {
		label = "管理员"
	}
	s.teamNotice(teamID, targetUserID, actorID, "role", "", fmt.Sprintf("你的团队角色已调整为「%s」", label))
	return nil
}

func (s *TeamService) RemoveMember(teamID, actorID, targetUserID int64) error {
	actor, err := commonservice.RequireTeamRole(s.DB, teamID, actorID, "admin")
	if err != nil {
		return err
	}
	target, err := commonservice.TeamRole(s.DB, teamID, targetUserID)
	if err != nil {
		return err
	}
	if target == "" || target == "owner" || targetUserID == actorID || (actor == "admin" && target != "member") {
		return commonservice.Forbidden("TEAM_FORBIDDEN")
	}
	if err := s.DB.Exec(`DELETE FROM team_members WHERE team_id=? AND user_id=?`, teamID, targetUserID).Error; err != nil {
		return commonservice.Internal("SAVE_FAILED")
	}
	var name string
	_ = s.DB.Raw(`SELECT name FROM teams WHERE id=?`, teamID).Scan(&name)
	s.teamNotice(teamID, targetUserID, actorID, "member", "", fmt.Sprintf("你已被移出团队「%s」", name))
	return nil
}

// ---------- 任务 ----------

func validTeamTask(t *TeamTaskDTO) error {
	t.Title = strings.TrimSpace(t.Title)
	if t.Title == "" {
		return commonservice.BadRequest("TEAM_TASK_TITLE_REQUIRED")
	}
	if t.Kind != "ticket" {
		t.Kind = "todo"
	}
	switch t.Priority {
	case "low", "medium", "high":
	default:
		t.Priority = "medium"
	}
	return nil
}

func (s *TeamService) TaskSave(userID int64, t TeamTaskDTO) (*TeamTaskDTO, error) {
	if err := validTeamTask(&t); err != nil {
		return nil, err
	}
	if _, err := commonservice.RequireTeamRole(s.DB, t.TeamID, userID, "admin"); err != nil {
		return nil, err
	}
	if t.AssigneeID > 0 {
		r, err := commonservice.TeamRole(s.DB, t.TeamID, t.AssigneeID)
		if err != nil || r == "" {
			return nil, commonservice.BadRequest("TEAM_ASSIGNEE_INVALID")
		}
	}
	now := commonservice.NowRFC()
	if t.ID == 0 {
		history := fmt.Sprintf(`[{"status":"open","at":%q}]`, now)
		rec := model.TeamTask{
			TeamID: t.TeamID, Kind: t.Kind, Title: t.Title, Description: t.Description,
			Priority: t.Priority, Status: "open", CreatorID: userID, AssigneeID: t.AssigneeID,
			StartAt: t.StartAt, DueAt: t.DueAt, History: history, UpdatedAt: now,
		}
		if err := s.DB.Create(&rec).Error; err != nil {
			return nil, commonservice.Internal("SAVE_FAILED")
		}
		t.ID, t.Status, t.CreatorID, t.History, t.UpdatedAt = rec.ID, "open", userID, history, now
		if t.AssigneeID > 0 {
			s.teamNotice(t.TeamID, t.AssigneeID, userID, "assign", fmt.Sprint(t.ID),
				fmt.Sprintf("%s 给你指派了%s：%s", s.teamUserName(userID), teamTaskKindLabel(t.Kind), t.Title))
		}
		return &t, nil
	}
	var prevAssignee int64
	var n int64
	s.DB.Raw(`SELECT COUNT(*) FROM team_tasks WHERE id=? AND team_id=? AND deleted=0`, t.ID, t.TeamID).Scan(&n)
	if n == 0 {
		return nil, commonservice.NotFound("TEAM_TASK_NOT_FOUND")
	}
	_ = s.DB.Raw(`SELECT assignee_id FROM team_tasks WHERE id=? AND team_id=? AND deleted=0`, t.ID, t.TeamID).Scan(&prevAssignee)
	if err := s.DB.Exec(`UPDATE team_tasks SET kind=?,title=?,description=?,priority=?,assignee_id=?,start_at=?,due_at=?,updated_at=? WHERE id=? AND team_id=?`,
		t.Kind, t.Title, t.Description, t.Priority, t.AssigneeID, t.StartAt, t.DueAt, now, t.ID, t.TeamID).Error; err != nil {
		return nil, commonservice.Internal("SAVE_FAILED")
	}
	if t.AssigneeID > 0 && t.AssigneeID != prevAssignee {
		s.teamNotice(t.TeamID, t.AssigneeID, userID, "assign", fmt.Sprint(t.ID),
			fmt.Sprintf("%s 给你指派了%s：%s", s.teamUserName(userID), teamTaskKindLabel(t.Kind), t.Title))
	}
	t.UpdatedAt = now
	return &t, nil
}

func (s *TeamService) TaskSetStatus(teamID, taskID, userID int64, status string) error {
	switch status {
	case "open", "doing", "done", "closed":
	default:
		return commonservice.BadRequest("TEAM_STATUS_INVALID")
	}
	role, err := commonservice.RequireTeamRole(s.DB, teamID, userID, "member")
	if err != nil {
		return err
	}
	var assignee, creator int64
	var title, history, kind string
	row := s.DB.Raw(`SELECT assignee_id,creator_id,title,history,kind FROM team_tasks WHERE id=? AND team_id=? AND deleted=0`, taskID, teamID).Row()
	if err := row.Scan(&assignee, &creator, &title, &history, &kind); err != nil {
		return commonservice.NotFound("TEAM_TASK_NOT_FOUND")
	}
	if commonservice.TeamRoleRank(role) < commonservice.TeamRoleRank("admin") && userID != assignee {
		return commonservice.Forbidden("TEAM_FORBIDDEN")
	}
	now := commonservice.NowRFC()
	history = appendHistoryNode(history, status, now)
	if err := s.DB.Exec(`UPDATE team_tasks SET status=?,history=?,updated_at=? WHERE id=?`, status, history, now, taskID).Error; err != nil {
		return commonservice.Internal("SAVE_FAILED")
	}
	if (status == "done" || status == "closed") && userID != creator {
		s.teamNotice(teamID, creator, userID, "status", fmt.Sprint(taskID),
			fmt.Sprintf("%s 将%s「%s」标记为%s", s.teamUserName(userID), teamTaskKindLabel(kind), title, teamStatusLabel(status)))
	}
	return nil
}

func (s *TeamService) TaskUrge(teamID, taskID, userID int64) error {
	if _, err := commonservice.RequireTeamRole(s.DB, teamID, userID, "admin"); err != nil {
		return err
	}
	var assignee int64
	var title, kind string
	if err := s.DB.Raw(`SELECT assignee_id,title,kind FROM team_tasks WHERE id=? AND team_id=? AND deleted=0`, taskID, teamID).
		Row().Scan(&assignee, &title, &kind); err != nil {
		return commonservice.NotFound("TEAM_TASK_NOT_FOUND")
	}
	if assignee <= 0 {
		return commonservice.BadRequest("TEAM_NO_ASSIGNEE")
	}
	now := commonservice.NowRFC()
	if err := s.DB.Exec(`UPDATE team_tasks SET urged_at=?,updated_at=? WHERE id=?`, now, now, taskID).Error; err != nil {
		return commonservice.Internal("SAVE_FAILED")
	}
	s.teamNotice(teamID, assignee, userID, "urge", fmt.Sprint(taskID),
		fmt.Sprintf("%s 催办了%s：%s", s.teamUserName(userID), teamTaskKindLabel(kind), title))
	return nil
}

func (s *TeamService) TaskDelete(teamID, taskID, userID int64) error {
	if _, err := commonservice.RequireTeamRole(s.DB, teamID, userID, "admin"); err != nil {
		return err
	}
	return s.DB.Exec(`UPDATE team_tasks SET deleted=1,updated_at=? WHERE id=? AND team_id=?`, commonservice.NowRFC(), taskID, teamID).Error
}

func (s *TeamService) TaskList(teamID, userID int64, filter string) ([]TeamTaskDTO, error) {
	if _, err := commonservice.RequireTeamRole(s.DB, teamID, userID, "member"); err != nil {
		return nil, err
	}
	q := `SELECT t.id,t.team_id,t.kind,t.title,t.description,t.priority,t.status,t.creator_id,t.assignee_id,
		t.start_at,t.due_at,t.urged_at,t.history,t.updated_at,
		COALESCE((SELECT ` + teamDisplayExpr + ` FROM users u LEFT JOIN user_profiles p ON p.user_id=u.id WHERE u.id=t.creator_id),''),
		COALESCE((SELECT ` + teamDisplayExpr + ` FROM users u LEFT JOIN user_profiles p ON p.user_id=u.id WHERE u.id=t.assignee_id),'')
		FROM team_tasks t WHERE t.team_id=? AND t.deleted=0`
	args := []any{teamID}
	switch filter {
	case "mine":
		q += ` AND t.assignee_id=?`
		args = append(args, userID)
	case "created":
		q += ` AND t.creator_id=?`
		args = append(args, userID)
	case "open":
		q += ` AND t.status IN ('open','doing')`
	}
	q += ` ORDER BY t.updated_at DESC LIMIT 500`
	rows, err := s.DB.Raw(q, args...).Rows()
	if err != nil {
		return nil, commonservice.Internal("QUERY_FAILED")
	}
	defer rows.Close()
	out := []TeamTaskDTO{}
	for rows.Next() {
		var t TeamTaskDTO
		if err := rows.Scan(&t.ID, &t.TeamID, &t.Kind, &t.Title, &t.Description, &t.Priority, &t.Status, &t.CreatorID, &t.AssigneeID,
			&t.StartAt, &t.DueAt, &t.UrgedAt, &t.History, &t.UpdatedAt, &t.Creator, &t.Assignee); err != nil {
			return nil, commonservice.Internal("QUERY_FAILED")
		}
		out = append(out, t)
	}
	return out, nil
}

// ---------- 共享 ----------

func (s *TeamService) SharedItems(teamID, userID int64) ([]TeamSharedItem, error) {
	if _, err := commonservice.RequireTeamRole(s.DB, teamID, userID, "member"); err != nil {
		return nil, err
	}
	q := `SELECT 'todo',s.uuid,s.user_id,` + teamDisplayExpr + `,s.title,s.status,s.priority,s.due_at,s.updated_at
		FROM sync_todos s JOIN users u ON u.id=s.user_id LEFT JOIN user_profiles p ON p.user_id=s.user_id
		WHERE s.team_id=? AND s.deleted=0
		UNION ALL
		SELECT 'ticket',s.uuid,s.user_id,` + teamDisplayExpr + `,s.title,s.status,s.priority,s.due_at,s.updated_at
		FROM sync_tickets s JOIN users u ON u.id=s.user_id LEFT JOIN user_profiles p ON p.user_id=s.user_id
		WHERE s.team_id=? AND s.deleted=0
		ORDER BY 9 DESC LIMIT 500`
	rows, err := s.DB.Raw(q, teamID, teamID).Rows()
	if err != nil {
		return nil, commonservice.Internal("QUERY_FAILED")
	}
	defer rows.Close()
	out := []TeamSharedItem{}
	for rows.Next() {
		var it TeamSharedItem
		if err := rows.Scan(&it.Kind, &it.UUID, &it.UserID, &it.Owner, &it.Title, &it.Status, &it.Priority, &it.DueAt, &it.UpdatedAt); err != nil {
			return nil, commonservice.Internal("QUERY_FAILED")
		}
		out = append(out, it)
	}
	return out, nil
}

func (s *TeamService) UrgeShared(teamID, userID int64, kind, uuid string) error {
	if _, err := commonservice.RequireTeamRole(s.DB, teamID, userID, "admin"); err != nil {
		return err
	}
	table := "sync_todos"
	if kind == "ticket" {
		table = "sync_tickets"
	}
	var owner int64
	var title string
	if err := s.DB.Raw(`SELECT user_id,title FROM `+table+` WHERE uuid=? AND team_id=? AND deleted=0`, uuid, teamID).
		Row().Scan(&owner, &title); err != nil {
		return commonservice.NotFound("TEAM_TASK_NOT_FOUND")
	}
	s.teamNotice(teamID, owner, userID, "urge", uuid,
		fmt.Sprintf("%s 催办了你共享的%s：%s", s.teamUserName(userID), teamTaskKindLabel(kind), title))
	return nil
}

// ---------- 日报 / 摘要 ----------

func (s *TeamService) ReportSubmit(teamID, userID int64, date, content string) error {
	if !teamDateRe.MatchString(date) {
		return commonservice.BadRequest("TEAM_DATE_INVALID")
	}
	content = strings.TrimSpace(content)
	if content == "" {
		return commonservice.BadRequest("TEAM_REPORT_EMPTY")
	}
	if _, err := commonservice.RequireTeamRole(s.DB, teamID, userID, "member"); err != nil {
		return err
	}
	return s.DB.Exec(`INSERT INTO team_reports(team_id,user_id,date,content,submitted_at) VALUES(?,?,?,?,?)
		ON DUPLICATE KEY UPDATE content=VALUES(content),submitted_at=VALUES(submitted_at)`,
		teamID, userID, date, content, commonservice.NowRFC()).Error
}

func (s *TeamService) ReportBoardGet(teamID, userID int64, date string) (*TeamReportBoard, error) {
	if !teamDateRe.MatchString(date) {
		return nil, commonservice.BadRequest("TEAM_DATE_INVALID")
	}
	role, err := commonservice.RequireTeamRole(s.DB, teamID, userID, "member")
	if err != nil {
		return nil, err
	}
	board := &TeamReportBoard{Date: date, Role: role, Reports: []TeamReportDTO{}, Missing: []TeamMemberDTO{}}
	rows, err := s.DB.Raw(`SELECT r.user_id,`+teamDisplayExpr+`,r.content,r.submitted_at
		FROM team_reports r JOIN users u ON u.id=r.user_id LEFT JOIN user_profiles p ON p.user_id=r.user_id
		WHERE r.team_id=? AND r.date=? ORDER BY r.submitted_at`, teamID, date).Rows()
	if err != nil {
		return nil, commonservice.Internal("QUERY_FAILED")
	}
	submitted := map[int64]bool{}
	for rows.Next() {
		var r TeamReportDTO
		if err := rows.Scan(&r.UserID, &r.User, &r.Content, &r.SubmittedAt); err != nil {
			rows.Close()
			return nil, commonservice.Internal("QUERY_FAILED")
		}
		r.Date = date
		if commonservice.TeamRoleRank(role) < commonservice.TeamRoleRank("admin") && r.UserID != userID {
			r.Content = ""
		}
		submitted[r.UserID] = true
		board.Reports = append(board.Reports, r)
	}
	rows.Close()
	members, err := s.memberRows(teamID)
	if err != nil {
		return nil, err
	}
	for _, m := range members {
		if !submitted[m.UserID] {
			board.Missing = append(board.Missing, m)
		}
	}
	var d TeamDigestDTO
	err = s.DB.Raw(`SELECT date,content,provider,generated_at FROM team_digests WHERE team_id=? AND date=?`, teamID, date).
		Row().Scan(&d.Date, &d.Content, &d.Provider, &d.GeneratedAt)
	if err == nil {
		d.Content = stripFence(d.Content)
		board.Digest = &d
	}
	return board, nil
}

func (s *TeamService) ReportUrge(teamID, actorID, targetUserID int64, date string) error {
	if !teamDateRe.MatchString(date) {
		return commonservice.BadRequest("TEAM_DATE_INVALID")
	}
	if _, err := commonservice.RequireTeamRole(s.DB, teamID, actorID, "admin"); err != nil {
		return err
	}
	r, err := commonservice.TeamRole(s.DB, teamID, targetUserID)
	if err != nil || r == "" {
		return commonservice.NotFound("TEAM_USER_NOT_FOUND")
	}
	s.teamNotice(teamID, targetUserID, actorID, "report_urge", date,
		fmt.Sprintf("%s 提醒你提交 %s 的团队日报", s.teamUserName(actorID), date))
	return nil
}

// DigestSave 存客户端本地 AI 生成后的摘要（admin+）。
func (s *TeamService) DigestSave(teamID, userID int64, date, content, provider string) error {
	if !teamDateRe.MatchString(date) {
		return commonservice.BadRequest("TEAM_DATE_INVALID")
	}
	content = strings.TrimSpace(content)
	if content == "" {
		return commonservice.BadRequest("TEAM_DIGEST_EMPTY")
	}
	if _, err := commonservice.RequireTeamRole(s.DB, teamID, userID, "admin"); err != nil {
		return err
	}
	return s.DB.Exec(`INSERT INTO team_digests(team_id,date,content,provider,generated_at) VALUES(?,?,?,?,?)
		ON DUPLICATE KEY UPDATE content=VALUES(content),provider=VALUES(provider),generated_at=VALUES(generated_at)`,
		teamID, date, content, provider, commonservice.NowRFC()).Error
}