package packs

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

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

type ConnectedAppPackSnapshot struct {
	packName     string
	declarations map[string]sourceBackedConnectedAppDeclarationRow
	apps         map[string]models.SObject
}

func SnapshotConnectedAppPackState(ctx context.Context, db *sql.DB, packName string) (*ConnectedAppPackSnapshot, error) {
	if db == nil {
		return nil, fmt.Errorf("connected_app pack snapshot requires database")
	}
	packName = strings.TrimSpace(packName)
	declarations, err := loadSourceBackedConnectedAppDeclarationsForPackIncludingDeleted(ctx, db, packName)
	if err != nil {
		return nil, err
	}
	snapshot := &ConnectedAppPackSnapshot{
		packName:     packName,
		declarations: map[string]sourceBackedConnectedAppDeclarationRow{},
		apps:         map[string]models.SObject{},
	}
	for _, declaration := range declarations {
		key := sourceBackedConnectedAppDeclarationSnapshotKey(declaration)
		if key == "" {
			continue
		}
		snapshot.declarations[key] = declaration
		app, ok, err := loadConnectedAppRecordForSnapshot(ctx, db, declaration.ConnectedAppID)
		if err != nil {
			return nil, err
		}
		if ok {
			snapshot.apps[connectedAppDeclarationKey(declaration.ConnectedAppID)] = app
		}
	}
	return snapshot, nil
}

func (s *ConnectedAppPackSnapshot) Restore(ctx context.Context, deps ApplyDeps) error {
	if s == nil {
		return nil
	}
	if deps.DB == nil {
		return fmt.Errorf("connected_app pack snapshot restore requires database")
	}
	tx, err := deps.DB.BeginTx(ctx, nil)
	if err != nil {
		return fmt.Errorf("begin connected_app pack snapshot restore: %w", err)
	}
	committed := false
	defer func() {
		if !committed {
			_ = tx.Rollback()
		}
	}()

	changedAppIDs := map[string]struct{}{}
	if err := deleteSourceBackedConnectedAppStagesForPack(ctx, tx, s.packName); err != nil {
		return err
	}
	current, err := loadSourceBackedConnectedAppDeclarationsForPackIncludingDeleted(ctx, tx, s.packName)
	if err != nil {
		return err
	}
	currentDeclarations := map[string]sourceBackedConnectedAppDeclarationRow{}
	for _, row := range current {
		if key := sourceBackedConnectedAppDeclarationSnapshotKey(row); key != "" {
			currentDeclarations[key] = row
		}
	}
	for _, row := range current {
		if _, ok := s.declarations[sourceBackedConnectedAppDeclarationSnapshotKey(row)]; ok {
			continue
		}
		appChanged, err := deactivateConnectedAppRecord(ctx, tx, row.ConnectedAppID)
		if err != nil {
			return err
		}
		if appChanged {
			changedAppIDs[connectedAppDeclarationKey(row.ConnectedAppID)] = struct{}{}
		}
		if row.IsDeleted != 0 {
			continue
		}
		row.IsDeleted = 1
		if err := upsertSourceBackedConnectedAppDeclarationExact(ctx, tx, row); err != nil {
			return err
		}
	}

	declarationKeys := make([]string, 0, len(s.declarations))
	for key := range s.declarations {
		declarationKeys = append(declarationKeys, key)
	}
	sort.Strings(declarationKeys)
	for _, key := range declarationKeys {
		row := s.declarations[key]
		if current, ok := currentDeclarations[sourceBackedConnectedAppDeclarationSnapshotKey(row)]; !ok || !sourceBackedConnectedAppDeclarationRowsEqual(current, row) {
			if err := upsertSourceBackedConnectedAppDeclarationExact(ctx, tx, row); err != nil {
				return err
			}
		}
		appKey := connectedAppDeclarationKey(row.ConnectedAppID)
		app, hadApp := s.apps[appKey]
		currentApp, exists, err := loadConnectedAppRecordForSnapshot(ctx, tx, row.ConnectedAppID)
		if err != nil {
			return err
		}
		if hadApp {
			if exists && reflect.DeepEqual(currentApp, app) {
				continue
			}
			if err := writeConnectedAppRecord(ctx, tx, app, exists); err != nil {
				return err
			}
			changedAppIDs[appKey] = struct{}{}
			continue
		}
		if exists {
			appChanged, err := deactivateConnectedAppRecord(ctx, tx, row.ConnectedAppID)
			if err != nil {
				return err
			}
			if appChanged {
				changedAppIDs[appKey] = struct{}{}
			}
		}
	}

	if err := tx.Commit(); err != nil {
		return fmt.Errorf("commit connected_app pack snapshot restore: %w", err)
	}
	committed = true

	appIDs := make([]string, 0, len(changedAppIDs))
	for appID := range changedAppIDs {
		if appID != "" {
			appIDs = append(appIDs, appID)
		}
	}
	sort.Strings(appIDs)
	for _, appID := range appIDs {
		if err := postApplyConnectedAppSecurityChange(ctx, deps, appID); err != nil {
			return err
		}
	}
	return nil
}

