package persistence

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

	database "github.com/nexuscrm/db"
)

const (
	FileUploadOperationPrepared   = "PREPARED"
	FileUploadOperationBlobStored = "BLOB_STORED"
	FileUploadOperationCompleted  = "COMPLETED"
	FileUploadOperationFailed     = "FAILED"
)

var (
	ErrFileUploadOperationConflict      = errors.New("file upload idempotency identity is bound to different content")
	ErrFileUploadOperationLostOwnership = errors.New("file upload operation lost state ownership")
	ErrFileUploadOperationNotFound      = errors.New("file upload operation was not found")
)

var fileUploadOperationRequiredColumns = map[string]string{
	"Id":                     "varchar",
	"IdempotencyKey":         "varchar",
	"RequestHash":            "varchar",
	"State":                  "varchar",
	"OwnerToken":             "varchar",
	"ObjectKey":              "varchar",
	"StorageProvider":        "varchar(64)",
	"StorageBucket":          "varchar(255)",
	"StorageAuthorityDigest": "varchar(255)",
	"SystemFileId":           "varchar",
	"ParentId":               "varchar",
	"ParentObjectApiName":    "varchar",
	"ErrorCode":              "varchar",
	"ErrorMessage":           "text",
	"RetryCount":             "int",
	"RepairId":               "varchar(255)",
	"RepairReason":           "varchar(1024)",
	"RepairCount":            "int",
	"LastRepairAt":           "datetime(6)",
	"ExpiresAt":              "datetime",
	"CreatedDate":            "datetime",
	"LastModifiedDate":       "datetime",
}

var recordAttachmentReadRequiredColumns = map[string]string{
	"Id":                  "varchar",
	"ParentId":            "varchar",
	"ParentObjectApiName": "varchar",
	"Name":                "varchar",
	"OriginalFileName":    "varchar",
	"MimeType":            "varchar",
	"SizeBytes":           "bigint",
	"StoragePath":         "text",
	"StorageKey":          "varchar",
	"StorageProvider":     "varchar(32)",
	"StorageBucket":       "varchar(255)",
	"Status":              "varchar(32)",
	"SourceType":          "varchar(32)",
	"Checksum":            "varchar",
}

var fileUploadSystemFileRequiredColumns = requiredColumnsWith(
	recordAttachmentReadRequiredColumns,
	map[string]string{"AttachmentKind": "varchar"},
)

func requiredColumnsWith(base, additions map[string]string) map[string]string {
	combined := make(map[string]string, len(base)+len(additions))
	for column, columnType := range base {
		combined[column] = columnType
	}
	for column, columnType := range additions {
		combined[column] = columnType
	}
	return combined
}

type FileUploadOperationRepository struct {
	db           *sql.DB
	transactions *TransactionManager
}

func NewFileUploadOperationRepository(db *sql.DB) *FileUploadOperationRepository {
	var transactions *TransactionManager
	if db != nil {
		transactions = NewTransactionManager(database.NewConnectionFromDB(db))
	}
	return &FileUploadOperationRepository{db: db, transactions: transactions}
}

func VerifyFileUploadOperationSchema(ctx context.Context, db *sql.DB) error {
	if ctx == nil || db == nil {
		return errors.New("file upload operation schema: context and database are required")
	}
	if err := VerifyRequiredTableColumns(
		ctx, db, "_System_FileUploadOperation", fileUploadOperationRequiredColumns,
	); err != nil {
		return fmt.Errorf("file upload operation schema: %w", err)
	}
	if err := VerifyExactTableIndex(
		ctx,
		db,
		"_System_FileUploadOperation",
		"uk__System_FileUploadOperation_idempotency",
		true,
		[]string{"IdempotencyKey"},
	); err != nil {
		return fmt.Errorf("file upload operation schema: %w", err)
	}
	if err := VerifyExactTableIndex(
		ctx,
		db,
		"_System_FileUploadOperation",
		"idx__System_FileUploadOperation_expires",
		false,
		[]string{"ExpiresAt"},
	); err != nil {
		return fmt.Errorf("file upload operation schema: %w", err)
	}
	if err := VerifyRequiredTableColumns(
		ctx, db, "_System_File", fileUploadSystemFileRequiredColumns,
	); err != nil {
		return fmt.Errorf("file upload operation schema: %w", err)
	}
	return nil
}

