package database

import (
	"fmt"
	"time"

	"github.com/jmoiron/sqlx"
)

func Initialize(databaseUrl string) (*sqlx.DB, error) {
	db, err := sqlx.Open("sqlite3", databaseUrl)

	if err != nil {
		return nil, fmt.Errorf("failed to open database connection: %w", err)
	}

	_, err = db.Exec(`PRAGMA journal_mode=WAL;`)

	if err != nil {
		return nil, fmt.Errorf("failed to set journal mode: %w", err)
	}

	_, err = db.Exec(`CREATE TABLE IF NOT EXISTS migrations (
		id TEXT NOT NULL,
		run_at INTEGER NOT NULL
	);`)

	if err != nil {
		return nil, fmt.Errorf("failed to initialize migrations table: %w", err)
	}

	pendingMigrations, err := getPendingMigrations(db)

	if err != nil {
		return nil, fmt.Errorf("failed to get pending migrations: %w", err)
	}

	for _, migrationId := range pendingMigrations {
		migrationSource, err := getMigrationContent(migrationId)

		if err != nil {
			return nil, err
		}

		tx, err := db.Begin()

		if err != nil {
			return nil, err
		}

		defer tx.Rollback()

		_, err = tx.Exec(migrationSource)

		if err != nil {
			return nil, fmt.Errorf("failed to run migration %s: %w", migrationId, err)
		}

		_, err = tx.Exec("INSERT INTO migrations (id, run_at) VALUES (?, ?)", migrationId, time.Now().Unix())

		if err != nil {
			return nil, fmt.Errorf("failed to store run migration: %w", err)
		}

		err = tx.Commit()

		if err != nil {
			return nil, err
		}
	}

	return db, nil
}