func loadSourceBackedConnectedAppDeclarationsForPackIncludingDeleted(ctx context.Context, db connectedAppSQLRunner, packName string) ([]sourceBackedConnectedAppDeclarationRow, error) {
	rows, err := db.QueryContext(ctx, `
SELECT Id, InstalledPackName, ConnectedAppId, PayloadHash, COALESCE(IsDeleted, 0)
  FROM `+sourceBackedConnectedAppDeclarationTable+`
 WHERE InstalledPackName = ?`, strings.TrimSpace(packName))
	if err != nil {
		return nil, fmt.Errorf("query connected_app declarations for snapshot (pack %s): %w", strings.TrimSpace(packName), err)
	}
	defer rows.Close()

	var out []sourceBackedConnectedAppDeclarationRow
	for rows.Next() {
		var row sourceBackedConnectedAppDeclarationRow
		if err := rows.Scan(&row.ID, &row.InstalledPackName, &row.ConnectedAppID, &row.PayloadHash, &row.IsDeleted); err != nil {
			return nil, fmt.Errorf("scan connected_app declaration for snapshot: %w", err)
		}
		out = append(out, row)
	}
	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("iterate connected_app declarations for snapshot: %w", err)
	}
	return out, nil
}

func loadConnectedAppRecordForSnapshot(ctx context.Context, db connectedAppSQLRunner, appID string) (models.SObject, bool, error) {
	var id, name, consumerKey, consumerSecret, consumerSecretHash sql.NullString
	var callbackURL, redirectURIs, scopes, clientType sql.NullString
	var tokenAuth, allowedGrants, consentPolicy sql.NullString
	var publisherName, supportURL, runAsUserID, permittedUsers, sessionIssuerClass sql.NullString
	var isActive, requirePKCE, issueRefreshTokens sql.NullBool
	var consentVersion, dailyLimit, hourlyLimit sql.NullInt64
	err := db.QueryRowContext(ctx, `
SELECT Id, Name, ConsumerKey, ConsumerSecret, ConsumerSecretHash, CallbackUrl, RedirectUris, Scopes, IsActive,
       ClientType, RequirePkce, TokenEndpointAuthMethod, AllowedGrantTypes, ConsentPolicy, ConsentVersion,
       IssueRefreshTokens, PublisherName, SupportUrl, RunAsUserId, PermittedUsers, SessionIssuerClass,
       DailyApiLimit, HourlyApiLimit
  FROM `+constants.TableConnectedApp+`
 WHERE Id = ?`, strings.TrimSpace(appID)).Scan(
		&id,
		&name,
		&consumerKey,
		&consumerSecret,
		&consumerSecretHash,
		&callbackURL,
		&redirectURIs,
		&scopes,
		&isActive,
		&clientType,
		&requirePKCE,
		&tokenAuth,
		&allowedGrants,
		&consentPolicy,
		&consentVersion,
		&issueRefreshTokens,
		&publisherName,
		&supportURL,
		&runAsUserID,
		&permittedUsers,
		&sessionIssuerClass,
		&dailyLimit,
		&hourlyLimit,
	)
	if err == sql.ErrNoRows {
		return nil, false, nil
	}
	if err != nil {
		return nil, false, fmt.Errorf("query _System_ConnectedApp %s for snapshot: %w", strings.TrimSpace(appID), err)
	}
	record := models.SObject{}
	setSnapshotString(record, constants.ColSysConnectedApp_ID, id)
	setSnapshotString(record, constants.ColSysConnectedApp_Name, name)
	setSnapshotString(record, constants.ColSysConnectedApp_ConsumerKey, consumerKey)
	setSnapshotString(record, constants.ColSysConnectedApp_ConsumerSecret, consumerSecret)
	setSnapshotString(record, constants.ColSysConnectedApp_ConsumerSecretHash, consumerSecretHash)
	setSnapshotString(record, constants.ColSysConnectedApp_CallbackURL, callbackURL)
	setSnapshotString(record, constants.ColSysConnectedApp_RedirectURIs, redirectURIs)
	setSnapshotString(record, constants.ColSysConnectedApp_Scopes, scopes)
	setSnapshotBool(record, constants.ColSysConnectedApp_IsActive, isActive)
	setSnapshotString(record, constants.ColSysConnectedApp_ClientType, clientType)
	setSnapshotBool(record, constants.ColSysConnectedApp_RequirePkce, requirePKCE)
	setSnapshotString(record, constants.ColSysConnectedApp_TokenEndpointAuthMethod, tokenAuth)
	setSnapshotString(record, constants.ColSysConnectedApp_AllowedGrantTypes, allowedGrants)
	setSnapshotString(record, constants.ColSysConnectedApp_ConsentPolicy, consentPolicy)
	setSnapshotInt(record, constants.ColSysConnectedApp_ConsentVersion, consentVersion)
	setSnapshotBool(record, constants.ColSysConnectedApp_IssueRefreshTokens, issueRefreshTokens)
	setSnapshotString(record, constants.ColSysConnectedApp_PublisherName, publisherName)
	setSnapshotString(record, constants.ColSysConnectedApp_SupportURL, supportURL)
	setSnapshotString(record, constants.ColSysConnectedApp_RunAsUserID, runAsUserID)
	setSnapshotString(record, constants.ColSysConnectedApp_PermittedUsers, permittedUsers)
	setSnapshotString(record, constants.ColSysConnectedApp_SessionIssuerClass, sessionIssuerClass)
	setSnapshotOptionalInt(record, constants.ColSysConnectedApp_DailyAPILimit, dailyLimit)
	setSnapshotOptionalInt(record, constants.ColSysConnectedApp_HourlyAPILimit, hourlyLimit)
	return record, true, nil
}

