# Install required packages
install.packages("RecordLinkage")

# Load required packages
library(dplyr)
library(stringdist)
library(RecordLinkage)

# Create sample customer data with realistic duplicates
customer_data <- data.frame(
  CustomerID = 1:20,
  FirstName = c("John", "Jane", "Robert", "John", "Mary", "Jane", 
                "Bob", "Mary", "Michael", "Sarah", "John", "Robert",
                "J", "Mike", "Sara", "Jane", "Rob", "Mary", "John", "Michael"),
  LastName = c("Smith", "Johnson", "Brown", "Smith", "Davis", "Doe",
               "Brown", "Davis", "Wilson", "Miller", "Smith", "Brown",
               "Doe", "Wilson", "Miller", "Johnson", "Brown", "Davis", "Smith", "Wilson"),
  Email = c("john.smith@email.com", "jane.johnson@email.com", "rbrown@email.com",
            "j.smith@email.com", "mary.davis@email.com", "jane.doe@email.com",
            "bob.brown@email.com", "m.davis@email.com", "mwilson@email.com",
            "sarah.miller@email.com", "johnsmith@email.com", "robert.brown@email.com",
            "j.doe@email.com", "mike.wilson@email.com", "s.miller@email.com",
            "jane.johnson@email.com", "r.brown@email.com", "mary.davis@email.com",
            "john.smith@email.com", "michael.wilson@email.com"),
  Phone = c("555-1234", "555-5678", "555-9012", "555-1234", "555-3456",
            "555-7890", "555-9012", "555-3456", "555-2468", "555-1357",
            "(555) 123-4", "5559012", "555-7890", "555-2468", "555-1357",
            "555-5678", "555-9012", "555-3456", "555-1234", "555-2468"),
  City = c("New York", "Boston", "Chicago", "New York", "Miami", "Seattle",
           "Chicago", "Miami", "Denver", "Portland", "NYC", "Chicago",
           "Seattle", "Denver", "Portland", "Boston", "Chicago", "Miami",
           "New York", "Denver"),
  stringsAsFactors = FALSE
)

# Display the dataset
print("Customer Database with Duplicates:")
print(customer_data)

# Dataset summary
cat("\nDataset Summary:\n")
cat("Total records:", nrow(customer_data), "\n")
cat("Unique first names:", length(unique(customer_data$FirstName)), "\n")
cat("Unique last names:", length(unique(customer_data$LastName)), "\n")
cat("Unique email addresses:", length(unique(customer_data$Email)), "\n")

# ---------------------------------------------------
# Step 2: Exact Duplicate Detection
# ---------------------------------------------------

# Method 1: Exact duplicates across all columns
exact_duplicates <- customer_data[
  duplicated(customer_data) |
    duplicated(customer_data, fromLast = TRUE),
]

cat("Exact Duplicates Found:\n")
if(nrow(exact_duplicates) > 0) {
  print(exact_duplicates)
} else {
  cat("No exact duplicates found.\n")
}

# Method 2: Duplicates based on key fields
key_duplicates <- customer_data[
  duplicated(customer_data[c("FirstName", "LastName", "Email")]) |
    duplicated(customer_data[c("FirstName", "LastName", "Email")], fromLast = TRUE),
]

cat("\nDuplicates based on Name + Email:\n")
if(nrow(key_duplicates) > 0) {
  print(key_duplicates[order(key_duplicates$FirstName,
                             key_duplicates$LastName), ])
} else {
  cat("No key-based duplicates found.\n")
}

# Method 3: Count duplicate name patterns
duplicate_counts <- customer_data %>%
  group_by(FirstName, LastName) %>%
  summarise(count = n(), .groups = "drop") %>%
  filter(count > 1) %>%
  arrange(desc(count))

cat("\nName combinations appearing multiple times:\n")
print(duplicate_counts)

# ---------------------------------------------------
# Step 3: Near Duplicate Detection
# ---------------------------------------------------

