app.R

# app.R - 警備マッチング可視化アプリ（日付×contract_idベースマッチング対応版）
# 要件: 充足率表示、日付期間選択、要営業強化エリア識別
# 修正: ac_date_xxx.csv対応、日付とcontract_idの組み合わせでマッチング
# 追加: 割当済隊員/未割当隊員の表示切替

library(shiny)
library(dplyr)
library(leaflet)

# =========================================================
# 0) 設定
# =========================================================
BBOX <- list(
  lat_min = 36.0,
  lat_max = 36.9,
  lng_min = 139.3,
  lng_max = 140.3
)

# 充足率の閾値
THRESHOLD_LOW  <- 80   # 80%未満 = 要営業強化（赤）
THRESHOLD_HIGH <- 100  # 100%以上 = 充足（緑）

# =========================================================
# 1) ユーティリティ
# =========================================================
read_csv_bom <- function(path) {
  if (!file.exists(path)) stop(paste("ファイルが見つからない:", path))
  df <- tryCatch(
    read.csv(path, fileEncoding = "UTF-8-BOM", stringsAsFactors = FALSE, check.names = FALSE),
    error = function(e) read.csv(path, stringsAsFactors = FALSE, check.names = FALSE)
  )
  df
}

# 日付変換関数（yyyy/m/d または yyyy-m-d 形式対応）
to_date <- function(x) {
  if (inherits(x, "Date")) return(x)
  x <- as.character(x)
  x <- trimws(x)
  x[x == ""] <- NA
  # / を - に統一
  x2 <- gsub("/", "-", x, fixed = TRUE)
  as.Date(x2)
}

haversine_km <- function(lat1, lon1, lat2, lon2) {
  r <- 6371.0
  to_rad <- function(x) x * pi / 180
  dlat <- to_rad(lat2 - lat1)
  dlon <- to_rad(lon2 - lon1)
  a <- sin(dlat/2)^2 + cos(to_rad(lat1)) * cos(to_rad(lat2)) * sin(dlon/2)^2
  2 * r * asin(pmin(1, sqrt(a)))
}

extract_city <- function(addr) {
  if (is.na(addr) || trimws(addr) == "") return(NA_character_)
  a <- gsub("　", " ", addr, fixed = TRUE)
  a <- gsub("\\（.*?\\）", "", a)
  a <- gsub("\\(.*?\\)", "", a)
  a2 <- sub(".*県", "", a)
  m <- regexpr("(市|町|村|区)", a2)
  if (m[1] == -1) return(NA_character_)
  endpos <- m[1] + attr(m, "match.length") - 1
  substr(a2, 1, endpos)
}

# 充足率に応じた色を返す
get_fill_color <- function(rate) {
  case_when(
    is.na(rate) ~ "gray",
    rate < THRESHOLD_LOW ~ "#e74c3c",   # 赤 - 要営業強化
    rate < THRESHOLD_HIGH ~ "#f39c12",  # オレンジ - 注意
    TRUE ~ "#27ae60"                     # 緑 - 充足
  )
}

