package packs

import (
	"bytes"
	"context"
	"database/sql"
	"errors"
	"fmt"
	"reflect"
	"sort"
	"time"

	cp "github.com/pingcap-inc/nexuscrm-contract/pkg/controlplane"
)

const managedLifecycleTargetTable = "_System_ManagedComponentTargetLock"

func validateManagedLifecycleTargetTable(ctx context.Context, db *sql.DB) error {
	rows, err := db.QueryContext(ctx, `
SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, DATETIME_PRECISION, IS_NULLABLE, COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = ?`, managedLifecycleTargetTable)
	if err != nil {
		return fmt.Errorf("inspect managed lifecycle target columns: %w", err)
	}
	type columnShape struct {
		dataType          string
		length            sql.NullInt64
		datetimePrecision sql.NullInt64
		nullable          string
		defaultValue      sql.NullString
	}
	columns := make(map[string]columnShape, 4)
	for rows.Next() {
		var name, dataType, nullable string
		var length, datetimePrecision sql.NullInt64
		var defaultValue sql.NullString
		if err := rows.Scan(&name, &dataType, &length, &datetimePrecision, &nullable, &defaultValue); err != nil {
			rows.Close()
			return fmt.Errorf("scan managed lifecycle target column: %w", err)
		}
		columns[name] = columnShape{dataType: dataType, length: length, datetimePrecision: datetimePrecision, nullable: nullable, defaultValue: defaultValue}
	}
	if err := rows.Err(); err != nil {
		rows.Close()
		return fmt.Errorf("iterate managed lifecycle target columns: %w", err)
	}
	if err := rows.Close(); err != nil {
		return fmt.Errorf("close managed lifecycle target columns: %w", err)
	}
	length := func(value int64) sql.NullInt64 { return sql.NullInt64{Int64: value, Valid: true} }
	defaultValue := func(value string) sql.NullString { return sql.NullString{String: value, Valid: true} }
	want := map[string]columnShape{
		"TargetDomain":     {dataType: "varbinary", length: length(cp.ComponentManagedMaximumTargetDomain), nullable: "NO"},
		"PhysicalKey":      {dataType: "varbinary", length: length(cp.ComponentManagedMaximumPhysicalKey), nullable: "NO"},
		"CreatedDate":      {dataType: "datetime", datetimePrecision: length(6), nullable: "NO", defaultValue: defaultValue("CURRENT_TIMESTAMP(6)")},
		"LastModifiedDate": {dataType: "datetime", datetimePrecision: length(6), nullable: "NO", defaultValue: defaultValue("CURRENT_TIMESTAMP(6)")},
	}
	if !reflect.DeepEqual(columns, want) {
		return fmt.Errorf("managed lifecycle target table column shape = %#v, want %#v", columns, want)
	}
	indexRows, err := db.QueryContext(ctx, `
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE CONSTRAINT_SCHEMA = DATABASE()
  AND TABLE_NAME = ?
  AND CONSTRAINT_NAME = 'PRIMARY'
ORDER BY ORDINAL_POSITION`, managedLifecycleTargetTable)
	if err != nil {
		return fmt.Errorf("inspect managed lifecycle target primary key: %w", err)
	}
	var primaryKey []string
	for indexRows.Next() {
		var column string
		if err := indexRows.Scan(&column); err != nil {
			indexRows.Close()
			return fmt.Errorf("scan managed lifecycle target primary key: %w", err)
		}
		primaryKey = append(primaryKey, column)
	}
	if err := indexRows.Err(); err != nil {
		indexRows.Close()
		return fmt.Errorf("iterate managed lifecycle target primary key: %w", err)
	}
	if err := indexRows.Close(); err != nil {
		return fmt.Errorf("close managed lifecycle target primary key: %w", err)
	}
	if !reflect.DeepEqual(primaryKey, []string{"TargetDomain", "PhysicalKey"}) {
		return fmt.Errorf("managed lifecycle target primary key = %q, want [TargetDomain PhysicalKey]", primaryKey)
	}
	return nil
}

func prepareManagedLifecycleTargets(ctx context.Context, db *sql.DB, targets []cp.ComponentManagedTarget, maxElapsed time.Duration, maxAttempts int) error {
	if db == nil {
		return fmt.Errorf("managed lifecycle target database is required")
	}
	canonical, err := canonicalManagedLifecycleTargets(targets)
	if err != nil {
		return err
	}
	if maxElapsed <= 0 || maxAttempts <= 0 {
		return fmt.Errorf("managed lifecycle target preparation requires positive time and attempt bounds")
	}
	prepareCtx, cancel := context.WithTimeout(ctx, maxElapsed)
	defer cancel()
	var lastErr error
	for attempt := 1; attempt <= maxAttempts; attempt++ {
		lastErr = prepareManagedLifecycleTargetsOnce(prepareCtx, db, canonical)
		observed, observationErr := observeManagedLifecycleTargets(prepareCtx, db, canonical)
		if observationErr == nil && observed {
			return nil
		}
		lastErr = errors.Join(lastErr, observationErr)
		if prepareCtx.Err() != nil {
			break
		}
	}
	if lastErr == nil {
		lastErr = fmt.Errorf("required target rows were not positively observed")
	}
	return fmt.Errorf("prepare managed lifecycle targets: %w", lastErr)
}

