df_csv <- read_csv("ecommerce.csv", show_col_types = FALSE)
cat("File: ecommerce.csv\n")

cat("Jumlah baris :", nrow(df_csv), "\n")

cat("Jumlah kolom :", ncol(df_csv), "\n")

cat("Nama kolom   :", paste(names(df_csv), collapse = ", "), "\n")

df_excel <- read_excel("ecommerce.xlsx")
cat("File: ecommerce.xlsx\n")

cat("Jumlah baris :", nrow(df_excel), "\n")

cat("Jumlah kolom :", ncol(df_excel), "\n")

cat("Nama kolom   :", paste(names(df_excel), collapse = ", "), "\n")

df_json <- fromJSON("ecommerce.json") |> as.data.frame()
cat("File: ecommerce.json\n")

cat("Jumlah baris :", nrow(df_json), "\n")

cat("Jumlah kolom :", ncol(df_json), "\n")

cat("Nama kolom   :", paste(names(df_json), collapse = ", "), "\n")

df_txt <- read_delim("ecommerce.txt", delim = "|", show_col_types = FALSE)
cat("File: ecommerce.txt\n")

cat("Jumlah baris :", nrow(df_txt), "\n")

cat("Jumlah kolom :", ncol(df_txt), "\n")

cat("Nama kolom   :", paste(names(df_txt), collapse = ", "), "\n")

xml_doc <- read_xml("ecommerce.xml")
records <- xml_find_all(xml_doc, ".//Record")

xml_ke_df <- function(rec) {
  anak  <- xml_children(rec)
  setNames(as.list(xml_text(anak)), xml_name(anak))
}

df_xml <- bind_rows(lapply(records, xml_ke_df))
cat("File: ecommerce.xml\n")

cat("Jumlah baris :", nrow(df_xml), "\n")

cat("Jumlah kolom :", ncol(df_xml), "\n")

cat("Nama kolom   :", paste(names(df_xml), collapse = ", "), "\n")

semua_df <- list(
  CSV   = df_csv   |> mutate(across(everything(), as.character)),
  Excel = df_excel |> mutate(across(everything(), as.character)),
  JSON  = df_json  |> mutate(across(everything(), as.character)),
  TXT   = df_txt   |> mutate(across(everything(), as.character)),
  XML   = df_xml   |> mutate(across(everything(), as.character))
)

kolom_referensi <- names(semua_df[["CSV"]])
siap_merge      <- list()

cat("=== Pengecekan Struktur Kolom ===\n\n")

for (nama in names(semua_df)) {
  df_cek <- semua_df[[nama]]
  if (setequal(names(df_cek), kolom_referensi)) {
    cat(nama, ": Struktur kolom sesuai -> Ready to merge\n")
    siap_merge[[nama]] <- df_cek[, kolom_referensi]
  } else {
    cat(nama, ": Perlu penyesuaian -> Need adjustment (disesuaikan otomatis)\n")
    kolom_hilang <- setdiff(kolom_referensi, names(df_cek))
    for (kol in kolom_hilang) df_cek[[kol]] <- NA
    siap_merge[[nama]] <- df_cek[, kolom_referensi]
  }
}

df_gabungan <- bind_rows(siap_merge)
cat(sprintf("\nDataset utama berhasil digabungkan: %d baris, %d kolom\n",
            nrow(df_gabungan), ncol(df_gabungan)))

cat("Jumlah Total Baris :", nrow(df_gabungan), "\n")

cat("Jumlah Total Kolom :", ncol(df_gabungan), "\n\n")

tipe <- data.frame(
  No    = 1:ncol(df_gabungan),
  Kolom = names(df_gabungan),
  Tipe  = sapply(df_gabungan, class),
  row.names = NULL
)

kable(tipe, caption = "Tipe Data Setiap Kolom") |>
  kable_styling(bootstrap_options = c("striped","hover","condensed"), full_width = FALSE) |>
  column_spec(2, bold = TRUE, color = "#2d6a4f") |>
  column_spec(3, background = "#f0faf3")

mv <- df_gabungan |>
  summarise(across(everything(),
    ~ sum(is.na(.) | . == "" | . == "NULL"))) |>
  pivot_longer(everything(), names_to = "Kolom", values_to = "Jumlah_NA") |>
  filter(Jumlah_NA > 0) |>
  arrange(desc(Jumlah_NA)) |>
  mutate(Persen = round(Jumlah_NA / nrow(df_gabungan) * 100, 2))

kable(mv, col.names = c("Kolom","Jumlah Missing","Persentase (%)"),
      caption = "Kolom dengan Missing Values") |>
  kable_styling(bootstrap_options = c("striped","hover"), full_width = FALSE) |>
  column_spec(3, bold = TRUE,
              color = ifelse(mv$Persen > 20, "#e76f51", "#2d6a4f"))

dup <- sum(duplicated(df_gabungan))
pct <- round(dup / nrow(df_gabungan) * 100, 2)
cat("Jumlah baris duplikat:", dup, "\n")

cat("Persentase duplikat  :", pct, "%\n")

masalah <- data.frame(
  No      = 1:5,
  Masalah = c(
    "Format tanggal tidak konsisten",
    "Nilai harga berformat string (Rp xxx.xxx)",
    "Inkonsistensi penulisan platform",
    "Missing values di kolom penting",
    "Duplikasi data akibat penggabungan 5 file"
  ),
  Contoh = c(
    "2024-01-01, 01/01/2024, 01-01-2024",
    "'Rp 1.500.000' alih-alih 1500000",
    "'shopee', 'SHOPEE', ' shopee '",
    "ship_date, customer_rating, payment_method",
    paste0(dup, " baris duplikat dari ", nrow(df_gabungan), " baris")
  )
)

