package storage

import (
	"context"
	"database/sql"
	"encoding/json"
	"fmt"
	"log/slog"
	"time"

	"elida/internal/instruction"

	_ "modernc.org/sqlite"
)

// unmarshalJSON safely unmarshals JSON from a nullable SQL string.
// Logs at debug level if unmarshal fails (indicates data corruption).
func unmarshalJSON(src sql.NullString, dst interface{}, field, id string) {
	if !src.Valid || src.String == "" {
		return
	}
	if err := json.Unmarshal([]byte(src.String), dst); err != nil {
		slog.Debug("failed to unmarshal JSON field", "field", field, "id", id, "error", err)
	}
}

// SessionRecord represents a historical session record
// CapturedRequest stores request/response content for session records
type CapturedRequest struct {
	Timestamp    time.Time `json:"timestamp"`
	Method       string    `json:"method"`
	Path         string    `json:"path"`
	RequestBody  string    `json:"request_body,omitempty"`
	ResponseBody string    `json:"response_body,omitempty"`
	StatusCode   int       `json:"status_code"`
}

// Violation records a policy violation for session records
type Violation struct {
	RuleName      string `json:"rule_name"`
	Description   string `json:"description"`
	Severity      string `json:"severity"`
	MatchedText   string `json:"matched_text,omitempty"`
	Action        string `json:"action"`
	EventCategory string `json:"event_category,omitempty"`
	FrameworkRef  string `json:"framework_ref,omitempty"`
	SourceRole    string `json:"source_role,omitempty"`
}

// TranscriptEntry represents a single utterance in a voice session
type TranscriptEntry struct {
	Timestamp time.Time `json:"timestamp"`
	Speaker   string    `json:"speaker"` // "user" or "assistant"
	Text      string    `json:"text"`
	IsFinal   bool      `json:"is_final"`
	Source    string    `json:"source"` // "stt", "tts", "text"
}

// VoiceSessionRecord represents a historical voice session record (CDR)
type VoiceSessionRecord struct {
	ID              string            `json:"id"`
	ParentSessionID string            `json:"parent_session_id"`
	State           string            `json:"state"`
	StartTime       time.Time         `json:"start_time"`
	AnswerTime      *time.Time        `json:"answer_time,omitempty"`
	EndTime         *time.Time        `json:"end_time,omitempty"`
	DurationMs      int64             `json:"duration_ms"`
	AudioDurationMs int64             `json:"audio_duration_ms"`
	TurnCount       int               `json:"turn_count"`
	Model           string            `json:"model,omitempty"`
	Voice           string            `json:"voice,omitempty"`
	Language        string            `json:"language,omitempty"`
	Protocol        string            `json:"protocol,omitempty"`
	AudioBytesIn    int64             `json:"audio_bytes_in"`
	AudioBytesOut   int64             `json:"audio_bytes_out"`
	Metadata        map[string]string `json:"metadata,omitempty"`
	Transcript      []TranscriptEntry `json:"transcript,omitempty"`
}

// TTSRequest represents a text-to-speech API call
type TTSRequest struct {
	ID            string    `json:"id"`
	SessionID     string    `json:"session_id"`
	Timestamp     time.Time `json:"timestamp"`
	Provider      string    `json:"provider"`       // "openai", "deepgram", "elevenlabs"
	Model         string    `json:"model"`          // e.g., "tts-1", "aura-asteria-en"
	Voice         string    `json:"voice"`          // e.g., "alloy", "nova"
	Text          string    `json:"text"`           // Text being synthesized
	TextLength    int       `json:"text_length"`    // Character count
	ResponseBytes int64     `json:"response_bytes"` // Audio response size
	DurationMs    int64     `json:"duration_ms"`    // Request duration
	StatusCode    int       `json:"status_code"`
}

type SessionRecord struct {
	ID              string            `json:"id"`
	State           string            `json:"state"`
	StartTime       time.Time         `json:"start_time"`
	EndTime         time.Time         `json:"end_time"`
	DurationMs      int64             `json:"duration_ms"`
	RequestCount    int               `json:"request_count"`
	BytesIn         int64             `json:"bytes_in"`
	BytesOut        int64             `json:"bytes_out"`
	Backend         string            `json:"backend"`
	ClientAddr      string            `json:"client_addr"`
	Metadata        map[string]string `json:"metadata,omitempty"`
	CapturedContent []CapturedRequest `json:"captured_content,omitempty"`
	Violations      []Violation       `json:"violations,omitempty"`
	Integrity       *SDRIntegrity     `json:"integrity,omitempty"`
}

// SQLiteStore provides persistent storage for session history
type SQLiteStore struct {
	db *sql.DB
}

// NewSQLiteStore creates a new SQLite-backed storage
func NewSQLiteStore(dbPath string) (*SQLiteStore, error) {
	db, err := sql.Open("sqlite", dbPath)
	if err != nil {
		return nil, fmt.Errorf("failed to open database: %w", err)
	}

	// SQLite supports only one writer at a time. Serialize all access to
	// prevent "database is locked" errors under concurrent load.
	db.SetMaxOpenConns(1)
	db.SetMaxIdleConns(1)
	db.SetConnMaxLifetime(0) // connections don't expire

	// Enable WAL mode for better concurrent performance
	if _, err := db.Exec("PRAGMA journal_mode=WAL"); err != nil {
		_ = db.Close()
		return nil, fmt.Errorf("failed to enable WAL mode: %w", err)
	}

	store := &SQLiteStore{db: db}

	if err := store.migrate(); err != nil {
		_ = db.Close()
		return nil, fmt.Errorf("failed to run migrations: %w", err)
	}

	slog.Info("SQLite storage initialized", "path", dbPath)
	return store, nil
}

