package db

import (
	"database/sql"
	"errors"
	"fmt"
)

// ManifestRow represents a manifest as stored in the database.
type ManifestRow struct {
	ID           int64
	RepositoryID int64
	Digest       string
	MediaType    string
	Content      []byte
	Size         int64
}

// GetRepositoryByName returns the repository ID for the given name.
func (d *DB) GetRepositoryByName(name string) (int64, error) {
	var id int64
	err := d.QueryRow(`SELECT id FROM repositories WHERE name = ?`, name).Scan(&id)
	if err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			return 0, ErrRepoNotFound
		}
		return 0, fmt.Errorf("db: get repository by name: %w", err)
	}
	return id, nil
}

// GetManifestByTag returns the manifest associated with the given tag in a repository.
func (d *DB) GetManifestByTag(repoID int64, tag string) (*ManifestRow, error) {
	var m ManifestRow
	err := d.QueryRow(
		`SELECT m.id, m.repository_id, m.digest, m.media_type, m.content, m.size
		 FROM manifests m
		 JOIN tags t ON t.manifest_id = m.id
		 WHERE t.repository_id = ? AND t.name = ?`,
		repoID, tag,
	).Scan(&m.ID, &m.RepositoryID, &m.Digest, &m.MediaType, &m.Content, &m.Size)
	if err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			return nil, ErrManifestNotFound
		}
		return nil, fmt.Errorf("db: get manifest by tag: %w", err)
	}
	return &m, nil
}

// GetManifestByDigest returns the manifest with the given digest in a repository.
func (d *DB) GetManifestByDigest(repoID int64, digest string) (*ManifestRow, error) {
	var m ManifestRow
	err := d.QueryRow(
		`SELECT id, repository_id, digest, media_type, content, size
		 FROM manifests
		 WHERE repository_id = ? AND digest = ?`,
		repoID, digest,
	).Scan(&m.ID, &m.RepositoryID, &m.Digest, &m.MediaType, &m.Content, &m.Size)
	if err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			return nil, ErrManifestNotFound
		}
		return nil, fmt.Errorf("db: get manifest by digest: %w", err)
	}
	return &m, nil
}

// BlobExistsInRepo checks whether a blob with the given digest exists and is
// referenced by at least one manifest in the given repository.
func (d *DB) BlobExistsInRepo(repoID int64, digest string) (bool, error) {
	var count int
	err := d.QueryRow(
		`SELECT COUNT(*) FROM blobs b
		 JOIN manifest_blobs mb ON mb.blob_id = b.id
		 JOIN manifests m ON m.id = mb.manifest_id
		 WHERE m.repository_id = ? AND b.digest = ?`,
		repoID, digest,
	).Scan(&count)
	if err != nil {
		return false, fmt.Errorf("db: blob exists in repo: %w", err)
	}
	return count > 0, nil
}

// ListTags returns tag names for a repository, ordered alphabetically.
// Pagination is cursor-based: after is the last tag name from the previous page,
// limit is the maximum number of tags to return.
func (d *DB) ListTags(repoID int64, after string, limit int) ([]string, error) {
	var query string
	var args []any

	if after != "" {
		query = `SELECT name FROM tags WHERE repository_id = ? AND name > ? ORDER BY name ASC LIMIT ?`
		args = []any{repoID, after, limit}
	} else {
		query = `SELECT name FROM tags WHERE repository_id = ? ORDER BY name ASC LIMIT ?`
		args = []any{repoID, limit}
	}

	rows, err := d.Query(query, args...)
	if err != nil {
		return nil, fmt.Errorf("db: list tags: %w", err)
	}
	defer func() { _ = rows.Close() }()

	var tags []string
	for rows.Next() {
		var name string
		if err := rows.Scan(&name); err != nil {
			return nil, fmt.Errorf("db: scan tag: %w", err)
		}
		tags = append(tags, name)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("db: iterate tags: %w", err)
	}
	return tags, nil
}

// ListRepositoryNames returns repository names ordered alphabetically.
// Pagination is cursor-based: after is the last repo name from the previous page,
// limit is the maximum number of names to return.
func (d *DB) ListRepositoryNames(after string, limit int) ([]string, error) {
	var query string
	var args []any

	if after != "" {
		query = `SELECT name FROM repositories WHERE name > ? ORDER BY name ASC LIMIT ?`
		args = []any{after, limit}
	} else {
		query = `SELECT name FROM repositories ORDER BY name ASC LIMIT ?`
		args = []any{limit}
	}

	rows, err := d.Query(query, args...)
	if err != nil {
		return nil, fmt.Errorf("db: list repository names: %w", err)
	}
	defer func() { _ = rows.Close() }()

	var names []string
	for rows.Next() {
		var name string
		if err := rows.Scan(&name); err != nil {
			return nil, fmt.Errorf("db: scan repository name: %w", err)
		}
		names = append(names, name)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("db: iterate repository names: %w", err)
	}
	return names, nil
}