package schemacatalog

import (
	"crypto/sha256"
	"encoding/hex"
	"encoding/json"
	"fmt"
	"sort"
	"strings"

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

const (
	AssignmentPolicyStoreInventorySchema = "nexuscrm.permission-set-assignment-policy-store-inventory.v3"

	AssignmentPolicyClaimTable            = "_System_PermissionSetAssignmentPolicyClaim"
	AssignmentPolicyReleaseReceiptTable   = "_System_PermissionSetAssignmentPolicyTargetReleaseReceipt"
	AssignmentPolicySelectorTable         = "_System_PermissionSetAssignmentPolicySelector"
	AssignmentPolicyTargetStateTable      = "_System_PermissionSetAssignmentPolicyTargetState"
	AssignmentPolicyTombstoneReceiptTable = "_System_PermissionSetAssignmentPolicyTombstoneReceipt"

	AssignmentPolicyStoreRoleClaim            = "claim"
	AssignmentPolicyStoreRoleProfileSelector  = "profile_selector"
	AssignmentPolicyStoreRoleReleaseReceipt   = "release_receipt"
	AssignmentPolicyStoreRoleTargetState      = "target_state"
	AssignmentPolicyStoreRoleTombstoneReceipt = "tombstone_receipt"
)

// AssignmentPolicyStore identifies one durable policy-owned store.
type AssignmentPolicyStore struct {
	Role      string   `json:"role"`
	TableName string   `json:"tableName"`
	Columns   []string `json:"columns"`
}

// AssignmentPolicyAccessPath is one catalog index required by exact
// reconciliation, reverse source discovery, or durable work scanning. These
// declarations are also warm-upgrade inputs: the control plane's additive
// schema reconciler reads ManagedTableDefinitions before physical table create.
type AssignmentPolicyAccessPath struct {
	TableName string   `json:"tableName"`
	IndexName string   `json:"indexName"`
	Columns   []string `json:"columns"`
	Unique    bool     `json:"unique"`
}

// AssignmentPolicyStoreInventory is the immutable schema projection consumed
// by policy registration, readiness, and writer-closure tests.
type AssignmentPolicyStoreInventory struct {
	stores    []AssignmentPolicyStore
	paths     []AssignmentPolicyAccessPath
	canonical []byte
	digest    string
}

type assignmentPolicyCatalogEntry struct {
	TableName                 string `json:"tableName"`
	AssignmentPolicyStoreRole string `json:"assignmentPolicyStoreRole,omitempty"`
}

type canonicalAssignmentPolicyStoreInventory struct {
	SchemaVersion string                       `json:"schemaVersion"`
	Stores        []AssignmentPolicyStore      `json:"stores"`
	AccessPaths   []AssignmentPolicyAccessPath `json:"accessPaths"`
}

var assignmentPolicyRequiredColumns = map[string][]string{
	AssignmentPolicyStoreRoleClaim: {
		"Id", "PermissionSetId", "OwnerPack", "PolicyKey", "PayloadHash",
		"CreatedDate", "LastModifiedDate",
	},
	AssignmentPolicyStoreRoleProfileSelector: {
		"Id", "PermissionSetId", "ProfileName", "CreatedDate", "LastModifiedDate",
	},
	AssignmentPolicyStoreRoleReleaseReceipt: {
		"Id", "OwnerPack", "PolicyKey", "TargetDomain", "PhysicalKey",
		"PermissionSetId", "ReleasedGeneration", "ExactEmptyDigest", "ReleasedAt",
		"CreatedDate", "LastModifiedDate",
	},
	AssignmentPolicyStoreRoleTargetState: {
		"Id", "TargetDomain", "PhysicalKey", "PermissionSetId", "Suspended",
		"SuspensionActor", "SuspensionReason", "SuspendedAt", "WorkGeneration",
		"AppliedGeneration", "NextAttemptAt", "RetryCount", "LastFailureCode",
		"LastFailureDigest", "ResolutionState", "ResolutionDigest", "CreatedDate",
		"LastModifiedDate",
	},
	AssignmentPolicyStoreRoleTombstoneReceipt: {
		"Id", "AssignmentId", "PermissionSetId", "AssigneeId", "OwnerPack",
		"PolicyKey", "TombstonedGeneration", "TombstonedAt", "CreatedDate",
		"LastModifiedDate",
	},
}

// Target state is one row per policy target: the claimed PermissionSet
// Definition aggregate. Eligible assignees fan out through
// PermissionSetAssignment and intentionally share that aggregate state.
var assignmentPolicyRequiredAccessPaths = []AssignmentPolicyAccessPath{
	{TableName: AssignmentPolicyClaimTable, IndexName: "uq_ps_assignment_policy_source", Columns: []string{"OwnerPack", "PolicyKey"}, Unique: true},
	{TableName: AssignmentPolicyClaimTable, IndexName: "uq_ps_assignment_policy_target", Columns: []string{"PermissionSetId"}, Unique: true},
	{TableName: AssignmentPolicyReleaseReceiptTable, IndexName: "idx_ps_assignment_policy_release_target", Columns: []string{"TargetDomain", "PhysicalKey"}},
	{TableName: AssignmentPolicyReleaseReceiptTable, IndexName: "uq_ps_assignment_policy_release_source_target", Columns: []string{"OwnerPack", "PolicyKey", "PermissionSetId"}, Unique: true},
	{TableName: AssignmentPolicySelectorTable, IndexName: "idx_ps_assignment_policy_profile_target", Columns: []string{"ProfileName", "PermissionSetId"}},
	{TableName: AssignmentPolicySelectorTable, IndexName: "uq_ps_assignment_policy_profile", Columns: []string{"PermissionSetId", "ProfileName"}, Unique: true},
	{TableName: AssignmentPolicyTargetStateTable, IndexName: "idx_ps_assignment_policy_due", Columns: []string{"NextAttemptAt", "TargetDomain", "PhysicalKey"}},
	{TableName: AssignmentPolicyTargetStateTable, IndexName: "idx_ps_assignment_policy_suspended", Columns: []string{"Suspended", "TargetDomain", "PhysicalKey"}},
	{TableName: AssignmentPolicyTargetStateTable, IndexName: "uq_ps_assignment_policy_definition", Columns: []string{"PermissionSetId"}, Unique: true},
	{TableName: AssignmentPolicyTargetStateTable, IndexName: "uq_ps_assignment_policy_physical_target", Columns: []string{"TargetDomain", "PhysicalKey"}, Unique: true},
	{TableName: AssignmentPolicyTombstoneReceiptTable, IndexName: "idx_ps_assignment_policy_tombstone_policy", Columns: []string{"OwnerPack", "PolicyKey"}},
	{TableName: AssignmentPolicyTombstoneReceiptTable, IndexName: "uq_ps_assignment_policy_tombstone_assignment", Columns: []string{"AssignmentId"}, Unique: true},
	{TableName: AssignmentPolicyTombstoneReceiptTable, IndexName: "uq_ps_assignment_policy_tombstone_pair", Columns: []string{"PermissionSetId", "AssigneeId"}, Unique: true},
	{TableName: "PermissionSetAssignment", IndexName: "idx_psa_assignee_permset", Columns: []string{"AssigneeId", "PermissionSetId"}, Unique: true},
	{TableName: "PermissionSetAssignment", IndexName: "idx_psa_permset_assignee", Columns: []string{"PermissionSetId", "AssigneeId"}},
	{TableName: "User", IndexName: "idx_user_assignment_policy_eligibility", Columns: []string{"ProfileId", "IsActive", "IsDeleted", "Id"}},
}

// ManagedAssignmentPolicyStoreInventory validates the platform-owned catalog
// and returns its canonical assignment-policy store and index projection.
func ManagedAssignmentPolicyStoreInventory() (AssignmentPolicyStoreInventory, error) {
	definitions, err := ManagedTableDefinitions()
	if err != nil {
		return AssignmentPolicyStoreInventory{}, err
	}
	return assignmentPolicyStoreInventoryFromCatalog(systemTablesJSON, definitions)
}

func (inventory AssignmentPolicyStoreInventory) SchemaVersion() string {
	return AssignmentPolicyStoreInventorySchema
}

func (inventory AssignmentPolicyStoreInventory) Stores() []AssignmentPolicyStore {
	return cloneAssignmentPolicyStores(inventory.stores)
}

func (inventory AssignmentPolicyStoreInventory) AccessPaths() []AssignmentPolicyAccessPath {
	return cloneAssignmentPolicyAccessPaths(inventory.paths)
}

func (inventory AssignmentPolicyStoreInventory) CanonicalJSON() []byte {
	return append([]byte(nil), inventory.canonical...)
}

func (inventory AssignmentPolicyStoreInventory) Digest() string {
	return inventory.digest
}

func assignmentPolicyStoreInventoryFromCatalog(raw []byte, definitions []schema.TableDefinition) (AssignmentPolicyStoreInventory, error) {
	var entries []assignmentPolicyCatalogEntry
	if err := json.Unmarshal(raw, &entries); err != nil {
		return AssignmentPolicyStoreInventory{}, fmt.Errorf("assignment policy store inventory: parse managed catalog: %w", err)
	}
	definitionsByName := make(map[string]schema.TableDefinition, len(definitions))
	for _, definition := range definitions {
		definitionsByName[strings.ToLower(definition.TableName)] = definition
	}

	stores := make([]AssignmentPolicyStore, 0, len(assignmentPolicyRequiredColumns))
	seenRoles := make(map[string]struct{}, len(assignmentPolicyRequiredColumns))
	for _, entry := range entries {
		role := strings.TrimSpace(entry.AssignmentPolicyStoreRole)
		if role == "" {
			continue
		}
		if role != entry.AssignmentPolicyStoreRole {
			return AssignmentPolicyStoreInventory{}, fmt.Errorf("assignment policy store inventory: table %s has non-canonical role %q", entry.TableName, entry.AssignmentPolicyStoreRole)
		}
		required, known := assignmentPolicyRequiredColumns[role]
		if !known {
			return AssignmentPolicyStoreInventory{}, fmt.Errorf("assignment policy store inventory: table %s has unknown role %q", entry.TableName, role)
		}
		if _, duplicate := seenRoles[role]; duplicate {
			return AssignmentPolicyStoreInventory{}, fmt.Errorf("assignment policy store inventory: duplicate role %q", role)
		}
		seenRoles[role] = struct{}{}
		definition, ok := definitionsByName[strings.ToLower(entry.TableName)]
		if !ok || definition.TableName != entry.TableName {
			return AssignmentPolicyStoreInventory{}, fmt.Errorf("assignment policy store inventory: table %q is not an exact managed table", entry.TableName)
		}
		columns, err := exactAssignmentPolicyColumns(definition, required)
		if err != nil {
			return AssignmentPolicyStoreInventory{}, err
		}
		if role != AssignmentPolicyStoreRoleClaim && assignmentPolicyDefinitionReference(definition) {
			return AssignmentPolicyStoreInventory{}, fmt.Errorf("assignment policy store inventory: %s must not own a PermissionSet foreign key", definition.TableName)
		}
		stores = append(stores, AssignmentPolicyStore{Role: role, TableName: definition.TableName, Columns: columns})
	}
	if len(seenRoles) != len(assignmentPolicyRequiredColumns) {
		return AssignmentPolicyStoreInventory{}, fmt.Errorf("assignment policy store inventory: roles %v are incomplete", seenRoles)
	}

	definitionInventory, err := ManagedPermissionSetDefinitionInventory()
	if err != nil {
		return AssignmentPolicyStoreInventory{}, err
	}
	claimRelation, ok := definitionInventory.RelationForTable(AssignmentPolicyClaimTable)
	if !ok || claimRelation.Role != PermissionSetDefinitionRoleNegativeEdge || claimRelation.ParentField != "PermissionSetId" {
		return AssignmentPolicyStoreInventory{}, fmt.Errorf("assignment policy store inventory: claim is not a PermissionSet negative edge")
	}

	paths := cloneAssignmentPolicyAccessPaths(assignmentPolicyRequiredAccessPaths)
	for _, path := range paths {
		definition, ok := definitionsByName[strings.ToLower(path.TableName)]
		if !ok || !assignmentPolicyHasIndex(definition, path) {
			return AssignmentPolicyStoreInventory{}, fmt.Errorf("assignment policy store inventory: missing access path %s.%s(%s)", path.TableName, path.IndexName, strings.Join(path.Columns, ","))
		}
	}
	sort.Slice(stores, func(i, j int) bool { return stores[i].Role < stores[j].Role })
	sort.Slice(paths, func(i, j int) bool {
		if paths[i].TableName == paths[j].TableName {
			return paths[i].IndexName < paths[j].IndexName
		}
		return paths[i].TableName < paths[j].TableName
	})
	canonical, err := json.Marshal(canonicalAssignmentPolicyStoreInventory{
		SchemaVersion: AssignmentPolicyStoreInventorySchema,
		Stores:        stores,
		AccessPaths:   paths,
	})
	if err != nil {
		return AssignmentPolicyStoreInventory{}, fmt.Errorf("assignment policy store inventory: encode canonical projection: %w", err)
	}
	sum := sha256.Sum256(canonical)
	return AssignmentPolicyStoreInventory{
		stores: cloneAssignmentPolicyStores(stores), paths: cloneAssignmentPolicyAccessPaths(paths),
		canonical: append([]byte(nil), canonical...), digest: "sha256:" + hex.EncodeToString(sum[:]),
	}, nil
}

func exactAssignmentPolicyColumns(definition schema.TableDefinition, required []string) ([]string, error) {
	actual := make(map[string]string, len(definition.Columns))
	for _, column := range definition.Columns {
		actual[strings.ToLower(column.Name)] = column.Name
	}
	out := make([]string, len(required))
	for index, name := range required {
		column, ok := actual[strings.ToLower(name)]
		if !ok || column != name {
			return nil, fmt.Errorf("assignment policy store inventory: table %s is missing exact column %s", definition.TableName, name)
		}
		out[index] = column
	}
	sort.Strings(out)
	return out, nil
}

func assignmentPolicyDefinitionReference(definition schema.TableDefinition) bool {
	for _, foreignKey := range definition.ForeignKeys {
		if strings.EqualFold(strings.TrimSpace(foreignKey.References), "PermissionSet(Id)") {
			return true
		}
	}
	for _, column := range definition.Columns {
		for _, target := range column.ReferenceTo {
			if strings.EqualFold(strings.TrimSpace(target), "PermissionSet") {
				return true
			}
		}
	}
	return false
}

func assignmentPolicyHasIndex(definition schema.TableDefinition, required AssignmentPolicyAccessPath) bool {
	for _, index := range definition.Indices {
		if index.Name != required.IndexName || index.Unique != required.Unique || len(index.Columns) != len(required.Columns) {
			continue
		}
		matches := true
		for position := range required.Columns {
			matches = matches && strings.EqualFold(index.Columns[position], required.Columns[position])
		}
		if matches {
			return true
		}
	}
	return false
}

func cloneAssignmentPolicyStores(input []AssignmentPolicyStore) []AssignmentPolicyStore {
	output := make([]AssignmentPolicyStore, len(input))
	for index, store := range input {
		output[index] = store
		output[index].Columns = append([]string(nil), store.Columns...)
	}
	return output
}

func cloneAssignmentPolicyAccessPaths(input []AssignmentPolicyAccessPath) []AssignmentPolicyAccessPath {
	output := make([]AssignmentPolicyAccessPath, len(input))
	for index, path := range input {
		output[index] = path
		output[index].Columns = append([]string(nil), path.Columns...)
	}
	return output
}