package lib

import (
	"database/sql"
	"log"
	"os"
	"path/filepath"

	_ "turso.tech/database/tursogo"
)

var DB *sql.DB

// init sqlite db — creates app.db at project root (run from root) or ROOT_DIR if set
func InitDB() {
	var err error

	dbPath := "app.db"
	if rootDir := os.Getenv("ROOT_DIR"); rootDir != "" {
		dbPath = filepath.Join(rootDir, "app.db")
	}

	DB, err = sql.Open("turso", dbPath)
	if err != nil {
		log.Fatal(err)
	}

	if _, err = DB.Exec("PRAGMA foreign_keys = ON;"); err != nil {
		log.Fatal(err)
	}

	// make users table
	userTable := `
	CREATE TABLE IF NOT EXISTS users (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		username TEXT UNIQUE NOT NULL,
		password_hash TEXT NOT NULL
	);`
	_, err = DB.Exec(userTable)
	if err != nil {
		log.Fatal(err)
	}

	// make sessions table
	sessionTable := `
	CREATE TABLE IF NOT EXISTS sessions (
		id TEXT PRIMARY KEY,
		user_id INTEGER NOT NULL,
		expires_at DATETIME NOT NULL,
		FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
	);`
	_, err = DB.Exec(sessionTable)
	if err != nil {
		log.Fatal(err)
	}

	// make rooms table
	roomTable := `
	CREATE TABLE IF NOT EXISTS rooms (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		name TEXT NOT NULL,
		code TEXT UNIQUE NOT NULL,
		scale TEXT NOT NULL DEFAULT 'fibonacci',
		owner_id INTEGER NOT NULL,
		created_at DATETIME NOT NULL,
		active_story_id INTEGER,
		FOREIGN KEY(owner_id) REFERENCES users(id) ON DELETE CASCADE
	);`
	_, err = DB.Exec(roomTable)
	if err != nil {
		log.Fatal(err)
	}

	// make room_members table
	memberTable := `
	CREATE TABLE IF NOT EXISTS room_members (
		room_id INTEGER NOT NULL,
		user_id INTEGER NOT NULL,
		joined_at DATETIME NOT NULL,
		PRIMARY KEY (room_id, user_id),
		FOREIGN KEY(room_id) REFERENCES rooms(id) ON DELETE CASCADE,
		FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
	);`
	_, err = DB.Exec(memberTable)
	if err != nil {
		log.Fatal(err)
	}

	// make stories table
	storyTable := `
	CREATE TABLE IF NOT EXISTS stories (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		room_id INTEGER NOT NULL,
		title TEXT NOT NULL,
		points INTEGER,
		voted INTEGER NOT NULL DEFAULT 0,
		FOREIGN KEY(room_id) REFERENCES rooms(id) ON DELETE CASCADE
	);`
	_, err = DB.Exec(storyTable)
	if err != nil {
		log.Fatal(err)
	}

	// make votes table
	voteTable := `
	CREATE TABLE IF NOT EXISTS votes (
		story_id INTEGER NOT NULL,
		user_id INTEGER NOT NULL,
		value TEXT NOT NULL,
		PRIMARY KEY (story_id, user_id),
		FOREIGN KEY(story_id) REFERENCES stories(id) ON DELETE CASCADE,
		FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
	);`
	_, err = DB.Exec(voteTable)
	if err != nil {
		log.Fatal(err)
	}

	indexes := []string{
		"CREATE INDEX IF NOT EXISTS idx_sessions_user_expires ON sessions(user_id, expires_at);",
		"CREATE INDEX IF NOT EXISTS idx_sessions_expires ON sessions(expires_at);",
		"CREATE INDEX IF NOT EXISTS idx_rooms_owner ON rooms(owner_id);",
		"CREATE INDEX IF NOT EXISTS idx_room_members_room ON room_members(room_id);",
		"CREATE INDEX IF NOT EXISTS idx_room_members_user ON room_members(user_id);",
		"CREATE INDEX IF NOT EXISTS idx_stories_room ON stories(room_id);",
		"CREATE INDEX IF NOT EXISTS idx_votes_story ON votes(story_id);",
		"CREATE INDEX IF NOT EXISTS idx_votes_user ON votes(user_id);",
	}
	for _, stmt := range indexes {
		if _, err = DB.Exec(stmt); err != nil {
			log.Fatal(err)
		}
	}
}