# Function to calculate similarity
calculate_similarity <- function(str1, str2) {
  stringdist(str1, str2, method = "jw")
}

# Create all combinations of records
name_combinations <- expand.grid(
  i = 1:nrow(customer_data),
  j = 1:nrow(customer_data)
)

name_combinations <- name_combinations[
  name_combinations$i < name_combinations$j,
]

# Calculate similarities
similarities <- data.frame(
  Record1 = name_combinations$i,
  Record2 = name_combinations$j,
  Name1 = paste(customer_data$FirstName[name_combinations$i],
                customer_data$LastName[name_combinations$i]),
  Name2 = paste(customer_data$FirstName[name_combinations$j],
                customer_data$LastName[name_combinations$j]),
  FirstName_Similarity = 1 - mapply(
    calculate_similarity,
    customer_data$FirstName[name_combinations$i],
    customer_data$FirstName[name_combinations$j]
  ),
  LastName_Similarity = 1 - mapply(
    calculate_similarity,
    customer_data$LastName[name_combinations$i],
    customer_data$LastName[name_combinations$j]
  ),
  stringsAsFactors = FALSE
)

# Find likely near duplicates
threshold <- 0.85

near_duplicates <- similarities[
  (similarities$FirstName_Similarity > threshold &
     similarities$LastName_Similarity > threshold) |
    (similarities$FirstName_Similarity > 0.95 |
       similarities$LastName_Similarity > 0.95),
]

cat("Potential Near Duplicates (Similarity > 85%):\n")

if(nrow(near_duplicates) > 0) {
  print(near_duplicates[
    order(-near_duplicates$FirstName_Similarity),
  ])
} else {
  cat("No near duplicates found.\n")
}

# Show detailed comparisons
if(nrow(near_duplicates) > 0) {
  
  cat("\nDetailed comparison of potential duplicates:\n")
  
  for(i in 1:min(3, nrow(near_duplicates))) {
    
    cat("\n--- Potential Match", i, "---\n")
    
    rec1 <- customer_data[near_duplicates$Record1[i], ]
    rec2 <- customer_data[near_duplicates$Record2[i], ]
    
    cat("Record", near_duplicates$Record1[i], ":",
        rec1$FirstName, rec1$LastName,
        "|", rec1$Email,
        "|", rec1$Phone, "\n")
    
    cat("Record", near_duplicates$Record2[i], ":",
        rec2$FirstName, rec2$LastName,
        "|", rec2$Email,
        "|", rec2$Phone, "\n")
    
    cat("First Name Similarity:",
        round(near_duplicates$FirstName_Similarity[i], 3), "\n")
    
    cat("Last Name Similarity:",
        round(near_duplicates$LastName_Similarity[i], 3), "\n")
  }
}

# ---------------------------------------------------
# Step 4: Advanced Pattern Detection
# ---------------------------------------------------

# Standardize phone numbers
standardize_phone <- function(phone) {
  gsub("[^0-9]", "", phone)
}

# Standardize city names
standardize_city <- function(city) {
  
  city_mapping <- c(
    "NYC" = "New York",
    "LA" = "Los Angeles",
    "SF" = "San Francisco"
  )
  
  standardized <- city_mapping[city]
  
  ifelse(is.na(standardized), city, standardized)
}

# Create cleaned dataset
customer_clean <- customer_data %>%
  mutate(
    FirstName_Clean = tolower(trimws(FirstName)),
    LastName_Clean = tolower(trimws(LastName)),
    Phone_Clean = standardize_phone(Phone),
    City_Clean = standardize_city(City),
    Email_Clean = tolower(trimws(Email))
  )

# Duplicate detection using cleaned names
multi_criteria_duplicates <- customer_clean %>%
  group_by(FirstName_Clean, LastName_Clean) %>%
  filter(n() > 1) %>%
  arrange(FirstName_Clean, LastName_Clean, CustomerID)

cat("Potential duplicates using standardized names:\n")

