library(shiny)
library(shinydashboard)
library(dplyr)
library(readr)
library(DT)

# ===== データ読み込み（複数のファイル名・エンコーディング対応） =====
load_data <- function() {
  # 優先順位で読み込みを試行するファイル名リスト
  file_candidates <- c(
    "名刺データサンプル.csv",
    "EightData_s.csv",
    "EightData.csv",
    "meishi.csv",
    "data.csv"
  )
  
  encodings <- c("UTF-8", "Shift_JIS", "CP932")
  
  for (file_name in file_candidates) {
    if (file.exists(file_name)) {
      message(paste("📂 ファイル発見:", file_name))
      
      for (enc in encodings) {
        tryCatch({
          df <- read_csv(file_name, locale = locale(encoding = enc), show_col_types = FALSE)
          message(paste("✅ 読み込み成功:", file_name, "エンコーディング:", enc))
          return(df)
        }, error = function(e) {
          message(paste("❌", enc, "で失敗"))
        })
      }
    }
  }
  
  # ファイルが見つからない場合はサンプルデータを生成
  message("⚠️ CSVファイルが見つかりません。サンプルデータを生成します...")
  return(create_sample_data())
}

# サンプルデータ生成関数
create_sample_data <- function() {
  companies <- c("株式会社ABC", "株式会社XYZ", "テクノソリューションズ", "グローバル商事", "デジタルネットワーク")
  departments <- c("営業部", "開発部", "マーケティング部", "人事部", "経理部")
  positions <- c("部長", "課長", "係長", "主任", "担当")
  last_names <- c("田中", "鈴木", "佐藤", "高橋", "山田", "伊藤", "渡辺", "中村", "小林", "加藤")
  first_names <- c("太郎", "花子", "一郎", "二郎", "三郎", "さくら", "翔太", "美咲", "健太", "優子")
  
  n <- 20
  
  data.frame(
    会社名 = sample(companies, n, replace = TRUE),
    部署名 = sample(departments, n, replace = TRUE),
    役職 = sample(positions, n, replace = TRUE),
    姓 = sample(last_names, n, replace = TRUE),
    名 = sample(first_names, n, replace = TRUE),
    `e-mail` = paste0(tolower(sample(last_names, n, replace = TRUE)), "@example.com"),
    郵便番号 = sprintf("%03d-%04d", sample(100:999, n, replace = TRUE), sample(1000:9999, n, replace = TRUE)),
    住所 = paste0("東京都", sample(c("千代田区", "港区", "新宿区", "渋谷区", "中央区"), n, replace = TRUE)),
    TEL会社 = sprintf("03-%04d-%04d", sample(1000:9999, n, replace = TRUE), sample(1000:9999, n, replace = TRUE)),
    TEL部門 = NA_character_,
    TEL直通 = NA_character_,
    Fax = NA_character_,
    携帯電話 = sprintf("090-%04d-%04d", sample(1000:9999, n, replace = TRUE), sample(1000:9999, n, replace = TRUE)),
    URL = paste0("https://", tolower(sample(companies, n, replace = TRUE)), ".co.jp") |> gsub("株式会社", "", x = _),
    名刺交換日 = format(Sys.Date() - sample(1:365, n, replace = TRUE), "%Y/%m/%d"),
    stringsAsFactors = FALSE,
    check.names = FALSE
  )
}

# データ読み込み
df <- load_data()

# 列名の確認と調整
expected_cols <- c("会社名", "部署名", "役職", "姓", "名", "e-mail", "郵便番号", "住所", 
                   "TEL会社", "TEL部門", "TEL直通", "Fax", "携帯電話", "URL", "名刺交換日")

# 存在する列だけ使用
available_cols <- intersect(expected_cols, colnames(df))

message(paste("📊 データ:", nrow(df), "行,", ncol(df), "列"))

