---
title: "농수산물 게시글 탐지 및 분석 대시보드 v3.1 (최종)"
output: 
  flexdashboard::flex_dashboard:
    orientation: columns
    vertical_layout: scroll
    theme: cosmo
    source_code: embed
---

<script>
(function() {
  const correctPassword = "agriculture123";
  
  if (!sessionStorage.getItem("passwordVerified")) {
    document.body.innerHTML = '<div style="display: flex; align-items: center; justify-content: center; height: 100vh; font-family: Arial, sans-serif; background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);"><div style="background: white; padding: 50px; border-radius: 10px; box-shadow: 0 10px 25px rgba(0,0,0,0.2); text-align: center; max-width: 400px;"><h2 style="color: #333; margin-bottom: 10px;">🔐 비밀번호 입력</h2><p style="color: #666; margin-bottom: 30px;">대시보드에 접근하기 위해 비밀번호를 입력해주세요.</p><input type="password" id="passwordInput" placeholder="비밀번호 입력" style="width: 100%; padding: 12px; border: 2px solid #ddd; border-radius: 5px; font-size: 16px; margin-bottom: 15px; box-sizing: border-box;" /><button onclick="checkPassword()" style="width: 100%; padding: 12px; background: #667eea; color: white; border: none; border-radius: 5px; font-size: 16px; font-weight: bold; cursor: pointer; transition: background 0.3s;">입장</button><p id="errorMsg" style="color: #e74c3c; margin-top: 15px; display: none;">❌ 비밀번호가 틀렸습니다.</p></div></div>';
    
    window.checkPassword = function() {
      const input = document.getElementById("passwordInput").value;
      if (input === correctPassword) {
        sessionStorage.setItem("passwordVerified", "true");
        location.reload();
      } else {
        document.getElementById("errorMsg").style.display = "block";
        document.getElementById("passwordInput").value = "";
        document.getElementById("passwordInput").focus();
      }
    };
    
    document.getElementById("passwordInput").onkeypress = function(e) {
      if (e.key === "Enter") window.checkPassword();
    };
    
    document.getElementById("passwordInput").focus();
    throw new Error("Password protection active");
  }
})();
</script>

