main.go

package main

import (
	"database/sql"
	"encoding/json"
	"flag"
	"fmt"
	"log"
	"os"
	"strings"

	_ "github.com/microsoft/go-mssqldb"
)

// Config represents the database configuration
type Config struct {
	Server                 string `json:"server"`
	Port                   int    `json:"port"`
	Database               string `json:"database"`
	Username               string `json:"username"`
	Password               string `json:"password"`
	Encrypt                string `json:"encrypt"`
	TrustServerCertificate bool   `json:"trust_server_certificate"`
}

// AuditTable represents an audit-related table with its row count
type AuditTable struct {
	Schema   string
	Name     string
	RowCount int64
}

// Command line flags
var (
	configFile = flag.String("config", "config.json", "Path to configuration file")
	purge      = flag.Bool("purge", false, "Purge all audit tables (DELETE within transaction)")
	truncate   = flag.Bool("truncate", false, "Truncate all audit tables (faster but cannot be rolled back)")
	dryRun     = flag.Bool("dry-run", false, "Show what would be purged without actually deleting")
	force      = flag.Bool("force", false, "Skip confirmation prompt for purge/truncate operations")
)

func main() {
	// Parse command line flags
	flag.Parse()

	// Load configuration
	config, err := loadConfig(*configFile)
	if err != nil {
		log.Fatalf("Failed to load config: %v", err)
	}

	// Connect to database
	db, err := connectToDatabase(config)
	if err != nil {
		log.Fatalf("Failed to connect to database: %v", err)
	}
	defer db.Close()

	fmt.Println("Successfully connected to MS SQL Server!")
	fmt.Printf("Database: %s\n\n", config.Database)

	// Find audit-related tables - simple sequential query, no transaction
	auditTables, err := findAuditTables(db)
	if err != nil {
		log.Fatalf("Failed to find audit tables: %v", err)
	}

	if len(auditTables) == 0 {
		fmt.Println("No audit-related tables found in the database.")
		return
	}

	// Display results
	printAuditTableSummary(auditTables)

	// Get DMV audit information
	fmt.Println("\n\n")
	getDMVAuditInfo(db)

	// Handle purge/truncate operations
	if *purge || *truncate {
		if *dryRun {
			fmt.Println("\n")
			printDryRunSummary(auditTables, *truncate)
			return
		}

		// Confirmation prompt unless --force is specified
		if !*force {
			if !confirmPurge(auditTables, *truncate) {
				fmt.Println("Operation cancelled.")
				return
			}
		}

		if *truncate {
			err = truncateAuditTables(db, auditTables)
		} else {
			err = purgeAuditTables(db, auditTables)
		}

		if err != nil {
			log.Fatalf("Failed to purge tables: %v", err)
		}
		return
	}
}

// getDMVAuditInfo retrieves and displays audit information from Dynamic Management Views
func getDMVAuditInfo(db *sql.DB) {
	fmt.Println("=" + strings.Repeat("=", 61))
	fmt.Println("           SQL SERVER AUDIT DMV INFORMATION")
	fmt.Println("=" + strings.Repeat("=", 61))
	fmt.Println()

	// 1. Server Audit Status
	printServerAuditStatus(db)

	// 2. Database Audit Specifications
	printDatabaseAuditSpecs(db)

	// 3. Audit Actions
	printAuditActions(db)

	// 4. Server-Level Audit Specifications
	printServerAuditSpecs(db)

	// 5. Audit-Related Performance Counters
	printAuditPerformanceCounters(db)

	// 6. Recent Audit Log Activity
	printRecentAuditActivity(db)
}

