package persistence

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

	"github.com/google/uuid"
	apperrors "github.com/nexuscrm/kernel/pkg/errors"
	"github.com/nexuscrm/kernel/pkg/query"
	"github.com/nexuscrm/shared/pkg/constants"
	"github.com/nexuscrm/shared/pkg/models"
)

type SystemRepository struct {
	db                            *sql.DB
	transactions                  *TransactionManager
	connectedAppRuntimeVisibility connectedAppRuntimeVisibilityPolicy
}

func NewSystemRepository(db *sql.DB) *SystemRepository {
	return &SystemRepository{db: db}
}

// SetTransactionManager installs the shared transaction owner used to
// serialize Connected App credential issuance with access revocation.
func (r *SystemRepository) SetTransactionManager(transactions *TransactionManager) error {
	if r == nil || transactions == nil {
		return errors.New("persistence: system repository transaction manager is required")
	}
	r.transactions = transactions
	return nil
}

type connectedAppRuntimeVisibilityPolicy interface {
	AllowsConnectedApp(ownerPack, connectedAppID string) bool
}

// SetConnectedAppRuntimeVisibilityPolicy gates declaration-owned Connected Apps
// through the serving process's local product OAuth capability catalog.
// Ownerless platform/admin Connected Apps keep the historical runtime path.
func (r *SystemRepository) SetConnectedAppRuntimeVisibilityPolicy(policy connectedAppRuntimeVisibilityPolicy) {
	if r == nil {
		return
	}
	r.connectedAppRuntimeVisibility = policy
}

// GetExecutor returns the transactional executor if present in context, or the DB connection
func (r *SystemRepository) GetExecutor(ctx context.Context) Executor {
	if tx, ok := ctx.Value(txContextKey{}).(*sql.Tx); ok && tx != nil {
		return tx
	}
	return r.db
}

// RecordAdminDynamicOAuthClient records the L2 administrator authorization for
// a dynamically registered client. It is deliberately separate from
// _System_ConnectedAppDeclaration: that registry is reserved for immutable L1
// pack components and is reconciled by pack apply.
func (r *SystemRepository) RecordAdminDynamicOAuthClient(ctx context.Context, connectedAppID, scopeOwnerPack, registeredByUserID string) error {
	connectedAppID = strings.TrimSpace(connectedAppID)
	scopeOwnerPack = strings.TrimSpace(scopeOwnerPack)
	registeredByUserID = strings.TrimSpace(registeredByUserID)
	if connectedAppID == "" || registeredByUserID == "" {
		return errors.New("persistence: dynamic OAuth client registration requires app id and administrator id")
	}
	var ownerPack any
	if scopeOwnerPack != "" {
		ownerPack = scopeOwnerPack
	}
	_, err := r.GetExecutor(ctx).ExecContext(ctx, `
		INSERT INTO _System_OAuthDynamicClientRegistration
			(Id, ConnectedAppId, ScopeOwnerPack, RegisteredByUserId, IsDeleted)
		VALUES (?, ?, ?, ?, 0)`, uuid.NewString(), connectedAppID, ownerPack, registeredByUserID)
	return err
}

// WithConnectedAppCredentialIssuance serializes the complete credential
// issuance callback with Connected App policy/access mutation. The callback
// inherits one pessimistic transaction so its final policy read, one-time code
// consumption, session insert, and refresh-token insert commit atomically.
func (r *SystemRepository) WithConnectedAppCredentialIssuance(
	ctx context.Context,
	appID string,
	issue func(context.Context) error,
) error {
	if r == nil {
		return errors.New("persistence: system repository is required")
	}
	if strings.TrimSpace(appID) == "" {
		return errors.New("persistence: connected app credential issuance requires an app ID")
	}
	if issue == nil {
		return errors.New("persistence: connected app credential issuance callback is required")
	}
	if r.transactions == nil {
		if !isPessimisticGrantTransaction(ctx) {
			return errors.New("persistence: system repository transaction manager is required")
		}
		return r.withLockedConnectedAppCredentialIssuance(ctx, appID, issue)
	}
	return r.transactions.WithPessimisticTransaction(WithReadCommittedIsolation(ctx), func(txCtx context.Context) error {
		return r.withLockedConnectedAppCredentialIssuance(txCtx, appID, issue)
	})
}

