package db

import (
	"context"
	"database/sql"
	"encoding/json"
	"errors"
	"fmt"
	"os"
	"path/filepath"
	"strings"
	"sync"
	"time"

	_ "modernc.org/sqlite"

	"ymhut-box/server/feedback-mailer/internal/config"
)

type Store struct {
	mu            sync.RWMutex
	db            *sql.DB
	dialect       dialect
	localDB       *sql.DB
	localDialect  dialect
	remoteDB      *sql.DB
	remoteDialect dialect
	cfg           *config.Config
	status        DatabaseRuntimeStatus
	hotSyncMu     sync.Mutex
	stop          chan struct{}
}

var errHotBackupMirror = errors.New("hot backup mirror sync")

type DatabaseRuntimeStatus struct {
	ActiveProvider  string `json:"activeProvider"`
	ConfigProvider  string `json:"configProvider"`
	SQLiteReady     bool   `json:"sqliteReady"`
	RemoteReady     bool   `json:"remoteReady"`
	FailoverActive  bool   `json:"failoverActive"`
	LastError       string `json:"lastError"`
	LastFailoverAt  string `json:"lastFailoverAt"`
	LastRecoveredAt string `json:"lastRecoveredAt"`
	LastSyncAt      string `json:"lastSyncAt"`
	LastSyncError   string `json:"lastSyncError"`
}

type FeedbackRecord struct {
	Code                 string   `json:"code"`
	ReceivedAt           string   `json:"receivedAt"`
	Title                string   `json:"title"`
	Type                 string   `json:"type"`
	Severity             string   `json:"severity"`
	Category             string   `json:"category"`
	Priority             string   `json:"priority"`
	Contact              string   `json:"contact"`
	Body                 string   `json:"body"`
	Status               string   `json:"status"`
	StatusDetail         string   `json:"statusDetail"`
	Note                 string   `json:"note"`
	PublicReply          string   `json:"publicReply"`
	HandledBy            string   `json:"handledBy"`
	Assignee             string   `json:"assignee"`
	DueAt                string   `json:"dueAt"`
	ResolvedAt           string   `json:"resolvedAt"`
	ArchivedAt           string   `json:"archivedAt"`
	SLALevel             string   `json:"slaLevel"`
	SourceChannel        string   `json:"sourceChannel"`
	RiskScore            int      `json:"riskScore"`
	Resolution           string   `json:"resolution"`
	PackagePath          string   `json:"packagePath"`
	EncryptedPackagePath string   `json:"encryptedPackagePath"`
	PackageSha256        string   `json:"packageSha256"`
	PlainPackageSha256   string   `json:"plainPackageSha256"`
	RemoteAddr           string   `json:"remoteAddr"`
	SummaryText          string   `json:"summaryText"`
	IncludedFiles        string   `json:"includedFiles"`
	MailSent             bool     `json:"mailSent"`
	UpdatedAt            string   `json:"updatedAt"`
	LastActivityAt       string   `json:"lastActivityAt"`
	Tags                 []string `json:"tags,omitempty"`
}

type MailRecord struct {
	ID             int64  `json:"id"`
	FeedbackCode   string `json:"feedbackCode"`
	Kind           string `json:"kind"`
	Status         string `json:"status"`
	ToAddress      string `json:"toAddress"`
	Subject        string `json:"subject"`
	PlainBody      string `json:"plainBody"`
	HTMLBody       string `json:"htmlBody"`
	AttachmentPath string `json:"attachmentPath"`
	AttachmentName string `json:"attachmentName"`
	ErrorMessage   string `json:"errorMessage"`
	CreatedAt      string `json:"createdAt"`
	SentAt         string `json:"sentAt"`
}

type StatusRow struct {
	Code         string
	Status       string
	StatusDetail string
	Category     string
	Priority     string
	PublicReply  string
	ReceivedAt   string
	UpdatedAt    string
	MailSent     bool
}

type FeedbackEvent struct {
	ID           int64  `json:"id"`
	FeedbackCode string `json:"feedbackCode"`
	EventType    string `json:"eventType"`
	Actor        string `json:"actor"`
	FromValue    string `json:"fromValue"`
	ToValue      string `json:"toValue"`
	Message      string `json:"message"`
	CreatedAt    string `json:"createdAt"`
}

type FeedbackComment struct {
	ID           int64  `json:"id"`
	FeedbackCode string `json:"feedbackCode"`
	Author       string `json:"author"`
	Body         string `json:"body"`
	Internal     bool   `json:"internal"`
	CreatedAt    string `json:"createdAt"`
}

type FeedbackDetail struct {
	FeedbackRecord
	Events   []FeedbackEvent   `json:"events"`
	Comments []FeedbackComment `json:"comments"`
}

type FeedbackFilters struct {
	Status   string
	Category string
	Priority string
	Mail     string
	Query    string
	Assignee string
	Tag      string
	SLA      string
	Overdue  string
	Sort     string
}

type FeedbackUpdate struct {
	Status       string
	Category     string
	Priority     string
	StatusDetail string
	HandledBy    string
	Assignee     string
	DueAt        string
	SLALevel     string
	Resolution   string
	Note         string
	PublicReply  string
	Actor        string
	Tags         []string
}

type FeedbackSummary struct {
	Total          int             `json:"total"`
	Today          int             `json:"today"`
	MailFailed     int             `json:"mailFailed"`
	Overdue        int             `json:"overdue"`
	StatusCounts   map[string]int  `json:"statusCounts"`
	CategoryCounts map[string]int  `json:"categoryCounts"`
	PriorityCounts map[string]int  `json:"priorityCounts"`
	SLACounts      map[string]int  `json:"slaCounts"`
	RecentEvents   []FeedbackEvent `json:"recentEvents"`
}

type AuditLog struct {
	ID        int64  `json:"id"`
	Actor     string `json:"actor"`
	Type      string `json:"type"`
	Target    string `json:"target"`
	Message   string `json:"message"`
	IP        string `json:"ip"`
	UserAgent string `json:"userAgent"`
	CreatedAt string `json:"createdAt"`
}

type WebhookDelivery struct {
	ID            int64  `json:"id"`
	WebhookName   string `json:"webhookName"`
	Event         string `json:"event"`
	Status        string `json:"status"`
	Attempts      int    `json:"attempts"`
	ResponseCode  int    `json:"responseCode"`
	ErrorMessage  string `json:"errorMessage"`
	PayloadSHA256 string `json:"payloadSha256"`
	CreatedAt     string `json:"createdAt"`
	FinishedAt    string `json:"finishedAt"`
}

type Page[T any] struct {
	Items      []T `json:"items"`
	Total      int `json:"total"`
	Page       int `json:"page"`
	PerPage    int `json:"perPage"`
	TotalPages int `json:"totalPages"`
	Offset     int `json:"offset"`
}

type Overview struct {
	FeedbackTotal  int             `json:"feedbackTotal"`
	TodayFeedback  int             `json:"todayFeedback"`
	MailFailed     int             `json:"mailFailed"`
	MailTotal      int             `json:"mailTotal"`
	Overdue        int             `json:"overdue"`
	StatusCounts   map[string]int  `json:"statusCounts"`
	CategoryCounts map[string]int  `json:"categoryCounts"`
	PriorityCounts map[string]int  `json:"priorityCounts"`
	SLACounts      map[string]int  `json:"slaCounts"`
	Storage        StorageInfo     `json:"storage"`
	Database       DatabaseInfo    `json:"database"`
	Mail           MailInfo        `json:"mail"`
	RecentEvents   []FeedbackEvent `json:"recentEvents"`
}

type StorageInfo struct {
	Path  string `json:"path"`
	Bytes int64  `json:"bytes"`
}

type DatabaseInfo struct {
	Path    string `json:"path"`
	Bytes   int64  `json:"bytes"`
	WALMode string `json:"walMode"`
}

type MailInfo struct {
	Configured       bool   `json:"configured"`
	Host             string `json:"host"`
	Port             int    `json:"port"`
	Secure           string `json:"secure"`
	FromAddress      string `json:"fromAddress"`
	DeveloperAddress string `json:"developerAddress"`
}

