package audit

import (
	"context"
	"database/sql"
	"fmt"
	"time"

	"github.com/nexuscrm/kernel/pkg/schemacatalog"
	"github.com/rs/zerolog/log"
)

type auditLogRetentionPolicy struct {
	maxRows      int64
	maxAge       time.Duration
	maxAgeMonths int
}

func loadAuditLogRetentionPolicy() (auditLogRetentionPolicy, error) {
	definitions, err := schemacatalog.SystemTableDefinitions()
	if err != nil {
		return auditLogRetentionPolicy{}, fmt.Errorf("audit retention catalog: %w", err)
	}
	for _, definition := range definitions {
		if definition.TableName != auditLogTable {
			continue
		}
		policy := auditLogRetentionPolicy{
			maxRows:      int64(definition.RetentionMaxRows),
			maxAge:       time.Duration(definition.RetentionMaxAgeSeconds) * time.Second,
			maxAgeMonths: definition.RetentionMaxAgeMonths,
		}
		if policy.maxRows < 0 || policy.maxAge < 0 || policy.maxAgeMonths < 0 {
			return auditLogRetentionPolicy{}, fmt.Errorf("audit retention: %s limits cannot be negative", auditLogTable)
		}
		if policy.maxAge > 0 && policy.maxAgeMonths > 0 {
			return auditLogRetentionPolicy{}, fmt.Errorf("audit retention: %s cannot declare both second and calendar-month age limits", auditLogTable)
		}
		if policy.maxRows == 0 && policy.maxAge == 0 && policy.maxAgeMonths == 0 {
			return auditLogRetentionPolicy{}, fmt.Errorf("audit retention: %s requires at least one positive limit", auditLogTable)
		}
		return policy, nil
	}
	return auditLogRetentionPolicy{}, fmt.Errorf("audit retention: table %s not found", auditLogTable)
}

const (
	auditRetentionDeleteBatchSize = int64(10_000)
	auditRetentionLockName        = "nexuscrm_audit_log_retention"
	acquireAuditRetentionLockSQL  = `SELECT GET_LOCK(?, 0)`
	releaseAuditRetentionLockSQL  = `SELECT RELEASE_LOCK(?)`
	deleteAuditRowsBeforeSQL      = `DELETE FROM _System_AuditLog
WHERE Id IN (
    SELECT Id FROM (
        SELECT Id FROM _System_AuditLog FORCE INDEX (idx_audit_retention_changed_at) WHERE ChangedAt < ? ORDER BY ChangedAt ASC, Id ASC LIMIT ?
    ) AS expired_audit_rows
)`
	countAuditRowsSQL        = `SELECT COUNT(*) FROM _System_AuditLog`
	deleteOldestAuditRowsSQL = `DELETE FROM _System_AuditLog
WHERE Id IN (
    SELECT Id FROM (
        SELECT Id FROM _System_AuditLog FORCE INDEX (idx_audit_retention_changed_at) ORDER BY ChangedAt ASC, Id ASC LIMIT ?
    ) AS oldest_audit_rows
)`
)

type auditRetentionDB interface {
	ExecContext(context.Context, string, ...interface{}) (sql.Result, error)
	QueryRowContext(context.Context, string, ...interface{}) *sql.Row
}

func trimAuditLog(ctx context.Context, db *sql.DB, policy auditLogRetentionPolicy, now time.Time) (int64, error) {
	if db == nil {
		return 0, fmt.Errorf("audit retention: nil database")
	}
	return trimAuditLogOn(ctx, db, policy, now)
}

