//go:build integration
// +build integration

package sqlite_test

import (
	"context"
	"database/sql"
	"errors"
	"fmt"
	"os"
	"path/filepath"
	"slices"
	"strconv"
	"testing"
	"time"

	"github.com/stashapp/stash/internal/manager/config"
	"github.com/stashapp/stash/pkg/models"
	"github.com/stashapp/stash/pkg/sqlite"
	"github.com/stashapp/stash/pkg/txn"

	// necessary to register custom migrations
	_ "github.com/stashapp/stash/pkg/sqlite/migrations"
)

var epochTime = time.Unix(0, 0).UTC()

const (
	spacedSceneTitle = "zzz yyy xxx"
)

const (
	folderIdxRoot = iota
	folderIdxWithSubFolder
	folderIdxWithParentFolder
	folderIdxWithFiles
	folderIdxInZip

	folderIdxForObjectFiles
	folderIdxWithImageFiles
	folderIdxWithGalleryFiles
	folderIdxWithSceneFiles

	totalFolders
)

const (
	fileIdxZip = iota
	fileIdxInZip

	fileIdxStartVideoFiles
	fileIdxStartImageFiles
	fileIdxStartGalleryFiles

	totalFiles
)

const (
	sceneIdxWithGroup = iota
	sceneIdxWithGallery
	sceneIdxWithPerformer
	sceneIdx1WithPerformer
	sceneIdx2WithPerformer
	sceneIdxWithTwoPerformers
	sceneIdxWithThreePerformers
	sceneIdxWithTag
	sceneIdxWithTwoTags
	sceneIdxWithThreeTags
	sceneIdxWithMarkerAndTag
	sceneIdxWithMarkerTwoTags
	sceneIdxWithStudio
	sceneIdx1WithStudio
	sceneIdx2WithStudio
	sceneIdxWithMarkers
	sceneIdxWithPerformerTag
	sceneIdxWithTwoPerformerTag
	sceneIdxWithPerformerTwoTags
	sceneIdxWithSpacedName
	sceneIdxWithStudioPerformer
	sceneIdx1WithTwoStudioPerformer
	sceneIdx2WithTwoStudioPerformer
	sceneIdxWithGrandChildStudio
	sceneIdxMissingPhash
	sceneIdxWithPerformerParentTag
	sceneIdxWithGroupWithParent
	// new indexes above
	lastSceneIdx

	totalScenes = lastSceneIdx + 3
)

const dupeScenePhashes = 2

const (
	imageIdxWithGallery = iota
	imageIdx1WithGallery
	imageIdx2WithGallery
	imageIdxWithTwoGalleries
	imageIdxWithPerformer
	imageIdx1WithPerformer
	imageIdx2WithPerformer
	imageIdxWithTwoPerformers
	imageIdxWithThreePerformers
	imageIdxWithTag
	imageIdxWithTwoTags
	imageIdxWithThreeTags
	imageIdxWithStudio
	imageIdx1WithStudio
	imageIdx2WithStudio
	imageIdxWithStudioPerformer
	imageIdxInZip
	imageIdxWithPerformerTag
	imageIdxWithTwoPerformerTag
	imageIdxWithPerformerTwoTags
	imageIdxWithGrandChildStudio
	imageIdxWithPerformerParentTag
	// new indexes above
	totalImages
)

const (
	performerIdxWithScene = iota
	performerIdx1WithScene
	performerIdx2WithScene
	performerIdx3WithScene
	performerIdxWithTwoScenes
	performerIdxWithImage
	performerIdxWithTwoImages
	performerIdx1WithImage
	performerIdx2WithImage
	performerIdx3WithImage
	performerIdxWithTag
	performerIdx2WithTag
	performerIdxWithTwoTags
	performerIdxWithGallery
	performerIdxWithTwoGalleries
	performerIdx1WithGallery
	performerIdx2WithGallery
	performerIdx3WithGallery
	performerIdxWithSceneStudio
	performerIdxWithImageStudio
	performerIdxWithGalleryStudio
	performerIdxWithTwoSceneStudio
	performerIdxWithParentTag
	// new indexes above
	// performers with dup names start from the end
	performerIdx1WithDupName
	performerIdxWithDupName

	performersNameCase   = performerIdx1WithDupName
	performersNameNoCase = 2

	totalPerformers = performersNameCase + performersNameNoCase
)

const (
	groupIdxWithScene = iota
	groupIdxWithStudio
	groupIdxWithTag
	groupIdxWithTwoTags
	groupIdxWithThreeTags
	groupIdxWithGrandChild
	groupIdxWithChild
	groupIdxWithParentAndChild
	groupIdxWithParent
	groupIdxWithGrandParent
	groupIdxWithParentAndScene
	groupIdxWithChildWithScene
	// groups with dup names start from the end
	groupIdxWithDupName

	groupsNameCase   = groupIdxWithDupName
	groupsNameNoCase = 1
)

const (
	galleryIdxWithScene = iota
	galleryIdxWithChapters
	galleryIdxWithImage
	galleryIdx1WithImage
	galleryIdx2WithImage
	galleryIdxWithTwoImages
	galleryIdxWithPerformer
	galleryIdx1WithPerformer
	galleryIdx2WithPerformer
	galleryIdxWithTwoPerformers
	galleryIdxWithThreePerformers
	galleryIdxWithTag
	galleryIdxWithTwoTags
	galleryIdxWithThreeTags
	galleryIdxWithStudio
	galleryIdx1WithStudio
	galleryIdx2WithStudio
	galleryIdxWithPerformerTag
	galleryIdxWithTwoPerformerTag
	galleryIdxWithPerformerTwoTags
	galleryIdxWithStudioPerformer
	galleryIdxWithGrandChildStudio
	galleryIdxWithoutFile
	galleryIdxWithPerformerParentTag
	// new indexes above
	lastGalleryIdx

	totalGalleries = lastGalleryIdx + 1
)

const (
	tagIdxWithScene = iota
	tagIdx1WithScene
	tagIdx2WithScene
	tagIdx3WithScene
	tagIdxWithPrimaryMarkers
	tagIdxWithMarkers
	tagIdxWithCoverImage
	tagIdxWithImage
	tagIdx1WithImage
	tagIdx2WithImage
	tagIdx3WithImage
	tagIdxWithPerformer
	tagIdx1WithPerformer
	tagIdx2WithPerformer
	tagIdxWithStudio
	tagIdx1WithStudio
	tagIdx2WithStudio
	tagIdxWithGallery
	tagIdx1WithGallery
	tagIdx2WithGallery
	tagIdx3WithGallery
	tagIdxWithChildTag
	tagIdxWithParentTag
	tagIdxWithGrandChild
	tagIdxWithParentAndChild
	tagIdxWithGrandParent
	tagIdx2WithMarkers
	tagIdxWithGroup
	tagIdx1WithGroup
	tagIdx2WithGroup
	tagIdx3WithGroup
	// new indexes above
	// tags with dup names start from the end
	tagIdx1WithDupName
	tagIdxWithDupName

	tagsNameNoCase = 2
	tagsNameCase   = tagIdx1WithDupName

	totalTags = tagsNameCase + tagsNameNoCase
)

const (
	studioIdxWithScene = iota
	studioIdxWithTwoScenes
	studioIdxWithGroup
	studioIdxWithChildStudio
	studioIdxWithParentStudio
	studioIdxWithImage
	studioIdxWithTwoImages
	studioIdxWithGallery
	studioIdxWithTwoGalleries
	studioIdxWithScenePerformer
	studioIdxWithImagePerformer
	studioIdxWithGalleryPerformer
	studioIdx1WithTwoScenePerformer
	studioIdx2WithTwoScenePerformer
	studioIdxWithTag
	studioIdx2WithTag
	studioIdxWithTwoTags
	studioIdxWithParentTag
	studioIdxWithGrandChild
	studioIdxWithParentAndChild
	studioIdxWithGrandParent
	// new indexes above
	// studios with dup names start from the end
	studioIdxWithDupName

	studiosNameCase   = studioIdxWithDupName
	studiosNameNoCase = 1

	totalStudios = studiosNameCase + studiosNameNoCase
)