func Open(cfg *config.Config) (*Store, error) {
	if err := os.MkdirAll(cfg.StorageDir, 0o750); err != nil {
		return nil, err
	}
	if err := os.MkdirAll(filepath.Dir(cfg.Database.SQLitePath), 0o750); err != nil {
		return nil, err
	}

	localCfg := cfg.Database
	localCfg.Provider = "sqlite"
	localCfg.SQLitePath = cfg.Database.SQLitePath
	local, localDialect, err := openSQLDatabase(localCfg, cfg.BaseDir)
	if err != nil {
		return nil, err
	}
	local.SetMaxOpenConns(1)
	store := &Store{
		db:           local,
		dialect:      localDialect,
		localDB:      local,
		localDialect: localDialect,
		cfg:          cfg,
		stop:         make(chan struct{}),
		status: DatabaseRuntimeStatus{
			ActiveProvider: "sqlite",
			ConfigProvider: cfg.Database.Provider,
			SQLiteReady:    true,
		},
	}
	if err := store.migrate(); err != nil {
		local.Close()
		return nil, err
	}
	if cfg.Database.Provider != "sqlite" {
		if err := store.openRemote(cfg.Database); err != nil {
			store.status.LastError = err.Error()
			store.status.FailoverActive = true
			store.status.LastFailoverAt = Now()
		}
	}
	go store.maintain()
	return store, nil
}

func (s *Store) Close() error {
	close(s.stop)
	s.mu.Lock()
	defer s.mu.Unlock()
	var err error
	if s.remoteDB != nil && s.remoteDB != s.localDB {
		err = s.remoteDB.Close()
	}
	if s.localDB != nil {
		if closeErr := s.localDB.Close(); err == nil {
			err = closeErr
		}
	}
	return err
}

func (s *Store) DB() *sql.DB {
	s.mu.RLock()
	defer s.mu.RUnlock()
	return s.db
}

func (s *Store) openRemote(cfg config.DatabaseConfig) error {
	remote, remoteDialect, err := openSQLDatabase(cfg, s.cfg.BaseDir)
	if err != nil {
		return err
	}
	if err := migrateDatabase(remote, remoteDialect); err != nil {
		_ = remote.Close()
		return err
	}
	s.mu.Lock()
	if s.remoteDB != nil && s.remoteDB != s.localDB && s.remoteDB != remote {
		_ = s.remoteDB.Close()
	}
	s.remoteDB = remote
	s.remoteDialect = remoteDialect
	s.db = remote
	s.dialect = remoteDialect
	s.status.ActiveProvider = cfg.Provider
	s.status.ConfigProvider = cfg.Provider
	s.status.RemoteReady = true
	s.status.FailoverActive = false
	s.status.LastError = ""
	s.status.LastRecoveredAt = Now()
	s.mu.Unlock()
	return nil
}

func (s *Store) maintain() {
	ticker := time.NewTicker(time.Duration(s.cfg.Database.HealthIntervalSeconds) * time.Second)
	defer ticker.Stop()
	lastSync := time.Now()
	for {
		select {
		case <-ticker.C:
			s.checkRemote()
			if s.cfg.Database.Sync.Enabled && time.Since(lastSync) >= time.Duration(s.cfg.Database.Sync.IntervalSeconds)*time.Second {
				s.syncRemoteToSQLite()
				lastSync = time.Now()
			}
		case <-s.stop:
			return
		}
	}
}

func (s *Store) checkRemote() {
	if s.cfg.Database.Provider == "sqlite" {
		return
	}
	s.mu.RLock()
	remote := s.remoteDB
	s.mu.RUnlock()
	if remote == nil {
		if err := s.openRemote(s.cfg.Database); err != nil {
			s.markFailover(err)
		}
		return
	}
	ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
	err := remote.PingContext(ctx)
	cancel()
	if err != nil {
		s.markFailover(err)
		return
	}
	s.mu.RLock()
	wasFailover := s.status.FailoverActive
	s.mu.RUnlock()
	if wasFailover {
		if _, err := s.ImportSQLiteToRemote(); err != nil {
			s.markFailover(err)
			return
		}
	}
	s.mu.Lock()
	if s.db == s.localDB {
		s.db = s.remoteDB
		s.dialect = s.remoteDialect
		s.status.ActiveProvider = s.cfg.Database.Provider
		s.status.FailoverActive = false
		s.status.RemoteReady = true
		s.status.LastRecoveredAt = Now()
		s.status.LastError = ""
	}
	s.mu.Unlock()
}

func (s *Store) markFailover(err error) {
	if err == nil || !s.cfg.Database.FailoverEnabled {
		return
	}
	s.mu.Lock()
	defer s.mu.Unlock()
	s.db = s.localDB
	s.dialect = s.localDialect
	s.status.ActiveProvider = "sqlite"
	s.status.ConfigProvider = s.cfg.Database.Provider
	s.status.FailoverActive = true
	s.status.RemoteReady = false
	s.status.LastError = err.Error()
	s.status.LastFailoverAt = Now()
}

func (s *Store) active() (*sql.DB, dialect) {
	s.mu.RLock()
	defer s.mu.RUnlock()
	return s.db, s.dialect
}

func (s *Store) exec(query string, args ...any) (sql.Result, error) {
	conn, d := s.active()
	result, err := conn.Exec(d.rebind(query), args...)
	if err != nil {
		s.markFailover(err)
		conn, d = s.active()
		if conn != nil {
			result, err = conn.Exec(d.rebind(query), args...)
		}
	}
	if err == nil {
		s.scheduleHotBackupSync(conn)
	}
	return result, err
}

func (s *Store) query(query string, args ...any) (*sql.Rows, error) {
	conn, d := s.active()
	rows, err := conn.Query(d.rebind(query), args...)
	if err != nil {
		s.markFailover(err)
		conn, d = s.active()
		if conn != nil {
			rows, err = conn.Query(d.rebind(query), args...)
		}
	}
	return rows, err
}

func (s *Store) queryRow(query string, args ...any) *sql.Row {
	conn, d := s.active()
	return conn.QueryRow(d.rebind(query), args...)
}

func (s *Store) scanRow(query string, args []any, dest ...any) error {
	conn, d := s.active()
	err := conn.QueryRow(d.rebind(query), args...).Scan(dest...)
	if err == nil || errors.Is(err, sql.ErrNoRows) {
		return err
	}
	s.markFailover(err)
	nextConn, nextDialect := s.active()
	if nextConn != nil && nextConn != conn {
		return nextConn.QueryRow(nextDialect.rebind(query), args...).Scan(dest...)
	}
	return err
}

func (s *Store) insertID(query string, args ...any) (int64, error) {
	conn, d := s.active()
	if d.name == "postgres" {
		query = strings.TrimSpace(query) + " RETURNING id"
		var id int64
		err := conn.QueryRow(d.rebind(query), args...).Scan(&id)
		if err != nil {
			s.markFailover(err)
			conn, d = s.active()
			if d.name == "postgres" {
				err = conn.QueryRow(d.rebind(query), args...).Scan(&id)
			} else {
				result, execErr := conn.Exec(d.rebind(strings.TrimSuffix(query, " RETURNING id")), args...)
				if execErr != nil {
					return 0, execErr
				}
				id, err = result.LastInsertId()
			}
		}
		if err == nil {
			s.scheduleHotBackupSync(conn)
		}
		return id, err
	}
	result, err := s.exec(query, args...)
	if err != nil {
		return 0, err
	}
	return result.LastInsertId()
}

type storeTx struct {
	tx       *sql.Tx
	dialect  dialect
	onCommit func()
}

func (s *Store) begin() (*storeTx, error) {
	conn, d := s.active()
	tx, err := conn.Begin()
	if err != nil {
		s.markFailover(err)
		conn, d = s.active()
		tx, err = conn.Begin()
	}
	if err != nil {
		return nil, err
	}
	return &storeTx{tx: tx, dialect: d, onCommit: func() { s.scheduleHotBackupSync(conn) }}, nil
}

func (tx *storeTx) Exec(query string, args ...any) (sql.Result, error) {
	return tx.tx.Exec(tx.dialect.rebind(query), args...)
}

func (tx *storeTx) Commit() error {
	err := tx.tx.Commit()
	if err == nil && tx.onCommit != nil {
		tx.onCommit()
	}
	return err
}

func (tx *storeTx) Rollback() error {
	return tx.tx.Rollback()
}

func (s *Store) scheduleHotBackupSync(source *sql.DB) {
	s.mu.RLock()
	shouldSync := source != nil &&
		source == s.remoteDB &&
		s.localDB != nil &&
		s.localDB != source &&
		s.cfg.Database.Sync.Enabled
	s.mu.RUnlock()
	if !shouldSync {
		return
	}
	go func() {
		s.hotSyncMu.Lock()
		defer s.hotSyncMu.Unlock()
		if _, err := s.syncRemoteToSQLite(); err != nil {
			s.setMirrorError(err)
		}
	}()
}

func (s *Store) setMirrorError(err error) {
	if err == nil {
		return
	}
	s.mu.Lock()
	defer s.mu.Unlock()
	s.status.LastSyncError = fmt.Errorf("%w: %v", errHotBackupMirror, err).Error()
}

func (s *Store) Status() DatabaseRuntimeStatus {
	s.mu.RLock()
	defer s.mu.RUnlock()
	return s.status
}