// migrate creates the necessary tables
func (s *SQLiteStore) migrate() error {
	schema := `
	CREATE TABLE IF NOT EXISTS sessions (
		id TEXT PRIMARY KEY,
		state TEXT NOT NULL,
		start_time DATETIME NOT NULL,
		end_time DATETIME NOT NULL,
		duration_ms INTEGER NOT NULL,
		request_count INTEGER NOT NULL DEFAULT 0,
		bytes_in INTEGER NOT NULL DEFAULT 0,
		bytes_out INTEGER NOT NULL DEFAULT 0,
		backend TEXT NOT NULL,
		client_addr TEXT NOT NULL,
		metadata TEXT,
		captured_content TEXT,
		violations TEXT,
		created_at DATETIME DEFAULT CURRENT_TIMESTAMP
	);

	CREATE INDEX IF NOT EXISTS idx_sessions_start_time ON sessions(start_time);
	CREATE INDEX IF NOT EXISTS idx_sessions_end_time ON sessions(end_time);
	CREATE INDEX IF NOT EXISTS idx_sessions_state ON sessions(state);
	CREATE INDEX IF NOT EXISTS idx_sessions_backend ON sessions(backend);

	-- Voice session CDR table
	CREATE TABLE IF NOT EXISTS voice_sessions (
		id TEXT PRIMARY KEY,
		parent_session_id TEXT NOT NULL,
		state TEXT NOT NULL,
		start_time DATETIME NOT NULL,
		answer_time DATETIME,
		end_time DATETIME,
		duration_ms INTEGER NOT NULL DEFAULT 0,
		audio_duration_ms INTEGER NOT NULL DEFAULT 0,
		turn_count INTEGER NOT NULL DEFAULT 0,
		model TEXT,
		voice TEXT,
		language TEXT,
		protocol TEXT,
		audio_bytes_in INTEGER NOT NULL DEFAULT 0,
		audio_bytes_out INTEGER NOT NULL DEFAULT 0,
		metadata TEXT,
		transcript TEXT,
		created_at DATETIME DEFAULT CURRENT_TIMESTAMP
	);

	CREATE INDEX IF NOT EXISTS idx_voice_sessions_parent ON voice_sessions(parent_session_id);
	CREATE INDEX IF NOT EXISTS idx_voice_sessions_start_time ON voice_sessions(start_time);
	CREATE INDEX IF NOT EXISTS idx_voice_sessions_state ON voice_sessions(state);

	-- TTS (Text-to-Speech) request table
	CREATE TABLE IF NOT EXISTS tts_requests (
		id TEXT PRIMARY KEY,
		session_id TEXT NOT NULL,
		timestamp DATETIME NOT NULL,
		provider TEXT NOT NULL,
		model TEXT,
		voice TEXT,
		text TEXT,
		text_length INTEGER NOT NULL DEFAULT 0,
		response_bytes INTEGER NOT NULL DEFAULT 0,
		duration_ms INTEGER NOT NULL DEFAULT 0,
		status_code INTEGER NOT NULL DEFAULT 0,
		created_at DATETIME DEFAULT CURRENT_TIMESTAMP
	);

	CREATE INDEX IF NOT EXISTS idx_tts_session ON tts_requests(session_id);
	CREATE INDEX IF NOT EXISTS idx_tts_timestamp ON tts_requests(timestamp);
	CREATE INDEX IF NOT EXISTS idx_tts_provider ON tts_requests(provider);

	-- Events table (immutable audit log)
	CREATE TABLE IF NOT EXISTS events (
		id INTEGER PRIMARY KEY AUTOINCREMENT,
		timestamp DATETIME NOT NULL,
		event_type TEXT NOT NULL,
		session_id TEXT NOT NULL,
		severity TEXT,
		data TEXT NOT NULL,
		created_at DATETIME DEFAULT CURRENT_TIMESTAMP
	);

	CREATE INDEX IF NOT EXISTS idx_events_session ON events(session_id);
	CREATE INDEX IF NOT EXISTS idx_events_timestamp ON events(timestamp);
	CREATE INDEX IF NOT EXISTS idx_events_type ON events(event_type);
	CREATE INDEX IF NOT EXISTS idx_events_severity ON events(severity);

	-- SDR integrity table (Merkle root for finalized AI Session Detail Records)
	CREATE TABLE IF NOT EXISTS sdr_integrity (
		session_id TEXT PRIMARY KEY,
		algorithm TEXT NOT NULL,
		canonicalization_version TEXT NOT NULL,
		event_count INTEGER NOT NULL,
		root_hash TEXT NOT NULL,
		leaf_hashes_json TEXT NOT NULL,
		created_at DATETIME DEFAULT CURRENT_TIMESTAMP
	);

	CREATE INDEX IF NOT EXISTS idx_sdr_integrity_created_at ON sdr_integrity(created_at);
	`

	_, err := s.db.Exec(schema)
	if err != nil {
		return err
	}

	// Instruction file integrity table
	instructionSchema := `
	CREATE TABLE IF NOT EXISTS instruction_files (
		hash TEXT PRIMARY KEY,
		file_type TEXT NOT NULL,
		confidence TEXT NOT NULL,
		source_path TEXT,
		content TEXT NOT NULL,
		scan_status TEXT NOT NULL,
		scan_results TEXT,
		first_seen DATETIME NOT NULL,
		last_seen DATETIME NOT NULL,
		session_count INTEGER DEFAULT 1,
		prev_hash TEXT,
		diff TEXT,
		created_at DATETIME DEFAULT CURRENT_TIMESTAMP
	);

	CREATE INDEX IF NOT EXISTS idx_instruction_files_type ON instruction_files(file_type);
	CREATE INDEX IF NOT EXISTS idx_instruction_files_status ON instruction_files(scan_status);
	CREATE INDEX IF NOT EXISTS idx_instruction_files_first_seen ON instruction_files(first_seen);
	`
	if _, err := s.db.Exec(instructionSchema); err != nil {
		return fmt.Errorf("instruction_files migration: %w", err)
	}

	// Add instruction columns to sessions table (idempotent — ignore "duplicate column" errors)
	_, _ = s.db.Exec("ALTER TABLE sessions ADD COLUMN instruction_hash TEXT")
	_, _ = s.db.Exec("ALTER TABLE sessions ADD COLUMN instruction_file_type TEXT")

	return nil
}

