package persistence

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

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

// GetUserProfileID fetches the profile ID for a given user
func (r *PermissionRepository) GetUserProfileID(ctx context.Context, userID string) (string, error) {
	qr := query.From(constants.TableUser).
		SkipID().
		Select([]string{constants.FieldProfileID}).
		Where(constants.FieldID, userID).
		Build()

	var profileID string
	err := r.getExecutor(ctx).QueryRowContext(ctx, qr.SQL, qr.Params...).Scan(&profileID)
	if err != nil {
		return "", apperrors.NewInternalError("failed to get user profile", err)
	}
	return profileID, nil
}

// GetAllRoles retrieves all system roles
func (r *PermissionRepository) GetAllRoles(ctx context.Context) ([]*models.UserRole, error) {
	qr := query.From(constants.TableUserRole).
		Select([]string{
			constants.FieldID, constants.FieldUserRole_Name,
			constants.FieldUserRole_RollupDescription, constants.FieldUserRole_ParentRoleID,
		}).
		Where(constants.FieldIsDeleted, 0).
		Build()

	rows, err := r.getExecutor(ctx).QueryContext(ctx, qr.SQL, qr.Params...)
	if err != nil {
		return nil, apperrors.NewInternalError("failed to query roles", err)
	}
	defer rows.Close()

	var roles []*models.UserRole
	for rows.Next() {
		var role models.UserRole
		var description sql.NullString
		if err := rows.Scan(&role.ID, &role.Name, &description, &role.ParentRoleID); err != nil {
			return nil, apperrors.NewInternalError("failed to scan role", err)
		}
		desc := utils.ScanNullStringValue(description)
		role.RollupDescription = &desc
		roles = append(roles, &role)
	}
	if err := rows.Err(); err != nil {
		return nil, apperrors.NewInternalError("error iterating roles", err)
	}
	return roles, nil
}

// CreateRole creates a new role
func (r *PermissionRepository) CreateRole(ctx context.Context, name, description string, parentRoleID *string) (string, error) {
	id := utils.GenerateID()
	now := time.Now()

	values := map[string]interface{}{
		constants.FieldID:                         id,
		constants.FieldUserRole_Name:              name,
		constants.FieldUserRole_RollupDescription: description,
		constants.FieldUserRole_ParentRoleID:      parentRoleID,
		constants.FieldCreatedDate:                now,
		constants.FieldLastModifiedDate:           now,
		constants.FieldIsDeleted:                  0,
	}

	qr := query.Insert(constants.TableUserRole, values).Build()
	if err := r.execRoleMutationWithClosureRebuild(ctx, qr.SQL, qr.Params...); err != nil {
		return "", apperrors.NewInternalError("failed to create role", err)
	}
	return id, nil
}

// GetRole retrieves a role by ID
func (r *PermissionRepository) GetRole(ctx context.Context, id string) (*models.UserRole, error) {
	return r.getRole(ctx, id, false)
}

// LockRoleLifecycle fences concurrent deletion until the selector's transaction ends.
func (r *PermissionRepository) LockRoleLifecycle(ctx context.Context, id string) (*models.UserRole, error) {
	if _, ok := r.getExecutor(ctx).(*sql.Tx); !ok {
		return nil, apperrors.NewInternalError("Role lifecycle locks require the owning transaction", nil)
	}
	return r.getRole(ctx, id, true)
}

func (r *PermissionRepository) getRole(ctx context.Context, id string, lock bool) (*models.UserRole, error) {
	qr := query.From(constants.TableUserRole).
		Select([]string{
			constants.FieldID, constants.FieldUserRole_Name,
			constants.FieldUserRole_RollupDescription, constants.FieldUserRole_ParentRoleID,
		}).
		Where(constants.FieldID, id).
		Where(constants.FieldIsDeleted, 0).
		Build()

	if lock {
		qr.SQL += " FOR UPDATE"
	}

	var role models.UserRole
	var description sql.NullString
	err := r.getExecutor(ctx).QueryRowContext(ctx, qr.SQL, qr.Params...).Scan(&role.ID, &role.Name, &description, &role.ParentRoleID)
	if err != nil {
		if err == sql.ErrNoRows {
			return nil, nil
		}
		return nil, apperrors.NewInternalError("failed to get role", err)
	}
	desc2 := utils.ScanNullStringValue(description)
	role.RollupDescription = &desc2
	return &role, nil
}

