package packs

import (
	"context"
	"database/sql"
	"fmt"
	"sort"
	"strings"

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

type objectRecordMutationPolicyPackBatch struct {
	declaredObjects []string
}

func newObjectRecordMutationPolicyPackSweeperComponent(packName string, batch *objectRecordMutationPolicyPackBatch) PackComponent {
	manifestComponent := ManifestComponent{
		Kind:          ComponentKindObjectRecordMutationPolicy,
		BusinessKey:   JoinComponentBusinessKey(packName, "@record-mutation-policy-sweeper"),
		Manageability: ManageabilityLocked,
	}
	return &sourceBackedManagedComponent{
		packName:          packName,
		manifestComponent: manifestComponent,
		apply: func(ctx context.Context, deps ApplyDeps) error {
			return applyObjectRecordMutationPolicyPackSweep(ctx, deps.DB, packName, batch)
		},
		verify: func(ctx context.Context, deps VerifyDeps) error {
			return verifyObjectRecordMutationPolicyPackSweep(ctx, deps.DB, packName, batch)
		},
	}
}

func applyObjectRecordMutationPolicyPackSweep(ctx context.Context, db *sql.DB, packName string, batch *objectRecordMutationPolicyPackBatch) error {
	orphans, err := queryObjectRecordMutationPolicyOrphans(ctx, db, packName, batch)
	if err != nil {
		return err
	}
	for _, row := range orphans {
		policy, err := decodeObjectAccessPolicy(row.accessPolicy, row.objectAPIName)
		if err != nil {
			return err
		}
		policy.RecordMutation = nil
		accessPolicyJSON, err := encodeObjectAccessPolicy(policy)
		if err != nil {
			return fmt.Errorf("marshal swept object recordMutation policy for %s: %w", row.objectAPIName, err)
		}
		result, err := db.ExecContext(ctx,
			fmt.Sprintf(
				"UPDATE `%s` SET `%s` = CAST(? AS JSON), `%s` = NULL, `%s` = NULL, `%s` = NULL, `%s` = NULL, `%s` = NOW() "+
					"WHERE `%s` = ? AND COALESCE(`%s`, 0) = 0 AND `%s` = ?",
				constants.TableObject,
				constants.FieldSysObject_AccessPolicy,
				constants.FieldSysObject_AccessPolicyRecordMutationPackName,
				constants.FieldSysObject_AccessPolicyRecordMutationPackBusinessKey,
				constants.FieldSysObject_AccessPolicyRecordMutationPackManageability,
				constants.FieldSysObject_AccessPolicyRecordMutationPackValue,
				constants.FieldLastModifiedDate,
				constants.FieldID,
				constants.FieldIsDeleted,
				constants.FieldSysObject_AccessPolicyRecordMutationPackName,
			),
			string(accessPolicyJSON),
			row.id,
			strings.TrimSpace(packName),
		)
		if err != nil {
			return fmt.Errorf("sweep undeclared object_record_mutation_policy for %s in pack %s: %w", row.objectAPIName, packName, err)
		}
		affected, err := result.RowsAffected()
		if err != nil {
			return fmt.Errorf("sweep undeclared object_record_mutation_policy for %s in pack %s: rows affected: %w", row.objectAPIName, packName, err)
		}
		if affected != 1 {
			return fmt.Errorf("sweep undeclared object_record_mutation_policy for %s in pack %s affected %d rows; expected exactly one owned object row", row.objectAPIName, packName, affected)
		}
	}
	return nil
}

func verifyObjectRecordMutationPolicyPackSweep(ctx context.Context, db *sql.DB, packName string, batch *objectRecordMutationPolicyPackBatch) error {
	orphans, err := queryObjectRecordMutationPolicyOrphans(ctx, db, packName, batch)
	if err != nil {
		return err
	}
	if len(orphans) == 0 {
		return nil
	}
	names := make([]string, 0, len(orphans))
	for _, row := range orphans {
		names = append(names, row.objectAPIName)
	}
	sort.Strings(names)
	return fmt.Errorf("pack %s has %d undeclared object_record_mutation_policy value(s) still live: %s",
		packName, len(names), strings.Join(names, ", "))
}

type objectRecordMutationPolicyOrphanRow struct {
	id            string
	objectAPIName string
	accessPolicy  sql.NullString
}

func queryObjectRecordMutationPolicyOrphans(ctx context.Context, db *sql.DB, packName string, batch *objectRecordMutationPolicyPackBatch) ([]objectRecordMutationPolicyOrphanRow, error) {
	if db == nil {
		return nil, fmt.Errorf("database is required")
	}
	declared := map[string]struct{}{}
	if batch != nil {
		for _, objectAPIName := range batch.declaredObjects {
			key := strings.ToLower(strings.TrimSpace(objectAPIName))
			if key != "" {
				declared[key] = struct{}{}
			}
		}
	}
	rows, err := db.QueryContext(ctx,
		fmt.Sprintf(
			"SELECT `%s`, `%s`, `%s` FROM `%s` "+
				"WHERE `%s` = ? AND COALESCE(`%s`, 0) = 0 ORDER BY `%s`",
			constants.FieldID,
			constants.FieldSysObject_APIName,
			constants.FieldSysObject_AccessPolicy,
			constants.TableObject,
			constants.FieldSysObject_AccessPolicyRecordMutationPackName,
			constants.FieldIsDeleted,
			constants.FieldSysObject_APIName,
		),
		strings.TrimSpace(packName),
	)
	if err != nil {
		return nil, fmt.Errorf("query live object_record_mutation_policy rows for pack sweep %s: %w", packName, err)
	}
	defer rows.Close()

	var out []objectRecordMutationPolicyOrphanRow
	for rows.Next() {
		var row objectRecordMutationPolicyOrphanRow
		if err := rows.Scan(&row.id, &row.objectAPIName, &row.accessPolicy); err != nil {
			return nil, fmt.Errorf("scan object_record_mutation_policy row for pack sweep %s: %w", packName, err)
		}
		if _, ok := declared[strings.ToLower(strings.TrimSpace(row.objectAPIName))]; ok {
			continue
		}
		out = append(out, row)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate object_record_mutation_policy rows for pack sweep %s: %w", packName, err)
	}
	return out, nil
}