package dbaasui

import (
	"context"
	"fmt"
	"regexp"
	"strings"

	"github.com/tidbcloud/controlplane9/internal/config"
)

const essentialV2UIFeatureFlag = "essentialv2_enable"
const productPlanActivationLock = "cp9-product-plan-" + config.EssentialV2UIOrganizationID

var seedIdentityEmailPattern = regexp.MustCompile(`^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$`)

// CommandRunner keeps SQL on stdin and command metadata in argv, so status
// output and process inspection never disclose the configured identity.
type CommandRunner func(context.Context, []byte, ...string) ([]byte, error)

// Dimension is one independently inspectable UI readiness result.
type Dimension struct {
	Ready   bool   `json:"ready"`
	Status  string `json:"status"`
	Message string `json:"message,omitempty"`
}

// EnsureSeedIdentity idempotently activates the resolved Account user and
// org:owner bindings so the UI proof identity can exercise product lifecycles
// in both CP9 organizations.
// Authentication remains owned by Auth0; CP9 does not create passwords, infer
// an owner, or implement just-in-time provisioning.
func EnsureSeedIdentity(ctx context.Context, email string, run CommandRunner) Dimension {
	email = strings.TrimSpace(email)
	if email == "" {
		return degradedDimension("not_configured", "designated seed identity is not configured")
	}
	if !seedIdentityEmailPattern.MatchString(email) {
		return degradedDimension("invalid", "designated seed identity is not a plain email address")
	}
	if run == nil {
		return degradedDimension("unavailable", "Account identity command runner is unavailable")
	}
	output, err := run(ctx, []byte(seedIdentityMutationSQL(email)), seedIdentityCommand()...)
	if err != nil {
		return degradedDimension("unavailable", fmt.Sprintf("Account seed identity mutation failed: %v", err))
	}
	if strings.TrimSpace(string(output)) != "1" {
		return degradedDimension("not_ready", "Account seed identity verification did not find both active org owner bindings")
	}
	return Dimension{Ready: true, Status: "ready", Message: "designated seed identity is active in both CP9 organizations"}
}

// EnsureSeedIdentityForSession extends the login identity contract with the
// plan-selection flag required when this session owns an Essential v2 pool.
func EnsureSeedIdentityForSession(
	ctx context.Context,
	email string,
	essentialV2 bool,
	run CommandRunner,
) Dimension {
	identity := EnsureSeedIdentity(ctx, email, run)
	if !identity.Ready || !essentialV2 {
		return identity
	}
	feature := EnsureEssentialV2UIFeature(ctx, run)
	if !feature.Ready {
		return feature
	}
	identity.Message += "; Essential v2 UI plan is enabled"
	return identity
}

// CheckSeedIdentity verifies the Account half of the identity contract without
// performing any mutation. It is used by cp9 status after lifecycle setup.
func CheckSeedIdentity(ctx context.Context, email string, run CommandRunner) Dimension {
	email = strings.TrimSpace(email)
	if email == "" {
		return degradedDimension("not_configured", "designated seed identity is not configured")
	}
	if !seedIdentityEmailPattern.MatchString(email) {
		return degradedDimension("invalid", "designated seed identity is not a plain email address")
	}
	if run == nil {
		return degradedDimension("unavailable", "Account identity command runner is unavailable")
	}
	output, err := run(ctx, []byte(seedIdentityVerificationSQL(email)), seedIdentityCommand()...)
	if err != nil {
		return degradedDimension("unavailable", fmt.Sprintf("Account seed identity check failed: %v", err))
	}
	if strings.TrimSpace(string(output)) != "1" {
		return degradedDimension("not_ready", "Account seed identity is not an active owner in both CP9 organizations")
	}
	return Dimension{Ready: true, Status: "ready", Message: "designated seed identity is active in both CP9 organizations"}
}

// CheckSeedIdentityForSession verifies both login membership and any
// session-owned plan-selection flag without mutating Account state.
func CheckSeedIdentityForSession(
	ctx context.Context,
	email string,
	essentialV2 bool,
	run CommandRunner,
) Dimension {
	identity := CheckSeedIdentity(ctx, email, run)
	if !identity.Ready || !essentialV2 {
		return identity
	}
	feature := checkEssentialV2UIFeature(ctx, run)
	if !feature.Ready {
		return feature
	}
	identity.Message += "; Essential v2 UI plan is enabled"
	return identity
}

// EnsureEssentialV2UIFeature idempotently enables the Essential v2 plan for
// the alternate CP9 organization. Essential v1 remains available to the
// default organization through the independent Starter catalog route.
func EnsureEssentialV2UIFeature(ctx context.Context, run CommandRunner) Dimension {
	if run == nil {
		return degradedDimension("unavailable", "Essential v2 UI feature command runner is unavailable")
	}
	output, err := run(ctx, []byte(essentialV2UIFeatureMutationSQL()), seedIdentityCommand()...)
	if err != nil {
		return degradedDimension("unavailable", fmt.Sprintf("Essential v2 UI plan mutation failed: %v", err))
	}
	if strings.TrimSpace(string(output)) != "1" {
		return degradedDimension("not_ready", "Essential v2 UI plan is not ready")
	}
	return Dimension{Ready: true, Status: "ready", Message: "Essential v2 UI plan is enabled for the alternate organization"}
}