func (s *Store) ApplyDatabaseConfig(next config.DatabaseConfig) error {
	s.cfg.Database = next
	if err := s.replaceLocalSQLite(next); err != nil {
		s.markFailover(err)
		return err
	}
	if next.Provider == "sqlite" {
		s.mu.Lock()
		remote := s.remoteDB
		s.remoteDB = nil
		s.db = s.localDB
		s.dialect = s.localDialect
		s.status.ActiveProvider = "sqlite"
		s.status.ConfigProvider = "sqlite"
		s.status.RemoteReady = false
		s.status.FailoverActive = false
		s.status.LastError = ""
		s.mu.Unlock()
		if remote != nil && remote != s.localDB {
			_ = remote.Close()
		}
		return nil
	}
	if err := s.openRemote(next); err != nil {
		s.markFailover(err)
		return err
	}
	return nil
}

func (s *Store) replaceLocalSQLite(next config.DatabaseConfig) error {
	localCfg := next
	localCfg.Provider = "sqlite"
	if localCfg.SQLitePath == "" {
		localCfg.SQLitePath = filepath.Join(s.cfg.BaseDir, "storage", "feedback.sqlite")
	}
	local, localDialect, err := openSQLDatabase(localCfg, s.cfg.BaseDir)
	if err != nil {
		return err
	}
	local.SetMaxOpenConns(1)
	if err := migrateDatabase(local, localDialect); err != nil {
		_ = local.Close()
		return err
	}

	s.mu.Lock()
	oldLocal := s.localDB
	activeWasLocal := s.db == s.localDB
	s.localDB = local
	s.localDialect = localDialect
	s.status.SQLiteReady = true
	if activeWasLocal || next.Provider == "sqlite" {
		s.db = local
		s.dialect = localDialect
	}
	s.mu.Unlock()
	if oldLocal != nil && oldLocal != local {
		_ = oldLocal.Close()
	}
	return nil
}

func (s *Store) migrate() error {
	conn, activeDialect := s.active()
	return migrateDatabase(conn, activeDialect)
}

func migrateDatabase(conn *sql.DB, activeDialect dialect) error {
	statements := []string{}
	if activeDialect.name == "sqlite" {
		statements = append(statements,
			`PRAGMA busy_timeout = 5000`,
			`PRAGMA journal_mode = WAL`,
			`PRAGMA foreign_keys = ON`,
		)
	}
	statements = append(statements,
		`CREATE TABLE IF NOT EXISTS feedbacks (
			code TEXT PRIMARY KEY,
			received_at TEXT NOT NULL,
			title TEXT NOT NULL,
			type TEXT NOT NULL,
			severity TEXT NOT NULL,
			category TEXT NOT NULL DEFAULT '',
			priority TEXT NOT NULL DEFAULT '',
			contact TEXT NOT NULL,
			body TEXT NOT NULL,
			status TEXT NOT NULL,
			status_detail TEXT NOT NULL DEFAULT '',
			note TEXT NOT NULL DEFAULT '',
			public_reply TEXT NOT NULL DEFAULT '',
			handled_by TEXT NOT NULL DEFAULT '',
			assignee TEXT NOT NULL DEFAULT '',
			due_at TEXT NOT NULL DEFAULT '',
			resolved_at TEXT NOT NULL DEFAULT '',
			archived_at TEXT NOT NULL DEFAULT '',
			sla_level TEXT NOT NULL DEFAULT '',
			source_channel TEXT NOT NULL DEFAULT '',
			risk_score INTEGER NOT NULL DEFAULT 0,
			resolution TEXT NOT NULL DEFAULT '',
			package_path TEXT NOT NULL,
			encrypted_package_path TEXT NOT NULL,
			package_sha256 TEXT NOT NULL,
			plain_package_sha256 TEXT NOT NULL,
			remote_addr TEXT NOT NULL,
			summary_text TEXT NOT NULL,
			included_files TEXT NOT NULL DEFAULT '',
			mail_sent INTEGER NOT NULL DEFAULT 0,
			updated_at TEXT NOT NULL,
			last_activity_at TEXT NOT NULL DEFAULT ''
		)`,
		fmt.Sprintf(`CREATE TABLE IF NOT EXISTS mail_records (
			id %s,
			feedback_code TEXT NOT NULL DEFAULT '',
			kind TEXT NOT NULL,
			status TEXT NOT NULL,
			to_address TEXT NOT NULL,
			subject TEXT NOT NULL,
			plain_body TEXT NOT NULL,
			html_body TEXT NOT NULL,
			attachment_path TEXT NOT NULL DEFAULT '',
			attachment_name TEXT NOT NULL DEFAULT '',
			error_message TEXT NOT NULL DEFAULT '',
			created_at TEXT NOT NULL,
			sent_at TEXT NOT NULL DEFAULT ''
		)`, activeDialect.idType()),
		fmt.Sprintf(`CREATE TABLE IF NOT EXISTS feedback_events (
			id %s,
			feedback_code TEXT NOT NULL,
			event_type TEXT NOT NULL,
			actor TEXT NOT NULL DEFAULT '',
			from_value TEXT NOT NULL DEFAULT '',
			to_value TEXT NOT NULL DEFAULT '',
			message TEXT NOT NULL DEFAULT '',
			created_at TEXT NOT NULL
		)`, activeDialect.idType()),
		fmt.Sprintf(`CREATE TABLE IF NOT EXISTS feedback_comments (
			id %s,
			feedback_code TEXT NOT NULL,
			author TEXT NOT NULL DEFAULT '',
			body TEXT NOT NULL,
			internal INTEGER NOT NULL DEFAULT 1,
			created_at TEXT NOT NULL
		)`, activeDialect.idType()),
		`CREATE TABLE IF NOT EXISTS feedback_tags (
			feedback_code TEXT NOT NULL,
			tag TEXT NOT NULL,
			created_at TEXT NOT NULL,
			PRIMARY KEY (feedback_code, tag)
		)`,
		fmt.Sprintf(`CREATE TABLE IF NOT EXISTS audit_logs (
			id %s,
			actor TEXT NOT NULL DEFAULT '',
			type TEXT NOT NULL,
			target TEXT NOT NULL DEFAULT '',
			message TEXT NOT NULL DEFAULT '',
			ip TEXT NOT NULL DEFAULT '',
			user_agent TEXT NOT NULL DEFAULT '',
			created_at TEXT NOT NULL
		)`, activeDialect.idType()),
		fmt.Sprintf(`CREATE TABLE IF NOT EXISTS webhook_deliveries (
			id %s,
			webhook_name TEXT NOT NULL,
			event TEXT NOT NULL,
			status TEXT NOT NULL,
			attempts INTEGER NOT NULL DEFAULT 0,
			response_code INTEGER NOT NULL DEFAULT 0,
			error_message TEXT NOT NULL DEFAULT '',
			payload_sha256 TEXT NOT NULL DEFAULT '',
			created_at TEXT NOT NULL,
			finished_at TEXT NOT NULL DEFAULT ''
		)`, activeDialect.idType()),
	)
	for _, statement := range statements {
		statement = activeDialect.columnDefault(statement)
		if _, err := conn.Exec(activeDialect.rebind(statement)); err != nil {
			return err
		}
	}

	columns := map[string]string{
		"note":                   `TEXT NOT NULL DEFAULT ''`,
		"public_reply":           `TEXT NOT NULL DEFAULT ''`,
		"category":               `TEXT NOT NULL DEFAULT ''`,
		"priority":               `TEXT NOT NULL DEFAULT ''`,
		"status_detail":          `TEXT NOT NULL DEFAULT ''`,
		"handled_by":             `TEXT NOT NULL DEFAULT ''`,
		"assignee":               `TEXT NOT NULL DEFAULT ''`,
		"due_at":                 `TEXT NOT NULL DEFAULT ''`,
		"resolved_at":            `TEXT NOT NULL DEFAULT ''`,
		"archived_at":            `TEXT NOT NULL DEFAULT ''`,
		"sla_level":              `TEXT NOT NULL DEFAULT ''`,
		"source_channel":         `TEXT NOT NULL DEFAULT ''`,
		"risk_score":             `INTEGER NOT NULL DEFAULT 0`,
		"resolution":             `TEXT NOT NULL DEFAULT ''`,
		"last_activity_at":       `TEXT NOT NULL DEFAULT ''`,
		"included_files":         `TEXT NOT NULL DEFAULT ''`,
		"mail_sent":              `INTEGER NOT NULL DEFAULT 0`,
		"encrypted_package_path": `TEXT NOT NULL DEFAULT ''`,
		"plain_package_sha256":   `TEXT NOT NULL DEFAULT ''`,
		"updated_at":             `TEXT NOT NULL DEFAULT ''`,
	}
	for column, definition := range columns {
		definition = activeDialect.columnDefault(definition)
		if err := ensureColumnDatabase(conn, activeDialect, "feedbacks", column, definition); err != nil {
			return err
		}
	}

	indexes := []string{
		`CREATE INDEX IF NOT EXISTS idx_feedbacks_received_at ON feedbacks (received_at DESC)`,
		`CREATE INDEX IF NOT EXISTS idx_feedbacks_status ON feedbacks (status)`,
		`CREATE INDEX IF NOT EXISTS idx_feedbacks_category ON feedbacks (category)`,
		`CREATE INDEX IF NOT EXISTS idx_feedbacks_priority ON feedbacks (priority)`,
		`CREATE INDEX IF NOT EXISTS idx_feedbacks_assignee ON feedbacks (assignee)`,
		`CREATE INDEX IF NOT EXISTS idx_feedbacks_sla ON feedbacks (sla_level)`,
		`CREATE INDEX IF NOT EXISTS idx_feedbacks_due ON feedbacks (due_at)`,
		`CREATE INDEX IF NOT EXISTS idx_feedbacks_last_activity ON feedbacks (last_activity_at DESC)`,
		`CREATE INDEX IF NOT EXISTS idx_feedback_events_code_created ON feedback_events (feedback_code, created_at DESC)`,
		`CREATE INDEX IF NOT EXISTS idx_feedback_events_created ON feedback_events (created_at DESC)`,
		`CREATE INDEX IF NOT EXISTS idx_feedback_comments_code_created ON feedback_comments (feedback_code, created_at DESC)`,
		`CREATE INDEX IF NOT EXISTS idx_feedback_tags_tag ON feedback_tags (tag)`,
		`CREATE INDEX IF NOT EXISTS idx_audit_logs_created ON audit_logs (created_at DESC, id DESC)`,
		`CREATE INDEX IF NOT EXISTS idx_audit_logs_type ON audit_logs (type)`,
		`CREATE INDEX IF NOT EXISTS idx_webhook_deliveries_created ON webhook_deliveries (created_at DESC, id DESC)`,
		`CREATE INDEX IF NOT EXISTS idx_webhook_deliveries_status ON webhook_deliveries (status)`,
		`CREATE INDEX IF NOT EXISTS idx_mail_records_created_id ON mail_records (created_at DESC, id DESC)`,
		`CREATE INDEX IF NOT EXISTS idx_mail_records_status ON mail_records (status)`,
	}
	for _, statement := range indexes {
		if _, err := conn.Exec(activeDialect.rebind(statement)); err != nil {
			return err
		}
	}
	if err := backfillWorkflowColumnsDatabase(conn, activeDialect); err != nil {
		return err
	}
	return nil
}

