package storage

import (
	"context"
	"database/sql"
	"fmt"
	"os"

	_ "modernc.org/sqlite" // Pure Go SQLite driver
)

// Storage provides event persistence using SQLite.
// Consumers should define their own interface based on their needs.
type Storage struct {
	db     *sql.DB
	dbPath string
}

// New creates a new Storage instance and initializes the database schema.
// The dbPath should be a file path or ":memory:" for in-memory database.
func New(dbPath string) (*Storage, error) {
	db, err := sql.Open("sqlite", dbPath)
	if err != nil {
		return nil, fmt.Errorf("failed to open database: %w", err)
	}

	// Configure connection pool for SQLite
	// SQLite works best with a single connection due to single-writer lock
	db.SetMaxOpenConns(1)    // Single connection (SQLite is single-writer)
	db.SetMaxIdleConns(1)    // Keep connection alive
	db.SetConnMaxLifetime(0) // Never close the connection

	// Configure SQLite for optimal performance
	pragmas := []string{
		"PRAGMA journal_mode=WAL",              // Write-Ahead Logging for concurrency
		"PRAGMA synchronous=NORMAL",            // Balance safety and performance
		"PRAGMA cache_size=10000",              // 10000 pages (~40MB cache)
		"PRAGMA temp_store=MEMORY",             // Temp tables in memory
		"PRAGMA mmap_size=268435456",           // 256MB memory-mapped I/O
		"PRAGMA page_size=4096",                // Standard page size
		"PRAGMA foreign_keys=ON",               // Enforce foreign key constraints
		"PRAGMA busy_timeout=5000",             // Wait up to 5s for locks
		"PRAGMA auto_vacuum=INCREMENTAL",       // Reclaim space incrementally
	}

	for _, pragma := range pragmas {
		if _, err := db.Exec(pragma); err != nil {
			db.Close()
			return nil, fmt.Errorf("failed to set pragma %q: %w", pragma, err)
		}
	}

	s := &Storage{
		db:     db,
		dbPath: dbPath,
	}

	// Initialize schema
	if err := s.initSchema(context.Background()); err != nil {
		db.Close()
		return nil, fmt.Errorf("failed to initialize schema: %w", err)
	}

	return s, nil
}

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

// initSchema creates all necessary tables and indexes.
func (s *Storage) initSchema(ctx context.Context) error {
	schema := `
	-- Main events table
	CREATE TABLE IF NOT EXISTS events (
		-- Primary event data
		id TEXT PRIMARY KEY,
		event_data BLOB NOT NULL,           -- Protobuf binary
		canonical_json BLOB NOT NULL,       -- zstd compressed canonical JSON

		-- Denormalized fields for efficient querying
		pubkey TEXT NOT NULL,
		kind INTEGER NOT NULL,
		created_at INTEGER NOT NULL,        -- Unix timestamp
		content TEXT,                       -- For full-text search (optional)
		tags TEXT,                          -- JSON text for tag queries (use json_* functions)
		sig TEXT NOT NULL,

		-- Metadata
		deleted INTEGER DEFAULT 0,          -- STRICT mode: use INTEGER for boolean
		received_at INTEGER DEFAULT (unixepoch())
	) STRICT;

	-- Critical indexes for Nostr query patterns
	CREATE INDEX IF NOT EXISTS idx_pubkey_created
		ON events(pubkey, created_at DESC)
		WHERE deleted = 0;

	CREATE INDEX IF NOT EXISTS idx_kind_created
		ON events(kind, created_at DESC)
		WHERE deleted = 0;

	CREATE INDEX IF NOT EXISTS idx_created
		ON events(created_at DESC)
		WHERE deleted = 0;

	-- For tag queries (#e, #p, etc)
	CREATE INDEX IF NOT EXISTS idx_tags
		ON events(tags)
		WHERE deleted = 0;

	-- Replaceable events tracking (NIP-16, NIP-33)
	CREATE TABLE IF NOT EXISTS replaceable_events (
		kind INTEGER NOT NULL,
		pubkey TEXT NOT NULL,
		d_tag TEXT NOT NULL DEFAULT '',    -- For parameterized replaceable events (empty string for non-parameterized)
		current_event_id TEXT NOT NULL,
		created_at INTEGER NOT NULL,
		PRIMARY KEY (kind, pubkey, d_tag),
		FOREIGN KEY (current_event_id) REFERENCES events(id)
	) STRICT;

	-- Auth challenges (NIP-42)
	CREATE TABLE IF NOT EXISTS auth_challenges (
		challenge TEXT PRIMARY KEY,
		created_at INTEGER NOT NULL,
		expires_at INTEGER NOT NULL,
		used INTEGER DEFAULT 0              -- STRICT mode: use INTEGER for boolean
	) STRICT;

	-- Rate limiting
	CREATE TABLE IF NOT EXISTS rate_limits (
		pubkey TEXT PRIMARY KEY,
		event_count INTEGER DEFAULT 0,
		window_start INTEGER NOT NULL,
		last_reset INTEGER DEFAULT (unixepoch())
	) STRICT;
	`

	_, err := s.db.ExecContext(ctx, schema)
	return err
}

// DB returns the underlying *sql.DB for advanced usage.
// This allows consumers to execute custom queries if needed.
func (s *Storage) DB() *sql.DB {
	return s.db
}

type Stats struct {
	EventCount int64
	DBSizeBytes int64
}

func (s *Storage) GetStats(ctx context.Context) (*Stats, error) {
	var eventCount int64
	err := s.db.QueryRowContext(ctx, "SELECT COUNT(*) FROM events WHERE deleted = 0").Scan(&eventCount)
	if err != nil {
		return nil, fmt.Errorf("failed to count events: %w", err)
	}

	var dbSize int64
	if s.dbPath != ":memory:" {
		info, err := os.Stat(s.dbPath)
		if err != nil {
			return nil, fmt.Errorf("failed to stat database file: %w", err)
		}
		dbSize = info.Size()
	}

	return &Stats{
		EventCount: eventCount,
		DBSizeBytes: dbSize,
	}, nil
}