// Package store holds chapter 10's closed list and nothing else, on SQLite or PostgreSQL.
package store

import (
	"context"
	"database/sql"
	"fmt"
	"os"
	"path/filepath"
	"strconv"
	"strings"

	_ "github.com/jackc/pgx/v5/stdlib"
	_ "modernc.org/sqlite"

	"github.com/barerepo/server/internal/config"
)

type DB struct {
	*sql.DB
	Kind config.Kind
}

// Open connects by URL and applies every migration, forward only. Chapter 41.8.
func Open(ctx context.Context, dbURL string) (*DB, error) {
	kind, err := config.Config{Database: config.Database{URL: dbURL}}.DatabaseKind()
	if err != nil {
		return nil, err
	}
	driver, dsn, err := dataSource(kind, dbURL)
	if err != nil {
		return nil, err
	}
	sqldb, err := sql.Open(driver, dsn)
	if err != nil {
		return nil, err
	}
	if kind == config.SQLite {
		// One writer, because SQLite serialises anyway and a pool only makes that SQLITE_BUSY.
		sqldb.SetMaxOpenConns(1)
	} else {
		sqldb.SetMaxOpenConns(16)
		sqldb.SetMaxIdleConns(4)
	}
	if err := sqldb.PingContext(ctx); err != nil {
		sqldb.Close()
		return nil, fmt.Errorf("%s: %w", dbURL, err)
	}
	db := &DB{DB: sqldb, Kind: kind}
	if err := db.migrate(ctx); err != nil {
		sqldb.Close()
		return nil, err
	}
	return db, nil
}

// dataSource turns one config URL into a driver name and a DSN.
func dataSource(kind config.Kind, dbURL string) (driver, dsn string, err error) {
	switch kind {
	case config.SQLite:
		path := strings.TrimPrefix(dbURL, "sqlite:")
		path = strings.TrimPrefix(path, "//")
		if path == "" {
			return "", "", fmt.Errorf("database.url names no sqlite file")
		}
		if dir := filepath.Dir(path); dir != "." {
			if err := os.MkdirAll(dir, 0o750); err != nil {
				return "", "", err
			}
		}
		// WAL so a reader never blocks the writer, and foreign keys because SQLite ignores them.
		return "sqlite", "file:" + path +
			"?_pragma=journal_mode(WAL)&_pragma=busy_timeout(5000)" +
			"&_pragma=foreign_keys(1)&_pragma=synchronous(NORMAL)", nil
	case config.Postgres:
		return "pgx", dbURL, nil
	}
	return "", "", fmt.Errorf("unsupported database %q", kind)
}

// rebind turns ? into $1 for PostgreSQL, which is the only dialect difference above the DDL.
func (db *DB) rebind(q string) string {
	if db.Kind != config.Postgres {
		return q
	}
	var b strings.Builder
	b.Grow(len(q) + 8)
	n := 0
	for i := 0; i < len(q); i++ {
		if q[i] != '?' {
			b.WriteByte(q[i])
			continue
		}
		n++
		b.WriteByte('$')
		b.WriteString(strconv.Itoa(n))
	}
	return b.String()
}

func (db *DB) ExecContext(ctx context.Context, q string, args ...any) (sql.Result, error) {
	return db.DB.ExecContext(ctx, db.rebind(q), args...)
}

func (db *DB) QueryContext(ctx context.Context, q string, args ...any) (*sql.Rows, error) {
	return db.DB.QueryContext(ctx, db.rebind(q), args...)
}

func (db *DB) QueryRowContext(ctx context.Context, q string, args ...any) *sql.Row {
	return db.DB.QueryRowContext(ctx, db.rebind(q), args...)
}

// Tx is a transaction that rebinds placeholders the same way DB does.
type Tx struct {
	*sql.Tx
	db *DB
}

func (db *DB) Begin(ctx context.Context) (*Tx, error) {
	tx, err := db.DB.BeginTx(ctx, nil)
	if err != nil {
		return nil, err
	}
	return &Tx{Tx: tx, db: db}, nil
}

