test.go

// server.go

package cmd

import (
	"encoding/csv"
	"fmt"
	"html/template"
	"log"
	"net/http"
	"os"
	"strconv"
	"strings"
	"time"

	"github.com/gin-gonic/gin"
	"github.com/spf13/cobra"
	"gorm.io/gorm"

	"aws-tools/internal/db"
	"aws-tools/internal/models"
)

var serverPort int

var serverCmd = &cobra.Command{
	Use:   "server",
	Short: "Run HTTP server to view discovered databases in a web UI",
	RunE: func(cmd *cobra.Command, args []string) error {
		return runServer(db.DBConn)
	},
}

func init() {
	root.AddCommand(serverCmd)
	serverCmd.Flags().IntVar(&serverPort, "port", 8080, "Port for server")
}

type Filters struct {
	Account string
	Region  string
	Engine  string
	Support string
	Sort    string
	Limit   int
	Offset  int
	Tab     string
}

func runServer(gdb *gorm.DB) error {
	if gdb == nil {
		return fmt.Errorf("database connection is nil")
	}

	r := gin.Default()

	funcMap := template.FuncMap{
		"add": func(a, b int) int { return a + b },
		"sub": func(a, b int) int { return a - b },
	}

	tmpl := template.Must(template.New("index").
		Funcs(funcMap).
		ParseFiles(
			"templates/index.html",
			"templates/clusters.html",
			"templates/instances.html",
			"templates/accounts.html",
			"templates/recommendations.html",
		))
	r.SetHTMLTemplate(tmpl)

	r.GET("/", func(c *gin.Context) {
		f := parseFilters(c)
		renderMainPage(c, gdb, f)
	})

	r.GET("/download", func(c *gin.Context) {
		f := parseFilters(c)
		downloadCSV(c, gdb, f)
	})

	addr := fmt.Sprintf(":%d", serverPort)
	log.Printf("Server running at http://localhost%s", addr)
	return r.Run(addr)
}

// --- Filters ---

func parseFilters(c *gin.Context) Filters {
	getLast := func(key string) string {
		vals := c.QueryArray(key)
		if len(vals) == 0 {
			return ""
		}
		return vals[len(vals)-1]
	}

	f := Filters{
		Account: getLast("account"),
		Region:  getLast("region"),
		Engine:  getLast("engine"),
		Support: getLast("support"),
		Sort:    getLast("sort"),
		Tab:     getLast("tab"),
	}

	if f.Tab == "" {
		f.Tab = "clusters"
	}

	f.Limit = 1000
	if v := getLast("limit"); v != "" {
		if n, err := strconv.Atoi(v); err == nil && n > 0 {
			f.Limit = n
		}
	}
	if v := getLast("offset"); v != "" {
		if n, err := strconv.Atoi(v); err == nil && n >= 0 {
			f.Offset = n
		}
	}

	return f
}

// --- Main Page ---

func renderMainPage(c *gin.Context, gdb *gorm.DB, f Filters) {
	var total int64
	var clusters []models.DatabaseCluster
	var instances []models.DatabaseInstance
	var accounts []models.Account
	var recs []models.DBRecommendation

	q := applyFilters(gdb, f.Tab, f)
	if f.Sort != "" {
		if expr := parseSort(f.Sort); expr != "" {
			q = q.Order(expr)
		}
	}

	switch f.Tab {
	case "clusters":
		q.Model(&models.DatabaseCluster{}).Count(&total)
		q.Limit(f.Limit).Offset(f.Offset).Find(&clusters)
	case "instances":
		q.Model(&models.DatabaseInstance{}).Count(&total)
		q.Limit(f.Limit).Offset(f.Offset).Find(&instances)
	case "accounts":
		q.Model(&models.Account{}).Count(&total)
		q.Limit(f.Limit).Offset(f.Offset).Find(&accounts)
	case "recommendations":
		q.Model(&models.DBRecommendation{}).Count(&total)
		q.Limit(f.Limit).Offset(f.Offset).Find(&recs)
	}

	c.HTML(http.StatusOK, "index", gin.H{
		"Clusters":        clusters,
		"Instances":       instances,
		"Accounts":        accounts,
		"Recommendations": recs,
		"Account":         f.Account,
		"Region":          f.Region,
		"Engine":          f.Engine,
		"Support":         f.Support,
		"Sort":            f.Sort,
		"Limit":           f.Limit,
		"Offset":          f.Offset,
		"Tab":             f.Tab,
		"Total":           total,
	})
}

