package db

import (
	"context"
	"database/sql"
	"strings"

	"github.com/bluesky-social/indigo/atproto/syntax"
	_ "github.com/mattn/go-sqlite3"
	"tangled.org/core/log"
	"tangled.org/core/orm"
)

type DB struct {
	*sql.DB
}

func Make(ctx context.Context, dbPath string) (*DB, error) {
	// https://github.com/mattn/go-sqlite3#connection-string
	opts := []string{
		"_foreign_keys=1",
		"_journal_mode=WAL",
		"_synchronous=NORMAL",
		"_auto_vacuum=incremental",
	}

	logger := log.FromContext(ctx)
	logger = log.SubLogger(logger, "db")

	db, err := sql.Open("sqlite3", dbPath+"?"+strings.Join(opts, "&"))
	if err != nil {
		return nil, err
	}

	conn, err := db.Conn(ctx)
	if err != nil {
		return nil, err
	}
	defer conn.Close()

	_, err = db.Exec(`
		create table if not exists _jetstream (
			id integer primary key autoincrement,
			last_time_us integer not null
		);

		create table if not exists known_dids (
			did text primary key
		);

		create table if not exists repos (
			id integer primary key autoincrement,
			knot text not null,
			owner text not null,
			name text not null,
			addedAt text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),

			unique(owner, name)
		);

		create table if not exists repo_collaborators (
			-- identifiers
			id integer primary key autoincrement,
			did text not null,
			rkey text not null,
			at_uri text generated always as ('at://' || did || '/' || 'sh.tangled.repo.collaborator' || '/' || rkey) stored,

			repo text not null,
			subject text not null,

			addedAt text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
			unique(did, rkey)
		);

		create table if not exists spindle_members (
			-- identifiers for the record
			id integer primary key autoincrement,
			did text not null,
			rkey text not null,

			-- data
			instance text not null,
			subject text not null,
			created text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),

			-- constraints
			unique (did, instance, subject)
		);

		-- status event for a single workflow
		create table if not exists events (
			rkey text not null,
			nsid text not null,
			event text not null, -- json
			created integer not null -- unix nanos
		);
	`)
	if err != nil {
		return nil, err
	}

	// run migrations

	// NOTE: this won't migrate existing records
	// they will be fetched again with tap instead
	orm.RunMigration(conn, logger, "add-rkey-to-repos", func(tx *sql.Tx) error {
		// archive legacy repos (just in case)
		_, err = tx.Exec(`alter table repos rename to repos_old`)
		if err != nil {
			return err
		}

		_, err := tx.Exec(`
			create table repos (
				-- identifiers
				id integer primary key autoincrement,
				did text not null,
				rkey text not null,
				at_uri text generated always as ('at://' || did || '/' || 'sh.tangled.repo' || '/' || rkey) stored,

				name text not null,
				knot text not null,

				addedAt text not null default (strftime('%Y-%m-%dT%H:%M:%SZ', 'now')),
				unique(did, rkey)
			);
		`)
		if err != nil {
			return err
		}

		return nil
	})

	return &DB{db}, nil
}

func (d *DB) IsKnownDid(did syntax.DID) (bool, error) {
	// is spindle member / repo collaborator
	var exists bool
	err := d.QueryRow(
		`select exists (
			select 1 from repo_collaborators where subject = ?
			union all
			select 1 from spindle_members where did = ?
		)`,
		did,
		did,
	).Scan(&exists)
	return exists, err
}