// SaveSession saves a completed session record
func (s *SQLiteStore) SaveSession(record SessionRecord) error {
	metadata, err := json.Marshal(record.Metadata)
	if err != nil {
		metadata = []byte("{}")
	}

	capturedContent, err := json.Marshal(record.CapturedContent)
	if err != nil {
		capturedContent = []byte("[]")
	}

	violations, err := json.Marshal(record.Violations)
	if err != nil {
		violations = []byte("[]")
	}

	_, err = s.db.Exec(`
		INSERT OR REPLACE INTO sessions
		(id, state, start_time, end_time, duration_ms, request_count, bytes_in, bytes_out, backend, client_addr, metadata, captured_content, violations)
		VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
		record.ID,
		record.State,
		record.StartTime,
		record.EndTime,
		record.DurationMs,
		record.RequestCount,
		record.BytesIn,
		record.BytesOut,
		record.Backend,
		record.ClientAddr,
		string(metadata),
		string(capturedContent),
		string(violations),
	)
	if err != nil {
		return fmt.Errorf("failed to save session: %w", err)
	}

	slog.Debug("session saved to history",
		"session_id", record.ID,
		"state", record.State,
		"captures", len(record.CapturedContent),
		"violations", len(record.Violations),
	)
	return nil
}

// GetSession retrieves a session by ID
func (s *SQLiteStore) GetSession(id string) (*SessionRecord, error) {
	return s.GetSessionCtx(context.Background(), id)
}

// GetSessionCtx retrieves a session by ID using the provided context.
func (s *SQLiteStore) GetSessionCtx(ctx context.Context, id string) (*SessionRecord, error) {
	row := s.db.QueryRowContext(ctx, `
		SELECT id, state, start_time, end_time, duration_ms, request_count, bytes_in, bytes_out, backend, client_addr, metadata, captured_content, violations
		FROM sessions WHERE id = ?`, id)

	var record SessionRecord
	var metadataStr, capturedStr, violationsStr sql.NullString
	err := row.Scan(
		&record.ID,
		&record.State,
		&record.StartTime,
		&record.EndTime,
		&record.DurationMs,
		&record.RequestCount,
		&record.BytesIn,
		&record.BytesOut,
		&record.Backend,
		&record.ClientAddr,
		&metadataStr,
		&capturedStr,
		&violationsStr,
	)
	if err == sql.ErrNoRows {
		return nil, nil
	}
	if err != nil {
		return nil, fmt.Errorf("failed to get session: %w", err)
	}

	unmarshalJSON(metadataStr, &record.Metadata, "metadata", record.ID)
	unmarshalJSON(capturedStr, &record.CapturedContent, "captured_content", record.ID)
	unmarshalJSON(violationsStr, &record.Violations, "violations", record.ID)

	integrity, integrityErr := s.GetSDRIntegrity(ctx, record.ID)
	if integrityErr != nil {
		slog.Debug("failed to load SDR integrity metadata", "session_id", record.ID, "error", integrityErr)
	} else {
		record.Integrity = integrity
	}

	return &record, nil
}

// ListSessionsOptions contains options for listing sessions
type ListSessionsOptions struct {
	Limit   int
	Offset  int
	State   string // Filter by state
	Backend string // Filter by backend
	Since   *time.Time
	Until   *time.Time
}

// CountSessions returns the total number of sessions matching the filter options (ignoring Limit/Offset)
func (s *SQLiteStore) CountSessions(opts ListSessionsOptions) (int, error) {
	query := `SELECT COUNT(*) FROM sessions WHERE 1=1`
	args := []interface{}{}

	if opts.State != "" {
		query += " AND state = ?"
		args = append(args, opts.State)
	}
	if opts.Backend != "" {
		query += " AND backend = ?"
		args = append(args, opts.Backend)
	}
	if opts.Since != nil {
		query += " AND start_time >= ?"
		args = append(args, *opts.Since)
	}
	if opts.Until != nil {
		query += " AND start_time <= ?"
		args = append(args, *opts.Until)
	}

	var count int
	if err := s.db.QueryRow(query, args...).Scan(&count); err != nil {
		return 0, fmt.Errorf("failed to count sessions: %w", err)
	}
	return count, nil
}

// ListSessions retrieves sessions with filtering and pagination
func (s *SQLiteStore) ListSessions(opts ListSessionsOptions) ([]SessionRecord, error) {
	query := `
		SELECT id, state, start_time, end_time, duration_ms, request_count, bytes_in, bytes_out, backend, client_addr, metadata, captured_content, violations
		FROM sessions WHERE 1=1`

	args := []interface{}{}

	if opts.State != "" {
		query += " AND state = ?"
		args = append(args, opts.State)
	}
	if opts.Backend != "" {
		query += " AND backend = ?"
		args = append(args, opts.Backend)
	}
	if opts.Since != nil {
		query += " AND start_time >= ?"
		args = append(args, *opts.Since)
	}
	if opts.Until != nil {
		query += " AND start_time <= ?"
		args = append(args, *opts.Until)
	}

	query += " ORDER BY start_time DESC"

	if opts.Limit > 0 {
		query += " LIMIT ?"
		args = append(args, opts.Limit)
	}
	if opts.Offset > 0 {
		query += " OFFSET ?"
		args = append(args, opts.Offset)
	}

	rows, err := s.db.Query(query, args...)
	if err != nil {
		return nil, fmt.Errorf("failed to list sessions: %w", err)
	}
	defer func() { _ = rows.Close() }()

	var records []SessionRecord
	for rows.Next() {
		var record SessionRecord
		var metadataStr, capturedStr, violationsStr sql.NullString
		err := rows.Scan(
			&record.ID,
			&record.State,
			&record.StartTime,
			&record.EndTime,
			&record.DurationMs,
			&record.RequestCount,
			&record.BytesIn,
			&record.BytesOut,
			&record.Backend,
			&record.ClientAddr,
			&metadataStr,
			&capturedStr,
			&violationsStr,
		)
		if err != nil {
			return nil, fmt.Errorf("failed to scan session: %w", err)
		}

		unmarshalJSON(metadataStr, &record.Metadata, "metadata", record.ID)
		unmarshalJSON(capturedStr, &record.CapturedContent, "captured_content", record.ID)
		unmarshalJSON(violationsStr, &record.Violations, "violations", record.ID)

		records = append(records, record)
	}

	return records, nil
}

// Stats represents aggregate statistics
type Stats struct {
	TotalSessions     int64            `json:"total_sessions"`
	TotalRequests     int64            `json:"total_requests"`
	TotalBytesIn      int64            `json:"total_bytes_in"`
	TotalBytesOut     int64            `json:"total_bytes_out"`
	AvgDurationMs     float64          `json:"avg_duration_ms"`
	AvgRequestCount   float64          `json:"avg_request_count"`
	SessionsByState   map[string]int64 `json:"sessions_by_state"`
	SessionsByBackend map[string]int64 `json:"sessions_by_backend"`
}

// GetStats retrieves aggregate statistics
func (s *SQLiteStore) GetStats(since *time.Time) (*Stats, error) {
	tx, err := s.db.Begin()
	if err != nil {
		return nil, fmt.Errorf("failed to begin transaction: %w", err)
	}
	defer func() { _ = tx.Rollback() }()

	stats := &Stats{
		SessionsByState:   make(map[string]int64),
		SessionsByBackend: make(map[string]int64),
	}

	// Build base WHERE clause
	whereClause := "WHERE 1=1"
	args := []interface{}{}
	if since != nil {
		whereClause += " AND start_time >= ?"
		args = append(args, *since)
	}

	// Get aggregate stats
	row := tx.QueryRow(fmt.Sprintf(`
		SELECT
			COUNT(*),
			COALESCE(SUM(request_count), 0),
			COALESCE(SUM(bytes_in), 0),
			COALESCE(SUM(bytes_out), 0),
			COALESCE(AVG(duration_ms), 0),
			COALESCE(AVG(request_count), 0)
		FROM sessions %s`, whereClause), args...)

	if err = row.Scan(
		&stats.TotalSessions,
		&stats.TotalRequests,
		&stats.TotalBytesIn,
		&stats.TotalBytesOut,
		&stats.AvgDurationMs,
		&stats.AvgRequestCount,
	); err != nil {
		return nil, fmt.Errorf("failed to get aggregate stats: %w", err)
	}

	// Get sessions by state
	rows, err := tx.Query(fmt.Sprintf(`
		SELECT state, COUNT(*) FROM sessions %s GROUP BY state`, whereClause), args...)
	if err != nil {
		return nil, fmt.Errorf("failed to get state stats: %w", err)
	}
	defer func() { _ = rows.Close() }()

	for rows.Next() {
		var state string
		var count int64
		if scanErr := rows.Scan(&state, &count); scanErr != nil {
			return nil, scanErr
		}
		stats.SessionsByState[state] = count
	}

	// Get sessions by backend
	rows, err = tx.Query(fmt.Sprintf(`
		SELECT backend, COUNT(*) FROM sessions %s GROUP BY backend`, whereClause), args...)
	if err != nil {
		return nil, fmt.Errorf("failed to get backend stats: %w", err)
	}
	defer func() { _ = rows.Close() }()

	for rows.Next() {
		var backend string
		var count int64
		if err := rows.Scan(&backend, &count); err != nil {
			return nil, err
		}
		stats.SessionsByBackend[backend] = count
	}

	return stats, nil
}

// TimeSeriesPoint represents a point in a time series
type TimeSeriesPoint struct {
	Timestamp    time.Time `json:"timestamp"`
	SessionCount int64     `json:"session_count"`
	RequestCount int64     `json:"request_count"`
	BytesIn      int64     `json:"bytes_in"`
	BytesOut     int64     `json:"bytes_out"`
}

// GetTimeSeries retrieves time series data for the dashboard
func (s *SQLiteStore) GetTimeSeries(since time.Time, interval string) ([]TimeSeriesPoint, error) {
	// SQLite date truncation based on interval
	// Use datetime() to normalize the timestamp format first
	var dateTrunc string
	switch interval {
	case "hour":
		dateTrunc = "strftime('%Y-%m-%d %H:00:00', datetime(start_time))"
	case "day":
		dateTrunc = "strftime('%Y-%m-%d', datetime(start_time))"
	case "minute":
		dateTrunc = "strftime('%Y-%m-%d %H:%M:00', datetime(start_time))"
	default:
		dateTrunc = "strftime('%Y-%m-%d %H:00:00', datetime(start_time))" // default to hourly
	}

	// #nosec G201 -- dateTrunc is safe, only set from hardcoded switch cases above, never user input
	query := fmt.Sprintf(` // nosemgrep: string-formatted-query
		SELECT
			COALESCE(%s, 'unknown') as bucket,
			COUNT(*) as session_count,
			COALESCE(SUM(request_count), 0) as request_count,
			COALESCE(SUM(bytes_in), 0) as bytes_in,
			COALESCE(SUM(bytes_out), 0) as bytes_out
		FROM sessions
		WHERE start_time >= ?
		GROUP BY bucket
		HAVING bucket != 'unknown'
		ORDER BY bucket ASC`, dateTrunc)

	rows, err := s.db.Query(query, since)
	if err != nil {
		return nil, fmt.Errorf("failed to get time series: %w", err)
	}
	defer func() { _ = rows.Close() }()

	var points []TimeSeriesPoint
	for rows.Next() {
		var point TimeSeriesPoint
		var bucket string
		if err := rows.Scan(&bucket, &point.SessionCount, &point.RequestCount, &point.BytesIn, &point.BytesOut); err != nil {
			return nil, err
		}
		point.Timestamp, _ = time.Parse("2006-01-02 15:04:05", bucket)
		if point.Timestamp.IsZero() {
			point.Timestamp, _ = time.Parse("2006-01-02", bucket)
		}
		points = append(points, point)
	}

	return points, nil
}

// Cleanup removes old session records
func (s *SQLiteStore) Cleanup(retentionDays int) (int64, error) {
	cutoff := time.Now().AddDate(0, 0, -retentionDays)
	result, err := s.db.Exec("DELETE FROM sessions WHERE end_time < ?", cutoff)
	if err != nil {
		return 0, fmt.Errorf("failed to cleanup old sessions: %w", err)
	}

	deleted, _ := result.RowsAffected()
	if deleted > 0 {
		slog.Info("cleaned up old sessions", "deleted", deleted, "retention_days", retentionDays)
	}
	return deleted, nil
}

// DB returns the underlying database handle for shared use by subsystems.
func (s *SQLiteStore) DB() *sql.DB {
	return s.db
}

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

// SaveVoiceSession saves a voice session record (CDR with transcript)
func (s *SQLiteStore) SaveVoiceSession(record VoiceSessionRecord) error {
	metadata, err := json.Marshal(record.Metadata)
	if err != nil {
		metadata = []byte("{}")
	}

	transcript, err := json.Marshal(record.Transcript)
	if err != nil {
		transcript = []byte("[]")
	}

	_, err = s.db.Exec(`
		INSERT OR REPLACE INTO voice_sessions
		(id, parent_session_id, state, start_time, answer_time, end_time, duration_ms, audio_duration_ms, turn_count, model, voice, language, protocol, audio_bytes_in, audio_bytes_out, metadata, transcript)
		VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
		record.ID,
		record.ParentSessionID,
		record.State,
		record.StartTime,
		record.AnswerTime,
		record.EndTime,
		record.DurationMs,
		record.AudioDurationMs,
		record.TurnCount,
		record.Model,
		record.Voice,
		record.Language,
		record.Protocol,
		record.AudioBytesIn,
		record.AudioBytesOut,
		string(metadata),
		string(transcript),
	)
	if err != nil {
		return fmt.Errorf("failed to save voice session: %w", err)
	}

	slog.Debug("voice session saved to history",
		"voice_session_id", record.ID,
		"parent_session_id", record.ParentSessionID,
		"state", record.State,
		"transcript_entries", len(record.Transcript),
	)
	return nil
}