// printServerAuditStatus displays server audit status from sys.dm_server_audit_status
func printServerAuditStatus(db *sql.DB) {
	fmt.Println("--- SERVER AUDIT STATUS (sys.dm_server_audit_status) ---")
	fmt.Println()

	query := `
		SELECT 
			audit_id,
			name,
			status,
			status_desc,
			status_time,
			log_file_path,
			log_file_name,
			audit_file_size,
			audit_file_offset
		FROM sys.dm_server_audit_status
	`

	rows, err := db.Query(query)
	if err != nil {
		fmt.Printf("  Note: Could not query sys.dm_server_audit_status (requires VIEW SERVER STATE permission)\n")
		fmt.Printf("  Error: %v\n\n", err)
		return
	}
	defer rows.Close()

	hasData := false
	for rows.Next() {
		hasData = true
		var (
			auditID       sql.NullInt64
			name          sql.NullString
			status        sql.NullInt64
			statusDesc    sql.NullString
			statusTime    sql.NullTime
			logFilePath   sql.NullString
			logFileName   sql.NullString
			auditFileSize sql.NullInt64
			auditFileOff  sql.NullInt64
		)

		if err := rows.Scan(&auditID, &name, &status, &statusDesc, &statusTime, &logFilePath, &logFileName, &auditFileSize, &auditFileOff); err != nil {
			fmt.Printf("  Error scanning row: %v\n", err)
			continue
		}

		fmt.Printf("  Audit Name: %s\n", nullString(name))
		fmt.Printf("    Audit ID: %d\n", nullInt64(auditID))
		fmt.Printf("    Status: %s (%d)\n", nullString(statusDesc), nullInt64(status))
		fmt.Printf("    Status Time: %s\n", nullTime(statusTime))
		fmt.Printf("    Log File: %s%s\n", nullString(logFilePath), nullString(logFileName))
		fmt.Printf("    File Size: %d bytes\n", nullInt64(auditFileSize))
		fmt.Println()
	}

	if !hasData {
		fmt.Println("  No server audits configured or running.")
	}
	fmt.Println()
}

// printDatabaseAuditSpecs displays database audit specifications
func printDatabaseAuditSpecs(db *sql.DB) {
	fmt.Println("--- DATABASE AUDIT SPECIFICATIONS ---")
	fmt.Println()

	query := `
		SELECT 
			das.name AS specification_name,
			das.audit_name,
			das.create_date,
			das.modify_date,
			das.is_state_enabled,
			das.is_password_change_required
		FROM sys.database_audit_specifications das
		LEFT JOIN sys.server_audits sa ON das.audit_id = sa.audit_id
	`

	rows, err := db.Query(query)
	if err != nil {
		fmt.Printf("  Note: Could not query database audit specifications.\n")
		fmt.Printf("  Error: %v\n\n", err)
		return
	}
	defer rows.Close()

	hasData := false
	for rows.Next() {
		hasData = true
		var (
			specName       sql.NullString
			auditName      sql.NullString
			createDate     sql.NullTime
			modifyDate     sql.NullTime
			isStateEnabled sql.NullBool
			pwdChangeReq   sql.NullBool
		)

		if err := rows.Scan(&specName, &auditName, &createDate, &modifyDate, &isStateEnabled, &pwdChangeReq); err != nil {
			fmt.Printf("  Error scanning row: %v\n", err)
			continue
		}

		fmt.Printf("  Specification: %s\n", nullString(specName))
		fmt.Printf("    Audit Name: %s\n", nullString(auditName))
		fmt.Printf("    Created: %s\n", nullTime(createDate))
		fmt.Printf("    Modified: %s\n", nullTime(modifyDate))
		fmt.Printf("    Enabled: %v\n", nullBool(isStateEnabled))
		fmt.Println()
	}

	if !hasData {
		fmt.Println("  No database audit specifications found.")
	}
	fmt.Println()
}

// printAuditActions displays audit action information
func printAuditActions(db *sql.DB) {
	fmt.Println("--- AUDIT ACTIONS (sys.dm_audit_actions) ---")
	fmt.Println()

	query := `
		SELECT TOP 50
			action_id,
			name,
			class_desc,
			parent_action_id,
			configuration_level,
			permission_name,
			containing_group_name
		FROM sys.dm_audit_actions
		WHERE parent_action_id IS NULL
		ORDER BY class_desc, name
	`

	rows, err := db.Query(query)
	if err != nil {
		fmt.Printf("  Note: Could not query sys.dm_audit_actions.\n")
		fmt.Printf("  Error: %v\n\n", err)
		return
	}
	defer rows.Close()

	fmt.Printf("%-12s %-30s %-20s %-15s\n", "Action ID", "Action Name", "Class", "Permission")
	fmt.Println(strings.Repeat("-", 80))

	for rows.Next() {
		var (
			actionID      sql.NullString
			name          sql.NullString
			classDesc     sql.NullString
			parentActionID sql.NullString
			configLevel   sql.NullString
			permissionName sql.NullString
			containingGrp sql.NullString
		)

		if err := rows.Scan(&actionID, &name, &classDesc, &parentActionID, &configLevel, &permissionName, &containingGrp); err != nil {
			continue
		}

		fmt.Printf("%-12s %-30s %-20s %-15s\n",
			nullString(actionID),
			nullString(name),
			nullString(classDesc),
			nullString(permissionName))
	}
	fmt.Println()
}

