// Package database provides SQLite database initialization with WAL mode,
// optimized PRAGMA settings for HDD I/O on resource-constrained VPS,
// and schema management with version tracking.
package database

import (
	"context"
	"database/sql"
	"fmt"
	"log"
	"runtime"
	"strings"
	"sync/atomic"
	"time"

	"huntr/internal/engine"
)

// WriterConfig holds configuration for the Writer.
type WriterConfig struct {
	// BatchSize is the default number of findings to batch before writing.
	// Default: 250
	BatchSize int

	// ChannelSize is the capacity of the findings channel (hard cap for memory).
	// Default: 1000
	ChannelSize int

	// MaxRetries is the number of retry attempts for failed writes.
	// Default: 3
	MaxRetries int

	// BaseRetryDelay is the initial retry delay (doubled each attempt).
	// Default: 10ms
	BaseRetryDelay time.Duration
}

// DefaultWriterConfig returns a WriterConfig with sensible defaults.
func DefaultWriterConfig() WriterConfig {
	return WriterConfig{
		BatchSize:      250,
		ChannelSize:    1000,
		MaxRetries:     3,
		BaseRetryDelay: 10 * time.Millisecond,
	}
}

// WriterStats holds statistics for the Writer.
// All counters except QueueDepth are atomic for concurrent access.
type WriterStats struct {
	// TotalWrites is the total number of findings written successfully.
	TotalWrites atomic.Int64

	// TotalBatches is the number of batch transactions committed.
	TotalBatches atomic.Int64

	// FailedWrites is the number of findings lost after retry exhaustion.
	FailedWrites atomic.Int64
}

// Snapshot returns a copy of the current stats for reading.
type WriterStatsSnapshot struct {
	TotalWrites  int64
	TotalBatches int64
	FailedWrites int64
	QueueDepth   int
}

// Writer implements the single-writer goroutine pattern for concurrent
// scan result persistence. It owns the database connection exclusively
// and processes findings through a buffered channel.
//
// Usage:
//
//	ctx, cancel := context.WithCancel(context.Background())
//	w := NewWriter(ctx, db, DefaultWriterConfig())
//	go w.Start()
//	w.QueueFinding(finding)
//	// ... more findings ...
//	cancel() // triggers graceful shutdown with queue drain
type Writer struct {
	db       *sql.DB
	findings chan engine.Finding
	cfg      WriterConfig
	stats    WriterStats
	ctx      context.Context
	cancel   context.CancelFunc
}

// NewWriter creates a new Writer with the given context, database, and configuration.
// The context controls the writer's lifecycle - cancelling it triggers graceful shutdown.
// The database connection should already be opened with Open().
func NewWriter(ctx context.Context, db *sql.DB, cfg WriterConfig) *Writer {
	// Apply defaults for zero-valued fields
	if cfg.BatchSize <= 0 {
		cfg.BatchSize = 250
	}
	if cfg.ChannelSize <= 0 {
		cfg.ChannelSize = 1000
	}
	if cfg.MaxRetries <= 0 {
		cfg.MaxRetries = 3
	}
	if cfg.BaseRetryDelay <= 0 {
		cfg.BaseRetryDelay = 10 * time.Millisecond
	}

	writerCtx, cancel := context.WithCancel(ctx)

	return &Writer{
		db:       db,
		findings: make(chan engine.Finding, cfg.ChannelSize),
		cfg:      cfg,
		ctx:      writerCtx,
		cancel:   cancel,
	}
}

// Start begins the single-writer goroutine loop. It processes findings
// from the channel in batches and writes them to the database.
//
// Start blocks until the context (passed to NewWriter) is cancelled.
// On cancellation, it drains the remaining queue before returning.
//
// Typical usage:
//
//	go w.Start()
func (w *Writer) Start() error {
	for {
		select {
		case <-w.ctx.Done():
			// Context cancelled - drain remaining queue
			close(w.findings)
			w.drainQueue()
			return w.ctx.Err()

		case finding, ok := <-w.findings:
			if !ok {
				// Channel closed externally (shouldn't happen)
				return nil
			}

			// Collect batch opportunistically (non-blocking)
			batch := w.collectBatch(finding)
			w.writeBatchWithRetry(batch)
		}
	}
}