func prepareManagedLifecycleTargetsOnce(ctx context.Context, db *sql.DB, targets []cp.ComponentManagedTarget) (err error) {
	tx, err := db.BeginTx(ctx, &sql.TxOptions{Isolation: sql.LevelReadCommitted})
	if err != nil {
		return err
	}
	finished := false
	defer func() {
		if finished {
			return
		}
		if rollbackErr := tx.Rollback(); rollbackErr != nil && !errors.Is(rollbackErr, sql.ErrTxDone) {
			err = errors.Join(err, fmt.Errorf("rollback target preparation: %w", rollbackErr))
		}
	}()
	statement := "INSERT INTO `" + managedLifecycleTargetTable + "` (`TargetDomain`, `PhysicalKey`) VALUES (?, ?) ON DUPLICATE KEY UPDATE `PhysicalKey` = VALUES(`PhysicalKey`)"
	for _, target := range targets {
		if _, err := tx.ExecContext(ctx, statement, []byte(target.TargetDomain), target.PhysicalKeyBytes); err != nil {
			return fmt.Errorf("prepare target %q: %w", target.TargetDomain, err)
		}
	}
	if err := tx.Commit(); err != nil {
		finished = true
		return fmt.Errorf("commit target preparation: %w", err)
	}
	finished = true
	return nil
}

func observeManagedLifecycleTargets(ctx context.Context, db *sql.DB, targets []cp.ComponentManagedTarget) (bool, error) {
	statement := "SELECT 1 FROM `" + managedLifecycleTargetTable + "` WHERE `TargetDomain` = ? AND `PhysicalKey` = ? LIMIT 1"
	for _, target := range targets {
		var present int
		if err := db.QueryRowContext(ctx, statement, []byte(target.TargetDomain), target.PhysicalKeyBytes).Scan(&present); err != nil {
			if errors.Is(err, sql.ErrNoRows) {
				return false, nil
			}
			return false, fmt.Errorf("observe target %q: %w", target.TargetDomain, err)
		}
		if present != 1 {
			return false, fmt.Errorf("observe target %q returned marker %d", target.TargetDomain, present)
		}
	}
	return true, nil
}

func lockManagedLifecycleTargets(ctx context.Context, tx *sql.Tx, targets []cp.ComponentManagedTarget, maxWait time.Duration) (time.Duration, error) {
	if tx == nil {
		return 0, fmt.Errorf("managed lifecycle target transaction is required")
	}
	canonical, err := canonicalManagedLifecycleTargets(targets)
	if err != nil {
		return 0, err
	}
	lockCtx, cancel := context.WithTimeout(ctx, maxWait)
	defer cancel()
	started := time.Now()
	statement := "SELECT 1 FROM `" + managedLifecycleTargetTable + "` WHERE `TargetDomain` = ? AND `PhysicalKey` = ? FOR UPDATE"
	for _, target := range canonical {
		var present int
		if err := tx.QueryRowContext(lockCtx, statement, []byte(target.TargetDomain), target.PhysicalKeyBytes).Scan(&present); err != nil {
			return time.Since(started), fmt.Errorf("lock target %q: %w", target.TargetDomain, err)
		}
		if present != 1 {
			return time.Since(started), fmt.Errorf("lock target %q returned marker %d", target.TargetDomain, present)
		}
	}
	return time.Since(started), nil
}

func canonicalManagedLifecycleTargets(targets []cp.ComponentManagedTarget) ([]cp.ComponentManagedTarget, error) {
	if len(targets) == 0 {
		return nil, fmt.Errorf("managed lifecycle targets are required")
	}
	out := make([]cp.ComponentManagedTarget, len(targets))
	for index, target := range targets {
		if target.TargetDomain == "" || len(target.TargetDomain) > cp.ComponentManagedMaximumTargetDomain || len(target.PhysicalKeyBytes) == 0 || len(target.PhysicalKeyBytes) > cp.ComponentManagedMaximumPhysicalKey {
			return nil, fmt.Errorf("managed lifecycle target %d is invalid", index)
		}
		out[index] = cp.ComponentManagedTarget{TargetDomain: target.TargetDomain, PhysicalKeyBytes: append([]byte(nil), target.PhysicalKeyBytes...)}
	}
	sort.Slice(out, func(i, j int) bool {
		if out[i].TargetDomain != out[j].TargetDomain {
			return out[i].TargetDomain < out[j].TargetDomain
		}
		return bytes.Compare(out[i].PhysicalKeyBytes, out[j].PhysicalKeyBytes) < 0
	})
	for index := 1; index < len(out); index++ {
		if out[index-1].TargetDomain == out[index].TargetDomain && bytes.Equal(out[index-1].PhysicalKeyBytes, out[index].PhysicalKeyBytes) {
			return nil, fmt.Errorf("managed lifecycle target %q is duplicated", out[index].TargetDomain)
		}
	}
	return out, nil
}