kable(masalah, col.names = c("No","Masalah","Contoh"),
      caption = "Minimal 3 Masalah Kualitas Data yang Ditemukan") |>
  kable_styling(bootstrap_options = c("striped","hover")) |>
  column_spec(2, bold = TRUE, color = "#e76f51")

df <- df_gabungan |> distinct(order_id, .keep_all = TRUE)
cat("Hapus duplikat berdasarkan order_id\n")

cat("Baris sebelum:", nrow(df_gabungan), "\n")

cat("Baris sesudah:", nrow(df), "\n")

cat("Baris dihapus:", nrow(df_gabungan) - nrow(df), "\n")

bersihkan_harga <- function(x) {
  x <- as.character(x)
  x <- str_replace_all(x, "Rp\\s*", "")
  x <- str_replace_all(x, "\\.", "")
  x <- str_replace_all(x, ",", ".")
  x <- str_trim(x)
  hasil <- suppressWarnings(as.numeric(x))
  ifelse(is.na(hasil) | hasil < 0, 0, hasil)
}

kolom_harga <- c("unit_price","gross_sales","net_sales","discount_value","shipping_cost")

cat("=== Membersihkan Kolom Harga dengan Looping ===\n\n")

for (kol in kolom_harga) {
  sebelum <- sum(is.na(df[[kol]]) | df[[kol]] == "")
  df[[kol]] <- bersihkan_harga(df[[kol]])
  cat(sprintf("  Kolom '%-18s' dibersihkan (NA sebelum: %d)\n", kol, sebelum))
}

df$discount_pct <- suppressWarnings(as.numeric(df$discount_pct))
df$discount_pct[is.na(df$discount_pct)] <- 0
df$quantity <- suppressWarnings(as.numeric(df$quantity))
cat("\nSemua kolom harga berhasil dikonversi ke numerik!\n")

standardisasi_platform <- function(p) {
  p <- str_trim(str_to_lower(as.character(p)))
  if      (p %in% c("shopee","shopee "))              return("Shopee")
  else if (p %in% c("tokopedia","tokped"))            return("Tokopedia")
  else if (p %in% c("lazada"))                        return("Lazada")
  else if (p %in% c("blibli"))                        return("Blibli")
  else if (p %in% c("tiktok shop","tiktok_shop"))     return("TikTok Shop")
  else                                                 return(str_to_title(p))
}

df$platform <- sapply(df$platform, standardisasi_platform)
cat("Platform distandarisasi\n")

print(table(df$platform))

df$payment_method <- ifelse(
  is.na(df$payment_method) | str_trim(df$payment_method) %in% c("","nan","NaN","NAN"),
  "Unknown",
  str_trim(df$payment_method)
)
cat("payment_method: NA -> 'Unknown'\n")

df$customer_rating <- suppressWarnings(as.numeric(df$customer_rating))
median_rating      <- median(df$customer_rating, na.rm = TRUE)
df$customer_rating <- ifelse(is.na(df$customer_rating), median_rating, df$customer_rating)
cat(sprintf("customer_rating: NA -> median = %.1f\n", median_rating))

cat("   (Logika: median lebih robust terhadap outlier)\n")

standardisasi_status <- function(s) {
  s <- str_trim(str_to_lower(as.character(s)))
  if      (s %in% c("delivered","completed","complete","completion")) return("Completed")
  else if (s %in% c("cancelled","cancel","batal","canceled"))         return("Cancelled")
  else if (s %in% c("returned","return","retur"))                     return("Returned")
  else if (s %in% c("shipped","shipping"))                            return("Shipped")
  else                                                                 return(str_to_title(s))
}
df$order_status <- sapply(df$order_status, standardisasi_status)
cat("order_status distandarisasi\n"); print(table(df$order_status))

cat("\n=== Standardisasi Tambahan (Looping) ===\n")

kolom_teks <- c("category","customer_segment","region","stock_status")
for (kol in kolom_teks) {
  df[[kol]] <- str_to_title(str_trim(as.character(df[[kol]])))
  cat(sprintf("  '%-20s' distandarisasi\n", kol))
}

payment_map <- c(
  "e-wallet"="E-Wallet","ewallet"="E-Wallet","e wallet"="E-Wallet",
  "virtual account"="Virtual Account","va"="Virtual Account",
  "cod"="COD","cash on delivery"="COD",
  "transfer bank"="Transfer Bank","bank transfer"="Transfer Bank",
  "credit card"="Credit Card","nan"="Unknown","none"="Unknown"
)
df$payment_method <- sapply(
  str_to_lower(str_trim(df$payment_method)),
  function(x) {
    if (is.na(x) || x == "") return("Unknown")
    if (x %in% names(payment_map)) return(payment_map[x])
    return(str_to_title(x))
  }
)
cat("  'payment_method'     distandarisasi\n")

cat(sprintf("\nData bersih: %d baris, %d kolom\n", nrow(df), ncol(df)))

df$net_sales <- suppressWarnings(as.numeric(df$net_sales))
df$net_sales[is.na(df$net_sales)] <- 0

df$is_high_value <- ifelse(df$net_sales > 1000000, "Yes", "No")

cat("Kolom 'is_high_value' ditambahkan\n")

tbl_hv <- table(df$is_high_value)
print(tbl_hv)

cat(sprintf("\nPersentase high-value: %.1f%%\n",
            tbl_hv["Yes"] / sum(tbl_hv) * 100))

df_hv <- as.data.frame(tbl_hv)
colnames(df_hv) <- c("is_high_value", "count")
df_hv$pct <- round(df_hv$count / sum(df_hv$count) * 100, 1)

