package db

import (
	"database/sql"
	"fmt"
	"strings"
	"testing"
	"time"
)

// setupBatchTestDB spins up a fresh in-memory libsql database with the full
// schema applied, so every batch test can write realistic data without
// stubbing individual tables.
func setupBatchTestDB(t *testing.T) *sql.DB {
	t.Helper()
	safeName := strings.ReplaceAll(t.Name(), "/", "_")
	d, err := InitDB(fmt.Sprintf("file:%s?mode=memory&cache=shared", safeName), LibsqlConfig{})
	if err != nil {
		t.Fatalf("init db: %v", err)
	}
	// Single conn to avoid cross-test contention in the shared in-memory cache.
	d.SetMaxOpenConns(1)
	t.Cleanup(func() { d.Close() })
	return d
}

func createBatchTestUser(t *testing.T, d *sql.DB, did string) {
	t.Helper()
	_, err := d.Exec(`
		INSERT OR IGNORE INTO users (did, handle, pds_endpoint, last_seen)
		VALUES (?, ?, ?, datetime('now'))
	`, did, did+".bsky.social", "https://pds.example.com")
	if err != nil {
		t.Fatalf("seed user: %v", err)
	}
}

func countRows(t *testing.T, d *sql.DB, query string, args ...any) int {
	t.Helper()
	var n int
	if err := d.QueryRow(query, args...).Scan(&n); err != nil {
		t.Fatalf("count: %v", err)
	}
	return n
}

func TestBuildPlaceholders(t *testing.T) {
	cases := []struct {
		rows, cols int
		want       string
	}{
		{1, 1, "(?)"},
		{2, 1, "(?),(?)"},
		{1, 3, "(?,?,?)"},
		{3, 2, "(?,?),(?,?),(?,?)"},
		{0, 5, ""},
		{5, 0, ""},
	}
	for _, c := range cases {
		got := buildPlaceholders(c.rows, c.cols)
		if got != c.want {
			t.Errorf("buildPlaceholders(%d,%d) = %q, want %q", c.rows, c.cols, got, c.want)
		}
	}
}

func TestBatchInsertManifests_InsertsAndReturnsIDs(t *testing.T) {
	d := setupBatchTestDB(t)
	createBatchTestUser(t, d, "did:plc:alice")

	now := time.Now()
	manifests := []Manifest{
		{DID: "did:plc:alice", Repository: "app1", Digest: "sha256:aaa", HoldEndpoint: "did:web:hold", SchemaVersion: 2, MediaType: "application/vnd.oci.image.manifest.v1+json", ArtifactType: "container-image", CreatedAt: now},
		{DID: "did:plc:alice", Repository: "app2", Digest: "sha256:bbb", HoldEndpoint: "did:web:hold", SchemaVersion: 2, MediaType: "application/vnd.oci.image.manifest.v1+json", ArtifactType: "container-image", CreatedAt: now},
	}

	ids, err := BatchInsertManifests(d, manifests)
	if err != nil {
		t.Fatalf("batch insert: %v", err)
	}
	if len(ids) != 2 {
		t.Fatalf("expected 2 ids, got %d", len(ids))
	}
	if ids[ManifestKey("did:plc:alice", "app1", "sha256:aaa")] == 0 {
		t.Errorf("missing id for app1")
	}
	if ids[ManifestKey("did:plc:alice", "app2", "sha256:bbb")] == 0 {
		t.Errorf("missing id for app2")
	}
	if got := countRows(t, d, `SELECT COUNT(*) FROM manifests`); got != 2 {
		t.Errorf("row count = %d, want 2", got)
	}
}