// QueueFinding adds a finding to the write queue. This method is safe
// to call from multiple goroutines concurrently.
//
// If the queue is full, this will block until space is available
// (backpressure to workers). Returns an error if the context is cancelled.
func (w *Writer) QueueFinding(f engine.Finding) error {
	select {
	case <-w.ctx.Done():
		return w.ctx.Err()
	case w.findings <- f:
		return nil
	}
}

// Stats returns a snapshot of the current writer statistics.
func (w *Writer) Stats() WriterStatsSnapshot {
	return WriterStatsSnapshot{
		TotalWrites:  w.stats.TotalWrites.Load(),
		TotalBatches: w.stats.TotalBatches.Load(),
		FailedWrites: w.stats.FailedWrites.Load(),
		QueueDepth:   len(w.findings),
	}
}

// collectBatch collects findings into a batch starting with the given finding.
// It uses non-blocking reads to opportunistically fill the batch up to
// the adaptive batch size, with a timeout to prevent blocking forever.
func (w *Writer) collectBatch(first engine.Finding) []engine.Finding {
	batchSize := w.determineBatchSize()
	batch := make([]engine.Finding, 0, batchSize)
	batch = append(batch, first)

	// Timeout for batch collection - don't block forever waiting for more
	timeout := time.After(50 * time.Millisecond)

	for len(batch) < batchSize {
		select {
		case <-w.ctx.Done():
			return batch
		case <-timeout:
			return batch
		case finding, ok := <-w.findings:
			if !ok {
				return batch
			}
			batch = append(batch, finding)
		default:
			// No more findings immediately available
			return batch
		}
	}

	return batch
}

// determineBatchSize returns the optimal batch size based on queue depth
// and memory pressure. Larger batches when the queue is deep for throughput,
// smaller batches when the queue is shallow for latency.
func (w *Writer) determineBatchSize() int {
	queueDepth := len(w.findings)

	// High queue depth: flush faster to prevent backlog
	if queueDepth > 800 {
		return 500
	}

	// Check memory pressure
	var memStats runtime.MemStats
	runtime.ReadMemStats(&memStats)
	if memStats.Alloc > 800*1024*1024 { // > 800MB
		// Flush everything under memory pressure
		return queueDepth + 1 // +1 for the first finding already collected
	}

	// Low queue depth: process immediately for low latency
	if queueDepth < 100 && queueDepth > 0 {
		return queueDepth + 1
	}

	// Default batch size
	return w.cfg.BatchSize
}

// writeBatchCtx writes a batch of findings to the database in a single transaction.
// Uses BEGIN IMMEDIATE to prevent SQLITE_BUSY from lock upgrades.
// The ctx parameter allows using a different context during graceful drain.
func (w *Writer) writeBatchCtx(ctx context.Context, findings []engine.Finding) error {
	if len(findings) == 0 {
		return nil
	}

	// BEGIN IMMEDIATE for immediate exclusive lock
	tx, err := w.db.BeginTx(ctx, &sql.TxOptions{})
	if err != nil {
		return fmt.Errorf("begin transaction: %w", err)
	}
	defer tx.Rollback()

	// Execute immediate to get exclusive lock
	_, err = tx.ExecContext(ctx, "SELECT 1")
	if err != nil {
		return fmt.Errorf("acquire lock: %w", err)
	}

	// Prepare INSERT statement once for batch
	stmt, err := tx.PrepareContext(ctx, `
		INSERT OR REPLACE INTO findings
		(domain, path, status_code, content_type, body_snippet, patterns, found_at)
		VALUES (?, ?, ?, ?, ?, ?, ?)
	`)
	if err != nil {
		return fmt.Errorf("prepare insert: %w", err)
	}
	defer stmt.Close()

	// Execute for each finding
	for _, f := range findings {
		// Join patterns as comma-separated string
		patterns := strings.Join(f.Patterns, ",")

		_, err = stmt.ExecContext(ctx,
			f.Domain,
			f.Path,
			f.StatusCode,
			f.ContentType,
			f.BodySnippet,
			patterns,
			f.FoundAt,
		)
		if err != nil {
			return fmt.Errorf("insert finding: %w", err)
		}
	}

	// Commit transaction
	if err := tx.Commit(); err != nil {
		return fmt.Errorf("commit: %w", err)
	}

	// Flush WAL to main .db file so data is visible to external tools immediately.
	// PASSIVE won't block any concurrent readers or writers.
	w.db.ExecContext(ctx, "PRAGMA wal_checkpoint(PASSIVE)")

	// Update stats
	w.stats.TotalWrites.Add(int64(len(findings)))
	w.stats.TotalBatches.Add(1)

	return nil
}

