package libmkwebpage

import (
	"database/sql"
	"embed"
	"errors"
	"fmt"
	"io"
	"io/fs"
	"os"
	"path"
	"slices"
	"strconv"
	"strings"
)

type migrations struct {
	staticRoot  embed.FS
	dynamicRoot fs.FS
}

func (w *Webpage) NewMigrations(static embed.FS, dynamic fs.FS) *Webpage {
	w.migrations = migrations{staticRoot: static, dynamicRoot: dynamic}
	return w
}
func (w *Webpage) getMigrationRoot() fs.FS {
	if w.isDynamic() {
		return w.migrations.dynamicRoot
	}
	return w.migrations.staticRoot
}

func (w *Webpage) ConnectDb() (*sql.DB, error) {
	db, err := sql.Open("sqlite3", "./test.db")
	if err != nil {
		return nil, err
	}

	var dir fs.ReadDirFile
	potenriallyDir, err := w.getMigrationRoot().Open("db")
	if err != nil {
		return nil, err
	}
	if foo, ok := potenriallyDir.(fs.ReadDirFile); !ok {
		dir = foo
	} else {
		return nil, errors.New("db directory is not a directory")
	}
	files, err := dir.ReadDir(0)
	if err != nil {
		return nil, err
	}

	err = db.Ping()
	if err != nil {
		return nil, err
	}

	tx, err := db.Begin()
	if err != nil {
		return nil, err
	}
	defer tx.Rollback()

	var migration_level int
	rows := tx.QueryRow("SELECT level FROM __MIGRATIONLEVEL")
	fmt.Fprintln(os.Stderr, *rows)
	if rows.Scan(&migration_level) != nil {
		fmt.Fprintln(os.Stderr, "Creating Migration-Metadata")
		_, err := tx.Exec("CREATE TABLE __MIGRATIONLEVEL (level INTEGER PRIMARY KEY)")
		if err != nil {
			return nil, err
		}
		_, err = tx.Exec("INSERT INTO __MIGRATIONLEVEL(level) VALUES (0)")
		if err != nil {
			return nil, err
		}
		migration_level = 0
	}
	slices.SortFunc(files, func(a, b fs.DirEntry) int {
		if a.Name() < b.Name() {
			return -1
		}
		if a.Name() > b.Name() {
			return 1
		}
		return 0
	})
	for _, file := range files {
		if !file.Type().IsRegular() {
			continue
		}
		id, err := strconv.Atoi(strings.Split(file.Name(), "-")[0])
		if err != nil {
			return nil, err
		}
		if migration_level < id {
			fmt.Fprintln(os.Stderr, "Running Migration nr", id)
			migration, err := w.getMigrationRoot().Open(path.Join("db", file.Name()))
			if err != nil {
				return nil, err
			}
			migrationContent, err := io.ReadAll(migration)
			if err != nil {
				return nil, err
			}

			_, err = tx.Exec(string(migrationContent))
			if err != nil {
				return nil, err
			}
			_, err = tx.Exec("UPDATE __MIGRATIONLEVEL SET level = ?", id)
			if err != nil {
				return nil, err
			}
		}

	}
	err = tx.Commit()
	if err != nil {
		return nil, err
	}

	return db, nil
}