package packs

import (
	"bytes"
	"context"
	"database/sql"
	"encoding/json"
	"fmt"
	"sort"
	"strings"

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

const sourceBackedSystemPermissionGrantProfileOwned = "profile_owned"

type sourceBackedSystemPermissionGrantsPayload struct {
	PermissionAPIName           string                                    `json:"permissionApiName"`
	GrantTo                     []sourceBackedSystemPermissionGrantTarget `json:"grantTo"`
	ExpectedPermissionOwnerPack string                                    `json:"expectedPermissionOwnerPack,omitempty"`
	Reason                      string                                    `json:"reason,omitempty"`
}

type sourceBackedSystemPermissionGrantTarget struct {
	ProfileName   string `json:"profileName"`
	PermissionSet string `json:"permissionSet"`
}

type sourceBackedSystemPermissionGrantDeclaration struct {
	PermissionAPIName           string `json:"permissionApiName"`
	ProfileName                 string `json:"profileName"`
	PermissionSet               string `json:"permissionSet"`
	ExpectedPermissionOwnerPack string `json:"expectedPermissionOwnerPack,omitempty"`
	Reason                      string `json:"reason,omitempty"`
}

type sourceBackedSystemPermissionGrantResolved struct {
	PermissionAPIName string `json:"permissionApiName"`
	ProfileName       string `json:"profileName"`
	PermissionSet     string `json:"permissionSet"`
	ProfileID         string `json:"profileId"`
	PermissionSetID   string `json:"permissionSetId"`
}

type systemPermissionGrantPackBatch struct {
	seen map[string]string
}

func newSourceBackedSystemPermissionGrantsComponent(packName string, component ManifestComponent, payload []byte) (PackComponent, []sourceBackedSystemPermissionGrantDeclaration, error) {
	packName = strings.TrimSpace(packName)
	if packName == "" {
		return nil, nil, fmt.Errorf("system_permission_grants %s: packName is required", componentPayloadLabel(component))
	}
	declarations, err := sourceBackedSystemPermissionGrantDeclarations(payload, componentPayloadLabel(component))
	if err != nil {
		return nil, nil, err
	}
	businessKey := strings.TrimSpace(component.BusinessKey)
	if businessKey != "" && businessKey != declarations[0].PermissionAPIName {
		return nil, nil, fmt.Errorf("system_permission_grants %s: businessKey %q must match permissionApiName %q",
			componentPayloadLabel(component), businessKey, declarations[0].PermissionAPIName)
	}
	managed := &sourceBackedManagedComponent{
		packName:          packName,
		manifestComponent: component,
		payload:           append([]byte(nil), payload...),
		expectedProjection: func() (any, error) {
			return append([]sourceBackedSystemPermissionGrantDeclaration(nil), declarations...), nil
		},
		liveProjection: func(ctx context.Context, deps VerifyDeps) (any, error) {
			return liveSourceBackedSystemPermissionGrantProjection(ctx, deps.DB, declarations)
		},
		apply: func(ctx context.Context, deps ApplyDeps) error {
			return applySourceBackedSystemPermissionGrants(ctx, deps.DB, declarations)
		},
	}
	managed.verify = func(ctx context.Context, deps VerifyDeps) error {
		return verifySourceBackedSystemPermissionGrants(ctx, deps.DB, declarations)
	}
	return managed, declarations, nil
}

func sourceBackedSystemPermissionGrantDeclarations(payload []byte, source string) ([]sourceBackedSystemPermissionGrantDeclaration, error) {
	var data sourceBackedSystemPermissionGrantsPayload
	dec := json.NewDecoder(bytes.NewReader(payload))
	dec.DisallowUnknownFields()
	if err := dec.Decode(&data); err != nil {
		return nil, fmt.Errorf("parse %s: %w", source, err)
	}
	if err := ensureJSONEOF(dec); err != nil {
		return nil, fmt.Errorf("parse %s: %w", source, err)
	}

	permissionAPIName := strings.TrimSpace(data.PermissionAPIName)
	if permissionAPIName == "" {
		return nil, fmt.Errorf("system_permission_grants %s: permissionApiName is required", source)
	}
	expectedOwnerPack := strings.TrimSpace(data.ExpectedPermissionOwnerPack)
	reason := strings.TrimSpace(data.Reason)
	if len(data.GrantTo) == 0 {
		return nil, fmt.Errorf("system_permission_grants %s: grantTo must declare at least one target", source)
	}

	seen := map[string]int{}
	out := make([]sourceBackedSystemPermissionGrantDeclaration, 0, len(data.GrantTo))
	for i, target := range data.GrantTo {
		profileName := strings.TrimSpace(target.ProfileName)
		if profileName == "" {
			return nil, fmt.Errorf("system_permission_grants %s grantTo[%d]: profileName is required", source, i)
		}
		permissionSet := strings.ToLower(strings.TrimSpace(target.PermissionSet))
		if permissionSet == "" {
			return nil, fmt.Errorf("system_permission_grants %s grantTo[%d]: permissionSet is required", source, i)
		}
		if permissionSet != sourceBackedSystemPermissionGrantProfileOwned {
			return nil, fmt.Errorf("system_permission_grants %s grantTo[%d]: unsupported permissionSet %q", source, i, strings.TrimSpace(target.PermissionSet))
		}
		declaration := sourceBackedSystemPermissionGrantDeclaration{
			PermissionAPIName:           permissionAPIName,
			ProfileName:                 profileName,
			PermissionSet:               permissionSet,
			ExpectedPermissionOwnerPack: expectedOwnerPack,
			Reason:                      reason,
		}
		key := declaration.normalizedKey()
		if prev, ok := seen[key]; ok {
			return nil, fmt.Errorf("system_permission_grants %s grantTo[%d]: duplicate assertion (%s, %s, %s) duplicates grantTo[%d]",
				source, i, permissionAPIName, profileName, permissionSet, prev)
		}
		seen[key] = i
		out = append(out, declaration)
	}
	sortSystemPermissionGrantDeclarations(out)
	return out, nil
}

func (b *systemPermissionGrantPackBatch) add(component ManifestComponent, declarations []sourceBackedSystemPermissionGrantDeclaration) error {
	if b == nil {
		return nil
	}
	if b.seen == nil {
		b.seen = map[string]string{}
	}
	label := strings.TrimSpace(component.BusinessKey)
	for _, declaration := range declarations {
		key := declaration.normalizedKey()
		if prev, ok := b.seen[key]; ok {
			return fmt.Errorf("system_permission_grants %s: duplicate assertion (%s, %s, %s) already declared by %s",
				label,
				declaration.PermissionAPIName,
				declaration.ProfileName,
				declaration.PermissionSet,
				prev,
			)
		}
		b.seen[key] = label
	}
	return nil
}

func (d sourceBackedSystemPermissionGrantDeclaration) normalizedKey() string {
	return strings.ToLower(strings.TrimSpace(d.PermissionAPIName)) + "\x00" +
		strings.ToLower(strings.TrimSpace(d.ProfileName)) + "\x00" +
		strings.ToLower(strings.TrimSpace(d.PermissionSet))
}

func sortSystemPermissionGrantDeclarations(rows []sourceBackedSystemPermissionGrantDeclaration) {
	sort.Slice(rows, func(i, j int) bool {
		if rows[i].PermissionAPIName != rows[j].PermissionAPIName {
			return rows[i].PermissionAPIName < rows[j].PermissionAPIName
		}
		if rows[i].ProfileName != rows[j].ProfileName {
			return rows[i].ProfileName < rows[j].ProfileName
		}
		return rows[i].PermissionSet < rows[j].PermissionSet
	})
}

func liveSourceBackedSystemPermissionGrantProjection(ctx context.Context, db *sql.DB, declarations []sourceBackedSystemPermissionGrantDeclaration) ([]sourceBackedSystemPermissionGrantResolved, error) {
	resolved, err := resolveSourceBackedSystemPermissionGrantDeclarations(ctx, db, declarations)
	if err != nil {
		return nil, err
	}
	live := make([]sourceBackedSystemPermissionGrantResolved, 0, len(resolved))
	for _, row := range resolved {
		exists, _, err := sourceBackedSystemPermissionGrantJunctionState(ctx, db, row.PermissionSetID, row.PermissionAPIName)
		if err != nil {
			return nil, err
		}
		if !exists {
			return nil, fmt.Errorf("system_permission_grants permission %s profile %s grant is missing", row.PermissionAPIName, row.ProfileName)
		}
		live = append(live, row)
	}
	return live, nil
}

func applySourceBackedSystemPermissionGrants(ctx context.Context, db *sql.DB, declarations []sourceBackedSystemPermissionGrantDeclaration) error {
	resolved, err := resolveSourceBackedSystemPermissionGrantDeclarations(ctx, db, declarations)
	if err != nil {
		return err
	}
	type missingGrant struct {
		permissionSetID   string
		permissionAPIName string
	}
	var missing []missingGrant
	for _, row := range resolved {
		exists, _, err := sourceBackedSystemPermissionGrantJunctionState(ctx, db, row.PermissionSetID, row.PermissionAPIName)
		if err != nil {
			return err
		}
		if exists {
			continue
		}
		missing = append(missing, missingGrant{
			permissionSetID:   row.PermissionSetID,
			permissionAPIName: row.PermissionAPIName,
		})
	}
	if len(missing) == 0 {
		return nil
	}

	valueParts := make([]string, 0, len(missing))
	args := make([]any, 0, len(missing)*3)
	for _, row := range missing {
		valueParts = append(valueParts, "(?, ?, ?, 1)")
		args = append(args, utils.GenerateID(), row.permissionSetID, row.permissionAPIName)
	}
	sqlStr := fmt.Sprintf(
		"INSERT IGNORE INTO `%s` (`%s`, `%s`, `%s`, `%s`) VALUES %s",
		constants.TablePermissionSetPermission,
		constants.FieldID,
		constants.FieldSysPermissionSetPermission_PermissionSetID,
		constants.FieldSysPermissionSetPermission_PermissionAPIName,
		constants.FieldSysPermissionSetPermission_IsEnabled,
		strings.Join(valueParts, ", "),
	)
	if _, err := db.ExecContext(ctx, sqlStr, args...); err != nil {
		return fmt.Errorf("insert system permission grants: %w", err)
	}
	return nil
}

func verifySourceBackedSystemPermissionGrants(ctx context.Context, db *sql.DB, declarations []sourceBackedSystemPermissionGrantDeclaration) error {
	resolved, err := resolveSourceBackedSystemPermissionGrantDeclarations(ctx, db, declarations)
	if err != nil {
		return err
	}
	for _, row := range resolved {
		exists, _, err := sourceBackedSystemPermissionGrantJunctionState(ctx, db, row.PermissionSetID, row.PermissionAPIName)
		if err != nil {
			return err
		}
		if !exists {
			return fmt.Errorf("system_permission_grants permission %s profile %s grant is missing", row.PermissionAPIName, row.ProfileName)
		}
	}
	return nil
}

func resolveSourceBackedSystemPermissionGrantDeclarations(ctx context.Context, db *sql.DB, declarations []sourceBackedSystemPermissionGrantDeclaration) ([]sourceBackedSystemPermissionGrantResolved, error) {
	if db == nil {
		return nil, fmt.Errorf("database is required")
	}
	checkedPermissions := map[string]struct{}{}
	out := make([]sourceBackedSystemPermissionGrantResolved, 0, len(declarations))
	for _, declaration := range declarations {
		permissionKey := declaration.PermissionAPIName + "\x00" + declaration.ExpectedPermissionOwnerPack
		if _, ok := checkedPermissions[permissionKey]; !ok {
			if err := verifySourceBackedSystemPermissionGrantCatalog(ctx, db, declaration); err != nil {
				return nil, err
			}
			checkedPermissions[permissionKey] = struct{}{}
		}
		profileID, permissionSetID, err := resolveProfileOwnedPermissionSet(ctx, db, declaration)
		if err != nil {
			return nil, err
		}
		out = append(out, sourceBackedSystemPermissionGrantResolved{
			PermissionAPIName: declaration.PermissionAPIName,
			ProfileName:       declaration.ProfileName,
			PermissionSet:     declaration.PermissionSet,
			ProfileID:         profileID,
			PermissionSetID:   permissionSetID,
		})
	}
	sort.Slice(out, func(i, j int) bool {
		if out[i].PermissionAPIName != out[j].PermissionAPIName {
			return out[i].PermissionAPIName < out[j].PermissionAPIName
		}
		if out[i].ProfileName != out[j].ProfileName {
			return out[i].ProfileName < out[j].ProfileName
		}
		return out[i].PermissionSetID < out[j].PermissionSetID
	})
	return out, nil
}

func verifySourceBackedSystemPermissionGrantCatalog(ctx context.Context, db *sql.DB, declaration sourceBackedSystemPermissionGrantDeclaration) error {
	var apiName, ownerPack, requiresPermission string
	var isActive int
	err := db.QueryRowContext(ctx, `
SELECT ApiName, COALESCE(OwnerPack, ''), IsActive, COALESCE(RequiresPermission, '')
FROM _System_Permission
WHERE ApiName = ?`, declaration.PermissionAPIName).Scan(&apiName, &ownerPack, &isActive, &requiresPermission)
	if err == sql.ErrNoRows {
		return fmt.Errorf("system_permission_grants permission %s is missing", declaration.PermissionAPIName)
	}
	if err != nil {
		return fmt.Errorf("query system permission %s: %w", declaration.PermissionAPIName, err)
	}
	if apiName != declaration.PermissionAPIName {
		return fmt.Errorf("system_permission_grants permission %s matched ApiName = %q, want exact %q", declaration.PermissionAPIName, apiName, declaration.PermissionAPIName)
	}
	if isActive != 1 {
		return fmt.Errorf("system_permission_grants permission %s IsActive = %d, want 1", declaration.PermissionAPIName, isActive)
	}
	expectedOwnerPack := strings.TrimSpace(declaration.ExpectedPermissionOwnerPack)
	if expectedOwnerPack != "" && strings.TrimSpace(ownerPack) != expectedOwnerPack {
		return fmt.Errorf("system_permission_grants permission %s OwnerPack = %q, want %q", declaration.PermissionAPIName, strings.TrimSpace(ownerPack), expectedOwnerPack)
	}
	requiresPermission = strings.TrimSpace(requiresPermission)
	if requiresPermission != "" {
		return fmt.Errorf("system_permission_grants permission %s RequiresPermission = %q; dependent permissions are not grantable by system_permission_grants v1", declaration.PermissionAPIName, requiresPermission)
	}
	return nil
}

func resolveProfileOwnedPermissionSet(ctx context.Context, db *sql.DB, declaration sourceBackedSystemPermissionGrantDeclaration) (string, string, error) {
	rows, err := db.QueryContext(ctx, `
SELECT p.Id, p.Name, ps.Id
FROM Profile p
JOIN PermissionSet ps ON ps.ProfileId = p.Id
WHERE p.Name = ?
  AND COALESCE(p.IsActive, 0) = 1
  AND COALESCE(p.IsDeleted, 0) = 0
  AND COALESCE(ps.IsOwnedByProfile, 0) = 1
  AND COALESCE(ps.IsActive, 0) = 1
  AND COALESCE(ps.IsDeleted, 0) = 0
ORDER BY p.Id, ps.Id`, declaration.ProfileName)
	if err != nil {
		return "", "", fmt.Errorf("query profile-owned PermissionSet for profile %s: %w", declaration.ProfileName, err)
	}
	defer rows.Close()

	type candidate struct {
		profileID       string
		profileName     string
		permissionSetID string
	}
	var candidates []candidate
	for rows.Next() {
		var row candidate
		if err := rows.Scan(&row.profileID, &row.profileName, &row.permissionSetID); err != nil {
			return "", "", fmt.Errorf("scan profile-owned PermissionSet for profile %s: %w", declaration.ProfileName, err)
		}
		row.profileID = strings.TrimSpace(row.profileID)
		row.permissionSetID = strings.TrimSpace(row.permissionSetID)
		if row.profileName != declaration.ProfileName {
			return "", "", fmt.Errorf("system_permission_grants permission %s profile %s matched Profile.Name = %q, want exact %q",
				declaration.PermissionAPIName, declaration.ProfileName, row.profileName, declaration.ProfileName)
		}
		candidates = append(candidates, row)
	}
	if err := rows.Err(); err != nil {
		return "", "", fmt.Errorf("iterate profile-owned PermissionSet for profile %s: %w", declaration.ProfileName, err)
	}
	if len(candidates) != 1 {
		return "", "", fmt.Errorf("system_permission_grants permission %s profile %s resolves to %d active profile-owned PermissionSet rows, want exactly 1",
			declaration.PermissionAPIName, declaration.ProfileName, len(candidates))
	}
	return candidates[0].profileID, candidates[0].permissionSetID, nil
}

func sourceBackedSystemPermissionGrantJunctionState(ctx context.Context, db *sql.DB, permissionSetID, permissionAPIName string) (bool, bool, error) {
	rows, err := db.QueryContext(ctx, `
SELECT PermissionApiName, IsEnabled
FROM _System_PermissionSetPermission
WHERE PermissionSetId = ? AND PermissionApiName = ?`, permissionSetID, permissionAPIName)
	if err != nil {
		return false, false, fmt.Errorf("query system permission grant %s/%s: %w", permissionSetID, permissionAPIName, err)
	}
	defer rows.Close()

	count := 0
	enabled := false
	for rows.Next() {
		var storedPermissionAPIName string
		var isEnabled int
		if err := rows.Scan(&storedPermissionAPIName, &isEnabled); err != nil {
			return false, false, fmt.Errorf("scan system permission grant %s/%s: %w", permissionSetID, permissionAPIName, err)
		}
		if storedPermissionAPIName != permissionAPIName {
			return false, false, fmt.Errorf("system_permission_grants permission %s PermissionSet %s matched PermissionApiName = %q, want exact %q",
				permissionAPIName, permissionSetID, storedPermissionAPIName, permissionAPIName)
		}
		count++
		if isEnabled == 1 {
			enabled = true
		}
	}
	if err := rows.Err(); err != nil {
		return false, false, fmt.Errorf("iterate system permission grant %s/%s: %w", permissionSetID, permissionAPIName, err)
	}
	if count > 1 {
		return false, false, fmt.Errorf("system_permission_grants permission %s PermissionSet %s has %d junction rows, want at most 1", permissionAPIName, permissionSetID, count)
	}
	if count == 0 {
		return false, false, nil
	}
	return true, enabled, nil
}