package app

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

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

type ResourceType string

func openDatabase() (*sql.DB, error) {
	os.MkdirAll(filepath.Dir(databasePath), 0755)
	return sql.Open("sqlite3", databasePath)
}

func initDatabase(db *sql.DB) error {
	// create user table
	if _, err := db.Exec(`
	CREATE TABLE IF NOT EXISTS users (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		created_at TIMESTAMP NOT NULL,
		username TEXT,
		email TEXT
	);
	`); err != nil {
		return err
	}

	// create user auth token table
	if _, err := db.Exec(`
	CREATE TABLE IF NOT EXISTS user_auth_tokens (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		created_at TIMESTAMP NOT NULL,
		expires_at TIMESTAMP NOT NULL,
		user_id INTEGER NOT NULL,
		client_id TEXT,
		type TEXT NOT NULL,
		token TEXT NOT NULL
	);
	`); err != nil {
		return err
	}

	// create resource reference table
	if _, err := db.Exec(`
	CREATE TABLE IF NOT EXISTS resources (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		created_at TIMESTAMP NOT NULL,
		created_by TEXT,
		type TEXT NOT NULL,
		data TEXT NOT NULL,
		alias TEXT NOT NULL UNIQUE
	);
	`); err != nil {
		return err
	}

	// create strategy board table
	if _, err := db.Exec(`
	CREATE TABLE IF NOT EXISTS strategy_boards (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		created_at TIMESTAMP NOT NULL,
		created_by TEXT,
		name TEXT NOT NULL,
		data TEXT NOT NULL,
		hash TEXT NOT NULL UNIQUE
	);
	`); err != nil {
		return err
	}

	// create form submission table
	if _, err := db.Exec(`
	CREATE TABLE IF NOT EXISTS form_submissions (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		created_at TIMESTAMP NOT NULL,
		created_by TEXT,
		form_name TEXT NOT NULL,
		data TEXT NOT NULL
	);
	`); err != nil {
		return err
	}

	// create characters table
	if _, err := db.Exec(`
	CREATE TABLE IF NOT EXISTS characters (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		created_at TIMESTAMP NOT NULL,
		updated_at TIMESTAMP NOT NULL,
		synced_at TIMESTAMP,
		lodestone_id TEXT NOT NULL UNIQUE,
		user_id INTEGER,
		status TEXT,
		first_name TEXT,
		last_name TEXT,
		world TEXT,
		avatar_url TEXT,
		verify_code TEXT,
		is_main BOOLEAN
	);
	`); err != nil {
		return err
	}

	// create documents table
	if _, err := db.Exec(`
	CREATE TABLE IF NOT EXISTS documents (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		created_at TIMESTAMP NOT NULL,
		updated_at TIMESTAMP NOT NULL,
		created_by TEXT,
		title TEXT,
		content TEXT,
		tags TEXT
	);
	`); err != nil {
		return err
	}

	return nil
}

type UserRecord struct {
	ID        int64
	CreatedAt time.Time
	Username  string
	Email     string
}

func createUserRecord(db *sql.DB, record *UserRecord) error {
	stmt, err := db.Prepare(`INSERT INTO users (created_at, username, email) VALUES (?, ?, ?)`)
	if err != nil {
		return err
	}
	defer stmt.Close()
	record.CreatedAt = time.Now()
	res, err := stmt.Exec(record.CreatedAt, sanitizeText(record.Username), sanitizeText(record.Email))
	if err != nil {
		return err
	}
	id, err := res.LastInsertId()
	if err != nil {
		return err
	}
	record.ID = id
	log.Printf(" >> create user %d", id)
	return nil
}

func fetchUserRecordFromRow(row *sql.Row) (UserRecord, error) {
	var record UserRecord
	return record, row.Scan(&record.ID, &record.CreatedAt, &record.Username, &record.Email)
}

func fetchUserRecordByID(db *sql.DB, ID int64) (UserRecord, error) {
	stmt, err := db.Prepare(`SELECT * FROM users WHERE id = ?`)
	if err != nil {
		return UserRecord{}, err
	}
	defer stmt.Close()
	return fetchUserRecordFromRow(stmt.QueryRow(ID))
}

type UserAuthTokenRecord struct {
	ID        int64
	CreatedAt time.Time
	ExpiresAt time.Time
	UserID    int64
	ClientID  string
	Type      string
	Token     string
}

