package db

import (
	"context"
	"database/sql"
	"fmt"
	"os"

	"github.com/mattn/go-sqlite3"
)

type (
	DB struct {
		db     *sql.DB
		source string // for backup
	}

	Word struct {
		Word string
		Line string
	}
)

func Open(filename string) (*DB, error) {
	sql.Register("sqlite3_with_extensions", &sqlite3.SQLiteDriver{
		ConnectHook: func(conn *sqlite3.SQLiteConn) error {
			return conn.LoadExtension("ext/libsqlite3ext", "sqlite3_spellfix_init")
		},
	})

	db, err := sql.Open("sqlite3_with_extensions", filename)
	if err != nil {
		return nil, err
	}

	return &DB{
		db:     db,
		source: filename,
	}, nil
}

func (d *DB) Migrate(ctx context.Context) error {
	_, err := d.db.ExecContext(
		ctx,
		`CREATE VIRTUAL TABLE IF NOT EXISTS words USING fts5 (word, line);
		CREATE VIRTUAL TABLE IF NOT EXISTS words_vocab USING fts5vocab('words', 'row');
		CREATE VIRTUAL TABLE IF NOT EXISTS spell USING spellfix1;`,
	)
	return err
}

func (d *DB) SelectDict(ctx context.Context, query string, limit int) ([]*Word, error) {
	rows, err := d.db.QueryContext(
		ctx,
		`SELECT
			word, line
		FROM words
		WHERE word MATCH ?
		ORDER BY rank;`,
		query, limit,
	)
	if err != nil {
		return nil, err
	}

	words := make([]*Word, 0)
	for rows.Next() {
		w := Word{}
		err := rows.Scan(&w.Word, &w.Line)
		if err != nil {
			return nil, err
		}
		words = append(words, &w)
	}

	return words, err

}

func (d *DB) SelectSpell(ctx context.Context, query string) ([]string, error) {
	rows, err := d.db.QueryContext(
		ctx,
		`SELECT
			word
		FROM spell
		WHERE word MATCH ?;`,
		query,
	)
	if err != nil {
		return nil, err
	}

	words := make([]string, 0)
	for rows.Next() {
		w := ""
		err := rows.Scan(&w)
		if err != nil {
			return nil, err
		}
		words = append(words, w)
	}

	return words, nil

}

func (d *DB) InsertLine(ctx context.Context, word, line string) error {
	_, err := d.db.ExecContext(
		ctx,
		`INSERT INTO words (WORD, LINE) VALUES(?, ?);`,
		word, line,
	)
	if err != nil {
		return err
	}
	return err
}

func (d *DB) Consolidade(ctx context.Context) error {
	_, err := d.db.ExecContext(
		ctx,
		`INSERT INTO spell(word) SELECT term FROM words_vocab`,
	)
	if err != nil {
		return err
	}
	return err
}

func (d *DB) Backup(ctx context.Context, name string) error {
	destDb, err := sql.Open("sqlite3_with_extensions", name)
	if err != nil {
		return err
	}
	defer destDb.Close()

	return Copy(ctx, d.db, destDb)
}

func (d *DB) Restore(ctx context.Context, name string) error {
	if _, err := os.Stat(name); err != nil {
		return err
	}

	srcDb, err := sql.Open("sqlite3_with_extensions", name)
	if err != nil {
		return err
	}
	defer srcDb.Close()

	return Copy(ctx, srcDb, d.db)
}

func Copy(ctx context.Context, srcDb *sql.DB, destDb *sql.DB) error {
	destConn, err := destDb.Conn(ctx)
	if err != nil {
		return err
	}
	defer destConn.Close()

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

	return destConn.Raw(func(destConn interface{}) error {
		return srcConn.Raw(func(srcConn interface{}) error {
			destSQLiteConn, ok := destConn.(*sqlite3.SQLiteConn)
			if !ok {
				return fmt.Errorf("can't convert destination connection to SQLiteConn")
			}

			srcSQLiteConn, ok := srcConn.(*sqlite3.SQLiteConn)
			if !ok {
				return fmt.Errorf("can't convert source connection to SQLiteConn")
			}

			b, err := destSQLiteConn.Backup("main", srcSQLiteConn, "main")
			if err != nil {
				return fmt.Errorf("error initializing SQLite backup: %w", err)
			}

			done, err := b.Step(-1)
			if !done {
				return fmt.Errorf("step of -1, but not done")
			}
			if err != nil {
				return fmt.Errorf("error in stepping backup: %w", err)
			}

			err = b.Finish()
			if err != nil {
				return fmt.Errorf("error finishing backup: %w", err)
			}

			return err
		})
	})

}