func (s *Store) ensureColumn(table, column, definition string) error {
	conn, activeDialect := s.active()
	return ensureColumnDatabase(conn, activeDialect, table, column, activeDialect.columnDefault(definition))
}

func ensureColumnDatabase(conn *sql.DB, activeDialect dialect, table, column, definition string) error {
	if !validIdentifier(table) || !validIdentifier(column) {
		return errors.New("invalid schema identifier")
	}

	var rows *sql.Rows
	var err error
	switch activeDialect.name {
	case "mysql":
		rows, err = conn.Query(activeDialect.rebind(`SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ?`), table)
	case "postgres":
		rows, err = conn.Query(activeDialect.rebind(`SELECT column_name FROM information_schema.columns WHERE table_schema = current_schema() AND table_name = ?`), table)
	default:
		rows, err = conn.Query(`PRAGMA table_info(` + table + `)`)
	}
	if err != nil {
		return err
	}
	defer rows.Close()

	for rows.Next() {
		var name string
		if activeDialect.name == "sqlite" {
			var cid int
			var typ string
			var notNull int
			var dflt sql.NullString
			var pk int
			if err := rows.Scan(&cid, &name, &typ, &notNull, &dflt, &pk); err != nil {
				return err
			}
		} else {
			if err := rows.Scan(&name); err != nil {
				return err
			}
		}
		if strings.EqualFold(name, column) {
			return nil
		}
	}
	if err := rows.Err(); err != nil {
		return err
	}

	_, err = conn.Exec(activeDialect.rebind(`ALTER TABLE ` + table + ` ADD COLUMN ` + column + ` ` + definition))
	return err
}

func validIdentifier(value string) bool {
	if value == "" {
		return false
	}
	for index, char := range value {
		if char == '_' || char >= 'a' && char <= 'z' || char >= 'A' && char <= 'Z' || index > 0 && char >= '0' && char <= '9' {
			continue
		}
		return false
	}
	return true
}

func (s *Store) FetchStatus(code string) (*StatusRow, error) {
	var result StatusRow
	var mailSent int
	if err := s.scanRow(
		`SELECT code, status, status_detail, category, priority, public_reply, received_at, updated_at, mail_sent FROM feedbacks WHERE code = ? LIMIT 1`,
		[]any{code},
		&result.Code, &result.Status, &result.StatusDetail, &result.Category, &result.Priority, &result.PublicReply, &result.ReceivedAt, &result.UpdatedAt, &mailSent,
	); err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			return nil, nil
		}
		return nil, err
	}
	result.MailSent = mailSent == 1
	return &result, nil
}