```{r setup, include=FALSE}
# 패키지 설치 및 로드
required_packages <- c("flexdashboard", "tidyverse", "plotly", "DT", "stringr", "scales")
for (pkg in required_packages) {
  if (!require(pkg, character.only = TRUE)) {
    install.packages(pkg, repos = "http://cran.r-project.org")
    library(pkg, character.only = TRUE)
  }
}

options(warn = -1)
knitr::opts_chunk$set(warning = FALSE, message = FALSE, error = FALSE)

# 데이터 로드
data <- read.csv("bquxjob_4350fd77_19a911d47f0.csv", 
                 encoding = "UTF-8", stringsAsFactors = FALSE)

# 데이터 정제
data$price <- as.numeric(gsub("[^0-9]", "", data$price))
data$price_numeric <- ifelse(is.na(data$price), 0, data$price)
data$created_at <- as.character(data$created_at)
data$created_at <- sub(" UTC$", "", data$created_at)
data$created_at <- as.POSIXct(data$created_at, format="%Y-%m-%d %H:%M:%OS")

# 농수산물 키워드
agricultural_products <- list(
  "과일-딸기" = c("딸기", "strawberry", "설향"), "과일-포도" = c("포도", "grape", "샤인머스켓"),
  "과일-사과" = c("사과", "apple", "홍로", "후지"), "과일-배" = c("신고배", "배\\s", "\\s배\\b", "원황배"),
  "과일-수박" = c("수박", "watermelon", "참외"), "과일-귤" = c("귤", "만다린", "감귤", "온주", "유자"),
  "과일-단감" = c("단감", "홍로감", "곶감"), "과일-복숭아" = c("복숭아", "peach", "황도"),
  "과일-체리" = c("체리", "cherry"), "과일-자몽" = c("자몽", "grapefruit"),
  "과일-파인애플" = c("파인애플", "pineapple"), "과일-키위" = c("키위", "kiwi"),
  "과일-기타" = c("과일", "열대과일"),
  
  "채소-상추" = c("상추", "lettuce", "음채"), "채소-배추" = c("배추", "napa cabbage"),
  "채소-양배추" = c("양배추", "cabbage"), "채소-브로콜리" = c("브로콜리", "broccoli"),
  "채소-당근" = c("당근", "carrot"), "채소-옥수수" = c("옥수수", "corn", "스위트콘"),
  "채소-고추" = c("고추", "chilli", "파프리카"), "채소-토마토" = c("토마토", "tomato"),
  "채소-감자" = c("감자", "potato"), "채소-고구마" = c("고구마", "sweet potato"),
  "채소-양파" = c("양파", "onion"), "채소-마늘" = c("마늘", "garlic"),
  "채소-생강" = c("생강", "ginger"), "채소-시금치" = c("시금치", "spinach"),
  "채소-버섯" = c("버섯", "mushroom", "느타리", "팽이", "송이", "표고"),
  "채소-무" = c("무\\b", "\\b무\\b", "무말랭이"), "채소-대파" = c("대파", "파\\b", "옆파"),
  "채소-기타" = c("채소", "신선채소"),
  
  "곡물-쌀" = c("쌀", "rice", "백미", "현미", "흑미"), "곡물-보리" = c("보리", "barley"),
  "곡물-콩" = c("콩", "bean", "검은콩"), "곡물-기타" = c("곡물", "잡곡"),
  
  "수산물-연어" = c("연어", "salmon"), "수산물-고등어" = c("고등어", "mackerel"),
  "수산물-멸치" = c("멸치", "anchovy"), "수산물-새우" = c("새우", "shrimp", "우새우"),
  "수산물-오징어" = c("오징어", "squid"), "수산물-게" = c("\\b게\\b", "crab", "대게"),
  "수산물-굴" = c("굴\\b", "oyster"), "수산물-조개" = c("조개", "clam", "바지락"),
  "수산물-미역" = c("미역", "seaweed", "다시마", "김\\b"), "수산물-전복" = c("전복", "abalone"),
  "수산물-홍합" = c("홍합", "mussel"), "수산물-소라" = c("소라", "turban shell"),
  "수산물-낙지" = c("낙지", "octopus"), "수산물-기타" = c("상어", "해산물"),
  
  "축산물-소고기" = c("소고기", "beef", "한우", "등심"), "축산물-돼지고기" = c("돼지고기", "pork", "삼겹살"),
  "축산물-닭고기" = c("닭고기", "chicken"), "축산물-계란" = c("계란", "egg", "달걀"),
  "축산물-우유" = c("우유", "milk", "치즈"), "축산물-기타" = c("고기", "육류"),
  
  "특용작물-꿀" = c("꿀", "honey", "벌꿀"), "특용작물-인삼" = c("인삼", "ginseng", "홍삼"),
  "특용작물-기타" = c("농산물", "특산품")
)

# 표현 키워드
agricultural_expressions <- list(
  "신선함-표현" = c("싱싱", "신선", "팔팔", "상상"),
  "출처-표현" = c("농장", "직접 재배", "산지직송", "상산", "직거래"),
  "품질-표현" = c("최고급", "프리미엄", "특선", "특급"),
  "자연-표현" = c("자연", "천연", "무농약", "유기농"),
  "시즈-표현" = c("제철", "당도", "한정"),
  "건강-표현" = c("건강", "영양", "비타민"),
  "산지-표현" = c("남해", "제주", "경주", "산지"),
  "추천-표현" = c("추천", "인기", "best")
)

# 탐지 함수
detect_ag <- function(title, content) {
  if (is.na(title) || is.na(content)) return("기타")
  text <- tolower(paste(title, content, sep = " "))
  text <- str_replace_all(text, "탐배", "")
  for (cat in names(agricultural_products)) {
    for (kw in agricultural_products[[cat]]) {
      if (str_detect(text, paste0("(?i)", kw))) return(cat)
    }
  }
  return("기타")
}

detect_expr <- function(title, content) {
  if (is.na(title) || is.na(content)) return("없음")
  text <- tolower(paste(title, content, sep = " "))
  expr_found <- c()
  for (cat in names(agricultural_expressions)) {
    for (kw in agricultural_expressions[[cat]]) {
      if (str_detect(text, paste0("(?i)", kw))) expr_found <- c(expr_found, cat)
    }
  }
  if (length(expr_found) == 0) return("없음")
  return(paste(unique(expr_found), collapse = ", "))
}

# 데이터 생성
data$ag_category <- mapply(detect_ag, data$title, data$content, SIMPLIFY = TRUE)
data$ag_expressions <- mapply(detect_expr, data$title, data$content, SIMPLIFY = TRUE)
data$is_agricultural <- data$ag_category != "기타"

# 판매 단위
data$quantity_units <- apply(data[, c("title", "content")], 1, function(row) {
  text <- tolower(paste(row[1], row[2], sep = " "))
  units <- c()
  if (str_detect(text, "kg|킬로")) units <- c(units, "무게-kg")
  if (str_detect(text, "g\\b|그램")) units <- c(units, "무게-g")
  if (str_detect(text, "근\\b")) units <- c(units, "무게-근")
  if (str_detect(text, "박스|box")) units <- c(units, "포장-박스")
  if (str_detect(text, "포기\\b")) units <- c(units, "포장-포기")
  if (str_detect(text, "개\\b")) units <- c(units, "개수-개")
  if (length(units) == 0) return("미표시")
  return(paste(unique(units), collapse = ", "))
})

# 통합 분석
data$has_unit <- data$quantity_units != "미표시"
data$has_expression <- data$ag_expressions != "없음"
data$fulfillment_count <- data$is_agricultural + data$has_unit + data$has_expression

# 표현 세부 데이터
expr_detail_list <- list()
for (cat in names(agricultural_expressions)) {
  for (kw in agricultural_expressions[[cat]]) {
    count <- sum(str_count(paste(data$title, data$content, sep = " "), paste0("(?i)", kw)), na.rm = TRUE)
    if (count > 0) expr_detail_list[[length(expr_detail_list) + 1]] <- 
      data.frame(표현카테고리 = cat, 키워드 = kw, 건수 = count, stringsAsFactors = FALSE)
  }
}
expression_detail_data <- do.call(rbind, expr_detail_list)
rownames(expression_detail_data) <- NULL

# 통합 분석 데이터
comprehensive_data <- data %>%
  filter(is_agricultural) %>%
  group_by(ag_category) %>%
  summarise(
    이_게시글 = n(),
    단위표시 = sum(has_unit, na.rm = TRUE),
    단위율 = paste0(round(sum(has_unit, na.rm = TRUE) / n() * 100, 1), "%"),
    표현포함 = sum(has_expression, na.rm = TRUE),
    표현율 = paste0(round(sum(has_expression, na.rm = TRUE) / n() * 100, 1), "%"),
    종합충족 = sum(has_unit & has_expression, na.rm = TRUE),
    종합율 = paste0(round(sum(has_unit & has_expression, na.rm = TRUE) / n() * 100, 1), "%"),
    .groups = 'drop'
  ) %>%
  rename(농수산물카테고리 = ag_category) %>%
  arrange(desc(종합충족))
```