func checkEssentialV2UIFeature(ctx context.Context, run CommandRunner) Dimension {
	if run == nil {
		return degradedDimension("unavailable", "Essential v2 UI feature command runner is unavailable")
	}
	output, err := run(ctx, []byte(essentialV2UIFeatureVerificationSQL()), seedIdentityCommand()...)
	if err != nil {
		return degradedDimension("unavailable", fmt.Sprintf("Essential v2 UI plan check failed: %v", err))
	}
	if strings.TrimSpace(string(output)) != "1" {
		return degradedDimension("not_ready", "Essential v2 UI plan is not ready")
	}
	return Dimension{Ready: true, Status: "ready", Message: "Essential v2 UI plan is enabled for the alternate organization"}
}

func seedIdentityCommand() []string {
	return []string{
		"-n", "cp9-system",
		"exec", "-i", "deploy/premium-metadb", "--",
		"env", "MYSQL_PWD=cp9-local",
		"mysql", "-N", "-B", "-uroot", "-Daccount",
	}
}

func seedIdentityMutationSQL(email string) string {
	return strings.Join([]string{
		"INSERT INTO users (created_at, updated_at, tenant_id, first_name, last_name, email, status, job, phone_number, role)",
		"SELECT NOW(), NOW(), " + config.DefaultUIOrganizationID + ", 'CP9', 'UI Seed', '" + email + "', 'active', '', '', 'member'",
		"WHERE NOT EXISTS (SELECT 1 FROM users WHERE email='" + email + "');",
		"UPDATE users SET status='active', tenant_id=" + config.DefaultUIOrganizationID + ", updated_at=NOW() WHERE email='" + email + "';",
		"UPDATE role_bindings rb JOIN users u ON rb.target_type='USER' AND rb.target_id=u.id",
		"JOIN rbac_roles r ON r.name='org:owner'",
		"SET rb.status='active', rb.updated_at=NOW(), rb.role='org:owner', rb.role_id=r.id",
		"WHERE u.email='" + email + "' AND rb.scope_type='ORG' AND rb.scope_id IN (" +
			config.DefaultUIOrganizationID + "," + config.EssentialV2UIOrganizationID + ");",
		"INSERT INTO role_bindings (created_at, updated_at, target_type, target_id, scope_type, scope_id, role, status, role_id)",
		"SELECT NOW(), NOW(), 'USER', u.id, 'ORG', org.id, 'org:owner', 'active', r.id",
		"FROM users u JOIN rbac_roles r ON r.name='org:owner'",
		"CROSS JOIN (SELECT " + config.DefaultUIOrganizationID + " AS id UNION ALL SELECT " +
			config.EssentialV2UIOrganizationID + ") org",
		"WHERE u.email='" + email + "' AND NOT EXISTS (",
		"SELECT 1 FROM role_bindings rb WHERE rb.target_type='USER' AND rb.target_id=u.id",
		"AND rb.scope_type='ORG' AND rb.scope_id=org.id AND rb.role='org:owner');",
		seedIdentityVerificationSQL(email),
	}, " ")
}

func seedIdentityVerificationSQL(email string) string {
	return "SELECT IF(COUNT(DISTINCT u.id)=1 AND COUNT(DISTINCT rb.scope_id)=2,1,0) FROM users u " +
		"JOIN role_bindings rb ON rb.target_type='USER' AND rb.target_id=u.id " +
		"JOIN rbac_roles r ON r.id=rb.role_id AND r.name='org:owner' " +
		"WHERE u.email='" + email + "' AND u.status='active' AND u.tenant_id=" + config.DefaultUIOrganizationID + " " +
		"AND rb.scope_type='ORG' AND rb.scope_id IN (" + config.DefaultUIOrganizationID + "," +
		config.EssentialV2UIOrganizationID + ") AND rb.status='active';"
}

func essentialV2UIFeatureMutationSQL() string {
	return strings.Join([]string{
		"SET @cp9_product_plan_lock = GET_LOCK('" + productPlanActivationLock + "', 60);",
		"START TRANSACTION;",
		"INSERT INTO feature_flags (flag_name, description, owner, email_suffix, min_org_id)",
		"SELECT '" + essentialV2UIFeatureFlag + "', 'CP9 Essential v2 UI plan', 'cp9', '', '' WHERE @cp9_product_plan_lock=1",
		"ON DUPLICATE KEY UPDATE updated_at=NOW();",
		"INSERT INTO feature_flag_orgs (flag_id, org_id)",
		"SELECT id, " + config.EssentialV2UIOrganizationID + " FROM feature_flags WHERE flag_name='" + essentialV2UIFeatureFlag + "'",
		"AND @cp9_product_plan_lock=1",
		"ON DUPLICATE KEY UPDATE updated_at=NOW();",
		"UPDATE nextgen_global_service.region_service_plans SET updated_at=NOW(3), deleted_at=NULL",
		"WHERE region_id=2 AND service_plan='Essential_V2' AND @cp9_product_plan_lock=1;",
		"COMMIT;",
		"DO RELEASE_LOCK('" + productPlanActivationLock + "');",
		essentialV2UIFeatureVerificationSQL(),
	}, " ")
}

func essentialV2UIFeatureVerificationSQL() string {
	return "SELECT IF(" +
		"(SELECT COUNT(*) FROM feature_flag_orgs o JOIN feature_flags f ON f.id=o.flag_id " +
		"WHERE f.flag_name='" + essentialV2UIFeatureFlag + "' AND o.org_id=" + config.EssentialV2UIOrganizationID + ")=1 " +
		"AND (SELECT COUNT(*) FROM nextgen_global_service.region_service_plans " +
		"WHERE region_id=2 AND service_plan='Essential_V2' AND deleted_at IS NULL)=1, 1, 0);"
}

func degradedDimension(status, message string) Dimension {
	return Dimension{Ready: false, Status: status, Message: message}
}