func (s *Store) InsertFeedback(record FeedbackRecord) error {
	if strings.TrimSpace(record.Category) == "" {
		record.Category = normalizeCategory(record.Type)
	} else {
		record.Category = normalizeCategory(record.Category)
	}
	if strings.TrimSpace(record.Priority) == "" {
		record.Priority = normalizePriority(record.Severity)
	} else {
		record.Priority = normalizePriority(record.Priority)
	}
	record.SLALevel = defaultString(normalizeSLA(record.SLALevel), defaultSLA(record.Priority))
	record.SourceChannel = defaultString(record.SourceChannel, "winui")
	record.RiskScore = defaultRisk(record.RiskScore, record.Priority)
	if record.LastActivityAt == "" {
		record.LastActivityAt = record.UpdatedAt
	}
	_, err := s.exec(
		`INSERT INTO feedbacks (
			code, received_at, title, type, severity, category, priority, contact, body, status, status_detail, note, public_reply, handled_by,
			assignee, due_at, resolved_at, archived_at, sla_level, source_channel, risk_score, resolution,
			package_path, encrypted_package_path, package_sha256, plain_package_sha256,
			remote_addr, summary_text, included_files, mail_sent, updated_at, last_activity_at
		) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
		record.Code, record.ReceivedAt, record.Title, record.Type, record.Severity, record.Category, record.Priority, record.Contact, record.Body, record.Status,
		record.StatusDetail, record.Note, record.PublicReply, record.HandledBy, record.Assignee, record.DueAt, record.ResolvedAt, record.ArchivedAt,
		record.SLALevel, record.SourceChannel, record.RiskScore, record.Resolution, record.PackagePath, record.EncryptedPackagePath, record.PackageSha256,
		record.PlainPackageSha256, record.RemoteAddr, record.SummaryText, record.IncludedFiles, boolInt(record.MailSent), record.UpdatedAt, record.LastActivityAt,
	)
	if err != nil {
		return err
	}
	if len(record.Tags) > 0 {
		_ = s.ReplaceTags(record.Code, record.Tags)
	}
	return s.InsertEvent(FeedbackEvent{
		FeedbackCode: record.Code,
		EventType:    "created",
		Actor:        "system",
		ToValue:      record.Status,
		Message:      "Ticket created",
		CreatedAt:    record.ReceivedAt,
	})
}

func (s *Store) UpdateFeedbackMailState(code string, sent bool) error {
	now := Now()
	_, err := s.exec(`UPDATE feedbacks SET mail_sent = ?, updated_at = ?, last_activity_at = ? WHERE code = ?`, boolInt(sent), now, now, code)
	if err != nil {
		return err
	}
	state := "failed"
	if sent {
		state = "sent"
	}
	return s.InsertEvent(FeedbackEvent{
		FeedbackCode: code,
		EventType:    "mail",
		Actor:        "system",
		ToValue:      state,
		Message:      "Mail notification " + state,
		CreatedAt:    now,
	})
}

func (s *Store) UpdateFeedback(code string, update FeedbackUpdate) error {
	current, err := s.GetFeedback(code)
	if err != nil {
		return err
	}
	if current == nil {
		return sql.ErrNoRows
	}

	if update.Category == "" {
		update.Category = current.Category
	}
	if update.Priority == "" {
		update.Priority = current.Priority
	}
	if update.Status == "" {
		update.Status = current.Status
	}
	if update.SLALevel == "" {
		update.SLALevel = current.SLALevel
	}
	update.Category = normalizeCategory(update.Category)
	update.Priority = normalizePriority(update.Priority)
	update.SLALevel = normalizeSLA(update.SLALevel)
	riskScore := defaultRisk(0, update.Priority)
	resolvedAt := current.ResolvedAt
	archivedAt := current.ArchivedAt
	now := Now()
	if update.Status == "resolved" && resolvedAt == "" {
		resolvedAt = now
	}
	if update.Status == "archived" && archivedAt == "" {
		archivedAt = now
	}

	_, err = s.exec(
		`UPDATE feedbacks
		 SET status = ?, category = ?, priority = ?, status_detail = ?, handled_by = ?,
		     assignee = ?, due_at = ?, resolved_at = ?, archived_at = ?, sla_level = ?,
		     risk_score = ?, resolution = ?, note = ?, public_reply = ?, updated_at = ?, last_activity_at = ?
		 WHERE code = ?`,
		update.Status, update.Category, update.Priority, update.StatusDetail, update.HandledBy,
		update.Assignee, update.DueAt, resolvedAt, archivedAt, update.SLALevel, riskScore,
		update.Resolution, update.Note, update.PublicReply, now, now, code,
	)
	if err != nil {
		return err
	}

	actor := strings.TrimSpace(update.Actor)
	if actor == "" {
		actor = "admin"
	}
	changes := []struct {
		event string
		from  string
		to    string
		msg   string
	}{
		{"status", current.Status, update.Status, "Status changed"},
		{"category", current.Category, update.Category, "Category changed"},
		{"priority", current.Priority, update.Priority, "Priority changed"},
		{"status_detail", current.StatusDetail, update.StatusDetail, "Status detail changed"},
		{"handled_by", current.HandledBy, update.HandledBy, "Handler changed"},
		{"assignee", current.Assignee, update.Assignee, "Assignee changed"},
		{"due_at", current.DueAt, update.DueAt, "Due date changed"},
		{"sla", current.SLALevel, update.SLALevel, "SLA changed"},
	}
	for _, change := range changes {
		if change.from != change.to {
			_ = s.InsertEvent(FeedbackEvent{FeedbackCode: code, EventType: change.event, Actor: actor, FromValue: change.from, ToValue: change.to, Message: change.msg, CreatedAt: now})
		}
	}
	if current.PublicReply != update.PublicReply {
		_ = s.InsertEvent(FeedbackEvent{FeedbackCode: code, EventType: "public_reply", Actor: actor, Message: "Public reply changed", CreatedAt: now})
	}
	if current.Note != update.Note {
		_ = s.InsertEvent(FeedbackEvent{FeedbackCode: code, EventType: "note", Actor: actor, Message: "Internal note changed", CreatedAt: now})
	}
	if current.Resolution != update.Resolution {
		_ = s.InsertEvent(FeedbackEvent{FeedbackCode: code, EventType: "resolution", Actor: actor, Message: "Resolution changed", CreatedAt: now})
	}
	if update.Tags != nil {
		if err := s.ReplaceTags(code, update.Tags); err != nil {
			return err
		}
		_ = s.InsertEvent(FeedbackEvent{FeedbackCode: code, EventType: "tags", Actor: actor, ToValue: strings.Join(normalizeTags(update.Tags), ","), Message: "Tags changed", CreatedAt: now})
	}
	return nil
}

func (s *Store) GetFeedback(code string) (*FeedbackRecord, error) {
	rows, err := s.query(feedbackSelectSQL()+` WHERE code = ? LIMIT 1`, code)
	if err != nil {
		return nil, err
	}
	if !rows.Next() {
		rows.Close()
		return nil, nil
	}
	item, err := scanFeedback(rows)
	if err != nil {
		rows.Close()
		return nil, err
	}
	if err := rows.Close(); err != nil {
		return nil, err
	}
	tags, err := s.ListTags(code)
	if err != nil {
		return nil, err
	}
	item.Tags = tags
	return &item, nil
}

func (s *Store) GetFeedbackDetail(code string) (*FeedbackDetail, error) {
	record, err := s.GetFeedback(code)
	if err != nil || record == nil {
		return nil, err
	}
	events, err := s.ListEvents(code, 100)
	if err != nil {
		return nil, err
	}
	comments, err := s.ListComments(code)
	if err != nil {
		return nil, err
	}
	return &FeedbackDetail{FeedbackRecord: *record, Events: events, Comments: comments}, nil
}

func (s *Store) ListFeedbacks(page, perPage int, filters FeedbackFilters) (Page[FeedbackRecord], error) {
	page, perPage = normalizePage(page, perPage)
	where, args := feedbackFilters(filters)
	total, err := s.count(`feedbacks`, where, args)
	if err != nil {
		return Page[FeedbackRecord]{}, err
	}
	page, totalPages, offset := finishPage(total, page, perPage)

	sqlText := feedbackSelectSQL() + where + feedbackOrder(filters.Sort) + ` LIMIT ? OFFSET ?`
	args = append(args, perPage, offset)
	rows, err := s.query(sqlText, args...)
	if err != nil {
		return Page[FeedbackRecord]{}, err
	}

	items := []FeedbackRecord{}
	for rows.Next() {
		item, err := scanFeedback(rows)
		if err != nil {
			rows.Close()
			return Page[FeedbackRecord]{}, err
		}
		items = append(items, item)
	}
	if err := rows.Err(); err != nil {
		rows.Close()
		return Page[FeedbackRecord]{}, err
	}
	if err := rows.Close(); err != nil {
		return Page[FeedbackRecord]{}, err
	}
	if err := s.attachTags(items); err != nil {
		return Page[FeedbackRecord]{}, err
	}
	return Page[FeedbackRecord]{Items: items, Total: total, Page: page, PerPage: perPage, TotalPages: totalPages, Offset: offset}, nil
}

func (s *Store) ExportFeedbacks(filters FeedbackFilters, limit int) ([]FeedbackRecord, error) {
	if limit <= 0 || limit > 20000 {
		limit = 5000
	}
	where, args := feedbackFilters(filters)
	args = append(args, limit)
	rows, err := s.query(feedbackSelectSQL()+where+feedbackOrder(filters.Sort)+` LIMIT ?`, args...)
	if err != nil {
		return nil, err
	}
	items := []FeedbackRecord{}
	for rows.Next() {
		item, err := scanFeedback(rows)
		if err != nil {
			rows.Close()
			return nil, err
		}
		items = append(items, item)
	}
	if err := rows.Err(); err != nil {
		rows.Close()
		return nil, err
	}
	if err := rows.Close(); err != nil {
		return nil, err
	}
	if err := s.attachTags(items); err != nil {
		return nil, err
	}
	return items, nil
}

func (s *Store) BulkUpdateFeedback(codes []string, update FeedbackUpdate) error {
	for _, code := range codes {
		if strings.TrimSpace(code) == "" {
			continue
		}
		current, err := s.GetFeedback(code)
		if err != nil {
			return err
		}
		if current == nil {
			continue
		}

		next := update
		if next.Status == "" {
			next.Status = current.Status
		}
		if next.Category == "" {
			next.Category = current.Category
		}
		if next.Priority == "" {
			next.Priority = current.Priority
		}
		if next.StatusDetail == "" {
			next.StatusDetail = current.StatusDetail
		}
		if next.HandledBy == "" {
			next.HandledBy = current.HandledBy
		}
		if next.Assignee == "" {
			next.Assignee = current.Assignee
		}
		if next.DueAt == "" {
			next.DueAt = current.DueAt
		}
		if next.SLALevel == "" {
			next.SLALevel = current.SLALevel
		}
		if next.Resolution == "" {
			next.Resolution = current.Resolution
		}
		if next.Note == "" {
			next.Note = current.Note
		}
		if next.PublicReply == "" {
			next.PublicReply = current.PublicReply
		}
		if next.Tags == nil {
			next.Tags = current.Tags
		}

		if err := s.UpdateFeedback(code, next); err != nil {
			return err
		}
	}
	return nil
}

func (s *Store) InsertComment(comment FeedbackComment) (FeedbackComment, error) {
	comment.Body = strings.TrimSpace(comment.Body)
	if comment.Body == "" {
		return comment, errors.New("comment body is empty")
	}
	if comment.CreatedAt == "" {
		comment.CreatedAt = Now()
	}
	id, err := s.insertID(
		`INSERT INTO feedback_comments (feedback_code, author, body, internal, created_at) VALUES (?, ?, ?, ?, ?)`,
		comment.FeedbackCode, defaultString(comment.Author, "admin"), comment.Body, boolInt(comment.Internal), comment.CreatedAt,
	)
	if err != nil {
		return comment, err
	}
	comment.ID = id
	_, _ = s.exec(`UPDATE feedbacks SET updated_at = ?, last_activity_at = ? WHERE code = ?`, comment.CreatedAt, comment.CreatedAt, comment.FeedbackCode)
	_ = s.InsertEvent(FeedbackEvent{FeedbackCode: comment.FeedbackCode, EventType: "comment", Actor: comment.Author, Message: "Comment added", CreatedAt: comment.CreatedAt})
	return comment, nil
}

func (s *Store) ListComments(code string) ([]FeedbackComment, error) {
	rows, err := s.query(`SELECT id, feedback_code, author, body, internal, created_at FROM feedback_comments WHERE feedback_code = ? ORDER BY created_at DESC, id DESC`, code)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	comments := []FeedbackComment{}
	for rows.Next() {
		var comment FeedbackComment
		var internal int
		if err := rows.Scan(&comment.ID, &comment.FeedbackCode, &comment.Author, &comment.Body, &internal, &comment.CreatedAt); err != nil {
			return nil, err
		}
		comment.Internal = internal == 1
		comments = append(comments, comment)
	}
	return comments, rows.Err()
}

func (s *Store) ReplaceTags(code string, tags []string) error {
	tx, err := s.begin()
	if err != nil {
		return err
	}
	defer tx.Rollback()
	if _, err := tx.Exec(`DELETE FROM feedback_tags WHERE feedback_code = ?`, code); err != nil {
		return err
	}
	now := Now()
	insertTag := tx.dialect.insertIgnore("feedback_tags", []string{"feedback_code", "tag", "created_at"}, []string{"feedback_code", "tag"})
	for _, tag := range normalizeTags(tags) {
		if _, err := tx.Exec(insertTag, code, tag, now); err != nil {
			return err
		}
	}
	return tx.Commit()
}

func (s *Store) ListTags(code string) ([]string, error) {
	rows, err := s.query(`SELECT tag FROM feedback_tags WHERE feedback_code = ? ORDER BY tag`, code)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	tags := []string{}
	for rows.Next() {
		var tag string
		if err := rows.Scan(&tag); err != nil {
			return nil, err
		}
		tags = append(tags, tag)
	}
	return tags, rows.Err()
}

func (s *Store) attachTags(items []FeedbackRecord) error {
	for index := range items {
		tags, err := s.ListTags(items[index].Code)
		if err != nil {
			return err
		}
		items[index].Tags = tags
	}
	return nil
}

func (s *Store) ListMails(page, perPage int, status string) (Page[MailRecord], error) {
	page, perPage = normalizePage(page, perPage)
	where := ""
	args := []any{}
	if status != "" {
		where = ` WHERE status = ?`
		args = append(args, status)
	}

	total, err := s.count(`mail_records`, where, args)
	if err != nil {
		return Page[MailRecord]{}, err
	}
	page, totalPages, offset := finishPage(total, page, perPage)

	sqlText := `SELECT id, feedback_code, kind, status, to_address, subject, plain_body, html_body,
		attachment_path, attachment_name, error_message, created_at, sent_at
		FROM mail_records` + where + ` ORDER BY created_at DESC, id DESC LIMIT ? OFFSET ?`
	args = append(args, perPage, offset)
	rows, err := s.query(sqlText, args...)
	if err != nil {
		return Page[MailRecord]{}, err
	}
	defer rows.Close()

	items := []MailRecord{}
	for rows.Next() {
		var item MailRecord
		if err := rows.Scan(&item.ID, &item.FeedbackCode, &item.Kind, &item.Status, &item.ToAddress, &item.Subject,
			&item.PlainBody, &item.HTMLBody, &item.AttachmentPath, &item.AttachmentName, &item.ErrorMessage,
			&item.CreatedAt, &item.SentAt); err != nil {
			return Page[MailRecord]{}, err
		}
		items = append(items, item)
	}
	return Page[MailRecord]{Items: items, Total: total, Page: page, PerPage: perPage, TotalPages: totalPages, Offset: offset}, rows.Err()
}

func (s *Store) InsertMail(record MailRecord) (int64, error) {
	id, err := s.insertID(
		`INSERT INTO mail_records (
			feedback_code, kind, status, to_address, subject, plain_body, html_body,
			attachment_path, attachment_name, error_message, created_at, sent_at
		) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, "", ?, "")`,
		record.FeedbackCode, record.Kind, record.Status, record.ToAddress, record.Subject, record.PlainBody,
		record.HTMLBody, record.AttachmentPath, record.AttachmentName, record.CreatedAt,
	)
	if err != nil {
		return 0, err
	}
	return id, nil
}

func (s *Store) UpdateMailState(id int64, status, errorMessage string) error {
	sentAt := ""
	if status == "sent" {
		sentAt = Now()
	}
	_, err := s.exec(`UPDATE mail_records SET status = ?, error_message = ?, sent_at = ? WHERE id = ?`, status, errorMessage, sentAt, id)
	return err
}

func (s *Store) Overview() (Overview, error) {
	feedbackTotal, err := s.count(`feedbacks`, "", nil)
	if err != nil {
		return Overview{}, err
	}
	mailTotal, err := s.count(`mail_records`, "", nil)
	if err != nil {
		return Overview{}, err
	}
	today, err := s.todayFeedbackCount()
	if err != nil {
		return Overview{}, err
	}
	mailFailed, err := s.mailFailedCount()
	if err != nil {
		return Overview{}, err
	}
	overdue, err := s.overdueCount()
	if err != nil {
		return Overview{}, err
	}

	statusCounts, err := s.groupCounts("feedbacks", "status")
	if err != nil {
		return Overview{}, err
	}
	categoryCounts, err := s.groupCounts("feedbacks", "category")
	if err != nil {
		return Overview{}, err
	}
	priorityCounts, err := s.groupCounts("feedbacks", "priority")
	if err != nil {
		return Overview{}, err
	}
	slaCounts, err := s.groupCounts("feedbacks", "sla_level")
	if err != nil {
		return Overview{}, err
	}
	recentEvents, err := s.ListEvents("", 8)
	if err != nil {
		return Overview{}, err
	}

	dbSize := fileSize(s.cfg.DatabasePath)
	storageSize := dirSize(s.cfg.StorageDir)
	return Overview{
		FeedbackTotal:  feedbackTotal,
		TodayFeedback:  today,
		MailFailed:     mailFailed,
		MailTotal:      mailTotal,
		Overdue:        overdue,
		StatusCounts:   statusCounts,
		CategoryCounts: categoryCounts,
		PriorityCounts: priorityCounts,
		SLACounts:      slaCounts,
		Storage: StorageInfo{
			Path:  s.cfg.StorageDir,
			Bytes: storageSize,
		},
		Database: DatabaseInfo{
			Path:    s.cfg.DatabasePath,
			Bytes:   dbSize,
			WALMode: s.WALMode(),
		},
		Mail: MailInfo{
			Configured:       s.cfg.Mail.Host != "" && s.cfg.Mail.FromAddress != "" && s.cfg.Mail.DeveloperAddress != "",
			Host:             s.cfg.Mail.Host,
			Port:             s.cfg.Mail.Port,
			Secure:           s.cfg.Mail.Secure,
			FromAddress:      s.cfg.Mail.FromAddress,
			DeveloperAddress: s.cfg.Mail.DeveloperAddress,
		},
		RecentEvents: recentEvents,
	}, nil
}

func (s *Store) FeedbackSummary() (FeedbackSummary, error) {
	total, err := s.count("feedbacks", "", nil)
	if err != nil {
		return FeedbackSummary{}, err
	}
	today, err := s.todayFeedbackCount()
	if err != nil {
		return FeedbackSummary{}, err
	}
	mailFailed, err := s.mailFailedCount()
	if err != nil {
		return FeedbackSummary{}, err
	}
	overdue, err := s.overdueCount()
	if err != nil {
		return FeedbackSummary{}, err
	}
	statusCounts, err := s.groupCounts("feedbacks", "status")
	if err != nil {
		return FeedbackSummary{}, err
	}
	categoryCounts, err := s.groupCounts("feedbacks", "category")
	if err != nil {
		return FeedbackSummary{}, err
	}
	priorityCounts, err := s.groupCounts("feedbacks", "priority")
	if err != nil {
		return FeedbackSummary{}, err
	}
	slaCounts, err := s.groupCounts("feedbacks", "sla_level")
	if err != nil {
		return FeedbackSummary{}, err
	}
	recentEvents, err := s.ListEvents("", 10)
	if err != nil {
		return FeedbackSummary{}, err
	}
	return FeedbackSummary{
		Total:          total,
		Today:          today,
		MailFailed:     mailFailed,
		Overdue:        overdue,
		StatusCounts:   statusCounts,
		CategoryCounts: categoryCounts,
		PriorityCounts: priorityCounts,
		SLACounts:      slaCounts,
		RecentEvents:   recentEvents,
	}, nil
}

func (s *Store) InsertEvent(event FeedbackEvent) error {
	if event.CreatedAt == "" {
		event.CreatedAt = Now()
	}
	_, err := s.exec(
		`INSERT INTO feedback_events (feedback_code, event_type, actor, from_value, to_value, message, created_at)
		 VALUES (?, ?, ?, ?, ?, ?, ?)`,
		event.FeedbackCode, event.EventType, event.Actor, event.FromValue, event.ToValue, sanitizeLog(event.Message), event.CreatedAt,
	)
	return err
}

func (s *Store) ListEvents(code string, limit int) ([]FeedbackEvent, error) {
	if limit <= 0 || limit > 200 {
		limit = 50
	}
	where := ""
	args := []any{}
	if code != "" {
		where = " WHERE feedback_code = ?"
		args = append(args, code)
	}
	args = append(args, limit)
	rows, err := s.query(`SELECT id, feedback_code, event_type, actor, from_value, to_value, message, created_at FROM feedback_events`+where+` ORDER BY created_at DESC, id DESC LIMIT ?`, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	events := []FeedbackEvent{}
	for rows.Next() {
		var event FeedbackEvent
		if err := rows.Scan(&event.ID, &event.FeedbackCode, &event.EventType, &event.Actor, &event.FromValue, &event.ToValue, &event.Message, &event.CreatedAt); err != nil {
			return nil, err
		}
		events = append(events, event)
	}
	return events, rows.Err()
}

func (s *Store) InsertAudit(log AuditLog) error {
	if log.CreatedAt == "" {
		log.CreatedAt = Now()
	}
	_, err := s.exec(
		`INSERT INTO audit_logs (actor, type, target, message, ip, user_agent, created_at) VALUES (?, ?, ?, ?, ?, ?, ?)`,
		sanitizeLog(log.Actor), sanitizeLog(log.Type), sanitizeLog(log.Target), sanitizeLog(log.Message), sanitizeLog(log.IP), sanitizeLog(log.UserAgent), log.CreatedAt,
	)
	return err
}

func (s *Store) ListAuditLogs(page, perPage int, actor, typ string) (Page[AuditLog], error) {
	page, perPage = normalizePage(page, perPage)
	clauses := []string{}
	args := []any{}
	if actor = strings.TrimSpace(actor); actor != "" {
		clauses = append(clauses, "actor LIKE ?")
		args = append(args, "%"+actor+"%")
	}
	if typ = strings.TrimSpace(typ); typ != "" {
		clauses = append(clauses, "type = ?")
		args = append(args, typ)
	}
	where := ""
	if len(clauses) > 0 {
		where = " WHERE " + strings.Join(clauses, " AND ")
	}
	total, err := s.count(`audit_logs`, where, args)
	if err != nil {
		return Page[AuditLog]{}, err
	}
	page, totalPages, offset := finishPage(total, page, perPage)
	args = append(args, perPage, offset)
	rows, err := s.query(`SELECT id, actor, type, target, message, ip, user_agent, created_at FROM audit_logs`+where+` ORDER BY created_at DESC, id DESC LIMIT ? OFFSET ?`, args...)
	if err != nil {
		return Page[AuditLog]{}, err
	}
	defer rows.Close()
	items := []AuditLog{}
	for rows.Next() {
		var item AuditLog
		if err := rows.Scan(&item.ID, &item.Actor, &item.Type, &item.Target, &item.Message, &item.IP, &item.UserAgent, &item.CreatedAt); err != nil {
			return Page[AuditLog]{}, err
		}
		items = append(items, item)
	}
	return Page[AuditLog]{Items: items, Total: total, Page: page, PerPage: perPage, TotalPages: totalPages, Offset: offset}, rows.Err()
}

func (s *Store) InsertWebhookDelivery(delivery WebhookDelivery) (int64, error) {
	if delivery.CreatedAt == "" {
		delivery.CreatedAt = Now()
	}
	id, err := s.insertID(
		`INSERT INTO webhook_deliveries (webhook_name, event, status, attempts, response_code, error_message, payload_sha256, created_at, finished_at)
		 VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,
		delivery.WebhookName, delivery.Event, delivery.Status, delivery.Attempts, delivery.ResponseCode, sanitizeLog(delivery.ErrorMessage), delivery.PayloadSHA256, delivery.CreatedAt, delivery.FinishedAt,
	)
	if err != nil {
		return 0, err
	}
	return id, nil
}

