// Package automation — audit_store.go
//
// DBauditStore is a minimal *sql.DB-backed implementation of AuditStore.
// It targets _System_FlowAuditLog using raw SQL to avoid import cycles with
// the persistence layer. Used exclusively by production wiring in
// service_manager_init.go. Tests continue to use inMemoryStore.
package automation

import (
	"context"
	"database/sql"
	"fmt"
	"log"
	"strings"
	"time"
)

const auditLogTable = "_System_FlowAuditLog"

// DBauditStore implements AuditStore against a *sql.DB.
type DBauditStore struct {
	db *sql.DB
}

// NewDBAuditStore wraps db as an AuditStore for _System_FlowAuditLog.
func NewDBAuditStore(db *sql.DB) AuditStore {
	return &DBauditStore{db: db}
}

// InsertRows inserts a batch of audit rows in a single multi-row INSERT.
func (s *DBauditStore) InsertRows(ctx context.Context, rows []Row) error {
	if len(rows) == 0 {
		return nil
	}
	const cols = "(Id, FlowExecutionId, FlowDefinitionId, FlowVersionId, ElementId, ElementType, Status, DurationMs, InputJSON, OutputJSON, ErrorPayload, ObjectMetadataId, RecordId, CreatedDate)"
	placeholders := make([]string, 0, len(rows))
	args := make([]interface{}, 0, len(rows)*14)
	for _, r := range rows {
		placeholders = append(placeholders, "(?,?,?,?,?,?,?,?,?,?,?,?,?,?)")
		args = append(args,
			r.Id, r.FlowExecutionId, r.FlowDefinitionId, r.FlowVersionId,
			r.ElementId, r.ElementType, r.Status, r.DurationMs,
			r.InputJSON, r.OutputJSON, r.ErrorPayload, r.ObjectMetadataId,
			r.RecordId, r.CreatedDate,
		)
	}
	q := fmt.Sprintf("INSERT INTO `%s` %s VALUES %s",
		auditLogTable, cols, strings.Join(placeholders, ","))
	if _, err := s.db.ExecContext(ctx, q, args...); err != nil {
		// Audit-log writes must NOT fail the calling flow. Log the row
		// count for operator correlation and swallow the error. Production
		// rows can fail due to: (a) TEXT 64KB ceiling on a single column —
		// already mitigated by capPayload on InputJSON/OutputJSON/ErrorPayload
		// before enqueue; (b) duplicate primary key — Id is a UUIDv4 string,
		// so collision is astronomically unlikely; (c) connection drop —
		// transient, retried on next batch.
		//
		// Pre-cutover (round-2) used INSERT IGNORE which silently dropped
		// errors in case (a)/(b); plain INSERT + log surfaces the cause
		// without breaking flow execution. Codex Lane C round-3 HIGH-2.
		log.Printf("audit_store: batch insert failed for %d rows: %v", len(rows), err)
		return err
	}
	return nil
}

// QueryRows returns rows matching filter. Supports FlowExecutionId, ElementId, Status, Limit.
func (s *DBauditStore) QueryRows(ctx context.Context, filter QueryFilter) ([]Row, error) {
	where := []string{}
	args := []interface{}{}
	if filter.FlowExecutionId != "" {
		where = append(where, "FlowExecutionId = ?")
		args = append(args, filter.FlowExecutionId)
	}
	if filter.ElementId != "" {
		where = append(where, "ElementId = ?")
		args = append(args, filter.ElementId)
	}
	if filter.Status != "" {
		where = append(where, "Status = ?")
		args = append(args, filter.Status)
	}
	q := fmt.Sprintf("SELECT Id, FlowExecutionId, FlowDefinitionId, FlowVersionId, ElementId, ElementType, Status, DurationMs, InputJSON, OutputJSON, ErrorPayload, ObjectMetadataId, RecordId, CreatedDate FROM `%s`", auditLogTable)
	if len(where) > 0 {
		q += " WHERE " + strings.Join(where, " AND ")
	}
	q += " ORDER BY CreatedDate DESC"
	if filter.Limit > 0 {
		q += fmt.Sprintf(" LIMIT %d", filter.Limit)
	}
	sqlRows, err := s.db.QueryContext(ctx, q, args...)
	if err != nil {
		return nil, err
	}
	defer sqlRows.Close()
	var out []Row
	for sqlRows.Next() {
		var r Row
		var createdDate time.Time
		if err := sqlRows.Scan(
			&r.Id, &r.FlowExecutionId, &r.FlowDefinitionId, &r.FlowVersionId,
			&r.ElementId, &r.ElementType, &r.Status, &r.DurationMs,
			&r.InputJSON, &r.OutputJSON, &r.ErrorPayload, &r.ObjectMetadataId,
			&r.RecordId, &createdDate,
		); err != nil {
			return nil, err
		}
		r.CreatedDate = createdDate
		out = append(out, r)
	}
	return out, sqlRows.Err()
}

// DeleteBefore removes rows whose CreatedDate is before the given time.
func (s *DBauditStore) DeleteBefore(ctx context.Context, before time.Time) (int64, error) {
	res, err := s.db.ExecContext(ctx,
		fmt.Sprintf("DELETE FROM `%s` WHERE CreatedDate < ?", auditLogTable), before)
	if err != nil {
		return 0, err
	}
	return res.RowsAffected()
}

// CountRows returns the total number of rows in the table.
func (s *DBauditStore) CountRows(ctx context.Context) (int64, error) {
	var n int64
	err := s.db.QueryRowContext(ctx,
		fmt.Sprintf("SELECT COUNT(*) FROM `%s`", auditLogTable)).Scan(&n)
	return n, err
}

// DeleteOldest removes the n oldest rows by CreatedDate.
//
// Deletion is keyed on the primary key Id, not on CreatedDate. CreatedDate is
// second-precision DATETIME, so high-volume audit traffic produces many rows
// sharing the same timestamp; matching rows by CreatedDate would delete every
// row in the oldest second — far more than the requested surplus. Selecting
// Id from an ordered (CreatedDate ASC, Id ASC) subquery and deleting by Id
// guarantees exactly n rows are removed.
func (s *DBauditStore) DeleteOldest(ctx context.Context, n int64) (int64, error) {
	if n <= 0 {
		return 0, nil
	}
	q := fmt.Sprintf(
		"DELETE FROM `%s` WHERE Id IN (SELECT Id FROM (SELECT Id FROM `%s` ORDER BY CreatedDate ASC, Id ASC LIMIT ?) AS t)",
		auditLogTable, auditLogTable)
	res, err := s.db.ExecContext(ctx, q, n)
	if err != nil {
		return 0, err
	}
	return res.RowsAffected()
}