visionA/visionA-backend/internal/device/postgres_repository.go

// Package device 的 Postgres 持久層實作（DB 接入塊 2）。
//
// PostgresRepository 實作與 InMemoryRepository 完全相同的 Repository interface，
// 讓 main.go 在 dbPool != nil 時無痛切換、handler 與呼叫端一行都不需改。
//
// 對齊：
//   - database.md §2.2（Device 雙狀態欄位 + paired_at）、§4（devices 表 schema：
//     partial unique index uq_devices_owner_serial_active + owner/remote_status filter index）
//   - migrations/0002_create_devices.up.sql（devices 表含全部欄位）
//
// 語意對齊 in-memory（見 device.go）：
//   - Get / GetBySerial / List 略過 deleted_at IS NOT NULL 的紀錄。
//   - GetBySerial 以 (owner_user_id, serial_number) 查未刪除紀錄。
//   - Save 為 upsert by ID；existing 且未刪除時保留原 created_at（in-memory device.go ~line 187）。
//   - Delete 為軟刪除（寫 deleted_at = now()）；已刪除或不存在回 ErrNotFound。
//
// partial unique × soft-delete 語意（塊 2 子任務 2.3）：
//
//	devices 的 (owner_user_id, serial_number) 唯一性只對「未刪除」紀錄成立
//	（migration 0002 的 uq_devices_owner_serial_active WHERE deleted_at IS NULL）。
//	因此：
//	  - 同 owner 同 serial 同時存在「兩筆未刪除」→ INSERT 第二筆會撞 unique（23505）。
//	  - 但若先把第一筆 soft-delete（deleted_at IS NOT NULL），它就退出 partial index 的
//	    管轄範圍，同 (owner, serial) 可再 INSERT 一筆新的（新 id）而不違反 unique。
//	這正是「已刪除 serial 可重新註冊」的決策落地。
//	注意：Save 是 upsert by **id**，而非 by (owner, serial)。重註冊走「新 id」路徑、
//	不會 ON CONFLICT (id) 命中舊列；舊列保持 soft-deleted，新列為一筆全新紀錄。
package device

import (
	"context"
	"errors"
	"fmt"
	"time"

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

	"visiona-backend/internal/db"
)

// PostgresRepository 是 Device 的 PostgreSQL 持久層實作。
type PostgresRepository struct {
	pool *pgxpool.Pool
}

// NewPostgresRepository 建立一個以 pgxpool 為後端的 Repository。
//
// pool 由 internal/db 的 NewPool 建立並注入；本套件不持有建池 / 關閉責任。
func NewPostgresRepository(pool *pgxpool.Pool) *PostgresRepository {
	return &PostgresRepository{pool: pool}
}

// 編譯時檢查：確保 PostgresRepository 實作 Repository。
var _ Repository = (*PostgresRepository)(nil)

// deviceColumns 是 SELECT 共用的欄位清單（順序必須與 scanDevice 對齊）。
//
// A' 模型（migration 0005）新增 4 欄，接在原 13 欄之後（agent_id / agent_local_device_id /
// registered_at / is_representative）。順序改動時 scanDevice 的 Scan 目標順序須同步（WP-B B1）。
const deviceColumns = `id, owner_user_id, name, device_type, serial_number,
	remote_status, last_seen_at, last_connected_at, status,
	created_at, updated_at, paired_at, deleted_at,
	agent_id, agent_local_device_id, registered_at, is_representative`

// Get 取得單一 device；不存在或已軟刪除回 ErrNotFound。
func (r *PostgresRepository) Get(ctx context.Context, id string) (*Device, error) {
	const q = `SELECT ` + deviceColumns + `
		FROM devices
		WHERE id = $1 AND deleted_at IS NULL`

	row := r.pool.QueryRow(ctx, q, id)
	d, err := scanDevice(row)
	if errors.Is(err, pgx.ErrNoRows) {
		return nil, ErrNotFound
	}
	if err != nil {
		return nil, fmt.Errorf("device: pg Get: %w", err)
	}
	return d, nil
}

// GetBySerial 以 (ownerUserID, serialNumber) 查未刪除紀錄；查不到回 ErrNotFound。
//
// 對齊 in-memory：同一個 serial 在不同 owner 下不互相干擾（owner 過濾）。
//
// serial 空字串：Save 把空 serial 寫成 SQL NULL（見 SaveTx），故空 serial 查詢需用
// serial_number IS NULL 比對（等號比較永不命中 NULL）。非空 serial 走參數化 = $2。
// 此分支讓 PG 與 in-memory（d.SerialNumber == serial，空查空）語意一致。
func (r *PostgresRepository) GetBySerial(ctx context.Context, ownerUserID, serial string) (*Device, error) {
	return r.GetBySerialTx(ctx, r.pool, ownerUserID, serial)
}