func (s *Store) FinishWebhookDelivery(id int64, status string, attempts, responseCode int, errorMessage string) error {
	_, err := s.exec(`UPDATE webhook_deliveries SET status = ?, attempts = ?, response_code = ?, error_message = ?, finished_at = ? WHERE id = ?`,
		status, attempts, responseCode, sanitizeLog(errorMessage), Now(), id)
	return err
}

func (s *Store) ListWebhookDeliveries(page, perPage int, status string) (Page[WebhookDelivery], error) {
	page, perPage = normalizePage(page, perPage)
	where := ""
	args := []any{}
	if status = strings.TrimSpace(status); status != "" {
		where = " WHERE status = ?"
		args = append(args, status)
	}
	total, err := s.count(`webhook_deliveries`, where, args)
	if err != nil {
		return Page[WebhookDelivery]{}, err
	}
	page, totalPages, offset := finishPage(total, page, perPage)
	args = append(args, perPage, offset)
	rows, err := s.query(`SELECT id, webhook_name, event, status, attempts, response_code, error_message, payload_sha256, created_at, finished_at FROM webhook_deliveries`+where+` ORDER BY created_at DESC, id DESC LIMIT ? OFFSET ?`, args...)
	if err != nil {
		return Page[WebhookDelivery]{}, err
	}
	defer rows.Close()
	items := []WebhookDelivery{}
	for rows.Next() {
		var item WebhookDelivery
		if err := rows.Scan(&item.ID, &item.WebhookName, &item.Event, &item.Status, &item.Attempts, &item.ResponseCode, &item.ErrorMessage, &item.PayloadSHA256, &item.CreatedAt, &item.FinishedAt); err != nil {
			return Page[WebhookDelivery]{}, err
		}
		items = append(items, item)
	}
	return Page[WebhookDelivery]{Items: items, Total: total, Page: page, PerPage: perPage, TotalPages: totalPages, Offset: offset}, rows.Err()
}