func trimAuditLogOn(ctx context.Context, db auditRetentionDB, policy auditLogRetentionPolicy, now time.Time) (int64, error) {
	var total int64
	if cutoff, ok := policy.ageCutoff(now); ok {
		for {
			result, err := db.ExecContext(ctx, deleteAuditRowsBeforeSQL, cutoff, auditRetentionDeleteBatchSize)
			if err != nil {
				return total, fmt.Errorf("audit retention delete by age: %w", err)
			}
			deleted, err := result.RowsAffected()
			if err != nil {
				return total, fmt.Errorf("audit retention age rows affected: %w", err)
			}
			total += deleted
			if deleted < auditRetentionDeleteBatchSize {
				break
			}
		}
	}

	if policy.maxRows == 0 {
		return total, nil
	}
	var count int64
	if err := db.QueryRowContext(ctx, countAuditRowsSQL).Scan(&count); err != nil {
		return total, fmt.Errorf("audit retention count rows: %w", err)
	}
	for surplus := count - policy.maxRows; surplus > 0; surplus = count - policy.maxRows {
		batch := min(surplus, auditRetentionDeleteBatchSize)
		result, err := db.ExecContext(ctx, deleteOldestAuditRowsSQL, batch)
		if err != nil {
			return total, fmt.Errorf("audit retention enforce row cap: %w", err)
		}
		deleted, err := result.RowsAffected()
		if err != nil {
			return total, fmt.Errorf("audit retention cap rows affected: %w", err)
		}
		total += deleted
		if deleted == 0 {
			return total, fmt.Errorf("audit retention row cap made no progress with %d surplus rows", surplus)
		}
		count -= deleted
	}
	return total, nil
}

func (policy auditLogRetentionPolicy) ageCutoff(now time.Time) (time.Time, bool) {
	if policy.maxAgeMonths > 0 {
		nowUTC := now.UTC()
		currentMonthUTC := time.Date(nowUTC.Year(), nowUTC.Month(), 1, 0, 0, 0, 0, time.UTC)
		return currentMonthUTC.AddDate(0, -policy.maxAgeMonths, 0), true
	}
	if policy.maxAge > 0 {
		return now.Add(-policy.maxAge), true
	}
	return time.Time{}, false
}

func trimAuditLogWithLease(ctx context.Context, db *sql.DB, policy auditLogRetentionPolicy, now time.Time) (int64, bool, error) {
	if db == nil {
		return 0, false, fmt.Errorf("audit retention: nil database")
	}
	conn, err := db.Conn(ctx)
	if err != nil {
		return 0, false, fmt.Errorf("audit retention lock connection: %w", err)
	}
	defer conn.Close()

	var acquired int
	if err := conn.QueryRowContext(ctx, acquireAuditRetentionLockSQL, auditRetentionLockName).Scan(&acquired); err != nil {
		return 0, false, fmt.Errorf("audit retention acquire lock: %w", err)
	}
	if acquired != 1 {
		return 0, false, nil
	}
	defer func() {
		releaseCtx, cancel := context.WithTimeout(context.Background(), time.Second)
		defer cancel()
		var released sql.NullInt64
		_ = conn.QueryRowContext(releaseCtx, releaseAuditRetentionLockSQL, auditRetentionLockName).Scan(&released)
	}()

	deleted, err := trimAuditLogOn(ctx, conn, policy, now)
	return deleted, true, err
}

// RunAuditLogRetention applies the catalog-owned retention policy
// immediately and then periodically until ctx is cancelled. It intentionally
// retains complete events rather than sampling high-churn fields: sampling
// would create permanent, indistinguishable gaps in the audit trail.
func RunAuditLogRetention(ctx context.Context, db *sql.DB, interval time.Duration) {
	policy, err := loadAuditLogRetentionPolicy()
	if err != nil {
		log.Error().Err(err).Msg("Audit V2 retention is unavailable")
		return
	}
	if interval <= 0 {
		interval = time.Hour
	}
	run := func() {
		deleted, acquired, err := trimAuditLogWithLease(ctx, db, policy, time.Now())
		if err != nil {
			if ctx.Err() == nil {
				log.Warn().Err(err).Msg("Audit V2 retention pass failed")
			}
			return
		}
		if !acquired {
			return
		}
		if deleted > 0 {
			log.Info().Int64("deleted_rows", deleted).Msg("Audit V2 retention pass completed")
		}
	}
	run()
	ticker := time.NewTicker(interval)
	defer ticker.Stop()
	for {
		select {
		case <-ctx.Done():
			return
		case <-ticker.C:
			run()
		}
	}
}