const (
	markerIdxWithScene = iota
	markerIdxWithTag
	markerIdxWithSceneTag
	markerIdxWithDuration
	markerIdx2WithDuration
	totalMarkers
)

const (
	chapterIdxWithGallery = iota
	totalChapters
)

const (
	savedFilterIdxScene = iota
	savedFilterIdxImage

	// new indexes above
	totalSavedFilters
)

const (
	pathField            = "Path"
	checksumField        = "Checksum"
	titleField           = "Title"
	detailsField         = "Details"
	urlField             = "URL"
	zipPath              = "zipPath.zip"
	firstSavedFilterName = "firstSavedFilterName"
)

var (
	folderIDs      []models.FolderID
	fileIDs        []models.FileID
	sceneFileIDs   []models.FileID
	imageFileIDs   []models.FileID
	galleryFileIDs []models.FileID
	chapterIDs     []int

	sceneIDs       []int
	imageIDs       []int
	performerIDs   []int
	groupIDs       []int
	galleryIDs     []int
	tagIDs         []int
	studioIDs      []int
	markerIDs      []int
	savedFilterIDs []int

	folderPaths []string

	tagNames       []string
	studioNames    []string
	groupNames     []string
	performerNames []string
)

type idAssociation struct {
	first  int
	second int
}

type linkMap map[int][]int

func (m linkMap) reverseLookup(idx int) []int {
	var result []int

	for k, v := range m {
		for _, vv := range v {
			if vv == idx {
				result = append(result, k)
			}
		}
	}

	return result
}

var (
	folderParentFolders = map[int]int{
		folderIdxWithSubFolder:    folderIdxRoot,
		folderIdxForObjectFiles:   folderIdxRoot,
		folderIdxWithParentFolder: folderIdxWithSubFolder,
		folderIdxWithSceneFiles:   folderIdxForObjectFiles,
		folderIdxWithImageFiles:   folderIdxForObjectFiles,
		folderIdxWithGalleryFiles: folderIdxForObjectFiles,
	}

	fileFolders = map[int]int{
		fileIdxZip:   folderIdxWithFiles,
		fileIdxInZip: folderIdxInZip,
	}

	folderZipFiles = map[int]int{
		folderIdxInZip: fileIdxZip,
	}

	fileZipFiles = map[int]int{
		fileIdxInZip: fileIdxZip,
	}
)

var (
	sceneTags = linkMap{
		sceneIdxWithTag:           {tagIdxWithScene},
		sceneIdxWithTwoTags:       {tagIdx1WithScene, tagIdx2WithScene},
		sceneIdxWithThreeTags:     {tagIdx1WithScene, tagIdx2WithScene, tagIdx3WithScene},
		sceneIdxWithMarkerAndTag:  {tagIdx3WithScene},
		sceneIdxWithMarkerTwoTags: {tagIdx2WithScene, tagIdx3WithScene},
	}

	scenePerformers = linkMap{
		sceneIdxWithPerformer:           {performerIdxWithScene},
		sceneIdxWithTwoPerformers:       {performerIdx1WithScene, performerIdx2WithScene},
		sceneIdxWithThreePerformers:     {performerIdx1WithScene, performerIdx2WithScene, performerIdx3WithScene},
		sceneIdxWithPerformerTag:        {performerIdxWithTag},
		sceneIdxWithTwoPerformerTag:     {performerIdxWithTag, performerIdx2WithTag},
		sceneIdxWithPerformerTwoTags:    {performerIdxWithTwoTags},
		sceneIdx1WithPerformer:          {performerIdxWithTwoScenes},
		sceneIdx2WithPerformer:          {performerIdxWithTwoScenes},
		sceneIdxWithStudioPerformer:     {performerIdxWithSceneStudio},
		sceneIdx1WithTwoStudioPerformer: {performerIdxWithTwoSceneStudio},
		sceneIdx2WithTwoStudioPerformer: {performerIdxWithTwoSceneStudio},
		sceneIdxWithPerformerParentTag:  {performerIdxWithParentTag},
	}

	sceneGalleries = linkMap{
		sceneIdxWithGallery: {galleryIdxWithScene},
	}

	sceneGroups = linkMap{
		sceneIdxWithGroup:           {groupIdxWithScene},
		sceneIdxWithGroupWithParent: {groupIdxWithParentAndScene},
	}

	sceneStudios = map[int]int{
		sceneIdxWithStudio:              studioIdxWithScene,
		sceneIdx1WithStudio:             studioIdxWithTwoScenes,
		sceneIdx2WithStudio:             studioIdxWithTwoScenes,
		sceneIdxWithStudioPerformer:     studioIdxWithScenePerformer,
		sceneIdx1WithTwoStudioPerformer: studioIdx1WithTwoScenePerformer,
		sceneIdx2WithTwoStudioPerformer: studioIdx2WithTwoScenePerformer,
		sceneIdxWithGrandChildStudio:    studioIdxWithGrandParent,
	}
)

type markerSpec struct {
	sceneIdx      int
	primaryTagIdx int
	tagIdxs       []int
}

var (
	// indexed by marker
	markerSpecs = []markerSpec{
		{sceneIdxWithMarkers, tagIdxWithPrimaryMarkers, nil},
		{sceneIdxWithMarkers, tagIdxWithPrimaryMarkers, []int{tagIdxWithMarkers}},
		{sceneIdxWithMarkers, tagIdxWithPrimaryMarkers, []int{tagIdx2WithMarkers}},
		{sceneIdxWithMarkers, tagIdxWithPrimaryMarkers, []int{tagIdxWithMarkers, tagIdx2WithMarkers}},
		{sceneIdxWithMarkerAndTag, tagIdxWithPrimaryMarkers, nil},
		{sceneIdxWithMarkerTwoTags, tagIdxWithPrimaryMarkers, nil},
	}
)

type chapterSpec struct {
	galleryIdx int
	title      string
	imageIndex int
}

var (
	// indexed by chapter
	chapterSpecs = []chapterSpec{
		{galleryIdxWithChapters, "Test1", 10},
	}
)

var (
	imageGalleries = linkMap{
		imageIdxWithGallery:      {galleryIdxWithImage},
		imageIdx1WithGallery:     {galleryIdxWithTwoImages},
		imageIdx2WithGallery:     {galleryIdxWithTwoImages},
		imageIdxWithTwoGalleries: {galleryIdx1WithImage, galleryIdx2WithImage},
	}
	imageStudios = map[int]int{
		imageIdxWithStudio:           studioIdxWithImage,
		imageIdx1WithStudio:          studioIdxWithTwoImages,
		imageIdx2WithStudio:          studioIdxWithTwoImages,
		imageIdxWithStudioPerformer:  studioIdxWithImagePerformer,
		imageIdxWithGrandChildStudio: studioIdxWithGrandParent,
	}
	imageTags = linkMap{
		imageIdxWithTag:       {tagIdxWithImage},
		imageIdxWithTwoTags:   {tagIdx1WithImage, tagIdx2WithImage},
		imageIdxWithThreeTags: {tagIdx1WithImage, tagIdx2WithImage, tagIdx3WithImage},
	}
	imagePerformers = linkMap{
		imageIdxWithPerformer:          {performerIdxWithImage},
		imageIdxWithTwoPerformers:      {performerIdx1WithImage, performerIdx2WithImage},
		imageIdxWithThreePerformers:    {performerIdx1WithImage, performerIdx2WithImage, performerIdx3WithImage},
		imageIdxWithPerformerTag:       {performerIdxWithTag},
		imageIdxWithTwoPerformerTag:    {performerIdxWithTag, performerIdx2WithTag},
		imageIdxWithPerformerTwoTags:   {performerIdxWithTwoTags},
		imageIdx1WithPerformer:         {performerIdxWithTwoImages},
		imageIdx2WithPerformer:         {performerIdxWithTwoImages},
		imageIdxWithStudioPerformer:    {performerIdxWithImageStudio},
		imageIdxWithPerformerParentTag: {performerIdxWithParentTag},
	}
)

