security

var AlertSchema = parquet.Schema{
	Columns: []parquet.Column{
		{Name: "ts", Type: parquet.TypeTimestamp},
		{Name: "src_ip", Type: parquet.TypeIPv4},
		{Name: "dst_ip", Type: parquet.TypeIPv4},
		{Name: "src_port", Type: parquet.TypePort},
		{Name: "dst_port", Type: parquet.TypePort},
		{Name: "protocol", Type: parquet.TypeProtocol},
		{Name: "signature_id", Type: parquet.TypeInt32},
		{Name: "severity", Type: parquet.TypeString},
		{Name: "category", Type: parquet.TypeString},
		{Name: "message", Type: parquet.TypeString},
		{Name: "action", Type: parquet.TypeString},
	},
}

var AllTables = map[string]parquet.Schema{
	"netflow":  NetflowSchema,
	"firewall": FirewallSchema,
	"dns":      DNSSchema,
	"auth":     AuthSchema,
	"alerts":   AlertSchema,
}

var AuthSchema = parquet.Schema{
	Columns: []parquet.Column{
		{Name: "ts", Type: parquet.TypeTimestamp},
		{Name: "src_ip", Type: parquet.TypeIPv4},
		{Name: "username", Type: parquet.TypeString},
		{Name: "action", Type: parquet.TypeString},
		{Name: "method", Type: parquet.TypeString},
		{Name: "service", Type: parquet.TypeString},
		{Name: "device", Type: parquet.TypeString},
		{Name: "user_agent", Type: parquet.TypeString, Nullable: true},
		{Name: "risk_score", Type: parquet.TypeInt32},
	},
}

var DNSSchema = parquet.Schema{
	Columns: []parquet.Column{
		{Name: "ts", Type: parquet.TypeTimestamp},
		{Name: "client_ip", Type: parquet.TypeIPv4},
		{Name: "server_ip", Type: parquet.TypeIPv4},
		{Name: "query_name", Type: parquet.TypeString},
		{Name: "query_type", Type: parquet.TypeString},
		{Name: "response_code", Type: parquet.TypeString},
		{Name: "answer_ip", Type: parquet.TypeIPv4, Nullable: true},
		{Name: "latency_ns", Type: parquet.TypeDuration},
		{Name: "is_recursive", Type: parquet.TypeBool},
	},
}

var FirewallSchema = parquet.Schema{
	Columns: []parquet.Column{
		{Name: "ts", Type: parquet.TypeTimestamp},
		{Name: "src_ip", Type: parquet.TypeIPv4},
		{Name: "dst_ip", Type: parquet.TypeIPv4},
		{Name: "src_port", Type: parquet.TypePort},
		{Name: "dst_port", Type: parquet.TypePort},
		{Name: "protocol", Type: parquet.TypeProtocol},
		{Name: "action", Type: parquet.TypeString},
		{Name: "rule_id", Type: parquet.TypeString},
		{Name: "zone_src", Type: parquet.TypeString},
		{Name: "zone_dst", Type: parquet.TypeString},
		{Name: "bytes_sent", Type: parquet.TypeInt64},
		{Name: "device", Type: parquet.TypeString},
	},
}

var NetflowSchema = parquet.Schema{
	Columns: []parquet.Column{
		{Name: "ts", Type: parquet.TypeTimestamp},
		{Name: "src_ip", Type: parquet.TypeIPv4},
		{Name: "dst_ip", Type: parquet.TypeIPv4},
		{Name: "src_port", Type: parquet.TypePort},
		{Name: "dst_port", Type: parquet.TypePort},
		{Name: "protocol", Type: parquet.TypeProtocol},
		{Name: "packets", Type: parquet.TypeInt64},
		{Name: "bytes_sent", Type: parquet.TypeInt64},
		{Name: "bytes_recv", Type: parquet.TypeInt64},
		{Name: "duration_ns", Type: parquet.TypeDuration},
		{Name: "tcp_flags", Type: parquet.TypeString},
		{Name: "direction", Type: parquet.TypeString},
	},
}