plot_ly(df_hv, labels = ~is_high_value, values = ~count, type = "pie",
        marker = list(colors = c("#40916c", "#b7e4c7"),
                      line = list(color = "white", width = 2)),
        textinfo = "label+percent",
        hovertemplate = "<b>%{label}</b><br>Jumlah: %{value:,}<br>Persentase: %{percent}<extra></extra>") |>
  layout(
    title = list(text = "<b>Distribusi Transaksi High-Value vs Non-High-Value</b>",
                 font = list(size = 15, color = "#1b4332")),
    legend = list(orientation = "h", x = 0.3, y = -0.1),
    paper_bgcolor = "white",
    height = 400,
    margin = list(t = 50, b = 50, l = 30, r = 30)
  )

tentukan_prioritas <- function(net) {
  if      (is.na(net))    return("Low")
  else if (net > 1000000) return("High")
  else if (net >= 500000) return("Medium")
  else                    return("Low")
}

df$order_priority <- sapply(df$net_sales, tentukan_prioritas)
cat("Kolom 'order_priority' ditambahkan\n")

print(table(df$order_priority))

df_prio <- as.data.frame(table(df$order_priority))
colnames(df_prio) <- c("priority", "count")
df_prio$priority <- factor(df_prio$priority, levels = c("High","Medium","Low"))
df_prio <- df_prio[order(df_prio$priority), ]
df_prio$pct <- round(df_prio$count / sum(df_prio$count) * 100, 1)

plot_ly(df_prio, x = ~priority, y = ~count, type = "bar",
        marker = list(color = c("#1b4332","#40916c","#b7e4c7"),
                      line = list(color = "white", width = 1.5)),
        text = ~paste0(count, "<br>(", pct, "%)"),
        textposition = "outside",
        hovertemplate = "<b>%{x}</b><br>Jumlah: %{y:,}<extra></extra>") |>
  layout(
    title = list(text = "<b>Distribusi Order Priority (Nested IF)</b>",
                 font = list(size = 14, color = "#1b4332")),
    xaxis = list(title = "Prioritas"),
    yaxis = list(title = "Jumlah Transaksi", gridcolor = "#f0faf3"),
    plot_bgcolor = "white", paper_bgcolor = "white",
    height = 400
  )

df$valid_transaction <- ifelse(
  df$order_status == "Cancelled", "Invalid", "Valid"
)
cat("Kolom 'valid_transaction' ditambahkan\n")

tbl_vt <- table(df$valid_transaction)
print(tbl_vt)

cat(sprintf("\nPersentase Valid  : %.1f%%\n", tbl_vt["Valid"]  / sum(tbl_vt) * 100))

cat(sprintf("Persentase Invalid: %.1f%%\n", tbl_vt["Invalid"] / sum(tbl_vt) * 100))

df_vt <- as.data.frame(tbl_vt)
colnames(df_vt) <- c("status", "count")

plot_ly(df_vt, labels = ~status, values = ~count, type = "pie", hole = 0.45,
        marker = list(colors = c("#e76f51","#40916c"),
                      line = list(color = "white", width = 2)),
        textinfo = "label+percent",
        hovertemplate = "<b>%{label}</b><br>Jumlah: %{value:,}<extra></extra>") |>
  layout(
    title = list(text = "<b>Distribusi Valid vs Invalid Transaction</b>",
                 font = list(size = 14, color = "#1b4332")),
    annotations = list(list(text = "Status", showarrow = FALSE,
                            font = list(size = 13, color = "#2d6a4f"))),
    legend = list(orientation = "h", x = 0.3, y = -0.1),
    paper_bgcolor = "white",
    height = 400
  )

df |>
  select(order_id, platform, net_sales, order_status,
         is_high_value, order_priority, valid_transaction) |>
  head(10) |>
  kable(caption = "Preview Dataset dengan 3 Kolom Baru (10 baris pertama)") |>
  kable_styling(bootstrap_options = c("striped","hover","condensed"), font_size = 12)

platform_count <- df |>
  count(platform, name = "jumlah") |>
  arrange(desc(jumlah)) |>
  mutate(persen = round(jumlah / sum(jumlah) * 100, 1))

kable(platform_count,
      col.names = c("Platform","Jumlah Transaksi","Persentase (%)"),
      caption = "Distribusi Transaksi per Platform") |>
  kable_styling(bootstrap_options = c("striped","hover"), full_width = FALSE) |>
  row_spec(1, bold = TRUE, background = "#f0faf3", color = "#1b4332")

p_plat <- plot_ly(
  data = platform_count |> arrange(jumlah),
  y = ~reorder(platform, jumlah), x = ~jumlah,
  type = "bar", orientation = "h",
  marker = list(
    color = colorRampPalette(c("#b7e4c7","#40916c","#1b4332"))(nrow(platform_count)),
    line  = list(color = "white", width = 1.5)
  ),
  text = ~paste0(format(jumlah, big.mark="."), " (", persen, "%)"),
  textposition = "outside",
  hovertemplate = "<b>%{y}</b><br>Transaksi: %{x:,}<extra></extra>"
) |>
  layout(
    title = list(text = "<b>Jumlah Transaksi per Platform</b>",
                 font = list(size = 15, color = "#1b4332")),
    xaxis = list(title = "Jumlah Transaksi", gridcolor = "#f0faf3"),
    yaxis = list(title = ""),
    plot_bgcolor = "white", paper_bgcolor = "white",
    margin = list(r = 120),
    hoverlabel = list(bgcolor = "#2d6a4f", font = list(color = "white")),
    height = 400
  )
p_plat

cat(sprintf("\nPLATFORM PALING DOMINAN: %s (%d transaksi)\n",
            platform_count$platform[1], platform_count$jumlah[1]))

kategori_count <- df |>
  count(category, name = "jumlah") |>
  arrange(desc(jumlah)) |>
  head(8) |>
  mutate(persen = round(jumlah / nrow(df) * 100, 1))

