package db

import (
	"context"
	"database/sql"
	"errors"
	"fmt"
	"time"

	"github.com/lib/pq"

	"sourcecraft.dev/bigbes/sr-ht-dolt/core"
)

// repoSelect is the common projection used by every repository read. It joins
// "user" to resolve the owner's username (core.Repo.OwnerName). description is
// nullable in the schema, so it is coalesced to the empty string.
const repoSelect = `
SELECT r.id, r.name, COALESCE(r.description, ''), r.owner_id,
       COALESCE(u.username, ''), r.path, r.visibility
FROM repository r
JOIN "user" u ON u.id = r.owner_id`

func scanRepo(sc rowScanner) (*core.Repo, error) {
	var (
		r          core.Repo
		visibility string
	)
	if err := sc.Scan(&r.ID, &r.Name, &r.Description, &r.OwnerID,
		&r.OwnerName, &r.Path, &visibility); err != nil {
		return nil, err
	}
	r.Visibility = core.Visibility(visibility)
	return &r, nil
}

// CreateRepo inserts a new repository row. It is normally run on a transaction
// (Store.WithTx) so the caller can create the on-disk NBS store in the same
// unit of work and roll both back on failure. r.Name, r.OwnerID, r.Path and
// r.Visibility must be set; ID, created and updated are assigned here and the
// populated repo is returned (with OwnerName carried through from the input,
// which the caller already knows).
//
// Both unique violations that a same-name re-create can raise are mapped to
// ErrNameTaken: uq_repo_owner_id_name and repository_path_key. The path index
// is redundant with (owner_id, name) — Path is always derived from the pair by
// RepoDiskPath — but it is declared inline on the column, so its index has the
// lower OID and Postgres reports *it* first. Matching only the named constraint
// therefore never fired in practice and every duplicate surfaced as a raw
// 23505: a 500 on /internal/repos for each push of an existing companion, a 500
// instead of 409 on the web create form, and a failed adopt on a lost
// remotesapi auto-create race. Any other unique violation is returned unwrapped
// so the caller still sees the true cause.
func (s *Store) CreateRepo(ctx context.Context, r *core.Repo) (*core.Repo, error) {
	now := time.Now().UTC()
	const q = `
INSERT INTO repository (created, updated, name, description, owner_id, path, visibility)
VALUES ($1, $1, $2, $3, $4, $5, $6)
RETURNING id`
	var desc any
	if r.Description != "" {
		desc = r.Description
	}
	var id int
	err := s.q.QueryRowContext(ctx, q,
		now, r.Name, desc, r.OwnerID, r.Path, string(r.Visibility)).Scan(&id)
	if err != nil {
		var pqErr *pq.Error
		if errors.As(err, &pqErr) && pqErr.Code == "23505" &&
			(pqErr.Constraint == "uq_repo_owner_id_name" ||
				pqErr.Constraint == "repository_path_key") {
			return nil, ErrNameTaken
		}
		return nil, fmt.Errorf("insert repository: %w", err)
	}
	out := *r
	out.ID = id
	return &out, nil
}

// GetRepoByOwnerAndName resolves a repository by its owner's username and name.
// Returns ErrNotFound if no such repository exists.
func (s *Store) GetRepoByOwnerAndName(ctx context.Context, ownerUsername, name string) (*core.Repo, error) {
	q := repoSelect + `
WHERE u.username = $1 AND r.name = $2`
	repo, err := scanRepo(s.q.QueryRowContext(ctx, q, ownerUsername, name))
	if errors.Is(err, sql.ErrNoRows) {
		return nil, ErrNotFound
	}
	if err != nil {
		return nil, fmt.Errorf("get repo %s/%s: %w", ownerUsername, name, err)
	}
	return repo, nil
}

// GetRepoByID resolves a repository by its primary key. Returns ErrNotFound if
// no such repository exists.
func (s *Store) GetRepoByID(ctx context.Context, id int) (*core.Repo, error) {
	q := repoSelect + `
WHERE r.id = $1`
	repo, err := scanRepo(s.q.QueryRowContext(ctx, q, id))
	if errors.Is(err, sql.ErrNoRows) {
		return nil, ErrNotFound
	}
	if err != nil {
		return nil, fmt.Errorf("get repo %d: %w", id, err)
	}
	return repo, nil
}

