package data

import (
	"context"
	"database/sql"
	"encoding/json"
	"fmt"
	"strings"
	"sync"

	"github.com/mattn/go-sqlite3"
)

// mcpify:versions:begin
var VersionStoreFiles = map[string]string{
{%- for v in version_entries %}
	"{{ v.label }}": "{{ v.db_file }}",
{%- endfor %}
}

// mcpify:versions:end

// ResolveStorePath resolves the active ApiVersion (from the config
// cascade) to its store file — the default version's data always lives
// in the project-root mcp_store.db this target has always used; every
// other version lives in its own mcp_store_v<label>.db sibling, added by
// a later `mcpify add-version` call.
func ResolveStorePath(apiVersion string) (string, error) {
	path, ok := VersionStoreFiles[apiVersion]
	if !ok {
		return "", fmt.Errorf("unknown api_version '%s' — run the 'versions' command to see what's available", apiVersion)
	}
	return path, nil
}

// ChromemPathFor derives an ApiVersion's chromem-go vector-store persist
// directory from its .db file name — mcp_store.db becomes
// mcp_store.chromem, mcp_store_v10.7.db becomes mcp_store_v10.7.chromem —
// so each version gets its own vector-store directory exactly parallel
// to its own .db file, matching ResolveStorePath's one-store-per-version
// scheme on the relational side. Without this, every version shared the
// same hardcoded "mcp_store.chromem" collection, so semantic search
// against a non-default ApiVersion would silently query whichever
// version happened to populate that shared collection last.
func ChromemPathFor(apiVersion string) (string, error) {
	dbPath, err := ResolveStorePath(apiVersion)
	if err != nil {
		return "", err
	}
	return strings.TrimSuffix(dbPath, ".db") + ".chromem", nil
}

// EndpointRecord is one row of mcp_store.db's endpoints table (written by
// mcpify's shared pipeline, Story 5/6) — the relational half of the
// deliberate divergence in v5-implementation-plan.md's Context section:
// Go reads this table via mattn/go-sqlite3 but never loads the sqlite-vec
// extension or touches semantic_endpoints; vectors live in a sibling
// chromem-go collection instead (services/vectorstore.go).
type EndpointRecord struct {
	OperationID   string
	Path          string
	Method        string
	Summary       *string
	Description   *string
	InputSchema   json.RawMessage
	OutputSchema  json.RawMessage
	AuthSchemeRef *string
}

// Store is the read-only relational data-access layer against
// mcp_store.db's endpoints table.
type Store struct {
	db *sql.DB
}

// Open opens mcp_store.db read-only — this project only ever reads it,
// except cmd/populate-embeddings, which also only ever reads this table
// (what it writes to is the chromem-go collection, not mcp_store.db).
func Open(path string) (*Store, error) {
	db, err := sql.Open("sqlite3", fmt.Sprintf("file:%s?mode=ro", path))
	if err != nil {
		return nil, err
	}
	return &Store{db: db}, nil
}

func (s *Store) Close() error { return s.db.Close() }

var (
	cachedStoresMu sync.Mutex
	cachedStores   = map[string]*Store{}
)

// CachedStore returns a process-wide, in-memory-backed Store for
// apiVersion: on first access, opens the on-disk file read-only via Open,
// copies its entire contents into a fresh in-memory database (via
// SQLite's backup API), then closes the on-disk connection immediately —
// so its file handle/lock is held only for that brief copy, not for the
// process's lifetime. This means an external process (a fresh
// populate-embeddings run, a deployment replacing the file) can update
// mcp_store.db without hitting "database is locked" against a live
// server, at the cost of the running process only picking up such an
// update on its next restart.
//
// Unlike Open, the returned *Store must not be closed by the caller —
// it's cached and reused for the rest of the process's lifetime. *sql.DB
// (and so *Store) is already safe for concurrent use by multiple
// goroutines, so no additional locking is needed once this returns.
func CachedStore(apiVersion string) (*Store, error) {
	path, err := ResolveStorePath(apiVersion)
	if err != nil {
		return nil, err
	}
	return cachedInMemoryStore(apiVersion, path)
}