// UpdateRole updates an existing role
func (r *PermissionRepository) UpdateRole(ctx context.Context, id, name, description string, parentRoleID *string) error {
	updates := map[string]interface{}{
		constants.FieldUserRole_Name:              name,
		constants.FieldUserRole_RollupDescription: description,
		constants.FieldUserRole_ParentRoleID:      parentRoleID,
		constants.FieldLastModifiedDate:           time.Now(),
	}

	qr := query.Update(constants.TableUserRole).
		Set(updates).
		Where(constants.FieldID, id).
		Where(constants.FieldIsDeleted, 0).
		Build()

	if err := r.execRoleMutationWithClosureRebuild(ctx, qr.SQL, qr.Params...); err != nil {
		return apperrors.NewInternalError("failed to update role", err)
	}
	return nil
}

// DeleteRole soft-deletes a role
func (r *PermissionRepository) DeleteRole(ctx context.Context, id string) error {
	updates := map[string]interface{}{
		constants.FieldIsDeleted:        1,
		constants.FieldLastModifiedDate: time.Now(),
	}
	qr := query.Update(constants.TableUserRole).
		Set(updates).
		Where(constants.FieldID, id).
		Build()
	if err := r.execRoleMutationWithClosureRebuild(ctx, qr.SQL, qr.Params...); err != nil {
		return apperrors.NewInternalError("failed to delete role", err)
	}
	return nil
}

func (r *PermissionRepository) execRoleMutationWithClosureRebuild(ctx context.Context, statement string, args ...interface{}) error {
	if tx, ok := ctx.Value(txContextKey{}).(*sql.Tx); ok && tx != nil {
		if _, err := tx.ExecContext(ctx, statement, args...); err != nil {
			return err
		}
		return RebuildGroupMembershipClosure(ctx, tx)
	}

	return withGroupMembershipClosureLockedTx(ctx, r.db, func(txCtx context.Context, tx *sql.Tx) error {
		if _, err := tx.ExecContext(txCtx, statement, args...); err != nil {
			return err
		}
		return RebuildGroupMembershipClosure(txCtx, tx)
	})
}

// IsUserInGroup checks if a user is a member of a group
func (r *PermissionRepository) IsUserInGroup(ctx context.Context, groupID, userID string) (bool, error) {
	qr := query.From(constants.TableGroupMembershipClosure).
		AddSelectRaw("COUNT(*)").
		Where(constants.FieldSysGroupMembershipClosure_GroupID, groupID).
		Where(constants.FieldSysGroupMembershipClosure_MemberID, userID).
		SkipID().
		Build()

	var count int
	if err := r.getExecutor(ctx).QueryRowContext(ctx, qr.SQL, qr.Params...).Scan(&count); err != nil {
		return false, apperrors.NewInternalError("failed to check group membership", err)
	}
	return count > 0, nil
}