# =========================================================
# 2) データ読み込み＆前処理
# =========================================================
load_all_data <- function() {
  # 新しいファイル名に変更
  contract_raw <- read_csv_bom("ac_date_contract_list.csv")
  guard_raw    <- read_csv_bom("ac_date_guard_master.csv")
  avail_raw    <- read_csv_bom("ac_date_availability.csv")
  
  # ---- contract_list (日付展開済み) ----
  # 対応日は yyyy/m/d 形式の文字列 → Dateに変換
  contract <- contract_raw %>%
    mutate(
      対応日 = to_date(`対応日`),
      現場住所 = paste0(
        ifelse(is.na(`現場住所１`), "", `現場住所１`),
        ifelse(is.na(`現場住所２`) | trimws(`現場住所２`) == "", "", paste0(" ", `現場住所２`))
      ),
      required_guards = `必要人数`,
      site_city = `市区町村`
    ) %>%
    # 2025/1/1〜2025/12/31のデータに限定
    filter(対応日 >= as.Date("2025-01-01"), 対応日 <= as.Date("2025-12-31"))
  
  # ---- guard_master ----
  guard <- guard_raw %>%
    mutate(
      ユーザー番号 = suppressWarnings(as.integer(`ユーザー番号`)),
      guard_city = vapply(`住所`, extract_city, character(1))
    )
  
  # ---- availability ----
  # 日付はyyyy/m/d形式
  avail <- avail_raw %>%
    mutate(
      日付 = to_date(`日付`),
      ユーザー番号 = suppressWarnings(as.integer(`ユーザー番号`)),
      available_flag = as.integer(`対応可否`),
      担当予定contract_id = trimws(as.character(`担当予定contract_id`))
    ) %>%
    # 担当予定contract_idが空文字やNAの場合はNA扱い
    mutate(担当予定contract_id = ifelse(担当予定contract_id == "" | 担当予定contract_id == "NA", NA_character_, 担当予定contract_id)) %>%
    # 2025/1/1〜2025/12/31のデータに限定
    filter(日付 >= as.Date("2025-01-01"), 日付 <= as.Date("2025-12-31"))
  
  list(contract = contract, guard = guard, avail = avail)
}

DATA <- NULL
LOAD_ERROR <- NULL
tryCatch({
  DATA <- load_all_data()
}, error = function(e) {
  LOAD_ERROR <<- e$message
})