# 개요 {data-icon="fa-chart-line"}

## Column {data-width=350}

### 📊 데이터 기본 정보
```{r}
info_df <- data.frame(
  항목 = c("이 게시글", "농수산물 게시글", "농수산물 비율", "이 사용자", "데이터 기간"),
  값 = c(
    format(nrow(data), big.mark = ","),
    format(sum(data$is_agricultural, na.rm = TRUE), big.mark = ","),
    paste0(round(sum(data$is_agricultural, na.rm = TRUE) / nrow(data) * 100, 1), "%"),
    format(length(unique(data$user_id[!is.na(data$user_id)])), big.mark = ","),
    paste(format(min(data$created_at, na.rm=TRUE), "%Y-%m-%d"), "~", 
          format(max(data$created_at, na.rm=TRUE), "%Y-%m-%d"))
  )
)
datatable(info_df, options = list(dom = 't'), rownames = FALSE)
```

### 🌾 상위 농수산물 카테고리 (Top 15)
```{r fig.height=6}
ag_top <- data %>% filter(ag_category != "기타", !is.na(ag_category)) %>%
  count(ag_category, sort = TRUE) %>% head(15)
plot_ly(ag_top, x = ~reorder(ag_category, -n), y = ~n, type = 'bar',
        marker = list(color = '#27ae60'), text = ~paste0(n, "건"), textposition = 'outside') %>%
  layout(xaxis = list(title = "", tickangle = -45, automargin = TRUE),
         yaxis = list(title = "게시글 수"), margin = list(b = 100), height = 450)
```