func (s *Store) BackupDatabase(path string) error {
	if err := os.MkdirAll(filepath.Dir(path), 0o750); err != nil {
		return err
	}
	_ = os.Remove(path)
	if _, err := s.localDB.Exec(`VACUUM INTO ?`, path); err != nil {
		return err
	}
	return nil
}

func (s *Store) WALMode() string {
	var mode string
	if s.localDB != nil {
		_ = s.localDB.QueryRow(`PRAGMA journal_mode`).Scan(&mode)
	}
	return mode
}

func (s *Store) count(table, where string, args []any) (int, error) {
	switch table {
	case "feedbacks", "mail_records", "audit_logs", "webhook_deliveries":
	default:
		return 0, fmt.Errorf("unsupported table %q", table)
	}
	var total int
	if err := s.scanRow(`SELECT COUNT(*) FROM `+table+where, args, &total); err != nil {
		return 0, err
	}
	return total, nil
}

func feedbackFilters(filters FeedbackFilters) (string, []any) {
	clauses := []string{}
	args := []any{}
	if filters.Status != "" {
		clauses = append(clauses, "status = ?")
		args = append(args, filters.Status)
	}
	if filters.Category != "" {
		clauses = append(clauses, "category = ?")
		args = append(args, filters.Category)
	}
	if filters.Priority != "" {
		clauses = append(clauses, "priority = ?")
		args = append(args, filters.Priority)
	}
	if filters.Assignee != "" {
		clauses = append(clauses, "assignee = ?")
		args = append(args, filters.Assignee)
	}
	if filters.SLA != "" {
		clauses = append(clauses, "sla_level = ?")
		args = append(args, filters.SLA)
	}
	if filters.Tag != "" {
		clauses = append(clauses, "EXISTS (SELECT 1 FROM feedback_tags ft WHERE ft.feedback_code = feedbacks.code AND ft.tag = ?)")
		args = append(args, strings.ToLower(strings.TrimSpace(filters.Tag)))
	}
	if filters.Overdue == "true" || filters.Overdue == "1" {
		clauses = append(clauses, `due_at != '' AND due_at < ? AND status NOT IN ('resolved', 'archived')`)
		args = append(args, Now())
	}
	if filters.Mail != "" {
		switch filters.Mail {
		case "sent":
			clauses = append(clauses, "mail_sent = 1")
		case "pending":
			clauses = append(clauses, "mail_sent = 0")
		}
	}
	if filters.Query = strings.TrimSpace(filters.Query); filters.Query != "" {
		like := "%" + filters.Query + "%"
		clauses = append(clauses, "(code LIKE ? OR title LIKE ? OR contact LIKE ? OR body LIKE ? OR status_detail LIKE ? OR handled_by LIKE ? OR assignee LIKE ? OR resolution LIKE ?)")
		args = append(args, like, like, like, like, like, like, like, like)
	}
	if len(clauses) == 0 {
		return "", args
	}
	return " WHERE " + strings.Join(clauses, " AND "), args
}