func createUserAuthTokenRecord(db *sql.DB, record *UserAuthTokenRecord) error {
	stmt, err := db.Prepare(`INSERT INTO user_auth_tokens (created_at, expires_at, user_id, client_id, type, token) VALUES (?, ?, ?, ?, ?, ?)`)
	if err != nil {
		return err
	}
	defer stmt.Close()
	record.CreatedAt = time.Now()
	res, err := stmt.Exec(record.CreatedAt, record.ExpiresAt, record.UserID, sanitizeText(record.ClientID), sanitizeText(record.Type), sanitizeText(record.Token))
	if err != nil {
		return err
	}
	id, err := res.LastInsertId()
	if err != nil {
		return err
	}
	record.ID = id
	log.Printf(" >> create user auth token %d", id)
	return nil
}

func fetchUserAuthTokenRecordFromRow(row *sql.Row) (UserAuthTokenRecord, error) {
	var record UserAuthTokenRecord
	return record, row.Scan(&record.ID, &record.CreatedAt, &record.ExpiresAt, &record.UserID, &record.ClientID, &record.Type, &record.Token)
}

func fetchUserAuthTokenRecordByID(db *sql.DB, ID int64) (UserAuthTokenRecord, error) {
	stmt, err := db.Prepare(`SELECT * FROM user_auth_tokens WHERE id = ? AND expires_at > ?`)
	if err != nil {
		return UserAuthTokenRecord{}, err
	}
	defer stmt.Close()
	return fetchUserAuthTokenRecordFromRow(stmt.QueryRow(ID, time.Now()))
}

func fetchUserAuthTokenRecordByTokenAndType(db *sql.DB, token string, tokenType string) (UserAuthTokenRecord, error) {
	stmt, err := db.Prepare(`SELECT * FROM user_auth_tokens WHERE token = ? AND type = ? AND expires_at > ? ORDER BY id DESC LIMIT 1`)
	if err != nil {
		return UserAuthTokenRecord{}, err
	}
	defer stmt.Close()
	return fetchUserAuthTokenRecordFromRow(stmt.QueryRow(token, tokenType, time.Now()))
}

func fetchUserAuthTokenRecordByUserAndType(db *sql.DB, userID int64, tokenType string) (UserAuthTokenRecord, error) {
	stmt, err := db.Prepare(`SELECT * FROM user_auth_tokens WHERE user_id = ? AND type = ? AND expires_at > ? ORDER BY id DESC LIMIT 1`)
	if err != nil {
		return UserAuthTokenRecord{}, err
	}
	defer stmt.Close()
	return fetchUserAuthTokenRecordFromRow(stmt.QueryRow(userID, tokenType, time.Now()))
}

func deleteExpiredUserAuthTokenRecords(db *sql.DB, userID int64) error {
	log.Printf(" >> delete expired auth tokens for user %d", userID)
	stmt, err := db.Prepare(`DELETE FROM user_auth_tokens WHERE user_id = ? AND expires_at < ?`)
	if err != nil {
		return err
	}
	defer stmt.Close()
	r, err := stmt.Exec(userID, time.Now())
	count, _ := r.RowsAffected()
	log.Printf(" >>> %d records deleted", count)
	return err
}

func deleteUserAuthTokenRecordsByType(db *sql.DB, userID int64, tokenType string) error {
	log.Printf(" >> delete %s auth tokens for user %d", tokenType, userID)
	stmt, err := db.Prepare(`DELETE FROM user_auth_tokens WHERE user_id = ? AND type = ?`)
	if err != nil {
		return err
	}
	defer stmt.Close()
	r, err := stmt.Exec(userID, sanitizeText(tokenType))
	count, _ := r.RowsAffected()
	log.Printf(" >>> %d records deleted", count)
	return err
}

type ResourceRecord struct {
	ID        int64
	CreatedAt time.Time
	CreatedBy string
	Data      string
	Alias     string
}

func createResourceRecord(db *sql.DB, record *ResourceRecord) error {
	stmt, err := db.Prepare(`INSERT INTO resources (created_at, created_by, data, alias) VALUES (?, ?, ?, ?)`)
	if err != nil {
		return err
	}
	defer stmt.Close()
	record.CreatedAt = time.Now()
	res, err := stmt.Exec(record.CreatedAt, record.CreatedBy, sanitizeText(record.Data), sanitizeText(record.Alias))
	if err != nil {
		return err
	}
	id, err := res.LastInsertId()
	if err != nil {
		return err
	}
	record.ID = id
	log.Printf(" >> create resource %d", id)
	return nil
}