// withLockedConnectedAppCredentialIssuance runs only after the caller has
// entered a pessimistic transaction. Keeping the locking body reusable lets
// direct permission-state writers participate in their already-owned
// transaction rather than creating a nested one.
func (r *SystemRepository) withLockedConnectedAppCredentialIssuance(
	ctx context.Context,
	appID string,
	issue func(context.Context) error,
) error {
	exec := r.GetExecutor(ctx)
	var lockedAppID string
	if err := exec.QueryRowContext(
		ctx,
		"SELECT Id FROM _System_ConnectedApp WHERE Id = ? FOR UPDATE",
		appID,
	).Scan(&lockedAppID); err != nil {
		if err == sql.ErrNoRows {
			return apperrors.NewNotFoundError("Connected App", appID)
		}
		return apperrors.NewInternalError("failed to lock Connected App credential issuance", err)
	}
	rows, err := exec.QueryContext(
		ctx,
		"SELECT Id FROM _System_ConnectedAppAccess WHERE ConnectedAppId = ? ORDER BY Id FOR UPDATE",
		appID,
	)
	if err != nil {
		return apperrors.NewInternalError("failed to lock Connected App access policy", err)
	}
	for rows.Next() {
	}
	if err := rows.Close(); err != nil {
		return apperrors.NewInternalError("failed to close Connected App access policy rows", err)
	}
	if err := rows.Err(); err != nil {
		return apperrors.NewInternalError("failed to iterate Connected App access policy rows", err)
	}
	return issue(ctx)
}