// Does the actual work for CachedStore, taking an explicit path (rather
// than resolving one from apiVersion itself) so it's testable against an
// arbitrary tempdir path — cacheKey and path are separate parameters
// because two different apiVersions should never collide in the
// process-wide cache even if (hypothetically) they resolved to the same
// file.
func cachedInMemoryStore(cacheKey, path string) (*Store, error) {
	cachedStoresMu.Lock()
	defer cachedStoresMu.Unlock()

	if cached, ok := cachedStores[cacheKey]; ok {
		return cached, nil
	}

	diskStore, err := Open(path)
	if err != nil {
		return nil, err
	}
	defer func() { _ = diskStore.Close() }()

	memDB, err := sql.Open("sqlite3", ":memory:")
	if err != nil {
		return nil, err
	}
	// database/sql opens connections lazily — force a real connection to
	// exist before backupToMemory reaches into the driver for it.
	if err := memDB.Ping(); err != nil {
		return nil, err
	}

	if err := backupToMemory(diskStore.db, memDB); err != nil {
		return nil, err
	}

	store := &Store{db: memDB}
	cachedStores[cacheKey] = store
	return store, nil
}

// backupToMemory copies every page of src into dst using SQLite's native
// online backup API — an exact copy (schema, indexes, data) rather than
// a manual table-by-table SQL re-creation.
func backupToMemory(src, dst *sql.DB) error {
	ctx := context.Background()

	srcConn, err := src.Conn(ctx)
	if err != nil {
		return err
	}
	defer func() { _ = srcConn.Close() }()

	dstConn, err := dst.Conn(ctx)
	if err != nil {
		return err
	}
	defer func() { _ = dstConn.Close() }()

	return dstConn.Raw(func(dstDriverConn any) error {
		dstSQLiteConn := dstDriverConn.(*sqlite3.SQLiteConn)
		return srcConn.Raw(func(srcDriverConn any) error {
			srcSQLiteConn := srcDriverConn.(*sqlite3.SQLiteConn)
			backup, err := dstSQLiteConn.Backup("main", srcSQLiteConn, "main")
			if err != nil {
				return err
			}
			defer func() { _ = backup.Finish() }()

			for {
				done, err := backup.Step(-1)
				if err != nil {
					return err
				}
				if done {
					return nil
				}
			}
		})
	})
}

const endpointColumns = "operation_id, path, method, summary, description, input_schema, output_schema, auth_scheme_ref"

func scanEndpoint(row interface{ Scan(...any) error }) (EndpointRecord, error) {
	var record EndpointRecord
	var inputSchema, outputSchema []byte
	err := row.Scan(
		&record.OperationID,
		&record.Path,
		&record.Method,
		&record.Summary,
		&record.Description,
		&inputSchema,
		&outputSchema,
		&record.AuthSchemeRef,
	)
	record.InputSchema = json.RawMessage(inputSchema)
	record.OutputSchema = json.RawMessage(outputSchema)
	return record, err
}

// GetEndpoint returns the endpoint named by operationID, or (zero value,
// false) if no such operation exists.
func (s *Store) GetEndpoint(operationID string) (EndpointRecord, bool, error) {
	row := s.db.QueryRow(
		"SELECT "+endpointColumns+" FROM endpoints WHERE operation_id = ?", operationID,
	)
	record, err := scanEndpoint(row)
	if err == sql.ErrNoRows {
		return EndpointRecord{}, false, nil
	}
	if err != nil {
		return EndpointRecord{}, false, err
	}
	return record, true, nil
}

// ListEndpoints returns every endpoint row, in no particular order.
func (s *Store) ListEndpoints() ([]EndpointRecord, error) {
	rows, err := s.db.Query("SELECT " + endpointColumns + " FROM endpoints")
	if err != nil {
		return nil, err
	}
	defer func() { _ = rows.Close() }()

	var records []EndpointRecord
	for rows.Next() {
		record, err := scanEndpoint(rows)
		if err != nil {
			return nil, err
		}
		records = append(records, record)
	}
	return records, rows.Err()
}