// printServerAuditSpecs displays server audit specification details
func printServerAuditSpecs(db *sql.DB) {
	fmt.Println("--- SERVER AUDIT SPECIFICATIONS ---")
	fmt.Println()

	query := `
		SELECT 
			sas.name AS specification_name,
			sas.audit_name,
			sas.is_state_enabled,
			sas.create_date,
			sas.modify_date
		FROM sys.server_audit_specifications sas
		LEFT JOIN sys.server_audits sa ON sas.audit_guid = sa.audit_guid
	`

	rows, err := db.Query(query)
	if err != nil {
		fmt.Printf("  Note: Could not query server audit specifications.\n")
		fmt.Printf("  Error: %v\n\n", err)
		return
	}
	defer rows.Close()

	hasData := false
	for rows.Next() {
		hasData = true
		var (
			specName       sql.NullString
			auditName      sql.NullString
			isStateEnabled sql.NullBool
			createDate     sql.NullTime
			modifyDate     sql.NullTime
		)

		if err := rows.Scan(&specName, &auditName, &isStateEnabled, &createDate, &modifyDate); err != nil {
			continue
		}

		fmt.Printf("  Specification: %s\n", nullString(specName))
		fmt.Printf("    Audit Name: %s\n", nullString(auditName))
		fmt.Printf("    Enabled: %v\n", nullBool(isStateEnabled))
		fmt.Printf("    Created: %s\n", nullTime(createDate))
		fmt.Printf("    Modified: %s\n", nullTime(modifyDate))
		fmt.Println()
	}

	if !hasData {
		fmt.Println("  No server audit specifications found.")
	}
	fmt.Println()
}

// printAuditPerformanceCounters displays audit-related performance counters
func printAuditPerformanceCounters(db *sql.DB) {
	fmt.Println("--- AUDIT-RELATED PERFORMANCE COUNTERS ---")
	fmt.Println()

	query := `
		SELECT 
			object_name,
			counter_name,
			instance_name,
			cntr_value,
			cntr_type
		FROM sys.dm_os_performance_counters
		WHERE object_name LIKE '%SQL Server Audit%'
		   OR counter_name LIKE '%Audit%'
		   OR counter_name LIKE '%Log%'
		ORDER BY object_name, counter_name
	`

	rows, err := db.Query(query)
	if err != nil {
		fmt.Printf("  Note: Could not query performance counters.\n")
		fmt.Printf("  Error: %v\n\n", err)
		return
	}
	defer rows.Close()

	fmt.Printf("%-30s %-30s %-20s %15s\n", "Object", "Counter", "Instance", "Value")
	fmt.Println(strings.Repeat("-", 100))

	hasData := false
	for rows.Next() {
		hasData = true
		var (
			objectName   sql.NullString
			counterName  sql.NullString
			instanceName sql.NullString
			cntrValue    sql.NullInt64
			cntrType     sql.NullInt64
		)

		if err := rows.Scan(&objectName, &counterName, &instanceName, &cntrValue, &cntrType); err != nil {
			continue
		}

		fmt.Printf("%-30s %-30s %-20s %15d\n",
			truncateString(nullString(objectName), 28),
			truncateString(nullString(counterName), 28),
			truncateString(nullString(instanceName), 18),
			nullInt64(cntrValue))
	}

	if !hasData {
		fmt.Println("  No audit-related performance counters found.")
	}
	fmt.Println()
}