// GetLogs retrieves system logs
func (r *SystemRepository) GetLogs(ctx context.Context, limit int) ([]*models.SystemLog, error) {
	if limit <= 0 {
		limit = 100
	}

	q := query.From(constants.TableLog).
		Select([]string{constants.FieldID, constants.FieldTimestamp, constants.FieldLevel, constants.FieldSource, constants.FieldMessage, constants.FieldDetails}).
		OrderBy(constants.FieldTimestamp, constants.SortDESC).
		Limit(limit).
		Build()

	exec := r.GetExecutor(ctx)
	rows, err := exec.QueryContext(ctx, q.SQL, q.Params...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	logs := make([]*models.SystemLog, 0)
	for rows.Next() {
		var log models.SystemLog
		var details *string

		if err := rows.Scan(&log.ID, &log.Timestamp, &log.Level, &log.Source, &log.Message, &details); err != nil {
			continue
		}

		log.Details = details
		logs = append(logs, &log)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}

	return logs, nil
}

// GetRecentItems retrieves recently viewed items for a user
func (r *SystemRepository) GetRecentItems(ctx context.Context, userID string, limit int) ([]*models.SystemRecent, error) {
	if userID == "" {
		return []*models.SystemRecent{}, nil
	}

	if limit <= 0 {
		limit = 10
	}

	q := query.From(constants.TableRecent).
		Select([]string{constants.FieldID, constants.FieldUserID, constants.FieldObjectAPIName, constants.FieldRecordID, constants.FieldRecordName, constants.FieldTimestamp}).
		Where(constants.FieldUserID, userID).
		OrderBy(constants.FieldTimestamp, constants.SortDESC).
		Limit(limit).
		Build()

	exec := r.GetExecutor(ctx)
	rows, err := exec.QueryContext(ctx, q.SQL, q.Params...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	items := make([]*models.SystemRecent, 0)
	for rows.Next() {
		var item models.SystemRecent

		if err := rows.Scan(&item.ID, &item.UserID, &item.ObjectAPIName, &item.RecordID, &item.RecordName, &item.Timestamp); err != nil {
			continue
		}

		items = append(items, &item)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}

	return items, nil
}

// GetConfig retrieves a system configuration value
func (r *SystemRepository) GetConfig(ctx context.Context, key string) (*string, error) {
	q := query.From(constants.TableConfig).
		SkipID().
		Select([]string{constants.FieldValue}).
		Where(constants.FieldKeyName, key).
		Limit(1).
		Build()

	exec := r.GetExecutor(ctx)
	rows, err := exec.QueryContext(ctx, q.SQL, q.Params...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	if rows.Next() {
		var value string
		if err := rows.Scan(&value); err != nil {
			return nil, err
		}
		return &value, nil
	}

	return nil, nil
}

// GetAllConfigs retrieves all system configurations
func (r *SystemRepository) GetAllConfigs(ctx context.Context) ([]*models.SystemConfig, error) {
	q := query.From(constants.TableConfig).
		SkipID().
		Select([]string{constants.FieldKeyName, constants.FieldValue, constants.FieldIsSecret, constants.FieldDescription}).
		Build()

	exec := r.GetExecutor(ctx)
	rows, err := exec.QueryContext(ctx, q.SQL, q.Params...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	configs := make([]*models.SystemConfig, 0)
	for rows.Next() {
		var config models.SystemConfig
		var isSecret int // TiDB/MySQL boolean is tinyint
		var description *string

		if err := rows.Scan(&config.KeyName, &config.Value, &isSecret, &description); err != nil {
			continue
		}

		config.IsSecret = isSecret != 0
		if description != nil {
			config.Description = description
		}

		configs = append(configs, &config)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}

	return configs, nil
}

// GetConfigID checks if a config key exists and returns the ID if found
func (r *SystemRepository) GetConfigID(ctx context.Context, key string) (string, error) {
	qr := query.From(constants.TableConfig).
		SkipID().
		Select([]string{constants.FieldSysConfig_KeyName}).
		Where(constants.FieldKeyName, key).
		Build()

	exec := r.GetExecutor(ctx)
	var id string
	err := exec.QueryRowContext(ctx, qr.SQL, qr.Params...).Scan(&id)
	if err == sql.ErrNoRows {
		return "", nil // Not found
	}
	if err != nil {
		return "", err
	}
	return id, nil
}

func (r *SystemRepository) UpsertConfig(ctx context.Context, config *models.SystemConfig) error {
	exec := r.GetExecutor(ctx)
	_, err := exec.ExecContext(
		ctx,
		`INSERT INTO _System_Config (KeyName, Value, IsSecret, Description, CreatedDate, LastModifiedDate)
		 VALUES (?, ?, ?, ?, NOW(), NOW())
		 ON DUPLICATE KEY UPDATE
		   Value = VALUES(Value),
		   IsSecret = VALUES(IsSecret),
		   Description = VALUES(Description),
		   LastModifiedDate = NOW()`,
		config.KeyName,
		config.Value,
		config.IsSecret,
		config.Description,
	)
	return err
}

func (r *SystemRepository) DeleteConfig(ctx context.Context, key string) error {
	exec := r.GetExecutor(ctx)
	_, err := exec.ExecContext(ctx, `DELETE FROM _System_Config WHERE KeyName = ?`, key)
	return err
}

// GetProfileIDByName checks existence by name and returns ID if found
func (r *SystemRepository) GetProfileIDByName(ctx context.Context, name string) (string, error) {
	qr := query.From(constants.TableProfile).
		Select([]string{constants.FieldID}).
		Where(constants.FieldName, name).
		Build()

	var id string
	exec := r.GetExecutor(ctx)
	err := exec.QueryRowContext(ctx, qr.SQL, qr.Params...).Scan(&id)
	if err == sql.ErrNoRows {
		return "", nil // Not found
	}
	if err != nil {
		return "", err
	}
	return id, nil
}

// GetUserIDByEmail checks existence by email and returns ID if found
func (r *SystemRepository) GetUserIDByEmail(ctx context.Context, email string) (string, error) {
	qr := query.From(constants.TableUser).
		Select([]string{constants.FieldID}).
		Where(constants.FieldEmail, email).
		Build()

	var id string
	exec := r.GetExecutor(ctx)
	err := exec.QueryRowContext(ctx, qr.SQL, qr.Params...).Scan(&id)
	if err == sql.ErrNoRows {
		return "", nil
	}
	if err != nil {
		return "", err
	}
	return id, nil
}

// BatchUpsertProfiles inserts multiple profiles using direct SQL (Hardened for Aliasing)
func (r *SystemRepository) BatchUpsertProfiles(ctx context.Context, profiles []models.Profile) error {
	if len(profiles) == 0 {
		return nil
	}

	values := []string{}
	args := []interface{}{}

	for _, p := range profiles {
		// (id, name, desc, system, created_date, last_modified)
		values = append(values, fmt.Sprintf("(?, ?, ?, ?, %s, %s)", FuncCurrentTimestamp, FuncCurrentTimestamp))
		args = append(args, p.ID, p.Name, p.Description, p.IsSystem)
	}

	// Use Physical Column Names explicitly for System Tables
	// ID -> Id
	cols := strings.Join([]string{
		constants.ColID, // Id
		constants.FieldProfile_Name,
		constants.FieldProfile_Description,
		constants.FieldProfile_IsSystem,
		constants.ColCreatedDate,
		constants.ColLastModifiedDate,
	}, ", ")

	query := fmt.Sprintf(
		"%s %s (%s) %s %s %s %s=VALUES(%s), %s=VALUES(%s), %s=VALUES(%s), %s=%s",
		KeywordInsertInto, constants.TableProfile,
		cols,
		KeywordValues, strings.Join(values, ","),
		KeywordOnDuplicate,
		constants.ColID, constants.ColID, // Updates ID
		constants.FieldProfile_Description, constants.FieldProfile_Description,
		constants.FieldProfile_IsSystem, constants.FieldProfile_IsSystem,
		constants.ColLastModifiedDate, FuncCurrentTimestamp,
	)

	exec := r.GetExecutor(ctx)
	if _, err := exec.ExecContext(ctx, query, args...); err != nil {
		return apperrors.NewInternalError("batch upsert profiles failed", err)
	}
	return nil
}

// BatchUpsertUsers inserts multiple users using direct SQL (Hardened for Aliasing)
func (r *SystemRepository) BatchUpsertUsers(ctx context.Context, users []models.User) error {
	if len(users) == 0 {
		return nil
	}

	values := []string{}
	args := []interface{}{}

	for _, u := range users {
		values = append(values, fmt.Sprintf("(?, ?, ?, ?, ?, ?, ?, ?, ?, %s, %s)", FuncCurrentTimestamp, FuncCurrentTimestamp))
		// ID, Username, Email, Password, FirstName, LastName, ProfileID, UserRoleId, IsActive
		var roleID interface{}
		if u.UserRoleID != nil {
			roleID = *u.UserRoleID
		}
		args = append(args, u.ID, u.Username, u.Email, u.Password, u.FirstName, u.LastName, u.ProfileID, roleID, true)
	}

	// Use Physical Column Names explicitly for System Tables
	// ID -> Id
	cols := strings.Join([]string{
		constants.ColID, // Id
		constants.FieldUser_Username, constants.FieldUser_Email,
		constants.FieldUser_Password, constants.FieldUser_FirstName, constants.FieldUser_LastName,
		constants.FieldUser_ProfileID, constants.FieldUser_UserRoleID, constants.FieldUser_IsActive,
		constants.ColCreatedDate, constants.ColLastModifiedDate,
	}, ", ")

	// Password update strategy:
	// - On INSERT: always set the hashed password from seed data
	// - On UPDATE: only overwrite if the existing password is not a bcrypt hash
	// Preserve existing bcrypt hashes. setup apply may be rerun during explicit
	// bootstrap or repair, so we must not overwrite passwords that may have
	// been changed through the app.
	// Only update if the existing value is not already a bcrypt hash (legacy plaintext fix).
	query := fmt.Sprintf(
		`%s %s (%s)
		%s %s
		%s
		%s=VALUES(%s), %s=VALUES(%s),
		%s=VALUES(%s), %s=VALUES(%s), %s=VALUES(%s),
		%s=VALUES(%s),
		%s=IF(%s LIKE '$2%%', %s, VALUES(%s)),
		%s=%s`,
		KeywordInsertInto, constants.TableUser, cols,
		KeywordValues, strings.Join(values, ","),
		KeywordOnDuplicate,
		constants.FieldUser_Username, constants.FieldUser_Username,
		constants.FieldUser_FirstName, constants.FieldUser_FirstName,
		constants.FieldUser_LastName, constants.FieldUser_LastName,
		constants.FieldUser_ProfileID, constants.FieldUser_ProfileID,
		constants.FieldUser_UserRoleID, constants.FieldUser_UserRoleID,
		constants.FieldUser_IsActive, constants.FieldUser_IsActive,
		constants.FieldUser_Password, constants.FieldUser_Password, constants.FieldUser_Password, constants.FieldUser_Password,
		constants.ColLastModifiedDate, FuncCurrentTimestamp,
	)

	exec := r.GetExecutor(ctx)
	if _, err := exec.ExecContext(ctx, query, args...); err != nil {
		return apperrors.NewInternalError("batch upsert users failed", err)
	}
	return nil
}

// ForceUpdateUserPassword updates a user's password unconditionally.
// Used to ensure explicitly configured admin passwords take effect on existing deployments.
func (r *SystemRepository) ForceUpdateUserPassword(ctx context.Context, email string, hashedPassword string) error {
	query := fmt.Sprintf("UPDATE %s SET %s = ?, %s = %s WHERE %s = ?",
		constants.TableUser,
		constants.FieldUser_Password,
		constants.ColLastModifiedDate, FuncCurrentTimestamp,
		constants.FieldUser_Email,
	)
	result, err := r.GetExecutor(ctx).ExecContext(ctx, query, hashedPassword, email)
	if err != nil {
		return err
	}
	rows, _ := result.RowsAffected()
	if rows == 0 {
		return nil // User doesn't exist yet, batch upsert will create them
	}
	return nil
}

// IsUserPermittedForApp checks whether the user has an access grant for the given Connected App.
// It returns true if the user's Profile or any of their PermissionSets is listed in
// _System_ConnectedAppAccess for the app.
func (r *SystemRepository) IsUserPermittedForApp(ctx context.Context, appID string, userID string) (bool, error) {
	var count int
	err := r.GetExecutor(ctx).QueryRowContext(ctx,
		`SELECT COUNT(*) FROM _System_ConnectedAppAccess caa
		WHERE caa.ConnectedAppId = ?
		AND (
			caa.ProfileId = (SELECT ProfileId FROM User WHERE Id = ?)
			OR caa.PermissionSetId IN (
				SELECT PermissionSetId FROM PermissionSetAssignment WHERE AssigneeId = ?
			)
		)`,
		appID, userID, userID,
	).Scan(&count)
	if err != nil {
		return false, err
	}
	return count > 0, nil
}

// GetConnectedAppByClientID retrieves a connected app by Consumer Key
func (r *SystemRepository) GetConnectedAppByClientID(ctx context.Context, clientID string) (*models.SystemConnectedApp, error) {
	var app models.SystemConnectedApp
	var isActive, requirePkce, consentVersion, issueRefreshTokens int
	var consumerSecret, consumerSecretHash, callbackURL, redirectURIs, scopes sql.NullString
	var clientType, tokenEndpointAuthMethod, allowedGrantTypes, consentPolicy, publisherName, supportURL sql.NullString
	var runAsUserID, permittedUsers, sessionIssuerClass, sourcePackName sql.NullString
	var sourcePackBindingCount int64
	// Runtime auth reads the committed live ConnectedApp row. Do not gate this
	// lookup on _System_InstalledPack.Status: source-pack reapply uses
	// installing/failed as transient control-plane states while an already
	// committed OAuth client must remain available.
	err := r.GetExecutor(ctx).QueryRowContext(ctx, `
	SELECT ca.Id, ca.Name, ca.ConsumerKey, ca.ConsumerSecret, ca.ConsumerSecretHash, ca.CallbackUrl,
	       ca.RedirectUris, ca.Scopes, ca.IsActive, ca.ClientType, ca.RequirePkce,
	       ca.TokenEndpointAuthMethod, ca.AllowedGrantTypes, ca.ConsentPolicy, ca.ConsentVersion,
	       ca.IssueRefreshTokens, ca.PublisherName, ca.SupportUrl, ca.RunAsUserId,
	       ca.PermittedUsers, ca.SessionIssuerClass, COALESCE(owner.BindingCount, 0), owner.OwnerPack
	  FROM _System_ConnectedApp ca
	  LEFT JOIN (
		SELECT ConnectedAppId, COUNT(*) AS BindingCount, MAX(OwnerPack) AS OwnerPack
		  FROM (
			SELECT ConnectedAppId, InstalledPackName AS OwnerPack
			  FROM _System_ConnectedAppDeclaration
			 WHERE COALESCE(IsDeleted, 0) = 0
			UNION ALL
			SELECT ConnectedAppId, ScopeOwnerPack AS OwnerPack
			  FROM _System_OAuthDynamicClientRegistration
			 WHERE COALESCE(IsDeleted, 0) = 0 AND ScopeOwnerPack IS NOT NULL AND ScopeOwnerPack <> ''
		  ) bindings
		 GROUP BY ConnectedAppId
	) owner ON owner.ConnectedAppId = ca.Id
	 WHERE ca.ConsumerKey = ?
	   AND ca.IsActive = 1
 LIMIT 1`, clientID).Scan(
		&app.ID,
		&app.Name,
		&app.ConsumerKey,
		&consumerSecret,
		&consumerSecretHash,
		&callbackURL,
		&redirectURIs,
		&scopes,
		&isActive,
		&clientType,
		&requirePkce,
		&tokenEndpointAuthMethod,
		&allowedGrantTypes,
		&consentPolicy,
		&consentVersion,
		&issueRefreshTokens,
		&publisherName,
		&supportURL,
		&runAsUserID,
		&permittedUsers,
		&sessionIssuerClass,
		&sourcePackBindingCount,
		&sourcePackName,
	)
	if err != nil {
		if err == sql.ErrNoRows {
			return nil, apperrors.NewNotFoundError("connected_app", clientID)
		}
		return nil, err
	}

	if consumerSecret.Valid {
		app.ConsumerSecret = consumerSecret.String
	}
	if consumerSecretHash.Valid {
		app.ConsumerSecretHash = &consumerSecretHash.String
	}
	if callbackURL.Valid {
		app.CallbackURL = &callbackURL.String
	}
	if redirectURIs.Valid {
		app.RedirectURIs = &redirectURIs.String
	}
	if scopes.Valid {
		app.Scopes = &scopes.String
	}
	app.IsActive = isActive != 0
	app.RequirePkce = requirePkce != 0
	app.ConsentVersion = consentVersion
	app.IssueRefreshTokens = issueRefreshTokens != 0
	if clientType.Valid {
		app.ClientType = &clientType.String
	}
	if tokenEndpointAuthMethod.Valid {
		app.TokenEndpointAuthMethod = &tokenEndpointAuthMethod.String
	}
	if allowedGrantTypes.Valid {
		app.AllowedGrantTypes = &allowedGrantTypes.String
	}
	if consentPolicy.Valid {
		app.ConsentPolicy = &consentPolicy.String
	}
	if publisherName.Valid {
		app.PublisherName = &publisherName.String
	}
	if supportURL.Valid {
		app.SupportURL = &supportURL.String
	}

	if runAsUserID.Valid {
		app.RunAsUserID = &runAsUserID.String
	}
	if permittedUsers.Valid {
		app.PermittedUsers = &permittedUsers.String
	}
	if sessionIssuerClass.Valid {
		app.SessionIssuerClass = &sessionIssuerClass.String
	}
	if sourcePackName.Valid && strings.TrimSpace(sourcePackName.String) != "" {
		value := strings.TrimSpace(sourcePackName.String)
		app.SourcePackName = &value
	}

	if !app.IsActive {
		return nil, apperrors.NewForbiddenError("connected app is disabled")
	}
	if sourcePackBindingCount > 1 {
		return nil, apperrors.NewForbiddenError("connected app scope-owner binding is ambiguous")
	}
	if !r.connectedAppRuntimeVisible(app.SourcePackName, app.ID) {
		return nil, apperrors.NewNotFoundError("connected app", clientID)
	}

	return &app, nil
}

func (r *SystemRepository) connectedAppRuntimeVisible(sourcePackName *string, connectedAppID string) bool {
	if sourcePackName == nil || strings.TrimSpace(*sourcePackName) == "" {
		return true
	}
	if r == nil || r.connectedAppRuntimeVisibility == nil {
		return false
	}
	return r.connectedAppRuntimeVisibility.AllowsConnectedApp(*sourcePackName, connectedAppID)
}