var (
	galleryPerformers = linkMap{
		galleryIdxWithPerformer:          {performerIdxWithGallery},
		galleryIdxWithTwoPerformers:      {performerIdx1WithGallery, performerIdx2WithGallery},
		galleryIdxWithThreePerformers:    {performerIdx1WithGallery, performerIdx2WithGallery, performerIdx3WithGallery},
		galleryIdxWithPerformerTag:       {performerIdxWithTag},
		galleryIdxWithTwoPerformerTag:    {performerIdxWithTag, performerIdx2WithTag},
		galleryIdxWithPerformerTwoTags:   {performerIdxWithTwoTags},
		galleryIdx1WithPerformer:         {performerIdxWithTwoGalleries},
		galleryIdx2WithPerformer:         {performerIdxWithTwoGalleries},
		galleryIdxWithStudioPerformer:    {performerIdxWithGalleryStudio},
		galleryIdxWithPerformerParentTag: {performerIdxWithParentTag},
	}

	galleryStudios = map[int]int{
		galleryIdxWithStudio:           studioIdxWithGallery,
		galleryIdx1WithStudio:          studioIdxWithTwoGalleries,
		galleryIdx2WithStudio:          studioIdxWithTwoGalleries,
		galleryIdxWithStudioPerformer:  studioIdxWithGalleryPerformer,
		galleryIdxWithGrandChildStudio: studioIdxWithGrandParent,
	}

	galleryTags = linkMap{
		galleryIdxWithTag:       {tagIdxWithGallery},
		galleryIdxWithTwoTags:   {tagIdx1WithGallery, tagIdx2WithGallery},
		galleryIdxWithThreeTags: {tagIdx1WithGallery, tagIdx2WithGallery, tagIdx3WithGallery},
	}
)

var (
	groupStudioLinks = [][2]int{
		{groupIdxWithStudio, studioIdxWithGroup},
	}

	groupTags = linkMap{
		groupIdxWithTag:       {tagIdxWithGroup},
		groupIdxWithTwoTags:   {tagIdx1WithGroup, tagIdx2WithGroup},
		groupIdxWithThreeTags: {tagIdx1WithGroup, tagIdx2WithGroup, tagIdx3WithGroup},
	}
)

var (
	studioParentLinks = [][2]int{
		{studioIdxWithChildStudio, studioIdxWithParentStudio},
		{studioIdxWithGrandChild, studioIdxWithParentAndChild},
		{studioIdxWithParentAndChild, studioIdxWithGrandParent},
	}
)

var (
	studioTags = linkMap{
		studioIdxWithTag:       {tagIdxWithStudio},
		studioIdx2WithTag:      {tagIdx2WithStudio},
		studioIdxWithTwoTags:   {tagIdx1WithStudio, tagIdx2WithStudio},
		studioIdxWithParentTag: {tagIdxWithParentAndChild},
	}
)

var (
	performerTags = linkMap{
		performerIdxWithTag:       {tagIdxWithPerformer},
		performerIdx2WithTag:      {tagIdx2WithPerformer},
		performerIdxWithTwoTags:   {tagIdx1WithPerformer, tagIdx2WithPerformer},
		performerIdxWithParentTag: {tagIdxWithParentAndChild},
	}
)

var (
	tagParentLinks = [][2]int{
		{tagIdxWithChildTag, tagIdxWithParentTag},
		{tagIdxWithGrandChild, tagIdxWithParentAndChild},
		{tagIdxWithParentAndChild, tagIdxWithGrandParent},
	}
)

var (
	groupParentLinks = [][2]int{
		{groupIdxWithChild, groupIdxWithParent},
		{groupIdxWithGrandChild, groupIdxWithParentAndChild},
		{groupIdxWithParentAndChild, groupIdxWithGrandParent},
		{groupIdxWithChildWithScene, groupIdxWithParentAndScene},
	}
)

func indexesToIDs(ids []int, indexes []int) []int {
	ret := make([]int, len(indexes))
	for i, idx := range indexes {
		ret[i] = indexToID(ids, idx)
	}

	return ret
}

func indexToID(ids []int, idx int) int {
	if idx < 0 {
		return invalidID
	}
	return ids[idx]
}

func indexesToIDPtrs[T any](ids []T, indexes []int) []*T {
	ret := make([]*T, len(indexes))
	for i, idx := range indexes {
		ret[i] = indexToIDPtr(ids, idx)
	}

	return ret
}

func indexToIDPtr[T any](ids []T, idx int) *T {
	if idx < 0 {
		return nil
	}
	return &ids[idx]
}

func indexFromID(ids []int, id int) int {
	for i, v := range ids {
		if v == id {
			return i
		}
	}

	return -1
}

var db *sqlite.Database

func TestMain(m *testing.M) {
	// initialise empty config - needed by some migrations
	_ = config.InitializeEmpty()

	ret := runTests(m)
	os.Exit(ret)
}

func withTxn(f func(ctx context.Context) error) error {
	return txn.WithTxn(context.Background(), db, f)
}

func withRollbackTxn(f func(ctx context.Context) error) error {
	var ret error
	withTxn(func(ctx context.Context) error {
		ret = f(ctx)
		return errors.New("fake error for rollback")
	})

	return ret
}

func runWithRollbackTxn(t *testing.T, name string, f func(t *testing.T, ctx context.Context)) {
	withRollbackTxn(func(ctx context.Context) error {
		t.Run(name, func(t *testing.T) {
			f(t, ctx)
		})
		return nil
	})
}

func testTeardown(databaseFile string) {
	err := db.Close()

	if err != nil {
		panic(err)
	}

	err = os.Remove(databaseFile)
	if err != nil {
		panic(err)
	}
}

func runTests(m *testing.M) int {
	// create the database file
	f, err := os.CreateTemp("", "*.sqlite")
	if err != nil {
		panic(fmt.Sprintf("Could not create temporary file: %s", err.Error()))
	}

	f.Close()
	databaseFile := f.Name()
	db = sqlite.NewDatabase()
	db.SetBlobStoreOptions(sqlite.BlobStoreOptions{
		UseDatabase: true,
		// don't use filesystem
	})

	if err := db.Open(databaseFile); err != nil {
		panic(fmt.Sprintf("Could not initialize database: %s", err.Error()))
	}

	// defer close and delete the database
	defer testTeardown(databaseFile)

	err = populateDB()
	if err != nil {
		panic(fmt.Sprintf("Could not populate database: %s", err.Error()))
	}

	// run the tests
	return m.Run()
}

func populateDB() error {
	if err := withTxn(func(ctx context.Context) error {
		if err := createFolders(ctx); err != nil {
			return fmt.Errorf("creating folders: %w", err)
		}

		if err := createFiles(ctx); err != nil {
			return fmt.Errorf("creating files: %w", err)
		}

		if err := linkFoldersToZip(ctx); err != nil {
			return fmt.Errorf("linking folders to zip files: %w", err)
		}

		if err := createTags(ctx, db.Tag, tagsNameCase, tagsNameNoCase); err != nil {
			return fmt.Errorf("error creating tags: %s", err.Error())
		}

		if err := createGroups(ctx, db.Group, groupsNameCase, groupsNameNoCase); err != nil {
			return fmt.Errorf("error creating groups: %s", err.Error())
		}

		if err := createPerformers(ctx, performersNameCase, performersNameNoCase); err != nil {
			return fmt.Errorf("error creating performers: %s", err.Error())
		}

		if err := createStudios(ctx, studiosNameCase, studiosNameNoCase); err != nil {
			return fmt.Errorf("error creating studios: %s", err.Error())
		}

		if err := createGalleries(ctx, totalGalleries); err != nil {
			return fmt.Errorf("error creating galleries: %s", err.Error())
		}

		if err := createScenes(ctx, totalScenes); err != nil {
			return fmt.Errorf("error creating scenes: %s", err.Error())
		}

		if err := createImages(ctx, totalImages); err != nil {
			return fmt.Errorf("error creating images: %s", err.Error())
		}

		if err := addTagImage(ctx, db.Tag, tagIdxWithCoverImage); err != nil {
			return fmt.Errorf("error adding tag image: %s", err.Error())
		}

		if err := createSavedFilters(ctx, db.SavedFilter, totalSavedFilters); err != nil {
			return fmt.Errorf("error creating saved filters: %s", err.Error())
		}

		if err := linkGroupStudios(ctx, db.Group); err != nil {
			return fmt.Errorf("error linking group studios: %s", err.Error())
		}

		if err := linkStudiosParent(ctx); err != nil {
			return fmt.Errorf("error linking studios parent: %s", err.Error())
		}

		if err := linkTagsParent(ctx, db.Tag); err != nil {
			return fmt.Errorf("error linking tags parent: %s", err.Error())
		}

		if err := linkGroupsParent(ctx, db.Group); err != nil {
			return fmt.Errorf("error linking tags parent: %s", err.Error())
		}

		for _, ms := range markerSpecs {
			if err := createMarker(ctx, db.SceneMarker, ms); err != nil {
				return fmt.Errorf("error creating scene marker: %s", err.Error())
			}
		}
		for _, cs := range chapterSpecs {
			if err := createChapter(ctx, db.GalleryChapter, cs); err != nil {
				return fmt.Errorf("error creating gallery chapter: %s", err.Error())
			}
		}

		return nil
	}); err != nil {
		return err
	}

	return nil
}