// GetManualShareAccessLevels retrieves access levels granted via manual sharing rules
func (r *PermissionRepository) GetManualShareAccessLevels(ctx context.Context, objectAPIName, recordID, userID string) ([]string, error) {
	subQuery := fmt.Sprintf(
		"SELECT %s FROM %s WHERE %s = ? AND %s = ?",
		constants.FieldSysGroupMembershipClosure_GroupID,
		constants.TableGroupMembershipClosure,
		constants.FieldSysGroupMembershipClosure_MemberType,
		constants.FieldSysGroupMembershipClosure_MemberID,
	)
	complexCondition := fmt.Sprintf("(%s = ? OR %s IN (%s))", constants.FieldSysRecordShare_ShareWithUserID, constants.FieldSysRecordShare_ShareWithGroupID, subQuery)

	qr := query.From(constants.TableRecordShare).
		SkipID().
		Select([]string{constants.FieldSysRecordShare_AccessLevel}).
		Where(constants.FieldSysRecordShare_ObjectAPIName, objectAPIName).
		Where(constants.FieldRecordID, recordID).
		Where(constants.FieldIsDeleted, 0).
		WhereRaw(complexCondition, []interface{}{userID, constants.GroupMembershipClosureMemberTypeUser, userID}).
		Build()

	rows, err := r.getExecutor(ctx).QueryContext(ctx, qr.SQL, qr.Params...)
	if err != nil {
		return nil, apperrors.NewInternalError("failed to query manual shares", err)
	}
	defer rows.Close()

	var levels []string
	for rows.Next() {
		var level string
		if err := rows.Scan(&level); err != nil {
			return nil, apperrors.NewInternalError("failed to scan manual share level", err)
		}
		levels = append(levels, level)
	}
	if err := rows.Err(); err != nil {
		return nil, apperrors.NewInternalError("error iterating manual shares", err)
	}
	return levels, nil
}

// GetTeamMemberAccessLevel retrieves the access level for a user in a record team
func (r *PermissionRepository) GetTeamMemberAccessLevel(ctx context.Context, objectAPIName, recordID, userID string) (*string, error) {
	qr := query.From(constants.TableTeamMember).
		SkipID().
		Select([]string{constants.FieldSysTeamMember_AccessLevel}).
		Where(constants.FieldSysTeamMember_ObjectAPIName, objectAPIName).
		Where(constants.FieldRecordID, recordID).
		Where(constants.FieldUserID, userID).
		Where(constants.FieldIsDeleted, 0).
		Build()

	var accessLevel string
	err := r.getExecutor(ctx).QueryRowContext(ctx, qr.SQL, qr.Params...).Scan(&accessLevel)
	if err != nil {
		if err == sql.ErrNoRows {
			return nil, nil
		}
		return nil, apperrors.NewInternalError("failed to query team member access", err)
	}
	return &accessLevel, nil
}

// GetManualShareAccessLevelsByRecordIDs retrieves manual-share access levels for
// a bounded set of candidate records. It intentionally keeps RecordId scoped to
// the caller's current page/batch so hot read paths do not preload all shares for
// an object.
func (r *PermissionRepository) GetManualShareAccessLevelsByRecordIDs(ctx context.Context, objectAPIName, userID string, recordIDs []string) (map[string][]string, error) {
	if len(recordIDs) == 0 {
		return map[string][]string{}, nil
	}
	placeholders := make([]string, len(recordIDs))
	args := make([]interface{}, 0, len(recordIDs)+4)
	args = append(args, objectAPIName)
	for i, recordID := range recordIDs {
		placeholders[i] = "?"
		args = append(args, recordID)
	}
	args = append(args, userID, constants.GroupMembershipClosureMemberTypeUser, userID)

	sqlStr := fmt.Sprintf(
		"SELECT `RecordId`, `AccessLevel` FROM `_System_RecordShare` WHERE `ObjectApiName` = ? AND `RecordId` IN (%s) AND (`ShareWithUserId` = ? OR `ShareWithGroupId` IN (SELECT `GroupId` FROM `_System_GroupMembershipClosure` WHERE `MemberType` = ? AND `MemberId` = ?)) AND (`IsDeleted` = 0 OR `IsDeleted` IS NULL)",
		strings.Join(placeholders, ","),
	)
	rows, err := r.getExecutor(ctx).QueryContext(ctx, sqlStr, args...)
	if err != nil {
		return nil, apperrors.NewInternalError("failed to query manual shares by record IDs", err)
	}
	defer rows.Close()

	levelsByID := make(map[string][]string)
	for rows.Next() {
		var recordID, level string
		if err := rows.Scan(&recordID, &level); err != nil {
			return nil, apperrors.NewInternalError("failed to scan manual share by record ID", err)
		}
		levelsByID[recordID] = append(levelsByID[recordID], level)
	}
	if err := rows.Err(); err != nil {
		return nil, apperrors.NewInternalError("error iterating manual shares by record IDs", err)
	}
	return levelsByID, nil
}