## Column {data-width=350}

### 💰 가격대 분포
```{r fig.height=5}
price_data <- data %>% filter(is_agricultural, price_numeric > 0) %>%
  mutate(range = cut(price_numeric, breaks = c(0,100000,300000,500000,1000000,Inf),
                     labels = c("10만이하","10-30만","30-50만","50-100만","100만이상"))) %>%
  count(range)
plot_ly(price_data, y = ~range, x = ~n, type = 'bar', orientation = 'h',
        marker = list(color = '#3498db'), text = ~paste0(n, "건"), textposition = 'outside') %>%
  layout(xaxis = list(title = "건수"), yaxis = list(title = ""), margin = list(l = 100), height = 400)
```

### 📂 상위 카테고리 (Top 10)
```{r fig.height=5}
cat_top <- data %>% filter(!is.na(category_id)) %>% count(category_id, sort = TRUE) %>%
  head(10) %>% mutate(label = paste0("ID.", category_id))
plot_ly(cat_top, x = ~reorder(label, -n), y = ~n, type = 'bar', marker = list(color = '#9b59b6'),
        text = ~paste0(n, "건"), textposition = 'outside') %>%
  layout(xaxis = list(title = "", tickangle = -45, automargin = TRUE),
         yaxis = list(title = "게시글 수"), margin = list(b = 100), height = 400)
```

# 농수산물 분석 {data-icon="fa-leaf"}

## Column {data-width=600}

### 🥬 카테고리별 분포 (Top 20)
```{r fig.height=6}
ag_all <- data %>% filter(ag_category != "기타", !is.na(ag_category)) %>%
  count(ag_category, sort = TRUE) %>% head(20)
plot_ly(ag_all, x = ~reorder(ag_category, -n), y = ~n, type = 'bar',
        marker = list(color = ~n, colorscale = list(c(0,'#d4edda'), c(1,'#27ae60')), showscale = FALSE),
        text = ~paste0(n, "건"), textposition = 'outside') %>%
  layout(xaxis = list(title = "", tickangle = -45, automargin = TRUE),
         yaxis = list(title = "게시글 수"), margin = list(b = 120), height = 450)
```

### 📦 통계 (Top 15)
```{r}
ag_stat <- data %>% filter(ag_category != "기타", !is.na(ag_category)) %>%
  count(ag_category, sort = TRUE) %>% head(15) %>%
  rename(카테고리 = ag_category, 건수 = n) %>%
  mutate(비율 = paste0(round(건수 / sum(ag_all$n) * 100, 1), "%"))
datatable(ag_stat, options = list(pageLength = 15, dom = 'tip'), rownames = FALSE) %>%
  formatStyle("건수", fontWeight = "bold",
    background = styleColorBar(range(ag_stat$건수), '#90ee90'),
    backgroundSize = '80% 70%', backgroundRepeat = 'no-repeat', backgroundPosition = 'right')
```

## Column {data-width=400}

### 🎯 농수산물 vs 기타
```{r}
comp <- data.frame(구분 = c("농수산물", "기타"), 건수 = c(sum(data$is_agricultural, na.rm=T), 
  nrow(data) - sum(data$is_agricultural, na.rm=T))) %>%
  mutate(비율 = paste0(round(건수 / nrow(data) * 100, 1), "%"))
datatable(comp, options = list(dom = 't'), rownames = FALSE) %>%
  formatStyle("건수", fontWeight = "bold")
```