// printRecentAuditActivity displays recent audit-related activity
func printRecentAuditActivity(db *sql.DB) {
	fmt.Println("--- RECENT AUDIT-RELATED SESSION ACTIVITY ---")
	fmt.Println()

	query := `
		SELECT TOP 20
			s.session_id,
			s.login_name,
			s.host_name,
			s.program_name,
			s.status,
			s.login_time,
			s.last_request_end_time,
			s.row_count,
			s.prev_error
		FROM sys.dm_exec_sessions s
		WHERE s.program_name LIKE '%audit%'
		   OR s.program_name LIKE '%log%'
		   OR s.last_request_end_time > DATEADD(hour, -1, GETUTCDATE())
		ORDER BY s.last_request_end_time DESC
	`

	rows, err := db.Query(query)
	if err != nil {
		fmt.Printf("  Note: Could not query session activity (requires VIEW SERVER STATE permission).\n")
		fmt.Printf("  Error: %v\n\n", err)
		return
	}
	defer rows.Close()

	fmt.Printf("%-10s %-25s %-20s %-10s %-20s\n", "Session", "Login", "Host", "Status", "Last Activity")
	fmt.Println(strings.Repeat("-", 90))

	hasData := false
	for rows.Next() {
		hasData = true
		var (
			sessionID          sql.NullInt64
			loginName          sql.NullString
			hostName           sql.NullString
			programName        sql.NullString
			status             sql.NullString
			loginTime          sql.NullTime
			lastRequestEndTime sql.NullTime
			rowCount           sql.NullInt64
			prevError          sql.NullInt64
		)

		if err := rows.Scan(&sessionID, &loginName, &hostName, &programName, &status, &loginTime, &lastRequestEndTime, &rowCount, &prevError); err != nil {
			continue
		}

		fmt.Printf("%-10d %-25s %-20s %-10s %-20s\n",
			nullInt64(sessionID),
			truncateString(nullString(loginName), 23),
			truncateString(nullString(hostName), 18),
			nullString(status),
			nullTime(lastRequestEndTime))
	}

	if !hasData {
		fmt.Println("  No recent audit-related session activity found.")
	}
	fmt.Println()
}

// Helper functions for handling NULL values
func nullString(s sql.NullString) string {
	if s.Valid {
		return s.String
	}
	return "N/A"
}

func nullInt64(i sql.NullInt64) int64 {
	if i.Valid {
		return i.Int64
	}
	return 0
}

func nullBool(b sql.NullBool) bool {
	if b.Valid {
		return b.Bool
	}
	return false
}

func nullTime(t sql.NullTime) string {
	if t.Valid {
		return t.Time.Format("2006-01-02 15:04:05")
	}
	return "N/A"
}

func truncateString(s string, maxLen int) string {
	if len(s) <= maxLen {
		return s
	}
	return s[:maxLen-2] + ".."
}

// loadConfig reads the configuration from a JSON file
func loadConfig(filename string) (*Config, error) {
	file, err := os.ReadFile(filename)
	if err != nil {
		return nil, fmt.Errorf("could not read config file '%s': %w", filename, err)
	}

	var config Config
	if err := json.Unmarshal(file, &config); err != nil {
		return nil, fmt.Errorf("could not parse config file: %w", err)
	}

	return &config, nil
}

// connectToDatabase establishes a connection to MS SQL Server
func connectToDatabase(config *Config) (*sql.DB, error) {
	// Build connection string
	connString := fmt.Sprintf(
		"server=%s;port=%d;database=%s;user id=%s;password=%s;encrypt=%s;trustservercertificate=%t",
		config.Server,
		config.Port,
		config.Database,
		config.Username,
		config.Password,
		config.Encrypt,
		config.TrustServerCertificate,
	)

	db, err := sql.Open("mssql", connString)
	if err != nil {
		return nil, fmt.Errorf("failed to open database connection: %w", err)
	}

	// Test the connection
	if err := db.Ping(); err != nil {
		return nil, fmt.Errorf("failed to ping database: %w", err)
	}

	return db, nil
}

