package db

import (
	"context"
	"crypto/rand"
	"database/sql"
	"encoding/hex"
	"net/url"
	"os"
	"strings"
	"testing"

	_ "github.com/lib/pq"

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

// testEnv names the DSN env var that gates the Postgres-backed tests. When
// unset, every integration test in this package skips with a clear message; the
// pure-logic tests (transition guards, token hashing, batch duplicate
// detection, schema/migration agreement) run regardless, so `go test ./db/...`
// is meaningful on a machine with no database at all.
const testEnv = "SPECSRHT_TEST_PG"

// newTestStore connects to the Postgres pointed at by SPECSRHT_TEST_PG, creates
// an isolated scratch schema (specsrht_test_<random>), applies schema.sql into
// it, and returns a Store bound to a pool whose search_path is that schema. The
// returned cleanup drops the schema and closes the pools. If the env var is
// unset the test is skipped.
//
// Isolation is achieved with a per-test schema rather than a whole database so
// no admin/CREATE DATABASE privilege is required and cleanup is a single
// DROP SCHEMA ... CASCADE. The scratch pool routes every connection to that
// schema via lib/pq's `options=-c search_path=...` startup parameter.
func newTestStore(t *testing.T) (*Store, *sql.DB, func()) {
	t.Helper()
	base := os.Getenv(testEnv)
	if base == "" {
		t.Skipf("%s not set; skipping Postgres-backed test (set it to a DSN to run)", testEnv)
	}

	admin, err := sql.Open("postgres", base)
	if err != nil {
		t.Fatalf("open admin pool: %v", err)
	}
	if err := admin.Ping(); err != nil {
		admin.Close()
		t.Fatalf("ping %s: %v", testEnv, err)
	}

	schema := "specsrht_test_" + randToken()
	if _, err := admin.Exec(`CREATE SCHEMA "` + schema + `"`); err != nil {
		admin.Close()
		t.Fatalf("create schema %s: %v", schema, err)
	}

	scopedDSN, err := withSearchPath(base, schema)
	if err != nil {
		admin.Exec(`DROP SCHEMA "` + schema + `" CASCADE`)
		admin.Close()
		t.Fatalf("build scoped dsn: %v", err)
	}
	pool, err := sql.Open("postgres", scopedDSN)
	if err != nil {
		admin.Exec(`DROP SCHEMA "` + schema + `" CASCADE`)
		admin.Close()
		t.Fatalf("open scoped pool: %v", err)
	}

	ddl, err := os.ReadFile("../schema.sql")
	if err != nil {
		pool.Close()
		admin.Exec(`DROP SCHEMA "` + schema + `" CASCADE`)
		admin.Close()
		t.Fatalf("read schema.sql: %v", err)
	}
	if _, err := pool.Exec(string(ddl)); err != nil {
		pool.Close()
		admin.Exec(`DROP SCHEMA "` + schema + `" CASCADE`)
		admin.Close()
		t.Fatalf("apply schema.sql: %v", err)
	}

	cleanup := func() {
		pool.Close()
		if _, err := admin.Exec(`DROP SCHEMA "` + schema + `" CASCADE`); err != nil {
			t.Errorf("drop schema %s: %v", schema, err)
		}
		admin.Close()
	}
	return NewStore(pool), pool, cleanup
}

// withSearchPath returns base with a connection option that pins search_path to
// schema for every pooled connection, handling both URL and keyword DSN forms.
func withSearchPath(base, schema string) (string, error) {
	opt := "-c search_path=" + schema
	if strings.Contains(base, "://") {
		u, err := url.Parse(base)
		if err != nil {
			return "", err
		}
		q := u.Query()
		q.Set("options", opt)
		u.RawQuery = q.Encode()
		return u.String(), nil
	}
	return base + " options='" + opt + "'", nil
}

func randToken() string {
	b := make([]byte, 8)
	if _, err := rand.Read(b); err != nil {
		panic(err)
	}
	return hex.EncodeToString(b)
}

// mkSpace is a convenience for CreateSpace in tests.
func mkSpace(t *testing.T, s *Store, ctx context.Context, owner, name string) *Space {
	t.Helper()
	sp, err := s.CreateSpace(ctx, core.SpaceRef{Owner: owner, Name: name})
	if err != nil {
		t.Fatalf("create space ~%s/%s: %v", owner, name, err)
	}
	return sp
}

// docID parses a document ID or fails the test.
func docID(t *testing.T, s string) core.DocID {
	t.Helper()
	id, err := core.ParseDocID(s)
	if err != nil {
		t.Fatalf("parse doc id %q: %v", s, err)
	}
	return id
}

// mkProposal opens a proposal with plausible provenance.
func mkProposal(t *testing.T, s *Store, ctx context.Context, spaceID int, title string) *Proposal {
	t.Helper()
	p, err := s.OpenProposal(ctx, &Proposal{
		SpaceID:      spaceID,
		Title:        title,
		Rationale:    "because",
		BaseRev:      "0000000000000000000000000000000000000000",
		Agent:        "claude-code/spec-writer",
		AgentSession: "8fb9c9a4-b078-4af1-89eb-d97c522f9921",
	})
	if err != nil {
		t.Fatalf("open proposal %q: %v", title, err)
	}
	return p
}