func getFolderPath(index int, parentFolderIdx *int) string {
	path := getPrefixedStringValue("folder", index, pathField)

	if parentFolderIdx != nil {
		return filepath.Join(folderPaths[*parentFolderIdx], path)
	}

	return path
}

func getFolderBasename(index int, parentFolderIdx *int) string {
	return filepath.Base(getFolderPath(index, parentFolderIdx))
}

func getFolderModTime(index int) time.Time {
	return time.Date(2000, 1, (index%10)+1, 0, 0, 0, 0, time.UTC)
}

func makeFolder(i int) models.Folder {
	var folderID *models.FolderID
	var folderIdx *int
	if pidx, ok := folderParentFolders[i]; ok {
		folderIdx = &pidx
		v := folderIDs[pidx]
		folderID = &v
	}

	return models.Folder{
		ParentFolderID: folderID,
		DirEntry: models.DirEntry{
			// zip files have to be added after creating files
			ModTime: getFolderModTime(i),
		},
		Path: getFolderPath(i, folderIdx),
	}
}

func createFolders(ctx context.Context) error {
	qb := db.Folder

	for i := 0; i < totalFolders; i++ {
		folder := makeFolder(i)

		if err := qb.Create(ctx, &folder); err != nil {
			return fmt.Errorf("Error creating folder [%d] %v+: %s", i, folder, err.Error())
		}

		folderIDs = append(folderIDs, folder.ID)
		folderPaths = append(folderPaths, folder.Path)
	}

	return nil
}

func linkFoldersToZip(ctx context.Context) error {
	// link folders to zip files
	for folderIdx, fileIdx := range folderZipFiles {
		folderID := folderIDs[folderIdx]
		fileID := fileIDs[fileIdx]

		f, err := db.Folder.Find(ctx, folderID)
		if err != nil {
			return fmt.Errorf("Error finding folder [%d] to link to zip file [%d]", folderID, fileID)
		}

		f.ZipFileID = &fileID

		if err := db.Folder.Update(ctx, f); err != nil {
			return fmt.Errorf("Error linking folder [%d] to zip file [%d]: %s", folderIdx, fileIdx, err.Error())
		}
	}

	return nil
}

func getFileBaseName(index int) string {
	return getPrefixedStringValue("file", index, "basename")
}

func getFileStringValue(index int, field string) string {
	return getPrefixedStringValue("file", index, field)
}

func getFileModTime(index int) time.Time {
	return getFolderModTime(index)
}

func getFilePhash(index int) int64 {
	return int64(index * 567)
}

func getFileFingerprints(index int) []models.Fingerprint {
	return []models.Fingerprint{
		{
			Type:        models.FingerprintTypeMD5,
			Fingerprint: getPrefixedStringValue("file", index, "md5"),
		},
		{
			Type:        models.FingerprintTypeOshash,
			Fingerprint: getPrefixedStringValue("file", index, "oshash"),
		},
		{
			Type:        models.FingerprintTypePhash,
			Fingerprint: getFilePhash(index),
		},
	}
}

func getFileSize(index int) int64 {
	return int64(index) * 10
}

func getFileDuration(index int) float64 {
	duration := (index % 4) + 1
	duration = duration * 100

	return float64(duration) + 0.432
}

func makeFile(i int) models.File {
	folderID := folderIDs[fileFolders[i]]
	if folderID == 0 {
		folderID = folderIDs[folderIdxWithFiles]
	}

	var zipFileID *models.FileID
	if zipFileIndex, found := fileZipFiles[i]; found {
		zipFileID = &fileIDs[zipFileIndex]
	}

	var ret models.File
	baseFile := &models.BaseFile{
		Basename:       getFileBaseName(i),
		ParentFolderID: folderID,
		DirEntry: models.DirEntry{
			// zip files have to be added after creating files
			ModTime:   getFileModTime(i),
			ZipFileID: zipFileID,
		},
		Fingerprints: getFileFingerprints(i),
		Size:         getFileSize(i),
	}

	ret = baseFile

	if i >= fileIdxStartVideoFiles && i < fileIdxStartImageFiles {
		ret = &models.VideoFile{
			BaseFile:   baseFile,
			Format:     getFileStringValue(i, "format"),
			Width:      getWidth(i),
			Height:     getHeight(i),
			Duration:   getFileDuration(i),
			VideoCodec: getFileStringValue(i, "videoCodec"),
			AudioCodec: getFileStringValue(i, "audioCodec"),
			FrameRate:  getFileDuration(i) * 2,
			BitRate:    int64(getFileDuration(i)) * 3,
		}
	} else if i >= fileIdxStartImageFiles && i < fileIdxStartGalleryFiles {
		ret = &models.ImageFile{
			BaseFile: baseFile,
			Format:   getFileStringValue(i, "format"),
			Width:    getWidth(i),
			Height:   getHeight(i),
		}
	}

	return ret
}

func createFiles(ctx context.Context) error {
	qb := db.File

	for i := 0; i < totalFiles; i++ {
		file := makeFile(i)

		if err := qb.Create(ctx, file); err != nil {
			return fmt.Errorf("Error creating file [%d] %v+: %s", i, file, err.Error())
		}

		fileIDs = append(fileIDs, file.Base().ID)
	}

	return nil
}

func getPrefixedStringValue(prefix string, index int, field string) string {
	return fmt.Sprintf("%s_%04d_%s", prefix, index, field)
}

func getPrefixedNullStringValue(prefix string, index int, field string) sql.NullString {
	if index > 0 && index%5 == 0 {
		return sql.NullString{}
	}
	if index > 0 && index%6 == 0 {
		return sql.NullString{
			String: "",
			Valid:  true,
		}
	}
	return sql.NullString{
		String: getPrefixedStringValue(prefix, index, field),
		Valid:  true,
	}
}

func getSceneStringValue(index int, field string) string {
	return getPrefixedStringValue("scene", index, field)
}

func getScenePhash(index int, field string) int64 {
	return int64(index % (totalScenes - dupeScenePhashes) * 1234)
}

func getSceneStringPtr(index int, field string) *string {
	v := getPrefixedStringValue("scene", index, field)
	return &v
}

func getSceneNullStringPtr(index int, field string) *string {
	return getStringPtrFromNullString(getPrefixedNullStringValue("scene", index, field))
}

func getSceneEmptyString(index int, field string) string {
	v := getSceneNullStringPtr(index, field)
	if v == nil {
		return ""
	}

	return *v
}

func getSceneTitle(index int) string {
	switch index {
	case sceneIdxWithSpacedName:
		return spacedSceneTitle
	default:
		return getSceneStringValue(index, titleField)
	}
}

func getRating(index int) sql.NullInt64 {
	rating := index % 6
	return sql.NullInt64{Int64: int64(rating * 20), Valid: rating > 0}
}