// GetBySerialTx 與 GetBySerial 相同語意，但在傳入的 Querier（pool 或 tx）上執行。
//
// WP-B B3（Mi#2 lost-update 收斂）：exchange 復用真 USB device 時，把「查 serial → 復用/建」
// 都放進同一交易（先前 GetBySerial 走 pool、與 SaveTx 之間有 race window）。tx 內查詢讓
// 「查到既有 → 復用」與後續寫入在同一快照下序列化，撞 partial unique 的機率降到只剩跨 tx
// 的並發配對（實務上同一 agent 序列配對，可接受），且撞到時整筆 rollback 不產生重複。
//
// 空 serial 分支（WP-0 Mi#4 收斂）：以序號查詢對「空序號」無業務意義（呼叫端一律帶已正規化
// 的非空 serial）。空 serial 直接回 ErrNotFound——不再掃「serial IS NULL」的多筆 representative
// / 佔位 device（那些不是「以序號識別的實體 USB」，撈出來也無意義且無 ORDER BY 為非決定性）。
func (r *PostgresRepository) GetBySerialTx(ctx context.Context, q db.Querier, ownerUserID, serial string) (*Device, error) {
	if serial == "" {
		// 空 serial 無法以序號識別實體 USB（Mi#4）：直接回 ErrNotFound，避免非決定性多筆掃描。
		return nil, ErrNotFound
	}

	const sql = `SELECT ` + deviceColumns + `
		FROM devices
		WHERE owner_user_id = $1 AND serial_number = $2 AND deleted_at IS NULL`

	row := q.QueryRow(ctx, sql, ownerUserID, serial)
	d, err := scanDevice(row)
	if errors.Is(err, pgx.ErrNoRows) {
		return nil, ErrNotFound
	}
	if err != nil {
		return nil, fmt.Errorf("device: pg GetBySerial: %w", err)
	}
	return d, nil
}

// List 列出某 owner 的所有未刪除、**非 representative** 的 device（WP-B B4），以 created_at
// DESC 排序（最新在前）。
//
// is_representative=false filter（A' 模型，ADR-018 §2.5 / migration-0005-spec §4.3）：
//
//	representative device 是「agent 連線佔位」（綁 session_tokens），不是真實 USB，不該出現在
//	使用者的裝置清單。遷移後所有舊 device 都被標為 representative（0005 data migration），若不
//	filter，UI 會把這些佔位 device 當 USB 顯示。故 List 只回真 USB device（is_representative=false）。
//	精確查詢（Get / GetBySerialTx / GetRepresentativeByAgentTx）不受此 filter 影響——它們是
//	by-id / by-serial / by-agent 的通用查詢，用途包含查 representative 本身。
func (r *PostgresRepository) List(ctx context.Context, ownerUserID string) ([]*Device, error) {
	const q = `SELECT ` + deviceColumns + `
		FROM devices
		WHERE owner_user_id = $1 AND deleted_at IS NULL AND is_representative = false
		ORDER BY created_at DESC`

	rows, err := r.pool.Query(ctx, q, ownerUserID)
	if err != nil {
		return nil, fmt.Errorf("device: pg List query: %w", err)
	}
	defer rows.Close()

	out := make([]*Device, 0)
	for rows.Next() {
		d, scanErr := scanDevice(rows)
		if scanErr != nil {
			return nil, fmt.Errorf("device: pg List scan: %w", scanErr)
		}
		out = append(out, d)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("device: pg List rows: %w", err)
	}
	return out, nil
}

// Save 新增或更新 device（upsert by id）。
//
// 語意對齊 in-memory（device.go ~line 187）：
//   - 既有且未刪除（deleted_at IS NULL）→ 保留原 created_at；
//   - 不存在 / 已刪除（復活）→ 以傳入 created_at（zero 時用 now()）為準。
//
// updated_at 一律設為 now()。created_at 用 CASE：當 conflict 既有列未刪除時保留
// devices.created_at，否則用 EXCLUDED.created_at。
//
// 重註冊（已 soft-delete 的 serial）走「新 id」→ 不會命中 ON CONFLICT (id)，視為 INSERT；
// partial unique 因舊列已 deleted 不阻擋（見 package 註解）。
func (r *PostgresRepository) Save(ctx context.Context, d *Device) error {
	return r.SaveTx(ctx, r.pool, d)
}