func (tx *Tx) ExecContext(ctx context.Context, q string, args ...any) (sql.Result, error) {
	return tx.Tx.ExecContext(ctx, tx.db.rebind(q), args...)
}

func (tx *Tx) QueryRowContext(ctx context.Context, q string, args ...any) *sql.Row {
	return tx.Tx.QueryRowContext(ctx, tx.db.rebind(q), args...)
}

// migrations are append-only in two dialects, and TestMigrationsAgree checks they agree.
type migration struct{ sqlite, postgres string }

var migrations = []migration{{
	sqlite: `
	CREATE TABLE accounts (
		name       TEXT    PRIMARY KEY,
		admin      INTEGER NOT NULL DEFAULT 0,
		created_at INTEGER NOT NULL
	);
	CREATE TABLE pubkeys (
		id          INTEGER PRIMARY KEY,
		account     TEXT    NOT NULL REFERENCES accounts(name) ON DELETE CASCADE,
		fingerprint TEXT    NOT NULL UNIQUE,
		algo        TEXT    NOT NULL,
		blob        TEXT    NOT NULL,
		comment     TEXT    NOT NULL DEFAULT '',
		created_at  INTEGER NOT NULL,
		last_used   INTEGER
	);
	CREATE INDEX pubkeys_account ON pubkeys(account);
	CREATE TABLE repos (
		owner      TEXT    NOT NULL REFERENCES accounts(name) ON DELETE CASCADE,
		name       TEXT    NOT NULL,
		created_at INTEGER NOT NULL,
		PRIMARY KEY (owner, name)
	);
	CREATE TABLE tokens (
		id         INTEGER PRIMARY KEY,
		kind       TEXT    NOT NULL,
		hash       TEXT    NOT NULL UNIQUE,
		account    TEXT    NOT NULL REFERENCES accounts(name) ON DELETE CASCADE,
		scope      TEXT    NOT NULL DEFAULT '',
		label      TEXT    NOT NULL DEFAULT '',
		created_at INTEGER NOT NULL,
		expires_at INTEGER,
		last_used  INTEGER
	);
	CREATE INDEX tokens_account ON tokens(account, kind);
	CREATE TABLE redirects (
		old_owner  TEXT    NOT NULL,
		old_name   TEXT    NOT NULL,
		new_owner  TEXT    NOT NULL,
		new_name   TEXT    NOT NULL,
		created_at INTEGER NOT NULL,
		PRIMARY KEY (old_owner, old_name)
	);`,
	postgres: `
	CREATE TABLE accounts (
		name       TEXT     PRIMARY KEY,
		admin      SMALLINT NOT NULL DEFAULT 0,
		created_at BIGINT   NOT NULL
	);
	CREATE TABLE pubkeys (
		id          BIGSERIAL PRIMARY KEY,
		account     TEXT      NOT NULL REFERENCES accounts(name) ON DELETE CASCADE,
		fingerprint TEXT      NOT NULL UNIQUE,
		algo        TEXT      NOT NULL,
		blob        TEXT      NOT NULL,
		comment     TEXT      NOT NULL DEFAULT '',
		created_at  BIGINT    NOT NULL,
		last_used   BIGINT
	);
	CREATE INDEX pubkeys_account ON pubkeys(account);
	CREATE TABLE repos (
		owner      TEXT   NOT NULL REFERENCES accounts(name) ON DELETE CASCADE,
		name       TEXT   NOT NULL,
		created_at BIGINT NOT NULL,
		PRIMARY KEY (owner, name)
	);
	CREATE TABLE tokens (
		id         BIGSERIAL PRIMARY KEY,
		kind       TEXT      NOT NULL,
		hash       TEXT      NOT NULL UNIQUE,
		account    TEXT      NOT NULL REFERENCES accounts(name) ON DELETE CASCADE,
		scope      TEXT      NOT NULL DEFAULT '',
		label      TEXT      NOT NULL DEFAULT '',
		created_at BIGINT    NOT NULL,
		expires_at BIGINT,
		last_used  BIGINT
	);
	CREATE INDEX tokens_account ON tokens(account, kind);
	CREATE TABLE redirects (
		old_owner  TEXT   NOT NULL,
		old_name   TEXT   NOT NULL,
		new_owner  TEXT   NOT NULL,
		new_name   TEXT   NOT NULL,
		created_at BIGINT NOT NULL,
		PRIMARY KEY (old_owner, old_name)
	);`,
}, {
	// Sign-in nonces, on disk so a restart mid-flow costs nobody, and deletable to stop a replay.
	sqlite: `
	CREATE TABLE challenges (
		nonce      TEXT    PRIMARY KEY,
		account    TEXT    NOT NULL REFERENCES accounts(name) ON DELETE CASCADE,
		expires_at INTEGER NOT NULL
	);`,
	postgres: `
	CREATE TABLE challenges (
		nonce      TEXT   PRIMARY KEY,
		account    TEXT   NOT NULL REFERENCES accounts(name) ON DELETE CASCADE,
		expires_at BIGINT NOT NULL
	);`,
}, {
	// Runners and jobs are item 6: a lost queue is a build that did not run, not data gone.
	sqlite: `
	CREATE TABLE runners (
		id         INTEGER PRIMARY KEY,
		token_id   INTEGER NOT NULL REFERENCES tokens(id) ON DELETE CASCADE,
		repo       TEXT    NOT NULL,
		hostname   TEXT    NOT NULL,
		os         TEXT    NOT NULL DEFAULT '',
		arch       TEXT    NOT NULL DEFAULT '',
		labels     TEXT    NOT NULL DEFAULT '',
		attached_at INTEGER NOT NULL,
		last_seen  INTEGER NOT NULL
	);
	CREATE INDEX runners_repo ON runners(repo);
	CREATE TABLE jobs (
		id         INTEGER PRIMARY KEY,
		repo       TEXT    NOT NULL,
		ref        TEXT    NOT NULL,
		sha        TEXT    NOT NULL,
		command    TEXT    NOT NULL,
		image      TEXT    NOT NULL DEFAULT '',
		state      TEXT    NOT NULL,
		runner_id  INTEGER,
		attempts   INTEGER NOT NULL DEFAULT 0,
		created_at INTEGER NOT NULL,
		started_at INTEGER,
		log        TEXT    NOT NULL DEFAULT ''
	);
	CREATE INDEX jobs_queue ON jobs(repo, state, id);`,
	postgres: `
	CREATE TABLE runners (
		id          BIGSERIAL PRIMARY KEY,
		token_id    BIGINT    NOT NULL REFERENCES tokens(id) ON DELETE CASCADE,
		repo        TEXT      NOT NULL,
		hostname    TEXT      NOT NULL,
		os          TEXT      NOT NULL DEFAULT '',
		arch        TEXT      NOT NULL DEFAULT '',
		labels      TEXT      NOT NULL DEFAULT '',
		attached_at BIGINT    NOT NULL,
		last_seen   BIGINT    NOT NULL
	);
	CREATE INDEX runners_repo ON runners(repo);
	CREATE TABLE jobs (
		id         BIGSERIAL PRIMARY KEY,
		repo       TEXT      NOT NULL,
		ref        TEXT      NOT NULL,
		sha        TEXT      NOT NULL,
		command    TEXT      NOT NULL,
		image      TEXT      NOT NULL DEFAULT '',
		state      TEXT      NOT NULL,
		runner_id  BIGINT,
		attempts   INTEGER   NOT NULL DEFAULT 0,
		created_at BIGINT    NOT NULL,
		started_at BIGINT,
		log        TEXT      NOT NULL DEFAULT ''
	);
	CREATE INDEX jobs_queue ON jobs(repo, state, id);`,
}, {
	// A signup challenge holds name and key server-side, and cannot key on an account yet.
	sqlite: `
	CREATE TABLE signup_challenges (
		nonce      TEXT    PRIMARY KEY,
		name       TEXT    NOT NULL,
		pubkey     TEXT    NOT NULL,
		expires_at INTEGER NOT NULL
	);`,
	postgres: `
	CREATE TABLE signup_challenges (
		nonce      TEXT   PRIMARY KEY,
		name       TEXT   NOT NULL,
		pubkey     TEXT   NOT NULL,
		expires_at BIGINT NOT NULL
	);`,
}, {
	// Events are item 6, rebuildable from git, and last_visited draws the inbox rule. 19.4.
	sqlite: `
	CREATE TABLE events (
		id         INTEGER PRIMARY KEY,
		kind       TEXT    NOT NULL,
		actor      TEXT    NOT NULL,
		repo       TEXT    NOT NULL,
		ref        TEXT    NOT NULL DEFAULT '',
		number     INTEGER NOT NULL DEFAULT 0,
		title      TEXT    NOT NULL DEFAULT '',
		created_at INTEGER NOT NULL
	);
	CREATE INDEX events_time ON events(created_at);
	CREATE INDEX events_repo ON events(repo, created_at);
	CREATE TABLE participation (
		account    TEXT    NOT NULL REFERENCES accounts(name) ON DELETE CASCADE,
		repo       TEXT    NOT NULL,
		number     INTEGER NOT NULL,
		PRIMARY KEY (account, repo, number)
	);
	ALTER TABLE accounts ADD COLUMN last_visited INTEGER;`,
	postgres: `
	CREATE TABLE events (
		id         BIGSERIAL PRIMARY KEY,
		kind       TEXT      NOT NULL,
		actor      TEXT      NOT NULL,
		repo       TEXT      NOT NULL,
		ref        TEXT      NOT NULL DEFAULT '',
		number     INTEGER   NOT NULL DEFAULT 0,
		title      TEXT      NOT NULL DEFAULT '',
		created_at BIGINT    NOT NULL
	);
	CREATE INDEX events_time ON events(created_at);
	CREATE INDEX events_repo ON events(repo, created_at);
	CREATE TABLE participation (
		account TEXT    NOT NULL REFERENCES accounts(name) ON DELETE CASCADE,
		repo    TEXT    NOT NULL,
		number  INTEGER NOT NULL,
		PRIMARY KEY (account, repo, number)
	);
	ALTER TABLE accounts ADD COLUMN last_visited BIGINT;`,
}, {
	// Chapter 15A: a job says which machine it needs and which workflow job it is.
	sqlite: `
	ALTER TABLE jobs ADD COLUMN labels TEXT NOT NULL DEFAULT '';
	ALTER TABLE jobs ADD COLUMN name   TEXT NOT NULL DEFAULT '';`,
	postgres: `
	ALTER TABLE jobs ADD COLUMN labels TEXT NOT NULL DEFAULT '';
	ALTER TABLE jobs ADD COLUMN name   TEXT NOT NULL DEFAULT '';`,
}, {
	// Chapter 23.4: a hook's failures are the server's to remember, since .barerepo/config cannot.
	sqlite: `
	CREATE TABLE webhooks (
		repo        TEXT    NOT NULL,
		url         TEXT    NOT NULL,
		failures    INTEGER NOT NULL DEFAULT 0,
		disabled_at INTEGER,
		last_error  TEXT    NOT NULL DEFAULT '',
		last_at     INTEGER,
		PRIMARY KEY (repo, url)
	);
	CREATE TABLE webhook_cursor (
		id       INTEGER PRIMARY KEY,
		event_id INTEGER NOT NULL
	);`,
	postgres: `
	CREATE TABLE webhooks (
		repo        TEXT    NOT NULL,
		url         TEXT    NOT NULL,
		failures    INTEGER NOT NULL DEFAULT 0,
		disabled_at BIGINT,
		last_error  TEXT    NOT NULL DEFAULT '',
		last_at     BIGINT,
		PRIMARY KEY (repo, url)
	);
	CREATE TABLE webhook_cursor (
		id       INTEGER PRIMARY KEY,
		event_id BIGINT  NOT NULL
	);`,
}, {
	// Chapter 17: one index, derived from git, carrying who may read it so the filter is in the query.
	sqlite: `
	CREATE TABLE search_docs (
		id      INTEGER PRIMARY KEY,
		repo    TEXT    NOT NULL,
		kind    TEXT    NOT NULL,
		path    TEXT    NOT NULL DEFAULT '',
		title   TEXT    NOT NULL DEFAULT '',
		body    TEXT    NOT NULL DEFAULT '',
		public  INTEGER NOT NULL DEFAULT 0,
		readers TEXT    NOT NULL DEFAULT ''
	);
	CREATE UNIQUE INDEX search_one ON search_docs(repo, kind, path);
	CREATE INDEX search_repo ON search_docs(repo);`,
	postgres: `
	CREATE TABLE search_docs (
		id      BIGSERIAL PRIMARY KEY,
		repo    TEXT      NOT NULL,
		kind    TEXT      NOT NULL,
		path    TEXT      NOT NULL DEFAULT '',
		title   TEXT      NOT NULL DEFAULT '',
		body    TEXT      NOT NULL DEFAULT '',
		public  INTEGER   NOT NULL DEFAULT 0,
		readers TEXT      NOT NULL DEFAULT ''
	);
	CREATE UNIQUE INDEX search_one ON search_docs(repo, kind, path);
	CREATE INDEX search_repo ON search_docs(repo);`,
}, {
	// Chapter 10 item 1 is account name to public keys, and a signing key is one of those.
	sqlite: `
	CREATE TABLE gpgkeys (
		id          INTEGER PRIMARY KEY,
		account     TEXT    NOT NULL REFERENCES accounts(name) ON DELETE CASCADE,
		fingerprint TEXT    NOT NULL UNIQUE,
		uid         TEXT    NOT NULL DEFAULT '',
		armor       TEXT    NOT NULL,
		created_at  INTEGER NOT NULL
	);
	CREATE INDEX gpgkeys_account ON gpgkeys(account);`,
	postgres: `
	CREATE TABLE gpgkeys (
		id          BIGSERIAL PRIMARY KEY,
		account     TEXT   NOT NULL REFERENCES accounts(name) ON DELETE CASCADE,
		fingerprint TEXT   NOT NULL UNIQUE,
		uid         TEXT   NOT NULL DEFAULT '',
		armor       TEXT   NOT NULL,
		created_at  BIGINT NOT NULL
	);
	CREATE INDEX gpgkeys_account ON gpgkeys(account);`,
}, {
	// Chapter 19.3: a failed build's second line is the runner and the exit, which title cannot hold.
	sqlite: `
	ALTER TABLE events ADD COLUMN detail TEXT NOT NULL DEFAULT '';`,
	postgres: `
	ALTER TABLE events ADD COLUMN detail TEXT NOT NULL DEFAULT '';`,
}, {
	// A retired key stops opening doors and keeps vouching for what it already signed, so it is kept and dated.
	sqlite: `
	ALTER TABLE pubkeys ADD COLUMN retired_at INTEGER;`,
	postgres: `
	ALTER TABLE pubkeys ADD COLUMN retired_at BIGINT;`,
}}

