subtrack/internal/store/checkins.go

package store

import (
	"database/sql"
	"errors"

	"daili/internal/domain"
)

// Checkin 每日服务打卡。
type Checkin struct {
	ID           int64
	SubID        int64
	ServiceDate  string
	Status       string
	Note         string
	OperatorID   int64
	OperatorName string
	CreatedAt    string
	UpdatedAt    string
}

// SaveCheckin 打卡（同订阅同日覆盖）。
func (db *DB) SaveCheckin(subID int64, date, status, note string, operatorID int64) error {
	if status != "done" && status != "skipped" {
		return errors.New("打卡状态不正确")
	}
	if _, err := domain.ParseDate(date); err != nil {
		return errors.New("日期格式不正确")
	}
	return db.withTx(func(tx *sql.Tx) error {
		s, err := scanSub(tx.QueryRow(subSelect+` WHERE s.id=?`, subID))
		if err != nil {
			return err
		}
		if s.Status == domain.StatusClosed {
			return errors.New("订阅已结束，无法打卡")
		}
		now := nowStamp()
		res, err := tx.Exec(`UPDATE checkins SET status=?, note=?, operator_id=?, updated_at=? WHERE sub_id=? AND service_date=?`,
			status, note, nullIfZero(operatorID), now, subID, date)
		if err != nil {
			return err
		}
		if n, _ := res.RowsAffected(); n > 0 {
			return nil
		}
		_, err = tx.Exec(`INSERT INTO checkins(sub_id,service_date,status,note,operator_id,created_at,updated_at)
			VALUES(?,?,?,?,?,?,?)`, subID, date, status, note, nullIfZero(operatorID), now, now)
		return err
	})
}

// DeleteCheckin 撤销打卡，返回所属订阅 ID。
func (db *DB) DeleteCheckin(id, operatorID int64) (int64, error) {
	var subID int64
	err := db.withTx(func(tx *sql.Tx) error {
		if err := tx.QueryRow(`SELECT sub_id FROM checkins WHERE id=?`, id).Scan(&subID); err != nil {
			if errors.Is(err, sql.ErrNoRows) {
				return ErrNotFound
			}
			return err
		}
		_, err := tx.Exec(`DELETE FROM checkins WHERE id=?`, id)
		return err
	})
	return subID, err
}

// CheckinsBetween 取区间内打卡，索引为 [subID][date]。
func (db *DB) CheckinsBetween(from, to string) (map[int64]map[string]Checkin, error) {
	rows, err := db.sql.Query(`SELECT c.id,c.sub_id,c.service_date,c.status,c.note,IFNULL(c.operator_id,0),
		IFNULL(u.display_name,''),c.created_at,c.updated_at
		FROM checkins c LEFT JOIN users u ON u.id=c.operator_id
		WHERE c.service_date BETWEEN ? AND ?`, from, to)
	if err != nil {
		return nil, err
	}
	return scanCheckinMap(rows)
}

func scanCheckinMap(rows *sql.Rows) (map[int64]map[string]Checkin, error) {
	out := map[int64]map[string]Checkin{}
	defer rows.Close()
	for rows.Next() {
		var c Checkin
		if err := rows.Scan(&c.ID, &c.SubID, &c.ServiceDate, &c.Status, &c.Note, &c.OperatorID,
			&c.OperatorName, &c.CreatedAt, &c.UpdatedAt); err != nil {
			return nil, err
		}
		if out[c.SubID] == nil {
			out[c.SubID] = map[string]Checkin{}
		}
		out[c.SubID][c.ServiceDate] = c
	}
	return out, rows.Err()
}

// CheckinsForSub 某订阅的打卡历史。
func (db *DB) CheckinsForSub(subID int64, limit int) ([]Checkin, error) {
	rows, err := db.sql.Query(`SELECT c.id,c.sub_id,c.service_date,c.status,c.note,IFNULL(c.operator_id,0),
		IFNULL(u.display_name,''),c.created_at,c.updated_at
		FROM checkins c LEFT JOIN users u ON u.id=c.operator_id
		WHERE c.sub_id=? ORDER BY c.service_date DESC LIMIT ?`, subID, limit)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	out := []Checkin{}
	for rows.Next() {
		var c Checkin
		if err := rows.Scan(&c.ID, &c.SubID, &c.ServiceDate, &c.Status, &c.Note, &c.OperatorID,
			&c.OperatorName, &c.CreatedAt, &c.UpdatedAt); err != nil {
			return nil, err
		}
		out = append(out, c)
	}
	return out, rows.Err()
}

// CheckinFor 取某订阅某天的打卡。
func (db *DB) CheckinFor(subID int64, date string) (*Checkin, error) {
	var c Checkin
	err := db.sql.QueryRow(`SELECT c.id,c.sub_id,c.service_date,c.status,c.note,IFNULL(c.operator_id,0),
		IFNULL(u.display_name,''),c.created_at,c.updated_at
		FROM checkins c LEFT JOIN users u ON u.id=c.operator_id
		WHERE c.sub_id=? AND c.service_date=?`, subID, date).
		Scan(&c.ID, &c.SubID, &c.ServiceDate, &c.Status, &c.Note, &c.OperatorID, &c.OperatorName, &c.CreatedAt, &c.UpdatedAt)
	if err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			return nil, ErrNotFound
		}
		return nil, err
	}
	return &c, nil
}