// ListReposByOwner lists the repositories owned by ownerUsername that viewer is
// allowed to see, newest first. The listing rule (distinct from clone/browse
// authorization) is:
//
//   - The owner, and any user holding an ACL entry on a repo, always see it
//     regardless of visibility (including PRIVATE and UNLISTED).
//   - Everyone else — including anonymous viewers (viewer == nil) — sees only
//     PUBLIC repositories. UNLISTED repositories are never listed to non-owners
//     without an ACL, and PRIVATE ones are never listed either.
//
// viewer is the browsing principal; pass nil for an anonymous request.
func (s *Store) ListReposByOwner(ctx context.Context, ownerUsername string, viewer *core.Caller) ([]*core.Repo, error) {
	viewerID := 0
	if viewer != nil {
		viewerID = viewer.UserID
	}
	q := repoSelect + `
WHERE u.username = $1 AND (
    r.visibility = 'PUBLIC'
    OR r.owner_id = $2
    OR EXISTS (SELECT 1 FROM access a WHERE a.repo_id = r.id AND a.user_id = $2)
)
ORDER BY r.created DESC, r.id DESC`
	return s.queryRepos(ctx, q, ownerUsername, viewerID)
}

// ListReposForDashboard lists every repository the given user owns or holds an
// ACL entry on, newest first. Used for the signed-in user's dashboard.
func (s *Store) ListReposForDashboard(ctx context.Context, userID int) ([]*core.Repo, error) {
	q := repoSelect + `
WHERE r.owner_id = $1
   OR EXISTS (SELECT 1 FROM access a WHERE a.repo_id = r.id AND a.user_id = $1)
ORDER BY r.created DESC, r.id DESC`
	return s.queryRepos(ctx, q, userID)
}

func (s *Store) queryRepos(ctx context.Context, q string, args ...any) ([]*core.Repo, error) {
	rows, err := s.q.QueryContext(ctx, q, args...)
	if err != nil {
		return nil, fmt.Errorf("list repos: %w", err)
	}
	defer rows.Close()
	var repos []*core.Repo
	for rows.Next() {
		repo, err := scanRepo(rows)
		if err != nil {
			return nil, fmt.Errorf("scan repo: %w", err)
		}
		repos = append(repos, repo)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate repos: %w", err)
	}
	return repos, nil
}

// UpdateRepo updates the mutable repository fields (description and visibility)
// and bumps updated. Returns ErrNotFound if id does not exist.
func (s *Store) UpdateRepo(ctx context.Context, id int, description string, visibility core.Visibility) error {
	const q = `
UPDATE repository
SET description = $2, visibility = $3, updated = $4
WHERE id = $1`
	var desc any
	if description != "" {
		desc = description
	}
	res, err := s.q.ExecContext(ctx, q, id, desc, string(visibility), time.Now().UTC())
	if err != nil {
		return fmt.Errorf("update repo %d: %w", id, err)
	}
	return requireOne(res, "update repo")
}

// DeleteRepo removes a repository row (cascading to its access entries). The
// on-disk store removal is the caller's responsibility. Returns ErrNotFound if
// id does not exist.
func (s *Store) DeleteRepo(ctx context.Context, id int) error {
	res, err := s.q.ExecContext(ctx, `DELETE FROM repository WHERE id = $1`, id)
	if err != nil {
		return fmt.Errorf("delete repo %d: %w", id, err)
	}
	return requireOne(res, "delete repo")
}

// requireOne turns a zero-rows-affected result into ErrNotFound so that missing
// targets surface loudly instead of passing silently.
func requireOne(res sql.Result, what string) error {
	n, err := res.RowsAffected()
	if err != nil {
		return fmt.Errorf("%s: rows affected: %w", what, err)
	}
	if n == 0 {
		return ErrNotFound
	}
	return nil
}