func getIntPtr(r sql.NullInt64) *int {
	if !r.Valid {
		return nil
	}

	v := int(r.Int64)
	return &v
}

func getStringPtrFromNullString(r sql.NullString) *string {
	if !r.Valid || r.String == "" {
		return nil
	}

	v := r.String
	return &v
}

func getStringPtr(r string) *string {
	if r == "" {
		return nil
	}

	return &r
}

func getEmptyStringFromPtr(v *string) string {
	if v == nil {
		return ""
	}

	return *v
}

func getOCounter(index int) int {
	return index % 3
}

func getSceneDuration(index int) float64 {
	duration := index + 1
	duration = duration * 100

	return float64(duration) + 0.432
}

func getHeight(index int) int {
	heights := []int{200, 240, 300, 480, 700, 720, 800, 1080, 1500, 2160, 3000}
	height := heights[index%len(heights)]
	return height
}

func getWidth(index int) int {
	height := getHeight(index)
	return height * 2
}

func getObjectDate(index int) *models.Date {
	dates := []string{"null", "2000-01-01", "0001-01-01", "2001-02-03"}
	date := dates[index%len(dates)]

	if date == "null" {
		return nil
	}

	ret, _ := models.ParseDate(date)
	return &ret
}

func sceneStashIDs(i int) []models.StashID {
	if i%5 == 0 {
		return nil
	}
	return []models.StashID{sceneStashID(i)}
}

func sceneStashID(i int) models.StashID {
	return models.StashID{
		StashID:   getSceneStringValue(i, "stashid"),
		Endpoint:  getSceneStringValue(0, "endpoint"),
		UpdatedAt: epochTime,
	}
}

func getSceneBasename(index int) string {
	return getSceneStringValue(index, pathField)
}

func makeSceneFile(i int) *models.VideoFile {
	fp := []models.Fingerprint{
		{
			Type:        models.FingerprintTypeMD5,
			Fingerprint: getSceneStringValue(i, checksumField),
		},
		{
			Type:        models.FingerprintTypeOshash,
			Fingerprint: getSceneStringValue(i, "oshash"),
		},
	}

	if i != sceneIdxMissingPhash {
		fp = append(fp, models.Fingerprint{
			Type:        models.FingerprintTypePhash,
			Fingerprint: getScenePhash(i, "phash"),
		})
	}

	return &models.VideoFile{
		BaseFile: &models.BaseFile{
			Path:           getFilePath(folderIdxWithSceneFiles, getSceneBasename(i)),
			Basename:       getSceneBasename(i),
			ParentFolderID: folderIDs[folderIdxWithSceneFiles],
			Fingerprints:   fp,
		},
		Duration: getSceneDuration(i),
		Height:   getHeight(i),
		Width:    getWidth(i),
	}
}

func getScenePlayDuration(index int) float64 {
	if index%5 == 0 {
		return 0
	}

	return float64(index%5) * 123.4
}

func getSceneResumeTime(index int) float64 {
	if index%5 == 0 {
		return 0
	}

	return float64(index%5) * 1.2
}

func makeScene(i int) *models.Scene {
	title := getSceneTitle(i)
	details := getSceneStringValue(i, "Details")

	var studioID *int
	if _, ok := sceneStudios[i]; ok {
		v := studioIDs[sceneStudios[i]]
		studioID = &v
	}

	gids := indexesToIDs(galleryIDs, sceneGalleries[i])
	pids := indexesToIDs(performerIDs, scenePerformers[i])
	tids := indexesToIDs(tagIDs, sceneTags[i])

	mids := indexesToIDs(groupIDs, sceneGroups[i])

	groups := make([]models.GroupsScenes, len(mids))
	for i, m := range mids {
		groups[i] = models.GroupsScenes{
			GroupID: m,
		}
	}

	rating := getRating(i)

	return &models.Scene{
		Title:   title,
		Details: details,
		URLs: models.NewRelatedStrings([]string{
			getSceneEmptyString(i, urlField),
		}),
		Rating:       getIntPtr(rating),
		Date:         getObjectDate(i),
		StudioID:     studioID,
		GalleryIDs:   models.NewRelatedIDs(gids),
		PerformerIDs: models.NewRelatedIDs(pids),
		TagIDs:       models.NewRelatedIDs(tids),
		Groups:       models.NewRelatedGroups(groups),
		StashIDs:     models.NewRelatedStashIDs(sceneStashIDs(i)),
		PlayDuration: getScenePlayDuration(i),
		ResumeTime:   getSceneResumeTime(i),
	}
}

func getSceneCustomFields(index int) map[string]interface{} {
	if index%5 == 0 {
		return nil
	}

	return map[string]interface{}{
		"string": getSceneStringValue(index, "custom"),
		"int":    int64(index % 5),
		"real":   float64(index) / 10,
	}
}

func createScenes(ctx context.Context, n int) error {
	sqb := db.Scene
	fqb := db.File

	for i := 0; i < n; i++ {
		f := makeSceneFile(i)
		if err := fqb.Create(ctx, f); err != nil {
			return fmt.Errorf("creating scene file: %w", err)
		}
		sceneFileIDs = append(sceneFileIDs, f.ID)

		scene := makeScene(i)

		if err := sqb.Create(ctx, scene, []models.FileID{f.ID}); err != nil {
			return fmt.Errorf("Error creating scene %v+: %s", scene, err.Error())
		}

		if err := sqb.SetCustomFields(ctx, scene.ID, models.CustomFieldsInput{Full: getSceneCustomFields(i)}); err != nil {
			return fmt.Errorf("Error setting custom fields for scene %d: %s", scene.ID, err.Error())
		}

		sceneIDs = append(sceneIDs, scene.ID)
	}

	return nil
}

func getImageStringValue(index int, field string) string {
	return fmt.Sprintf("image_%04d_%s", index, field)
}

func getImageNullStringPtr(index int, field string) *string {
	return getStringPtrFromNullString(getPrefixedNullStringValue("image", index, field))
}

func getImageEmptyString(index int, field string) string {
	v := getImageNullStringPtr(index, field)
	if v == nil {
		return ""
	}

	return *v
}

func getImageBasename(index int) string {
	return getImageStringValue(index, pathField)
}

func getImageCustomFields(index int) map[string]interface{} {
	if index%5 == 0 {
		return nil
	}

	return map[string]interface{}{
		"string": getImageStringValue(index, "custom"),
		"int":    int64(index % 5),
		"real":   float64(index) / 10,
	}
}

func makeImageFile(i int) *models.ImageFile {
	return &models.ImageFile{
		BaseFile: &models.BaseFile{
			Path:           getFilePath(folderIdxWithImageFiles, getImageBasename(i)),
			Basename:       getImageBasename(i),
			ParentFolderID: folderIDs[folderIdxWithImageFiles],
			Fingerprints: []models.Fingerprint{
				{
					Type:        models.FingerprintTypeMD5,
					Fingerprint: getImageStringValue(i, checksumField),
				},
			},
		},
		Height: getHeight(i),
		Width:  getWidth(i),
	}
}

func makeImage(i int) *models.Image {
	title := getImageStringValue(i, titleField)
	var studioID *int
	if _, ok := imageStudios[i]; ok {
		v := studioIDs[imageStudios[i]]
		studioID = &v
	}

	gids := indexesToIDs(galleryIDs, imageGalleries[i])
	pids := indexesToIDs(performerIDs, imagePerformers[i])
	tids := indexesToIDs(tagIDs, imageTags[i])

	return &models.Image{
		Title:   title,
		Details: getImageStringValue(i, detailsField),
		Rating:  getIntPtr(getRating(i)),
		Date:    getObjectDate(i),
		URLs: models.NewRelatedStrings([]string{
			getImageEmptyString(i, urlField),
		}),
		OCounter:     getOCounter(i),
		StudioID:     studioID,
		GalleryIDs:   models.NewRelatedIDs(gids),
		PerformerIDs: models.NewRelatedIDs(pids),
		TagIDs:       models.NewRelatedIDs(tids),
	}
}