// findAuditTables discovers audit-related tables and counts their rows
// Uses simple sequential query - no transaction needed for read-only
func findAuditTables(db *sql.DB) ([]AuditTable, error) {
	// Query to find tables with audit-related names
	// Common audit table patterns: audit, log, history, trail, event, activity
	query := `
		SELECT 
			s.name AS schema_name,
			t.name AS table_name,
			SUM(p.rows) AS row_count
		FROM sys.tables t
		INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
		INNER JOIN sys.partitions p ON t.object_id = p.object_id
		WHERE p.index_id IN (0, 1)
		AND (
			t.name LIKE '%audit%' 
			OR t.name LIKE '%log%'
			OR t.name LIKE '%history%'
			OR t.name LIKE '%trail%'
			OR t.name LIKE '%event%'
			OR t.name LIKE '%activity%'
			OR t.name LIKE '%tracking%'
			OR t.name LIKE '%transaction%'
			OR t.name LIKE '%record%'
			OR t.name LIKE '%archive%'
		)
		GROUP BY s.name, t.name
		ORDER BY s.name, t.name
	`

	rows, err := db.Query(query)
	if err != nil {
		return nil, fmt.Errorf("failed to query audit tables: %w", err)
	}
	defer rows.Close()

	var tables []AuditTable
	for rows.Next() {
		var t AuditTable
		if err := rows.Scan(&t.Schema, &t.Name, &t.RowCount); err != nil {
			return nil, fmt.Errorf("failed to scan row: %w", err)
		}
		tables = append(tables, t)
	}

	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("error iterating rows: %w", err)
	}

	return tables, nil
}

// printDryRunSummary shows what would be purged without actually deleting
func printDryRunSummary(tables []AuditTable, useTruncate bool) {
	fmt.Println("=" + strings.Repeat("=", 61))
	fmt.Println("                    DRY RUN SUMMARY")
	fmt.Println("=" + strings.Repeat("=", 61))
	fmt.Println()

	if useTruncate {
		fmt.Println("Mode: TRUNCATE (fast, minimal logging, cannot be rolled back)")
	} else {
		fmt.Println("Mode: PURGE (DELETE within transaction, can be rolled back)")
	}
	fmt.Println()

	var totalRows int64 = 0
	for _, t := range tables {
		if t.RowCount > 0 {
			totalRows += t.RowCount
		}
	}

	fmt.Printf("Tables to be cleared: %d\n", len(tables))
	fmt.Printf("Total rows to be removed: %d\n\n", totalRows)

	fmt.Println("Tables affected:")
	fmt.Println("-" + strings.Repeat("-", 61))
	for _, t := range tables {
		if t.RowCount > 0 {
			fmt.Printf("  [%s].[%s] - %d rows\n", t.Schema, t.Name, t.RowCount)
		}
	}
	fmt.Println()

	if useTruncate {
		fmt.Println("⚠️  WARNING: TRUNCATE operations cannot be rolled back!")
		fmt.Println("   - TRUNCATE is faster but permanently removes data")
		fmt.Println("   - Transaction rollback will NOT restore truncated data")
	} else {
		fmt.Println("ℹ️  INFO: DELETE operations will be performed within a transaction")
		fmt.Println("   - Changes can be rolled back if an error occurs")
		fmt.Println("   - Slower than TRUNCATE but safer for compliance")
	}
	fmt.Println()

	fmt.Println("To execute this operation, run without --dry-run")
	if useTruncate {
		fmt.Println("  ./mssql-audit-tool --truncate --force")
	} else {
		fmt.Println("  ./mssql-audit-tool --purge --force")
	}
	fmt.Println("=" + strings.Repeat("=", 61))
}

// confirmPurge prompts the user for confirmation before purging
func confirmPurge(tables []AuditTable, useTruncate bool) bool {
	fmt.Println()
	fmt.Println("=" + strings.Repeat("=", 61))
	fmt.Println("                    ⚠️  CONFIRMATION REQUIRED")
	fmt.Println("=" + strings.Repeat("=", 61))
	fmt.Println()

	var totalRows int64 = 0
	for _, t := range tables {
		if t.RowCount > 0 {
			totalRows += t.RowCount
		}
	}

	if useTruncate {
		fmt.Println("⚠️  WARNING: You are about to TRUNCATE the following tables.")
		fmt.Println("   This operation CANNOT be rolled back!")
	} else {
		fmt.Println("⚠️  WARNING: You are about to DELETE all rows from the following tables.")
		fmt.Println("   This operation will be performed within a transaction.")
	}
	fmt.Println()

	fmt.Printf("Tables: %d\n", len(tables))
	fmt.Printf("Total rows to be deleted: %d\n\n", totalRows)

	fmt.Println("Affected tables:")
	for _, t := range tables {
		if t.RowCount > 0 {
			fmt.Printf("  - [%s].[%s] (%d rows)\n", t.Schema, t.Name, t.RowCount)
		}
	}
	fmt.Println()

	fmt.Print("Are you sure you want to proceed? (yes/no): ")
	var response string
	fmt.Scanln(&response)

	return strings.ToLower(response) == "yes" || strings.ToLower(response) == "y"
}