// GetVoiceSession retrieves a voice session by ID
func (s *SQLiteStore) GetVoiceSession(id string) (*VoiceSessionRecord, error) {
	row := s.db.QueryRow(`
		SELECT id, parent_session_id, state, start_time, answer_time, end_time, duration_ms, audio_duration_ms, turn_count, model, voice, language, protocol, audio_bytes_in, audio_bytes_out, metadata, transcript
		FROM voice_sessions WHERE id = ?`, id)

	var record VoiceSessionRecord
	var answerTime, endTime sql.NullTime
	var model, voice, language, protocol sql.NullString
	var metadataStr, transcriptStr sql.NullString

	err := row.Scan(
		&record.ID,
		&record.ParentSessionID,
		&record.State,
		&record.StartTime,
		&answerTime,
		&endTime,
		&record.DurationMs,
		&record.AudioDurationMs,
		&record.TurnCount,
		&model,
		&voice,
		&language,
		&protocol,
		&record.AudioBytesIn,
		&record.AudioBytesOut,
		&metadataStr,
		&transcriptStr,
	)
	if err == sql.ErrNoRows {
		return nil, nil
	}
	if err != nil {
		return nil, fmt.Errorf("failed to get voice session: %w", err)
	}

	if answerTime.Valid {
		record.AnswerTime = &answerTime.Time
	}
	if endTime.Valid {
		record.EndTime = &endTime.Time
	}
	if model.Valid {
		record.Model = model.String
	}
	if voice.Valid {
		record.Voice = voice.String
	}
	if language.Valid {
		record.Language = language.String
	}
	if protocol.Valid {
		record.Protocol = protocol.String
	}
	unmarshalJSON(metadataStr, &record.Metadata, "metadata", record.ID)
	unmarshalJSON(transcriptStr, &record.Transcript, "transcript", record.ID)

	return &record, nil
}