func createImages(ctx context.Context, n int) error {
	qb := db.Image
	fqb := db.File

	for i := 0; i < n; i++ {
		f := makeImageFile(i)
		if i == imageIdxInZip {
			f.ZipFileID = &fileIDs[fileIdxZip]
		}

		if err := fqb.Create(ctx, f); err != nil {
			return fmt.Errorf("creating image file: %w", err)
		}
		imageFileIDs = append(imageFileIDs, f.ID)

		image := makeImage(i)

		err := qb.Create(ctx, &models.CreateImageInput{
			Image:        image,
			FileIDs:      []models.FileID{f.ID},
			CustomFields: getImageCustomFields(i),
		})

		if err != nil {
			return fmt.Errorf("Error creating image %v+: %s", image, err.Error())
		}

		imageIDs = append(imageIDs, image.ID)
	}

	return nil
}

func getGalleryStringValue(index int, field string) string {
	return getPrefixedStringValue("gallery", index, field)
}

func getGalleryNullStringValue(index int, field string) sql.NullString {
	return getPrefixedNullStringValue("gallery", index, field)
}

func getGalleryNullStringPtr(index int, field string) *string {
	return getStringPtrFromNullString(getPrefixedNullStringValue("gallery", index, field))
}

func getGalleryEmptyString(index int, field string) string {
	v := getGalleryNullStringPtr(index, field)
	if v == nil {
		return ""
	}

	return *v
}

func getGalleryBasename(index int) string {
	return getGalleryStringValue(index, pathField)
}

func makeGalleryFile(i int) *models.BaseFile {
	return &models.BaseFile{
		Path:           getFilePath(folderIdxWithGalleryFiles, getGalleryBasename(i)),
		Basename:       getGalleryBasename(i),
		ParentFolderID: folderIDs[folderIdxWithGalleryFiles],
		Fingerprints: []models.Fingerprint{
			{
				Type:        models.FingerprintTypeMD5,
				Fingerprint: getGalleryStringValue(i, checksumField),
			},
		},
	}
}

func makeGallery(i int, includeScenes bool) *models.Gallery {
	var studioID *int
	if _, ok := galleryStudios[i]; ok {
		v := studioIDs[galleryStudios[i]]
		studioID = &v
	}

	pids := indexesToIDs(performerIDs, galleryPerformers[i])
	tids := indexesToIDs(tagIDs, galleryTags[i])

	ret := &models.Gallery{
		Title: getGalleryStringValue(i, titleField),
		URLs: models.NewRelatedStrings([]string{
			getGalleryEmptyString(i, urlField),
		}),
		Rating:       getIntPtr(getRating(i)),
		Date:         getObjectDate(i),
		StudioID:     studioID,
		PerformerIDs: models.NewRelatedIDs(pids),
		TagIDs:       models.NewRelatedIDs(tids),
	}

	if includeScenes {
		ret.SceneIDs = models.NewRelatedIDs(indexesToIDs(sceneIDs, sceneGalleries.reverseLookup(i)))
	}

	return ret
}

func getGalleryCustomFields(index int) map[string]interface{} {
	if index%5 == 0 {
		return nil
	}

	return map[string]interface{}{
		"string": getGalleryStringValue(index, "custom"),
		"int":    int64(index % 5),
		"real":   float64(index) / 10,
	}
}

func createGalleries(ctx context.Context, n int) error {
	gqb := db.Gallery
	fqb := db.File

	for i := 0; i < n; i++ {
		var fileIDs []models.FileID
		if i != galleryIdxWithoutFile {
			f := makeGalleryFile(i)
			if err := fqb.Create(ctx, f); err != nil {
				return fmt.Errorf("creating gallery file: %w", err)
			}
			galleryFileIDs = append(galleryFileIDs, f.ID)
			fileIDs = []models.FileID{f.ID}
		} else {
			galleryFileIDs = append(galleryFileIDs, 0)
		}

		// gallery relationship will be created with galleries
		const includeScenes = false
		gallery := makeGallery(i, includeScenes)

		err := gqb.Create(ctx, &models.CreateGalleryInput{
			Gallery:      gallery,
			FileIDs:      fileIDs,
			CustomFields: getGalleryCustomFields(i),
		})

		if err != nil {
			return fmt.Errorf("Error creating gallery %v+: %s", gallery, err.Error())
		}

		galleryIDs = append(galleryIDs, gallery.ID)
	}

	return nil
}

func getGroupStringValue(index int, field string) string {
	return getPrefixedStringValue("group", index, field)
}

func getGroupNullStringValue(index int, field string) string {
	ret := getPrefixedNullStringValue("group", index, field)

	return ret.String
}

func getGroupEmptyString(index int, field string) string {
	v := getPrefixedNullStringValue("group", index, field)
	if !v.Valid {
		return ""
	}

	return v.String
}

func getGroupCustomFields(index int) map[string]interface{} {
	if index%5 == 0 {
		return nil
	}

	return map[string]interface{}{
		"string": getGroupStringValue(index, "custom"),
		"int":    int64(index % 5),
		"real":   float64(index) / 10,
	}
}

// createGroups creates n groups with plain Name and o groups with camel cased NaMe included
func createGroups(ctx context.Context, mqb models.GroupReaderWriter, n int, o int) error {
	const namePlain = "Name"
	const nameNoCase = "NaMe"

	for i := 0; i < n+o; i++ {
		index := i
		name := namePlain

		tids := indexesToIDs(tagIDs, groupTags[i])

		if i >= n { // i<n tags get normal names
			name = nameNoCase       // i>=n groups get dup names if case is not checked
			index = n + o - (i + 1) // for the name to be the same the number (index) must be the same also
		} // so count backwards to 0 as needed
		// groups [ i ] and [ n + o - i - 1  ] should have similar names with only the Name!=NaMe part different

		name = getGroupStringValue(index, name)
		group := models.Group{
			Name: name,
			URLs: models.NewRelatedStrings([]string{
				getGroupEmptyString(i, urlField),
			}),
			TagIDs: models.NewRelatedIDs(tids),
		}

		err := mqb.Create(ctx, &group)

		if err != nil {
			return fmt.Errorf("Error creating group [%d] %v+: %s", i, group, err.Error())
		}

		customFields := getGroupCustomFields(i)
		if customFields != nil {
			if err := mqb.SetCustomFields(ctx, group.ID, models.CustomFieldsInput{Full: customFields}); err != nil {
				return fmt.Errorf("Error setting custom fields for group %d: %s", group.ID, err.Error())
			}
		}

		groupIDs = append(groupIDs, group.ID)
		groupNames = append(groupNames, group.Name)
	}

	return nil
}

func getPerformerStringValue(index int, field string) string {
	return getPrefixedStringValue("performer", index, field)
}

func getPerformerNullStringValue(index int, field string) string {
	ret := getPrefixedNullStringValue("performer", index, field)

	return ret.String
}

func getPerformerEmptyString(index int, field string) string {
	v := getPrefixedNullStringValue("performer", index, field)
	if !v.Valid {
		return ""
	}

	return v.String
}

func getPerformerBoolValue(index int) bool {
	index = index % 2
	return index == 1
}

func getPerformerBirthdate(index int) *models.Date {
	const minAge = 18
	birthdate := time.Now()
	birthdate = birthdate.AddDate(-minAge-index, -1, -1)

	ret := models.Date{
		Time: birthdate,
	}
	return &ret
}

func getPerformerDeathDate(index int) *models.Date {
	if index != 5 {
		return nil
	}

	deathDate := time.Now()
	deathDate = deathDate.AddDate(-index+1, -1, -1)

	ret := models.Date{
		Time: deathDate,
	}
	return &ret
}

func getPerformerCareerStart(index int) *models.Date {
	if index%5 == 0 {
		return nil
	}

	date := models.DateFromYear(2000 + index)
	return &date
}

func getPerformerCareerEnd(index int) *models.Date {
	if index%5 == 0 {
		return nil
	}

	// only set career_end for even indices
	if index%2 == 0 {
		date := models.DateFromYear(2010 + index)
		return &date
	}
	return nil
}

func getPerformerPenisLength(index int) *float64 {
	if index%5 == 0 {
		return nil
	}

	ret := float64(index)
	return &ret
}

