package db

import (
	"database/sql"
	"time"
)

// GetRepositoryAnnotations retrieves all annotations for a repository
func GetRepositoryAnnotations(db *sql.DB, did, repository string) (map[string]string, error) {
	rows, err := db.Query(`
		SELECT key, value
		FROM repository_annotations
		WHERE did = ? AND repository = ?
	`, did, repository)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	annotations := make(map[string]string)
	for rows.Next() {
		var key, value string
		if err := rows.Scan(&key, &value); err != nil {
			return nil, err
		}
		annotations[key] = value
	}

	return annotations, rows.Err()
}

// UpsertRepositoryAnnotations replaces all annotations for a repository
// Only called when manifest has at least one non-empty annotation
func UpsertRepositoryAnnotations(db *sql.DB, did, repository string, annotations map[string]string) error {
	tx, err := db.Begin()
	if err != nil {
		return err
	}
	defer tx.Rollback()

	// Delete existing annotations
	_, err = tx.Exec(`
		DELETE FROM repository_annotations
		WHERE did = ? AND repository = ?
	`, did, repository)
	if err != nil {
		return err
	}

	// Insert new annotations
	stmt, err := tx.Prepare(`
		INSERT INTO repository_annotations (did, repository, key, value, updated_at)
		VALUES (?, ?, ?, ?, ?)
	`)
	if err != nil {
		return err
	}
	defer stmt.Close()

	now := time.Now()
	for key, value := range annotations {
		_, err = stmt.Exec(did, repository, key, value, now)
		if err != nil {
			return err
		}
	}

	return tx.Commit()
}

// DeleteRepositoryAnnotations removes all annotations for a repository
func DeleteRepositoryAnnotations(db *sql.DB, did, repository string) error {
	_, err := db.Exec(`
		DELETE FROM repository_annotations
		WHERE did = ? AND repository = ?
	`, did, repository)
	return err
}