func fetchResourceRecordFromRow(row *sql.Row) (ResourceRecord, error) {
	var record ResourceRecord
	return record, row.Scan(&record.ID, &record.CreatedAt, &record.CreatedBy, &record.Data, &record.Alias)
}

func fetchResourceRecordByAlias(db *sql.DB, alias string) (ResourceRecord, error) {
	stmt, err := db.Prepare(`SELECT * FROM resources WHERE alias = ?`)
	if err != nil {
		return ResourceRecord{}, err
	}
	defer stmt.Close()
	return fetchResourceRecordFromRow(stmt.QueryRow(alias))
}

type StrategyBoardRecord struct {
	ID        int64
	CreatedAt time.Time
	CreatedBy string
	Name      string
	Data      string
	Hash      string
}

func createStrategyBoardRecord(db *sql.DB, record *StrategyBoardRecord) error {
	stmt, err := db.Prepare(`INSERT INTO strategy_boards (created_at, created_by, name, data, hash) VALUES (?, ?, ?, ?, ?)`)
	if err != nil {
		return err
	}
	defer stmt.Close()
	record.CreatedAt = time.Now()
	res, err := stmt.Exec(record.CreatedAt, record.CreatedBy, sanitizeText(record.Name), sanitizeText(record.Data), record.Hash)
	if err != nil {
		return err
	}
	id, err := res.LastInsertId()
	if err != nil {
		return err
	}
	record.ID = id
	log.Printf(" >> create strategy board %d", id)
	return nil
}

func fetchStrategyBoardRecordFromRow(row *sql.Row) (StrategyBoardRecord, error) {
	var record StrategyBoardRecord
	return record, row.Scan(&record.ID, &record.CreatedAt, &record.CreatedBy, &record.Name, &record.Data, &record.Hash)
}

func fetchStrategyBoardRecordByHash(db *sql.DB, hash string) (StrategyBoardRecord, error) {
	stmt, err := db.Prepare(`SELECT * FROM strategy_boards WHERE hash = ?`)
	if err != nil {
		return StrategyBoardRecord{}, err
	}
	defer stmt.Close()
	return fetchStrategyBoardRecordFromRow(stmt.QueryRow(hash))
}

func fetchStrategyBoardRecordByID(db *sql.DB, ID int64) (StrategyBoardRecord, error) {
	stmt, err := db.Prepare(`SELECT * FROM strategy_boards WHERE id = ?`)
	if err != nil {
		return StrategyBoardRecord{}, err
	}
	defer stmt.Close()
	return fetchStrategyBoardRecordFromRow(stmt.QueryRow(ID))
}

type FormSubmissionRecord struct {
	ID        int64
	CreatedAt time.Time
	CreatedBy string
	FormName  string
	Data      string
}

func createFormSubmissionRecord(db *sql.DB, record *FormSubmissionRecord) error {
	stmt, err := db.Prepare(`INSERT INTO form_submissions (created_at, created_by, form_name, data) VALUES (?, ?, ?, ?)`)
	if err != nil {
		return err
	}
	defer stmt.Close()
	res, err := stmt.Exec(record.CreatedAt, record.CreatedBy, sanitizeText(record.FormName), sanitizeText(record.Data))
	if err != nil {
		return err
	}
	id, err := res.LastInsertId()
	if err != nil {
		return err
	}
	record.ID = id
	log.Printf(" >> create form submission %d", id)
	return nil
}

func fetchFormSubmissionRecordFromRow(row *sql.Rows) (FormSubmissionRecord, error) {
	var record FormSubmissionRecord
	return record, row.Scan(&record.ID, &record.CreatedAt, &record.CreatedBy, &record.FormName, &record.Data)
}

func fetchFormSubmissionRecordsByFormName(db *sql.DB, name string) ([]FormSubmissionRecord, error) {
	stmt, err := db.Prepare(`SELECT * FROM form_submissions WHERE form_name = ?`)
	if err != nil {
		return nil, err
	}
	defer stmt.Close()
	records := make([]FormSubmissionRecord, 0)
	rows, err := stmt.Query(name)
	if err != nil {
		return nil, err
	}
	for rows.Next() {

		record, err := fetchFormSubmissionRecordFromRow(rows)
		if err != nil {
			return nil, err
		}
		records = append(records, record)
	}
	return records, nil
}