// purgeAuditTables deletes all rows from audit tables within a single transaction
// Uses simple sequential execution - one connection, one transaction, no concurrency
func purgeAuditTables(db *sql.DB, tables []AuditTable) error {
	fmt.Println()
	fmt.Println("=" + strings.Repeat("=", 61))
	fmt.Println("                    PURGING AUDIT TABLES")
	fmt.Println("=" + strings.Repeat("=", 61))
	fmt.Println()
	fmt.Println("Starting transaction with REPEATABLE READ isolation level...")
	fmt.Println()

	// Start a single transaction
	tx, err := db.Begin()
	if err != nil {
		return fmt.Errorf("failed to begin transaction: %w", err)
	}

	// Set isolation level
	_, err = tx.Exec("SET TRANSACTION ISOLATION LEVEL REPEATABLE READ")
	if err != nil {
		tx.Rollback()
		return fmt.Errorf("failed to set isolation level: %w", err)
	}

	fmt.Println("Using DELETE statements sequentially...")
	fmt.Println()

	var totalDeleted int64 = 0
	var errors []string

	// Loop through each table and delete sequentially
	for _, t := range tables {
		if t.RowCount == 0 {
			fmt.Printf("  [%s].[%s] - SKIPPED (empty table)\n", t.Schema, t.Name)
			continue
		}

		fullName := fmt.Sprintf("[%s].[%s]", t.Schema, t.Name)
		fmt.Printf("  [%s].[%s] - deleting %d rows...", t.Schema, t.Name, t.RowCount)

		result, err := tx.Exec(fmt.Sprintf("DELETE FROM %s", fullName))
		if err != nil {
			fmt.Printf(" FAILED: %v\n", err)
			errors = append(errors, fmt.Sprintf("%s: %v", fullName, err))
			continue
		}

		rowsAffected, _ := result.RowsAffected()
		totalDeleted += rowsAffected
		fmt.Printf(" DELETED %d rows\n", rowsAffected)
	}

	if len(errors) > 0 {
		fmt.Println()
		fmt.Println("❌ Errors occurred during deletion:")
		for _, e := range errors {
			fmt.Printf("  - %s\n", e)
		}
		fmt.Println()
		fmt.Println("Rolling back transaction...")

		if err := tx.Rollback(); err != nil {
			return fmt.Errorf("failed to rollback transaction: %w", err)
		}
		fmt.Println("Transaction rolled back. No data was deleted.")
		return fmt.Errorf("deletion failed for %d table(s)", len(errors))
	}

	fmt.Println()
	fmt.Printf("Total rows deleted: %d\n", totalDeleted)
	fmt.Println()
	fmt.Print("Committing transaction... ")

	if err := tx.Commit(); err != nil {
		return fmt.Errorf("failed to commit transaction: %w", err)
	}

	fmt.Println("✓ COMMITTED")
	fmt.Println()
	fmt.Println("=" + strings.Repeat("=", 61))
	fmt.Printf("✓ Successfully purged %d tables (%d rows deleted)\n", len(tables), totalDeleted)
	fmt.Println("=" + strings.Repeat("=", 61))

	return nil
}

