// Package database provides session management for scan checkpointing and resume.
package database

import (
	"database/sql"
	"fmt"
	"strings"
	"time"
)

// Session status constants
const (
	SessionActive   = "active"
	SessionPaused   = "paused"
	SessionComplete = "complete"
)

// Session represents a scan session that groups targets and tracks progress.
// Sessions enable checkpointing long-running scans and resuming from interruption.
type Session struct {
	ID               int64
	Name             string
	Status           string // "active", "paused", "complete"
	TotalTargets     int
	CompletedTargets int
	TargetsFile      string // Path to original targets file (empty for single domain scans)
	StartedAt        time.Time
	UpdatedAt        time.Time
	CompletedAt      *time.Time // nil if not completed
}

// CreateSession creates a new session with the given name, target count, and optional targets file path.
// If the name already exists, it tries name-2, name-3, etc. up to 100 attempts.
// Returns the created session with ID populated.
func CreateSession(db *sql.DB, name string, totalTargets int, targetsFile string) (*Session, error) {
	if name == "" {
		return nil, fmt.Errorf("session name is required")
	}

	// Try original name first, then name-2, name-3, etc.
	var finalName string
	var id int64

	for attempt := 1; attempt <= 100; attempt++ {
		if attempt == 1 {
			finalName = name
		} else {
			finalName = fmt.Sprintf("%s-%d", name, attempt)
		}

		result, err := db.Exec(
			`INSERT INTO sessions (name, status, total_targets, targets_file) VALUES (?, ?, ?, ?)`,
			finalName, SessionActive, totalTargets, targetsFile,
		)
		if err != nil {
			// Check for UNIQUE constraint violation and retry with next suffix
			// SQLite error text contains "UNIQUE constraint failed"
			if attempt < 100 {
				continue // Try next suffix
			}
			return nil, fmt.Errorf("insert session after %d attempts: %w", attempt, err)
		}

		id, err = result.LastInsertId()
		if err != nil {
			return nil, fmt.Errorf("get last insert id: %w", err)
		}
		break
	}

	// Retrieve the created session to get all fields including timestamps
	return GetSession(db, id)
}

// GetSession retrieves a session by ID.
func GetSession(db *sql.DB, id int64) (*Session, error) {
	s := &Session{}
	var completedAt sql.NullTime

	err := db.QueryRow(
		`SELECT id, name, status, total_targets, completed_targets, targets_file,
		        started_at, updated_at, completed_at
		 FROM sessions WHERE id = ?`,
		id,
	).Scan(
		&s.ID, &s.Name, &s.Status, &s.TotalTargets, &s.CompletedTargets, &s.TargetsFile,
		&s.StartedAt, &s.UpdatedAt, &completedAt,
	)
	if err != nil {
		if err == sql.ErrNoRows {
			return nil, fmt.Errorf("session %d not found", id)
		}
		return nil, fmt.Errorf("query session %d: %w", id, err)
	}

	if completedAt.Valid {
		s.CompletedAt = &completedAt.Time
	}

	return s, nil
}

// GetSessionByName retrieves a session by exact name match.
func GetSessionByName(db *sql.DB, name string) (*Session, error) {
	s := &Session{}
	var completedAt sql.NullTime

	err := db.QueryRow(
		`SELECT id, name, status, total_targets, completed_targets, targets_file,
		        started_at, updated_at, completed_at
		 FROM sessions WHERE name = ?`,
		name,
	).Scan(
		&s.ID, &s.Name, &s.Status, &s.TotalTargets, &s.CompletedTargets, &s.TargetsFile,
		&s.StartedAt, &s.UpdatedAt, &completedAt,
	)
	if err != nil {
		if err == sql.ErrNoRows {
			return nil, fmt.Errorf("session %q not found", name)
		}
		return nil, fmt.Errorf("query session %q: %w", name, err)
	}

	if completedAt.Valid {
		s.CompletedAt = &completedAt.Time
	}

	return s, nil
}

// UpdateSessionProgress updates the completed target count and updated_at timestamp.
func UpdateSessionProgress(db *sql.DB, sessionID int64, completedTargets int) error {
	result, err := db.Exec(
		`UPDATE sessions
		 SET completed_targets = ?, updated_at = CURRENT_TIMESTAMP
		 WHERE id = ?`,
		completedTargets, sessionID,
	)
	if err != nil {
		return fmt.Errorf("update session progress: %w", err)
	}

	rows, _ := result.RowsAffected()
	if rows == 0 {
		return fmt.Errorf("session %d not found", sessionID)
	}

	return nil
}