// VerifyRecordAttachmentReadSchema owns the physical _System_File facts needed
// to authorize and open a frozen record attachment. It deliberately excludes
// the upload journal, which outbound-only Runtime composition never consumes.
func VerifyRecordAttachmentReadSchema(ctx context.Context, db *sql.DB) error {
	if ctx == nil || db == nil {
		return errors.New("record attachment read schema: context and database are required")
	}
	if err := VerifyRequiredTableColumns(
		ctx, db, "_System_File", recordAttachmentReadRequiredColumns,
	); err != nil {
		return fmt.Errorf("record attachment read schema: %w", err)
	}
	return nil
}

// VerifyFileUploadLifecycleIndex owns the physical prerequisite for the
// Runtime lifecycle admission hint. Foreground upload and operator repair do
// not use that hint and therefore keep their base journal readiness contract.
func VerifyFileUploadLifecycleIndex(ctx context.Context, db *sql.DB) error {
	if ctx == nil || db == nil {
		return errors.New("file upload lifecycle schema: context and database are required")
	}
	if err := VerifyExactTableIndex(
		ctx,
		db,
		"_System_FileUploadOperation",
		"idx__System_FileUploadOperation_lifecycle",
		false,
		[]string{"State", "ExpiresAt", "Id"},
	); err != nil {
		return fmt.Errorf("file upload lifecycle schema: %w", err)
	}
	return nil
}

type FileUploadOperationAcceptance struct {
	ID                     string
	IdempotencyKey         string
	RequestHash            string
	OwnerToken             string
	ObjectKey              string
	StorageProvider        string
	StorageBucket          string
	StorageAuthorityDigest string
	SystemFileID           string
	ParentID               string
	ParentObjectAPIName    string
	LeaseUntil             time.Time
	Now                    time.Time
}

type FileUploadOperation struct {
	ID                     string
	IdempotencyKey         string
	RequestHash            string
	State                  string
	OwnerToken             string
	ObjectKey              string
	StorageProvider        string
	StorageBucket          string
	StorageAuthorityDigest string
	SystemFileID           string
	ParentID               string
	ParentObjectAPIName    string
	ExpiresAt              time.Time
}