// GetTeamMemberAccessLevelsByRecordIDs retrieves team-member access levels for a
// bounded set of candidate records. The returned map omits records with no team
// membership row for the user.
func (r *PermissionRepository) GetTeamMemberAccessLevelsByRecordIDs(ctx context.Context, objectAPIName, userID string, recordIDs []string) (map[string]string, error) {
	if len(recordIDs) == 0 {
		return map[string]string{}, nil
	}
	placeholders := make([]string, len(recordIDs))
	args := make([]interface{}, 0, len(recordIDs)+2)
	args = append(args, objectAPIName, userID)
	for i, recordID := range recordIDs {
		placeholders[i] = "?"
		args = append(args, recordID)
	}

	sqlStr := fmt.Sprintf(
		"SELECT `RecordId`, `AccessLevel` FROM `_System_TeamMember` WHERE `ObjectApiName` = ? AND `UserId` = ? AND `RecordId` IN (%s) AND (`IsDeleted` = 0 OR `IsDeleted` IS NULL)",
		strings.Join(placeholders, ","),
	)
	rows, err := r.getExecutor(ctx).QueryContext(ctx, sqlStr, args...)
	if err != nil {
		return nil, apperrors.NewInternalError("failed to query team member access by record IDs", err)
	}
	defer rows.Close()

	levelsByID := make(map[string]string)
	for rows.Next() {
		var recordID, level string
		if err := rows.Scan(&recordID, &level); err != nil {
			return nil, apperrors.NewInternalError("failed to scan team member access by record ID", err)
		}
		levelsByID[recordID] = level
	}
	if err := rows.Err(); err != nil {
		return nil, apperrors.NewInternalError("error iterating team member access by record IDs", err)
	}
	return levelsByID, nil
}