// MarkSessionComplete marks a session as complete with completed_at timestamp.
func MarkSessionComplete(db *sql.DB, sessionID int64) error {
	result, err := db.Exec(
		`UPDATE sessions
		 SET status = ?, completed_at = CURRENT_TIMESTAMP, updated_at = CURRENT_TIMESTAMP
		 WHERE id = ?`,
		SessionComplete, sessionID,
	)
	if err != nil {
		return fmt.Errorf("mark session complete: %w", err)
	}

	rows, _ := result.RowsAffected()
	if rows == 0 {
		return fmt.Errorf("session %d not found", sessionID)
	}

	return nil
}

// MarkSessionPaused marks a session as paused (for checkpoint/resume).
func MarkSessionPaused(db *sql.DB, sessionID int64) error {
	result, err := db.Exec(
		`UPDATE sessions
		 SET status = ?, updated_at = CURRENT_TIMESTAMP
		 WHERE id = ?`,
		SessionPaused, sessionID,
	)
	if err != nil {
		return fmt.Errorf("mark session paused: %w", err)
	}

	rows, _ := result.RowsAffected()
	if rows == 0 {
		return fmt.Errorf("session %d not found", sessionID)
	}

	return nil
}

// GetIncompleteSessions returns all sessions that are active or paused,
// ordered by most recently updated first (for resume UI).
func GetIncompleteSessions(db *sql.DB) ([]Session, error) {
	rows, err := db.Query(
		`SELECT id, name, status, total_targets, completed_targets, targets_file,
		        started_at, updated_at, completed_at
		 FROM sessions
		 WHERE status IN (?, ?)
		 ORDER BY updated_at DESC`,
		SessionActive, SessionPaused,
	)
	if err != nil {
		return nil, fmt.Errorf("query incomplete sessions: %w", err)
	}
	defer rows.Close()

	var sessions []Session
	for rows.Next() {
		var s Session
		var completedAt sql.NullTime

		err := rows.Scan(
			&s.ID, &s.Name, &s.Status, &s.TotalTargets, &s.CompletedTargets, &s.TargetsFile,
			&s.StartedAt, &s.UpdatedAt, &completedAt,
		)
		if err != nil {
			return nil, fmt.Errorf("scan session row: %w", err)
		}

		if completedAt.Valid {
			s.CompletedAt = &completedAt.Time
		}

		sessions = append(sessions, s)
	}

	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate sessions: %w", err)
	}

	return sessions, nil
}

// RecordDomainComplete records that a domain has been fully scanned in a session.
// Uses INSERT OR IGNORE to handle duplicate recordings gracefully.
func RecordDomainComplete(db *sql.DB, sessionID int64, domain string) error {
	_, err := db.Exec(
		`INSERT OR IGNORE INTO session_progress (session_id, domain) VALUES (?, ?)`,
		sessionID, domain,
	)
	if err != nil {
		return fmt.Errorf("record domain complete: %w", err)
	}
	return nil
}

// GetCompletedDomains returns a map of domains that have been completed in the session.
// Returns map[domain]bool for O(1) lookup during resume.
func GetCompletedDomains(db *sql.DB, sessionID int64) (map[string]bool, error) {
	rows, err := db.Query(
		`SELECT domain FROM session_progress WHERE session_id = ?`,
		sessionID,
	)
	if err != nil {
		return nil, fmt.Errorf("query completed domains: %w", err)
	}
	defer rows.Close()

	completed := make(map[string]bool)
	for rows.Next() {
		var domain string
		if err := rows.Scan(&domain); err != nil {
			return nil, fmt.Errorf("scan domain: %w", err)
		}
		completed[domain] = true
	}

	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate domains: %w", err)
	}

	return completed, nil
}

// GetAllCompletedDomains returns all domains that have been scanned in ANY completed session.
// Used for cross-session deduplication to avoid re-scanning known targets.
// Returns map[normalizedDomain]bool for O(1) lookup.
// Domains are normalized: lowercased and www. prefix stripped.
func GetAllCompletedDomains(db *sql.DB) (map[string]bool, error) {
	rows, err := db.Query(`
		SELECT DISTINCT domain FROM session_progress
		WHERE session_id IN (SELECT id FROM sessions WHERE status = ?)
	`, SessionComplete)
	if err != nil {
		return nil, fmt.Errorf("query all completed domains: %w", err)
	}
	defer rows.Close()

	completed := make(map[string]bool)
	for rows.Next() {
		var domain string
		if err := rows.Scan(&domain); err != nil {
			return nil, fmt.Errorf("scan domain: %w", err)
		}
		// Normalize: lowercase and strip www. prefix
		normalized := NormalizeDomain(domain)
		completed[normalized] = true
	}

	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate domains: %w", err)
	}

	return completed, nil
}

// NormalizeDomain normalizes a domain for consistent deduplication matching.
// Strips www. prefix and lowercases.
func NormalizeDomain(domain string) string {
	domain = strings.ToLower(domain)
	domain = strings.TrimPrefix(domain, "www.")
	return domain
}