package database

import (
	"database/sql"
	"errors"
	"time"

	_ "github.com/mattn/go-sqlite3"
)

// connectionFreshness is how long a device counts as present after we last saw
// it in the ARP table. It smooths over the kernel briefly aging an entry out.
const connectionFreshness = 60 * time.Second

type Connection struct {
	IP        string
	Mac       string
	UpdatedAt time.Time
}

// RefreshConnections upserts the current ARP snapshot, bumping updated_at for
// devices still present, and prunes ones not seen within the freshness window.
func RefreshConnections(db *sql.DB, ipToMac map[string]string) error {
	tx, err := db.Begin()
	if err != nil {
		return err
	}
	defer tx.Rollback()

	stmt, err := tx.Prepare(`INSERT INTO connections (ip, mac, updated_at) VALUES (?, ?, ?)
		ON CONFLICT(ip) DO UPDATE SET mac = excluded.mac, updated_at = excluded.updated_at`)
	if err != nil {
		return err
	}
	defer stmt.Close()

	now := time.Now()
	for ip, mac := range ipToMac {
		if _, err := stmt.Exec(ip, mac, now); err != nil {
			return err
		}
	}

	if _, err := tx.Exec("DELETE FROM connections WHERE updated_at < ?", now.Add(-connectionFreshness)); err != nil {
		return err
	}

	return tx.Commit()
}

func MacForIP(db *sql.DB, ip string) (string, error) {
	var mac string
	err := db.QueryRow(
		"SELECT mac FROM connections WHERE ip = ? AND updated_at > ?",
		ip, time.Now().Add(-connectionFreshness),
	).Scan(&mac)
	if errors.Is(err, sql.ErrNoRows) {
		return "", nil
	}
	return mac, err
}

// NetworkUserIDs returns the set of users whose MAC was seen on the LAN within
// the freshness window, whether or not they're on the chat.
func NetworkUserIDs(db *sql.DB) (map[string]bool, error) {
	rows, err := db.Query(
		"SELECT DISTINCT m.user_id FROM macs m JOIN connections c ON c.mac = m.mac WHERE c.updated_at > ?",
		time.Now().Add(-connectionFreshness),
	)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	ids := map[string]bool{}
	for rows.Next() {
		var id string
		if err := rows.Scan(&id); err != nil {
			return nil, err
		}
		ids[id] = true
	}
	return ids, rows.Err()
}

func ListAliveConnections(db *sql.DB) ([]Connection, error) {
	rows, err := db.Query(
		"SELECT ip, mac, updated_at FROM connections WHERE updated_at > ? ORDER BY ip",
		time.Now().Add(-connectionFreshness),
	)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	conns := []Connection{}
	for rows.Next() {
		var c Connection
		if err := rows.Scan(&c.IP, &c.Mac, &c.UpdatedAt); err != nil {
			return nil, err
		}
		conns = append(conns, c)
	}
	return conns, rows.Err()
}