kable(kategori_count,
      col.names = c("Kategori","Jumlah","Persentase (%)"),
      caption = "Top 8 Kategori Produk") |>
  kable_styling(bootstrap_options = c("striped","hover"), full_width = FALSE) |>
  row_spec(1, bold = TRUE, background = "#f0faf3")

cat(sprintf("\nKATEGORI PALING SERING: %s (%d transaksi)\n",
            kategori_count$category[1], kategori_count$jumlah[1]))

plot_ly(
  data = kategori_count |> arrange(jumlah),
  y = ~reorder(category, jumlah), x = ~jumlah,
  type = "bar", orientation = "h",
  marker = list(
    color = colorRampPalette(c("#b7e4c7","#40916c","#1b4332"))(nrow(kategori_count)),
    line  = list(color = "white", width = 1.2)
  ),
  text = ~paste0(jumlah, " (", persen, "%)"),
  textposition = "outside",
  hovertemplate = "<b>%{y}</b><br>Jumlah: %{x:,}<extra></extra>"
) |>
  layout(
    title = list(text = "<b>Top 8 Kategori Produk Terlaris</b>",
                 font = list(size = 14, color = "#1b4332")),
    xaxis = list(title = "Jumlah Transaksi", gridcolor = "#f0faf3"),
    yaxis = list(title = ""),
    plot_bgcolor = "white", paper_bgcolor = "white",
    margin = list(r = 110),
    height = 400
  )

status_count <- df |>
  count(order_status, name = "jumlah") |>
  arrange(desc(jumlah)) |>
  mutate(persen = round(jumlah / sum(jumlah) * 100, 1))

kable(status_count,
      col.names = c("Status","Jumlah","Persentase (%)"),
      caption = "Distribusi Status Transaksi") |>
  kable_styling(bootstrap_options = c("striped","hover"), full_width = FALSE) |>
  row_spec(1, bold = TRUE, background = "#f0faf3")

cat(sprintf("\nSTATUS PALING BANYAK: %s (%d transaksi)\n",
            status_count$order_status[1], status_count$jumlah[1]))

plot_ly(status_count, labels = ~order_status, values = ~jumlah,
        type = "pie", hole = 0.4,
        marker = list(
          colors = colorRampPalette(c("#1b4332","#40916c","#74c69d","#b7e4c7","#d8f3dc"))(nrow(status_count)),
          line   = list(color = "white", width = 2)
        ),
        textinfo = "label+percent",
        hovertemplate = "<b>%{label}</b><br>Jumlah: %{value:,}<br>%{percent}<extra></extra>") |>
  layout(
    title = list(text = "<b>Distribusi Status Transaksi</b>",
                 font = list(size = 14, color = "#1b4332")),
    legend = list(orientation = "h", x = 0.1, y = -0.15),
    paper_bgcolor = "white",
    height = 400
  )

df |> write.csv("ecommerce_cleaned_R.csv", row.names = FALSE)
cat("\nDisimpan -> ecommerce_cleaned_R.csv\n")

cat(sprintf("   Total baris: %d | Total kolom: %d\n", nrow(df), ncol(df)))

countries_list <- list()
page <- get_page_r(paste0(BASE_URL, "/pages/simple/"))

if (!is.null(page)) {
  nodes <- page %>% html_nodes("div.country")
  cat(sprintf("Ditemukan %d negara\n\n", length(nodes)))

  for (i in seq_along(nodes)) {
    nd   <- nodes[[i]]
    name <- nd %>% html_node("h3.country-name")         %>% html_text(trim=TRUE)
    cap  <- nd %>% html_node("span.country-capital")    %>% html_text(trim=TRUE)
    pop  <- nd %>% html_node("span.country-population") %>% html_text(trim=TRUE)
    area <- nd %>% html_node("span.country-area")       %>% html_text(trim=TRUE)

    name <- ifelse(is.na(name)||nchar(name)==0, "Unknown", name)
    cap  <- ifelse(is.na(cap) ||nchar(trimws(cap))==0, "Unknown", cap)
    pop  <- ifelse(is.na(pop),  "0", pop)
    area <- ifelse(is.na(area), "0", area)

    data_status <- if(name!="Unknown" && cap!="Unknown") "Complete" else "Incomplete"

    countries_list[[i]] <- list(country_name=name, capital=cap,
                                population=pop, area=area, data_status=data_status)
  }
}

df_countries_raw <- bind_rows(countries_list)
cat(sprintf("Total data: %d baris\n", nrow(df_countries_raw)))

kable(head(df_countries_raw, 5), caption = "Preview Countries (Raw)") |>
  kable_styling(bootstrap_options = c("striped","hover","condensed"), font_size = 12)

cat(sprintf("Baris: %d | Kolom: %d | Duplikat: %d\n\n",
            nrow(df_countries_raw), ncol(df_countries_raw),
            sum(duplicated(df_countries_raw))))

cat("Tipe data:\n"); print(sapply(df_countries_raw, class))

cat("\nMissing values:\n"); print(colSums(is.na(df_countries_raw)))

cat("\nDATA ISSUES:\n")

cat("  (1) population & area bertipe character, bukan numerik\n")

cat("  (2) Beberapa capital memiliki spasi ekstra\n")

df_countries <- df_countries_raw
for (col in c("country_name","capital")) {
  df_countries[[col]] <- str_to_title(str_trim(df_countries[[col]]))
  cat(sprintf("  '%-15s' trim & proper case\n", col))
}

df_countries$population <- suppressWarnings(as.numeric(str_replace_all(df_countries$population,",","")))
df_countries$area       <- suppressWarnings(as.numeric(str_replace_all(df_countries$area,",","")))
df_countries$population[is.na(df_countries$population)] <- 0
df_countries$area[is.na(df_countries$area)]             <- 0
df_countries <- df_countries %>% distinct(country_name, .keep_all=TRUE)
cat(sprintf("\nBaris final: %d\n", nrow(df_countries)))

