package packs

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

	"github.com/rs/zerolog/log"
)

var permissionSetPermissionColumnRe = regexp.MustCompile(`^Permissions[A-Za-z0-9]+$`)

// BackfillPermissionSetPermissionSSoTFromBooleanColumns populates missing rows in
// _System_PermissionSetPermission (SSoT) from pre-existing boolean columns on PermissionSet.
//
// Scope: profile-owned PermissionSets only (IsOwnedByProfile=1, IsDeleted=0).
// Safety:
// - Insert-only: uses INSERT IGNORE so existing rows (including IsEnabled=0) are not overridden.
// - Catalog-gated: only permissions present in _System_Permission (IsActive=1) are considered.
// - Column-gated: only PermissionSet columns that physically exist in the current DB schema are read.
//
// This is intended for "SF profile migration gap" repairs where the PermissionSet bitmap columns
// were loaded (e.g. via Fivetran) but the SSoT junction table was never populated.
func BackfillPermissionSetPermissionSSoTFromBooleanColumns(ctx context.Context, db *sql.DB) error {
	start := time.Now()

	var schemaName string
	if err := db.QueryRowContext(ctx, "SELECT DATABASE()").Scan(&schemaName); err != nil {
		return fmt.Errorf("resolve current schema: %w", err)
	}
	if schemaName == "" {
		return fmt.Errorf("resolve current schema: empty schema name")
	}

	// 1) Load active permissions from catalog.
	activePerms := make(map[string]struct{})
	{
		rows, err := db.QueryContext(ctx, "SELECT `ApiName` FROM `_System_Permission` WHERE `IsActive` = 1")
		if err != nil {
			return fmt.Errorf("query active permissions: %w", err)
		}
		defer rows.Close()

		for rows.Next() {
			var apiName string
			if err := rows.Scan(&apiName); err != nil {
				return fmt.Errorf("scan active permission: %w", err)
			}
			apiName = strings.TrimSpace(apiName)
			if apiName == "" {
				continue
			}
			activePerms[apiName] = struct{}{}
		}
		if err := rows.Err(); err != nil {
			return fmt.Errorf("iterate active permissions: %w", err)
		}
	}

	// 2) Discover PermissionSet boolean permission columns (PermissionsX) that exist in this schema.
	type mapping struct {
		column string
		api    string
	}
	var mappings []mapping
	{
		const q = `
SELECT COLUMN_NAME
FROM information_schema.columns
WHERE table_schema = ?
  AND table_name = 'PermissionSet'
  AND COLUMN_NAME LIKE 'Permissions%'
ORDER BY ORDINAL_POSITION`

		rows, err := db.QueryContext(ctx, q, schemaName)
		if err != nil {
			return fmt.Errorf("query PermissionSet columns: %w", err)
		}
		defer rows.Close()

		for rows.Next() {
			var col string
			if err := rows.Scan(&col); err != nil {
				return fmt.Errorf("scan PermissionSet column: %w", err)
			}
			col = strings.TrimSpace(col)
			if col == "" || !permissionSetPermissionColumnRe.MatchString(col) {
				continue
			}

			apiName := strings.TrimPrefix(col, "Permissions")
			if apiName == "" {
				continue
			}
			if _, ok := activePerms[apiName]; !ok {
				continue
			}
			mappings = append(mappings, mapping{column: col, api: apiName})
		}
		if err := rows.Err(); err != nil {
			return fmt.Errorf("iterate PermissionSet columns: %w", err)
		}
	}

	if len(mappings) == 0 {
		log.Info().Msg("🔧 Permission SSoT backfill: no (Permissions*) columns intersect with active permission catalog; skipping")
		return nil
	}

	// 3) Insert missing SSoT rows for each (PermissionSet, PermissionApiName) where the boolean column is true.
	// Insert-only: do not override existing rows (including explicit disables).
	totalInserted := int64(0)
	for _, m := range mappings {
		sqlStr := fmt.Sprintf(
			"INSERT IGNORE INTO `_System_PermissionSetPermission` (`Id`, `PermissionSetId`, `PermissionApiName`, `IsEnabled`) "+
				"SELECT UUID(), ps.`Id`, ?, 1 FROM `PermissionSet` ps "+
				"WHERE ps.`IsOwnedByProfile` = 1 AND ps.`IsDeleted` = 0 AND ps.`%s` = 1",
			m.column,
		)
		res, err := db.ExecContext(ctx, sqlStr, m.api)
		if err != nil {
			return fmt.Errorf("backfill ssot from %s (%s): %w", m.column, m.api, err)
		}
		if n, _ := res.RowsAffected(); n > 0 {
			totalInserted += n
		}
	}

	log.Info().
		Str("schema", schemaName).
		Int("columns_considered", len(mappings)).
		Int64("rows_inserted", totalInserted).
		Dur("elapsed", time.Since(start)).
		Msg("✅ Permission SSoT backfill from PermissionSet boolean columns complete")
	return nil
}