package main

import (
	"database/sql"
	"fmt"
	"os"
	"path/filepath"
	"regexp"
	"strings"

	_ "modernc.org/sqlite"
)

var nonFTS5 = regexp.MustCompile(`[^a-zA-Z0-9\s\*]+`)

// Journal represents a single journal entry.
type Journal struct {
	ID           int64  `json:"id"`
	FullName     string `json:"full_name"`
	Abbreviation string `json:"abbreviation"`
}

// DB wraps a SQLite connection.
type DB struct {
	conn *sql.DB
}

// OpenDB opens or creates the SQLite database at the given path.
func OpenDB(path string) (*DB, error) {
	// Ensure parent directory exists
	dir := filepath.Dir(path)
	if dir != "." && dir != "/" {
		if err := os.MkdirAll(dir, 0755); err != nil {
			return nil, fmt.Errorf("failed to create data directory: %w", err)
		}
	}

	conn, err := sql.Open("sqlite", path)
	if err != nil {
		return nil, fmt.Errorf("failed to open database: %w", err)
	}

	conn.SetMaxOpenConns(1) // SQLite prefers single writer
	conn.SetMaxIdleConns(1)

	if err := conn.Ping(); err != nil {
		return nil, fmt.Errorf("failed to ping database: %w", err)
	}

	db := &DB{conn: conn}
	if err := db.initSchema(); err != nil {
		conn.Close()
		return nil, fmt.Errorf("failed to init schema: %w", err)
	}

	return db, nil
}

// initSchema creates the journals table and FTS5 virtual table.
func (db *DB) initSchema() error {
	stmts := []string{
		`CREATE TABLE IF NOT EXISTS journals (
			id INTEGER PRIMARY KEY AUTOINCREMENT,
			full_name TEXT NOT NULL,
			abbreviation TEXT NOT NULL
		);`,
		`CREATE VIRTUAL TABLE IF NOT EXISTS journals_fts USING fts5(
			full_name, abbreviation,
			content='journals',
			content_rowid='id'
		);`,
	}

	for _, stmt := range stmts {
		if _, err := db.conn.Exec(stmt); err != nil {
			return fmt.Errorf("schema init failed: %w", err)
		}
	}

	return nil
}

// Close closes the database connection.
func (db *DB) Close() error {
	return db.conn.Close()
}

// InsertJournals inserts a batch of journals in a single transaction.
func (db *DB) InsertJournals(journals []Journal) error {
	if len(journals) == 0 {
		return nil
	}

	tx, err := db.conn.Begin()
	if err != nil {
		return err
	}
	defer tx.Rollback()

	insertStmt, err := tx.Prepare("INSERT INTO journals (full_name, abbreviation) VALUES (?, ?)")
	if err != nil {
		return err
	}
	defer insertStmt.Close()

	ftsStmt, err := tx.Prepare("INSERT INTO journals_fts (rowid, full_name, abbreviation) VALUES (?, ?, ?)")
	if err != nil {
		return err
	}
	defer ftsStmt.Close()

	for _, j := range journals {
		res, err := insertStmt.Exec(j.FullName, j.Abbreviation)
		if err != nil {
			return fmt.Errorf("insert failed for %q: %w", j.FullName, err)
		}

		rowid, err := res.LastInsertId()
		if err != nil {
			return fmt.Errorf("last insert id failed: %w", err)
		}

		if _, err := ftsStmt.Exec(rowid, j.FullName, j.Abbreviation); err != nil {
			return fmt.Errorf("fts insert failed for %q: %w", j.FullName, err)
		}
	}

	return tx.Commit()
}

func sanitizeFTS5(query string) string {
	clean := nonFTS5.ReplaceAllString(query, " ")
	tokens := strings.Fields(clean)
	for i, t := range tokens {
		t = strings.ToLower(t)
		if t != "*" && !strings.HasSuffix(t, "*") {
			t = t + "*"
		}
		tokens[i] = t
	}
	return strings.Join(tokens, " ")
}

// SearchJournals searches for journals matching the query using FTS5.
func (db *DB) SearchJournals(query string, limit int) ([]Journal, error) {
	if limit <= 0 {
		limit = 50
	}
	if limit > 200 {
		limit = 200
	}

	clean := sanitizeFTS5(query)
	if clean == "" {
		return []Journal{}, nil
	}

	// FTS5 content tables don't store original data, so we must join back to the
	// main journals table to retrieve full_name and abbreviation columns.
	rows, err := db.conn.Query(`
		SELECT j.id, j.full_name, j.abbreviation
		FROM journals j
		JOIN (
			SELECT rowid FROM journals_fts WHERE journals_fts MATCH ? ORDER BY rank LIMIT ?
		) fts ON j.id = fts.rowid
	`, clean, limit)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	results := []Journal{}
	for rows.Next() {
		var j Journal
		if err := rows.Scan(&j.ID, &j.FullName, &j.Abbreviation); err != nil {
			return nil, err
		}
		results = append(results, j)
	}

	return results, rows.Err()
}

func (db *DB) Count() (int64, error) {
	var count int64
	err := db.conn.QueryRow("SELECT COUNT(*) FROM journals").Scan(&count)
	return count, err
}