func connectedAppRowExists(ctx context.Context, db connectedAppSQLRunner, appID string) (bool, error) {
	var id string
	err := db.QueryRowContext(ctx, `SELECT Id FROM `+constants.TableConnectedApp+` WHERE Id = ?`, strings.TrimSpace(appID)).Scan(&id)
	if err == sql.ErrNoRows {
		return false, nil
	}
	if err != nil {
		return false, fmt.Errorf("query _System_ConnectedApp %s existence: %w", strings.TrimSpace(appID), err)
	}
	return true, nil
}

func deactivateConnectedAppRecord(ctx context.Context, db connectedAppSQLRunner, appID string) (bool, error) {
	appID = strings.TrimSpace(appID)
	if appID == "" {
		return false, nil
	}
	result, err := db.ExecContext(ctx, `
UPDATE `+constants.TableConnectedApp+`
   SET IsActive = 0, LastModifiedDate = CURRENT_TIMESTAMP
 WHERE Id = ? AND COALESCE(IsActive, 0) <> 0`, appID)
	if err != nil {
		return false, fmt.Errorf("deactivate ConnectedApp %s: %w", appID, err)
	}
	if affected, err := result.RowsAffected(); err == nil {
		return affected > 0, nil
	}
	return true, nil
}

func sourceBackedConnectedAppDeclarationSnapshotKey(row sourceBackedConnectedAppDeclarationRow) string {
	id := strings.ToLower(strings.TrimSpace(row.ID))
	if id != "" {
		return id
	}
	return connectedAppDeclarationKey(row.ConnectedAppID)
}

func sourceBackedConnectedAppDeclarationRowsEqual(a, b sourceBackedConnectedAppDeclarationRow) bool {
	return strings.TrimSpace(a.ID) == strings.TrimSpace(b.ID) &&
		strings.TrimSpace(a.InstalledPackName) == strings.TrimSpace(b.InstalledPackName) &&
		strings.TrimSpace(a.ConnectedAppID) == strings.TrimSpace(b.ConnectedAppID) &&
		strings.TrimSpace(a.PayloadHash) == strings.TrimSpace(b.PayloadHash) &&
		a.IsDeleted == b.IsDeleted
}

func setSnapshotString(record models.SObject, field string, value sql.NullString) {
	if value.Valid {
		record[field] = value.String
		return
	}
	record[field] = nil
}

func setSnapshotBool(record models.SObject, field string, value sql.NullBool) {
	if value.Valid {
		record[field] = value.Bool
		return
	}
	record[field] = false
}

func setSnapshotInt(record models.SObject, field string, value sql.NullInt64) {
	if value.Valid {
		record[field] = int(value.Int64)
		return
	}
	record[field] = 0
}

func setSnapshotOptionalInt(record models.SObject, field string, value sql.NullInt64) {
	if value.Valid {
		record[field] = int(value.Int64)
		return
	}
	record[field] = nil
}