package sqlite

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

	"github.com/google/uuid"
)

// Connection is a remote backend credential (instance or project scope).
type Connection struct {
	ID        string    `json:"id"`
	Name      string    `json:"name"`
	Kind      string    `json:"kind"`
	BaseURL   string    `json:"baseUrl"`
	Scope     string    `json:"scope"` // instance | project
	CreatedBy string    `json:"createdBy"`
	CreatedAt time.Time `json:"createdAt"`
	Token     string    `json:"-"` // only populated when decrypted for sync
}

// CreateConnection stores an encrypted token.
func (db *DB) CreateConnection(name, kind, baseURL, scope, token, createdBy string) (Connection, error) {
	name = strings.TrimSpace(name)
	kind = strings.TrimSpace(kind)
	baseURL = strings.TrimSpace(baseURL)
	scope = strings.TrimSpace(scope)
	if name == "" || kind == "" || baseURL == "" || token == "" {
		return Connection{}, fmt.Errorf("name, kind, baseUrl and token required")
	}
	if scope == "" {
		scope = "instance"
	}
	enc, err := db.EncryptToken(token)
	if err != nil {
		return Connection{}, err
	}
	c := Connection{
		ID:        uuid.NewString(),
		Name:      name,
		Kind:      kind,
		BaseURL:   baseURL,
		Scope:     scope,
		CreatedBy: createdBy,
		CreatedAt: time.Now().UTC(),
	}
	_, err = db.SQL.Exec(`
		INSERT INTO connections(id, name, kind, base_url, scope, encrypted_token, created_by, created_at)
		VALUES(?,?,?,?,?,?,?,?)`,
		c.ID, c.Name, c.Kind, c.BaseURL, c.Scope, enc, c.CreatedBy, formatTime(c.CreatedAt))
	if err != nil {
		return Connection{}, err
	}
	return c, nil
}

// ListConnections returns connections without tokens.
func (db *DB) ListConnections() ([]Connection, error) {
	rows, err := db.SQL.Query(`
		SELECT id, name, kind, base_url, scope, created_by, created_at
		FROM connections ORDER BY name COLLATE NOCASE`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []Connection
	for rows.Next() {
		var c Connection
		var created string
		if err := rows.Scan(&c.ID, &c.Name, &c.Kind, &c.BaseURL, &c.Scope, &c.CreatedBy, &created); err != nil {
			return nil, err
		}
		c.CreatedAt = parseTime(created)
		out = append(out, c)
	}
	if out == nil {
		out = []Connection{}
	}
	return out, rows.Err()
}

// GetConnection loads a connection and decrypts its token.
func (db *DB) GetConnection(id string) (Connection, error) {
	var c Connection
	var created string
	var blob []byte
	err := db.SQL.QueryRow(`
		SELECT id, name, kind, base_url, scope, encrypted_token, created_by, created_at
		FROM connections WHERE id = ?`, id).
		Scan(&c.ID, &c.Name, &c.Kind, &c.BaseURL, &c.Scope, &blob, &c.CreatedBy, &created)
	if err == sql.ErrNoRows {
		return Connection{}, fmt.Errorf("connection %s: not found", id)
	}
	if err != nil {
		return Connection{}, err
	}
	c.CreatedAt = parseTime(created)
	c.Token, err = db.DecryptToken(blob)
	if err != nil {
		return Connection{}, err
	}
	return c, nil
}

// DeleteConnection removes a connection.
func (db *DB) DeleteConnection(id string) error {
	res, err := db.SQL.Exec(`DELETE FROM connections WHERE id = ?`, id)
	if err != nil {
		return err
	}
	n, _ := res.RowsAffected()
	if n == 0 {
		return fmt.Errorf("connection %s: not found", id)
	}
	return nil
}