// writeBatch writes a batch using the writer's context.
func (w *Writer) writeBatch(findings []engine.Finding) error {
	return w.writeBatchCtx(w.ctx, findings)
}

// writeBatchWithRetryCtx attempts to write a batch with exponential backoff retry.
// On failure after all retries, logs the lost findings and continues.
// The ctx parameter controls whether to check for cancellation between retries.
// If useBgOnCancel is true, makes a final attempt with background context when cancelled.
func (w *Writer) writeBatchWithRetryCtx(ctx context.Context, findings []engine.Finding, useBgOnCancel bool) error {
	var lastErr error

	for attempt := 0; attempt < w.cfg.MaxRetries; attempt++ {
		lastErr = w.writeBatchCtx(ctx, findings)
		if lastErr == nil {
			return nil
		}

		// Check if context is cancelled before sleeping
		select {
		case <-ctx.Done():
			if useBgOnCancel {
				// Final attempt with background context for graceful shutdown
				bgErr := w.writeBatchCtx(context.Background(), findings)
				if bgErr == nil {
					return nil
				}
				// Background attempt failed - count as lost
				w.stats.FailedWrites.Add(int64(len(findings)))
				log.Printf("[database/writer] lost %d findings (ctx cancelled, bg failed): %v",
					len(findings), bgErr)
				return bgErr
			}
			return ctx.Err()
		default:
		}

		// Exponential backoff: 10ms, 20ms, 40ms
		delay := w.cfg.BaseRetryDelay * (1 << attempt)
		time.Sleep(delay)
	}

	// All retries exhausted - data loss accepted per requirements
	w.stats.FailedWrites.Add(int64(len(findings)))
	log.Printf("[database/writer] lost %d findings after %d retries: %v",
		len(findings), w.cfg.MaxRetries, lastErr)

	return lastErr
}

// writeBatchWithRetry attempts to write a batch using the writer's context.
// On context cancellation, makes a final attempt with background context.
func (w *Writer) writeBatchWithRetry(findings []engine.Finding) error {
	return w.writeBatchWithRetryCtx(w.ctx, findings, true)
}

// drainQueue processes all remaining findings in the channel after shutdown.
// Uses context.Background() to ensure writes complete even after context cancellation.
// Called after the channel is closed by Start().
func (w *Writer) drainQueue() {
	// Use background context for drain - we want to finish writing
	ctx := context.Background()
	batch := make([]engine.Finding, 0, w.cfg.BatchSize)

	for finding := range w.findings {
		batch = append(batch, finding)

		if len(batch) >= w.cfg.BatchSize {
			w.writeBatchWithRetryCtx(ctx, batch, false) // false = don't need bg fallback, already using bg
			batch = batch[:0]
		}
	}

	// Write final partial batch
	if len(batch) > 0 {
		w.writeBatchWithRetryCtx(ctx, batch, false)
	}
}