// SaveTx 與 Save 相同的 upsert 語意，但在傳入的 Querier（pool 或 tx）上執行。
//
// 用於 pairing exchange 自建 device 時，與 session token 建立在同一交易內（pairing 收尾問題 #2）：
// 「建 device + 建 session token」整筆原子——任一步失敗整筆 rollback，避免建了 device 卻沒建成
// session token 的中間態。q 可為 *pgxpool.Pool（自動 commit）或 pgx.Tx（隨外層交易）。
func (r *PostgresRepository) SaveTx(ctx context.Context, q db.Querier, d *Device) error {
	if d == nil || d.ID == "" {
		return errors.New("device: Save requires non-nil device with ID")
	}

	// remote_status / status 補預設值（offline / unknown），避免空字串寫進「有預設」的
	// NOT NULL 欄位（devices.remote_status / status 為 NOT NULL DEFAULT）後語意混淆。
	// 此補預設行為與 InMemoryRepository.Save 一致（見 device.go），兩個實作對空狀態的
	// 處理刻意對齊，讓同一筆空狀態 device 不論走 PG 或 in-memory 都讀回 offline / unknown。
	remoteStatus := d.RemoteStatus
	if remoteStatus == "" {
		remoteStatus = RemoteStatusOffline
	}
	status := d.Status
	if status == "" {
		status = USBStatusUnknown
	}

	// created_at：zero 時交給 DB now()（用 NULL 觸發 COALESCE）。
	var createdAt any
	if !d.CreatedAt.IsZero() {
		createdAt = d.CreatedAt.UTC()
	} // else: 留 nil → COALESCE($n, now())

	// serial_number：空字串寫成 SQL NULL（而非 ''）。
	//
	// 為什麼：devices.serial_number 是 nullable TEXT，partial unique index
	// uq_devices_owner_serial_active (owner_user_id, serial_number) WHERE deleted_at IS NULL
	// 對「非 NULL」值才強制唯一。SQL 規範下每個 NULL 互不相等，故多筆「無序號」的
	// device（同 owner）不互撞 unique；但空字串 '' 是個確定值，同 owner 多筆 '' 會撞。
	//
	// 語意：實體裝置一定帶非空 serial（防重複註冊照舊生效）；雲端 pairing exchange
	// 自建的 device 沒有真實序號，正確表示是「無序號」(NULL) 而非 ''。如此同一 owner
	// 多次 exchange 各建一筆 serial=NULL 的 distinct device、不撞 unique。
	//
	// 未來 local-tool 上報真實 serial 時，直接把這個 NULL 更新成真值即可，無 reconciliation
	// 成本（若先前捏一個假 serial 佔位，反而要額外處理覆蓋）。
	var serialNumber any
	if d.SerialNumber != "" {
		serialNumber = d.SerialNumber
	} // else: 留 nil → 寫入 SQL NULL

	// A' 新欄（migration 0005，WP-B B2）：
	//   - agent_id：空字串寫 SQL NULL（agent_id 是 nullable UUID FK→agents；空字串無法 cast
	//     UUID 會炸，且「未掛 agent」的語意正是 NULL——遷移前舊資料 / 尚未歸入 agent 的 device）。
	//   - agent_local_device_id：空字串寫 NULL（nullable TEXT，對齊 serial_number 慣例）。
	//   - registered_at：*time.Time nil → SQL NULL（pgx 直接處理），非 nil 寫值（註冊軸）。
	//   - is_representative：NOT NULL BOOLEAN，bool 直接寫（zero value false = 真 USB / 未指定）。
	var agentID any
	if d.AgentID != "" {
		agentID = d.AgentID
	} // else: 留 nil → SQL NULL
	var agentLocalDeviceID any
	if d.AgentLocalDeviceID != "" {
		agentLocalDeviceID = d.AgentLocalDeviceID
	} // else: 留 nil → SQL NULL

	const sql = `
		INSERT INTO devices (
			id, owner_user_id, name, device_type, serial_number,
			remote_status, last_seen_at, last_connected_at, status,
			created_at, updated_at, paired_at, deleted_at,
			agent_id, agent_local_device_id, registered_at, is_representative
		) VALUES (
			$1, $2, $3, $4, $5,
			$6, $7, $8, $9,
			COALESCE($10, now()), now(), $11, $12,
			$13, $14, $15, $16
		)
		ON CONFLICT (id) DO UPDATE SET
			owner_user_id     = EXCLUDED.owner_user_id,
			name              = EXCLUDED.name,
			device_type       = EXCLUDED.device_type,
			serial_number     = EXCLUDED.serial_number,
			remote_status     = EXCLUDED.remote_status,
			last_seen_at      = EXCLUDED.last_seen_at,
			last_connected_at = EXCLUDED.last_connected_at,
			status            = EXCLUDED.status,
			-- 保留原 created_at 僅當既有列未刪除；已刪除（復活）則用新值。
			created_at        = CASE
				WHEN devices.deleted_at IS NULL THEN devices.created_at
				ELSE EXCLUDED.created_at
			END,
			updated_at        = now(),
			paired_at         = EXCLUDED.paired_at,
			deleted_at        = EXCLUDED.deleted_at,
			agent_id              = EXCLUDED.agent_id,
			agent_local_device_id = EXCLUDED.agent_local_device_id,
			registered_at         = EXCLUDED.registered_at,
			is_representative      = EXCLUDED.is_representative`

	_, err := q.Exec(ctx, sql,
		d.ID,               // $1
		d.OwnerUserID,      // $2
		d.Name,             // $3
		d.DeviceType,       // $4
		serialNumber,       // $5
		remoteStatus,       // $6
		d.LastSeenAt,       // $7
		d.LastConnectedAt,  // $8
		status,             // $9
		createdAt,          // $10
		d.PairedAt,         // $11
		d.DeletedAt,        // $12
		agentID,            // $13
		agentLocalDeviceID, // $14
		d.RegisteredAt,     // $15
		d.IsRepresentative, // $16
	)
	if err != nil {
		return fmt.Errorf("device: pg Save upsert: %w", err)
	}
	return nil
}

