visionA/visionA-backend/internal/model/postgres_sharing.go

// postgres_sharing.go — PostgresRepository 的模型共享方法（Library 查詢 + model_shares CRUD）。
//
// 對齊：
//   - feature-model-sharing-tdd.md §4（可見性 predicate + query 形狀 + 效能考量）
//   - api/api-model-sharing.md §1（library：cursor 分頁 / sort / filter / q）
//   - adr-017-model-library-access.md 決策 3（model_shares schema）
//   - migrations/0006_model_sharing.up.sql（visibility 欄 + model_shares 表 + index）
//
// 可見性 predicate（single source of truth 的 SQL 展開，對齊 TDD §4.1）：
//
//	可見(model, user) =
//	     owner_user_id = :userID                                   -- 我的
//	  OR visibility = 'public'                                     -- 全平台
//	  OR (visibility = 'tenant' AND owner.org_id = :orgID          -- 同租戶
//	        AND :orgID <> '' AND owner.org_id IS NOT NULL)
//	  OR EXISTS (model_shares 命中 grantee=:userID)                -- 分享給我
//
// tenant 邊界（SEC-4）：org_id 兩者皆非空才可能命中，空 org 一律不落 tenant 可見。
// OIDC 現況不帶 org claim → :orgID 恆空 → tenant 集合恆空（安全預設），schema 就緒待 OIDC 補齊。
package model

