package persistence

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

	"github.com/nexuscrm/kernel/pkg/ports"
	"github.com/nexuscrm/shared/pkg/models"
	"github.com/pingcap-inc/nexuscrm-contract/pkg/runtime/permissionstatev1"
)

var _ ports.AuthorizingSessionRepository = (*SessionRepository)(nil)

// LoadAuthorizingSessionState observes the durable session, current user
// subject, Connected App state, and Permission State floor in one SQL
// statement. It does not use the legacy active-user or Connected App caches.
func (r *SessionRepository) LoadAuthorizingSessionState(
	ctx context.Context,
	sessionID string,
) (*ports.AuthorizingSessionState, error) {
	if r == nil || r.db == nil {
		return nil, errors.New("authorizing session: repository is unavailable")
	}
	if err := ctx.Err(); err != nil {
		return nil, err
	}
	executor := r.getExecutor(ctx)
	tx, transactional := executor.(*sql.Tx)
	if transactional {
		pessimistic, _ := ctx.Value(pessimisticTxMarkerKey{}).(bool)
		if !pessimistic {
			return nil, errors.New(
				"authorizing session: transaction fence requires a pessimistic transaction",
			)
		}
	}

	var (
		session                 models.SystemSession
		storedToken             string
		currentBearerRaw        sql.NullString
		controlProofHash        sql.NullString
		lastActivity            []byte
		scope                   sql.NullString
		connectedAppID          sql.NullString
		grantType               sql.NullString
		userID                  sql.NullString
		profileID               sql.NullString
		userRoleID              sql.NullString
		userActive              sql.NullBool
		userDeleted             sql.NullBool
		connectedAppActive      sql.NullBool
		scopeOwnerBindingCount  int64
		connectedAppSourcePack  sql.NullString
		permissionSingletonKey  sql.NullInt64
		permissionLineage       []byte
		permissionRevision      sql.NullString
		permissionMode          sql.NullString
		permissionCreated       sql.NullTime
		permissionModified      sql.NullTime
		permissionStateRowCount int64
	)
	err := executor.QueryRowContext(ctx, loadAuthorizingSessionStateSQL, sessionID).Scan(
		&session.ID,
		&session.UserID,
		&storedToken,
		&currentBearerRaw,
		&session.ExpiresAt,
		&session.IsRevoked,
		&session.AuthEpoch,
		&controlProofHash,
		&lastActivity,
		&scope,
		&connectedAppID,
		&grantType,
		&userID,
		&profileID,
		&userRoleID,
		&userActive,
		&userDeleted,
		&connectedAppActive,
		&scopeOwnerBindingCount,
		&connectedAppSourcePack,
		&permissionSingletonKey,
		&permissionLineage,
		&permissionRevision,
		&permissionMode,
		&permissionCreated,
		&permissionModified,
		&permissionStateRowCount,
	)
	if err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			return nil, nil
		}
		return nil, fmt.Errorf("authorizing session: strong read: %w", err)
	}

	session.Token, err = resolveCurrentBearerToken(storedToken, currentBearerRaw)
	if err != nil {
		return nil, fmt.Errorf("authorizing session: resolve current bearer: %w", err)
	}
	session.LastActivity = parseTime(lastActivity)
	session.ControlProofHash = authorizingNullableString(controlProofHash)
	session.Scope = authorizingNullableString(scope)
	session.ConnectedAppID = authorizingNullableString(connectedAppID)
	session.GrantType = authorizingNullableString(grantType)

	floor, err := authorizingPermissionFloor(
		permissionStateRowCount,
		permissionSingletonKey,
		permissionLineage,
		permissionRevision,
		permissionMode,
		permissionCreated,
		permissionModified,
	)
	if err != nil {
		return nil, err
	}

	state := &ports.AuthorizingSessionState{
		Session:                session,
		UserAuthorized:         false,
		ConnectedAppAuthorized: stringPointerValue(session.ConnectedAppID) == "",
		PermissionFloor:        floor,
	}
	if userID.Valid {
		if !userActive.Valid || !userDeleted.Valid {
			return nil, errors.New("authorizing session: current user state is incomplete")
		}
		subject, subjectErr := permissionstatev1.NewSubject(userID.String, profileID.String)
		if subjectErr != nil {
			return nil, fmt.Errorf("authorizing session: invalid current subject: %w", subjectErr)
		}
		state.Subject = subject
		state.UserRoleID = authorizingNullableStringValue(userRoleID)
		state.UserAuthorized = session.UserID == userID.String && userActive.Bool && !userDeleted.Bool
	}

	if stringPointerValue(session.ConnectedAppID) != "" {
		if scopeOwnerBindingCount < 0 || scopeOwnerBindingCount > 1 {
			return nil, errors.New("authorizing session: Connected App scope-owner binding is ambiguous")
		}
		if connectedAppActive.Valid && connectedAppActive.Bool {
			sourcePack := strings.TrimSpace(authorizingNullableStringValue(connectedAppSourcePack))
			switch {
			case sourcePack == "", r.independentIdentity:
				state.ConnectedAppAuthorized = true
			default:
				state.ConnectedAppAuthorized = r.connectedAppRuntimeVisible(
					sourcePack,
					stringPointerValue(session.ConnectedAppID),
				)
			}
		}
	}
	if transactional {
		var lockedSessionID string
		err := tx.QueryRowContext(
			ctx,
			fenceAuthorizingSessionStateSQL,
			session.ID,
			session.UserID,
			session.AuthEpoch,
		).Scan(&lockedSessionID)
		if errors.Is(err, sql.ErrNoRows) {
			return nil, nil
		}
		if err != nil {
			return nil, fmt.Errorf("authorizing session: transaction fence: %w", err)
		}
		if lockedSessionID != session.ID {
			return nil, errors.New("authorizing session: transaction fence returned foreign session")
		}
	}
	return state, nil
}