func getPerformerCircumcised(index int) *models.CircumcisedEnum {
	var ret models.CircumcisedEnum
	switch {
	case index%3 == 0:
		return nil
	case index%3 == 1:
		ret = models.CircumcisedEnumCut
	default:
		ret = models.CircumcisedEnumUncut
	}

	return &ret
}

func getIgnoreAutoTag(index int) bool {
	return index%5 == 0
}

func performerStashID(i int) models.StashID {
	return models.StashID{
		StashID:  getPerformerStringValue(i, "stashid"),
		Endpoint: getPerformerStringValue(0, "endpoint"),
	}
}

func performerAliases(i int) []string {
	if i%5 == 0 {
		return []string{}
	}

	return []string{getPerformerStringValue(i, "alias")}
}

func getPerformerCustomFields(index int) map[string]interface{} {
	if index%5 == 0 {
		return nil
	}

	return map[string]interface{}{
		"string": getPerformerStringValue(index, "custom"),
		"int":    int64(index % 5),
		"real":   float64(index) / 10,
	}
}

// createPerformers creates n performers with plain Name and o performers with camel cased NaMe included
func createPerformers(ctx context.Context, n int, o int) error {
	pqb := db.Performer

	const namePlain = "Name"
	const nameNoCase = "NaMe"

	name := namePlain

	for i := 0; i < n+o; i++ {
		index := i

		if i >= n { // i<n tags get normal names
			name = nameNoCase       // i>=n performers get dup names if case is not checked
			index = n + o - (i + 1) // for the name to be the same the number (index) must be the same also
		} // so count backwards to 0 as needed
		// performers [ i ] and [ n + o - i - 1  ] should have similar names with only the Name!=NaMe part different

		tids := indexesToIDs(tagIDs, performerTags[i])

		performer := models.Performer{
			Name:           getPerformerStringValue(index, name),
			Disambiguation: getPerformerStringValue(index, "disambiguation"),
			Aliases:        models.NewRelatedStrings(performerAliases(index)),
			URLs: models.NewRelatedStrings([]string{
				getPerformerEmptyString(i, urlField),
			}),
			Favorite:      getPerformerBoolValue(i),
			Birthdate:     getPerformerBirthdate(i),
			DeathDate:     getPerformerDeathDate(i),
			Details:       getPerformerStringValue(i, "Details"),
			Ethnicity:     getPerformerStringValue(i, "Ethnicity"),
			PenisLength:   getPerformerPenisLength(i),
			Circumcised:   getPerformerCircumcised(i),
			Rating:        getIntPtr(getRating(i)),
			IgnoreAutoTag: getIgnoreAutoTag(i),
			TagIDs:        models.NewRelatedIDs(tids),
		}

		performer.CareerStart = getPerformerCareerStart(i)
		performer.CareerEnd = getPerformerCareerEnd(i)

		if (index+1)%5 != 0 {
			performer.StashIDs = models.NewRelatedStashIDs([]models.StashID{
				performerStashID(i),
			})
		}

		err := pqb.Create(ctx, &models.CreatePerformerInput{
			Performer:    &performer,
			CustomFields: getPerformerCustomFields(i),
		})

		if err != nil {
			return fmt.Errorf("Error creating performer %v+: %s", performer, err.Error())
		}

		performerIDs = append(performerIDs, performer.ID)
		performerNames = append(performerNames, performer.Name)
	}

	return nil
}
func getTagBoolValue(index int) bool {
	index = index % 2
	return index == 1
}
func getTagStringValue(index int, field string) string {
	return "tag_" + strconv.FormatInt(int64(index), 10) + "_" + field
}

func getTagSceneCount(id int) int {
	idx := indexFromID(tagIDs, id)
	return len(sceneTags.reverseLookup(idx))
}

func getTagMarkerCount(id int) int {
	count := 0
	idx := indexFromID(tagIDs, id)
	for _, s := range markerSpecs {
		if s.primaryTagIdx == idx || slices.Contains(s.tagIdxs, idx) {
			count++
		}
	}

	return count
}

func getTagImageCount(id int) int {
	idx := indexFromID(tagIDs, id)
	return len(imageTags.reverseLookup(idx))
}

func getTagGalleryCount(id int) int {
	idx := indexFromID(tagIDs, id)
	return len(galleryTags.reverseLookup(idx))
}

func getTagPerformerCount(id int) int {
	idx := indexFromID(tagIDs, id)
	return len(performerTags.reverseLookup(idx))
}

func getTagStudioCount(id int) int {
	idx := indexFromID(tagIDs, id)
	return len(studioTags.reverseLookup(idx))
}

func getTagParentCount(id int) int {
	if id == tagIDs[tagIdxWithParentTag] || id == tagIDs[tagIdxWithGrandParent] || id == tagIDs[tagIdxWithParentAndChild] {
		return 1
	}

	return 0
}

func getTagChildCount(id int) int {
	if id == tagIDs[tagIdxWithChildTag] || id == tagIDs[tagIdxWithGrandChild] || id == tagIDs[tagIdxWithParentAndChild] {
		return 1
	}

	return 0
}

func tagStashID(i int) models.StashID {
	return models.StashID{
		StashID:  getTagStringValue(i, "stashid"),
		Endpoint: getTagStringValue(0, "endpoint"),
	}
}

func getTagCustomFields(index int) map[string]interface{} {
	if index%5 == 0 {
		return nil
	}

	return map[string]interface{}{
		"string": getTagStringValue(index, "custom"),
		"int":    int64(index % 5),
		"real":   float64(index) / 10,
	}
}

// createTags creates n tags with plain Name and o tags with camel cased NaMe included
func createTags(ctx context.Context, tqb models.TagReaderWriter, n int, o int) error {
	const namePlain = "Name"
	const nameNoCase = "NaMe"

	name := namePlain

	for i := 0; i < n+o; i++ {
		index := i

		if i >= n { // i<n tags get normal names
			name = nameNoCase       // i>=n tags get dup names if case is not checked
			index = n + o - (i + 1) // for the name to be the same the number (index) must be the same also
		} // so count backwards to 0 as needed
		// tags [ i ] and [ n + o - i - 1  ] should have similar names with only the Name!=NaMe part different

		tag := models.Tag{
			Name:          getTagStringValue(index, name),
			IgnoreAutoTag: getIgnoreAutoTag(i),
		}

		if (index+1)%5 != 0 {
			tag.StashIDs = models.NewRelatedStashIDs([]models.StashID{
				tagStashID(i),
			})
		}

		err := tqb.Create(ctx, &models.CreateTagInput{
			Tag:          &tag,
			CustomFields: getTagCustomFields(i),
		})

		if err != nil {
			return fmt.Errorf("Error creating tag %v+: %s", tag, err.Error())
		}

		// add alias
		alias := getTagStringValue(i, "Alias")
		if err := tqb.UpdateAliases(ctx, tag.ID, []string{alias}); err != nil {
			return fmt.Errorf("error setting tag alias: %s", err.Error())
		}

		tagIDs = append(tagIDs, tag.ID)
		tagNames = append(tagNames, tag.Name)
	}

	return nil
}

func getStudioStringValue(index int, field string) string {
	return getPrefixedStringValue("studio", index, field)
}

func getStudioNullStringValue(index int, field string) string {
	ret := getPrefixedNullStringValue("studio", index, field)

	return ret.String
}

func getStudioCustomFields(index int) map[string]interface{} {
	if index%5 == 0 {
		return nil
	}

	return map[string]interface{}{
		"string": getStudioStringValue(index, "custom"),
		"int":    int64(index % 5),
		"real":   float64(index) / 10,
	}
}

func createStudio(ctx context.Context, sqb *sqlite.StudioStore, name string, parentID *int, customFields map[string]interface{}) (*models.Studio, error) {
	studio := models.Studio{
		Name: name,
	}

	if parentID != nil {
		studio.ParentID = parentID
	}

	err := createStudioFromModel(ctx, sqb, &studio, customFields)
	if err != nil {
		return nil, err
	}

	return &studio, nil
}