// ListVoiceSessionsOptions contains options for listing voice sessions
type ListVoiceSessionsOptions struct {
	Limit           int
	Offset          int
	ParentSessionID string // Filter by parent HTTP session
	State           string
	Since           *time.Time
	Until           *time.Time
}

// ListVoiceSessions retrieves voice sessions with filtering and pagination
func (s *SQLiteStore) ListVoiceSessions(opts ListVoiceSessionsOptions) ([]VoiceSessionRecord, error) {
	query := `
		SELECT id, parent_session_id, state, start_time, answer_time, end_time, duration_ms, audio_duration_ms, turn_count, model, voice, language, protocol, audio_bytes_in, audio_bytes_out, metadata, transcript
		FROM voice_sessions WHERE 1=1`

	args := []interface{}{}

	if opts.ParentSessionID != "" {
		query += " AND parent_session_id = ?"
		args = append(args, opts.ParentSessionID)
	}
	if opts.State != "" {
		query += " AND state = ?"
		args = append(args, opts.State)
	}
	if opts.Since != nil {
		query += " AND start_time >= ?"
		args = append(args, *opts.Since)
	}
	if opts.Until != nil {
		query += " AND start_time <= ?"
		args = append(args, *opts.Until)
	}

	query += " ORDER BY start_time DESC"

	if opts.Limit > 0 {
		query += " LIMIT ?"
		args = append(args, opts.Limit)
	}
	if opts.Offset > 0 {
		query += " OFFSET ?"
		args = append(args, opts.Offset)
	}

	rows, err := s.db.Query(query, args...)
	if err != nil {
		return nil, fmt.Errorf("failed to list voice sessions: %w", err)
	}
	defer func() { _ = rows.Close() }()

	var records []VoiceSessionRecord
	for rows.Next() {
		var record VoiceSessionRecord
		var answerTime, endTime sql.NullTime
		var model, voice, language, protocol sql.NullString
		var metadataStr, transcriptStr sql.NullString

		err := rows.Scan(
			&record.ID,
			&record.ParentSessionID,
			&record.State,
			&record.StartTime,
			&answerTime,
			&endTime,
			&record.DurationMs,
			&record.AudioDurationMs,
			&record.TurnCount,
			&model,
			&voice,
			&language,
			&protocol,
			&record.AudioBytesIn,
			&record.AudioBytesOut,
			&metadataStr,
			&transcriptStr,
		)
		if err != nil {
			return nil, fmt.Errorf("failed to scan voice session: %w", err)
		}

		if answerTime.Valid {
			record.AnswerTime = &answerTime.Time
		}
		if endTime.Valid {
			record.EndTime = &endTime.Time
		}
		if model.Valid {
			record.Model = model.String
		}
		if voice.Valid {
			record.Voice = voice.String
		}
		if language.Valid {
			record.Language = language.String
		}
		if protocol.Valid {
			record.Protocol = protocol.String
		}
		unmarshalJSON(metadataStr, &record.Metadata, "metadata", record.ID)
		unmarshalJSON(transcriptStr, &record.Transcript, "transcript", record.ID)

		records = append(records, record)
	}

	return records, nil
}