// truncateAuditTables truncates all audit tables (cannot be rolled back)
// Uses simple sequential execution - one connection, no transaction for TRUNCATE
func truncateAuditTables(db *sql.DB, tables []AuditTable) error {
	fmt.Println()
	fmt.Println("=" + strings.Repeat("=", 61))
	fmt.Println("                    TRUNCATING AUDIT TABLES")
	fmt.Println("=" + strings.Repeat("=", 61))
	fmt.Println()
	fmt.Println("⚠️  WARNING: TRUNCATE operations cannot be rolled back!")
	fmt.Println("Using TRUNCATE TABLE statements sequentially...")
	fmt.Println()

	var errors []string
	truncatedCount := 0

	// Loop through each table and truncate sequentially
	for _, t := range tables {
		if t.RowCount == 0 {
			fmt.Printf("  [%s].[%s] - SKIPPED (empty table)\n", t.Schema, t.Name)
			continue
		}

		fullName := fmt.Sprintf("[%s].[%s]", t.Schema, t.Name)
		fmt.Printf("  [%s].[%s] - truncating...", t.Schema, t.Name)

		_, err := db.Exec(fmt.Sprintf("TRUNCATE TABLE %s", fullName))
		if err != nil {
			fmt.Printf(" FAILED: %v\n", err)
			errors = append(errors, fmt.Sprintf("%s: %v", fullName, err))
			continue
		}

		fmt.Println(" ✓ TRUNCATED")
		truncatedCount++
	}

	fmt.Println()
	if len(errors) > 0 {
		fmt.Println("❌ Errors occurred during truncation:")
		for _, e := range errors {
			fmt.Printf("  - %s\n", e)
		}
		fmt.Println()
		fmt.Printf("Partially completed: %d tables truncated, %d failed\n", truncatedCount, len(errors))
		return fmt.Errorf("truncation failed for %d table(s)", len(errors))
	}

	fmt.Println("=" + strings.Repeat("=", 61))
	fmt.Printf("✓ Successfully truncated %d tables\n", len(tables))
	fmt.Println("=" + strings.Repeat("=", 61))

	return nil
}

// printAuditTableSummary displays the audit tables and instructions for emptying them
func printAuditTableSummary(tables []AuditTable) {
	fmt.Println("=" + strings.Repeat("=", 61))
	fmt.Println("                    AUDIT TABLES SUMMARY")
	fmt.Println("=" + strings.Repeat("=", 61))
	fmt.Println()

	// Print table header
	fmt.Printf("%-30s %-40s %15s\n", "Schema", "Table Name", "Row Count")
	fmt.Println("-" + strings.Repeat("-", 61))

	var totalRows int64 = 0
	for _, t := range tables {
		fmt.Printf("%-30s %-40s %15d\n", t.Schema, t.Name, t.RowCount)
		totalRows += t.RowCount
	}

	fmt.Println("-" + strings.Repeat("-", 61))
	fmt.Printf("%-71s %15d\n", "TOTAL ROWS IN AUDIT TABLES:", totalRows)
	fmt.Println()

	// Print instructions for emptying tables
	fmt.Println("=" + strings.Repeat("=", 61))
	fmt.Println("              COMMANDS TO EMPTY AUDIT TABLES")
	fmt.Println("=" + strings.Repeat("=", 61))
	fmt.Println()
	fmt.Println("⚠️  WARNING: The following commands will DELETE ALL DATA from the tables!")
	fmt.Println("   Review carefully before executing.\n")

	for _, t := range tables {
		if t.RowCount > 0 {
			fullName := fmt.Sprintf("[%s].[%s]", t.Schema, t.Name)
			fmt.Printf("-- Table: %s (contains %d rows)\n", fullName, t.RowCount)
			fmt.Printf("TRUNCATE TABLE %s;\n\n", fullName)
		}
	}

	// Print alternative DELETE statements for tables with foreign key constraints
	fmt.Println("=" + strings.Repeat("-", 61))
	fmt.Println("ALTERNATIVE: If TRUNCATE fails due to foreign key constraints:")
	fmt.Println("-" + strings.Repeat("-", 61))
	fmt.Println()

	for _, t := range tables {
		if t.RowCount > 0 {
			fullName := fmt.Sprintf("[%s].[%s]", t.Schema, t.Name)
			fmt.Printf("-- Table: %s\n", fullName)
			fmt.Printf("DELETE FROM %s;\n", fullName)
			if t.RowCount > 10000 {
				fmt.Printf("-- Note: Large table (%d rows). Consider batching deletes.\n", t.RowCount)
			}
			fmt.Println()
		}
	}

	fmt.Println("=" + strings.Repeat("=", 61))
	fmt.Printf("Total audit tables found: %d\n", len(tables))
	fmt.Printf("Total rows across all audit tables: %d\n", totalRows)
	fmt.Println("=" + strings.Repeat("=", 61))
}