func createStudioFromModel(ctx context.Context, sqb *sqlite.StudioStore, studio *models.Studio, customFields map[string]interface{}) error {
	err := sqb.Create(ctx, &models.CreateStudioInput{
		Studio:       studio,
		CustomFields: customFields,
	})

	if err != nil {
		return fmt.Errorf("Error creating studio %v+: %s", studio, err.Error())
	}

	return nil
}

func getStudioBoolValue(index int) bool {
	index = index % 2
	return index == 1
}

func getStudioEmptyString(index int, field string) string {
	v := getPrefixedNullStringValue("studio", index, field)
	if !v.Valid {
		return ""
	}

	return v.String
}

func getStudioStringList(index int, field string) []string {
	v := getStudioEmptyString(index, field)
	if v == "" {
		return []string{}
	}

	return []string{v}
}

// createStudios creates n studios with plain Name and o studios with camel cased NaMe included
func createStudios(ctx context.Context, n int, o int) error {
	sqb := db.Studio
	const namePlain = "Name"
	const nameNoCase = "NaMe"

	for i := 0; i < n+o; i++ {
		index := i
		name := namePlain

		if i >= n { // i<n studios get normal names
			name = nameNoCase       // i>=n studios get dup names if case is not checked
			index = n + o - (i + 1) // for the name to be the same the number (index) must be the same also
		} // so count backwards to 0 as needed
		// studios [ i ] and [ n + o - i - 1  ] should have similar names with only the Name!=NaMe part different

		name = getStudioStringValue(index, name)
		tids := indexesToIDs(tagIDs, studioTags[i])
		studio := models.Studio{
			Name:          name,
			URLs:          models.NewRelatedStrings(getStudioStringList(i, urlField)),
			Favorite:      getStudioBoolValue(index),
			IgnoreAutoTag: getIgnoreAutoTag(i),
			TagIDs:        models.NewRelatedIDs(tids),
		}
		// only add aliases for some scenes
		if i == studioIdxWithGroup || i%5 == 0 {
			alias := getStudioStringValue(i, "Alias")
			studio.Aliases = models.NewRelatedStrings([]string{alias})
		}
		err := createStudioFromModel(ctx, sqb, &studio, getStudioCustomFields(i))

		if err != nil {
			return err
		}

		studioIDs = append(studioIDs, studio.ID)
		studioNames = append(studioNames, studio.Name)
	}

	return nil
}

func getMarkerEndSeconds(index int) *float64 {
	if index != markerIdxWithDuration && index != markerIdx2WithDuration {
		return nil
	}
	ret := float64(index)
	return &ret
}

func createMarker(ctx context.Context, mqb models.SceneMarkerReaderWriter, markerSpec markerSpec) error {
	markerIdx := len(markerIDs)
	marker := models.SceneMarker{
		SceneID:      sceneIDs[markerSpec.sceneIdx],
		PrimaryTagID: tagIDs[markerSpec.primaryTagIdx],
		EndSeconds:   getMarkerEndSeconds(markerIdx),
	}

	err := mqb.Create(ctx, &marker)

	if err != nil {
		return fmt.Errorf("error creating marker %v+: %w", marker, err)
	}

	markerIDs = append(markerIDs, marker.ID)

	if len(markerSpec.tagIdxs) > 0 {
		newTagIDs := []int{}

		for _, tagIdx := range markerSpec.tagIdxs {
			newTagIDs = append(newTagIDs, tagIDs[tagIdx])
		}

		if err := mqb.UpdateTags(ctx, marker.ID, newTagIDs); err != nil {
			return fmt.Errorf("error creating marker/tag join: %w", err)
		}
	}

	return nil
}

func createChapter(ctx context.Context, mqb models.GalleryChapterReaderWriter, chapterSpec chapterSpec) error {
	chapter := models.GalleryChapter{
		GalleryID:  sceneIDs[chapterSpec.galleryIdx],
		Title:      chapterSpec.title,
		ImageIndex: chapterSpec.imageIndex,
	}

	err := mqb.Create(ctx, &chapter)

	if err != nil {
		return fmt.Errorf("error creating chapter %v+: %w", chapter, err)
	}

	chapterIDs = append(chapterIDs, chapter.ID)

	return nil
}

func getSavedFilterMode(index int) models.FilterMode {
	switch index {
	case savedFilterIdxScene:
		return models.FilterModeScenes
	case savedFilterIdxImage:
		return models.FilterModeImages
	default:
		return models.FilterModeScenes
	}
}

func getSavedFilterName(index int) string {
	if index <= savedFilterIdxImage {
		// use the same name for the first two - should be possible
		return firstSavedFilterName
	}

	return getPrefixedStringValue("savedFilter", index, "Name")
}

func createSavedFilters(ctx context.Context, qb models.SavedFilterReaderWriter, n int) error {
	for i := 0; i < n; i++ {
		filterQ := ""
		filterPage := i
		filterPerPage := i * 40
		filterSort := "date"
		filterDirection := models.SortDirectionEnumAsc
		findFilter := models.FindFilterType{
			Q:         &filterQ,
			Page:      &filterPage,
			PerPage:   &filterPerPage,
			Sort:      &filterSort,
			Direction: &filterDirection,
		}
		savedFilter := models.SavedFilter{
			Mode:       getSavedFilterMode(i),
			Name:       getSavedFilterName(i),
			FindFilter: &findFilter,
			ObjectFilter: map[string]interface{}{
				"test": "object",
			},
			UIOptions: map[string]interface{}{
				"display_mode": 1,
				"zoom_index":   1,
			},
		}

		err := qb.Create(ctx, &savedFilter)

		if err != nil {
			return fmt.Errorf("Error creating saved filter %v+: %s", savedFilter, err.Error())
		}

		savedFilterIDs = append(savedFilterIDs, savedFilter.ID)
	}

	return nil
}

func doLinks(links [][2]int, fn func(idx1, idx2 int) error) error {
	for _, l := range links {
		if err := fn(l[0], l[1]); err != nil {
			return err
		}
	}

	return nil
}

func linkGroupStudios(ctx context.Context, mqb models.GroupWriter) error {
	return doLinks(groupStudioLinks, func(groupIndex, studioIndex int) error {
		group := models.GroupPartial{
			StudioID: models.NewOptionalInt(studioIDs[studioIndex]),
		}
		_, err := mqb.UpdatePartial(ctx, groupIDs[groupIndex], group)

		return err
	})
}

func linkStudiosParent(ctx context.Context) error {
	qb := db.Studio
	return doLinks(studioParentLinks, func(parentIndex, childIndex int) error {
		input := &models.StudioPartial{
			ID:       studioIDs[childIndex],
			ParentID: models.NewOptionalInt(studioIDs[parentIndex]),
		}
		_, err := qb.UpdatePartial(ctx, *input)

		return err
	})
}

func linkTagsParent(ctx context.Context, qb models.TagReaderWriter) error {
	return doLinks(tagParentLinks, func(parentIndex, childIndex int) error {
		tagID := tagIDs[childIndex]
		parentTags, err := qb.FindByChildTagID(ctx, tagID)
		if err != nil {
			return err
		}

		var parentIDs []int
		for _, parentTag := range parentTags {
			parentIDs = append(parentIDs, parentTag.ID)
		}

		parentIDs = append(parentIDs, tagIDs[parentIndex])

		return qb.UpdateParentTags(ctx, tagID, parentIDs)
	})
}

func linkGroupsParent(ctx context.Context, qb models.GroupReaderWriter) error {
	return doLinks(groupParentLinks, func(parentIndex, childIndex int) error {
		groupID := groupIDs[childIndex]

		p := models.GroupPartial{
			ContainingGroups: &models.UpdateGroupDescriptions{
				Groups: []models.GroupIDDescription{
					{GroupID: groupIDs[parentIndex]},
				},
				Mode: models.RelationshipUpdateModeAdd,
			},
		}

		_, err := qb.UpdatePartial(ctx, groupID, p)
		return err
	})
}

func addTagImage(ctx context.Context, qb models.TagWriter, tagIndex int) error {
	return qb.UpdateImage(ctx, tagIDs[tagIndex], []byte("image"))
}