### 📊 파이차트
```{r}
ag_pie <- data %>% filter(ag_category != "기타") %>%
  count(ag_category, sort = TRUE) %>% head(12)
plot_ly(ag_pie, labels = ~ag_category, values = ~n, type = 'pie', textinfo = 'label+percent',
  marker = list(colors = c('#27ae60','#16a085','#2ecc71','#1abc9c','#3498db','#9b59b6',
                           '#e74c3c','#e67e22','#f39c12','#f1c40f','#26a69a','#5dade2'))) %>%
  layout(height = 400)
```

# 판매 단위 분석 {data-icon="fa-weight"}

## Column {data-width=600}

### 📝 단위 분포
```{r fig.height=6}
unit_data <- data %>% filter(is_agricultural) %>%
  separate_rows(quantity_units, sep = ", ") %>%
  filter(quantity_units != "미표시") %>% count(quantity_units, sort = TRUE) %>% head(15)
plot_ly(unit_data, x = ~reorder(quantity_units, -n), y = ~n, type = 'bar',
  marker = list(color = '#3498db'), text = ~paste0(n, "건"), textposition = 'outside') %>%
  layout(xaxis = list(title = "", tickangle = -45, automargin = TRUE),
  yaxis = list(title = "게시글 수"), margin = list(b = 100), height = 450)
```

### 📊 표시 현황
```{r}
unit_status <- data %>% filter(is_agricultural) %>%
  summarise(`단위표시` = sum(quantity_units != "미표시"),
            `단위미표시` = sum(quantity_units == "미표시")) %>%
  pivot_longer(everything(), names_to = "구분", values_to = "건수") %>%
  mutate(비율 = paste0(round(건수 / sum(건수) * 100, 1), "%"))
datatable(unit_status, options = list(dom = 't'), rownames = FALSE) %>%
  formatStyle("건수", fontWeight = "bold")
```

## Column {data-width=400}

### 🎯 상위 단위 (Top 12)
```{r fig.height=6}
top_units <- data %>% filter(is_agricultural) %>%
  separate_rows(quantity_units, sep = ", ") %>%
  filter(quantity_units != "미표시") %>% count(quantity_units, sort = TRUE) %>% head(12)
plot_ly(top_units, y = ~reorder(quantity_units, n), x = ~n, type = 'bar', orientation = 'h',
  marker = list(color = '#f39c12'), text = ~paste0(n, "건"), textposition = 'outside') %>%
  layout(xaxis = list(title = "게시글 수"), yaxis = list(title = ""),
  margin = list(l = 120), height = 450)
```

# 표현 분석 {data-icon="fa-comment"}

## Column {data-width=500}

### 💬 표현 카테고리별 빈도
```{r fig.height=6}
expr_freq <- data %>% filter(is_agricultural) %>%
  separate_rows(ag_expressions, sep = ", ") %>%
  filter(ag_expressions != "없음") %>% count(ag_expressions, sort = TRUE)
plot_ly(expr_freq, x = ~reorder(ag_expressions, -n), y = ~n, type = 'bar',
  marker = list(color = ~n, colorscale = list(c(0,'#fff3e0'), c(1,'#e65100')), showscale = FALSE),
  text = ~paste0(n, "건"), textposition = 'outside') %>%
  layout(xaxis = list(title = "", tickangle = -45, automargin = TRUE),
  yaxis = list(title = "게시글 수"), margin = list(b = 120), height = 450)
```

### 📊 표현 포함 현황
```{r}
expr_status <- data %>% filter(is_agricultural) %>%
  summarise(`표현포함` = sum(ag_expressions != "없음"),
            `표현없음` = sum(ag_expressions == "없음")) %>%
  pivot_longer(everything(), names_to = "구분", values_to = "건수") %>%
  mutate(비율 = paste0(round(건수 / sum(건수) * 100, 1), "%"))
datatable(expr_status, options = list(dom = 't'), rownames = FALSE) %>%
  formatStyle("건수", fontWeight = "bold")
```

