// Package queue is the agent's local SQLite store: outbound result queue,
// event log and worklist cache. Pure-Go SQLite (modernc.org/sqlite) — no cgo,
// so the agent builds as a single static binary.
package queue

import (
	"database/sql"
	"encoding/json"
	"fmt"
	"time"

	"github.com/google/uuid"
	_ "modernc.org/sqlite"
)

// QueueItem is one queued raw device message awaiting cloud sync.
type QueueItem struct {
	ID          int64
	UUID        string
	Vendor      string
	DeviceID    *int64
	RawMessage  string
	ReceivedAt  time.Time
	Status      string // pending | synced | failed
	Attempts    int
	LastError   string
	CreatedAt   time.Time
	SyncedAt    *time.Time
}

// Event is one log/error event awaiting batch push.
type Event struct {
	ID        int64
	Type      string                 // "log" | "error"
	Level     string                 // info | success | warning | error
	Category  string
	DeviceID  *int64
	Vendor    string
	DeviceIP  string
	SampleNo  string
	Message   string
	Context   map[string]interface{}
	Status    string                 // pending | synced
	CreatedAt time.Time
}

// Store wraps the embedded SQLite database.
type Store struct {
	db *sql.DB
}

// Open creates/opens the queue database at path and applies the schema.
func Open(path string) (*Store, error) {
	db, err := sql.Open("sqlite", path+"?_pragma=journal_mode(WAL)&_pragma=busy_timeout(5000)")
	if err != nil {
		return nil, fmt.Errorf("open sqlite %s: %w", path, err)
	}
	s := &Store{db: db}
	if err := s.migrate(); err != nil {
		_ = db.Close()
		return nil, err
	}
	return s, nil
}

func (s *Store) Close() error { return s.db.Close() }

func (s *Store) migrate() error {
	stmts := []string{
		`CREATE TABLE IF NOT EXISTS queue (
			id              INTEGER PRIMARY KEY AUTOINCREMENT,
			uuid            TEXT NOT NULL UNIQUE,
			vendor          TEXT NOT NULL,
			device_id       INTEGER,
			raw_message     TEXT NOT NULL,
			received_at     TEXT NOT NULL,
			status          TEXT NOT NULL DEFAULT 'pending',
			attempt_count   INTEGER NOT NULL DEFAULT 0,
			last_error      TEXT,
			created_at      TEXT NOT NULL DEFAULT (datetime('now')),
			synced_at       TEXT
		)`,
		`CREATE INDEX IF NOT EXISTS queue_status_idx ON queue(status, id)`,

		`CREATE TABLE IF NOT EXISTS events (
			id              INTEGER PRIMARY KEY AUTOINCREMENT,
			type            TEXT NOT NULL,
			level           TEXT,
			category        TEXT,
			device_id       INTEGER,
			vendor          TEXT,
			device_ip       TEXT,
			sample_no       TEXT,
			message         TEXT,
			context_json    TEXT,
			status          TEXT NOT NULL DEFAULT 'pending',
			created_at      TEXT NOT NULL DEFAULT (datetime('now'))
		)`,
		`CREATE INDEX IF NOT EXISTS events_status_idx ON events(status, id)`,

		`CREATE TABLE IF NOT EXISTS worklist_cache (
			id          INTEGER PRIMARY KEY,
			vendor      TEXT,
			device_id   INTEGER,
			sample_no   TEXT,
			payload     TEXT,
			pulled_at   TEXT NOT NULL DEFAULT (datetime('now'))
		)`,
	}
	for _, q := range stmts {
		if _, err := s.db.Exec(q); err != nil {
			return fmt.Errorf("migrate %q: %w", q, err)
		}
	}
	return nil
}

// EnqueueResult adds one raw message to the outbound queue. Generates a UUID
// if not supplied. Returns the UUID actually used.
func (s *Store) EnqueueResult(vendor string, deviceID *int64, raw string, receivedAt time.Time, suppliedUUID string) (string, error) {
	id := suppliedUUID
	if id == "" {
		id = uuid.NewString()
	}
	_, err := s.db.Exec(
		`INSERT INTO queue (uuid, vendor, device_id, raw_message, received_at) VALUES (?,?,?,?,?)`,
		id, vendor, deviceID, raw, receivedAt.UTC().Format(time.RFC3339),
	)
	if err != nil {
		return "", fmt.Errorf("enqueue: %w", err)
	}
	return id, nil
}

// PendingResults returns up to limit queue items that still need to be pushed.
func (s *Store) PendingResults(limit int) ([]*QueueItem, error) {
	rows, err := s.db.Query(
		`SELECT id, uuid, vendor, device_id, raw_message, received_at, status,
		        attempt_count, COALESCE(last_error,''), created_at, synced_at
		 FROM queue WHERE status='pending' ORDER BY id ASC LIMIT ?`, limit)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var out []*QueueItem
	for rows.Next() {
		var item QueueItem
		var deviceID sql.NullInt64
		var receivedRaw, createdRaw string
		var syncedRaw sql.NullString
		if err := rows.Scan(&item.ID, &item.UUID, &item.Vendor, &deviceID,
			&item.RawMessage, &receivedRaw, &item.Status, &item.Attempts,
			&item.LastError, &createdRaw, &syncedRaw); err != nil {
			return nil, err
		}
		if deviceID.Valid {
			d := deviceID.Int64
			item.DeviceID = &d
		}
		item.ReceivedAt, _ = time.Parse(time.RFC3339, receivedRaw)
		item.CreatedAt, _ = time.Parse(time.RFC3339, createdRaw)
		if syncedRaw.Valid {
			t, _ := time.Parse(time.RFC3339, syncedRaw.String)
			item.SyncedAt = &t
		}
		out = append(out, &item)
	}
	return out, rows.Err()
}