// SetRegistered 設定 / 清除 registered_at（註冊軸單欄 UPDATE，feature-device-mgmt-tdd §3.3）。
//
// 精準單欄 UPDATE（不走 Save 的全欄 upsert），避免「Get→改欄→Save 回去」的讀寫競態面：
//
//	UPDATE devices SET registered_at = $2, updated_at = now()
//	WHERE id = $1 AND deleted_at IS NULL AND is_representative = false
//
// WHERE 的 deleted_at IS NULL + is_representative = false 是縱深第三層（配合 handler 的
// owner / representative / already-registered 檢查）：對不存在 / 已軟刪 / representative 的
// 列 RowsAffected()==0 → 回 ErrNotFound。
//
//   - register：at != nil（handler 已先擋 already-registered，這裡不重複判）。
//   - unregister：at == nil，清成 NULL；對已 NULL 的列 UPDATE 到相同值 RowsAffected 仍為 1
//     （WHERE 命中），語意上「取消一個未註冊的 = 已達成目標」（冪等，TDD §4.1）。
//
// 絕不軟刪、不呼叫 DeviceUnpairer、不碰 token（TDD §1.2 紅線）。
func (r *PostgresRepository) SetRegistered(ctx context.Context, id string, at *time.Time) error {
	const sql = `UPDATE devices
		SET registered_at = $2, updated_at = now()
		WHERE id = $1 AND deleted_at IS NULL AND is_representative = false`

	tag, err := r.pool.Exec(ctx, sql, id, at)
	if err != nil {
		return fmt.Errorf("device: pg SetRegistered: %w", err)
	}
	if tag.RowsAffected() == 0 {
		return ErrNotFound
	}
	return nil
}

// GetRepresentativeByAgentTx 取得某 agent 的 representative device（is_representative=true、
// 未刪除）；不存在回 ErrNotFound（在傳入 Querier / tx 上執行）。
//
// A' 語意（ADR-018 §2.4）：一 agent 一顆 representative device，綁 session_tokens。exchange
// 復用 agent 時用它找既有 representative 復用（避免每次配對長出一顆新 representative）。
// 一 agent 最多一顆 representative；仍加 ORDER BY created_at + LIMIT 1 保決定性。
func (r *PostgresRepository) GetRepresentativeByAgentTx(ctx context.Context, q db.Querier, agentID string) (*Device, error) {
	const sql = `SELECT ` + deviceColumns + `
		FROM devices
		WHERE agent_id = $1 AND is_representative = true AND deleted_at IS NULL
		ORDER BY created_at ASC
		LIMIT 1`

	row := q.QueryRow(ctx, sql, agentID)
	d, err := scanDevice(row)
	if errors.Is(err, pgx.ErrNoRows) {
		return nil, ErrNotFound
	}
	if err != nil {
		return nil, fmt.Errorf("device: pg GetRepresentativeByAgent: %w", err)
	}
	return d, nil
}

