app.R

# app.R - 警備マッチング可視化アプリ（改良版）
# 要件: 充足率表示、日付期間選択、要営業強化エリア識別

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
}

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("contract_list.csv")
  guard_raw    <- read_csv_bom("guard_master.csv")
  avail_raw    <- read_csv_bom("availability.csv")
  
  # ---- contract_list ----
  contract <- contract_raw %>%
    mutate(
      `案件予定日(開始)` = to_date(`案件予定日(開始)`),
      `案件予定日(終了)` = to_date(`案件予定日(終了)`),
      現場住所 = paste0(
        ifelse(is.na(`現場住所１`), "", `現場住所１`),
        ifelse(is.na(`現場住所２`) | trimws(`現場住所２`) == "", "", paste0(" ", `現場住所２`))
      ),
      required_guards = `必要人数`,
      site_city = `市区町村`
    )
  
  # ---- guard_master ----
  guard <- guard_raw %>%
    mutate(
      従業員番号 = suppressWarnings(as.integer(`従業員番号`)),
      guard_city = vapply(`住所`, extract_city, character(1))
    )
  
  # ---- availability ----
  avail <- avail_raw %>%
    mutate(
      日付 = to_date(`日付`),
      従業員番号 = suppressWarnings(as.integer(`従業員番号`)),
      available_flag = as.integer(`対応可否`)
    )
  
  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("警備マッチング可視化（充足率・期間選択対応）"),
  sidebarLayout(
    sidebarPanel(
      if (!is.null(LOAD_ERROR)) {
        tags$div(
          style = "color:#b00020; font-weight:600;",
          paste0("データ読込エラー: ", LOAD_ERROR),
          tags$br(),
          "同じフォルダに contract_list.csv / guard_master.csv / availability.csv を置いているか確認する。"
        )
      } else {
        tagList(
          h4("📅 期間選択"),
          sliderInput(
            "date_range",
            "対象期間",
            min   = min(DATA$contract$`案件予定日(開始)`, na.rm = TRUE),
            max   = max(DATA$contract$`案件予定日(終了)`, na.rm = TRUE),
            value = c(
              min(DATA$contract$`案件予定日(開始)`, na.rm = TRUE),
              min(DATA$contract$`案件予定日(開始)`, na.rm = TRUE) + 7
            ),
            timeFormat = "%m/%d",
            step = 1
          ),
          hr(),
          h4("🗺️ 表示設定"),
          checkboxInput("show_guards", "アベイラブル隊員を表示", 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; opacity: 0.7;"),
              tags$span("アベイラブル隊員")
            )
          )
        )
      },
      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)
          )
        )
      ),
      leafletOutput("map", height = 480),
      br(),
      h4("現場別 需給状況（選択期間）"),
      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]
    
    # 期間内に稼働中の現場
    active_sites <- contract %>%
      filter(!is.na(`案件予定日(開始)`), !is.na(`案件予定日(終了)`)) %>%
      filter(`案件予定日(開始)` <= d_end, `案件予定日(終了)` >= d_start)
    
    # 期間内で「対応可」の日が1日でもある隊員
    available_emp <- avail %>%
      filter(!is.na(日付), 日付 >= d_start, 日付 <= d_end, available_flag == 1L) %>%
      distinct(従業員番号)
    
    available_guards <- guard %>%
      inner_join(available_emp, by = "従業員番号")
    
    list(
      date_start = d_start,
      date_end = d_end,
      active_sites = active_sites,
      available_guards = available_guards
    )
  })
  
  # 需給サマリー計算
  summary_tbl <- reactive({
    dd <- daily()
    sites <- dd$active_sites
    gds   <- dd$available_guards
    
    if (nrow(sites) == 0) {
      return(data.frame(メッセージ = "選択期間に稼働中の現場がない", stringsAsFactors = FALSE))
    }
    
    if (input$match_mode == "city") {
      g_by_city <- gds %>%
        mutate(guard_city = ifelse(is.na(guard_city), "不明", guard_city)) %>%
        count(guard_city, name = "available_guards")
      
      result <- sites %>%
        mutate(site_city = ifelse(is.na(site_city), "不明", site_city)) %>%
        left_join(g_by_city, by = c("site_city" = "guard_city")) %>%
        mutate(
          available_guards = ifelse(is.na(available_guards), 0L, available_guards),
          shortage = pmax(required_guards - available_guards, 0L),
          fulfillment_rate = round(available_guards / required_guards * 100, 0)
        )
    } else {
      radius <- input$radius_km
      if (is.null(radius) || is.na(radius) || radius <= 0) radius <- 20
      
      if (nrow(gds) == 0) {
        result <- sites %>%
          mutate(
            available_guards = 0L,
            shortage = required_guards,
            fulfillment_rate = 0
          )
      } else {
        result <- sites %>%
          rowwise() %>%
          mutate(
            available_guards = {
              dist <- haversine_km(site_lat, site_lng, gds$home_lat, gds$home_lng)
              sum(dist <= radius, na.rm = TRUE)
            }
          ) %>%
          ungroup() %>%
          mutate(
            shortage = pmax(required_guards - available_guards, 0L),
            fulfillment_rate = round(available_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
  })
  
  # 表示用テーブル
  output$summary <- renderTable({
    tbl <- summary_tbl()
    if ("メッセージ" %in% colnames(tbl)) return(tbl)
    
    tbl %>%
      transmute(
        契約ID = contract_id,
        契約No = `契約No`,
        顧客 = `顧客`,
        件名 = paste0(`件名１`, ifelse(is.na(`件名２`) | trimws(`件名２`) == "", "", paste0(" ", `件名２`))),
        市区町村 = site_city,
        必要人数 = required_guards,
        対応可能 = as.integer(available_guards),
        充足率 = paste0(fulfillment_rate, "%"),
        状態 = status
      )
  })
  
  # サマリー統計
  output$stat_sites <- renderText({
    dd <- daily()
    paste0(nrow(dd$active_sites), " 件")
  })
  
  output$stat_required <- renderText({
    tbl <- summary_tbl()
    if ("メッセージ" %in% colnames(tbl)) return("-")
    paste0(sum(tbl$required_guards, na.rm = TRUE), " 人")
  })
  
  output$stat_available <- renderText({
    dd <- daily()
    paste0(nrow(dd$available_guards), " 人")
  })
  
  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$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()
    gds <- dd$available_guards
    
    proxy <- leafletProxy("map")
    proxy %>%
      clearGroup("現場（充足率）") %>%
      clearGroup("アベイラブル隊員")
    
    # 現場マーカー（充足率付き）
    if (!("メッセージ" %in% colnames(sites_data)) && nrow(sites_data) > 0) {
      # CircleMarkersを追加
      proxy %>%
        addCircleMarkers(
          data = sites_data,
          lng = ~site_lng, lat = ~site_lat,
          radius = 14,
          color = ~fill_color,
          fillColor = ~fill_color,
          fillOpacity = 0.85,
          weight = 2,
          label = ~paste0(
            "【", `契約No`, "】", `件名１`,
            " | 充足率: ", fulfillment_rate, "%",
            " | 必要: ", required_guards, "人",
            " | 対応可: ", available_guards, "人"
          ),
          group = "現場（充足率）"
        )
      
      # 充足率ラベルを個別に追加（色を動的に設定するため）
      for (i in seq_len(nrow(sites_data))) {
        row <- sites_data[i, ]
        proxy %>%
          addLabelOnlyMarkers(
            lng = row$site_lng,
            lat = row$site_lat,
            label = paste0(row$fulfillment_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_guards) && nrow(gds) > 0) {
      proxy %>%
        addCircleMarkers(
          data = gds,
          lng = ~home_lng, lat = ~home_lat,
          radius = 6,
          color = "#3498db",
          fillColor = "#3498db",
          fillOpacity = 0.6,
          weight = 1,
          label = ~paste0("従業員番号: ", 従業員番号, " | ", 苗字, " ", 名前, " | ", guard_city),
          group = "アベイラブル隊員"
        )
    }
  })
}

shinyApp(ui, server)