type CharacterRecord struct {
	ID          int64
	CreatedAt   time.Time
	UpdatedAt   time.Time
	SyncedAt    time.Time
	LodestoneID string
	UserID      int64
	Status      string
	FirstName   string
	LastName    string
	World       string
	AvatarURL   string
	VerifyCode  string
	IsMain      bool
}

func updateCharacterRecord(db *sql.DB, record *CharacterRecord) error {
	stmt, err := db.Prepare(`
		INSERT INTO characters (created_at, updated_at, synced_at, lodestone_id, user_id, status, first_name, last_name, world, avatar_url, verify_code, is_main)
		VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
		ON CONFLICT(lodestone_id)
		DO UPDATE SET 
			updated_at=excluded.updated_at, synced_at=excluded.synced_at, lodestone_id=excluded.lodestone_id, user_id=excluded.user_id, 
			status=excluded.status, first_name=excluded.first_name, last_name=excluded.last_name, world=excluded.world,
			avatar_url=excluded.avatar_url, verify_code=excluded.verify_code, is_main=excluded.is_main
	`)
	if err != nil {
		return err
	}
	defer stmt.Close()
	if record.ID == 0 {
		record.CreatedAt = time.Now()
	}
	record.UpdatedAt = time.Now()
	res, err := stmt.Exec(
		record.CreatedAt, record.UpdatedAt, record.SyncedAt, sanitizeText(record.LodestoneID), record.UserID,
		sanitizeText(record.Status), sanitizeText(record.FirstName), sanitizeText(record.LastName), sanitizeText(record.World),
		sanitizeText(record.AvatarURL), sanitizeText(record.VerifyCode), record.IsMain,
	)
	if err != nil {
		return err
	}
	if record.ID == 0 {
		id, err := res.LastInsertId()
		if err != nil {
			return err
		}
		record.ID = id
	}
	log.Printf(" >> update character %d", record.ID)
	return nil
}

func fetchCharacterRecordFromRow(row *sql.Rows) (CharacterRecord, error) {
	var record CharacterRecord
	return record, row.Scan(
		&record.ID, &record.CreatedAt, &record.UpdatedAt, &record.SyncedAt, &record.LodestoneID,
		&record.UserID, &record.Status, &record.FirstName, &record.LastName, &record.World,
		&record.AvatarURL, &record.VerifyCode, &record.IsMain,
	)
}

func fetchCharacterRecordByID(db *sql.DB, ID int64) (CharacterRecord, error) {
	stmt, err := db.Prepare(`SELECT * FROM characters WHERE id = ?`)
	if err != nil {
		return CharacterRecord{}, err
	}
	defer stmt.Close()
	rows, err := stmt.Query(ID)
	if err != nil {
		return CharacterRecord{}, err
	}
	defer rows.Close()
	if !rows.Next() {
		return CharacterRecord{}, sql.ErrNoRows
	}
	return fetchCharacterRecordFromRow(rows)
}

func fetchCharacterRecordByLodestoneID(db *sql.DB, lodestoneID string) (CharacterRecord, error) {
	stmt, err := db.Prepare(`SELECT * FROM characters WHERE lodestone_id = ?`)
	if err != nil {
		return CharacterRecord{}, err
	}
	defer stmt.Close()
	rows, err := stmt.Query(lodestoneID)
	if err != nil {
		return CharacterRecord{}, err
	}
	defer rows.Close()
	if !rows.Next() {
		return CharacterRecord{}, sql.ErrNoRows
	}
	return fetchCharacterRecordFromRow(rows)
}

func fetchCharacterRecordsByUserID(db *sql.DB, userID int64) ([]CharacterRecord, error) {
	stmt, err := db.Prepare(`SELECT * FROM characters WHERE user_id = ?`)
	if err != nil {
		return nil, err
	}
	defer stmt.Close()
	records := make([]CharacterRecord, 0)
	rows, err := stmt.Query(userID)
	if err != nil {
		return nil, err
	}
	for rows.Next() {
		record, err := fetchCharacterRecordFromRow(rows)
		if err != nil {
			return nil, err
		}
		records = append(records, record)
	}
	return records, nil
}