func TestBatchInsertManifests_Idempotent(t *testing.T) {
	d := setupBatchTestDB(t)
	createBatchTestUser(t, d, "did:plc:alice")

	now := time.Now()
	m := []Manifest{{
		DID: "did:plc:alice", Repository: "app", Digest: "sha256:aaa",
		HoldEndpoint: "did:web:hold", SchemaVersion: 2,
		MediaType:    "application/vnd.oci.image.manifest.v1+json",
		ArtifactType: "container-image", CreatedAt: now,
	}}
	if _, err := BatchInsertManifests(d, m); err != nil {
		t.Fatalf("first insert: %v", err)
	}
	if _, err := BatchInsertManifests(d, m); err != nil {
		t.Fatalf("second insert: %v", err)
	}
	if got := countRows(t, d, `SELECT COUNT(*) FROM manifests`); got != 1 {
		t.Errorf("expected idempotent; row count = %d", got)
	}
}

func TestBatchInsertManifests_Chunking(t *testing.T) {
	// Exceed one sub-batch to exercise the chunk loop.
	d := setupBatchTestDB(t)
	createBatchTestUser(t, d, "did:plc:alice")

	const n = BatchSize + 17
	now := time.Now()
	manifests := make([]Manifest, n)
	for i := range n {
		manifests[i] = Manifest{
			DID: "did:plc:alice", Repository: "app", Digest: fmt.Sprintf("sha256:%04d", i),
			HoldEndpoint: "did:web:hold", SchemaVersion: 2,
			MediaType:    "application/vnd.oci.image.manifest.v1+json",
			ArtifactType: "container-image", CreatedAt: now,
		}
	}
	ids, err := BatchInsertManifests(d, manifests)
	if err != nil {
		t.Fatalf("batch insert: %v", err)
	}
	if len(ids) != n {
		t.Errorf("ids len = %d, want %d", len(ids), n)
	}
	if got := countRows(t, d, `SELECT COUNT(*) FROM manifests`); got != n {
		t.Errorf("row count = %d, want %d", got, n)
	}
}

func TestBatchInsertLayers_RespectsFK(t *testing.T) {
	d := setupBatchTestDB(t)
	createBatchTestUser(t, d, "did:plc:alice")

	now := time.Now()
	ids, err := BatchInsertManifests(d, []Manifest{{
		DID: "did:plc:alice", Repository: "app", Digest: "sha256:aaa",
		HoldEndpoint: "did:web:hold", SchemaVersion: 2,
		MediaType:    "application/vnd.oci.image.manifest.v1+json",
		ArtifactType: "container-image", CreatedAt: now,
	}})
	if err != nil {
		t.Fatalf("insert manifest: %v", err)
	}
	mid := ids[ManifestKey("did:plc:alice", "app", "sha256:aaa")]

	layers := []Layer{
		{ManifestID: mid, Digest: "sha256:L0", Size: 100, MediaType: "application/vnd.oci.image.layer.v1.tar+gzip", LayerIndex: 0},
		{ManifestID: mid, Digest: "sha256:L1", Size: 200, MediaType: "application/vnd.oci.image.layer.v1.tar+gzip", LayerIndex: 1},
	}
	if err := BatchInsertLayers(d, layers); err != nil {
		t.Fatalf("batch insert layers: %v", err)
	}
	if got := countRows(t, d, `SELECT COUNT(*) FROM layers`); got != 2 {
		t.Errorf("layers count = %d, want 2", got)
	}
	// Re-run to confirm ON CONFLICT DO NOTHING doesn't error.
	if err := BatchInsertLayers(d, layers); err != nil {
		t.Fatalf("idempotent layers: %v", err)
	}
	if got := countRows(t, d, `SELECT COUNT(*) FROM layers`); got != 2 {
		t.Errorf("layers after re-insert = %d, want 2", got)
	}
}

func TestBatchUpsertTags_Idempotent(t *testing.T) {
	d := setupBatchTestDB(t)
	createBatchTestUser(t, d, "did:plc:alice")

	now := time.Now()
	tags := []Tag{
		{DID: "did:plc:alice", Repository: "app", Tag: "v1", Digest: "sha256:aaa", CreatedAt: now},
		{DID: "did:plc:alice", Repository: "app", Tag: "v2", Digest: "sha256:bbb", CreatedAt: now},
	}
	if err := BatchUpsertTags(d, tags); err != nil {
		t.Fatalf("batch upsert: %v", err)
	}
	if err := BatchUpsertTags(d, tags); err != nil {
		t.Fatalf("rerun: %v", err)
	}
	if got := countRows(t, d, `SELECT COUNT(*) FROM tags`); got != 2 {
		t.Errorf("tags count = %d, want 2", got)
	}
}