# =========================================================
# 3) UI
# =========================================================
ui <- fluidPage(
  tags$head(
    tags$link(rel = "icon", href = "data:,"),
    tags$style(HTML("
      .legend-box { padding: 10px; background: white; border-radius: 5px; }
      .legend-item { display: flex; align-items: center; margin: 5px 0; }
      .legend-color { width: 20px; height: 20px; border-radius: 50%; margin-right: 8px; }
      .summary-header { background-color: #f8f9fa; padding: 10px; border-radius: 5px; margin-bottom: 10px; }
    "))
  ),
  titlePanel("警備マッチング可視化（日付×契約ID別対応版）"),
  sidebarLayout(
    sidebarPanel(
      if (!is.null(LOAD_ERROR)) {
        tags$div(
          style = "color:#b00020; font-weight:600;",
          paste0("データ読込エラー: ", LOAD_ERROR),
          tags$br(),
          "同じフォルダに ac_date_contract_list.csv / ac_date_guard_master.csv / ac_date_availability.csv を置いているか確認する。"
        )
      } else {
        tagList(
          h4("📅 期間選択"),
          dateRangeInput(
            "date_range",
            "対象期間",
            start = as.Date("2025-01-01"),
            end   = as.Date("2025-03-31"),
            min   = as.Date("2025-01-01"),
            max   = as.Date("2025-12-31"),
            format = "yyyy/mm/dd",
            language = "ja",
            separator = " ～ "
          ),
          hr(),
          h4("🗺️ 表示設定"),
          checkboxInput("show_available", "割当済隊員を表示（対応可否=1）", TRUE),
          checkboxInput("show_non_available", "未割当隊員を表示（対応可否=0）", TRUE),
          radioButtons(
            "match_mode",
            "追加マッチング方法（未割当隊員の参考）",
            choices = c("市区町村一致" = "city", "距離（半径km）" = "dist"),
            selected = "city"
          ),
          conditionalPanel(
            condition = "input.match_mode == 'dist'",
            numericInput("radius_km", "半径（km）", value = 20, min = 1, max = 200, step = 1)
          ),
          hr(),
          h4("📊 凡例"),
          tags$div(
            class = "legend-box",
            tags$div(class = "legend-item",
              tags$div(class = "legend-color", style = "background-color: #27ae60;"),
              tags$span("充足（100%以上）")
            ),
            tags$div(class = "legend-item",
              tags$div(class = "legend-color", style = "background-color: #f39c12;"),
              tags$span("注意（80-99%）")
            ),
            tags$div(class = "legend-item",
              tags$div(class = "legend-color", style = "background-color: #e74c3c;"),
              tags$span("要営業強化（80%未満）")
            ),
            tags$div(class = "legend-item",
              tags$div(class = "legend-color", style = "background-color: #3498db;"),
              tags$span("割当済隊員（対応可否=1）")
            ),
            tags$div(class = "legend-item",
              tags$div(class = "legend-color", style = "background-color: #95a5a6;"),
              tags$span("未割当隊員（対応可否=0）")
            )
          ),
          hr(),
          helpText("※ 対応可能人数 = 日付×契約IDが一致する担当予定の隊員数をカウント")
        )
      },
      width = 3
    ),
    mainPanel(
      # サマリー統計
      fluidRow(
        column(3, 
          tags$div(class = "summary-header",
            h5("稼働現場件数"),
            textOutput("stat_sites", inline = TRUE)
          )
        ),
        column(3,
          tags$div(class = "summary-header",
            h5("必要人数合計"),
            textOutput("stat_required", inline = TRUE)
          )
        ),
        column(3,
          tags$div(class = "summary-header",
            h5("対応可能人数"),
            textOutput("stat_available", inline = TRUE)
          )
        ),
        column(3,
          tags$div(class = "summary-header",
            h5("平均充足率"),
            textOutput("stat_avg_rate", inline = TRUE)
          )
        )
      ),
      # 隊員統計
      fluidRow(
        column(6,
          tags$div(class = "summary-header",
            h5("割当済隊員数（期間内）"),
            textOutput("stat_available_guards", inline = TRUE)
          )
        ),
        column(6,
          tags$div(class = "summary-header",
            h5("未割当隊員数（期間内）"),
            textOutput("stat_non_available_guards", inline = TRUE)
          )
        )
      ),
      leafletOutput("map", height = 480),
      br(),
      h4("現場別 需給状況（選択期間・日付×契約ID別集計）"),
      tableOutput("summary"),
      width = 9
    )
  )
)

# =========================================================
# 4) Server
# =========================================================
server <- function(input, output, session) {
  if (!is.null(LOAD_ERROR)) {
    output$map <- renderLeaflet({ leaflet() %>% addTiles() })
    output$summary <- renderTable({
      data.frame(エラー = LOAD_ERROR, stringsAsFactors = FALSE)
    })
    return()
  }
  
  contract <- DATA$contract
  guard    <- DATA$guard
  avail    <- DATA$avail
  
  # 選択期間のデータを取得
  daily <- reactive({
    date_range <- input$date_range
    d_start <- date_range[1]
    d_end   <- date_range[2]
    
    # 期間内の現場（日付×contract_idの組み合わせ）
    active_sites <- contract %>%
      filter(!is.na(対応日)) %>%
      filter(対応日 >= d_start, 対応日 <= d_end)
    
    # 期間内で担当予定がある隊員（日付×contract_id単位）- 充足率計算用
    assigned_avail <- avail %>%
      filter(!is.na(日付), 日付 >= d_start, 日付 <= d_end) %>%
      filter(available_flag == 1L, !is.na(担当予定contract_id))
    
    # 割当済隊員（対応可否=1）
    available_guards_avail <- avail %>%
      filter(!is.na(日付), 日付 >= d_start, 日付 <= d_end) %>%
      filter(available_flag == 1L) %>%
      distinct(ユーザー番号)
    
    available_guards <- available_guards_avail %>%
      inner_join(guard, by = "ユーザー番号")
    
    # 未割当隊員（対応可否=0）
    non_available_guards_avail <- avail %>%
      filter(!is.na(日付), 日付 >= d_start, 日付 <= d_end) %>%
      filter(available_flag == 0L) %>%
      distinct(ユーザー番号)
    
    non_available_guards <- non_available_guards_avail %>%
      inner_join(guard, by = "ユーザー番号")
    
    list(
      date_start = d_start,
      date_end = d_end,
      active_sites = active_sites,
      assigned_avail = assigned_avail,
      available_guards = available_guards,
      non_available_guards = non_available_guards
    )
  })
  
  # 需給サマリー計算（日付×contract_id別）
  summary_tbl <- reactive({
    dd <- daily()
    sites <- dd$active_sites
    assigned <- dd$assigned_avail
    
    if (nrow(sites) == 0) {
      return(data.frame(メッセージ = "選択期間に稼働中の現場がない", stringsAsFactors = FALSE))
    }
    
    # 日付×contract_id別の割当済み隊員数をカウント
    assigned_count <- assigned %>%
      count(日付, 担当予定contract_id, name = "assigned_guards")
    
    # 現場データと結合
    result <- sites %>%
      left_join(
        assigned_count,
        by = c("対応日" = "日付", "contract_id" = "担当予定contract_id")
      ) %>%
      mutate(
        assigned_guards = ifelse(is.na(assigned_guards), 0L, assigned_guards),
        shortage = pmax(required_guards - assigned_guards, 0L),
        fulfillment_rate = round(assigned_guards / required_guards * 100, 0)
      )
    
    # 色と状態を追加
    result <- result %>%
      mutate(
        fill_color = get_fill_color(fulfillment_rate),
        status = case_when(
          fulfillment_rate < THRESHOLD_LOW ~ "⚠️ 要営業強化",
          fulfillment_rate < THRESHOLD_HIGH ~ "△ 注意",
          TRUE ~ "○ 充足"
        )
      )
    
    result
  })
  
  # 表示用テーブル（日付×契約ID別）
  output$summary <- renderTable({
    tbl <- summary_tbl()
    if ("メッセージ" %in% colnames(tbl)) return(tbl)
    
    tbl %>%
      transmute(
        対応日 = format(対応日, "%Y/%m/%d"),
        契約ID = contract_id,
        契約No = `契約No`,
        顧客 = `顧客`,
        件名 = paste0(`件名１`, ifelse(is.na(`件名２`) | trimws(`件名２`) == "" | `件名２` == "NA", "", paste0(" ", `件名２`))),
        市区町村 = site_city,
        必要人数 = required_guards,
        対応可能 = as.integer(assigned_guards),
        充足率 = paste0(fulfillment_rate, "%"),
        状態 = status
      ) %>%
      arrange(対応日, 契約ID)
  })
  
  # サマリー統計
  output$stat_sites <- renderText({
    tbl <- summary_tbl()
    if ("メッセージ" %in% colnames(tbl)) return("-")
    # ユニークな日付×契約IDの組み合わせ数
    paste0(nrow(tbl), " 件（日付×契約ID）")
  })
  
  output$stat_required <- renderText({
    tbl <- summary_tbl()
    if ("メッセージ" %in% colnames(tbl)) return("-")
    paste0(sum(tbl$required_guards, na.rm = TRUE), " 人")
  })
  
  output$stat_available <- renderText({
    tbl <- summary_tbl()
    if ("メッセージ" %in% colnames(tbl)) return("-")
    paste0(sum(tbl$assigned_guards, na.rm = TRUE), " 人")
  })
  
  output$stat_avg_rate <- renderText({
    tbl <- summary_tbl()
    if ("メッセージ" %in% colnames(tbl)) return("-")
    avg_rate <- mean(tbl$fulfillment_rate, na.rm = TRUE)
    paste0(round(avg_rate, 0), "%")
  })
  
  # 隊員統計
  output$stat_available_guards <- renderText({
    dd <- daily()
    paste0(nrow(dd$available_guards), " 人")
  })
  
  output$stat_non_available_guards <- renderText({
    dd <- daily()
    paste0(nrow(dd$non_available_guards), " 人")
  })
  
  # 地図（初回のみ）
  output$map <- renderLeaflet({
    center_lat <- mean(c(BBOX$lat_min, BBOX$lat_max))
    center_lng <- mean(c(BBOX$lng_min, BBOX$lng_max))
    
    leaflet() %>%
      addTiles() %>%
      setView(lng = center_lng, lat = center_lat, zoom = 9) %>%
      addLayersControl(
        overlayGroups = c("現場（充足率）", "割当済隊員", "未割当隊員"),
        options = layersControlOptions(collapsed = FALSE)
      )
  })
  
  # 地図更新（マーカー）
  observe({
    dd <- daily()
    sites_data <- summary_tbl()
    available_gds <- dd$available_guards
    non_available_gds <- dd$non_available_guards
    
    proxy <- leafletProxy("map")
    proxy %>%
      clearGroup("現場（充足率）") %>%
      clearGroup("割当済隊員") %>%
      clearGroup("未割当隊員")
    
    # 現場マーカー（充足率付き）- 地理的にユニークな場所ごとに集約
    if (!("メッセージ" %in% colnames(sites_data)) && nrow(sites_data) > 0) {
      # 同じ座標の現場を集約（代表値として平均充足率を表示）
      sites_geo <- sites_data %>%
        filter(!is.na(site_lat), !is.na(site_lng)) %>%
        group_by(site_lat, site_lng, site_city, `契約No`, `件名１`) %>%
        summarise(
          avg_rate = round(mean(fulfillment_rate, na.rm = TRUE), 0),
          total_required = sum(required_guards, na.rm = TRUE),
          total_assigned = sum(assigned_guards, na.rm = TRUE),
          n_days = n(),
          .groups = "drop"
        ) %>%
        mutate(
          fill_color = get_fill_color(avg_rate)
        )
      
      if (nrow(sites_geo) > 0) {
        proxy %>%
          addCircleMarkers(
            data = sites_geo,
            lng = ~site_lng, lat = ~site_lat,
            radius = 14,
            color = ~fill_color,
            fillColor = ~fill_color,
            fillOpacity = 0.85,
            weight = 2,
            label = ~paste0(
              "【", `契約No`, "】", `件名１`,
              " | 平均充足率: ", avg_rate, "%",
              " | 延べ必要: ", total_required, "人",
              " | 延べ対応: ", total_assigned, "人",
              " | 対応日数: ", n_days, "日"
            ),
            group = "現場（充足率）"
          )
        
        # 充足率ラベルを個別に追加
        for (i in seq_len(nrow(sites_geo))) {
          row <- sites_geo[i, ]
          proxy %>%
            addLabelOnlyMarkers(
              lng = row$site_lng,
              lat = row$site_lat,
              label = paste0(row$avg_rate, "%"),
              labelOptions = labelOptions(
                noHide = TRUE,
                direction = "top",
                textOnly = TRUE,
                style = list(
                  "font-weight" = "bold",
                  "font-size" = "11px",
                  "color" = "white",
                  "background-color" = row$fill_color,
                  "padding" = "2px 5px",
                  "border-radius" = "4px"
                )
              ),
              group = "現場（充足率）"
            )
        }
      }
    }
    
    # 割当済隊員マーカー（青）
    if (isTRUE(input$show_available) && nrow(available_gds) > 0) {
      gds_unique <- available_gds %>%
        filter(!is.na(home_lat), !is.na(home_lng)) %>%
        distinct(ユーザー番号, .keep_all = TRUE)
      
      if (nrow(gds_unique) > 0) {
        proxy %>%
          addCircleMarkers(
            data = gds_unique,
            lng = ~home_lng, lat = ~home_lat,
            radius = 6,
            color = "#3498db",
            fillColor = "#3498db",
            fillOpacity = 0.7,
            weight = 1,
            label = ~paste0("【割当済】ユーザー番号: ", ユーザー番号, " | ", 苗字, " ", 名前, " | ", guard_city),
            group = "割当済隊員"
          )
      }
    }
    
    # 未割当隊員マーカー（グレー）
    if (isTRUE(input$show_non_available) && nrow(non_available_gds) > 0) {
      gds_unique <- non_available_gds %>%
        filter(!is.na(home_lat), !is.na(home_lng)) %>%
        distinct(ユーザー番号, .keep_all = TRUE)
      
      if (nrow(gds_unique) > 0) {
        proxy %>%
          addCircleMarkers(
            data = gds_unique,
            lng = ~home_lng, lat = ~home_lat,
            radius = 6,
            color = "#95a5a6",
            fillColor = "#95a5a6",
            fillOpacity = 0.7,
            weight = 1,
            label = ~paste0("【未割当】ユーザー番号: ", ユーザー番号, " | ", 苗字, " ", 名前, " | ", guard_city),
            group = "未割当隊員"
          )
      }
    }
  })
}

shinyApp(ui, server)