write.csv(df_countries, "countries_of_the_world_R.csv", row.names=FALSE)
cat("Disimpan -> countries_of_the_world_R.csv\n")

print(sapply(df_countries, class))

kable(head(df_countries, 5)) |>
  kable_styling(bootstrap_options = c("striped","hover","condensed"), font_size=12)

df_countries <- df_countries %>%
  mutate(
    is_large = case_when(
      population > 10000000 ~ "Large",
      population > 1000000  ~ "Medium",
      TRUE                  ~ "Small"
    ),
    data_status = case_when(
      is.na(country_name) | country_name == "Unknown" ~ "Incomplete",
      capital == "Unknown"                            ~ "Incomplete",
      TRUE                                            ~ "Complete"
    )
  )

cat("Kolom 'is_large' ditambahkan\n")

print(table(df_countries$is_large))

cat("\nKolom 'data_status' diperbarui\n")

print(table(df_countries$data_status))

write.csv(df_countries, "countries_of_the_world_R.csv", row.names = FALSE)
cat("\nDisimpan -> countries_of_the_world_R.csv\n")

cat("\n=== INSIGHT COUNTRIES ===\n")

biggest <- df_countries[which.max(df_countries$population), ]
cat(sprintf("1. Negara dengan populasi terbesar: %s (%.0f jiwa)\n",
            biggest$country_name, biggest$population))

cat("2. Distribusi ukuran negara:\n")

print(table(df_countries$is_large))

total_c <- nrow(df_countries)
cat(sprintf("3. Persentase: Large=%.1f%%, Medium=%.1f%%, Small=%.1f%%\n",
  sum(df_countries$is_large=="Large")  / total_c * 100,
  sum(df_countries$is_large=="Medium") / total_c * 100,
  sum(df_countries$is_large=="Small")  / total_c * 100))

top15_countries <- df_countries |> arrange(desc(population)) |> head(15)

plot_ly(
  data = top15_countries,
  y = ~reorder(country_name, population), x = ~population,
  type = "bar", orientation = "h",
  marker = list(
    color = colorRampPalette(c("#b7e4c7","#40916c","#1b4332"))(15),
    line  = list(color = "white", width = 1)
  ),
  text  = ~paste0(round(population/1e6, 1), " jt"),
  textposition = "outside",
  hovertemplate = "<b>%{y}</b><br>Populasi: %{x:,.0f}<extra></extra>"
) |>
  layout(
    title = list(text = "<b>Top 15 Negara dengan Populasi Terbesar</b>",
                 font = list(size = 14, color = "#1b4332")),
    xaxis = list(title = "Populasi", gridcolor = "#f0faf3"),
    yaxis = list(title = ""),
    plot_bgcolor = "white", paper_bgcolor = "white",
    margin = list(r = 80),
    height = 420
  )

df_size <- as.data.frame(table(df_countries$is_large))
colnames(df_size) <- c("size", "count")
plot_ly(df_size, labels = ~size, values = ~count, type = "pie",
        marker = list(colors = c("#1b4332","#74c69d","#d8f3dc"),
                      line   = list(color = "white", width = 2)),
        textinfo = "label+percent",
        hovertemplate = "<b>%{label}</b><br>Jumlah: %{value}<extra></extra>") |>
  layout(
    title = list(text = "<b>Distribusi Ukuran Negara (Berdasarkan Populasi)</b>",
                 font = list(size = 14, color = "#1b4332")),
    legend = list(orientation = "h", x = 0.2, y = -0.1),
    paper_bgcolor = "white",
    height = 400
  )

hockey_list <- list(); counter <- 1

cat("Scraping dengan pagination...\n\n")

for (pg in 1:24) {
  page <- get_page_r(paste0(BASE_URL, "/pages/forms/"),
                     query_params = list(page_num=pg, per_page=25))
  if (!is.null(page)) {
    rows <- page %>% html_nodes("tr.team")
    if (length(rows) == 0) { cat(sprintf("  Pagination berhenti di halaman %d\n", pg)); break }
    cat(sprintf("  Halaman %d -> %d tim\n", pg, length(rows)))

    for (row in rows) {
      cols <- row %>% html_nodes("td") %>% html_text(trim=TRUE)
      if (length(cols) >= 8) {
        ds <- if(nchar(cols[1])>0 && nchar(cols[2])>0) "Complete" else "Incomplete"
        hockey_list[[counter]] <- list(
          team_name=cols[1], year=cols[2], wins=cols[3], losses=cols[4],
          win_pct=cols[6], goals_for=cols[7], goals_against=cols[8], data_status=ds)
        counter <- counter + 1
      }
    }
  }
  Sys.sleep(0.3)
}

df_hockey_raw <- bind_rows(hockey_list)
cat(sprintf("\nTotal data: %d baris\n", nrow(df_hockey_raw)))

kable(head(df_hockey_raw, 5), caption = "Preview Hockey Teams (Raw)") |>
  kable_styling(bootstrap_options = c("striped","hover","condensed"), font_size=12)

cat(sprintf("Baris: %d | Kolom: %d | Duplikat: %d\n\n",
            nrow(df_hockey_raw), ncol(df_hockey_raw),
            sum(duplicated(df_hockey_raw))))

cat("Tipe data:\n"); print(sapply(df_hockey_raw, class))

cat("\nDATA ISSUES:\n")

cat("  (1) Kolom wins/losses/goals bertipe character, bukan numerik\n")

cat("  (2) Kolom year bertipe string, perlu konversi ke integer\n")