print(
  multi_criteria_duplicates[, c(
    "CustomerID",
    "FirstName",
    "LastName",
    "Email",
    "Phone",
    "City"
  )]
)

# Check for duplicate phone numbers
phone_duplicates <- customer_clean %>%
  group_by(Phone_Clean) %>%
  filter(n() > 1, Phone_Clean != "") %>%
  arrange(Phone_Clean, CustomerID)

cat("\nRecords sharing the same phone number:\n")

if(nrow(phone_duplicates) > 0) {
  
  print(
    phone_duplicates[, c(
      "CustomerID",
      "FirstName",
      "LastName",
      "Phone",
      "Phone_Clean"
    )]
  )
  
} else {
  
  cat("No phone number duplicates found.\n")
}

# Summary
cat("\n=== DUPLICATE ANALYSIS SUMMARY ===\n")

cat("Total records in database:",
    nrow(customer_data), "\n")

cat("Records with duplicate names:",
    nrow(multi_criteria_duplicates), "\n")

cat("Records with duplicate phone numbers:",
    nrow(phone_duplicates), "\n")

cat(
  "Estimated unique customers:",
  nrow(customer_data) -
    nrow(multi_criteria_duplicates) +
    length(unique(
      paste(
        multi_criteria_duplicates$FirstName_Clean,
        multi_criteria_duplicates$LastName_Clean
      )
    )),
  "\n"
)

# ---------------------------------------------------
# Practice Dataset
# ---------------------------------------------------

library(dplyr)
library(stringdist)

set.seed(42)

practice_data <- data.frame(
  CustomerID = 1:25,
  FirstName = c("Anna", "Anne", "A.", "John", "Jon", "J.", "Maria", "Marie",
                "M.", "Chris", "Kris", "Christopher", "Sara", "Sarah", "S.",
                "David", "Dave", "D.", "Lily", "Lilly", "Li", "Mark", "Marc",
                "M.", "John"),
  LastName = c("Lee", "Lee", "Li", "Smith", "Smith", "Smith", "Garcia", "Garcia",
               "Garcia", "Brown", "Brown", "Brown", "Miller", "Miller", "Miller",
               "Davis", "Davis", "Davis", "Wilson", "Wilson", "Wilson",
               "Taylor", "Taylor", "Taylor", "Smith"),
  Email = c("anna.lee@email.com", "anne.lee@email.com", "a.lee@email.com",
            "john.smith@email.com", "jon.smith@email.com", "j.smith@email.com",
            "maria.garcia@email.com", "marie.garcia@email.com",
            "m.garcia@email.com", "chris.brown@email.com",
            "kris.brown@email.com", "christopher.brown@email.com",
            "sara.miller@email.com", "sarah.miller@email.com",
            "s.miller@email.com", "david.davis@email.com",
            "dave.davis@email.com", "d.davis@email.com",
            "lily.wilson@email.com", "lilly.wilson@email.com",
            "li.wilson@email.com", "mark.taylor@email.com",
            "marc.taylor@email.com", "m.taylor@email.com",
            "johnsmith@email.com"),
  Phone = c("555-1111", "5551111", "(555) 1111",
            "555-2222", "5552222", "(555) 2222",
            "555-3333", "5553333", "(555) 3333",
            "555-4444", "5554444", "(555) 4444",
            "555-5555", "5555555", "(555) 5555",
            "555-6666", "5556666", "(555) 6666",
            "555-7777", "5557777", "(555) 7777",
            "555-8888", "5558888", "(555) 8888",
            "555-2222"),
  City = c("New York", "NYC", "New York",
           "Boston", "Boston", "BOS",
           "Miami", "Miami", "MIA",
           "Chicago", "Chicago", "CHI",
           "Seattle", "Seattle", "SEA",
           "Denver", "Denver", "DEN",
           "Portland", "Portland", "PDX",
           "New York", "NYC", "New York",
           "Boston"),
  stringsAsFactors = FALSE
)

practice_data