// GetUserGroupIDs returns all direct and transitive Group IDs the user belongs to.
func (r *PermissionRepository) GetUserGroupIDs(ctx context.Context, userID string) ([]string, error) {
	rows, err := r.getExecutor(ctx).QueryContext(ctx,
		"SELECT DISTINCT `GroupId` FROM `_System_GroupMembershipClosure` WHERE `MemberType` = ? AND `MemberId` = ?",
		constants.GroupMembershipClosureMemberTypeUser, userID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var ids []string
	for rows.Next() {
		var id string
		if err := rows.Scan(&id); err == nil {
			ids = append(ids, id)
		}
	}
	return ids, rows.Err()
}

// GetManualShareRecordIDs reports whether the user has any manual share for the
// object: it returns at most one RecordId (LIMIT 1), used purely as an existence
// probe so RLS can drop the manual-share semi-join arm when the user has none
// (issue #2585). It is NOT a full enumeration of shared records.
func (r *PermissionRepository) GetManualShareRecordIDs(ctx context.Context, objectAPIName, userID string) ([]string, error) {
	rows, err := r.getExecutor(ctx).QueryContext(ctx,
		"SELECT `RecordId` FROM `_System_RecordShare` WHERE `ObjectApiName` = ? AND (`ShareWithUserId` = ? OR `ShareWithGroupId` IN (SELECT `GroupId` FROM `_System_GroupMembershipClosure` WHERE `MemberType` = ? AND `MemberId` = ?)) AND (`IsDeleted` = 0 OR `IsDeleted` IS NULL) LIMIT 1",
		objectAPIName, userID, constants.GroupMembershipClosureMemberTypeUser, userID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var ids []string
	for rows.Next() {
		var id string
		if err := rows.Scan(&id); err == nil {
			ids = append(ids, id)
		}
	}
	return ids, rows.Err()
}

// GetTeamMemberRecordIDs reports whether the user is on any record's team for the
// object: at most one RecordId (LIMIT 1), an existence probe like
// GetManualShareRecordIDs (issue #2585) — not a full enumeration.
func (r *PermissionRepository) GetTeamMemberRecordIDs(ctx context.Context, objectAPIName, userID string) ([]string, error) {
	rows, err := r.getExecutor(ctx).QueryContext(ctx,
		"SELECT `RecordId` FROM `_System_TeamMember` WHERE `ObjectApiName` = ? AND `UserId` = ? AND (`IsDeleted` = 0 OR `IsDeleted` IS NULL) LIMIT 1",
		objectAPIName, userID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var ids []string
	for rows.Next() {
		var id string
		if err := rows.Scan(&id); err == nil {
			ids = append(ids, id)
		}
	}
	return ids, rows.Err()
}

// GetGroupMemberIDs reports whether the given groups have any member at all: at
// most one MemberId (LIMIT 1), an existence probe so RLS can drop the
// group-from-owner semi-join arm when none of the shared-from groups has members
// (issue #2585) — not a full enumeration of members.
func (r *PermissionRepository) GetGroupMemberIDs(ctx context.Context, groupIDs []string) ([]string, error) {
	if len(groupIDs) == 0 {
		return nil, nil
	}
	placeholders := make([]string, len(groupIDs))
	args := make([]interface{}, len(groupIDs))
	for i, gid := range groupIDs {
		placeholders[i] = "?"
		args[i] = gid
	}
	query := fmt.Sprintf(
		"SELECT `MemberId` FROM `_System_GroupMembershipClosure` WHERE `GroupId` IN (%s) LIMIT 1",
		strings.Join(placeholders, ", "))
	rows, err := r.getExecutor(ctx).QueryContext(ctx, query, args...)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var ids []string
	for rows.Next() {
		var id string
		if err := rows.Scan(&id); err == nil {
			ids = append(ids, id)
		}
	}
	return ids, rows.Err()
}

// GetRecordOwnerID retrieves the OwnerId of a record by table name and record ID.
// Used by ControlledByParent access checks to resolve parent record ownership.
func (r *PermissionRepository) GetRecordOwnerID(ctx context.Context, tableName, recordID string) (string, error) {
	sqlStr := fmt.Sprintf("SELECT `OwnerId` FROM `%s` WHERE `Id` = ? AND (`IsDeleted` = 0 OR `IsDeleted` IS NULL) LIMIT 1",
		tableName)

	var ownerID string
	err := r.getExecutor(ctx).QueryRowContext(ctx, sqlStr, recordID).Scan(&ownerID)
	if err != nil {
		return "", err
	}
	return ownerID, nil
}

// GetRecordByID retrieves a full record as SObject by table name and record ID.
// Used by ControlledByParent access checks that need the full parent record
// (criteria-based sharing rules evaluate formula expressions against record fields).
func (r *PermissionRepository) GetRecordByID(ctx context.Context, tableName, recordID string) (models.SObject, error) {
	sqlStr := fmt.Sprintf("SELECT * FROM `%s` WHERE `Id` = ? AND (`IsDeleted` = 0 OR `IsDeleted` IS NULL) LIMIT 1",
		tableName)

	rows, err := r.getExecutor(ctx).QueryContext(ctx, sqlStr, recordID)
	if err != nil {
		return nil, apperrors.NewInternalError(fmt.Sprintf("get record from %s", tableName), err)
	}
	defer rows.Close()

	columns, err := rows.Columns()
	if err != nil {
		return nil, apperrors.NewInternalError("get columns", err)
	}

	if !rows.Next() {
		return nil, sql.ErrNoRows
	}

	values := make([]interface{}, len(columns))
	valuePtrs := make([]interface{}, len(columns))
	for i := range values {
		valuePtrs[i] = &values[i]
	}

	if err := rows.Scan(valuePtrs...); err != nil {
		return nil, apperrors.NewInternalError("scan record", err)
	}

	record := make(models.SObject, len(columns))
	for i, col := range columns {
		val := values[i]
		// Convert []byte to string for readability and formula evaluation
		if b, ok := val.([]byte); ok {
			record[col] = string(b)
		} else {
			record[col] = val
		}
	}

	return record, nil
}