package packs

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

	"github.com/nexuscrm/shared/pkg/constants"
	"github.com/rs/zerolog/log"
)

type setupObjectBackfillTarget struct {
	APIName                 string
	RequiredWritePermission string
}

func quotedTable(name string) string {
	return fmt.Sprintf("`%s`", name)
}

func listSetupObjectBackfillTargets(ctx context.Context, db *sql.DB) ([]setupObjectBackfillTarget, error) {
	query := fmt.Sprintf(`
SELECT
	`+"`%s`, `%s`"+`
FROM `+"`%s`"+`
WHERE `+"`%s`"+` LIKE 'system%%'
  AND `+"`%s`"+` IS NOT NULL
  AND `+"`%s`"+` <> ''
  AND `+"`%s`"+` <> 'BLOCKED'
  AND (`+"`%s`"+` IS NULL OR `+"`%s`"+` <> 'BLOCKED')
  AND `+"`%s`"+` = 0
ORDER BY `+"`%s`"+` ASC
`,
		constants.FieldSysObject_APIName,
		constants.FieldSysObject_RequiredWritePermission,
		constants.TableObject,
		constants.FieldSysObject_TableType,
		constants.FieldSysObject_RequiredWritePermission,
		constants.FieldSysObject_RequiredWritePermission,
		constants.FieldSysObject_RequiredWritePermission,
		constants.FieldSysObject_RequiredReadPermission,
		constants.FieldSysObject_RequiredReadPermission,
		constants.FieldSysObject_IsDeleted,
		constants.FieldSysObject_APIName,
	)

	rows, err := db.QueryContext(ctx, query)
	if err != nil {
		return nil, fmt.Errorf("query setup-object backfill targets: %w", err)
	}
	defer rows.Close()

	var targets []setupObjectBackfillTarget
	for rows.Next() {
		var target setupObjectBackfillTarget
		if err := rows.Scan(&target.APIName, &target.RequiredWritePermission); err != nil {
			return nil, fmt.Errorf("scan setup-object backfill target: %w", err)
		}
		target.APIName = strings.TrimSpace(target.APIName)
		target.RequiredWritePermission = strings.TrimSpace(target.RequiredWritePermission)
		if target.APIName == "" || target.RequiredWritePermission == "" {
			continue
		}
		targets = append(targets, target)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate setup-object backfill targets: %w", err)
	}

	return targets, nil
}