var SecurityQueries = map[int]QueryDef{

	1: {
		Name: "Top Talkers by Bytes",
		SQL: `SELECT src_ip, SUM(bytes_sent) as total_bytes, COUNT(*) as flow_count
			FROM netflow
			GROUP BY src_ip
			ORDER BY total_bytes DESC
			LIMIT 25`,
	},

	2: {
		Name: "Traffic by Protocol",
		SQL: `SELECT protocol, COUNT(*) as flows,
				SUM(packets) as total_packets, SUM(bytes_sent + bytes_recv) as total_bytes
			FROM netflow
			GROUP BY protocol
			ORDER BY total_bytes DESC`,
	},

	3: {
		Name: "Outbound Non-Standard Ports",
		SQL: `SELECT dst_ip, dst_port, COUNT(*) as flows, SUM(bytes_sent) as bytes_out
			FROM netflow
			WHERE direction = 'outbound'
				AND dst_port NOT IN (80, 443, 53, 22, 25, 993, 587)
				AND protocol = 6
			GROUP BY dst_ip, dst_port
			ORDER BY flows DESC
			LIMIT 50`,
	},

	4: {
		Name: "Hourly Traffic Volume",
		SQL: `SELECT EXTRACT(HOUR FROM ts) as hour,
				COUNT(*) as flows,
				SUM(bytes_sent + bytes_recv) as total_bytes
			FROM netflow
			GROUP BY EXTRACT(HOUR FROM ts)
			ORDER BY hour`,
	},

	5: {
		Name: "Long Duration Flows",
		SQL: `SELECT src_ip, dst_ip, dst_port, protocol, duration_ns, bytes_sent
			FROM netflow
			WHERE duration_ns > 60000000000
			ORDER BY duration_ns DESC
			LIMIT 100`,
	},

	6: {
		Name: "Top Denied Sources",
		SQL: `SELECT src_ip, COUNT(*) as deny_count, SUM(bytes_sent) as bytes_attempted
			FROM firewall
			WHERE action IN ('deny', 'drop')
			GROUP BY src_ip
			ORDER BY deny_count DESC
			LIMIT 25`,
	},

	7: {
		Name: "Cross-Zone Traffic Matrix",
		SQL: `SELECT zone_src, zone_dst, action, COUNT(*) as events
			FROM firewall
			GROUP BY zone_src, zone_dst, action
			ORDER BY events DESC`,
	},

	8: {
		Name: "Hot Firewall Rules",
		SQL: `SELECT rule_id, action, COUNT(*) as hits
			FROM firewall
			GROUP BY rule_id, action
			HAVING COUNT(*) > 10
			ORDER BY hits DESC
			LIMIT 50`,
	},

	9: {
		Name: "NXDOMAIN by Client",
		SQL: `SELECT client_ip, COUNT(*) as nxdomain_count
			FROM dns
			WHERE response_code = 'NXDOMAIN'
			GROUP BY client_ip
			ORDER BY nxdomain_count DESC
			LIMIT 25`,
	},

	10: {
		Name: "Suspicious DNS Queries",
		SQL: `SELECT client_ip, query_name, query_type, COUNT(*) as query_count
			FROM dns
			WHERE query_name LIKE '%.evil.%'
				OR query_name LIKE '%.badactor.%'
				OR query_name LIKE '%phish%'
				OR query_name LIKE '%malware%'
				OR query_name LIKE '%crypto-mine%'
			GROUP BY client_ip, query_name, query_type
			ORDER BY query_count DESC`,
	},

	11: {
		Name: "DNS Latency by Server",
		SQL: `SELECT server_ip, COUNT(*) as queries,
				AVG(latency_ns) as avg_latency,
				MAX(latency_ns) as max_latency
			FROM dns
			GROUP BY server_ip
			ORDER BY avg_latency DESC`,
	},

	12: {
		Name: "Brute Force Candidates",
		SQL: `SELECT username, service, COUNT(*) as failures, COUNT(DISTINCT src_ip) as unique_sources
			FROM auth
			WHERE action = 'login_failure'
			GROUP BY username, service
			ORDER BY failures DESC
			LIMIT 25`,
	},

	13: {
		Name: "Privilege Escalation Events",
		SQL: `SELECT ts, src_ip, username, method, service, device, risk_score
			FROM auth
			WHERE action = 'privilege_escalation'
			ORDER BY risk_score DESC, ts DESC`,
	},

	14: {
		Name: "High Risk Auth Events",
		SQL: `SELECT src_ip, username, action, service, risk_score
			FROM auth
			WHERE risk_score > 70
			ORDER BY risk_score DESC
			LIMIT 100`,
	},

	15: {
		Name: "Alert Severity Distribution",
		SQL: `SELECT severity, category, COUNT(*) as alert_count
			FROM alerts
			GROUP BY severity, category
			ORDER BY alert_count DESC`,
	},

	16: {
		Name: "Critical Alerts Timeline",
		SQL: `SELECT ts, src_ip, dst_ip, dst_port, message, action
			FROM alerts
			WHERE severity = 'critical'
			ORDER BY ts DESC`,
	},

	17: {
		Name: "Most Targeted Hosts",
		SQL: `SELECT dst_ip, COUNT(*) as alert_count,
				COUNT(DISTINCT signature_id) as unique_sigs
			FROM alerts
			WHERE severity IN ('critical', 'high')
			GROUP BY dst_ip
			ORDER BY alert_count DESC
			LIMIT 25`,
	},

	18: {
		Name: "Denied Sources with Alerts",
		SQL: `SELECT f.src_ip, COUNT(DISTINCT f.dst_port) as ports_tried,
				COUNT(DISTINCT a.signature_id) as alert_types
			FROM firewall f
			JOIN alerts a ON f.src_ip = a.src_ip
			WHERE f.action IN ('deny', 'drop')
			GROUP BY f.src_ip
			ORDER BY alert_types DESC
			LIMIT 25`,
	},

	19: {
		Name: "Failed Auth to Suspicious DNS",
		SQL: `SELECT d.client_ip, d.query_name, COUNT(*) as queries
			FROM dns d
			WHERE d.client_ip IN (
				SELECT DISTINCT src_ip FROM auth WHERE action = 'login_failure'
			)
			AND (d.query_name LIKE '%.evil.%' OR d.query_name LIKE '%badactor%'
				OR d.query_name LIKE '%phish%' OR d.query_name LIKE '%malware%')
			GROUP BY d.client_ip, d.query_name
			ORDER BY queries DESC
			LIMIT 50`,
	},

	20: {
		Name: "Alert Source Firewall Activity",
		SQL: `SELECT f.src_ip, f.action, f.dst_port, COUNT(*) as events
			FROM firewall f
			WHERE f.src_ip IN (
				SELECT DISTINCT src_ip FROM alerts WHERE severity IN ('critical', 'high')
			)
			GROUP BY f.src_ip, f.action, f.dst_port
			ORDER BY events DESC
			LIMIT 50`,
	},
}