package repository

import (
	"authorization/db"
	"authorization/models"
	"database/sql"
	"errors"
	"fmt"
	"log"
)

var ErrUserDeleted = errors.New("user is deleted")

func GetPermissionByResourceActionAndRole(resource, action string, roleID int) (*models.Permission, error) {
	log.Printf("[Repository] GetPermissionByResourceActionAndRole - resource=%s, action=%s, roleID=%d",
		resource, action, roleID)

	query := `
		       SELECT p.permissions_id, p.permission_name, p.description, p.resource, p.action
		       FROM uess_user_management.permissions p
		       INNER JOIN uess_user_management.role_permissions rp
		       ON p.permissions_id = rp.permission_id
		       WHERE p.resource = ? AND p.action = ? AND rp.role_id = ? AND rp.is_deleted = 0
		       LIMIT 1
	       `

	var perm models.Permission
	err := db.DB.QueryRow(query, resource, action, roleID).Scan(
		&perm.ID,
		&perm.PermissionName,
		&perm.Description,
		&perm.Resource,
		&perm.Action,
	)

	if err != nil {
		if err == sql.ErrNoRows {
			log.Printf("[Repository] ✗ No permission found for resource=%s, action=%s, roleID=%d",
				resource, action, roleID)
			return nil, fmt.Errorf("permission not found or not granted to role_id=%d for resource=%s, action=%s", roleID, resource, action)
		}
		log.Printf("[Repository] ✗ Database error querying permission: %v", err)
		return nil, fmt.Errorf("error querying permission: %w", err)
	}

	log.Printf("[Repository] ✓ Permission found: ID=%d, Name=%s", perm.ID, perm.PermissionName)
	return &perm, nil
}

// GetPolicyAttributesByPermission retrieves all policy attributes for a permission
func GetPolicyAttributesByPermission(permissionID int) ([]models.PolicyAttribute, error) {
	query := `
		       SELECT id, attribute_name, attribute_type, comparison, attribute_value, permission_id
		       FROM uess_user_management.policy_attributes
		       WHERE permission_id = ?
	       `

	rows, err := db.DB.Query(query, permissionID)
	if err != nil {
		return nil, fmt.Errorf("error querying policy attributes: %w", err)
	}
	defer rows.Close()

	var attributes []models.PolicyAttribute
	for rows.Next() {
		var attr models.PolicyAttribute
		err := rows.Scan(
			&attr.ID,
			&attr.AttributeName,
			&attr.AttributeType,
			&attr.Comparison,
			&attr.AttributeValue,
			&attr.PermissionID,
		)
		if err != nil {
			return nil, fmt.Errorf("error scanning policy attribute: %w", err)
		}
		attributes = append(attributes, attr)
	}

	return attributes, nil
}

// GetUserAttributes retrieves all attributes for a user
func GetUserAttributes(userID string) (map[string]string, error) {
	log.Printf("[Repository] GetUserAttributes - userID=%s", userID)

	query := `
			   SELECT attribute_name, attribute_value
			   FROM uess_user_management.user_attributes
			   WHERE users_id = ?

			   UNION

			   SELECT 'region' AS attribute_name, o.reg AS attribute_value
			   FROM  uess_reference.office o
			   LEFT JOIN  uess_user_management.users u
				   ON o.id = u.office_id
			   WHERE u.users_id = ?
	   `

	rows, err := db.DB.Query(query, userID, userID)
	if err != nil {
		log.Printf("[Repository] ✗ Database error querying user attributes: %v", err)
		return nil, fmt.Errorf("error querying user attributes: %w", err)
	}
	defer rows.Close()

	attributes := make(map[string]string)
	for rows.Next() {
		var name string
		var value *string
		err := rows.Scan(&name, &value)
		if err != nil {
			log.Printf("[Repository] ✗ Error scanning user attribute: %v", err)
			return nil, fmt.Errorf("error scanning user attribute: %w", err)
		}
		if value != nil {
			attributes[name] = *value
		} else {
			attributes[name] = ""
		}
	}

	log.Printf("[Repository] ✓ Retrieved %d user attributes", len(attributes))
	return attributes, nil
}

// GetUserByID retrieves user details
func GetUserByID(userID string) (*models.User, error) {
	log.Printf("[Repository] GetUserByID - userID=%s", userID)

	query := `
		       SELECT u.users_id, u.email_address, COALESCE(MIN(ur.role_id), 0) AS role_id, u.is_deleted
		       FROM uess_user_management.users u
		       LEFT JOIN uess_user_management.user_roles ur
		       ON ur.users_id = u.users_id AND u.is_deleted = 0
		       WHERE u.users_id = ?
		       GROUP BY u.users_id, u.email_address, u.is_deleted
	       `

	var user models.User
	err := db.DB.QueryRow(query, userID).Scan(&user.UsersID, &user.EmailAddress, &user.RoleID, &user.IsDeleted)
	if err != nil {
		if err == sql.ErrNoRows {
			log.Printf("[Repository] ✗ User not found: %s", userID)
			return nil, fmt.Errorf("user not found: %s", userID)
		}
		log.Printf("[Repository] ✗ Database error querying user: %v", err)
		return nil, fmt.Errorf("error querying user: %w", err)
	}

	if user.IsDeleted == "1" {
		log.Printf("[Repository] ✗ User is deleted: %s", userID)
		return nil, ErrUserDeleted
	}

	log.Printf("[Repository] ✓ User found: UsersID=%s", user.UsersID)
	return &user, nil
}

// GetAllPermissions retrieves all permissions (for caching)
func GetAllPermissions() ([]models.Permission, error) {
	query := `
		       SELECT id, permission_name, description, resource, action
		       FROM  uess_user_management.permissions
		       ORDER BY id
	       `

	rows, err := db.DB.Query(query)
	if err != nil {
		return nil, fmt.Errorf("error querying all permissions: %w", err)
	}
	defer rows.Close()

	var permissions []models.Permission
	for rows.Next() {
		var perm models.Permission
		err := rows.Scan(
			&perm.ID,
			&perm.PermissionName,
			&perm.Description,
			&perm.Resource,
			&perm.Action,
		)
		if err != nil {
			return nil, fmt.Errorf("error scanning permission: %w", err)
		}
		permissions = append(permissions, perm)
	}

	return permissions, nil
}

// GetAllPolicyAttributes retrieves all policy attributes (for caching)
func GetAllPolicyAttributes() (map[int][]models.PolicyAttribute, error) {
	query := `
		       SELECT policy_attributes_id, attribute_name, attribute_type, comparison, attribute_value, permission_id
		       FROM  uess_user_management.policy_attributes
		       ORDER BY permission_id, policy_attributes_id
	       `

	rows, err := db.DB.Query(query)
	if err != nil {
		return nil, fmt.Errorf("error querying all policy attributes: %w", err)
	}
	defer rows.Close()

	attributesByPermission := make(map[int][]models.PolicyAttribute)
	for rows.Next() {
		var attr models.PolicyAttribute
		err := rows.Scan(
			&attr.ID,
			&attr.AttributeName,
			&attr.AttributeType,
			&attr.Comparison,
			&attr.AttributeValue,
			&attr.PermissionID,
		)
		if err != nil {
			return nil, fmt.Errorf("error scanning policy attribute: %w", err)
		}
		attributesByPermission[attr.PermissionID] = append(attributesByPermission[attr.PermissionID], attr)
	}

	return attributesByPermission, nil
}

// Helper function to parse IN clause values
// func parseINValues(value string) []string {
// 	// Remove spaces and split by comma
// 	value = strings.ReplaceAll(value, " ", "")
// 	return strings.Split(value, ",")
// }