// --- CSV Download ---

func downloadCSV(c *gin.Context, gdb *gorm.DB, f Filters) {
	q := applyFilters(gdb, f.Tab, f)
	if f.Sort != "" {
		if expr := parseSort(f.Sort); expr != "" {
			q = q.Order(expr)
		}
	}

	now := time.Now().Format("2006-01-02_15-04-05")
	filename := fmt.Sprintf("%s_%s.csv", f.Tab, now)

	c.Header("Content-Type", "text/csv")
	c.Header("Content-Disposition", "attachment; filename="+filename)
	w := csv.NewWriter(c.Writer)

	switch f.Tab {
	case "clusters":
		var clusters []models.DatabaseCluster
		q.Find(&clusters)
		w.Write([]string{"Identifier", "Account", "Region", "Engine", "Version", "Support"})
		for _, cl := range clusters {
			w.Write([]string{cl.Identifier, cl.AccountID, cl.RegionName, cl.Engine, cl.Version, cl.Support})
		}
	case "instances":
		var instances []models.DatabaseInstance
		q.Find(&instances)
		w.Write([]string{"Identifier", "Account", "Region", "Engine", "Class", "Version"})
		for _, inst := range instances {
			w.Write([]string{inst.Identifier, inst.AccountID, inst.RegionName, inst.Engine, inst.Class, inst.Version})
		}
	case "accounts":
		var accounts []models.Account
		q.Find(&accounts)
		w.Write([]string{"Account ID", "Name", "PrimaryOwner", "CreatedAt"})
		for _, acct := range accounts {
			w.Write([]string{acct.ID, acct.Name, acct.PrimaryOwner, acct.CreatedAt.Format(time.RFC3339)})
		}
	case "recommendations":
		var recs []models.DBRecommendation
		q.Find(&recs)
		w.Write([]string{"Account", "ResourceArn", "Category", "Severity", "Status", "Description"})
		for _, rec := range recs {
			w.Write([]string{rec.AccountID, rec.ResourceArn, rec.Category, rec.Severity, rec.Status, rec.Description})
		}
	}
	w.Flush()
}

// --- Filtering and Sorting ---

func applyFilters(gdb *gorm.DB, kind string, f Filters) *gorm.DB {
	switch kind {
	case "clusters":
		q := gdb.Model(&models.DatabaseCluster{})
		if f.Account != "" {
			q = q.Where("account_id = ?", f.Account)
		}
		if f.Region != "" {
			q = q.Where("region_name = ?", f.Region)
		}
		if f.Engine != "" {
			q = q.Where("engine = ?", f.Engine)
		}
		if f.Support != "" {
			q = q.Where("support = ?", f.Support)
		}
		return q
	case "instances":
		q := gdb.Model(&models.DatabaseInstance{})
		if f.Account != "" {
			q = q.Where("account_id = ?", f.Account)
		}
		if f.Region != "" {
			q = q.Where("region_name = ?", f.Region)
		}
		if f.Engine != "" {
			q = q.Where("engine = ?", f.Engine)
		}
		if f.Support != "" {
			q = q.Where("support = ?", f.Support)
		}
		return q
	case "accounts":
		q := gdb.Model(&models.Account{})
		if f.Account != "" {
			q = q.Where("id = ?", f.Account)
		}
		return q
	case "recommendations":
		q := gdb.Model(&models.DBRecommendation{})
		if f.Account != "" {
			q = q.Where("account_id = ?", f.Account)
		}
		return q
	default:
		return gdb
	}
}

