package db

import (
	"database/sql"

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

type Character struct {
	ID          int64
	DiscordID   string
	FirstName   string
	LastName    string
	ServerName  string
	TomestoneID sql.NullInt64
}

func New(path string) (*sql.DB, error) {
	db, err := sql.Open("sqlite3", path)
	if err != nil {
		return nil, err
	}

	if _, err := db.Exec(`
		CREATE TABLE IF NOT EXISTS characters (
			id INTEGER PRIMARY KEY AUTOINCREMENT,
			discord_id TEXT NOT NULL,
			first_name TEXT NOT NULL,
			last_name TEXT NOT NULL,
			server_name TEXT NOT NULL,
			tomestone_id INTEGER,
			UNIQUE(discord_id, first_name, last_name, server_name)
		)
	`); err != nil {
		return nil, err
	}

	if _, err := db.Exec(`
		CREATE TABLE IF NOT EXISTS party_messages (
			discord_message_id TEXT PRIMARY KEY,
			party_id TEXT NOT NULL
		)
	`); err != nil {
		return nil, err
	}

	if _, err := db.Exec(`
		CREATE TABLE IF NOT EXISTS roster_messages (
			page INTEGER PRIMARY KEY,
			discord_message_id TEXT NOT NULL
		)
	`); err != nil {
		return nil, err
	}

	if _, err := db.Exec(`
		CREATE TABLE IF NOT EXISTS progression (
			tomestone_id INTEGER NOT NULL,
			encounter_id INTEGER NOT NULL,
			best_value REAL NOT NULL DEFAULT 0,
			PRIMARY KEY (tomestone_id, encounter_id)
		)
	`); err != nil {
		return nil, err
	}

	return db, nil
}

func AddCharacter(db *sql.DB, discordID, firstName, lastName, serverName string) error {
	_, err := db.Exec(
		`INSERT OR IGNORE INTO characters (discord_id, first_name, last_name, server_name) VALUES (?, ?, ?, ?)`,
		discordID, firstName, lastName, serverName,
	)
	return err
}

func RemoveCharacter(db *sql.DB, discordID, firstName, lastName, serverName string) error {
	_, err := db.Exec(
		`DELETE FROM characters WHERE discord_id = ? AND first_name = ? AND last_name = ? AND server_name = ?`,
		discordID, firstName, lastName, serverName,
	)
	return err
}

func GetCharactersByDiscordID(db *sql.DB, discordID string) ([]Character, error) {
	rows, err := db.Query(
		`SELECT id, discord_id, first_name, last_name, server_name, tomestone_id FROM characters WHERE discord_id = ?`,
		discordID,
	)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var chars []Character
	for rows.Next() {
		var c Character
		if err := rows.Scan(&c.ID, &c.DiscordID, &c.FirstName, &c.LastName, &c.ServerName, &c.TomestoneID); err != nil {
			return nil, err
		}
		chars = append(chars, c)
	}
	return chars, rows.Err()
}

func GetAllCharacters(db *sql.DB) ([]Character, error) {
	rows, err := db.Query(`SELECT id, discord_id, first_name, last_name, server_name, tomestone_id FROM characters`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var chars []Character
	for rows.Next() {
		var c Character
		if err := rows.Scan(&c.ID, &c.DiscordID, &c.FirstName, &c.LastName, &c.ServerName, &c.TomestoneID); err != nil {
			return nil, err
		}
		chars = append(chars, c)
	}
	return chars, rows.Err()
}

func GetDiscordIDByCharacter(db *sql.DB, firstName, lastName, serverName string) (string, error) {
	var discordID string
	err := db.QueryRow(
		`SELECT discord_id FROM characters WHERE first_name = ? AND last_name = ? AND server_name = ?`,
		firstName, lastName, serverName,
	).Scan(&discordID)
	return discordID, err
}

func GetHostNames(db *sql.DB) (map[string]bool, error) {
	rows, err := db.Query(`SELECT first_name, last_name FROM characters`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	names := make(map[string]bool)
	for rows.Next() {
		var firstName, lastName string
		if err := rows.Scan(&firstName, &lastName); err != nil {
			return nil, err
		}
		names[firstName+" "+lastName] = true
	}
	return names, rows.Err()
}

type PartyMessage struct {
	DiscordMessageID string
	PartyID          string
}

func SavePartyMessage(db *sql.DB, discordMsgID, partyID string) error {
	_, err := db.Exec(
		`INSERT OR REPLACE INTO party_messages (discord_message_id, party_id) VALUES (?, ?)`,
		discordMsgID, partyID,
	)
	return err
}

func DeletePartyMessage(db *sql.DB, discordMsgID string) error {
	_, err := db.Exec(`DELETE FROM party_messages WHERE discord_message_id = ?`, discordMsgID)
	return err
}

func GetPartyIDByMessageID(db *sql.DB, discordMsgID string) (string, error) {
	var partyID string
	err := db.QueryRow(`SELECT party_id FROM party_messages WHERE discord_message_id = ?`, discordMsgID).Scan(&partyID)
	return partyID, err
}

func GetDiscordMsgIDByPartyID(db *sql.DB, partyID string) (string, error) {
	var msgID string
	err := db.QueryRow(`SELECT discord_message_id FROM party_messages WHERE party_id = ?`, partyID).Scan(&msgID)
	return msgID, err
}

func GetAllPartyMessages(db *sql.DB) ([]PartyMessage, error) {
	rows, err := db.Query(`SELECT discord_message_id, party_id FROM party_messages`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var msgs []PartyMessage
	for rows.Next() {
		var m PartyMessage
		if err := rows.Scan(&m.DiscordMessageID, &m.PartyID); err != nil {
			return nil, err
		}
		msgs = append(msgs, m)
	}
	return msgs, rows.Err()
}

func DeletePartyMessageByPartyID(db *sql.DB, partyID string) (string, error) {
	row := db.QueryRow(`SELECT discord_message_id FROM party_messages WHERE party_id = ?`, partyID)
	var msgID string
	if err := row.Scan(&msgID); err != nil {
		return "", err
	}
	if _, err := db.Exec(`DELETE FROM party_messages WHERE party_id = ?`, partyID); err != nil {
		return "", err
	}
	return msgID, nil
}

type RosterMessage struct {
	Page           int
	DiscordMessageID string
}

func SaveRosterMessage(db *sql.DB, page int, discordMsgID string) error {
	_, err := db.Exec(
		`INSERT OR REPLACE INTO roster_messages (page, discord_message_id) VALUES (?, ?)`,
		page, discordMsgID,
	)
	return err
}

func DeleteRosterMessage(db *sql.DB, page int) error {
	_, err := db.Exec(`DELETE FROM roster_messages WHERE page = ?`, page)
	return err
}

func GetRosterMessageByID(db *sql.DB, discordMsgID string) (int, error) {
	var page int
	err := db.QueryRow(`SELECT page FROM roster_messages WHERE discord_message_id = ?`, discordMsgID).Scan(&page)
	return page, err
}

func GetAllRosterMessages(db *sql.DB) ([]RosterMessage, error) {
	rows, err := db.Query(`SELECT page, discord_message_id FROM roster_messages ORDER BY page`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var msgs []RosterMessage
	for rows.Next() {
		var m RosterMessage
		if err := rows.Scan(&m.Page, &m.DiscordMessageID); err != nil {
			return nil, err
		}
		msgs = append(msgs, m)
	}
	return msgs, rows.Err()
}

func ClearAllRosterMessages(db *sql.DB) error {
	_, err := db.Exec(`DELETE FROM roster_messages`)
	return err
}

func UpdateCharacterTomestoneID(db *sql.DB, firstName, lastName, serverName string, tomestoneID int64) error {
	_, err := db.Exec(
		`UPDATE characters SET tomestone_id = ? WHERE first_name = ? AND last_name = ? AND server_name = ?`,
		tomestoneID, firstName, lastName, serverName,
	)
	return err
}

func GetTomestoneIDByCharacter(db *sql.DB, firstName, lastName, serverName string) (int64, error) {
	var tomestoneID sql.NullInt64
	err := db.QueryRow(
		`SELECT tomestone_id FROM characters WHERE first_name = ? AND last_name = ? AND server_name = ?`,
		firstName, lastName, serverName,
	).Scan(&tomestoneID)
	if err != nil {
		return 0, err
	}
	if !tomestoneID.Valid {
		return 0, nil
	}
	return tomestoneID.Int64, nil
}

func GetAllCharactersWithTomestoneID(db *sql.DB) ([]Character, error) {
	rows, err := db.Query(`SELECT id, discord_id, first_name, last_name, server_name, tomestone_id FROM characters WHERE tomestone_id IS NOT NULL`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var chars []Character
	for rows.Next() {
		var c Character
		if err := rows.Scan(&c.ID, &c.DiscordID, &c.FirstName, &c.LastName, &c.ServerName, &c.TomestoneID); err != nil {
			return nil, err
		}
		chars = append(chars, c)
	}
	return chars, rows.Err()
}

type ProgressionRecord struct {
	TomestoneID int64
	EncounterID int
	BestValue   float64
}

func GetProgression(db *sql.DB, tomestoneID int64, encounterID int) (float64, error) {
	var bestValue float64
	err := db.QueryRow(
		`SELECT best_value FROM progression WHERE tomestone_id = ? AND encounter_id = ?`,
		tomestoneID, encounterID,
	).Scan(&bestValue)
	return bestValue, err
}

func UpdateProgression(db *sql.DB, tomestoneID int64, encounterID int, bestValue float64) error {
	_, err := db.Exec(
		`INSERT OR REPLACE INTO progression (tomestone_id, encounter_id, best_value) VALUES (?, ?, ?)`,
		tomestoneID, encounterID, bestValue,
	)
	return err
}

func GetAllProgression(db *sql.DB) ([]ProgressionRecord, error) {
	rows, err := db.Query(`SELECT tomestone_id, encounter_id, best_value FROM progression`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var records []ProgressionRecord
	for rows.Next() {
		var r ProgressionRecord
		if err := rows.Scan(&r.TomestoneID, &r.EncounterID, &r.BestValue); err != nil {
			return nil, err
		}
		records = append(records, r)
	}
	return records, rows.Err()
}