## Column {data-width=500}

### 🎯 상위 표현 (Top 8)
```{r fig.height=6}
top_expr <- data %>% filter(is_agricultural) %>%
  separate_rows(ag_expressions, sep = ", ") %>%
  filter(ag_expressions != "없음") %>% count(ag_expressions, sort = TRUE) %>% head(8)
plot_ly(top_expr, y = ~reorder(ag_expressions, n), x = ~n, type = 'bar', orientation = 'h',
  marker = list(color = '#e74c3c'), text = ~paste0(n, "건"), textposition = 'outside') %>%
  layout(xaxis = list(title = "게시글 수"), yaxis = list(title = ""),
  margin = list(l = 150), height = 400)
```

# 표현 세부 분석 {data-icon="fa-search"}

## Column

### 📝 표현별 세부 키워드 분포
```{r}
if (nrow(expression_detail_data) > 0) {
  detail_sum <- expression_detail_data %>% arrange(표현카테고리, desc(건수)) %>%
    rename(`표현 카테고리` = 표현카테고리, `세부 키워드` = 키워드, `게시글 건수` = 건수)
  datatable(detail_sum, filter = 'top', options = list(pageLength = 30, scrollX = FALSE,
    autoWidth = FALSE, columnDefs = list(list(width = '35%', targets = 0),
    list(width = '35%', targets = 1), list(width = '20%', targets = 2))),
    rownames = FALSE, class = 'display nowrap') %>%
    formatStyle("게시글 건수", fontWeight = "bold",
      background = styleColorBar(range(expression_detail_data$건수), '#fff3e0'),
      backgroundSize = '80% 70%', backgroundRepeat = 'no-repeat', backgroundPosition = 'right') %>%
    formatStyle("표현 카테고리", backgroundColor = "#f8f9fa", fontWeight = "bold")
}
```

# 통합 분석 {data-icon="fa-chart-pie"}

## Column {data-width=600}

### 📊 카테고리별 종합 분석
```{r}
if (nrow(comprehensive_data) > 0) {
  comp_top <- comprehensive_data %>% head(15)
  datatable(comp_top, options = list(pageLength = 15, scrollX = FALSE, autoWidth = FALSE,
    dom = 'tip', columnDefs = list(list(width = '22%', targets = 0),
    list(width = '11%', targets = c(1:8)))), rownames = FALSE) %>%
    formatStyle("종합충족", fontWeight = "bold",
      background = styleColorBar(range(comp_top$종합충족), '#c8e6c9'),
      backgroundSize = '80% 70%', backgroundRepeat = 'no-repeat', backgroundPosition = 'right')
}
```

### 📈 충족율 현황
```{r fig.height=5}
fulfill <- data %>% filter(is_agricultural) %>%
  summarise(`품목만` = sum(fulfillment_count == 1, na.rm = TRUE),
            `품목+단위` = sum(fulfillment_count == 2 & data$has_unit & !data$has_expression, na.rm = TRUE),
            `품목+표현` = sum(fulfillment_count == 2 & !data$has_unit & data$has_expression, na.rm = TRUE),
            `품목+단위+표현` = sum(fulfillment_count == 3, na.rm = TRUE)) %>%
  pivot_longer(everything(), names_to = "분류", values_to = "건수")
plot_ly(fulfill, x = ~reorder(분류, -건수), y = ~건수, type = 'bar',
  marker = list(color = c('#ffcdd2','#ffe082','#fff9c4','#c8e6c9')),
  text = ~paste0(건수, "건"), textposition = 'outside') %>%
  layout(xaxis = list(title = ""), yaxis = list(title = "게시글 수"), margin = list(b = 80), height = 400)
```

## Column {data-width=400}