df_hockey <- df_hockey_raw
for (col in c("wins","losses","goals_for","goals_against")) {
  df_hockey[[col]] <- suppressWarnings(as.numeric(df_hockey[[col]]))
  if(any(is.na(df_hockey[[col]]))) df_hockey[[col]][is.na(df_hockey[[col]])] <- 0
  cat(sprintf("  '%-15s' -> numeric\n", col))
}

df_hockey$year     <- suppressWarnings(as.numeric(df_hockey$year))
df_hockey$win_pct  <- suppressWarnings(as.numeric(df_hockey$win_pct))
df_hockey$team_name <- str_to_title(str_trim(df_hockey$team_name))
df_hockey <- df_hockey %>% distinct(team_name, year, .keep_all=TRUE)
cat(sprintf("\nBaris final: %d\n", nrow(df_hockey)))

write.csv(df_hockey, "hockey_teams_R.csv", row.names=FALSE)
cat("Disimpan -> hockey_teams_R.csv\n")

print(sapply(df_hockey, class))

kable(head(df_hockey, 5)) |>
  kable_styling(bootstrap_options = c("striped","hover","condensed"), font_size=12)

df_hockey <- df_hockey %>%
  mutate(
    performance = case_when(
      wins > 40 ~ "High",
      wins > 20 ~ "Medium",
      TRUE      ~ "Low"
    ),
    data_status = case_when(
      is.na(team_name) | team_name == "" ~ "Incomplete",
      is.na(wins)                        ~ "Incomplete",
      TRUE                               ~ "Complete"
    )
  )

df_hockey <- df_hockey %>%
  mutate(win_rate = round(wins / (wins + losses) * 100, 2))
df_hockey$win_rate[is.nan(df_hockey$win_rate)] <- 0

cat("Kolom 'performance' ditambahkan\n")

print(table(df_hockey$performance))

cat("\nKolom 'win_rate' ditambahkan\n")

cat("\nKolom 'data_status' diperbarui\n")

print(table(df_hockey$data_status))

write.csv(df_hockey, "hockey_teams_R.csv", row.names = FALSE)
cat("\nDisimpan -> hockey_teams_R.csv\n")

kable(
  df_hockey %>% select(team_name, year, wins, losses, win_rate) %>% head(8),
  caption = "Preview Hockey Teams dengan Win Rate"
) |>
  kable_styling(bootstrap_options = c("striped","hover","condensed"), font_size = 12)

cat("\n=== INSIGHT HOCKEY TEAMS ===\n")

best_team <- df_hockey[which.max(df_hockey$win_rate), ]
cat(sprintf("1. Tim dengan win rate tertinggi: %s (%d) - %.2f%%\n",
            best_team$team_name, best_team$year, best_team$win_rate))

avg_per_year <- df_hockey %>%
  group_by(year) %>%
  summarise(avg_wr = round(mean(win_rate, na.rm=TRUE), 2), .groups="drop") %>%
  arrange(desc(avg_wr)) %>% slice(1)
cat(sprintf("2. Tahun dengan rata-rata win rate tertinggi: %d (%.2f%%)\n",
            avg_per_year$year, avg_per_year$avg_wr))

cat("3. Distribusi performance:\n")

print(table(df_hockey$performance))

df_perf <- as.data.frame(table(df_hockey$performance))
colnames(df_perf) <- c("performance", "count")
df_perf$performance <- factor(df_perf$performance, levels = c("High","Medium","Low"))

plot_ly(df_perf, x = ~performance, y = ~count, type = "bar",
        marker = list(color = c("#1b4332","#40916c","#b7e4c7"),
                      line = list(color = "white", width = 1.5)),
        text = ~count, textposition = "outside",
        hovertemplate = "<b>%{x}</b><br>Jumlah Tim: %{y:,}<extra></extra>") |>
  layout(
    title = list(text = "<b>Distribusi Performance Tim Hockey</b>",
                 font = list(size = 14, color = "#1b4332")),
    xaxis = list(title = "Performance"),
    yaxis = list(title = "Jumlah Tim", gridcolor = "#f0faf3"),
    plot_bgcolor = "white", paper_bgcolor = "white",
    height = 400
  )

avg_wr_year <- df_hockey %>%
  group_by(year) %>%
  summarise(avg_win_rate = round(mean(win_rate, na.rm=TRUE), 2), .groups="drop") %>%
  arrange(year)

plot_ly(avg_wr_year, x = ~year, y = ~avg_win_rate, type = "scatter", mode = "lines+markers",
        line    = list(color = "#40916c", width = 2.5),
        marker  = list(color = "#1b4332", size = 7),
        text    = ~paste0(avg_win_rate, "%"),
        textposition = "top center",
        hovertemplate = "<b>Tahun %{x}</b><br>Avg Win Rate: %{y:.2f}%<extra></extra>") |>
  layout(
    title = list(text = "<b>Rata-rata Win Rate Tim Hockey per Tahun</b>",
                 font = list(size = 14, color = "#1b4332")),
    xaxis = list(title = "Tahun", gridcolor = "#f0faf3"),
    yaxis = list(title = "Rata-rata Win Rate (%)", gridcolor = "#f0faf3"),
    plot_bgcolor = "white", paper_bgcolor = "white",
    height = 400
  )

oscar_list <- list(); counter <- 1
cat("Scraping data Oscar per tahun...\n\n")

