package db_test

import (
	"context"
	"database/sql"
	"math/rand"
	"os"
	"path/filepath"
	"time"

	"github.com/navidrome/navidrome/conf"
	"github.com/navidrome/navidrome/conf/configtest"
	. "github.com/navidrome/navidrome/db"
	"github.com/navidrome/navidrome/tests"
	"github.com/navidrome/navidrome/utils/singleton"
	. "github.com/onsi/ginkgo/v2"
	. "github.com/onsi/gomega"
)

func shortTime(year int, month time.Month, day, hour, minute int) time.Time {
	return time.Date(year, month, day, hour, minute, 0, 0, time.UTC)
}

const (
	gooseTableDDL      = "CREATE TABLE goose_db_version(version_id, is_applied)"
	navidromeTablesDDL = "CREATE TABLE user(id); CREATE TABLE media_file(id)"
)

func sqliteFile(statements string) func(path string) {
	return func(path string) {
		GinkgoHelper()
		other, err := sql.Open(Driver, path)
		Expect(err).ToNot(HaveOccurred())
		defer other.Close()
		_, err = other.Exec(statements)
		Expect(err).ToNot(HaveOccurred())
	}
}

var _ = Describe("database backups", func() {
	When("there are a few backup files", func() {
		var ctx context.Context
		var timesShuffled []time.Time

		timesDecreasingChronologically := []time.Time{
			shortTime(2024, 11, 6, 5, 11),
			shortTime(2024, 11, 6, 5, 8),
			shortTime(2024, 11, 6, 4, 32),
			shortTime(2024, 11, 6, 2, 4),
			shortTime(2024, 11, 6, 1, 52),

			shortTime(2024, 11, 5, 23, 0),
			shortTime(2024, 11, 5, 6, 4),
			shortTime(2024, 11, 4, 2, 4),
			shortTime(2024, 11, 3, 8, 5),
			shortTime(2024, 11, 2, 5, 24),
			shortTime(2024, 11, 1, 5, 24),

			shortTime(2024, 10, 31, 5, 9),
			shortTime(2024, 10, 30, 5, 9),
			shortTime(2024, 10, 23, 14, 3),
			shortTime(2024, 10, 22, 3, 6),
			shortTime(2024, 10, 11, 14, 3),

			shortTime(2024, 9, 21, 19, 5),
			shortTime(2024, 9, 3, 8, 5),

			shortTime(2024, 7, 5, 1, 1),

			shortTime(2023, 8, 2, 19, 5),

			shortTime(2021, 8, 2, 19, 5),
			shortTime(2020, 8, 2, 19, 5),
		}

		BeforeEach(func() {
			DeferCleanup(configtest.SetupConfig())

			tempFolder, err := os.MkdirTemp("", "navidrome_backup")
			Expect(err).ToNot(HaveOccurred())
			conf.Server.Backup.Path = conf.NewDir(tempFolder)

			DeferCleanup(func() {
				_ = os.RemoveAll(tempFolder)
			})

			timesShuffled = make([]time.Time, len(timesDecreasingChronologically))
			copy(timesShuffled, timesDecreasingChronologically)
			//nolint:gosec // shuffle order is not a security decision
			rand.Shuffle(len(timesShuffled), func(i, j int) {
				timesShuffled[i], timesShuffled[j] = timesShuffled[j], timesShuffled[i]
			})

			for _, time := range timesShuffled {
				path := BackupPath(time)
				file, err := os.Create(path)
				Expect(err).ToNot(HaveOccurred())
				_ = file.Close()
			}

			ctx = context.Background()
		})

		DescribeTable("prune", func(count, expected int) {
			conf.Server.Backup.Count = count
			pruneCount, err := Prune(ctx)
			Expect(err).ToNot(HaveOccurred())
			for idx, time := range timesDecreasingChronologically {
				_, err := os.Stat(BackupPath(time))
				shouldExist := idx < conf.Server.Backup.Count
				if shouldExist {
					Expect(err).ToNot(HaveOccurred())
				} else {
					Expect(err).To(MatchError(os.ErrNotExist))
				}
			}

			Expect(len(timesDecreasingChronologically) - pruneCount).To(Equal(expected))
		},
			Entry("preserve latest 5 backups", 5, 5),
			Entry("delete all files", 0, 0),
			Entry("preserve all files when at length", len(timesDecreasingChronologically), len(timesDecreasingChronologically)),
			Entry("preserve all files when less than count", 10000, len(timesDecreasingChronologically)))

		It("ignores SQLite sidecar files when counting backups", func() {
			for _, suffix := range []string{"-shm", "-wal"} {
				file, err := os.Create(BackupPath(timesDecreasingChronologically[0]) + suffix)
				Expect(err).ToNot(HaveOccurred())
				_ = file.Close()
			}

			conf.Server.Backup.Count = len(timesDecreasingChronologically)
			pruneCount, err := Prune(ctx)
			Expect(err).ToNot(HaveOccurred())
			Expect(pruneCount).To(BeZero())
		})
	})

	Describe("backup and restore", Ordered, func() {
		var ctx context.Context

		BeforeAll(func() {
			ctx = context.Background()
			DeferCleanup(configtest.SetupConfig())

			conf.Server.DbPath = "file::memory:?cache=shared&_foreign_keys=on"
			DeferCleanup(Init(ctx))
		})

		BeforeEach(func() {
			tempFolder, err := os.MkdirTemp("", "navidrome_backup")
			Expect(err).ToNot(HaveOccurred())
			conf.Server.Backup.Path = conf.NewDir(tempFolder)

			DeferCleanup(func() {
				_ = os.RemoveAll(tempFolder)
			})
		})

		It("successfully backups the database", func() {
			path, err := Backup(ctx)
			Expect(err).ToNot(HaveOccurred())

			backup, err := sql.Open(Driver, path)
			Expect(err).ToNot(HaveOccurred())
			Expect(IsSchemaEmpty(ctx, backup)).To(BeFalse())
		})

		It("successfully restores the database", func() {
			path, err := Backup(ctx)
			Expect(err).ToNot(HaveOccurred())

			err = tests.ClearDB()
			Expect(err).ToNot(HaveOccurred())
			Expect(IsSchemaEmpty(ctx, Db())).To(BeTrue())

			err = Restore(ctx, path, false)
			Expect(err).ToNot(HaveOccurred())
			Expect(IsSchemaEmpty(ctx, Db())).To(BeFalse())
		})
	})

	Describe("backup and restore with a file-based database", Ordered, func() {
		var ctx context.Context
		var tempFolder string
		var dbFilePath string

		BeforeAll(func() {
			ctx = context.Background()
			DeferCleanup(configtest.SetupConfig())

			var err error
			tempFolder, err = os.MkdirTemp("", "navidrome_restore")
			Expect(err).ToNot(HaveOccurred())
			conf.Server.Backup.Path = conf.NewDir(tempFolder)
			DeferCleanup(func() {
				Close(ctx)
				_ = os.RemoveAll(tempFolder)
			})

			// Mimic the production DSN (consts.DefaultDbPath): a file database in WAL mode.
			dbFilePath = filepath.Join(tempFolder, "navidrome.db")
			conf.Server.DbPath = dbFilePath + "?_busy_timeout=15000&_journal_mode=WAL&_foreign_keys=on&synchronous=normal"
			// The previous container's cleanup closed the shared *sql.DB without
			// dropping the singleton; force a fresh connection for this container.
			singleton.DeleteInstance[*sql.DB]()
			DeferCleanup(Init(ctx))
		})

		It("restores data into a database whose stale WAL sidecar files were left behind", func() {
			By("seeding user data in the current database")
			_, err := Db().ExecContext(ctx, `INSERT INTO user (id, user_name, name, email, password, is_admin, created_at, updated_at)
				VALUES ('u-restore-1', 'drilladmin', 'drilladmin', 'drilladmin@example.com', 'x', 1, datetime('now'), datetime('now'))`)
			Expect(err).ToNot(HaveOccurred())

			By("creating a backup containing the user row")
			path, err := Backup(ctx)
			Expect(err).ToNot(HaveOccurred())

			By("simulating the CLI exiting without closing the pool: sidecar files stay behind")
			_, err = Db().ExecContext(ctx, "CREATE TABLE IF NOT EXISTS _restore_probe(x)")
			Expect(err).ToNot(HaveOccurred())
			singleton.DeleteInstance[*sql.DB]()

			err = tests.ClearDB()
			Expect(err).ToNot(HaveOccurred())

			By("restoring the backup")
			Expect(Restore(ctx, path, false)).To(Succeed())

			By("verifying the restored data is readable through a fresh connection")
			singleton.DeleteInstance[*sql.DB]()
			var userName string
			Expect(Db().QueryRowContext(ctx, "SELECT user_name FROM user WHERE id = 'u-restore-1'").Scan(&userName)).To(Succeed())
			Expect(userName).To(Equal("drilladmin"))
		})

		expectRestoreRefused := func(path, expectedErr string) {
			GinkgoHelper()
			By("seeding user data in the current database")
			_, err := Db().ExecContext(ctx, `INSERT OR REPLACE INTO user (id, user_name, name, email, password, is_admin, created_at, updated_at)
				VALUES ('u-restore-2', 'keepme', 'keepme', 'keepme@example.com', 'x', 1, datetime('now'), datetime('now'))`)
			Expect(err).ToNot(HaveOccurred())

			By("attempting the restore")
			Expect(Restore(ctx, path, false)).To(MatchError(ContainSubstring(expectedErr)))

			By("verifying the database was not wiped")
			var userName string
			Expect(Db().QueryRowContext(ctx, "SELECT user_name FROM user WHERE id = 'u-restore-2'").Scan(&userName)).To(Succeed())
			Expect(userName).To(Equal("keepme"))
		}

		It("fails to restore from a backup file that does not exist, leaving the database intact", func() {
			missingPath := filepath.Join(tempFolder, "does_not_exist.db")
			expectRestoreRefused(missingPath, "backup file not available")
			_, statErr := os.Stat(missingPath)
			Expect(statErr).To(MatchError(os.ErrNotExist))
		})

		DescribeTable("fails to restore from a file that is not a Navidrome database, leaving the database intact",
			func(createFile func(path string), expectedErr string) {
				invalidPath := filepath.Join(GinkgoT().TempDir(), "invalid_backup.db")
				createFile(invalidPath)
				expectRestoreRefused(invalidPath, expectedErr)
			},
			Entry("empty file", func(path string) {
				Expect(os.WriteFile(path, nil, 0600)).To(Succeed())
			}, "is not a Navidrome database"),
			Entry("file that is not a SQLite database", func(path string) {
				Expect(os.WriteFile(path, []byte("this is not a database, just some text"), 0600)).To(Succeed())
			}, "reading backup file"),
			Entry("SQLite database without the Navidrome schema",
				sqliteFile("CREATE TABLE something_else(x)"), "is not a Navidrome database"),
			Entry("another project's goose database",
				sqliteFile(gooseTableDDL+"; INSERT INTO goose_db_version VALUES (1, 1)"), "is not a Navidrome database"),
			Entry("goose database with only one of the Navidrome tables",
				sqliteFile(gooseTableDDL+"; INSERT INTO goose_db_version VALUES (1, 1); CREATE TABLE media_file(id)"), "is not a Navidrome database"),
			Entry("SQLite database with an empty goose_db_version table",
				sqliteFile(gooseTableDDL+"; "+navidromeTablesDDL), "has no applied migrations"),
			Entry("SQLite database with only goose's initial version row",
				sqliteFile(gooseTableDDL+"; "+navidromeTablesDDL+"; INSERT INTO goose_db_version VALUES (0, 1)"), "has no applied migrations"),
			Entry("SQLite database whose migrations were all rolled back",
				sqliteFile(gooseTableDDL+"; "+navidromeTablesDDL+"; INSERT INTO goose_db_version VALUES (1, 0)"), "has no applied migrations"),
			Entry("backup with garbage over its last pages", func(path string) {
				// Filler rows go to the end of the file, so the damage below stays clear of
				// the schema and of goose_db_version, which still read fine.
				_, err := Db().ExecContext(ctx, "CREATE TABLE _restore_filler AS SELECT zeroblob(1000000) AS x")
				Expect(err).ToNot(HaveOccurred())
				backupFile, err := Backup(ctx)
				Expect(err).ToNot(HaveOccurred())
				data, err := os.ReadFile(backupFile)
				Expect(err).ToNot(HaveOccurred())

				for i := len(data) * 4 / 5; i < len(data); i++ {
					data[i] = 0xFF
				}
				Expect(os.WriteFile(path, data, 0600)).To(Succeed())
			}, "is damaged"),
		)

		It("restores a good backup with the integrity check", func() {
			path, err := Backup(ctx)
			Expect(err).ToNot(HaveOccurred())
			Expect(Restore(ctx, path, true)).To(Succeed())
		})

		It("refuses an index that doesn't match its table only with the integrity check", func() {
			path, err := Backup(ctx)
			Expect(err).ToNot(HaveOccurred())
			writeMismatchedIndex(ctx, path, 1)
			restored := func() int {
				var count int
				Expect(Db().QueryRowContext(ctx, "SELECT count(*) FROM sqlite_master WHERE name = 't'").Scan(&count)).To(Succeed())
				return count
			}

			Expect(Restore(ctx, path, true)).To(MatchError(ContainSubstring("is damaged")))
			Expect(restored()).To(BeZero())

			Expect(Restore(ctx, path, false)).To(Succeed())
			Expect(restored()).To(Equal(1))
		})
	})
})