func TestBatchUpsertStars(t *testing.T) {
	d := setupBatchTestDB(t)
	createBatchTestUser(t, d, "did:plc:alice")
	createBatchTestUser(t, d, "did:plc:bob")

	now := time.Now()
	stars := []StarInput{
		{StarrerDID: "did:plc:bob", OwnerDID: "did:plc:alice", Repository: "app", CreatedAt: now},
	}
	if err := BatchUpsertStars(d, stars); err != nil {
		t.Fatalf("batch upsert stars: %v", err)
	}
	// Re-insert to confirm ON CONFLICT DO NOTHING.
	if err := BatchUpsertStars(d, stars); err != nil {
		t.Fatalf("rerun: %v", err)
	}
	if got := countRows(t, d, `SELECT COUNT(*) FROM stars`); got != 1 {
		t.Errorf("stars count = %d, want 1", got)
	}
}

func TestBatchUpsertRepoPages(t *testing.T) {
	d := setupBatchTestDB(t)
	createBatchTestUser(t, d, "did:plc:alice")

	now := time.Now()
	pages := []RepoPage{
		{DID: "did:plc:alice", Repository: "app", Description: "desc", CreatedAt: now, UpdatedAt: now},
	}
	if err := BatchUpsertRepoPages(d, pages); err != nil {
		t.Fatalf("batch upsert: %v", err)
	}
	// Update with new description.
	pages[0].Description = "new desc"
	if err := BatchUpsertRepoPages(d, pages); err != nil {
		t.Fatalf("update: %v", err)
	}
	var desc string
	if err := d.QueryRow(`SELECT description FROM repo_pages WHERE did=? AND repository=?`,
		"did:plc:alice", "app").Scan(&desc); err != nil {
		t.Fatalf("select: %v", err)
	}
	if desc != "new desc" {
		t.Errorf("description = %q, want %q", desc, "new desc")
	}
}

func TestBatchUpsertDailyStats(t *testing.T) {
	d := setupBatchTestDB(t)
	createBatchTestUser(t, d, "did:plc:alice")

	stats := []DailyStats{
		{DID: "did:plc:alice", Repository: "app", Date: "2026-04-19", PullCount: 5, PushCount: 2},
	}
	if err := BatchUpsertDailyStats(d, stats); err != nil {
		t.Fatalf("upsert: %v", err)
	}
	stats[0].PullCount = 10
	if err := BatchUpsertDailyStats(d, stats); err != nil {
		t.Fatalf("update: %v", err)
	}
	var pull int
	if err := d.QueryRow(`SELECT pull_count FROM repository_stats_daily WHERE did=? AND repository=? AND date=?`,
		"did:plc:alice", "app", "2026-04-19").Scan(&pull); err != nil {
		t.Fatalf("select: %v", err)
	}
	if pull != 10 {
		t.Errorf("pull = %d, want 10", pull)
	}
}