// GetVoiceSessionsByParent retrieves all voice sessions for a parent HTTP session
func (s *SQLiteStore) GetVoiceSessionsByParent(parentSessionID string) ([]VoiceSessionRecord, error) {
	return s.ListVoiceSessions(ListVoiceSessionsOptions{
		ParentSessionID: parentSessionID,
	})
}

// VoiceStats represents aggregate statistics for voice sessions
type VoiceStats struct {
	TotalSessions      int64            `json:"total_sessions"`
	TotalAudioMs       int64            `json:"total_audio_ms"`
	TotalTurns         int64            `json:"total_turns"`
	AvgDurationMs      float64          `json:"avg_duration_ms"`
	AvgTurnsPerSession float64          `json:"avg_turns_per_session"`
	SessionsByState    map[string]int64 `json:"sessions_by_state"`
	SessionsByModel    map[string]int64 `json:"sessions_by_model"`
}

// GetVoiceStats retrieves aggregate statistics for voice sessions
func (s *SQLiteStore) GetVoiceStats(since *time.Time) (*VoiceStats, error) {
	tx, err := s.db.Begin()
	if err != nil {
		return nil, fmt.Errorf("failed to begin transaction: %w", err)
	}
	defer func() { _ = tx.Rollback() }()

	stats := &VoiceStats{
		SessionsByState: make(map[string]int64),
		SessionsByModel: make(map[string]int64),
	}

	whereClause := "WHERE 1=1"
	args := []interface{}{}
	if since != nil {
		whereClause += " AND start_time >= ?"
		args = append(args, *since)
	}

	row := tx.QueryRow(fmt.Sprintf(`
		SELECT
			COUNT(*),
			COALESCE(SUM(audio_duration_ms), 0),
			COALESCE(SUM(turn_count), 0),
			COALESCE(AVG(duration_ms), 0),
			COALESCE(AVG(turn_count), 0)
		FROM voice_sessions %s`, whereClause), args...)

	if err = row.Scan(
		&stats.TotalSessions,
		&stats.TotalAudioMs,
		&stats.TotalTurns,
		&stats.AvgDurationMs,
		&stats.AvgTurnsPerSession,
	); err != nil {
		return nil, fmt.Errorf("failed to get voice stats: %w", err)
	}

	// Sessions by state
	rows, err := tx.Query(fmt.Sprintf(`
		SELECT state, COUNT(*) FROM voice_sessions %s GROUP BY state`, whereClause), args...)
	if err != nil {
		return nil, fmt.Errorf("failed to get voice state stats: %w", err)
	}
	defer func() { _ = rows.Close() }()

	for rows.Next() {
		var state string
		var count int64
		if scanErr := rows.Scan(&state, &count); scanErr != nil {
			return nil, scanErr
		}
		stats.SessionsByState[state] = count
	}

	// Sessions by model
	rows, err = tx.Query(fmt.Sprintf(`
		SELECT COALESCE(model, 'unknown'), COUNT(*) FROM voice_sessions %s GROUP BY model`, whereClause), args...)
	if err != nil {
		return nil, fmt.Errorf("failed to get voice model stats: %w", err)
	}
	defer func() { _ = rows.Close() }()

	for rows.Next() {
		var model string
		var count int64
		if err := rows.Scan(&model, &count); err != nil {
			return nil, err
		}
		stats.SessionsByModel[model] = count
	}

	return stats, nil
}

