GPT_Management/backend/internal/repository/chatgpt_account_repo.go

package repository

import (
	"database/sql"
	"time"

	"gpt-manager-go/internal/models"
)

// ChatGPTAccountRepository ChatGPT 账号仓储
type ChatGPTAccountRepository struct {
	db *sql.DB
}

// NewChatGPTAccountRepository 创建仓储
func NewChatGPTAccountRepository(db *sql.DB) *ChatGPTAccountRepository {
	return &ChatGPTAccountRepository{db: db}
}

// Create 创建账号
func (r *ChatGPTAccountRepository) Create(account *models.ChatGPTAccount) error {
	return r.db.QueryRow(`
		INSERT INTO chatgpt_accounts (name, auth_token, team_account_id, seats_in_use, seats_entitled, 
			active_start, active_until, is_active, consecutive_failures, created_at)
		VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
		RETURNING id
	`,
		account.Name,
		account.AuthToken,
		account.TeamAccountID,
		account.SeatsInUse,
		account.SeatsEntitled,
		account.ActiveStart,
		account.ActiveUntil,
		account.IsActive,
		account.ConsecutiveFailures,
		time.Now(),
	).Scan(&account.ID)
}

// Update 更新账号
func (r *ChatGPTAccountRepository) Update(account *models.ChatGPTAccount) error {
	_, err := r.db.Exec(`
		UPDATE chatgpt_accounts SET
			name = $1, auth_token = $2, seats_in_use = $3, seats_entitled = $4,
			active_start = $5, active_until = $6, is_active = $7, 
			consecutive_failures = $8, last_check = $9, updated_at = $10
		WHERE id = $11
	`,
		account.Name,
		account.AuthToken,
		account.SeatsInUse,
		account.SeatsEntitled,
		account.ActiveStart,
		account.ActiveUntil,
		account.IsActive,
		account.ConsecutiveFailures,
		account.LastCheck,
		time.Now(),
		account.ID,
	)
	return err
}

// FindByID 根据 ID 查找
func (r *ChatGPTAccountRepository) FindByID(id int) (*models.ChatGPTAccount, error) {
	account := &models.ChatGPTAccount{}
	err := r.db.QueryRow(`
		SELECT id, name, auth_token, team_account_id, seats_in_use, seats_entitled,
			active_start, active_until, is_active, consecutive_failures,
			last_check, last_used, created_at, updated_at
		FROM chatgpt_accounts WHERE id = $1
	`, id).Scan(
		&account.ID, &account.Name, &account.AuthToken, &account.TeamAccountID,
		&account.SeatsInUse, &account.SeatsEntitled, &account.ActiveStart, &account.ActiveUntil,
		&account.IsActive, &account.ConsecutiveFailures, &account.LastCheck,
		&account.LastUsed, &account.CreatedAt, &account.UpdatedAt,
	)
	if err == sql.ErrNoRows {
		return nil, nil
	}
	return account, err
}

// FindByTeamAccountID 根据 Team Account ID 查找
func (r *ChatGPTAccountRepository) FindByTeamAccountID(teamAccountID string) (*models.ChatGPTAccount, error) {
	account := &models.ChatGPTAccount{}
	err := r.db.QueryRow(`
		SELECT id, name, auth_token, team_account_id, seats_in_use, seats_entitled,
			active_start, active_until, is_active, consecutive_failures,
			last_check, last_used, created_at, updated_at
		FROM chatgpt_accounts WHERE team_account_id = $1
	`, teamAccountID).Scan(
		&account.ID, &account.Name, &account.AuthToken, &account.TeamAccountID,
		&account.SeatsInUse, &account.SeatsEntitled, &account.ActiveStart, &account.ActiveUntil,
		&account.IsActive, &account.ConsecutiveFailures, &account.LastCheck,
		&account.LastUsed, &account.CreatedAt, &account.UpdatedAt,
	)
	if err == sql.ErrNoRows {
		return nil, nil
	}
	return account, err
}

