package persistence

import (
	"context"
	"fmt"

	"github.com/nexuscrm/kernel/pkg/schema"
)

const (
	fileUploadOperationTable       = "_System_FileUploadOperation"
	fileUploadLifecycleIndex       = "idx__System_FileUploadOperation_lifecycle"
	fileUploadLifecycleIndexAddSQL = `ALTER TABLE _System_FileUploadOperation
ADD INDEX idx__System_FileUploadOperation_lifecycle (State, ExpiresAt, Id)`
)

var fileUploadLifecycleIndexColumns = []string{"State", "ExpiresAt", "Id"}

func declaresFileUploadLifecycleIndex(def schema.TableDefinition) bool {
	if def.TableName != fileUploadOperationTable {
		return false
	}
	for _, index := range def.Indices {
		if index.Name == fileUploadLifecycleIndex {
			return !index.Unique && equalExactColumnNames(index.Columns, fileUploadLifecycleIndexColumns)
		}
	}
	return false
}

// reconcileFileUploadLifecycleIndex is the warm-schema half of the
// file-upload observation cutover. It is additive: old readers and writers do
// not depend on the index, while later bounded due-work reads can require the
// canonical shape after it has landed.
func (r *SchemaRepository) reconcileFileUploadLifecycleIndex(ctx context.Context) error {
	columns, unique, hasPrefix, err := inspectManagedPhysicalIndex(
		ctx,
		r.db,
		fileUploadOperationTable,
		fileUploadLifecycleIndex,
	)
	if err != nil {
		return fmt.Errorf("reconcile file upload lifecycle index: %w", err)
	}
	if len(columns) != 0 {
		if !unique && !hasPrefix && equalExactColumnNames(columns, fileUploadLifecycleIndexColumns) {
			return nil
		}
		return fmt.Errorf(
			"reconcile file upload lifecycle index: physical %s.%s has incompatible shape (unique=%t, prefix=%t, columns=%v)",
			fileUploadOperationTable,
			fileUploadLifecycleIndex,
			unique,
			hasPrefix,
			columns,
		)
	}

	if _, err := r.db.ExecContext(ctx, fileUploadLifecycleIndexAddSQL); err != nil {
		return fmt.Errorf("reconcile file upload lifecycle index: add state-leading due-work index: %w", err)
	}
	if err := VerifyExactTableIndex(
		ctx,
		r.db,
		fileUploadOperationTable,
		fileUploadLifecycleIndex,
		false,
		fileUploadLifecycleIndexColumns,
	); err != nil {
		return fmt.Errorf("reconcile file upload lifecycle index: verify migrated index: %w", err)
	}
	return nil
}