// Delete 軟刪除：寫 deleted_at = now()。已刪除或不存在回 ErrNotFound。
//
// 直接在 pool 上跑（自動 commit）。若需與 token cascade 撤銷在同一交易內，請改用 DeleteTx。
func (r *PostgresRepository) Delete(ctx context.Context, id string) error {
	return r.DeleteTx(ctx, r.pool, id)
}

// DeleteTx 與 Delete 相同的軟刪除語意，但在傳入的 Querier（pool 或 tx）上執行。
//
// 塊 5.2 cascade 撤銷：unpair 流程在 db.WithTx 內先呼叫本方法軟刪 device，再於同一 tx 對
// pairing_tokens / session_tokens 撤銷——任一步失敗整筆 rollback，device 不會「已刪但 token 沒撤」。
//
// q 可為 *pgxpool.Pool（自動 commit）或 pgx.Tx（隨外層交易）。語意與 Delete 一致：
// 已刪除或不存在回 ErrNotFound（呼叫端可 errors.Is 比對）。
func (r *PostgresRepository) DeleteTx(ctx context.Context, q db.Querier, id string) error {
	const sql = `UPDATE devices
		SET deleted_at = now(), updated_at = now()
		WHERE id = $1 AND deleted_at IS NULL`

	tag, err := q.Exec(ctx, sql, id)
	if err != nil {
		return fmt.Errorf("device: pg Delete: %w", err)
	}
	if tag.RowsAffected() == 0 {
		return ErrNotFound
	}
	return nil
}

// ==========================================================================
// scan helper
// ==========================================================================

// rowScanner 抽象 pgx.Row 與 pgx.Rows 的共同 Scan 介面，讓 scanDevice 同時服務 Get 與 List。
type rowScanner interface {
	Scan(dest ...any) error
}

// scanDevice 從一列掃出 *Device。欄位順序必須與 deviceColumns 對齊。
//
// nullable TEXT 欄位（device_type / serial_number / agent_id / agent_local_device_id）在 DB
// 為 NULL 時掃進空字串（對齊 in-memory zero value）；nullable TIMESTAMPTZ（last_seen_at /
// last_connected_at / paired_at / deleted_at / registered_at）以 *time.Time 接，NULL → nil。
// is_representative 為 NOT NULL BOOLEAN（DEFAULT false），直接掃進 bool。
//
// A' 新欄（migration 0005，WP-B B1）：agent_id / agent_local_device_id 以 nullable *string
// 接（遷移前舊資料 / 真 USB 未掛 agent 時為 NULL），registered_at 以 *time.Time 接（NULL=未註冊）。
func scanDevice(row rowScanner) (*Device, error) {
	var (
		d                  Device
		deviceType         *string
		serialNumber       *string
		agentID            *string
		agentLocalDeviceID *string
	)

	err := row.Scan(
		&d.ID,
		&d.OwnerUserID,
		&d.Name,
		&deviceType,
		&serialNumber,
		&d.RemoteStatus,
		&d.LastSeenAt,
		&d.LastConnectedAt,
		&d.Status,
		&d.CreatedAt,
		&d.UpdatedAt,
		&d.PairedAt,
		&d.DeletedAt,
		&agentID,
		&agentLocalDeviceID,
		&d.RegisteredAt,
		&d.IsRepresentative,
	)
	if err != nil {
		return nil, err
	}

	d.DeviceType = derefString(deviceType)
	d.SerialNumber = derefString(serialNumber)
	d.AgentID = derefString(agentID)
	d.AgentLocalDeviceID = derefString(agentLocalDeviceID)

	// 正規化時間為 UTC，對齊 in-memory（time.Now().UTC()）。
	d.CreatedAt = d.CreatedAt.UTC()
	d.UpdatedAt = d.UpdatedAt.UTC()
	if d.LastSeenAt != nil {
		t := d.LastSeenAt.UTC()
		d.LastSeenAt = &t
	}
	if d.LastConnectedAt != nil {
		t := d.LastConnectedAt.UTC()
		d.LastConnectedAt = &t
	}
	if d.PairedAt != nil {
		t := d.PairedAt.UTC()
		d.PairedAt = &t
	}
	if d.DeletedAt != nil {
		t := d.DeletedAt.UTC()
		d.DeletedAt = &t
	}
	if d.RegisteredAt != nil {
		t := d.RegisteredAt.UTC()
		d.RegisteredAt = &t
	}

	return &d, nil
}

// derefString 解指標字串，nil 視為空字串（對齊 in-memory zero value）。
func derefString(s *string) string {
	if s == nil {
		return ""
	}
	return *s
}