func parseSort(s string) string {
	if s == "" {
		return ""
	}
	parts := strings.Split(s, ",")
	out := make([]string, 0, len(parts))
	for _, p := range parts {
		p = strings.TrimSpace(p)
		if p == "" {
			continue
		}
		colDir := strings.SplitN(p, ":", 2)
		col := strings.TrimSpace(colDir[0])
		dir := "asc"
		if len(colDir) == 2 {
			d := strings.ToLower(strings.TrimSpace(colDir[1]))
			if d == "desc" {
				dir = "desc"
			}
		}
		out = append(out, fmt.Sprintf("%s %s", col, dir))
	}
	return strings.Join(out, ", ")
}

// index.html

{{define "index"}}
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>RDS Inventory</title>

<style>
body { font-family: system-ui, apple-system, Segoe UI, Roboto, Arial, sans-serif; margin: 20px; }
h1 { margin-bottom: 12px; }
.topbar { position: sticky; top: 0; background: #fff; z-index: 10; padding: 10px 0; border-bottom: 1px solid #eee; }
form.filters { display: flex; flex-wrap: wrap; gap: 8px 16px; align-items: center; }
form.filters input, form.filters select { padding: 6px 8px; font-size: 14px; }
.tablink { display: inline-block; padding: 8px 12px; margin-right: 6px; text-decoration: none; border: 1px solid #ddd; border-bottom: none; background: #f9f9f9; color: #333; }
.tablink.active { background: #fff; border-color: #fff; font-weight: 600; }
.tablewrap { border: 1px solid #ddd; border-radius: 4px; overflow: auto; }
th, td { border: 1px solid #eee; padding: 6px 8px; text-align: left; font-size: 13px; white-space: nowrap; }
tr:nth-child(even) { background: #fcfcfc; }
.controls { margin: 8px 0 16px 0; font-size: 13px; }
.badge-success { background: #e6ffed; color: #0a7b34; border: 1px solid #bde5cc; }
.badge-warning { background: #fff7e6; color: #8a5a00; border: 1px solid #ffd78a; }
.badge-ok { display: none; }
</style>

<script>
// --- Switch tab and preserve filters ---
function setTab(tab) {
  const url = new URL(window.location);
  url.searchParams.set('tab', tab);
  url.searchParams.set('offset', '0');
  window.location = url.toString();
}

// --- Toggle column visibility ---
function toggleCols(prefix, check, cols) {
  const show = check.checked;
  cols.forEach(idx => {
    document.querySelectorAll(`.${prefix}-col-${idx}`).forEach(td => {
      td.style.display = show ? '' : 'none';
    });
  });
}

function initToggles(prefix) {
  const groups = document.querySelectorAll(`input[data-cols][data-prefix="${prefix}"]`);
  groups.forEach(cb => {
    const cols = cb.getAttribute('data-cols').split(',').map(x => parseInt(x, 10));
    cb.addEventListener('change', () => toggleCols(prefix, cb, cols));
    toggleCols(prefix, cb, cols); // initial state
  });
}

// --- Clean form submission (prevents duplicate params) ---
function cleanQuery(e) {
  e.preventDefault();
  const form = e.target;
  const base = window.location.pathname;
  const params = new URLSearchParams(new FormData(form));
  params.set('offset', '0'); // reset pagination on filter change
  window.location = `${base}?${params.toString()}`;
}

window.addEventListener('DOMContentLoaded', () => {
  initToggles('cluster');
  initToggles('inst');
});
</script>
</head>

<body>
<div class="topbar">
  <h1>RDS Inventory</h1>
  <form id="filters" class="filters" method="get" onsubmit="cleanQuery(event)">
    <input type="hidden" name="tab" value="{{.Tab}}">
    <input type="hidden" name="offset" value="{{.Offset}}">
    <label>Account:
      <select name="account">
        <option value="">All</option>
        {{range $i, $acct := .Accounts}}
        <option value="{{$acct.ID}}" {{if eq $.Account $acct.ID}}selected{{end}}>{{$acct.Name}} - {{$acct.ID}}</option>
        {{end}}
      </select>
    </label>
    <label>Region:
      <select name="region">
        <option value="">All</option>
        {{range $r := .Regions}}
        <option value="{{$r}}" {{if eq $.Region $r}}selected{{end}}>{{$r}}</option>
        {{end}}
      </select>
    </label>
    <label>Engine:
      <select name="engine">
        <option value="">All</option>
        {{range $e := .Engines}}
        <option value="{{$e}}" {{if eq $.Engine $e}}selected{{end}}>{{$e}}</option>
        {{end}}
      </select>
    </label>
    <label>Support:
      <select name="support">
        <option value="">All</option>
        <option value="supported" {{if eq $.Support "supported"}}selected{{end}}>Supported</option>
        <option value="unsupported" {{if eq $.Support "unsupported"}}selected{{end}}>Unsupported</option>
      </select>
    </label>
    <label>Sort: <input name="sort" value="{{.Sort}}" placeholder="engine:asc,version:desc"></label>
    <label>Limit: <input type="number" name="limit" min="1" value="{{.Limit}}"></label>
    <button type="submit">Apply</button>
    <button type="button" id="clearFilters" onclick="window.location='/'">Clear Filters</button>
  </form>

  <div class="pagination_controls" style="margin-top:6px;">
    {{if gt .Offset 0}}
    <button onclick="setTab('{{.Tab}}');">Prev</button>
    {{end}}
    <span>Showing {{add .Offset 1}}–{{add .Offset .Limit}} of {{.Total}}</span>
    {{if lt (add .Offset .Limit) .Total}}
    <button onclick="const url=new URL(window.location);url.searchParams.set('offset', {{add .Offset .Limit}});window.location=url;">Next</button>
    {{end}}
  </div>

  <a id="downloadLink" href="/download?tab={{.Tab}}&account={{.Account}}&region={{.Region}}&engine={{.Engine}}&support={{.Support}}&sort={{.Sort}}&limit={{.Limit}}&offset={{.Offset}}">
    <button>Download CSV</button>
  </a>
</div>

<!-- tabs -->
<div>
  <a href="javascript:setTab('clusters')" class="tablink {{if eq .Tab "clusters"}}active{{end}}">Clusters</a>
  <a href="javascript:setTab('instances')" class="tablink {{if eq .Tab "instances"}}active{{end}}">Instances</a>
  <a href="javascript:setTab('accounts')" class="tablink {{if eq .Tab "accounts"}}active{{end}}">Accounts</a>
  <a href="javascript:setTab('recommendations')" class="tablink {{if eq .Tab "recommendations"}}active{{end}}">Recommendations</a>
</div>

<!-- render tables (cluster, instance, account, recommendation) -->
{{if eq .Tab "clusters"}}
  {{template "clusters" .}}
{{else if eq .Tab "instances"}}
  {{template "instances" .}}
{{else if eq .Tab "accounts"}}
  {{template "accounts" .}}
{{else if eq .Tab "recommendations"}}
  {{template "recommendations" .}}
{{end}}
</body>
</html>
{{end}}

// clusters.html

{{define "clusters"}}
<div class="controls">
  <strong>Columns:</strong>
  <label><input type="checkbox" checked data-prefix="cluster" data-cols="1,2,3,4,5,6"> Core</label>
  <label><input type="checkbox" checked data-prefix="cluster" data-cols="7,8,9"> Backups</label>
  <label><input type="checkbox" checked data-prefix="cluster" data-cols="10,11,12"> Security</label>
  <label><input type="checkbox" checked data-prefix="cluster" data-cols="13,14"> Serverless v2</label>
  <label><input type="checkbox" checked data-prefix="cluster" data-cols="15"> Members</label>
</div>

<div class="tablewrap">
  <table>
    <tr>
      <th>Identifier</th><th>Account</th><th>Region</th><th>Engine</th><th>Version</th><th>Support</th>
      <th>Backup Retention</th><th>Preferred Window</th><th>Encrypted</th>
      <th>IAM Auth</th><th>Min Cap</th><th>Max Cap</th><th>Cluster Members</th>
    </tr>
    {{range .Clusters}}
    <tr>
      <td>{{.Identifier}}</td>
      <td>{{.AccountID}}</td>
      <td>{{.RegionName}}</td>
      <td>{{.Engine}}</td>
      <td>{{.Version}}</td>
      <td>{{.Support}}</td>
      <td>{{.Properties.BackupRetention}}</td>
      <td>{{.Properties.PreferredBackupWindow}}</td>
      <td>{{.Properties.StorageEncrypted}}</td>
      <td>{{.Properties.IAMAuth}}</td>
      <td>{{.Properties.MinCapacity}}</td>
      <td>{{.Properties.MaxCapacity}}</td>
      <td>{{.Properties.ClusterMembers}}</td>
    </tr>
    {{end}}
  </table>
</div>
{{end}}

// instances.html

{{define "instances"}}
<div class="controls">
  <strong>Columns:</strong>
  <label><input type="checkbox" checked data-prefix="inst" data-cols="1,2,3,4,5,6"> Core</label>
  <label><input type="checkbox" checked data-prefix="inst" data-cols="7,8,9"> Storage</label>
  <label><input type="checkbox" checked data-prefix="inst" data-cols="10,11,12"> Security</label>
</div>

<div class="tablewrap">
  <table>
    <tr>
      <th>Identifier</th><th>Account</th><th>Region</th><th>Engine</th><th>Class</th><th>Version</th>
      <th>Encrypted</th><th>KMS Key</th><th>IAM Auth</th>
    </tr>
    {{range .Instances}}
    <tr>
      <td>{{.Identifier}}</td>
      <td>{{.AccountID}}</td>
      <td>{{.RegionName}}</td>
      <td>{{.Engine}}</td>
      <td>{{.Class}}</td>
      <td>{{.Version}}</td>
      <td>{{.Properties.StorageEncrypted}}</td>
      <td>{{.Properties.KmsKeyID}}</td>
      <td>{{.Properties.IAMAuth}}</td>
    </tr>
    {{end}}
  </table>
</div>
{{end}}

// accounts.html

{{define "accounts"}}
<div class="controls"><strong>Accounts</strong></div>
<div class="tablewrap">
  <table>
    <tr><th>Account ID</th><th>Name</th><th>Primary Owner</th><th>Created</th></tr>
    {{range .Accounts}}
    <tr>
      <td>{{.ID}}</td>
      <td>{{.Name}}</td>
      <td>{{.PrimaryOwner}}</td>
      <td>{{.CreatedAt}}</td>
    </tr>
    {{end}}
  </table>
</div>
{{end}}

// recs.html

{{define "recommendations"}}
<div class="controls"><strong>Recommendations</strong></div>
<div class="tablewrap">
  <table>
    <tr>
      <th>Account</th><th>Resource ARN</th><th>Category</th><th>Severity</th><th>Status</th><th>Description</th>
    </tr>
    {{range .Recommendations}}
    <tr>
      <td>{{.AccountID}}</td>
      <td>{{.ResourceArn}}</td>
      <td>{{.Category}}</td>
      <td>{{.Severity}}</td>
      <td>{{.Status}}</td>
      <td>{{.Description}}</td>
    </tr>
    {{end}}
  </table>
</div>
{{end}}