### 🎯 전체 통계
```{r}
total_comp <- data.frame(
  구분 = c("전체 농수산물", "단위표시", "표현포함", "단위+표현모두"),
  건수 = c(sum(data$is_agricultural, na.rm=TRUE),
           sum(data$has_unit & data$is_agricultural, na.rm=TRUE),
           sum(data$has_expression & data$is_agricultural, na.rm=TRUE),
           sum(data$has_unit & data$has_expression & data$is_agricultural, na.rm=TRUE))
) %>% mutate(비율 = paste0(round(건수 / 건수[1] * 100, 1), "%"))
datatable(total_comp, options = list(dom = 't'), rownames = FALSE) %>%
  formatStyle("건수", fontWeight = "bold",
    background = styleColorBar(range(total_comp$건수), '#c8e6c9'),
    backgroundSize = '80% 70%', backgroundRepeat = 'no-repeat', backgroundPosition = 'right')
```

# 카테고리 분석 {data-icon="fa-folder"}

## Column {data-width=600}

### 📁 Category ID별 분포
```{r fig.height=6}
cat_dist <- data %>% filter(!is.na(category_id)) %>%
  count(category_id, sort = TRUE) %>% head(20) %>% mutate(label = paste0("ID.", category_id))
plot_ly(cat_dist, x = ~reorder(label, -n), y = ~n, type = 'bar',
  marker = list(color = ~n, colorscale = list(c(0,'#e3f2fd'), c(1,'#1976d2')), showscale = FALSE),
  text = ~paste0(n, "건"), textposition = 'outside') %>%
  layout(xaxis = list(title = "", tickangle = -45, automargin = TRUE),
  yaxis = list(title = "게시글 수"), margin = list(b = 100), height = 450)
```

### 📊 농수산물 비율
```{r}
cat_ratio <- data %>% filter(!is.na(category_id)) %>%
  group_by(category_id) %>%
  summarise(전체 = n(), 농수산물 = sum(is_agricultural, na.rm=TRUE),
            비율 = round(sum(is_agricultural, na.rm=TRUE) / n() * 100, 1), .groups='drop') %>%
  arrange(desc(농수산물)) %>% head(15) %>%
  mutate(카테고리ID = paste0("ID.", category_id)) %>%
  select(카테고리ID, 전체, 농수산물, 비율)
datatable(cat_ratio, options = list(pageLength = 15, dom = 'tip'), rownames = FALSE) %>%
  formatStyle("농수산물", fontWeight = "bold",
    background = styleColorBar(range(cat_ratio$농수산물), '#bbdefb'),
    backgroundSize = '80% 70%', backgroundRepeat = 'no-repeat', backgroundPosition = 'right')
```

## Column {data-width=400}

### 🎯 카테고리별 게시글 수 (Top 15)
```{r fig.height=6}
top_cat <- data %>% filter(!is.na(category_id)) %>%
  count(category_id, sort = TRUE) %>% head(15) %>% mutate(label = paste0("ID.", category_id))
plot_ly(top_cat, y = ~reorder(label, n), x = ~n, type = 'bar', orientation = 'h',
  marker = list(color = '#3498db'), text = ~paste0(n, "건"), textposition = 'outside') %>%
  layout(xaxis = list(title = "게시글 수"), yaxis = list(title = ""),
  margin = list(l = 100), height = 500)
```

# 상세 데이터 {data-icon="fa-table"}

## Column

### 📋 농수산물 게시글 상세 정보
```{r}
display_data <- data %>% filter(is_agricultural) %>%
  select(id, title, ag_category, quantity_units, ag_expressions, price_numeric, category_id, user_id, created_at) %>%
  rename(게시글ID = id, 제목 = title, 농수산물 = ag_category, 판매단위 = quantity_units,
         표현 = ag_expressions, 가격 = price_numeric, 카테고리 = category_id,
         사용자ID = user_id, 생성일시 = created_at) %>%
  mutate(제목 = ifelse(nchar(제목) > 80, paste0(substr(제목, 1, 80), "..."), 제목),
         가격 = ifelse(!is.na(가격) & 가격 > 0, paste0(format(round(가격), big.mark = ","), "원"), "나눔/무료"),
         생성일시 = format(생성일시, "%Y-%m-%d %H:%M"))
datatable(display_data, filter = 'top', options = list(pageLength = 20, scrollX = TRUE,
  autoWidth = TRUE, lengthMenu = c(10,20,50,100)), rownames = FALSE)
```