package persistence

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

	apperrors "github.com/nexuscrm/backend/pkg/errors"
	"github.com/nexuscrm/backend/pkg/query"
	"github.com/nexuscrm/backend/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.getExecutor(ctx).ExecContext(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) {
	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()

	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.getExecutor(ctx).ExecContext(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.getExecutor(ctx).ExecContext(ctx, qr.SQL, qr.Params...); err != nil {
		return apperrors.NewInternalError("failed to delete role", err)
	}
	return nil
}

// 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.TableGroupMember).
		AddSelectRaw("COUNT(*)").
		Where(constants.FieldGroupMember_GroupID, groupID).
		Where(constants.FieldGroupMember_UserOrGroupID, 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 = ?", constants.FieldGroupMember_GroupID, constants.TableGroupMember, constants.FieldGroupMember_UserOrGroupID)
	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.FieldObjectPermissions_SobjectType, objectAPIName).
		Where(constants.FieldRecordID, recordID).
		Where(constants.FieldIsDeleted, 0).
		WhereRaw(complexCondition, []interface{}{userID, 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.FieldObjectPermissions_SobjectType, 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
}

// GetUserGroupIDs returns all Group IDs the user belongs to via GroupMember.
func (r *PermissionRepository) GetUserGroupIDs(ctx context.Context, userID string) ([]string, error) {
	rows, err := r.getExecutor(ctx).QueryContext(ctx, "SELECT `GroupId` FROM `GroupMember` WHERE `UserOrGroupId` = ? AND (`IsDeleted` = 0 OR `IsDeleted` IS NULL)", 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 returns record IDs shared with the user via _System_RecordShare.
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 `GroupMember` WHERE `UserOrGroupId` = ?)) AND (`IsDeleted` = 0 OR `IsDeleted` IS NULL)",
		objectAPIName, userID, 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 returns record IDs where the user is a team member.
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)",
		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()
}

// 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
}