func fetchMainCharacterRecordByUserID(db *sql.DB, userID int64) (CharacterRecord, error) {
	stmt, err := db.Prepare(`SELECT * FROM characters WHERE user_id = ? AND is_main = ? AND status = ?`)
	if err != nil {
		return CharacterRecord{}, err
	}
	defer stmt.Close()
	rows, err := stmt.Query(userID, true, characterStatusVerified)
	if err != nil {
		return CharacterRecord{}, err
	}
	defer rows.Close()
	if !rows.Next() {
		return CharacterRecord{}, sql.ErrNoRows
	}
	return fetchCharacterRecordFromRow(rows)
}

func deleteCharacterRecord(db *sql.DB, ID int64) error {
	log.Printf(" >> delete character %d", ID)
	stmt, err := db.Prepare(`DELETE FROM character WHERE id = ?`)
	if err != nil {
		return err
	}
	if _, err := stmt.Exec(ID); err != nil {
		return err
	}
	return nil
}

type DocumentRecord struct {
	ID        int64
	CreatedAt time.Time
	UpdatedAt time.Time
	CreatedBy string
	Title     string
	Content   string
	Tags      string
}

func updateDocumentsRecord(db *sql.DB, record *DocumentRecord) error {
	// update
	if record.ID > 0 {
		log.Printf(" >> update document %d", record.ID)
		stmt, err := db.Prepare(`UPDATE documents SET updated_at=?, created_by=?, title=?, content=?, tags=? WHERE id=?`)
		if err != nil {
			return err
		}
		defer stmt.Close()
		record.UpdatedAt = time.Now()
		if _, err := stmt.Exec(record.UpdatedAt, record.CreatedBy, sanitizeText(record.Title), sanitizeText(record.Content), sanitizeText(record.Tags), record.ID); err != nil {
			return err
		}
		return nil
	}

	// create
	stmt, err := db.Prepare(`
		INSERT INTO documents (created_at, updated_at, created_by, title, content, tags)
		VALUES (?, ?, ?, ?, ?, ?)
	`)
	if err != nil {
		return err
	}
	defer stmt.Close()
	record.CreatedAt = time.Now()
	record.UpdatedAt = record.CreatedAt
	res, err := stmt.Exec(
		record.CreatedAt, record.UpdatedAt, record.CreatedBy, sanitizeText(record.Title), sanitizeText(record.Content), sanitizeText(record.Tags),
	)
	if err != nil {
		return err
	}
	id, err := res.LastInsertId()
	if err != nil {
		return err
	}
	record.ID = id
	log.Printf(" >> created document %d", record.ID)
	return nil
}

func fetchDocumentRecordFromRow(rows *sql.Rows) (DocumentRecord, error) {
	var record DocumentRecord
	return record, rows.Scan(&record.ID, &record.CreatedAt, &record.UpdatedAt, &record.CreatedBy, &record.Title, &record.Content, &record.Tags)
}

func fetchDocumentRecordByID(db *sql.DB, ID int64) (DocumentRecord, error) {
	stmt, err := db.Prepare(`SELECT * FROM documents WHERE id = ?`)
	if err != nil {
		return DocumentRecord{}, err
	}
	defer stmt.Close()
	rows, err := stmt.Query(ID)
	if err != nil {
		return DocumentRecord{}, err
	}
	defer rows.Close()
	if !rows.Next() {
		return DocumentRecord{}, sql.ErrNoRows
	}
	return fetchDocumentRecordFromRow(rows)
}

func fetchDocumentRecordsByUserID(db *sql.DB, userID int64) ([]DocumentRecord, error) {
	stmt, err := db.Prepare(`SELECT * FROM documents WHERE created_by = ?`)
	if err != nil {
		return nil, err
	}
	defer stmt.Close()
	records := make([]DocumentRecord, 0)
	rows, err := stmt.Query(fmt.Sprintf("user:%d", userID))
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	for rows.Next() {
		record, err := fetchDocumentRecordFromRow(rows)
		if err != nil {
			return nil, err
		}
		records = append(records, record)
	}
	return records, nil
}

func deleteDocumentRecord(db *sql.DB, ID int64) error {
	log.Printf(" >> delete document %d", ID)
	stmt, err := db.Prepare(`DELETE FROM documents WHERE id = ?`)
	if err != nil {
		return err
	}
	if _, err := stmt.Exec(ID); err != nil {
		return err
	}
	return nil
}