package database

import (
	"database/sql"
	"strings"

	_ "modernc.org/sqlite"

	"mmgame/internal/config"
)

var DB *sql.DB

func normalizeDSN(url string) string {
	if url == "" {
		return "mmgame.db"
	}
	for _, prefix := range []string{"sqlite+aiosqlite:///", "sqlite:///"} {
		if strings.HasPrefix(url, prefix) {
			path := strings.TrimPrefix(url, prefix)
			if path == ":memory:" {
				return "file::memory:?cache=shared"
			}
			return path
		}
	}
	return url
}

func Init() error {
	dsn := normalizeDSN(config.Cfg.DatabaseURL)
	db, err := sql.Open("sqlite", dsn)
	if err != nil {
		return err
	}
	db.SetMaxOpenConns(1)
	if err := db.Ping(); err != nil {
		return err
	}
	DB = db
	return createTables()
}

func createTables() error {
	stmts := []string{
		`CREATE TABLE IF NOT EXISTS users (
			id VARCHAR(36) PRIMARY KEY,
			email VARCHAR(255) UNIQUE,
			nickname VARCHAR(50),
			avatar_url TEXT,
			password_hash VARCHAR(255),
			role VARCHAR(20) DEFAULT 'player',
			is_guest BOOLEAN DEFAULT 0,
			game_count INTEGER DEFAULT 0,
			token_version INTEGER DEFAULT 0,
			created_at TEXT
		)`,
		`CREATE TABLE IF NOT EXISTS game_sessions (
			id VARCHAR(36) PRIMARY KEY,
			script_id VARCHAR(36),
			status VARCHAR(20) DEFAULT 'waiting',
			phase VARCHAR(100) DEFAULT '',
			phase_index INTEGER DEFAULT 0,
			started_at TEXT,
			completed_at TEXT,
			config TEXT,
			created_at TEXT
		)`,
		`CREATE TABLE IF NOT EXISTS session_players (
			id VARCHAR(36) PRIMARY KEY,
			session_id VARCHAR(36),
			user_id VARCHAR(36),
			role_id VARCHAR(100),
			role_name VARCHAR(100) DEFAULT '',
			is_human BOOLEAN DEFAULT 1,
			is_ready BOOLEAN DEFAULT 0,
			avatar_url TEXT,
			status VARCHAR(20) DEFAULT 'active',
			joined_at TEXT
		)`,
		`CREATE TABLE IF NOT EXISTS chat_messages (
			id VARCHAR(36) PRIMARY KEY,
			session_id VARCHAR(36),
			sender_role_id VARCHAR(100),
			sender_name VARCHAR(100) DEFAULT '',
			message_type VARCHAR(20) DEFAULT 'public',
			content TEXT,
			target_role_id VARCHAR(100),
			clue_id VARCHAR(100),
			phase_index INTEGER DEFAULT 0,
			created_at TEXT
		)`,
		`CREATE TABLE IF NOT EXISTS clues_state (
			id VARCHAR(36) PRIMARY KEY,
			session_id VARCHAR(36),
			clue_id VARCHAR(100),
			status VARCHAR(20) DEFAULT 'unreleased',
			revealed_by VARCHAR(100),
			released_at TEXT,
			created_at TEXT
		)`,
		`CREATE TABLE IF NOT EXISTS votes (
			id VARCHAR(36) PRIMARY KEY,
			session_id VARCHAR(36),
			round INTEGER DEFAULT 1,
			voter_id VARCHAR(36),
			target_id VARCHAR(36),
			reason TEXT,
			created_at TEXT
		)`,
	}
	for _, s := range stmts {
		if _, err := DB.Exec(s); err != nil {
			return err
		}
	}
	return nil
}