package db

import (
	"database/sql"
	"encoding/json"
	"strings"

	"github.com/cynfo/rmm-backend/models"
)

// ListWAURules gibt alle WAU-Regeln eines Kunden zurück
func (d *Database) ListWAURules(customerID int) ([]models.WAURule, error) {
	rows, err := d.db.Query(`
		SELECT id, customer_id, winget_id, display_name, rule_type, created_at
		FROM customer_wau_rules
		WHERE customer_id = $1
		ORDER BY rule_type, lower(display_name)
	`, customerID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var rules []models.WAURule
	for rows.Next() {
		var r models.WAURule
		if err := rows.Scan(&r.ID, &r.CustomerID, &r.WingetID, &r.DisplayName, &r.RuleType, &r.CreatedAt); err != nil {
			return nil, err
		}
		rules = append(rules, r)
	}
	if rules == nil {
		rules = []models.WAURule{}
	}
	return rules, rows.Err()
}

// AddWAURule fügt eine neue Regel hinzu (upsert auf winget_id)
func (d *Database) AddWAURule(customerID int, wingetID, displayName, ruleType string) (*models.WAURule, error) {
	var r models.WAURule
	err := d.db.QueryRow(`
		INSERT INTO customer_wau_rules (customer_id, winget_id, display_name, rule_type)
		VALUES ($1, $2, $3, $4)
		ON CONFLICT (customer_id, winget_id) DO UPDATE
			SET display_name = EXCLUDED.display_name,
			    rule_type = EXCLUDED.rule_type
		RETURNING id, customer_id, winget_id, display_name, rule_type, created_at
	`, customerID, wingetID, displayName, ruleType).Scan(
		&r.ID, &r.CustomerID, &r.WingetID, &r.DisplayName, &r.RuleType, &r.CreatedAt,
	)
	if err != nil {
		return nil, err
	}
	return &r, nil
}

// DeleteWAURule löscht eine Regel (per ID, gehört zu customerID)
func (d *Database) DeleteWAURule(customerID, ruleID int) (bool, error) {
	res, err := d.db.Exec(`
		DELETE FROM customer_wau_rules WHERE id = $1 AND customer_id = $2
	`, ruleID, customerID)
	if err != nil {
		return false, err
	}
	n, _ := res.RowsAffected()
	return n > 0, nil
}

// GetWAUList gibt alle winget-IDs eines bestimmten Typs zurück (für den Plaintext-Endpoint)
func (d *Database) GetWAUList(customerID int, ruleType string) ([]string, error) {
	rows, err := d.db.Query(`
		SELECT winget_id FROM customer_wau_rules
		WHERE customer_id = $1 AND rule_type = $2
		ORDER BY lower(winget_id)
	`, customerID, ruleType)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

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

// GetWAUSoftwareInventory aggregiert installierte Software aller Windows-Agents eines Kunden
func (d *Database) GetWAUSoftwareInventory(customerID int) ([]models.WAUSoftwareEntry, error) {
	rows, err := d.db.Query(`
		SELECT
			app->>'name'                                                               AS name,
			COALESCE(app->>'publisher', '')                                            AS publisher,
			COUNT(DISTINCT sd.agent_id)                                                AS device_count,
			jsonb_agg(DISTINCT app->>'version') FILTER (WHERE (app->>'version') != '') AS versions
		FROM agents a
		JOIN system_data sd ON sd.agent_id = a.id
		CROSS JOIN jsonb_array_elements(sd.data_json->'windows'->'installed_software') AS app
		WHERE a.customer_id = $1
		  AND sd.data_json ? 'windows'
		  AND jsonb_typeof(sd.data_json->'windows'->'installed_software') = 'array'
		  AND (app->>'name') IS NOT NULL
		  AND (app->>'name') != ''
		GROUP BY app->>'name', app->>'publisher'
		ORDER BY lower(app->>'name')
	`, customerID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var entries []models.WAUSoftwareEntry
	for rows.Next() {
		var e models.WAUSoftwareEntry
		var versionsJSON sql.NullString

		if err := rows.Scan(&e.Name, &e.Publisher, &e.DeviceCount, &versionsJSON); err != nil {
			return nil, err
		}
		if versionsJSON.Valid && versionsJSON.String != "" && versionsJSON.String != "null" {
			json.Unmarshal([]byte(versionsJSON.String), &e.Versions)
		}
		if e.Versions == nil {
			e.Versions = []string{}
		}
		entries = append(entries, e)
	}
	if entries == nil {
		entries = []models.WAUSoftwareEntry{}
	}
	return entries, rows.Err()
}

// GetWAUCompliance berechnet den Compliance-Status aller Windows-Agents eines Kunden
// Prüft: WAU installiert, URLs korrekt, alle allow-Pakete installiert
func (d *Database) GetWAUCompliance(customerID int, expectedIncludeURL, expectedExcludeURL string) ([]models.WAUAgentCompliance, error) {
	// Alle Windows-Agents des Kunden mit ihren system_data holen
	rows, err := d.db.Query(`
		SELECT a.id, a.hostname, sd.data_json
		FROM agents a
		JOIN system_data sd ON sd.agent_id = a.id
		WHERE a.customer_id = $1
		  AND sd.data_json ? 'windows'
		ORDER BY lower(a.hostname)
	`, customerID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	// Allow-Pakete (= Required) für diesen Kunden laden
	allowRules, err := d.GetWAUList(customerID, "allow")
	if err != nil {
		return nil, err
	}
	allowRulesFull, err := d.ListWAURules(customerID)
	if err != nil {
		return nil, err
	}
	// Map winget_id → display_name
	displayNames := make(map[string]string)
	for _, r := range allowRulesFull {
		if r.RuleType == "allow" {
			displayNames[r.WingetID] = r.DisplayName
		}
	}

	var result []models.WAUAgentCompliance

	for rows.Next() {
		var agentID, hostname string
		var dataJSON []byte
		if err := rows.Scan(&agentID, &hostname, &dataJSON); err != nil {
			continue
		}

		// JSON parsen (nur was wir brauchen)
		var data struct {
			Windows struct {
				InstalledSoftware []struct {
					Name    string `json:"name"`
					Version string `json:"version"`
				} `json:"installed_software"`
				WAU *struct {
					Installed    bool   `json:"installed"`
					IncludeURL   string `json:"include_url"`
					ExcludeURL   string `json:"exclude_url"`
					PendingCount int    `json:"pending_updates"`
				} `json:"wau"`
			} `json:"windows"`
		}
		if err := json.Unmarshal(dataJSON, &data); err != nil {
			continue
		}

		ac := models.WAUAgentCompliance{
			AgentID:   agentID,
			AgentName: hostname,
		}

		// WAU-Status
		if data.Windows.WAU != nil {
			wau := data.Windows.WAU
			ac.WAUInstalled = wau.Installed
			ac.IncludeURL = wau.IncludeURL
			ac.ExcludeURL = wau.ExcludeURL
			ac.PendingCount = wau.PendingCount

			// URL-Check: beide URLs müssen gesetzt sein und passen
			includeOK := expectedIncludeURL == "" || wau.IncludeURL == expectedIncludeURL
			excludeOK := expectedExcludeURL == "" || wau.ExcludeURL == expectedExcludeURL
			ac.WAUConfigOK = wau.Installed && includeOK && excludeOK
		}

		// Installierte Software als lowercase-Set für schnelles Lookup
		installedSet := make(map[string]bool)
		for _, s := range data.Windows.InstalledSoftware {
			installedSet[strings.ToLower(s.Name)] = true
		}

		// Pro allow-Paket prüfen ob installiert
		for _, wingetID := range allowRules {
			displayName := displayNames[wingetID]
			if displayName == "" {
				displayName = wingetID
			}
			pkg := models.WAUPackageCompliance{
				WingetID:    wingetID,
				DisplayName: displayName,
			}
			// Fuzzy-Match: display_name (lowercase) muss in installierter Software enthalten sein
			lowerDisplay := strings.ToLower(displayName)
			for installedName := range installedSet {
				if strings.Contains(installedName, lowerDisplay) ||
					strings.Contains(lowerDisplay, installedName) {
					pkg.Installed = true
					break
				}
			}
			// Winget-ID-Teile als Fallback (z.B. "Mozilla.Firefox" → "firefox")
			if !pkg.Installed {
				parts := strings.Split(strings.ToLower(wingetID), ".")
				if len(parts) > 1 {
					appName := parts[len(parts)-1]
					for installedName := range installedSet {
						if strings.Contains(installedName, appName) {
							pkg.Installed = true
							break
						}
					}
				}
			}
			ac.Packages = append(ac.Packages, pkg)
		}

		// Gesamt-Compliance: WAU installiert + korrekt + alle Pakete da + keine Updates ausstehend
		allInstalled := true
		for _, p := range ac.Packages {
			if !p.Installed {
				allInstalled = false
				break
			}
		}
		ac.Compliant = ac.WAUInstalled && ac.WAUConfigOK && allInstalled && ac.PendingCount == 0

		result = append(result, ac)
	}
	if result == nil {
		result = []models.WAUAgentCompliance{}
	}
	return result, rows.Err()
}

// GetWAUComplianceSummary gibt eine kompakte Zusammenfassung für die Kundenliste zurück
func (d *Database) GetWAUComplianceSummary(customerID int, expectedIncludeURL, expectedExcludeURL string) (*models.WAUComplianceSummary, error) {
	agents, err := d.GetWAUCompliance(customerID, expectedIncludeURL, expectedExcludeURL)
	if err != nil {
		return nil, err
	}

	summary := &models.WAUComplianceSummary{
		CustomerID:  customerID,
		TotalAgents: len(agents),
	}

	for _, a := range agents {
		if a.Compliant {
			summary.CompliantAgents++
		}
		if !a.WAUInstalled {
			summary.WAUMissing++
		} else if !a.WAUConfigOK {
			summary.WAUMisconfigured++
		}
		if a.PendingCount > 0 {
			summary.UpdatesPending++
		}
		for _, p := range a.Packages {
			if !p.Installed {
				summary.PackagesMissing++
				break // pro Agent nur einmal zählen
			}
		}
	}

	// Status ableiten
	if summary.WAUMissing > 0 || summary.PackagesMissing > 0 {
		summary.Status = "critical"
	} else if summary.WAUMisconfigured > 0 || summary.UpdatesPending > 0 {
		summary.Status = "warning"
	} else if summary.TotalAgents == 0 {
		summary.Status = "unknown"
	} else {
		summary.Status = "ok"
	}

	return summary, nil
}

// GetAllCustomersWAUSummary gibt Compliance-Summary für alle Kunden zurück
func (d *Database) GetAllCustomersWAUSummary(includeURL, excludeURL string) ([]models.WAUComplianceSummary, error) {
	rows, err := d.db.Query(`SELECT id FROM customers ORDER BY name`)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var summaries []models.WAUComplianceSummary
	for rows.Next() {
		var id int
		if err := rows.Scan(&id); err != nil {
			continue
		}
		s, err := d.GetWAUComplianceSummary(id, includeURL, excludeURL)
		if err != nil {
			continue
		}
		if s.TotalAgents > 0 {
			summaries = append(summaries, *s)
		}
	}
	if summaries == nil {
		summaries = []models.WAUComplianceSummary{}
	}
	return summaries, rows.Err()
}