func TestBatchUpsertRepositoryAnnotations_DropsStaleKeys(t *testing.T) {
	d := setupBatchTestDB(t)
	createBatchTestUser(t, d, "did:plc:alice")

	rows := []AnnotationRow{
		{DID: "did:plc:alice", Repository: "app", Key: "a", Value: "1"},
		{DID: "did:plc:alice", Repository: "app", Key: "b", Value: "2"},
	}
	if err := BatchUpsertRepositoryAnnotations(d, rows); err != nil {
		t.Fatalf("initial: %v", err)
	}
	if got := countRows(t, d, `SELECT COUNT(*) FROM repository_annotations WHERE did=? AND repository=?`,
		"did:plc:alice", "app"); got != 2 {
		t.Errorf("initial count = %d, want 2", got)
	}

	// Second call drops stale key "b".
	rows = []AnnotationRow{
		{DID: "did:plc:alice", Repository: "app", Key: "a", Value: "1-updated"},
	}
	if err := BatchUpsertRepositoryAnnotations(d, rows); err != nil {
		t.Fatalf("update: %v", err)
	}
	if got := countRows(t, d, `SELECT COUNT(*) FROM repository_annotations WHERE did=? AND repository=?`,
		"did:plc:alice", "app"); got != 1 {
		t.Errorf("after update = %d, want 1", got)
	}
	var val string
	if err := d.QueryRow(`SELECT value FROM repository_annotations WHERE key=? AND did=? AND repository=?`,
		"a", "did:plc:alice", "app").Scan(&val); err != nil {
		t.Fatalf("select: %v", err)
	}
	if val != "1-updated" {
		t.Errorf("value = %q, want 1-updated", val)
	}
}

func TestBatchUpsertCaptainRecords(t *testing.T) {
	d := setupBatchTestDB(t)

	now := time.Now()
	records := []HoldCaptainRecord{
		{HoldDID: "did:web:hold1", OwnerDID: "did:plc:alice", Public: true, AllowAllCrew: false, UpdatedAt: now},
	}
	if err := BatchUpsertCaptainRecords(d, records); err != nil {
		t.Fatalf("upsert: %v", err)
	}
	if got := countRows(t, d, `SELECT COUNT(*) FROM hold_captain_records`); got != 1 {
		t.Errorf("count = %d, want 1", got)
	}
}

func TestBatchUpsertCrewMembers(t *testing.T) {
	d := setupBatchTestDB(t)

	members := []CrewMember{
		{HoldDID: "did:web:hold1", MemberDID: "did:plc:alice", Rkey: "rkey1", Role: "owner"},
	}
	if err := BatchUpsertCrewMembers(d, members); err != nil {
		t.Fatalf("upsert: %v", err)
	}
	// Update the rkey: triggers the ON CONFLICT path.
	members[0].Rkey = "rkey2"
	if err := BatchUpsertCrewMembers(d, members); err != nil {
		t.Fatalf("update: %v", err)
	}
	var rkey string
	if err := d.QueryRow(`SELECT rkey FROM hold_crew_members WHERE hold_did=? AND member_did=?`,
		"did:web:hold1", "did:plc:alice").Scan(&rkey); err != nil {
		t.Fatalf("select: %v", err)
	}
	if rkey != "rkey2" {
		t.Errorf("rkey = %q, want rkey2", rkey)
	}
}

func TestBatchEmptySlices(t *testing.T) {
	d := setupBatchTestDB(t)
	// Every batch function must tolerate an empty input slice without erroring.
	if _, err := BatchInsertManifests(d, nil); err != nil {
		t.Errorf("manifests: %v", err)
	}
	if err := BatchInsertLayers(d, nil); err != nil {
		t.Errorf("layers: %v", err)
	}
	if err := BatchInsertManifestReferences(d, nil); err != nil {
		t.Errorf("refs: %v", err)
	}
	if err := BatchUpsertTags(d, nil); err != nil {
		t.Errorf("tags: %v", err)
	}
	if err := BatchUpsertStars(d, nil); err != nil {
		t.Errorf("stars: %v", err)
	}
	if err := BatchUpsertRepoPages(d, nil); err != nil {
		t.Errorf("repo pages: %v", err)
	}
	if err := BatchUpsertDailyStats(d, nil); err != nil {
		t.Errorf("daily: %v", err)
	}
	if err := BatchUpsertRepositoryStats(d, nil); err != nil {
		t.Errorf("repo stats: %v", err)
	}
	if err := BatchUpsertCaptainRecords(d, nil); err != nil {
		t.Errorf("captain: %v", err)
	}
	if err := BatchUpsertCrewMembers(d, nil); err != nil {
		t.Errorf("crew: %v", err)
	}
	if err := BatchUpsertRepositoryAnnotations(d, nil); err != nil {
		t.Errorf("annotations: %v", err)
	}
}