package persistence

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

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

var _ ports.AuthorizingApiTokenRepository = (*ApiTokenRepository)(nil)
var _ ports.RuntimeAuthorizingApiTokenRepository = (*ApiTokenRepository)(nil)

// LoadAuthorizingApiTokenState observes one durable PAT, its current user
// projection, and the Permission State floor in one SQL statement.
func (r *ApiTokenRepository) LoadAuthorizingApiTokenState(
	ctx context.Context,
	tokenHash string,
) (*ports.AuthorizingApiTokenState, error) {
	return r.loadAuthorizingApiTokenState(
		ctx,
		loadAuthorizingApiTokenStateSQL,
		tokenHash,
	)
}

// LoadRuntimeAuthorizingApiTokenState observes one durable PAT by its
// non-secret identifier after the request path has already admitted it.
func (r *ApiTokenRepository) LoadRuntimeAuthorizingApiTokenState(
	ctx context.Context,
	tokenID string,
) (*ports.AuthorizingApiTokenState, error) {
	return r.loadAuthorizingApiTokenState(
		ctx,
		loadRuntimeAuthorizingApiTokenStateSQL,
		tokenID,
	)
}

func (r *ApiTokenRepository) loadAuthorizingApiTokenState(
	ctx context.Context,
	query string,
	selector string,
) (*ports.AuthorizingApiTokenState, error) {
	if r == nil || r.db == nil {
		return nil, errors.New("authorizing API token: repository is unavailable")
	}
	if err := ctx.Err(); err != nil {
		return nil, err
	}

	var (
		token                   models.SystemApiToken
		scopes                  sql.NullString
		expiresAt               []byte
		lastUsedAt              []byte
		createdAt               []byte
		lastModifiedAt          []byte
		userID                  sql.NullString
		username                sql.NullString
		firstName               sql.NullString
		lastName                sql.NullString
		email                   sql.NullString
		profileID               sql.NullString
		userRoleID              sql.NullString
		userActive              sql.NullBool
		userDeleted             sql.NullBool
		lockedUntil             []byte
		profileName             sql.NullString
		userRoleName            sql.NullString
		permissionSingletonKey  sql.NullInt64
		permissionLineage       []byte
		permissionRevision      sql.NullString
		permissionMode          sql.NullString
		permissionCreated       sql.NullTime
		permissionModified      sql.NullTime
		permissionStateRowCount int64
	)
	err := r.db.QueryRowContext(ctx, query, selector).Scan(
		&token.ID,
		&token.OwnerID,
		&token.Name,
		&token.TokenHash,
		&token.TokenPrefix,
		&scopes,
		&expiresAt,
		&lastUsedAt,
		&token.IsRevoked,
		&createdAt,
		&lastModifiedAt,
		&userID,
		&username,
		&firstName,
		&lastName,
		&email,
		&profileID,
		&userRoleID,
		&userActive,
		&userDeleted,
		&lockedUntil,
		&profileName,
		&userRoleName,
		&permissionSingletonKey,
		&permissionLineage,
		&permissionRevision,
		&permissionMode,
		&permissionCreated,
		&permissionModified,
		&permissionStateRowCount,
	)
	if err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			return nil, nil
		}
		return nil, fmt.Errorf("authorizing API token: strong read: %w", err)
	}

	token.Scopes = authorizingNullableString(scopes)
	if len(expiresAt) > 0 {
		value := parseTime(expiresAt)
		token.ExpiresAt = &value
	}
	if len(lastUsedAt) > 0 {
		value := parseTime(lastUsedAt)
		token.LastUsedDate = &value
	}
	token.CreatedDate = parseTime(createdAt)
	token.LastModifiedDate = parseTime(lastModifiedAt)

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

	state := &ports.AuthorizingApiTokenState{
		Token:           token,
		PermissionFloor: floor,
	}
	if !userID.Valid {
		return state, nil
	}
	if !userActive.Valid || !userDeleted.Valid {
		return nil, errors.New("authorizing API token: current user state is incomplete")
	}
	subject, err := permissionstatev1.NewSubject(userID.String, profileID.String)
	if err != nil {
		return nil, fmt.Errorf("authorizing API token: invalid current subject: %w", err)
	}
	state.Subject = subject
	state.Username = authorizingNullableStringValue(username)
	state.FirstName = authorizingNullableStringValue(firstName)
	state.LastName = authorizingNullableStringValue(lastName)
	state.Email = authorizingNullableStringValue(email)
	state.ProfileName = authorizingNullableStringValue(profileName)
	state.UserRoleID = authorizingNullableStringValue(userRoleID)
	state.UserRoleName = authorizingNullableStringValue(userRoleName)
	state.UserAuthorized = token.OwnerID == userID.String &&
		userActive.Bool &&
		!userDeleted.Bool &&
		!authorizingUserLocked(lockedUntil, time.Now())
	return state, nil
}

func authorizingUserLocked(raw []byte, now time.Time) bool {
	if len(raw) == 0 {
		return false
	}
	lockedUntil := parseTime(raw)
	return lockedUntil.IsZero() || now.Before(lockedUntil)
}

const loadAuthorizingApiTokenStateSelectSQL = `
SELECT
	t.Id,
	t.OwnerId,
	t.Name,
	t.TokenHash,
	t.TokenPrefix,
	t.Scopes,
	t.ExpiresAt,
	t.LastUsedDate,
	t.IsRevoked,
	t.CreatedDate,
	t.LastModifiedDate,
	u.Id,
	u.Username,
	u.FirstName,
	u.LastName,
	u.Email,
	u.ProfileId,
	u.UserRoleId,
	u.IsActive,
	u.IsDeleted,
	u.LockedUntil,
	p.Name,
	ur.Name,
	ps.SingletonKey,
	ps.Lineage,
	CAST(ps.Revision AS CHAR),
	ps.EnforcementMode,
	ps.CreatedDate,
	ps.LastModifiedDate,
	(SELECT COUNT(*) FROM _System_PermissionStateVersion)
FROM _System_ApiToken t
LEFT JOIN User u ON u.Id = t.OwnerId
LEFT JOIN Profile p ON p.Id = u.ProfileId AND COALESCE(p.IsDeleted, 0) = 0
LEFT JOIN UserRole ur ON ur.Id = u.UserRoleId AND COALESCE(ur.IsDeleted, 0) = 0
LEFT JOIN _System_PermissionStateVersion ps ON ps.SingletonKey = 1`

const loadAuthorizingApiTokenStateSQL = loadAuthorizingApiTokenStateSelectSQL + `
WHERE t.TokenHash = ?
LIMIT 1`

const loadRuntimeAuthorizingApiTokenStateSQL = loadAuthorizingApiTokenStateSelectSQL + `
WHERE t.Id = ?
LIMIT 1`