for (year in 2010:2015) {
  url_oscar <- sprintf("%s/pages/ajax-javascript/?ajax=true&year=%d", BASE_URL, year)
  resp      <- GET(url_oscar, add_headers("User-Agent"="Mozilla/5.0"), timeout(15))

  if (status_code(resp) == 200) {
    movies <- tryCatch(
      fromJSON(content(resp, "text", encoding="UTF-8"), flatten=TRUE),
      error = function(e) NULL
    )
    if (!is.null(movies) && is.data.frame(movies) && nrow(movies) > 0) {
      cat(sprintf("  Tahun %d -> %d film\n", year, nrow(movies)))
      for (i in seq_len(nrow(movies))) {
        title <- ifelse(!is.null(movies$title[i]) && nchar(str_trim(movies$title[i]))>0,
                        str_trim(movies$title[i]), "Unknown")
        ds    <- if(title!="Unknown") "Complete" else "Incomplete"
        oscar_list[[counter]] <- list(
          movie_title = title, year = year,
          nominations = as.character(ifelse(is.null(movies$nominations[i]), 0, movies$nominations[i])),
          awards      = as.character(ifelse(is.null(movies$awards[i]),      0, movies$awards[i])),
          data_status = ds)
        counter <- counter + 1
      }
    }
  } else {
    cat(sprintf("  Gagal tahun %d: status %d\n", year, status_code(resp)))
  }
  Sys.sleep(0.3)
}

df_oscar_raw <- bind_rows(oscar_list)
cat(sprintf("\nTotal film terkumpul: %d baris\n", nrow(df_oscar_raw)))

kable(head(df_oscar_raw, 5), caption = "Preview Oscar Films (Raw)") |>
  kable_styling(bootstrap_options = c("striped","hover","condensed"), font_size=12)

cat(sprintf("Baris: %d | Kolom: %d | Duplikat: %d\n\n",
            nrow(df_oscar_raw), ncol(df_oscar_raw),
            sum(duplicated(df_oscar_raw))))

cat("Tipe data:\n"); print(sapply(df_oscar_raw, class))

cat("\nDATA ISSUES:\n")

cat("  (1) nominations & awards bertipe character, perlu konversi numerik\n")

cat("  (2) Beberapa film nominations = 0 (perlu diverifikasi)\n")

df_oscar <- df_oscar_raw
if (nrow(df_oscar) > 0) {
  df_oscar$movie_title <- str_to_title(str_trim(df_oscar$movie_title))
  for (col in c("nominations","awards")) {
    df_oscar[[col]] <- suppressWarnings(as.numeric(df_oscar[[col]]))
    df_oscar[[col]][is.na(df_oscar[[col]])] <- 0
    cat(sprintf("  '%-15s' -> numeric\n", col))
  }
  df_oscar$movie_title[is.na(df_oscar$movie_title)|df_oscar$movie_title==""] <- "Unknown"
  df_oscar <- df_oscar %>% distinct()
  cat(sprintf("\nBaris final: %d\n", nrow(df_oscar)))
}

write.csv(df_oscar, "oscar_winning_films_R.csv", row.names=FALSE)
cat("Disimpan -> oscar_winning_films_R.csv\n")

print(sapply(df_oscar, class))

kable(head(df_oscar, 5)) |>
  kable_styling(bootstrap_options = c("striped","hover","condensed"), font_size=12)

if (nrow(df_oscar) > 0) {
  df_oscar <- df_oscar %>%
    mutate(
      category = case_when(
        awards >= 3 ~ "High Awards",
        awards >= 1 ~ "Mid Awards",
        TRUE        ~ "No Award"
      ),
      data_status = case_when(
        is.na(movie_title) | movie_title == "Unknown" ~ "Incomplete",
        TRUE                                          ~ "Complete"
      )
    )
}

cat("Kolom 'category' ditambahkan\n")

print(table(df_oscar$category))

cat("\nKolom 'data_status' diperbarui\n")

print(table(df_oscar$data_status))

write.csv(df_oscar, "oscar_winning_films_R.csv", row.names = FALSE)
cat("\nDisimpan ulang -> oscar_winning_films_R.csv\n")

kable(head(df_oscar, 8),
      caption = "Preview Oscar Films dengan Kolom Category & Data Status") |>
  kable_styling(bootstrap_options = c("striped","hover","condensed"), font_size = 12) |>
  column_spec(5, bold = TRUE,
              color = ifelse(
                head(df_oscar, 8)$category == "High Awards", "#2d6a4f",
                ifelse(head(df_oscar, 8)$category == "Mid Awards", "#40916c", "#95d5b2")
              ))

if (nrow(df_oscar) > 0) {
  df_oscar_cat <- as.data.frame(table(df_oscar$category))
  colnames(df_oscar_cat) <- c("category", "count")
  plot_ly(df_oscar_cat, labels = ~category, values = ~count, type = "pie",
          marker = list(colors = c("#1b4332","#74c69d","#d8f3dc"),
                        line   = list(color = "white", width = 2)),
          textinfo = "label+percent",
          hovertemplate = "<b>%{label}</b><br>Jumlah: %{value}<extra></extra>") |>
    layout(
      title = list(text = "<b>Distribusi Kategori Film Oscar (2010-2015)</b>",
                   font = list(size = 14, color = "#1b4332")),
      legend = list(orientation = "h", x = 0.1, y = -0.1),
      paper_bgcolor = "white",
      height = 400
    )
}

if (nrow(df_oscar) > 0) {
  films_per_year <- df_oscar %>% count(year, name = "total")
  plot_ly(films_per_year, x = ~year, y = ~total, type = "bar",
          marker = list(
            color = colorRampPalette(c("#b7e4c7","#1b4332"))(nrow(films_per_year)),
            line  = list(color = "white", width = 1.5)
          ),
          text = ~total, textposition = "outside",
          hovertemplate = "<b>Tahun %{x}</b><br>Film: %{y}<extra></extra>") |>
    layout(
      title = list(text = "<b>Jumlah Film Oscar yang Di-scrape per Tahun</b>",
                   font = list(size = 14, color = "#1b4332")),
      xaxis = list(title = "Tahun", tickmode = "linear"),
      yaxis = list(title = "Jumlah Film", gridcolor = "#f0faf3"),
      plot_bgcolor = "white", paper_bgcolor = "white",
      height = 400
    )
}