// SaveTTSRequest saves a TTS request record
func (s *SQLiteStore) SaveTTSRequest(req TTSRequest) error {
	_, err := s.db.Exec(`
		INSERT INTO tts_requests
		(id, session_id, timestamp, provider, model, voice, text, text_length, response_bytes, duration_ms, status_code)
		VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
		req.ID,
		req.SessionID,
		req.Timestamp,
		req.Provider,
		req.Model,
		req.Voice,
		req.Text,
		req.TextLength,
		req.ResponseBytes,
		req.DurationMs,
		req.StatusCode,
	)
	if err != nil {
		return fmt.Errorf("failed to save TTS request: %w", err)
	}

	slog.Debug("TTS request saved",
		"id", req.ID,
		"session_id", req.SessionID,
		"provider", req.Provider,
		"text_length", req.TextLength,
	)
	return nil
}

// ListTTSRequestsOptions contains options for listing TTS requests
type ListTTSRequestsOptions struct {
	Limit     int
	Offset    int
	SessionID string
	Provider  string
	Since     *time.Time
	Until     *time.Time
}

// ListTTSRequests retrieves TTS requests with filtering
func (s *SQLiteStore) ListTTSRequests(opts ListTTSRequestsOptions) ([]TTSRequest, error) {
	query := `
		SELECT id, session_id, timestamp, provider, model, voice, text, text_length, response_bytes, duration_ms, status_code
		FROM tts_requests WHERE 1=1`

	args := []interface{}{}

	if opts.SessionID != "" {
		query += " AND session_id = ?"
		args = append(args, opts.SessionID)
	}
	if opts.Provider != "" {
		query += " AND provider = ?"
		args = append(args, opts.Provider)
	}
	if opts.Since != nil {
		query += " AND timestamp >= ?"
		args = append(args, *opts.Since)
	}
	if opts.Until != nil {
		query += " AND timestamp <= ?"
		args = append(args, *opts.Until)
	}

	query += " ORDER BY timestamp DESC"

	if opts.Limit > 0 {
		query += " LIMIT ?"
		args = append(args, opts.Limit)
	}
	if opts.Offset > 0 {
		query += " OFFSET ?"
		args = append(args, opts.Offset)
	}

	rows, err := s.db.Query(query, args...)
	if err != nil {
		return nil, fmt.Errorf("failed to list TTS requests: %w", err)
	}
	defer func() { _ = rows.Close() }()

	var requests []TTSRequest
	for rows.Next() {
		var req TTSRequest
		var model, voice, text sql.NullString

		err := rows.Scan(
			&req.ID,
			&req.SessionID,
			&req.Timestamp,
			&req.Provider,
			&model,
			&voice,
			&text,
			&req.TextLength,
			&req.ResponseBytes,
			&req.DurationMs,
			&req.StatusCode,
		)
		if err != nil {
			return nil, fmt.Errorf("failed to scan TTS request: %w", err)
		}

		if model.Valid {
			req.Model = model.String
		}
		if voice.Valid {
			req.Voice = voice.String
		}
		if text.Valid {
			req.Text = text.String
		}

		requests = append(requests, req)
	}

	return requests, nil
}

// GetTTSRequestsBySession retrieves all TTS requests for a session
func (s *SQLiteStore) GetTTSRequestsBySession(sessionID string) ([]TTSRequest, error) {
	return s.ListTTSRequests(ListTTSRequestsOptions{
		SessionID: sessionID,
	})
}

// SaveInstructionFile inserts or replaces an instruction file record.
func (s *SQLiteStore) SaveInstructionFile(record instruction.Record) error {
	scanResults, err := json.Marshal(record.ScanResults)
	if err != nil {
		scanResults = []byte("[]")
	}

	_, err = s.db.Exec(`
		INSERT OR REPLACE INTO instruction_files
		(hash, file_type, confidence, source_path, content, scan_status, scan_results, first_seen, last_seen, session_count, prev_hash, diff)
		VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
		record.Hash, record.FileType, record.Confidence, record.SourcePath,
		record.Content, record.ScanStatus, string(scanResults),
		record.FirstSeen, record.LastSeen, record.SessionCount,
		record.PrevHash, record.Diff,
	)
	return err
}

// GetInstructionFile retrieves an instruction file by hash.
func (s *SQLiteStore) GetInstructionFile(hash string) (*instruction.Record, error) {
	row := s.db.QueryRow(`
		SELECT hash, file_type, confidence, source_path, content, scan_status, scan_results,
		       first_seen, last_seen, session_count, prev_hash, diff
		FROM instruction_files WHERE hash = ?`, hash)

	var r instruction.Record
	var scanResultsStr, sourcePath, prevHash, diff sql.NullString
	err := row.Scan(&r.Hash, &r.FileType, &r.Confidence, &sourcePath,
		&r.Content, &r.ScanStatus, &scanResultsStr,
		&r.FirstSeen, &r.LastSeen, &r.SessionCount, &prevHash, &diff)
	if err == sql.ErrNoRows {
		return nil, nil
	}
	if err != nil {
		return nil, fmt.Errorf("get instruction file: %w", err)
	}

	if sourcePath.Valid {
		r.SourcePath = sourcePath.String
	}
	if prevHash.Valid {
		r.PrevHash = prevHash.String
	}
	if diff.Valid {
		r.Diff = diff.String
	}
	unmarshalJSON(scanResultsStr, &r.ScanResults, "scan_results", r.Hash)
	return &r, nil
}

