Copy

package database

import (
	"errors"
	"fmt"
	"time"
)

type TopStats struct {
	RemoteAddrs []LabelCount `json:"remote_addrs"`
	Ports       []LabelCount `json:"ports"`
	Types       []LabelCount `json:"types"`
	Domains     []LabelCount `json:"domains"`
	Countries   []LabelCount `json:"countries"`
	ASNs        []LabelCount `json:"asns"`
}

type StatPoint struct {
	Time  time.Time `json:"time"`
	Count int       `json:"count"`
}

type DashboardStats struct {
	Stats24h  TopStats    `json:"stats_24h"`
	StatsAll  TopStats    `json:"stats_all"`
	Count24h  int         `json:"count_24h"`
	Sparkline []StatPoint `json:"sparkline"`
}

func (db *Database) GetDashboardStats() (*DashboardStats, error) {
	if db.DB == nil {
		return nil, errors.New("database is not connected")
	}

	stats := &DashboardStats{
		Stats24h: TopStats{
			RemoteAddrs: []LabelCount{},
			Ports:       []LabelCount{},
			Types:       []LabelCount{},
			Domains:     []LabelCount{},
			Countries:   []LabelCount{},
		},
		StatsAll: TopStats{
			RemoteAddrs: []LabelCount{},
			Ports:       []LabelCount{},
			Types:       []LabelCount{},
			Domains:     []LabelCount{},
			Countries:   []LabelCount{},
		},
		Sparkline: []StatPoint{},
	}

	intervals := []struct {
		name     string
		interval string
		target   *TopStats
	}{
		{"24h", "'24 HOURS'", &stats.Stats24h},
		{"all", "", &stats.StatsAll},
	}

	for _, inv := range intervals {
		whereClause := ""
		if inv.interval != "" {
			whereClause = "WHERE time >= now() - INTERVAL " + inv.interval
		}

		query := fmt.Sprintf(`
			SELECT remote_addr, COUNT(*) as count 
			FROM honeypot_events 
			%s
			GROUP BY remote_addr 
			ORDER BY count DESC, remote_addr ASC
			LIMIT 20
		`, whereClause)
		rows, err := db.DB.Query(query)
		if err != nil {
			return nil, err
		}
		for rows.Next() {
			var ac LabelCount
			if err := rows.Scan(&ac.Label, &ac.Count); err != nil {
				rows.Close()
				return nil, err
			}
			inv.target.RemoteAddrs = append(inv.target.RemoteAddrs, ac)
		}
		rows.Close()

		// Top Ports
		portWhere := "WHERE dst_port != 0"
		if inv.interval != "" {
			portWhere += " AND time >= now() - INTERVAL " + inv.interval
		}
		query = fmt.Sprintf(`
			SELECT dst_port, COUNT(*) as count 
			FROM honeypot_events 
			%s
			GROUP BY dst_port 
			ORDER BY count DESC 
			LIMIT 20
		`, portWhere)
		rows, err = db.DB.Query(query)
		if err != nil {
			return nil, err
		}
		for rows.Next() {
			var pc LabelCount
			if err := rows.Scan(&pc.Label, &pc.Count); err != nil {
				rows.Close()
				return nil, err
			}
			inv.target.Ports = append(inv.target.Ports, pc)
		}
		rows.Close()

		// Top Types
		query = fmt.Sprintf(`
			SELECT type, COUNT(*) as count 
			FROM honeypot_events 
			%s
			GROUP BY type 
			ORDER BY count DESC
		`, whereClause)
		rows, err = db.DB.Query(query)
		if err != nil {
			return nil, err
		}
		for rows.Next() {
			var tc LabelCount
			if err := rows.Scan(&tc.Label, &tc.Count); err != nil {
				rows.Close()
				return nil, err
			}
			inv.target.Types = append(inv.target.Types, tc)
		}
		rows.Close()

		// Top Domains
		domainWhere := "WHERE ips.domain IS NOT NULL AND ips.domain != ''"
		if inv.interval != "" {
			domainWhere += " AND honeypot_events.time >= now() - INTERVAL " + inv.interval
		}
		query = fmt.Sprintf(`
			SELECT ips.domain, COUNT(*) as count 
			FROM honeypot_events 
			JOIN ips ON honeypot_events.remote_addr = ips.ip
			%s
			GROUP BY ips.domain 
			ORDER BY count DESC, ips.domain ASC
			LIMIT 20
		`, domainWhere)
		rows, err = db.DB.Query(query)
		if err != nil {
			return nil, err
		}
		for rows.Next() {
			var dc LabelCount
			if err := rows.Scan(&dc.Label, &dc.Count); err != nil {
				rows.Close()
				return nil, err
			}
			inv.target.Domains = append(inv.target.Domains, dc)
		}
		rows.Close()

		// Top Countries
		countryWhere := "WHERE ips.country IS NOT NULL AND ips.country != ''"
		if inv.interval != "" {
			countryWhere += " AND honeypot_events.time >= now() - INTERVAL " + inv.interval
		}
		query = fmt.Sprintf(`
			SELECT ips.country, COUNT(*) as count 
			FROM honeypot_events 
			JOIN ips ON honeypot_events.remote_addr = ips.ip
			%s
			GROUP BY ips.country 
			ORDER BY count DESC
			LIMIT 20
		`, countryWhere)
		rows, err = db.DB.Query(query)
		if err != nil {
			return nil, err
		}
		for rows.Next() {
			var cc LabelCount
			if err := rows.Scan(&cc.Label, &cc.Count); err != nil {
				rows.Close()
				return nil, err
			}
			inv.target.Countries = append(inv.target.Countries, cc)
		}
		rows.Close()
	}

	// Counts
	err := db.DB.QueryRow(`
		SELECT 
			COUNT(*) FILTER (WHERE time >= now() - INTERVAL '24 HOURS')
		FROM honeypot_events
	`).Scan(&stats.Count24h)
	if err != nil {
		return nil, err
	}

	// Sparkline (last 24 hours, hourly buckets)
	rows, err := db.DB.Query(`
		SELECT time_bucket(INTERVAL '1 HOUR', time) AS bucket, COUNT(*) 
		FROM honeypot_events 
		WHERE time >= now() - INTERVAL '24 HOURS'
		GROUP BY bucket 
		ORDER BY bucket ASC
	`)
	if err != nil {
		return nil, err
	}
	for rows.Next() {
		var sp StatPoint
		if err := rows.Scan(&sp.Time, &sp.Count); err != nil {
			rows.Close()
			return nil, err
		}
		stats.Sparkline = append(stats.Sparkline, sp)
	}
	rows.Close()

	return stats, nil
}