// MarkSynced marks a queue item as successfully pushed.
func (s *Store) MarkSynced(id int64) error {
	_, err := s.db.Exec(
		`UPDATE queue SET status='synced', synced_at=datetime('now'), last_error='' WHERE id=?`, id)
	return err
}

// MarkFailed bumps the attempt counter and records the error message.
func (s *Store) MarkFailed(id int64, errMsg string) error {
	_, err := s.db.Exec(
		`UPDATE queue SET attempt_count=attempt_count+1, last_error=? WHERE id=?`, errMsg, id)
	return err
}

// PendingCount is what the heartbeat reports as queue_depth.
func (s *Store) PendingCount() (int, error) {
	var n int
	err := s.db.QueryRow(`SELECT COUNT(*) FROM queue WHERE status='pending'`).Scan(&n)
	return n, err
}

// RecentSyncedUUIDs returns the most-recent synced UUIDs for the reconcile
// manifest — the cloud confirms whether it actually has them.
func (s *Store) RecentSyncedUUIDs(limit int) ([]string, error) {
	rows, err := s.db.Query(
		`SELECT uuid FROM queue WHERE status='synced' ORDER BY id DESC LIMIT ?`, limit)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []string
	for rows.Next() {
		var u string
		if err := rows.Scan(&u); err != nil {
			return nil, err
		}
		out = append(out, u)
	}
	return out, rows.Err()
}

// ReQueueByUUIDs flips synced items back to pending so they get re-pushed —
// used when the cloud's manifest reconcile reports them missing.
func (s *Store) ReQueueByUUIDs(uuids []string) error {
	if len(uuids) == 0 {
		return nil
	}
	// Build (?,?,?...) placeholder list.
	q := `UPDATE queue SET status='pending', synced_at=NULL WHERE uuid IN (`
	args := make([]interface{}, 0, len(uuids))
	for i, u := range uuids {
		if i > 0 {
			q += ","
		}
		q += "?"
		args = append(args, u)
	}
	q += ")"
	_, err := s.db.Exec(q, args...)
	return err
}

// EnqueueEvent records a log/error event for the next sync sweep.
func (s *Store) EnqueueEvent(ev Event) error {
	ctx := ""
	if ev.Context != nil {
		if raw, err := json.Marshal(ev.Context); err == nil {
			ctx = string(raw)
		}
	}
	_, err := s.db.Exec(
		`INSERT INTO events (type, level, category, device_id, vendor, device_ip,
		                     sample_no, message, context_json)
		 VALUES (?,?,?,?,?,?,?,?,?)`,
		ev.Type, ev.Level, ev.Category, ev.DeviceID, ev.Vendor, ev.DeviceIP,
		ev.SampleNo, ev.Message, ctx,
	)
	return err
}

// PendingEvents returns events not yet pushed.
func (s *Store) PendingEvents(limit int) ([]*Event, error) {
	rows, err := s.db.Query(
		`SELECT id, type, COALESCE(level,''), COALESCE(category,''),
		        device_id, COALESCE(vendor,''), COALESCE(device_ip,''),
		        COALESCE(sample_no,''), COALESCE(message,''),
		        COALESCE(context_json,''), created_at
		 FROM events WHERE status='pending' ORDER BY id ASC LIMIT ?`, limit)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []*Event
	for rows.Next() {
		var e Event
		var deviceID sql.NullInt64
		var ctxRaw, createdRaw string
		if err := rows.Scan(&e.ID, &e.Type, &e.Level, &e.Category, &deviceID,
			&e.Vendor, &e.DeviceIP, &e.SampleNo, &e.Message, &ctxRaw, &createdRaw); err != nil {
			return nil, err
		}
		if deviceID.Valid {
			d := deviceID.Int64
			e.DeviceID = &d
		}
		if ctxRaw != "" {
			_ = json.Unmarshal([]byte(ctxRaw), &e.Context)
		}
		e.CreatedAt, _ = time.Parse(time.RFC3339, createdRaw)
		out = append(out, &e)
	}
	return out, rows.Err()
}

// MarkEventsSynced flips a batch of events to synced.
func (s *Store) MarkEventsSynced(ids []int64) error {
	if len(ids) == 0 {
		return nil
	}
	q := `UPDATE events SET status='synced' WHERE id IN (`
	args := make([]interface{}, 0, len(ids))
	for i, id := range ids {
		if i > 0 {
			q += ","
		}
		q += "?"
		args = append(args, id)
	}
	q += ")"
	_, err := s.db.Exec(q, args...)
	return err
}

// ReplaceWorklist swaps the cached worklist with the freshly-pulled set.
func (s *Store) ReplaceWorklist(items []map[string]interface{}) error {
	tx, err := s.db.Begin()
	if err != nil {
		return err
	}
	if _, err := tx.Exec(`DELETE FROM worklist_cache`); err != nil {
		_ = tx.Rollback()
		return err
	}
	for _, it := range items {
		raw, _ := json.Marshal(it)
		var (
			id        int64
			vendor    string
			deviceID  interface{}
			sampleNo  string
		)
		if v, ok := it["id"].(float64); ok {
			id = int64(v)
		}
		if v, ok := it["vendor"].(string); ok {
			vendor = v
		}
		if v, ok := it["device_id"].(float64); ok {
			deviceID = int64(v)
		}
		if v, ok := it["sample_no"].(string); ok {
			sampleNo = v
		}
		if _, err := tx.Exec(
			`INSERT OR REPLACE INTO worklist_cache (id, vendor, device_id, sample_no, payload) VALUES (?,?,?,?,?)`,
			id, vendor, deviceID, sampleNo, string(raw),
		); err != nil {
			_ = tx.Rollback()
			return err
		}
	}
	return tx.Commit()
}