turtles_list <- list(); counter <- 1
page_main    <- get_page_r(paste0(BASE_URL, "/pages/frames/"))

if (!is.null(page_main)) {
  iframes <- html_nodes(page_main, "iframe")
  cat(sprintf("Jumlah iframe ditemukan: %d\n\n", length(iframes)))

  for (iframe in iframes) {
    src <- html_attr(iframe, "src")
    if (is.na(src) || nchar(src)==0) next
    iframe_url <- if(str_starts(src,"/")) paste0(BASE_URL, src) else src
    cat(sprintf("  Mengakses: %s\n", iframe_url))

    iframe_page <- get_page_r(iframe_url)
    if (!is.null(iframe_page)) {
      rows <- html_nodes(iframe_page, "tr")
      for (row in rows) {
        cols <- html_nodes(row, "td")
        if (length(cols)>=2 && nchar(str_trim(html_text(cols[[1]])))>1) {
          name <- str_trim(html_text(cols[[1]]))
          desc <- str_trim(html_text(cols[[2]]))
          info <- if(length(cols)>2) str_trim(html_text(cols[[3]])) else "N/A"
          if (!nchar(desc)) desc <- "Unknown"
          if (!nchar(info)) info <- "N/A"
          ds <- if(name!=""&&desc!="Unknown") "Complete" else "Incomplete"
          turtles_list[[counter]] <- list(
            name=name, description=desc,
            additional_info=info, source_iframe=iframe_url, data_status=ds)
          counter <- counter + 1
        }
      }
    }
  }
}

if (length(turtles_list)==0) {
  df_turtles_raw <- data.frame(name=character(), description=character(),
    additional_info=character(), source_iframe=character(),
    data_status=character(), stringsAsFactors=FALSE)
  cat("[WARN] Tidak ada data turtle.\n")
} else {
  df_turtles_raw <- bind_rows(turtles_list) |>
    mutate(across(where(is.character), ~ str_trunc(str_trim(.x), 80)))
}

cat(sprintf("\nTotal data: %d baris\n", nrow(df_turtles_raw)))

if (nrow(df_turtles_raw)>0)
  kable(head(df_turtles_raw,5), caption="Preview Turtles (Raw)") |>
    kable_styling(bootstrap_options = c("striped","hover","condensed"), font_size=12)

cat(sprintf("Baris: %d | Kolom: %d | Duplikat: %d\n\n",
            nrow(df_turtles_raw), ncol(df_turtles_raw),
            sum(duplicated(df_turtles_raw))))

cat("Tipe data:\n"); print(sapply(df_turtles_raw, class))

cat("\nDATA ISSUES:\n")

cat("  (1) Kolom additional_info banyak berisi 'N/A'\n")

cat("  (2) Beberapa description sangat pendek/tidak informatif\n")

df_turtles <- df_turtles_raw
for (col in c("name","description","additional_info")) {
  if (col %in% names(df_turtles)) {
    df_turtles[[col]] <- str_trim(df_turtles[[col]])
    df_turtles[[col]][is.na(df_turtles[[col]])] <- "Unknown"
    cat(sprintf("  '%-20s' trim & fill NA\n", col))
  }
}

df_turtles <- df_turtles %>% distinct()
cat(sprintf("\nBaris final: %d\n", nrow(df_turtles)))

write.csv(df_turtles, "turtles_data_R.csv", row.names=FALSE)
cat("Disimpan -> turtles_data_R.csv\n")

if (nrow(df_turtles)>0)
  kable(head(df_turtles,5)) |>
    kable_styling(bootstrap_options = c("striped","hover","condensed"), font_size=12)

df_turtles <- df_turtles %>%
  mutate(
    data_status = case_when(
      is.na(name)        | name        == "Unknown" ~ "Incomplete",
      is.na(description) | description == "Unknown" ~ "Incomplete",
      TRUE                                          ~ "Complete"
    )
  )

cat("Kolom 'data_status' diperbarui\n")

print(table(df_turtles$data_status))

write.csv(df_turtles, "turtles_data_R.csv", row.names = FALSE)
cat("\nDisimpan -> turtles_data_R.csv\n")

cat("\n=== INSIGHT TURTLES ===\n")

cat(sprintf("1. Total family kura-kura yang ditemukan: %d family\n", nrow(df_turtles)))

cat("2. Status kelengkapan data:\n")

print(table(df_turtles$data_status))

complete_pct <- sum(df_turtles$data_status=="Complete") / nrow(df_turtles) * 100
cat(sprintf("3. Persentase data Complete: %.1f%%\n", complete_pct))

if (nrow(df_turtles) > 0) {
  df_turt_status <- as.data.frame(table(df_turtles$data_status))
  colnames(df_turt_status) <- c("status", "count")
  df_turt_status$pct <- round(df_turt_status$count / sum(df_turt_status$count) * 100, 1)

  plot_ly(df_turt_status, labels = ~status, values = ~count, type = "pie", hole = 0.45,
          marker = list(colors = c("#40916c","#e76f51"),
                        line   = list(color = "white", width = 2)),
          textinfo = "label+percent",
          hovertemplate = "<b>%{label}</b><br>Jumlah: %{value}<extra></extra>") |>
    layout(
      title = list(text = "<b>Kelengkapan Data Turtles (iFrame)</b>",
                   font = list(size = 14, color = "#1b4332")),
      annotations = list(list(text = "Status", showarrow = FALSE,
                              font = list(size = 13, color = "#2d6a4f"))),
      legend = list(orientation = "h", x = 0.3, y = -0.1),
      paper_bgcolor = "white",
      height = 400
    )
}