// BackfillSetupObjectPermissionsFromSystemPerms inserts missing ObjectPermissions and
// FieldPermissions rows for setup objects whose write access is already governed by
// metadata (`_System_Object.RequiredWritePermission`).
//
// Scope:
// - profile-owned PermissionSets only
// - system tables with a metadata-declared write gate and non-BLOCKED read path
//
// Source of truth:
//   - Read is seeded as allowed for authenticated users (Salesforce read-exempt setup-object model)
//   - Create/Edit/Delete/ViewAll/ModifyAll and field editability are derived from the existing
//     effective system grant row in `_System_PermissionSetPermission`
//
// Safety:
// - insert-only: `INSERT IGNORE` keeps existing OP/FP rows untouched
// - metadata-driven: target objects come from `_System_Object`, not hardcoded object names
// - order-sensitive: should run after `BackfillPermissionSetPermissionSSoT`
func BackfillSetupObjectPermissionsFromSystemPerms(ctx context.Context, db *sql.DB) error {
	start := time.Now()

	targets, err := listSetupObjectBackfillTargets(ctx, db)
	if err != nil {
		return err
	}
	if len(targets) == 0 {
		log.Info().Msg("🔧 Setup-object OP/FP backfill: no metadata-gated setup objects found; skipping")
		return nil
	}

	objectInsertSQL := fmt.Sprintf(`
INSERT IGNORE INTO %s (
	Id, %s, %s, %s, %s, %s, %s, %s, %s
)
SELECT
	UUID(),
	ps.Id,
	?,
	1,
	IF(ssot.PermissionSetId IS NULL, 0, 1),
	IF(ssot.PermissionSetId IS NULL, 0, 1),
	IF(ssot.PermissionSetId IS NULL, 0, 1),
	IF(ssot.PermissionSetId IS NULL, 0, 1),
	IF(ssot.PermissionSetId IS NULL, 0, 1)
FROM %s ps
LEFT JOIN %s ssot
	ON ssot.PermissionSetId = ps.Id
	AND ssot.PermissionApiName = ?
	AND ssot.IsEnabled = 1
WHERE ps.%s = 1
  AND ps.%s = 0
`,
		quotedTable(constants.TableObjectPermissions),
		constants.FieldObjectPermissions_ParentID,
		constants.FieldObjectPermissions_SobjectType,
		constants.FieldObjectPermissions_PermissionsRead,
		constants.FieldObjectPermissions_PermissionsCreate,
		constants.FieldObjectPermissions_PermissionsEdit,
		constants.FieldObjectPermissions_PermissionsDelete,
		constants.FieldObjectPermissions_PermissionsViewAllRecords,
		constants.FieldObjectPermissions_PermissionsModifyAllRecords,
		quotedTable(constants.TablePermissionSet),
		quotedTable(constants.TablePermissionSetPermission),
		constants.FieldPermissionSet_IsOwnedByProfile,
		constants.FieldPermissionSet_IsDeleted,
	)

	fieldInsertSQL := fmt.Sprintf(`
INSERT IGNORE INTO %s (
	Id, %s, %s, %s, %s, %s
)
SELECT
	UUID(),
	ps.Id,
	obj.%s,
	f.%s,
	1,
	IF(ssot.PermissionSetId IS NULL, 0, 1)
FROM %s ps
JOIN %s obj
	ON obj.%s = ?
	AND obj.%s = 0
JOIN %s f
	ON f.%s = obj.Id
	AND f.%s = 0
LEFT JOIN %s ssot
	ON ssot.PermissionSetId = ps.Id
	AND ssot.PermissionApiName = ?
	AND ssot.IsEnabled = 1
WHERE ps.%s = 1
  AND ps.%s = 0
`,
		quotedTable(constants.TableFieldPermissions),
		constants.FieldFieldPermissions_ParentID,
		constants.FieldFieldPermissions_SobjectType,
		constants.FieldFieldPermissions_Field,
		constants.FieldFieldPermissions_PermissionsRead,
		constants.FieldFieldPermissions_PermissionsEdit,
		constants.FieldSysObject_APIName,
		constants.FieldSysField_APIName,
		quotedTable(constants.TablePermissionSet),
		quotedTable(constants.TableObject),
		constants.FieldSysObject_APIName,
		constants.FieldSysObject_IsDeleted,
		quotedTable(constants.TableField),
		constants.FieldSysField_ObjectID,
		constants.FieldSysField_IsDeleted,
		quotedTable(constants.TablePermissionSetPermission),
		constants.FieldPermissionSet_IsOwnedByProfile,
		constants.FieldPermissionSet_IsDeleted,
	)

	var totalObjectRows int64
	var totalFieldRows int64
	for _, target := range targets {
		objRes, err := db.ExecContext(ctx, objectInsertSQL, target.APIName, target.RequiredWritePermission)
		if err != nil {
			return fmt.Errorf("backfill object permissions for %s: %w", target.APIName, err)
		}
		if n, _ := objRes.RowsAffected(); n > 0 {
			totalObjectRows += n
		}

		fieldRes, err := db.ExecContext(ctx, fieldInsertSQL, target.APIName, target.RequiredWritePermission)
		if err != nil {
			return fmt.Errorf("backfill field permissions for %s: %w", target.APIName, err)
		}
		if n, _ := fieldRes.RowsAffected(); n > 0 {
			totalFieldRows += n
		}
	}

	log.Info().
		Int("objects_considered", len(targets)).
		Int64("object_rows_inserted", totalObjectRows).
		Int64("field_rows_inserted", totalFieldRows).
		Dur("elapsed", time.Since(start)).
		Msg("✅ Setup-object OP/FP backfill from system permissions complete")

	return nil
}