func (m migration) ddl(k config.Kind) string {
	if k == config.Postgres {
		return m.postgres
	}
	return m.sqlite
}

func (db *DB) migrate(ctx context.Context) error {
	// One tracking table in both dialects, because PRAGMA user_version exists in only one.
	if _, err := db.DB.ExecContext(ctx, `CREATE TABLE IF NOT EXISTS schema_migrations (
		version    INTEGER PRIMARY KEY,
		applied_at BIGINT  NOT NULL
	)`); err != nil {
		return err
	}
	var have int
	if err := db.QueryRowContext(ctx,
		`SELECT COALESCE(MAX(version), 0) FROM schema_migrations`).Scan(&have); err != nil {
		return err
	}
	for i := have; i < len(migrations); i++ {
		tx, err := db.Begin(ctx)
		if err != nil {
			return err
		}
		if _, err := tx.ExecContext(ctx, migrations[i].ddl(db.Kind)); err != nil {
			tx.Rollback()
			return fmt.Errorf("migration %d: %w", i+1, err)
		}
		if _, err := tx.ExecContext(ctx,
			`INSERT INTO schema_migrations (version, applied_at) VALUES (?, ?)`,
			i+1, now().Unix()); err != nil {
			tx.Rollback()
			return err
		}
		if err := tx.Commit(); err != nil {
			return err
		}
	}
	return nil
}