import (
	"context"
	"errors"
	"fmt"
	"strings"

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

// libraryColumns 是 Library 查詢的 SELECT 欄位（m.* + owner join + share 資訊）。
// 順序必須與 scanLibraryItem 對齊。內部 key（storage_key / faa_object_key）雖 SELECT
// 出來供 domain Model 完整（download 端點需 FAAObjectKey），但 DTO 序列化層（api）不揭露。
const libraryColumns = `m.id, m.owner_user_id, m.name, m.description, m.storage_key, m.file_size,
	m.file_checksum, m.faa_object_key, m.target_chip, m.input_shape, m.classes, m.framework,
	m.source, m.source_job_id, m.visibility, m.created_at, m.updated_at, m.uploaded_at, m.deleted_at,
	COALESCE(u.name, '') AS owner_name, COALESCE(u.org_id::text, '') AS owner_org_id,
	(s.grantee_user_id IS NOT NULL) AS shared_with_me, COALESCE(s.role, '') AS share_role`

// Library 依查詢 user 身份列出可見 model（cursor 分頁）。見檔頭 predicate 說明。
//
// query 形狀（對齊 TDD §4.2）：單一 SELECT + JOIN users（取 owner.name / owner.org_id，
// 一次帶出避免 handler N+1）+ LEFT JOIN model_shares（取當前 user 的 share role / shared_with_me）。
// filter / 排序 / keyset cursor 皆參數化拼接（無字串拼接使用者輸入）。
func (r *PostgresRepository) Library(ctx context.Context, q LibraryQuery) ([]*LibraryItem, bool, error) {
	var args []any
	arg := func(v any) string { // 追加參數並回傳其 $N placeholder
		args = append(args, v)
		return fmt.Sprintf("$%d", len(args))
	}

	userIDP := arg(q.UserID)
	// orgID：空字串時仍傳入，SQL 內以 `<> ''` 判非空（tenant 邊界 SEC-4）。
	orgIDP := arg(q.UserOrgID)

	// 可見性 predicate（TDD §4.1）。model_shares 子查用 m.id 關聯（相關子查）。
	visPredicate := fmt.Sprintf(`(
		    m.owner_user_id = %[1]s
		 OR m.visibility = 'public'
		 OR (m.visibility = 'tenant' AND u.org_id IS NOT NULL AND u.org_id::text = %[2]s AND %[2]s <> '')
		 OR EXISTS (SELECT 1 FROM model_shares ms
		             WHERE ms.model_id = m.id AND ms.grantee_user_id = %[1]s)
		)`, userIDP, orgIDP)

	conds := []string{
		"m.deleted_at IS NULL",
		"m.uploaded_at IS NOT NULL", // 共享庫只列 ready
		visPredicate,
	}

	// filter：owned 維度。
	if q.Owned != nil {
		if *q.Owned {
			conds = append(conds, "m.owner_user_id = "+userIDP)
		} else {
			conds = append(conds, "m.owner_user_id <> "+userIDP)
		}
	}
	if q.TargetChip != "" {
		conds = append(conds, "m.target_chip = "+arg(q.TargetChip))
	}
	if q.Source != "" {
		conds = append(conds, "m.source = "+arg(q.Source))
	}
	// visibility filter：僅 public / tenant 有意義（private 不在共享庫語意內，忽略）。
	if q.Visibility == VisibilityPublic || q.Visibility == VisibilityTenant {
		conds = append(conds, "m.visibility = "+arg(q.Visibility))
	}
	if q.Q != "" {
		// ILIKE 包含式搜尋 name + description（TDD §5：第一階段 ILIKE，量大再上 FTS）。
		// 參數化 + 手動 escape LIKE 萬用字元，避免使用者輸入的 % / _ 改變語意。
		like := "%" + escapeLike(q.Q) + "%"
		p := arg(like)
		conds = append(conds, "(m.name ILIKE "+p+" ESCAPE '\\' OR COALESCE(m.description, '') ILIKE "+p+" ESCAPE '\\')")
	}

	// 排序欄位白名單（handler 已 validate，這裡再次以 switch 白名單防禦，杜絕 SQL 注入）。
	sortCol := "m.created_at"
	switch q.Sort {
	case "name":
		sortCol = "m.name"
	case "file_size":
		sortCol = "m.file_size"
	case "created_at", "":
		sortCol = "m.created_at"
	}
	dir := "DESC"
	cmpOp := "<"
	if q.Order == "asc" {
		dir = "ASC"
		cmpOp = ">"
	}

	// keyset cursor：WHERE (sortCol, id) </>  (cursorSortValue, cursorID)。
	// 用 row-value 比較保證與 ORDER BY (sortCol, id) 一致的穩定分頁。
	if q.Cursor != nil {
		sv := castCursorValue(q.Sort, q.Cursor.SortValue)
		svP := arg(sv.value)
		idP := arg(q.Cursor.ID)
		conds = append(conds, fmt.Sprintf("(%s, m.id) %s (%s%s, %s)", sortCol, cmpOp, svP, sv.cast, idP))
	}

	limit := q.Limit
	if limit <= 0 {
		limit = 20
	}
	// 多取一筆判 hasMore。
	limitP := arg(limit + 1)

	query := `SELECT ` + libraryColumns + `
		FROM models m
		JOIN users u ON u.id = m.owner_user_id
		LEFT JOIN model_shares s ON s.model_id = m.id AND s.grantee_user_id = ` + userIDP + `
		WHERE ` + joinAnd(conds) + `
		ORDER BY ` + sortCol + ` ` + dir + `, m.id ` + dir + `
		LIMIT ` + limitP

	rows, err := r.pool.Query(ctx, query, args...)
	if err != nil {
		return nil, false, fmt.Errorf("model: pg Library query: %w", err)
	}
	defer rows.Close()

	items := make([]*LibraryItem, 0, limit)
	for rows.Next() {
		it, scanErr := scanLibraryItem(rows)
		if scanErr != nil {
			return nil, false, fmt.Errorf("model: pg Library scan: %w", scanErr)
		}
		items = append(items, it)
	}
	if err := rows.Err(); err != nil {
		return nil, false, fmt.Errorf("model: pg Library rows: %w", err)
	}

	hasMore := false
	if len(items) > limit {
		hasMore = true
		items = items[:limit]
	}
	return items, hasMore, nil
}

// cursorCast 描述 cursor 排序值的 SQL 值 + 型別 cast（讓 row-value 比較型別對齊欄位）。
type cursorCast struct {
	value string
	cast  string // 附加在 placeholder 後的 ::type，如 "::bigint" / "::timestamptz"；name 為空
}

// castCursorValue 依 sort 欄位決定 cursor 值的型別 cast（避免 text 與欄位型別不符）。
func castCursorValue(sortField, raw string) cursorCast {
	switch sortField {
	case "file_size":
		return cursorCast{value: raw, cast: "::bigint"}
	case "name":
		return cursorCast{value: raw, cast: ""}
	default: // created_at
		return cursorCast{value: raw, cast: "::timestamptz"}
	}
}

// scanLibraryItem 掃出一列 LibraryItem。欄位順序須對齊 libraryColumns。
func scanLibraryItem(row rowScanner) (*LibraryItem, error) {
	var (
		m            Model
		description  *string
		fileChecksum *string
		faaObjectKey *string
		targetChip   *string
		inputShape   []int32
		framework    *string
		sourceJobID  *string
		ownerName    string
		ownerOrgID   string
		sharedWithMe bool
		shareRole    string
	)
	err := row.Scan(
		&m.ID, &m.OwnerUserID, &m.Name, &description, &m.StorageKey, &m.FileSize,
		&fileChecksum, &faaObjectKey, &targetChip, &inputShape, &m.Classes, &framework,
		&m.Source, &sourceJobID, &m.Visibility, &m.CreatedAt, &m.UpdatedAt, &m.UploadedAt, &m.DeletedAt,
		&ownerName, &ownerOrgID, &sharedWithMe, &shareRole,
	)
	if err != nil {
		return nil, err
	}
	m.Description = derefString(description)
	m.FileChecksum = derefString(fileChecksum)
	m.FAAObjectKey = derefString(faaObjectKey)
	m.TargetChip = derefString(targetChip)
	m.Framework = derefString(framework)
	m.SourceJobID = derefString(sourceJobID)
	m.InputShape = toIntSlice(inputShape)
	m.CreatedAt = m.CreatedAt.UTC()
	m.UpdatedAt = m.UpdatedAt.UTC()
	if m.UploadedAt != nil {
		u := m.UploadedAt.UTC()
		m.UploadedAt = &u
	}

	// my_access：owner > share.role > public/tenant(viewer)。owner 由呼叫端已知（owner_user_id=userID），
	// 但此處 Library 已用 predicate 過濾出可見列，故 access 一定 != none。
	// owner 的判斷在 handler（is_me），這裡計算「非 owner 情境」的 access；owner 情境 handler 覆寫為 owner。
	access := AccessViewer
	if sharedWithMe && shareRole == "editor" {
		access = AccessEditor
	}
	return &LibraryItem{
		Model:        &m,
		OwnerName:    ownerName,
		OwnerOrgID:   ownerOrgID,
		SharedWithMe: sharedWithMe,
		MyAccess:     access,
	}, nil
}

// GetWithOwner 取單一未刪除 Model + owner 顯示名稱（一次 JOIN users，供 profile 顯示 owner name）。
// 不存在或已軟刪回 ErrNotFound。
func (r *PostgresRepository) GetWithOwner(ctx context.Context, id string) (*Model, string, error) {
	q := `SELECT ` + prefixCols("m", modelColumns) + `, COALESCE(u.name, '') AS owner_name
		FROM models m
		JOIN users u ON u.id = m.owner_user_id
		WHERE m.id = $1 AND m.deleted_at IS NULL`

	var ownerName string
	row := r.pool.QueryRow(ctx, q, id)
	m, err := scanModelWithExtra(row, &ownerName)
	if errors.Is(err, pgx.ErrNoRows) {
		return nil, "", ErrNotFound
	}
	if err != nil {
		return nil, "", fmt.Errorf("model: pg GetWithOwner: %w", err)
	}
	return m, ownerName, nil
}

// prefixCols 把 modelColumns 的每個裸欄名加上 table alias 前綴（`id` → `m.id`）。
// modelColumns 是不含前綴的欄位清單；GetWithOwner 需 alias 以區分 join 的 users 欄。
func prefixCols(alias, cols string) string {
	parts := strings.Split(cols, ",")
	for i, p := range parts {
		parts[i] = alias + "." + strings.TrimSpace(p)
	}
	return strings.Join(parts, ", ")
}

// scanModelWithExtra 掃出 *Model 後，再把 owner_name 掃進 extra（附加在 modelColumns 之後）。
// 為此需重掃：pgx row 只能 Scan 一次，故這裡直接展開 model 欄位 + extra 一起 Scan。
func scanModelWithExtra(row pgx.Row, ownerName *string) (*Model, error) {
	var (
		m            Model
		description  *string
		fileChecksum *string
		faaObjectKey *string
		targetChip   *string
		inputShape   []int32
		framework    *string
		sourceJobID  *string
	)
	err := row.Scan(
		&m.ID, &m.OwnerUserID, &m.Name, &description, &m.StorageKey, &m.FileSize,
		&fileChecksum, &faaObjectKey, &targetChip, &inputShape, &m.Classes, &framework,
		&m.Source, &sourceJobID, &m.Visibility, &m.CreatedAt, &m.UpdatedAt, &m.UploadedAt, &m.DeletedAt,
		ownerName,
	)
	if err != nil {
		return nil, err
	}
	m.Description = derefString(description)
	m.FileChecksum = derefString(fileChecksum)
	m.FAAObjectKey = derefString(faaObjectKey)
	m.TargetChip = derefString(targetChip)
	m.Framework = derefString(framework)
	m.SourceJobID = derefString(sourceJobID)
	m.InputShape = toIntSlice(inputShape)
	m.CreatedAt = m.CreatedAt.UTC()
	m.UpdatedAt = m.UpdatedAt.UTC()
	if m.UploadedAt != nil {
		u := m.UploadedAt.UTC()
		m.UploadedAt = &u
	}
	if m.DeletedAt != nil {
		d := m.DeletedAt.UTC()
		m.DeletedAt = &d
	}
	return &m, nil
}

// ---------------------------------------------------------------------------
// model_shares CRUD
// ---------------------------------------------------------------------------

// GetShare 取得 (modelID, granteeUserID) 的分享；不存在回 ErrNotFound。
func (r *PostgresRepository) GetShare(ctx context.Context, modelID, granteeUserID string) (*ModelShare, error) {
	const q = `SELECT model_id, grantee_user_id, role, granted_by, created_at
		FROM model_shares WHERE model_id = $1 AND grantee_user_id = $2`
	var s ModelShare
	err := r.pool.QueryRow(ctx, q, modelID, granteeUserID).
		Scan(&s.ModelID, &s.GranteeUserID, &s.Role, &s.GrantedBy, &s.CreatedAt)
	if errors.Is(err, pgx.ErrNoRows) {
		return nil, ErrNotFound
	}
	if err != nil {
		return nil, fmt.Errorf("model: pg GetShare: %w", err)
	}
	s.CreatedAt = s.CreatedAt.UTC()
	return &s, nil
}

// ListShares 列出某 model 的所有分享（owner 檢視授權清單）。
func (r *PostgresRepository) ListShares(ctx context.Context, modelID string) ([]*ModelShare, error) {
	const q = `SELECT model_id, grantee_user_id, role, granted_by, created_at
		FROM model_shares WHERE model_id = $1 ORDER BY created_at ASC`
	rows, err := r.pool.Query(ctx, q, modelID)
	if err != nil {
		return nil, fmt.Errorf("model: pg ListShares: %w", err)
	}
	defer rows.Close()
	out := make([]*ModelShare, 0)
	for rows.Next() {
		var s ModelShare
		if err := rows.Scan(&s.ModelID, &s.GranteeUserID, &s.Role, &s.GrantedBy, &s.CreatedAt); err != nil {
			return nil, fmt.Errorf("model: pg ListShares scan: %w", err)
		}
		s.CreatedAt = s.CreatedAt.UTC()
		out = append(out, &s)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("model: pg ListShares rows: %w", err)
	}
	return out, nil
}

// UpsertShare 新增 / 更新一筆分享（by PK (model_id, grantee_user_id)）。重複 grantee → 更新 role。
func (r *PostgresRepository) UpsertShare(ctx context.Context, s *ModelShare) error {
	if s == nil || s.ModelID == "" || s.GranteeUserID == "" {
		return errors.New("model: UpsertShare requires modelID and granteeUserID")
	}
	role := s.Role
	if role == "" {
		role = "viewer"
	}
	const q = `INSERT INTO model_shares (model_id, grantee_user_id, role, granted_by)
		VALUES ($1, $2, $3, $4)
		ON CONFLICT (model_id, grantee_user_id) DO UPDATE SET
			role = EXCLUDED.role, granted_by = EXCLUDED.granted_by`
	if _, err := r.pool.Exec(ctx, q, s.ModelID, s.GranteeUserID, role, s.GrantedBy); err != nil {
		return fmt.Errorf("model: pg UpsertShare: %w", err)
	}
	return nil
}

// DeleteShare 移除 (modelID, granteeUserID) 的分享；不存在回 ErrNotFound。
func (r *PostgresRepository) DeleteShare(ctx context.Context, modelID, granteeUserID string) error {
	const q = `DELETE FROM model_shares WHERE model_id = $1 AND grantee_user_id = $2`
	tag, err := r.pool.Exec(ctx, q, modelID, granteeUserID)
	if err != nil {
		return fmt.Errorf("model: pg DeleteShare: %w", err)
	}
	if tag.RowsAffected() == 0 {
		return ErrNotFound
	}
	return nil
}

// ---------------------------------------------------------------------------
// helper
// ---------------------------------------------------------------------------

// joinAnd 以 " AND " 串接 WHERE 條件。
func joinAnd(conds []string) string {
	out := ""
	for i, c := range conds {
		if i > 0 {
			out += " AND "
		}
		out += c
	}
	return out
}

// escapeLike escape LIKE / ILIKE 的萬用字元（% _ \），避免使用者輸入改變 pattern 語意。
// 搭配查詢端的 `ESCAPE '\'`。
func escapeLike(s string) string {
	var b []byte
	for i := 0; i < len(s); i++ {
		c := s[i]
		if c == '%' || c == '_' || c == '\\' {
			b = append(b, '\\')
		}
		b = append(b, c)
	}
	return string(b)
}