// FindAll 获取所有账号
func (r *ChatGPTAccountRepository) FindAll() ([]*models.ChatGPTAccount, error) {
	rows, err := r.db.Query(`
		SELECT id, name, auth_token, team_account_id, seats_in_use, seats_entitled,
			active_start, active_until, is_active, consecutive_failures,
			last_check, last_used, created_at, updated_at
		FROM chatgpt_accounts ORDER BY created_at DESC
	`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var accounts []*models.ChatGPTAccount
	for rows.Next() {
		account := &models.ChatGPTAccount{}
		if err := rows.Scan(
			&account.ID, &account.Name, &account.AuthToken, &account.TeamAccountID,
			&account.SeatsInUse, &account.SeatsEntitled, &account.ActiveStart, &account.ActiveUntil,
			&account.IsActive, &account.ConsecutiveFailures, &account.LastCheck,
			&account.LastUsed, &account.CreatedAt, &account.UpdatedAt,
		); err != nil {
			return nil, err
		}
		accounts = append(accounts, account)
	}
	return accounts, nil
}

// FindActive 获取所有激活的账号
func (r *ChatGPTAccountRepository) FindActive() ([]*models.ChatGPTAccount, error) {
	rows, err := r.db.Query(`
		SELECT id, name, auth_token, team_account_id, seats_in_use, seats_entitled,
			active_start, active_until, is_active, consecutive_failures,
			last_check, last_used, created_at, updated_at
		FROM chatgpt_accounts WHERE is_active = true ORDER BY created_at DESC
	`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var accounts []*models.ChatGPTAccount
	for rows.Next() {
		account := &models.ChatGPTAccount{}
		if err := rows.Scan(
			&account.ID, &account.Name, &account.AuthToken, &account.TeamAccountID,
			&account.SeatsInUse, &account.SeatsEntitled, &account.ActiveStart, &account.ActiveUntil,
			&account.IsActive, &account.ConsecutiveFailures, &account.LastCheck,
			&account.LastUsed, &account.CreatedAt, &account.UpdatedAt,
		); err != nil {
			return nil, err
		}
		accounts = append(accounts, account)
	}
	return accounts, nil
}

// Delete 删除账号
func (r *ChatGPTAccountRepository) Delete(id int) error {
	_, err := r.db.Exec(`DELETE FROM chatgpt_accounts WHERE id = $1`, id)
	return err
}

// FindActiveWithAvailableSeats 查找激活且有可用席位的账号
func (r *ChatGPTAccountRepository) FindActiveWithAvailableSeats() ([]*models.ChatGPTAccount, error) {
	rows, err := r.db.Query(`
		SELECT id, name, auth_token, team_account_id, seats_in_use, seats_entitled,
			active_start, active_until, is_active, consecutive_failures,
			last_check, last_used, created_at, updated_at
		FROM chatgpt_accounts 
		WHERE is_active = true AND seats_entitled > seats_in_use
		ORDER BY (seats_entitled - seats_in_use) DESC, last_used ASC NULLS FIRST
	`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var accounts []*models.ChatGPTAccount
	for rows.Next() {
		account := &models.ChatGPTAccount{}
		if err := rows.Scan(
			&account.ID, &account.Name, &account.AuthToken, &account.TeamAccountID,
			&account.SeatsInUse, &account.SeatsEntitled, &account.ActiveStart, &account.ActiveUntil,
			&account.IsActive, &account.ConsecutiveFailures, &account.LastCheck,
			&account.LastUsed, &account.CreatedAt, &account.UpdatedAt,
		); err != nil {
			return nil, err
		}
		accounts = append(accounts, account)
	}
	return accounts, nil
}

// UpdateLastUsed 更新账号最后使用时间和席位
func (r *ChatGPTAccountRepository) UpdateLastUsed(id int) error {
	_, err := r.db.Exec(`
		UPDATE chatgpt_accounts SET last_used = $1, seats_in_use = seats_in_use + 1, updated_at = $1 
		WHERE id = $2
	`, time.Now(), id)
	return err
}

// FindAllPaginated 分页获取账号
func (r *ChatGPTAccountRepository) FindAllPaginated(page, pageSize int) ([]*models.ChatGPTAccount, error) {
	offset := (page - 1) * pageSize
	rows, err := r.db.Query(`
		SELECT id, name, auth_token, team_account_id, seats_in_use, seats_entitled,
			active_start, active_until, is_active, consecutive_failures,
			last_check, last_used, created_at, updated_at
		FROM chatgpt_accounts ORDER BY created_at DESC
		LIMIT $1 OFFSET $2
	`, pageSize, offset)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var accounts []*models.ChatGPTAccount
	for rows.Next() {
		account := &models.ChatGPTAccount{}
		if err := rows.Scan(
			&account.ID, &account.Name, &account.AuthToken, &account.TeamAccountID,
			&account.SeatsInUse, &account.SeatsEntitled, &account.ActiveStart, &account.ActiveUntil,
			&account.IsActive, &account.ConsecutiveFailures, &account.LastCheck,
			&account.LastUsed, &account.CreatedAt, &account.UpdatedAt,
		); err != nil {
			return nil, err
		}
		accounts = append(accounts, account)
	}
	return accounts, nil
}

// Count 获取账号总数
func (r *ChatGPTAccountRepository) Count() (int, error) {
	var count int
	err := r.db.QueryRow(`SELECT COUNT(*) FROM chatgpt_accounts`).Scan(&count)
	return count, err
}