func feedbackOrder(sort string) string {
	switch sort {
	case "oldest":
		return ` ORDER BY received_at ASC`
	case "due":
		return ` ORDER BY CASE WHEN due_at = '' THEN 1 ELSE 0 END, due_at ASC, last_activity_at DESC`
	case "priority":
		return ` ORDER BY CASE priority WHEN 'blocking' THEN 0 WHEN 'major' THEN 1 ELSE 2 END, last_activity_at DESC`
	default:
		return ` ORDER BY last_activity_at DESC, received_at DESC`
	}
}

func feedbackSelectSQL() string {
	return `SELECT code, received_at, title, type, severity, category, priority, contact, body, status, status_detail, note, public_reply, handled_by,
		assignee, due_at, resolved_at, archived_at, sla_level, source_channel, risk_score, resolution,
		package_path, encrypted_package_path, package_sha256, plain_package_sha256, remote_addr, summary_text,
		included_files, mail_sent, updated_at, last_activity_at FROM feedbacks`
}

type feedbackScanner interface {
	Scan(dest ...any) error
}

func scanFeedback(scanner feedbackScanner) (FeedbackRecord, error) {
	var item FeedbackRecord
	var mailSent int
	err := scanner.Scan(
		&item.Code, &item.ReceivedAt, &item.Title, &item.Type, &item.Severity, &item.Category, &item.Priority, &item.Contact, &item.Body,
		&item.Status, &item.StatusDetail, &item.Note, &item.PublicReply, &item.HandledBy, &item.Assignee, &item.DueAt, &item.ResolvedAt,
		&item.ArchivedAt, &item.SLALevel, &item.SourceChannel, &item.RiskScore, &item.Resolution, &item.PackagePath, &item.EncryptedPackagePath,
		&item.PackageSha256, &item.PlainPackageSha256, &item.RemoteAddr, &item.SummaryText, &item.IncludedFiles,
		&mailSent, &item.UpdatedAt, &item.LastActivityAt,
	)
	item.MailSent = mailSent == 1
	return item, err
}

func (s *Store) backfillWorkflowColumns() error {
	conn, activeDialect := s.active()
	return backfillWorkflowColumnsDatabase(conn, activeDialect)
}

func backfillWorkflowColumnsDatabase(conn *sql.DB, activeDialect dialect) error {
	_, err := conn.Exec(activeDialect.rebind(`UPDATE feedbacks
		SET category = CASE
				WHEN category = '' AND lower(type) IN ('suggestion', 'ui', 'other') THEN lower(type)
				WHEN category = '' THEN 'issue'
				ELSE category END,
		    priority = CASE
				WHEN priority = '' AND lower(severity) IN ('major', 'blocking') THEN lower(severity)
				WHEN priority = '' THEN 'normal'
				ELSE priority END,
		    last_activity_at = CASE WHEN last_activity_at = '' THEN updated_at ELSE last_activity_at END,
		    sla_level = CASE
				WHEN sla_level != '' THEN sla_level
				WHEN lower(severity) = 'blocking' THEN 'urgent'
				WHEN lower(severity) = 'major' THEN 'elevated'
				ELSE 'standard' END,
		    source_channel = CASE WHEN source_channel = '' THEN 'winui' ELSE source_channel END,
		    risk_score = CASE
				WHEN risk_score > 0 THEN risk_score
				WHEN lower(severity) = 'blocking' THEN 90
				WHEN lower(severity) = 'major' THEN 65
				ELSE 30 END`))
	return err
}

func (s *Store) groupCounts(table, column string) (map[string]int, error) {
	if table != "feedbacks" && table != "mail_records" {
		return nil, fmt.Errorf("unsupported table %q", table)
	}
	if !validIdentifier(column) {
		return nil, fmt.Errorf("invalid column %q", column)
	}
	rows, err := s.query(`SELECT ` + column + `, COUNT(*) FROM ` + table + ` GROUP BY ` + column)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	counts := map[string]int{}
	for rows.Next() {
		var key string
		var count int
		if err := rows.Scan(&key, &count); err != nil {
			return nil, err
		}
		if key == "" {
			key = "unknown"
		}
		counts[key] = count
	}
	return counts, rows.Err()
}

func (s *Store) todayFeedbackCount() (int, error) {
	prefix := time.Now().UTC().Format("2006-01-02")
	return s.count("feedbacks", " WHERE received_at LIKE ?", []any{prefix + "%"})
}

func (s *Store) mailFailedCount() (int, error) {
	return s.count("mail_records", " WHERE status = ?", []any{"failed"})
}

func (s *Store) overdueCount() (int, error) {
	return s.count("feedbacks", ` WHERE due_at != '' AND due_at < ? AND status NOT IN ('resolved', 'archived')`, []any{Now()})
}

func normalizeCategory(value string) string {
	switch strings.ToLower(strings.TrimSpace(value)) {
	case "suggestion", "ui", "other":
		return strings.ToLower(strings.TrimSpace(value))
	default:
		return "issue"
	}
}

func normalizePriority(value string) string {
	switch strings.ToLower(strings.TrimSpace(value)) {
	case "major", "blocking":
		return strings.ToLower(strings.TrimSpace(value))
	default:
		return "normal"
	}
}

func normalizeSLA(value string) string {
	switch strings.ToLower(strings.TrimSpace(value)) {
	case "elevated", "urgent":
		return strings.ToLower(strings.TrimSpace(value))
	default:
		return "standard"
	}
}

func defaultSLA(priority string) string {
	switch normalizePriority(priority) {
	case "blocking":
		return "urgent"
	case "major":
		return "elevated"
	default:
		return "standard"
	}
}

func defaultRisk(value int, priority string) int {
	if value > 0 {
		return value
	}
	switch normalizePriority(priority) {
	case "blocking":
		return 90
	case "major":
		return 65
	default:
		return 30
	}
}

func normalizeTags(tags []string) []string {
	seen := map[string]bool{}
	out := []string{}
	for _, tag := range tags {
		tag = strings.ToLower(strings.TrimSpace(tag))
		tag = strings.Trim(tag, ",;#")
		if tag == "" || seen[tag] {
			continue
		}
		runes := []rune(tag)
		if len(runes) > 32 {
			tag = string(runes[:32])
		}
		seen[tag] = true
		out = append(out, tag)
	}
	return out
}

func normalizePage(page, perPage int) (int, int) {
	if page < 1 {
		page = 1
	}
	switch perPage {
	case 20, 50, 100:
	default:
		perPage = 20
	}
	return page, perPage
}

func finishPage(total, page, perPage int) (int, int, int) {
	totalPages := total / perPage
	if total%perPage != 0 {
		totalPages++
	}
	if totalPages < 1 {
		totalPages = 1
	}
	if page > totalPages {
		page = totalPages
	}
	return page, totalPages, (page - 1) * perPage
}

func Now() string {
	return time.Now().UTC().Format(time.RFC3339)
}

func boolInt(value bool) int {
	if value {
		return 1
	}
	return 0
}

func defaultString(value, fallback string) string {
	if strings.TrimSpace(value) == "" {
		return fallback
	}
	return value
}

func sanitizeLog(value string) string {
	value = strings.TrimSpace(strings.ReplaceAll(value, "\x00", ""))
	value = strings.Map(func(r rune) rune {
		if r == '\n' || r == '\r' || r == '\t' {
			return ' '
		}
		if r < 32 {
			return -1
		}
		return r
	}, value)
	runes := []rune(value)
	if len(runes) > 1000 {
		return string(runes[:1000])
	}
	return value
}

func fileSize(path string) int64 {
	info, err := os.Stat(path)
	if err != nil || info.IsDir() {
		return 0
	}
	return info.Size()
}

func dirSize(path string) int64 {
	var total int64
	_ = filepath.WalkDir(path, func(_ string, entry os.DirEntry, err error) error {
		if err != nil || entry.IsDir() {
			return nil
		}
		if info, err := entry.Info(); err == nil {
			total += info.Size()
		}
		return nil
	})
	return total
}

func ToJSON(value any) string {
	data, err := json.Marshal(value)
	if err != nil {
		return "{}"
	}
	return string(data)
}
