package persistence

import (
	"bytes"
	"context"
	"crypto/sha256"
	"database/sql"
	"encoding/hex"
	"encoding/json"
	"errors"
	"fmt"
	semantic "github.com/pingcap-inc/nexuscrm-contract/pkg/runtime/applicationnavigationv1"
	"io"
	"strings"

	"github.com/nexuscrm/shared/pkg/constants"
)

var (
	ErrNavigationMissing         = errors.New("application navigation is unavailable")
	ErrNavigationUninitialized   = errors.New("application navigation migration is required")
	ErrNavigationConflict        = errors.New("application navigation changed; reload before saving")
	ErrNavigationRequestConflict = errors.New("navigation request identity has different content")
)

const navigationReceiptTable = constants.TableApplicationNavigationReceipt

// ApplicationNavigationRepository is the single current navigation persistence
// owner. Mutation methods require the owning pessimistic transaction. Permission
// authorization and target interpretation are supplied by their owning engines.
type ApplicationNavigationRepository struct {
	db                               *sql.DB
	transactions                     *TransactionManager
	permissionStateDirectCoordinator *PermissionStateDirectWriterCoordinator
	initializationError              error
	setupAudit                       *SetupAuditRepository
}

func NewApplicationNavigationRepository(db *sql.DB, transactions *TransactionManager) *ApplicationNavigationRepository {
	repository := &ApplicationNavigationRepository{db: db, transactions: transactions, setupAudit: NewSetupAuditRepository(db)}
	authority, err := NewPermissionStateAuthority(transactions)
	if err == nil {
		repository.permissionStateDirectCoordinator, err = NewPermissionStateDirectWriterCoordinator(transactions, authority)
	}
	repository.initializationError = err
	return repository
}
func VerifyApplicationNavigationSchema(ctx context.Context, db *sql.DB) error {
	if err := VerifyRequiredTableColumns(ctx, db, "_System_App", map[string]string{"Id": "varchar(255)", "FrontendAppId": "varchar(255)", "NavigationItems": "json", "NavigationRevision": "bigint"}); err != nil {
		return err
	}
	if err := VerifyRequiredTableColumns(ctx, db, constants.TableSetupAuditTrail, map[string]string{"Id": "varchar(255)", "Action": "varchar(255)", "Section": "varchar(255)", "Display": "mediumtext", "CreatedById": "varchar(255)", "CreatedDate": "datetime"}); err != nil {
		return err
	}
	if err := VerifyRequiredTableColumns(ctx, db, navigationReceiptTable, map[string]string{"Id": "char(64)", "ApplicationId": "varchar(255)", "ActorId": "varchar(255)", "RequestDigest": "char(64)", "Result": "json", "CreatedDate": "datetime(6)", "LastModifiedDate": "datetime(6)"}); err != nil {
		return err
	}
	if err := VerifyExactTableIndex(ctx, db, navigationReceiptTable, "PRIMARY", true, []string{"Id"}); err != nil {
		return err
	}
	// Receipts belong to the app's lifetime, including generic physical deletion.
	// Verify the database-owned cleanup once at readiness, never on each save.
	var cascades int
	if err := db.QueryRowContext(ctx, `SELECT COUNT(*)
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE k
JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS r
 ON r.CONSTRAINT_SCHEMA = k.CONSTRAINT_SCHEMA AND r.TABLE_NAME = k.TABLE_NAME
 AND r.CONSTRAINT_NAME = k.CONSTRAINT_NAME
WHERE k.CONSTRAINT_SCHEMA = DATABASE() AND k.TABLE_NAME = ?
 AND k.COLUMN_NAME = 'ApplicationId' AND k.REFERENCED_TABLE_SCHEMA = DATABASE()
 AND k.REFERENCED_TABLE_NAME = '_System_App' AND k.REFERENCED_COLUMN_NAME = 'Id'
 AND r.DELETE_RULE = 'CASCADE'`, navigationReceiptTable).Scan(&cascades); err != nil {
		return fmt.Errorf("navigation receipt lifecycle: %w", err)
	}
	if cascades != 1 {
		return errors.New("navigation receipts require ApplicationId -> _System_App(Id) ON DELETE CASCADE")
	}
	return nil
}
func (r *ApplicationNavigationRepository) executor(ctx context.Context, locked bool) (Executor, error) {
	if r != nil && r.initializationError != nil {
		return nil, r.initializationError
	}
	if r == nil || r.db == nil || r.transactions == nil {
		return nil, errors.New("navigation repository requires database and transaction owner")
	}
	if r.transactions.ownsPessimisticTx(ctx) || (!locked && r.transactions.IsTransactionActive(ctx)) {
		tx, ok := ctx.Value(txContextKey{}).(*sql.Tx)
		if !ok || tx == nil {
			return nil, errors.New("navigation transaction is missing")
		}
		return tx, nil
	}
	if locked {
		return nil, errors.New("navigation mutation requires its owning pessimistic transaction")
	}
	return r.db, nil
}
func (r *ApplicationNavigationRepository) Read(ctx context.Context, ownerID, applicationID string) (semantic.Document, error) {
	return r.read(ctx, ownerID, applicationID, false)
}
func (r *ApplicationNavigationRepository) Lock(ctx context.Context, ownerID, applicationID string) (semantic.Document, error) {
	return r.read(ctx, ownerID, applicationID, true)
}
func (r *ApplicationNavigationRepository) read(ctx context.Context, ownerID, applicationID string, lock bool) (semantic.Document, error) {
	exec, err := r.executor(ctx, lock)
	if err != nil {
		return semantic.Document{}, err
	}
	query := "SELECT Id, FrontendAppId, NavigationRevision, NavigationItems FROM `_System_App` WHERE Id = ? AND FrontendAppId = ?"
	if lock {
		query += " FOR UPDATE"
	}
	var d semantic.Document
	var actualOwner string
	var raw []byte
	err = exec.QueryRowContext(ctx, query, applicationID, ownerID).Scan(&d.ApplicationID, &actualOwner, &d.Revision, &raw)
	if errors.Is(err, sql.ErrNoRows) {
		return d, ErrNavigationMissing
	}
	if err != nil {
		return d, err
	}
	if d.ApplicationID != applicationID || actualOwner != ownerID {
		return semantic.Document{}, ErrNavigationMissing
	}
	if d.Revision == 0 {
		return d, ErrNavigationUninitialized
	}
	d.Items, err = decodeNavigation(raw)
	return d, err
}
func decodeNavigation(raw []byte) ([]semantic.Item, error) {
	decoder := json.NewDecoder(bytes.NewReader(raw))
	decoder.DisallowUnknownFields()
	var items []semantic.Item
	if err := decoder.Decode(&items); err != nil {
		return nil, fmt.Errorf("invalid stored navigation: %w", err)
	}
	if err := decoder.Decode(new(any)); err != io.EOF {
		return nil, errors.New("invalid trailing navigation data")
	}
	if err := semantic.ValidateItems(items); err != nil {
		return nil, errors.New("invalid stored navigation document")
	}
	return items, nil
}
func (r *ApplicationNavigationRepository) ReadMany(ctx context.Context, ownerID string, ids []string) ([]semantic.Document, error) {
	if len(ids) > semantic.MaxApplications {
		return nil, errors.New("too many navigation applications")
	}
	result := make([]semantic.Document, 0, len(ids))
	if len(ids) == 0 {
		return result, nil
	}
	exec, err := r.executor(ctx, false)
	if err != nil {
		return nil, err
	}
	args := make([]any, 0, len(ids)+1)
	args = append(args, ownerID)
	allowed := map[string]bool{}
	for _, id := range ids {
		args = append(args, id)
		allowed[id] = true
	}
	rows, err := exec.QueryContext(ctx, "SELECT Id, FrontendAppId, NavigationRevision, NavigationItems FROM `_System_App` WHERE FrontendAppId = ? AND Id IN ("+strings.TrimSuffix(strings.Repeat("?,", len(ids)), ",")+") ORDER BY Id", args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	for rows.Next() {
		var actualOwner string
		var d semantic.Document
		var raw []byte
		if err := rows.Scan(&d.ApplicationID, &actualOwner, &d.Revision, &raw); err != nil {
			return nil, err
		}
		if !allowed[d.ApplicationID] || actualOwner != ownerID {
			continue
		}
		if d.Revision == 0 {
			return nil, ErrNavigationUninitialized
		}
		d.Items, err = decodeNavigation(raw)
		if err != nil {
			return nil, err
		}
		result = append(result, d)
	}
	return result, rows.Err()
}
func navigationDigest(value any) string {
	raw, _ := json.Marshal(value)
	hash := sha256.Sum256(raw)
	return hex.EncodeToString(hash[:])
}
func navigationReceiptID(ownerID, actorID string, request semantic.SaveRequest) string {
	return navigationDigest([]string{ownerID, request.ApplicationID, actorID, request.IdempotencyKey})
}

type navigationAuditChange struct {
	OwnerID       string            `json:"owner_id"`
	ApplicationID string            `json:"application_id"`
	RequestID     string            `json:"request_id"`
	Before        semantic.Document `json:"before"`
	After         semantic.Document `json:"after"`
}

// Mutation result contains the exact committed document, never a serving pointer.
type NavigationMutationResult struct {
	Document semantic.Document `json:"document"`
	Changed  bool              `json:"changed"`
}

func (r *ApplicationNavigationRepository) Replay(ctx context.Context, ownerID, actorID string, request semantic.SaveRequest) (NavigationMutationResult, bool, error) {
	if err := semantic.ValidateSaveRequest(ctx, request); err != nil {
		return NavigationMutationResult{}, false, err
	}
	if !semantic.ValidIdentifier(ownerID) || !semantic.ValidIdentifier(actorID) {
		return NavigationMutationResult{}, false, ErrNavigationMissing
	}
	exec, err := r.executor(ctx, true)
	if err != nil {
		return NavigationMutationResult{}, false, err
	}
	var digest string
	var raw []byte
	err = exec.QueryRowContext(ctx, "SELECT RequestDigest, Result FROM `_System_ApplicationNavigationReceipt` WHERE Id = ?", navigationReceiptID(ownerID, actorID, request)).Scan(&digest, &raw)
	if errors.Is(err, sql.ErrNoRows) {
		return NavigationMutationResult{}, false, nil
	}
	if err != nil {
		return NavigationMutationResult{}, false, err
	}
	if digest != navigationDigest(request) {
		return NavigationMutationResult{}, false, ErrNavigationRequestConflict
	}
	var result NavigationMutationResult
	if err := json.Unmarshal(raw, &result); err != nil {
		return result, false, err
	}
	return result, true, nil
}
func (r *ApplicationNavigationRepository) Store(ctx context.Context, ownerID, actorID string, request semantic.SaveRequest, before semantic.Document) (NavigationMutationResult, error) {
	if err := semantic.ValidateSaveRequest(ctx, request); err != nil {
		return NavigationMutationResult{}, err
	}
	if !semantic.ValidIdentifier(ownerID) || !semantic.ValidIdentifier(actorID) {
		return NavigationMutationResult{}, ErrNavigationMissing
	}
	const writerID = "nexuscrm-persistence/application_navigation.go#ApplicationNavigationRepository.Store"
	if r.permissionStateDirectWriterShouldRun(ctx, writerID) {
		if _, err := r.executor(ctx, true); err != nil {
			return NavigationMutationResult{}, err
		}
		var result NavigationMutationResult
		err := r.runPermissionStateDirectWriter(ctx, writerID, request.ApplicationID, func(txCtx context.Context) error {
			var err error
			result, err = r.Store(txCtx, ownerID, actorID, request, before)
			return err
		})
		return result, err
	}

	current, err := r.Lock(ctx, ownerID, request.ApplicationID)
	if err != nil {
		return NavigationMutationResult{}, err
	}
	if navigationDigest(current) != navigationDigest(before) {
		return NavigationMutationResult{}, ErrNavigationConflict
	}
	exec, err := r.executor(ctx, true)
	if err != nil {
		return NavigationMutationResult{}, err
	}
	if before.ApplicationID != request.ApplicationID || before.Revision != request.ExpectedRevision {
		return NavigationMutationResult{}, ErrNavigationConflict
	}
	encoded, err := json.Marshal(request.Items)
	if err != nil {
		return NavigationMutationResult{}, err
	}
	previous, err := json.Marshal(before.Items)
	if err != nil {
		return NavigationMutationResult{}, err
	}
	result := NavigationMutationResult{Document: before, Changed: !bytes.Equal(encoded, previous)}
	if result.Changed {
		if before.Revision == uint64(1<<63-1) {
			return result, errors.New("navigation revision exhausted")
		}
		result.Document = semantic.Document{ApplicationID: request.ApplicationID, Revision: before.Revision + 1, Items: request.Items}
		updated, err := exec.ExecContext(ctx, "UPDATE `_System_App` SET NavigationItems = ?, NavigationRevision = ? WHERE Id = ? AND FrontendAppId = ? AND NavigationRevision = ?", encoded, result.Document.Revision, request.ApplicationID, ownerID, before.Revision)
		if err != nil {
			return result, err
		}
		count, err := updated.RowsAffected()
		if err != nil {
			return result, err
		}
		if count != 1 {
			return result, ErrNavigationConflict
		}
		change, err := json.Marshal(navigationAuditChange{
			OwnerID: ownerID, ApplicationID: request.ApplicationID,
			RequestID: request.IdempotencyKey, Before: before, After: result.Document,
		})
		if err != nil {
			return result, err
		}
		// The shared setup writer uses this same transaction. Audit failure must
		// roll back navigation and its receipt; a replay never reaches this write.
		if err := r.setupAudit.RecordSetupChange(ctx, "Updated application navigation", "Application navigation", string(change), actorID); err != nil {
			return result, err
		}
	}
	payload, err := json.Marshal(result)
	if err != nil {
		return result, err
	}
	_, err = exec.ExecContext(ctx, "INSERT INTO `_System_ApplicationNavigationReceipt` (Id, ApplicationId, ActorId, RequestDigest, Result, CreatedDate) VALUES (?, ?, ?, ?, ?, NOW(6))", navigationReceiptID(ownerID, actorID, request), request.ApplicationID, actorID, navigationDigest(request), payload)
	return result, err
}

// NavigationInitializationSource is a locked source for Controlplane migration.
// RawItems is inventory only; Runtime never serves revision-zero documents.
type NavigationInitializationSource struct {
	Revision uint64
	RawItems json.RawMessage
	Digest   string
}

func NavigationSourceDigest(raw []byte) string {
	sum := sha256.Sum256(raw)
	return hex.EncodeToString(sum[:])
}

func (r *ApplicationNavigationRepository) LockInitializationSource(ctx context.Context, ownerID, applicationID string) (NavigationInitializationSource, error) {
	exec, err := r.executor(ctx, true)
	if err != nil {
		return NavigationInitializationSource{}, err
	}
	var actualID, actualOwner string
	var source NavigationInitializationSource
	if err := exec.QueryRowContext(ctx, "SELECT Id, FrontendAppId, NavigationRevision, NavigationItems FROM `_System_App` WHERE Id = ? AND FrontendAppId = ? FOR UPDATE", applicationID, ownerID).Scan(&actualID, &actualOwner, &source.Revision, &source.RawItems); err != nil {
		return source, err
	}
	if actualID != applicationID || actualOwner != ownerID {
		return NavigationInitializationSource{}, ErrNavigationMissing
	}
	source.Digest = NavigationSourceDigest(source.RawItems)
	return source, nil
}

// Initialize is the only install/migration write port. The source digest binds
// an explicit migration decision to the row locked in this transaction. A
// changed or initialized document cannot be overwritten by a stale installer.
func (r *ApplicationNavigationRepository) Initialize(ctx context.Context, ownerID, applicationID string, items []semantic.Item, sourceDigest string) (bool, error) {
	if err := semantic.ValidateItems(items); err != nil {
		return false, err
	}
	const writerID = "nexuscrm-persistence/application_navigation.go#ApplicationNavigationRepository.Initialize"
	if r.permissionStateDirectWriterShouldRun(ctx, writerID) {
		if _, err := r.executor(ctx, true); err != nil {
			return false, err
		}
		var changed bool
		err := r.runPermissionStateDirectWriter(ctx, writerID, applicationID, func(txCtx context.Context) error {
			var err error
			changed, err = r.Initialize(txCtx, ownerID, applicationID, items, sourceDigest)
			return err
		})
		return changed, err
	}

	source, err := r.LockInitializationSource(ctx, ownerID, applicationID)
	if err != nil {
		return false, err
	}
	if source.Revision != 0 {
		return false, nil
	}
	if sourceDigest != source.Digest {
		return false, ErrNavigationConflict
	}
	exec, err := r.executor(ctx, true)
	if err != nil {
		return false, err
	}
	raw, err := json.Marshal(items)
	if err != nil {
		return false, err
	}
	result, err := exec.ExecContext(ctx, "UPDATE `_System_App` SET NavigationItems = ?, NavigationRevision = 1 WHERE Id = ? AND FrontendAppId = ? AND NavigationRevision = 0", raw, applicationID, ownerID)
	if err != nil {
		return false, err
	}
	count, err := result.RowsAffected()
	if err != nil {
		return false, err
	}
	if count != 1 {
		return false, ErrNavigationConflict
	}
	return true, nil
}

func (r *ApplicationNavigationRepository) permissionStateDirectWriterShouldRun(ctx context.Context, writerID string) bool {
	return r != nil && r.permissionStateDirectCoordinator != nil && r.permissionStateDirectCoordinator.shouldRun(ctx, writerID)
}
func (r *ApplicationNavigationRepository) runPermissionStateDirectWriter(ctx context.Context, writerID, applicationID string, mutation func(context.Context) error) error {
	// The shared App row retains security-sensitive ownership. Use the same
	// generated guard as other writers; a navigation-only update has no delta
	// in those facts and therefore never advances Permission State.
	executor, err := r.executor(ctx, true)
	if err != nil {
		return err
	}
	return r.permissionStateDirectCoordinator.runProjected(ctx, writerID, []permissionStateDirectTarget{
		permissionStateDirectTargetFor("_System_App", permissionStateDirectEqual("Id"), applicationID),
	}, func(context.Context) Executor { return executor }, mutation)
}