// Accept creates one durable upload identity. Existing PREPARED/FAILED owners
// remain immutable until the Files cleanup owner deletes their attempt object
// and journal row. Rotating OwnerToken/ObjectKey here would discard the only
// durable cleanup authority for an object committed before a process crash or
// an ambiguous object-store/journal result. BLOB_STORED and COMPLETED rows are
// likewise read-only reconciliation facts and are never silently restarted.
func (r *FileUploadOperationRepository) Accept(
	ctx context.Context,
	input FileUploadOperationAcceptance,
) (FileUploadOperation, bool, error) {
	if r == nil || r.db == nil {
		return FileUploadOperation{}, false, errors.New("file upload operation repository is unavailable")
	}
	if input.ID == "" || input.IdempotencyKey == "" || input.RequestHash == "" || input.OwnerToken == "" ||
		input.ObjectKey == "" || input.StorageProvider == "" || input.StorageBucket == "" ||
		input.StorageAuthorityDigest == "" ||
		input.SystemFileID == "" || input.ParentID == "" ||
		input.ParentObjectAPIName == "" || input.Now.IsZero() ||
		!input.LeaseUntil.After(input.Now) {
		return FileUploadOperation{}, false, errors.New("file upload operation acceptance is incomplete")
	}
	result, err := r.db.ExecContext(ctx, `INSERT IGNORE INTO _System_FileUploadOperation
(Id, IdempotencyKey, RequestHash, State, OwnerToken, ObjectKey, StorageProvider, StorageBucket,
 StorageAuthorityDigest,
 SystemFileId, ParentId, ParentObjectApiName, ExpiresAt)
VALUES (?, ?, ?, 'PREPARED', ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
		input.ID, input.IdempotencyKey, input.RequestHash, input.OwnerToken, input.ObjectKey,
		input.StorageProvider, input.StorageBucket, input.StorageAuthorityDigest,
		input.SystemFileID, input.ParentID,
		input.ParentObjectAPIName, input.LeaseUntil,
	)
	if err != nil {
		return FileUploadOperation{}, false, fmt.Errorf("accept file upload operation: %w", err)
	}
	affected, err := result.RowsAffected()
	if err != nil {
		return FileUploadOperation{}, false, fmt.Errorf("accept file upload operation rows affected: %w", err)
	}
	row, err := r.Load(ctx, input.IdempotencyKey)
	if err != nil {
		return FileUploadOperation{}, false, err
	}
	if !FileUploadOperationMatchesIdentity(row, input) {
		return row, false, ErrFileUploadOperationConflict
	}
	if affected == 1 {
		return row, true, nil
	}
	return row, false, nil
}

func (r *FileUploadOperationRepository) Load(
	ctx context.Context,
	idempotencyKey string,
) (FileUploadOperation, error) {
	if r == nil || r.db == nil || idempotencyKey == "" {
		return FileUploadOperation{}, errors.New("file upload operation load is incomplete")
	}
	var row FileUploadOperation
	var objectKey, storageProvider, storageBucket, storageAuthorityDigest, systemFileID sql.NullString
	var expiresAt sql.NullTime
	err := r.db.QueryRowContext(ctx, `SELECT Id, IdempotencyKey, RequestHash, State,
       OwnerToken, ObjectKey, StorageProvider, StorageBucket, StorageAuthorityDigest,
       SystemFileId, ParentId, ParentObjectApiName, ExpiresAt
  FROM _System_FileUploadOperation
 WHERE IdempotencyKey = ?
 LIMIT 1`, idempotencyKey).Scan(
		&row.ID, &row.IdempotencyKey, &row.RequestHash, &row.State,
		&row.OwnerToken, &objectKey, &storageProvider, &storageBucket, &storageAuthorityDigest,
		&systemFileID, &row.ParentID, &row.ParentObjectAPIName, &expiresAt,
	)
	if err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			return FileUploadOperation{}, ErrFileUploadOperationNotFound
		}
		return FileUploadOperation{}, fmt.Errorf("load file upload operation: %w", err)
	}
	if objectKey.Valid {
		row.ObjectKey = objectKey.String
	}
	if storageProvider.Valid {
		row.StorageProvider = storageProvider.String
	}
	if storageBucket.Valid {
		row.StorageBucket = storageBucket.String
	}
	if storageAuthorityDigest.Valid {
		row.StorageAuthorityDigest = storageAuthorityDigest.String
	}
	if systemFileID.Valid {
		row.SystemFileID = systemFileID.String
	}
	if expiresAt.Valid {
		row.ExpiresAt = expiresAt.Time
	}
	return row, nil
}

func (r *FileUploadOperationRepository) MarkBlobStored(
	ctx context.Context,
	id string,
	ownerToken string,
	headerRequestHash string,
	completeRequestHash string,
	objectKey string,
	expiresAt time.Time,
) error {
	if r == nil || r.db == nil || id == "" || ownerToken == "" ||
		headerRequestHash == "" || completeRequestHash == "" ||
		objectKey == "" || expiresAt.IsZero() {
		return errors.New("file upload blob transition is incomplete")
	}
	result, err := r.db.ExecContext(ctx, `UPDATE _System_FileUploadOperation
SET State = 'BLOB_STORED', RequestHash = ?, ExpiresAt = ?,
    LastModifiedDate = CURRENT_TIMESTAMP(6)
WHERE Id = ? AND State = 'PREPARED' AND OwnerToken = ? AND RequestHash = ?
  AND ObjectKey = ?`,
		completeRequestHash, expiresAt, id, ownerToken, headerRequestHash, objectKey,
	)
	if err != nil {
		return fmt.Errorf("mark file upload blob stored: %w", err)
	}
	return requireOneFileUploadTransition(result)
}

func (r *FileUploadOperationRepository) MarkCompleted(
	ctx context.Context,
	id string,
	ownerToken string,
	completeRequestHash string,
	objectKey string,
	expiresAt time.Time,
) error {
	if r == nil || r.db == nil || id == "" || ownerToken == "" ||
		completeRequestHash == "" || objectKey == "" || expiresAt.IsZero() {
		return errors.New("file upload completion is incomplete")
	}
	result, err := r.db.ExecContext(ctx, `UPDATE _System_FileUploadOperation
SET State = 'COMPLETED', ExpiresAt = ?, LastModifiedDate = CURRENT_TIMESTAMP(6)
WHERE Id = ? AND State = 'BLOB_STORED' AND OwnerToken = ?
  AND RequestHash = ? AND ObjectKey = ?`,
		expiresAt, id, ownerToken, completeRequestHash, objectKey,
	)
	if err != nil {
		return fmt.Errorf("complete file upload operation: %w", err)
	}
	return requireOneFileUploadTransition(result)
}

// FinalizeBlobStored serializes the foreground metadata commit with every
// repair owner on the upload-operation row. The callback must perform the
// _System_File read/write using txCtx; the journal transition and callback
// then share one pessimistic TiDB transaction.
func (r *FileUploadOperationRepository) FinalizeBlobStored(
	ctx context.Context,
	id string,
	ownerToken string,
	completeRequestHash string,
	objectKey string,
	expiresAt time.Time,
	commitMetadata func(context.Context) error,
) error {
	if r == nil || r.db == nil || r.transactions == nil || id == "" || ownerToken == "" ||
		completeRequestHash == "" || objectKey == "" || expiresAt.IsZero() ||
		commitMetadata == nil {
		return errors.New("file upload atomic completion is incomplete")
	}
	return r.transactions.WithPessimisticTransaction(ctx, func(txCtx context.Context) error {
		tx, ok := r.transactions.ExtractTx(txCtx).(*sql.Tx)
		if !ok || tx == nil {
			return errors.New("file upload atomic completion requires a pessimistic transaction")
		}
		row, err := loadFileUploadOperationForUpdate(txCtx, tx, id)
		if err != nil {
			return err
		}
		if row.State != FileUploadOperationBlobStored || row.OwnerToken != ownerToken ||
			row.RequestHash != completeRequestHash || row.ObjectKey != objectKey {
			return ErrFileUploadOperationLostOwnership
		}
		if err := commitMetadata(txCtx); err != nil {
			return err
		}
		result, err := tx.ExecContext(txCtx, `UPDATE _System_FileUploadOperation
SET State = 'COMPLETED', ExpiresAt = ?, LastModifiedDate = CURRENT_TIMESTAMP(6)
WHERE Id = ? AND State = 'BLOB_STORED' AND OwnerToken = ?
  AND RequestHash = ? AND ObjectKey = ?`,
			expiresAt, id, ownerToken, completeRequestHash, objectKey,
		)
		if err != nil {
			return fmt.Errorf("complete file upload operation: %w", err)
		}
		return requireOneFileUploadTransition(result)
	})
}

func loadFileUploadOperationForUpdate(
	ctx context.Context,
	tx *sql.Tx,
	id string,
) (FileUploadOperation, error) {
	var row FileUploadOperation
	var objectKey, storageProvider, storageBucket, systemFileID sql.NullString
	var expiresAt sql.NullTime
	err := tx.QueryRowContext(ctx, `SELECT Id, IdempotencyKey, RequestHash, State,
       OwnerToken, ObjectKey, StorageProvider, StorageBucket,
       SystemFileId, ParentId, ParentObjectApiName, ExpiresAt
  FROM _System_FileUploadOperation
 WHERE Id = ?
 FOR UPDATE`, id).Scan(
		&row.ID, &row.IdempotencyKey, &row.RequestHash, &row.State,
		&row.OwnerToken, &objectKey, &storageProvider, &storageBucket,
		&systemFileID, &row.ParentID, &row.ParentObjectAPIName, &expiresAt,
	)
	if err != nil {
		return FileUploadOperation{}, fmt.Errorf("lock file upload operation: %w", err)
	}
	if objectKey.Valid {
		row.ObjectKey = objectKey.String
	}
	if storageProvider.Valid {
		row.StorageProvider = storageProvider.String
	}
	if storageBucket.Valid {
		row.StorageBucket = storageBucket.String
	}
	if systemFileID.Valid {
		row.SystemFileID = systemFileID.String
	}
	if expiresAt.Valid {
		row.ExpiresAt = expiresAt.Time
	}
	return row, nil
}

// FileUploadOperationMatchesIdentity is the sole comparison path for a
// journal row and a candidate request. ObjectKey and OwnerToken are attempt
// ownership, not caller identity: they remain immutable once Accept creates
// the row and are validated by every state transition.
func FileUploadOperationMatchesIdentity(row FileUploadOperation, input FileUploadOperationAcceptance) bool {
	return row.ID == input.ID && row.IdempotencyKey == input.IdempotencyKey &&
		(row.RequestHash == input.RequestHash || strings.HasPrefix(row.RequestHash, input.RequestHash+"|")) &&
		row.StorageProvider == input.StorageProvider && row.StorageBucket == input.StorageBucket &&
		row.StorageAuthorityDigest == input.StorageAuthorityDigest &&
		row.SystemFileID == input.SystemFileID &&
		row.ParentID == input.ParentID && row.ParentObjectAPIName == input.ParentObjectAPIName
}

func requireOneFileUploadTransition(result sql.Result) error {
	affected, err := result.RowsAffected()
	if err != nil {
		return fmt.Errorf("file upload operation rows affected: %w", err)
	}
	if affected != 1 {
		return ErrFileUploadOperationLostOwnership
	}
	return nil
}