package db

import (
	"context"
	"database/sql"
	"fmt"
	"strings"
	_ "github.com/mattn/go-sqlite3"
)

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

	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 = conn.ExecContext(ctx, `
		create table if not exists repos (
			did text not null,
			rkey text not null,
			at_uri text generated always as ('at://' || did || '/' || 'sh.tangled.repo' || '/' || rkey) stored,
			cid text not null,

			-- record content
			name text not null,
			knot_domain text not null,

			-- sync data
			git_rev text not null,
			repo_sha text not null,
			state text not null default 'pending',
			error_msg text,
			retry_count integer not null default 0,
			retry_after integer not null default 0,

			unique(did, rkey)
		);

		-- knot hosts
		create table if not exists hosts (
			hostname text not null,
			no_ssl integer not null default 0,
			status text not null default 'active',
			last_seq integer not null default -1,

			unique(hostname)
		);
	`)
	if err != nil {
		return nil, fmt.Errorf("initializing db schema: %w", err)
	}

	return db, nil
}