# ===== UI =====
ui <- dashboardPage(
  skin = "blue",
  dashboardHeader(title = "📇 名刺データベース"),
  dashboardSidebar(
    sidebarMenu(
      menuItem("🔍 検索・閲覧", tabName = "search", icon = icon("search")),
      menuItem("📊 統計情報", tabName = "stats", icon = icon("bar-chart")),
      menuItem("📥 データ管理", tabName = "download", icon = icon("download"))
    ),
    hr(),
    h4("検索条件", style = "padding-left: 15px;"),
    textInput("keyword", "キーワード検索", placeholder = "名前、会社名、メールなど"),
    selectInput("filter_company", "会社名で絞り込み", 
                choices = c("すべて" = "", sort(unique(na.omit(df$会社名))))),
    selectInput("filter_dept", "部署名で絞り込み", 
                choices = c("すべて" = "", sort(unique(na.omit(df$部署名))))),
    selectInput("filter_position", "役職で絞り込み", 
                choices = c("すべて" = "", sort(unique(na.omit(df$役職))))),
    actionButton("search_btn", "🔎 検索", class = "btn-primary btn-block"),
    actionButton("reset_btn", "🔄 リセット", class = "btn-default btn-block"),
    hr(),
    h5(textOutput("result_count"), style = "padding-left: 15px; font-weight: bold;")
  ),
  dashboardBody(
    tags$head(
      tags$style(HTML("
        .content-wrapper { background-color: #f4f6f9; }
        .box { border-radius: 5px; }
        .dataTables_filter { display: none; }
      "))
    ),
    tabItems(
      # 検索・閲覧タブ
      tabItem(
        tabName = "search",
        fluidRow(
          box(
            title = "検索結果", width = 12, status = "primary", solidHeader = TRUE,
            DT::dataTableOutput("data_table"),
            br(),
            downloadButton("download_csv", "📥 検索結果をCSVでダウンロード", class = "btn-success")
          )
        )
      ),
      # 統計情報タブ
      tabItem(
        tabName = "stats",
        fluidRow(
          infoBox("総レコード数", nrow(df), icon = icon("database"), color = "blue", width = 4),
          infoBox("企業数", length(unique(na.omit(df$会社名))), icon = icon("building"), color = "green", width = 4),
          infoBox("部署数", length(unique(na.omit(df$部署名))), icon = icon("sitemap"), color = "purple", width = 4)
        ),
        fluidRow(
          box(title = "会社別レコード数 (Top 15)", width = 6, status = "info", solidHeader = TRUE,
              tableOutput("company_stats")),
          box(title = "部署別レコード数 (Top 15)", width = 6, status = "info", solidHeader = TRUE,
              tableOutput("department_stats"))
        ),
        fluidRow(
          box(title = "役職別レコード数", width = 6, status = "warning", solidHeader = TRUE,
              tableOutput("position_stats")),
          box(title = "名刺交換日", width = 6, status = "warning", solidHeader = TRUE,
              tableOutput("date_stats"))
        )
      ),
      # データ管理タブ
      tabItem(
        tabName = "download",
        fluidRow(
          box(
            title = "データベース全体のダウンロード", width = 12, status = "success", solidHeader = TRUE,
            p("現在のデータベース全体をCSV形式でダウンロードできます。"),
            downloadButton("download_all_csv", "📥 全データをCSVでダウンロード", class = "btn-primary btn-lg"),
            hr(),
            h4("データベース情報"),
            p(paste("総レコード数:", nrow(df))),
            p(paste("カラム数:", ncol(df))),
            p(paste("カラム:", paste(colnames(df), collapse = ", ")))
          )
        )
      )
    )
  )
)

# ===== Server =====
server <- function(input, output, session) {
  
  # リアクティブ: フィルタリングされたデータ
  filtered_data <- reactive({
    result <- df
    
    # キーワード検索（複数列対象）
    if (!is.null(input$keyword) && input$keyword != "") {
      keyword <- tolower(input$keyword)
      search_cols <- c("会社名", "部署名", "役職", "姓", "名", "e-mail", "住所")
      search_cols <- intersect(search_cols, colnames(result))
      
      result <- result %>%
        filter(
          Reduce(`|`, lapply(search_cols, function(col) {
            grepl(keyword, tolower(as.character(.data[[col]])), fixed = TRUE)
          }))
        )
    }
    
    # 会社名フィルタ
    if (!is.null(input$filter_company) && input$filter_company != "") {
      result <- result %>% filter(会社名 == input$filter_company)
    }
    
    # 部署名フィルタ
    if (!is.null(input$filter_dept) && input$filter_dept != "") {
      result <- result %>% filter(部署名 == input$filter_dept)
    }
    
    # 役職フィルタ
    if (!is.null(input$filter_position) && input$filter_position != "") {
      result <- result %>% filter(役職 == input$filter_position)
    }
    
    result
  })
  
  # リセットボタン
  observeEvent(input$reset_btn, {
    updateTextInput(session, "keyword", value = "")
    updateSelectInput(session, "filter_company", selected = "")
    updateSelectInput(session, "filter_dept", selected = "")
    updateSelectInput(session, "filter_position", selected = "")
  })
  
  # データテーブル表示
  output$data_table <- DT::renderDataTable({
    display_cols <- c("会社名", "部署名", "役職", "姓", "名", "e-mail", "携帯電話", "名刺交換日")
    display_cols <- intersect(display_cols, colnames(filtered_data()))
    
    DT::datatable(
      filtered_data()[, display_cols, drop = FALSE],
      options = list(
        pageLength = 15,
        lengthMenu = c(10, 15, 25, 50, 100),
        scrollX = TRUE,
        language = list(
          lengthMenu = "_MENU_ 件表示",
          search = "テーブル内検索:",
          info = "_TOTAL_ 件中 _START_ ～ _END_ 件を表示",
          paginate = list(previous = "前へ", `next` = "次へ")
        )
      ),
      filter = "top",
      rownames = FALSE
    )
  })
  
  # 検索結果の件数表示
  output$result_count <- renderText({
    paste0("検索結果: ", nrow(filtered_data()), " 件")
  })
  
  # CSV ダウンロード（検索結果）
  output$download_csv <- downloadHandler(
    filename = function() {
      paste0("meishi_search_", format(Sys.time(), "%Y%m%d_%H%M%S"), ".csv")
    },
    content = function(file) {
      write_csv(filtered_data(), file, na = "")
    }
  )
  
  # CSV ダウンロード（全データ）
  output$download_all_csv <- downloadHandler(
    filename = function() {
      paste0("meishi_all_", format(Sys.time(), "%Y%m%d_%H%M%S"), ".csv")
    },
    content = function(file) {
      write_csv(df, file, na = "")
    }
  )
  
  # 統計情報
  output$company_stats <- renderTable({
    df %>%
      filter(!is.na(会社名)) %>%
      group_by(会社名) %>%
      summarise(件数 = n(), .groups = 'drop') %>%
      arrange(desc(件数)) %>%
      head(15)
  })
  
  output$department_stats <- renderTable({
    df %>%
      filter(!is.na(部署名)) %>%
      group_by(部署名) %>%
      summarise(件数 = n(), .groups = 'drop') %>%
      arrange(desc(件数)) %>%
      head(15)
  })
  
  output$position_stats <- renderTable({
    df %>%
      filter(!is.na(役職)) %>%
      group_by(役職) %>%
      summarise(件数 = n(), .groups = 'drop') %>%
      arrange(desc(件数))
  })
  
  output$date_stats <- renderTable({
    df %>%
      filter(!is.na(名刺交換日)) %>%
      group_by(名刺交換日) %>%
      summarise(件数 = n(), .groups = 'drop') %>%
      arrange(desc(名刺交換日)) %>%
      head(15)
  })
}

# アプリを起動
shinyApp(ui, server)