// IncrementInstructionFileSessionCount bumps the session count and updates last_seen.
func (s *SQLiteStore) IncrementInstructionFileSessionCount(hash string, lastSeen time.Time) error {
	_, err := s.db.Exec(`
		UPDATE instruction_files SET session_count = session_count + 1, last_seen = ?
		WHERE hash = ?`, lastSeen, hash)
	return err
}

// InstructionEvent wraps an instruction integrity event for the events table.
type InstructionEvent struct {
	Timestamp time.Time
	EventType string
	SessionID string
	Severity  string
	Data      map[string]interface{}
}

// SaveInstructionEvent writes an instruction integrity event to the events table.
func (s *SQLiteStore) SaveInstructionEvent(evt InstructionEvent) error {
	data, err := json.Marshal(evt.Data)
	if err != nil {
		data = []byte("{}")
	}
	_, err = s.db.Exec(`
		INSERT INTO events (timestamp, event_type, session_id, severity, data)
		VALUES (?, ?, ?, ?, ?)`,
		evt.Timestamp, evt.EventType, evt.SessionID, evt.Severity, string(data),
	)
	return err
}

// ListInstructionFiles returns instruction file records, optionally filtered.
func (s *SQLiteStore) ListInstructionFiles(fileType, scanStatus string) ([]instruction.Record, error) {
	query := `SELECT hash, file_type, confidence, source_path, content, scan_status, scan_results,
	                 first_seen, last_seen, session_count, prev_hash, diff
	          FROM instruction_files WHERE 1=1`
	args := []interface{}{}

	if fileType != "" {
		query += " AND file_type = ?"
		args = append(args, fileType)
	}
	if scanStatus != "" {
		query += " AND scan_status = ?"
		args = append(args, scanStatus)
	}
	query += " ORDER BY last_seen DESC"

	rows, err := s.db.Query(query, args...)
	if err != nil {
		return nil, err
	}
	defer func() { _ = rows.Close() }()

	var records []instruction.Record
	for rows.Next() {
		var r instruction.Record
		var scanResultsStr, sourcePath, prevHash, diff sql.NullString
		if err := rows.Scan(&r.Hash, &r.FileType, &r.Confidence, &sourcePath,
			&r.Content, &r.ScanStatus, &scanResultsStr,
			&r.FirstSeen, &r.LastSeen, &r.SessionCount, &prevHash, &diff); err != nil {
			return nil, err
		}
		if sourcePath.Valid {
			r.SourcePath = sourcePath.String
		}
		if prevHash.Valid {
			r.PrevHash = prevHash.String
		}
		if diff.Valid {
			r.Diff = diff.String
		}
		unmarshalJSON(scanResultsStr, &r.ScanResults, "scan_results", r.Hash)
		records = append(records, r)
	}
	return records, rows.Err()
}

// TTSStats represents aggregate statistics for TTS requests
type TTSStats struct {
	TotalRequests      int64            `json:"total_requests"`
	TotalCharacters    int64            `json:"total_characters"`
	TotalResponseBytes int64            `json:"total_response_bytes"`
	AvgTextLength      float64          `json:"avg_text_length"`
	RequestsByProvider map[string]int64 `json:"requests_by_provider"`
	RequestsByVoice    map[string]int64 `json:"requests_by_voice"`
}

// GetTTSStats retrieves aggregate TTS statistics
func (s *SQLiteStore) GetTTSStats(since *time.Time) (*TTSStats, error) {
	tx, err := s.db.Begin()
	if err != nil {
		return nil, fmt.Errorf("failed to begin transaction: %w", err)
	}
	defer func() { _ = tx.Rollback() }()

	stats := &TTSStats{
		RequestsByProvider: make(map[string]int64),
		RequestsByVoice:    make(map[string]int64),
	}

	whereClause := "WHERE 1=1"
	args := []interface{}{}
	if since != nil {
		whereClause += " AND timestamp >= ?"
		args = append(args, *since)
	}

	row := tx.QueryRow(fmt.Sprintf(`
		SELECT
			COUNT(*),
			COALESCE(SUM(text_length), 0),
			COALESCE(SUM(response_bytes), 0),
			COALESCE(AVG(text_length), 0)
		FROM tts_requests %s`, whereClause), args...)

	if err = row.Scan(
		&stats.TotalRequests,
		&stats.TotalCharacters,
		&stats.TotalResponseBytes,
		&stats.AvgTextLength,
	); err != nil {
		return nil, fmt.Errorf("failed to get TTS stats: %w", err)
	}

	// Requests by provider
	rows, err := tx.Query(fmt.Sprintf(`
		SELECT provider, COUNT(*) FROM tts_requests %s GROUP BY provider`, whereClause), args...)
	if err != nil {
		return nil, fmt.Errorf("failed to get TTS provider stats: %w", err)
	}
	defer func() { _ = rows.Close() }()

	for rows.Next() {
		var provider string
		var count int64
		if scanErr := rows.Scan(&provider, &count); scanErr != nil {
			return nil, scanErr
		}
		stats.RequestsByProvider[provider] = count
	}

	// Requests by voice
	rows, err = tx.Query(fmt.Sprintf(`
		SELECT COALESCE(voice, 'unknown'), COUNT(*) FROM tts_requests %s GROUP BY voice`, whereClause), args...)
	if err != nil {
		return nil, fmt.Errorf("failed to get TTS voice stats: %w", err)
	}
	defer func() { _ = rows.Close() }()

	for rows.Next() {
		var voice string
		var count int64
		if err := rows.Scan(&voice, &count); err != nil {
			return nil, err
		}
		stats.RequestsByVoice[voice] = count
	}

	return stats, nil
}