func authorizingPermissionFloor(
	rowCount int64,
	singletonKey sql.NullInt64,
	lineageBytes []byte,
	revision sql.NullString,
	mode sql.NullString,
	created sql.NullTime,
	modified sql.NullTime,
) (permissionstatev1.Version, error) {
	if rowCount != 1 || !singletonKey.Valid || singletonKey.Int64 != 1 {
		return permissionstatev1.Version{}, errors.New("authorizing session: invalid Permission State singleton")
	}
	if !revision.Valid || !mode.Valid || !created.Valid || !modified.Valid {
		return permissionstatev1.Version{}, errors.New(
			"authorizing session: incomplete Permission State singleton",
		)
	}
	state, err := permissionStateFromDurableValues(
		singletonKey.Int64,
		lineageBytes,
		revision.String,
		mode.String,
		created.Time,
		modified.Time,
	)
	if err != nil {
		return permissionstatev1.Version{}, fmt.Errorf(
			"authorizing session: invalid Permission State singleton: %w",
			err,
		)
	}
	return state.Version, nil
}

func authorizingNullableString(value sql.NullString) *string {
	if !value.Valid {
		return nil
	}
	result := value.String
	return &result
}

func authorizingNullableStringValue(value sql.NullString) string {
	if !value.Valid {
		return ""
	}
	return value.String
}

func stringPointerValue(value *string) string {
	if value == nil {
		return ""
	}
	return *value
}

const loadAuthorizingSessionStateSQL = `
SELECT
	s.Id,
	s.UserId,
	s.Token,
	s.CurrentBearerToken,
	s.ExpiresAt,
	s.IsRevoked,
	s.AuthEpoch,
	s.ControlProofHash,
	s.LastActivity,
	s.Scope,
	s.ConnectedAppId,
	s.GrantType,
	u.Id,
	u.ProfileId,
	u.UserRoleId,
	u.IsActive,
	u.IsDeleted,
	ca.IsActive,
	COALESCE((SELECT COUNT(*)
	            FROM _System_ConnectedAppDeclaration cad
	           WHERE cad.ConnectedAppId = s.ConnectedAppId
	             AND COALESCE(cad.IsDeleted, 0) = 0), 0)
	+
	COALESCE((SELECT COUNT(*)
	            FROM _System_OAuthDynamicClientRegistration dcr
	           WHERE dcr.ConnectedAppId = s.ConnectedAppId
	             AND COALESCE(dcr.IsDeleted, 0) = 0
	             AND dcr.ScopeOwnerPack IS NOT NULL AND dcr.ScopeOwnerPack <> ''), 0),
	COALESCE((SELECT MAX(cad.InstalledPackName)
	            FROM _System_ConnectedAppDeclaration cad
	           WHERE cad.ConnectedAppId = s.ConnectedAppId
	             AND COALESCE(cad.IsDeleted, 0) = 0),
	         (SELECT MAX(dcr.ScopeOwnerPack)
	            FROM _System_OAuthDynamicClientRegistration dcr
	           WHERE dcr.ConnectedAppId = s.ConnectedAppId
	             AND COALESCE(dcr.IsDeleted, 0) = 0
	             AND dcr.ScopeOwnerPack IS NOT NULL AND dcr.ScopeOwnerPack <> '')),
	ps.SingletonKey,
	ps.Lineage,
	CAST(ps.Revision AS CHAR),
	ps.EnforcementMode,
	ps.CreatedDate,
	ps.LastModifiedDate,
	(SELECT COUNT(*) FROM _System_PermissionStateVersion)
FROM _System_Session s
LEFT JOIN User u ON u.Id = s.UserId
LEFT JOIN _System_ConnectedApp ca ON ca.Id = s.ConnectedAppId
LEFT JOIN _System_PermissionStateVersion ps ON ps.SingletonKey = 1
WHERE s.Id = ?
LIMIT 1`

// A caller-owned authorization transaction rechecks and locks the exact
// session row after the composite authority read. A revocation or AuthEpoch
// advance that wins the lock is therefore observed before the caller can
// commit its own authorization-dependent mutation; one that loses the lock
// serializes after that mutation.
const fenceAuthorizingSessionStateSQL = `
SELECT Id
FROM _System_Session
WHERE BINARY Id = BINARY ?
  AND BINARY UserId = BINARY ?
  AND AuthEpoch = ?
  AND IsRevoked = 0
  AND ExpiresAt > UTC_TIMESTAMP(6)
LIMIT 1
FOR UPDATE`