未分類

import sys
import os
import sqlite3
import pandas as pd
import numpy as np
from datetime import datetime, timedelta, timezone
import re
import pyodbc
import shutil
import requests
import json
import math
import glob
import io

from PyQt6.QtWidgets import (QApplication, QMainWindow, QTabWidget, QWidget, 
                             QVBoxLayout, QHBoxLayout, QPushButton, QLabel, 
                             QFileDialog, QTableWidget, QTableWidgetItem, QComboBox, 
                             QDateEdit, QMessageBox, QGroupBox, QHeaderView, QTextEdit, 
                             QLineEdit, QCheckBox, QFormLayout, QScrollArea, QMenu, QGridLayout,
                             QSystemTrayIcon, QStyle, QRadioButton, QButtonGroup, QFrame, QDateTimeEdit)
from PyQt6.QtCore import QDate, Qt, QPoint, QTimer, QDateTime

import matplotlib
matplotlib.use('QtAgg')
matplotlib.rc('font', family=['Meiryo', 'MS Gothic', 'Yu Gothic', 'sans-serif'])
from matplotlib.backends.backend_qtagg import FigureCanvasQTAgg as FigureCanvas
from matplotlib.backends.backend_qtagg import NavigationToolbar2QT as NavigationToolbar
from matplotlib.figure import Figure

try:
    import contextily as cx
    HAS_CX = True
except ImportError:
    HAS_CX = False

class FilterableTableWidget(QTableWidget):
    def __init__(self, rows, columns):
        super().__init__(rows, columns)
        self.horizontalHeader().sectionClicked.connect(self.show_filter_menu)
        self.filters = {}
        self.setStyleSheet("QHeaderView::section { background-color: #F6F8FA; padding: 5px; border: 1px solid #D0D7DE; }")

    def keyPressEvent(self, event):
        if event.key() == Qt.Key.Key_C and (event.modifiers() & Qt.KeyboardModifier.ControlModifier):
            self.copy_selection()
        else:
            super().keyPressEvent(event)

    def copy_selection(self):
        selection = self.selectedIndexes()
        if not selection: return
        rows = sorted(list(set(idx.row() for idx in selection)))
        cols = sorted(list(set(idx.column() for idx in selection)))
        copy_text = ""
        for r in rows:
            row_data = []
            for c in cols:
                item = self.item(r, c)
                if item and self.isItemSelected(item):
                    row_data.append(item.text().replace("\n", " "))
                else:
                    row_data.append("")
            copy_text += "\t".join(row_data) + "\n"
        QApplication.clipboard().setText(copy_text)

    def show_filter_menu(self, logical_index):
        menu = QMenu(self)
        values = set()
        for row in range(self.rowCount()):
            item = self.item(row, logical_index)
            if item: values.add(item.text())
        values = sorted(list(values))
        
        action_all = menu.addAction("すべて表示 (フィルタ解除)")
        menu.addSeparator()
        for val in values: menu.addAction(val)
            
        header_pos = self.horizontalHeader().sectionViewportPosition(logical_index)
        global_pos = self.mapToGlobal(self.horizontalHeader().pos() + QPoint(header_pos, self.horizontalHeader().height()))
        
        selected_action = menu.exec(global_pos)
        if not selected_action: return
        
        if selected_action == action_all:
            if logical_index in self.filters: del self.filters[logical_index]
        else:
            self.filters[logical_index] = selected_action.text()
        self.apply_filters()

    def apply_filters(self):
        for row in range(self.rowCount()):
            hidden = False
            for col, filter_val in self.filters.items():
                item = self.item(row, col)
                if item and item.text() != filter_val:
                    hidden = True; break
            self.setRowHidden(row, hidden)


class WeatherVerificationApp(QMainWindow):
    def __init__(self):
        super().__init__()
        self.setWindowTitle("気象予報精度検証システム (Ver 17.0 - GRIB2・API自動連携/DB統合版)")
        self.resize(1500, 950)
        self.really_quit = False 
        
        self.setStyleSheet("""
            QMainWindow { background-color: #F4F6F9; font-family: 'Segoe UI', 'Meiryo', sans-serif; }
            QTabWidget::pane { border: 1px solid #D0D7DE; background: white; border-radius: 8px; }
            QTabBar::tab { background: #FFD180; color: #555; padding: 10px 20px; margin-right: 2px; border-top-left-radius: 8px; border-top-right-radius: 8px; font-weight: bold; }
            QTabBar::tab:selected { background: #FF8C00; color: white; border-bottom: 2px solid #E65100; }
            QGroupBox { font-weight: bold; color: #E65100; border: 1px solid #D0D7DE; border-radius: 6px; margin-top: 15px; background-color: #FFFFFF; }
            QGroupBox::title { subcontrol-origin: margin; left: 10px; padding: 0 5px; }
            QPushButton { background-color: #FF8C00; color: white; border: none; border-radius: 5px; padding: 8px 16px; font-weight: bold; }
            QPushButton:hover { background-color: #F57C00; }
            QPushButton:pressed { background-color: #E65100; }
            QPushButton:disabled { background-color: #A0A0A0; color: #E0E0E0; }
            QPushButton#ActionBtn { background-color: #28A745; }
            QPushButton#ActionBtn:hover { background-color: #218838; }
            QPushButton#TestBtn { background-color: #007BFF; }
            QPushButton#TestBtn:hover { background-color: #0069D9; }
            QPushButton#AutoBtn { background-color: #D73A49; }
            QPushButton#QuitBtn { background-color: #CB2431; }
            QLineEdit, QComboBox, QDateEdit, QDateTimeEdit { border: 1px solid #D0D7DE; border-radius: 4px; padding: 5px; background-color: #FAFBFC; min-height: 25px; }
            QDateEdit, QDateTimeEdit { min-width: 140px; font-size: 13px; }
            QComboBox { min-width: 120px; }
            QTableWidget { gridline-color: #E1E4E8; border: 1px solid #D0D7DE; font-size: 13px; }
            QScrollArea { border: none; background-color: transparent; }
        """)

        self.tray_icon = QSystemTrayIcon(self)
        self.tray_icon.setIcon(self.style().standardIcon(QStyle.StandardPixmap.SP_ComputerIcon))
        self.tray_icon.setToolTip("気象検証システム - 継続抽出・計算中")
        
        tray_menu = QMenu()
        show_action = QAction("画面を表示する", self)
        show_action.triggered.connect(self.showNormal)
        quit_action = QAction("完全に終了する", self)
        quit_action.triggered.connect(self.force_quit)
        tray_menu.addAction(show_action); tray_menu.addSeparator(); tray_menu.addAction(quit_action)
        self.tray_icon.setContextMenu(tray_menu); self.tray_icon.show()
        self.tray_icon.activated.connect(self.on_tray_icon_activated)

        self.db_path = "weather_verification(削除禁止).db"
        self.last_forecast_folder = "未設定"
        
        # 札幌圏 座標設定 (GRIB2抽出用)
        self.lat_min = 42.0 + (40.0 / 60.0)
        self.lat_max = 43.0 + (20.0 / 60.0)
        self.lon_min = 141.0 + (0.0 / 60.0)
        self.lon_max = 141.0 + (40.0 / 60.0)
        self.map_aspect_ratio = 1.0 / math.cos(math.radians(43.0))
        self.wgrib2_path = "wgrib2.exe"
        self.grib_target_points = []
        self.grib_selected_point = None

        self.t1_auto_timer = QTimer(self); self.t1_auto_timer.timeout.connect(self.extract_and_aggregate_obs)
        self.t2_auto_timer = QTimer(self); self.t2_auto_timer.timeout.connect(self.fetch_amedas_api)
        self.t3_auto_timer = QTimer(self); self.t3_auto_timer.timeout.connect(self.auto_load_forecast_model_folder)

        self.init_db()
        self.init_ui()

    def on_tray_icon_activated(self, reason):
        if reason == QSystemTrayIcon.ActivationReason.DoubleClick:
            self.showNormal(); self.activateWindow()

    def closeEvent(self, event):
        if not self.really_quit:
            event.ignore(); self.hide()
            self.tray_icon.showMessage("バックグラウンドで実行中", "自動抽出・計算処理は継続しています。\n完全に終了する場合は右クリックまたは完全終了ボタンから終了してください。", QSystemTrayIcon.MessageIcon.Information, 3000)
        else:
            event.accept()

    def force_quit(self):
        self.really_quit = True
        QApplication.quit()

    def select_db(self):
        path, _ = QFileDialog.getOpenFileName(self, "SQLiteデータベースを選択", "", "SQLite DB (*.db);;すべてのファイル (*)")
        if path:
            self.db_path = path
            self.lbl_db_path.setText(f"📁 現在のDB: {os.path.basename(path)}")
            try:
                self.conn.close(); self.init_db()
                self.update_tab1_station_combo(); self.update_tab5_station_combo()
                QMessageBox.information(self, "完了", f"データベースを切り替えました。\n{path}")
            except Exception as e: QMessageBox.critical(self, "エラー", f"データベースの読み込みに失敗しました:\n{e}")

    def init_db(self):
        self.conn = sqlite3.connect(self.db_path)
        cursor = self.conn.cursor()

        cursor.execute("PRAGMA table_info(OBS)")
        obs_cols = [r[1] for r in cursor.fetchall()]
        if obs_cols and "MsCd" not in obs_cols: cursor.execute("DROP TABLE OBS")
        if obs_cols and "SnowDepth" not in obs_cols:
            try: cursor.execute("ALTER TABLE OBS ADD COLUMN SnowDepth REAL")
            except: pass

        cursor.execute('''CREATE TABLE IF NOT EXISTS OBS (ObsDay TEXT, ObsTime TEXT, MsCd TEXT, RainfallP1 REAL, SnowfallP1 REAL, SnowDepth REAL, PRIMARY KEY (ObsDay, ObsTime, MsCd))''')
        cursor.execute('''CREATE TABLE IF NOT EXISTS FCST (IssueDay TEXT, IssueTime TEXT, TargetDay TEXT, TargetTime TEXT, MsCd TEXT, ForecastStep INTEGER, FcstRainfall REAL, FcstSnowfall REAL, PRIMARY KEY (IssueDay, IssueTime, TargetDay, TargetTime, MsCd, ForecastStep))''')
        cursor.execute('''CREATE TABLE IF NOT EXISTS Mapping (ObsCd TEXT PRIMARY KEY, FcstName TEXT)''')
        cursor.execute('''CREATE TABLE IF NOT EXISTS JMA_FCST (TargetStart TEXT, TargetEnd TEXT, Region TEXT, Element TEXT, RawText TEXT, MinVal REAL, MaxVal REAL, PRIMARY KEY (TargetStart, TargetEnd, Region, Element))''')
        
        # ★追加: GRIB2座標とIDの紐付けマスタ
        cursor.execute('''CREATE TABLE IF NOT EXISTS GPV_Points (PointID INTEGER PRIMARY KEY AUTOINCREMENT, Lon REAL, Lat REAL, UNIQUE(Lon, Lat))''')

        cursor.execute("PRAGMA table_info(TargetStations)")
        if "TemplateId" not in [r[1] for r in cursor.fetchall()]:
            cursor.execute("DROP TABLE IF EXISTS TargetStations")
            cursor.execute('''CREATE TABLE TargetStations (TemplateId INTEGER, FcstName TEXT, PRIMARY KEY (TemplateId, FcstName))''')
            targets = [(1, "大通り・円山・山鼻"), (1, "屯田・篠路"), (1, "苗穂・元町・栄町"), (1, "菊水・白石・南郷"), (1, "大谷地・新札幌"), (1, "豊平・平岸・月寒"), (1, "真駒内・澄川・藻岩下"), (1, "琴似・発寒"), (1, "前田・山口"), (1, "北野・清田・里塚"), (1, "花川・花畔・新港"), (1, "恵庭"), (1, "石山・常盤"), (1, "野幌・江別")]
            cursor.executemany("INSERT INTO TargetStations VALUES (?,?)", targets)

        cursor.execute("SELECT COUNT(*) FROM Mapping")
        if cursor.fetchone()[0] == 0:
            defaults = [("MS1", "大通り・円山・山鼻"), ("MS2", "屯田・篠路"), ("アメダス:札幌", "大通り・円山・山鼻"), ("アメダス:石狩", "花川・花畔・新港"), ("アメダス:恵庭島松", "恵庭"), ("アメダス:小金湯", "石山・常盤"), ("アメダス:江別", "野幌・江別"), ("アメダス:手稲山口", "前田・山口")]
            cursor.executemany("INSERT OR IGNORE INTO Mapping VALUES (?,?)", defaults)
        self.conn.commit()

    def create_scroll_tab(self):
        scroll = QScrollArea(); scroll.setWidgetResizable(True); inner_widget = QWidget(); scroll.setWidget(inner_widget); return scroll, inner_widget

    def init_ui(self):
        main_widget = QWidget()
        main_layout = QVBoxLayout(main_widget)
        
        top_header = QHBoxLayout()
        self.lbl_db_path = QLabel(f"📁 現在のDB: {os.path.basename(self.db_path)}")
        self.lbl_db_path.setStyleSheet("font-weight: bold; color: #555;")
        btn_db = QPushButton("⚙️ DBを選択 / 変更")
        btn_db.clicked.connect(self.select_db)
        btn_quit = QPushButton("❌ 完全に終了する")
        btn_quit.setObjectName("QuitBtn")
        btn_quit.clicked.connect(self.force_quit)
        
        top_header.addWidget(self.lbl_db_path); top_header.addStretch()
        top_header.addWidget(btn_db); top_header.addWidget(btn_quit)
        main_layout.addLayout(top_header)

        self.tabs = QTabWidget()
        main_layout.addWidget(self.tabs)
        self.setCentralWidget(main_widget)

        self.setup_tab1(); self.setup_tab2(); self.setup_tab8() # 新GRIB2タブ
        self.setup_tab3(); self.setup_tab4(); self.setup_tab5(); self.setup_tab6(); self.setup_tab7()

    # ==========================================
    # タブ1: MS観測値
    # ==========================================
    def setup_tab1(self):
        scroll, inner_widget = self.create_scroll_tab(); layout = QVBoxLayout(inner_widget); layout.setSpacing(15)
        group_db = QGroupBox("SQL Server 接続設定 ＆ データ抽出"); db_layout = QVBoxLayout(); db_layout.setContentsMargins(15, 25, 15, 15)
        
        header_layout = QHBoxLayout()
        self.lbl_t1_auto = QLabel(""); self.lbl_t1_auto.setStyleSheet("color: #D73A49; font-weight: bold; font-size: 14px;")
        header_layout.addStretch(); header_layout.addWidget(self.lbl_t1_auto); db_layout.addLayout(header_layout)

        form_layout = QFormLayout()
        self.cmb_driver = QComboBox(); self.cmb_driver.setEditable(True); self.cmb_driver.addItems(["SQL Server", "ODBC Driver 17 for SQL Server"])
        self.txt_server = QLineEdit("172."); self.txt_db = QLineEdit("S"); self.txt_user = QLineEdit("sa"); self.txt_pwd = QLineEdit("")
        self.txt_pwd.setEchoMode(QLineEdit.EchoMode.Password)
        form_layout.addRow("ODBC:", self.cmb_driver); form_layout.addRow("Host:", self.txt_server)
        form_layout.addRow("DB:", self.txt_db); form_layout.addRow("User:", self.txt_user); form_layout.addRow("Pass:", self.txt_pwd)
        db_layout.addLayout(form_layout)
        
        filter_layout = QHBoxLayout(); self.t1_date_from = QDateEdit(QDate.currentDate().addDays(-7)); self.t1_date_from.setCalendarPopup(True)
        self.t1_date_to = QDateEdit(QDate.currentDate()); self.t1_date_to.setCalendarPopup(True)
        filter_layout.addWidget(QLabel("抽出期間:")); filter_layout.addWidget(self.t1_date_from); filter_layout.addWidget(QLabel("～")); filter_layout.addWidget(self.t1_date_to); filter_layout.addStretch()
        db_layout.addLayout(filter_layout)
        
        action_layout = QHBoxLayout()
        btn_test_conn = QPushButton("🔌 接続テスト"); btn_test_conn.setObjectName("TestBtn"); btn_test_conn.clicked.connect(self.test_sql_connection)
        btn_extract = QPushButton("手動で抽出して保存"); btn_extract.setObjectName("ActionBtn"); btn_extract.clicked.connect(self.extract_and_aggregate_obs)
        self.btn_t1_auto = QPushButton("MS自動取得を開始 (10分毎)"); self.btn_t1_auto.setObjectName("AutoBtn"); self.btn_t1_auto.clicked.connect(self.toggle_t1_auto)
        action_layout.addWidget(btn_test_conn); action_layout.addWidget(btn_extract); action_layout.addWidget(self.btn_t1_auto); action_layout.addStretch()
        db_layout.addLayout(action_layout); group_db.setLayout(db_layout)

        group_view = QGroupBox("ローカルDB (OBS) 検索・閲覧ビューア"); view_layout = QVBoxLayout(); view_layout.setContentsMargins(15, 25, 15, 15)
        search_layout = QHBoxLayout(); self.v_date_from = QDateEdit(QDate.currentDate().addDays(-7)); self.v_date_from.setCalendarPopup(True)
        self.v_date_to = QDateEdit(QDate.currentDate()); self.v_date_to.setCalendarPopup(True)
        self.v_cmb_station = QComboBox(); self.update_tab1_station_combo()
        btn_view_search = QPushButton("検索"); btn_view_search.clicked.connect(self.search_obs_table)
        search_layout.addWidget(QLabel("表示期間:")); search_layout.addWidget(self.v_date_from); search_layout.addWidget(QLabel("～")); search_layout.addWidget(self.v_date_to)
        search_layout.addWidget(QLabel("地点:")); search_layout.addWidget(self.v_cmb_station); search_layout.addWidget(btn_view_search); search_layout.addStretch()
        view_layout.addLayout(search_layout)

        self.tbl_obs = FilterableTableWidget(0, 6)
        self.tbl_obs.setHorizontalHeaderLabels(["観測日▽", "観測時間▽", "地点コード▽", "降水量(mm)▽", "降雪量(cm)▽", "積雪深(cm)▽"])
        self.tbl_obs.horizontalHeader().setSectionResizeMode(QHeaderView.ResizeMode.Stretch)
        self.tbl_obs.setMinimumHeight(350); view_layout.addWidget(self.tbl_obs); group_view.setLayout(view_layout)

        layout.addWidget(group_db); layout.addWidget(group_view); self.tabs.addTab(scroll, "1. 観測値設定① (MS)")

    def test_sql_connection(self):
        QMessageBox.information(self, "省略", "接続テスト（省略）")

    def toggle_t1_auto(self):
        if self.t1_auto_timer.isActive(): self.t1_auto_timer.stop(); self.lbl_t1_auto.setText(""); self.btn_t1_auto.setText("MS自動取得を開始")
        else: self.t1_auto_timer.start(600000); self.lbl_t1_auto.setText("🔴 MS自動取得中"); self.btn_t1_auto.setText("MS自動取得を停止")

    def update_tab1_station_combo(self):
        self.v_cmb_station.clear(); self.v_cmb_station.addItem("すべて")
        cursor = self.conn.cursor(); cursor.execute("SELECT DISTINCT MsCd FROM OBS ORDER BY MsCd"); self.v_cmb_station.addItems([str(r[0]) for r in cursor.fetchall()])

    def search_obs_table(self):
        d_from = self.v_date_from.date().toString("yyyy-MM-dd"); d_to = self.v_date_to.date().toString("yyyy-MM-dd"); st = self.v_cmb_station.currentText()
        cursor = self.conn.cursor()
        if st == "すべて": cursor.execute("SELECT ObsDay, ObsTime, MsCd, RainfallP1, SnowfallP1, SnowDepth FROM OBS WHERE ObsDay BETWEEN ? AND ? ORDER BY ObsDay DESC, ObsTime DESC LIMIT 300", (d_from, d_to))
        else: cursor.execute("SELECT ObsDay, ObsTime, MsCd, RainfallP1, SnowfallP1, SnowDepth FROM OBS WHERE MsCd=? AND ObsDay BETWEEN ? AND ? ORDER BY ObsDay DESC, ObsTime DESC LIMIT 300", (st, d_from, d_to))
        rows = cursor.fetchall(); self.tbl_obs.setRowCount(len(rows))
        for r_i, r_data in enumerate(rows):
            for c_i, c_data in enumerate(r_data): self.tbl_obs.setItem(r_i, c_i, QTableWidgetItem(str(c_data) if c_data is not None else ""))

    def get_sql_connection_string(self):
        return f"DRIVER={{{self.cmb_driver.currentText()}}};SERVER={self.txt_server.text()};DATABASE={self.txt_db.text()};UID={self.txt_user.text()};PWD={self.txt_pwd.text()}"

    def extract_and_aggregate_obs(self):
        pass # 省略（前回実装済み）

    # ==========================================
    # タブ2: アメダス抽出 (API機能統合)
    # ==========================================
    def setup_tab2(self):
        scroll, inner_widget = self.create_scroll_tab(); layout = QVBoxLayout(inner_widget); layout.setSpacing(15)
        
        group_api = QGroupBox("気象庁 アメダスAPI (JSON) から自動取得")
        api_layout = QVBoxLayout(); api_layout.setContentsMargins(15, 20, 15, 15)
        lbl_api = QLabel("札幌圏のアメダス（札幌、石狩、恵庭島松、小金湯、江別、手稲山口）の最新データを気象庁APIから取得し、\nOBSテーブルに保存します。※ 降雪量は積雪深の差分から自動計算されます。")
        api_layout.addWidget(lbl_api)
        
        h_api = QHBoxLayout()
        btn_api_now = QPushButton("最新アメダスデータを1回取得"); btn_api_now.setObjectName("ActionBtn"); btn_api_now.clicked.connect(self.fetch_amedas_api)
        self.btn_t2_auto = QPushButton("アメダスAPI自動取得開始 (10分毎)"); self.btn_t2_auto.setObjectName("AutoBtn"); self.btn_t2_auto.clicked.connect(self.toggle_t2_auto)
        self.lbl_t2_auto = QLabel(""); self.lbl_t2_auto.setStyleSheet("color: #D73A49; font-weight: bold;")
        h_api.addWidget(btn_api_now); h_api.addWidget(self.btn_t2_auto); h_api.addWidget(self.lbl_t2_auto); h_api.addStretch()
        api_layout.addLayout(h_api); group_api.setLayout(api_layout)
        
        group_csv = QGroupBox("気象庁アメダス CSV取り込み (過去データ用)")
        csv_layout = QHBoxLayout(); csv_layout.setContentsMargins(15, 20, 15, 15)
        btn_csv = QPushButton("JMA CSVを選択してOBSへ取り込み"); btn_csv.setObjectName("TestBtn"); btn_csv.clicked.connect(self.import_jma_csv)
        csv_layout.addWidget(btn_csv); csv_layout.addStretch(); group_csv.setLayout(csv_layout)
        
        self.txt_log2 = QTextEdit(); self.txt_log2.setReadOnly(True); self.txt_log2.setText("ここに読込ログが表示されます..."); self.txt_log2.setMinimumHeight(350)
        
        layout.addWidget(group_api); layout.addWidget(group_csv); layout.addWidget(self.txt_log2); layout.addStretch(); self.tabs.addTab(scroll, "2. アメダス観測値 (API/CSV)")

    def toggle_t2_auto(self):
        if self.t2_auto_timer.isActive(): self.t2_auto_timer.stop(); self.lbl_t2_auto.setText(""); self.btn_t2_auto.setText("アメダスAPI自動取得開始")
        else: self.t2_auto_timer.start(600000); self.lbl_t2_auto.setText("🔴 API自動取得中"); self.btn_t2_auto.setText("アメダス自動取得停止"); self.fetch_amedas_api()

    def fetch_amedas_api(self):
        # 札幌周辺の地点コード
        target_stations = {"14163": "札幌", "14111": "石狩", "14311": "恵庭島松", "14166": "小金湯", "14164": "江別", "14136": "手稲山口"}
        try:
            # 最新のデータ時刻を取得するためのメタ情報を取得 (簡略化のため、現在時刻の直近の3時間ごとの最新データなどを叩くロジック)
            # 気象庁API: 最新時刻のJSONを取得
            latest_time_url = "https://www.jma.go.jp/bosai/amedas/data/latest_time.txt"
            res_time = requests.get(latest_time_url, timeout=5)
            latest_time_iso = res_time.text.strip() # 例: 2026-06-26T21:40:00+09:00
            
            # API URLの形式用に変換 (yyyyMMddHHmm00.json)
            dt = datetime.fromisoformat(latest_time_iso)
            time_str = dt.strftime("%Y%m%d%H%M00")
            
            data_url = f"https://www.jma.go.jp/bosai/amedas/data/map/{time_str}.json"
            res_data = requests.get(data_url, timeout=5)
            if res_data.status_code != 200: raise Exception(f"API HTTP Status: {res_data.status_code}")
            
            data_json = res_data.json()
            
            obs_day = dt.strftime("%Y-%m-%d")
            obs_time = dt.strftime("%H:%M")
            
            cursor = self.conn.cursor()
            inserted = 0
            
            self.txt_log2.append(f"--- API取得開始: {dt.strftime('%Y-%m-%d %H:%M')} (JST) ---")
            
            for st_code, st_name in target_stations.items():
                if st_code in data_json:
                    st_data = data_json[st_code]
                    rain = st_data.get('precipitation1h', [0.0])[0]
                    snow_depth = st_data.get('snow', [None])[0]
                    
                    ms_cd = f"アメダス:{st_name}"
                    
                    # 降雪量は前回の積雪深との差分
                    snowfall = 0.0
                    if snow_depth is not None:
                        cursor.execute("SELECT SnowDepth FROM OBS WHERE MsCd=? AND SnowDepth IS NOT NULL ORDER BY ObsDay DESC, ObsTime DESC LIMIT 1", (ms_cd,))
                        last_row = cursor.fetchone()
                        if last_row and last_row[0] is not None:
                            snowfall = max(0.0, float(snow_depth) - float(last_row[0]))
                    
                    cursor.execute("INSERT OR REPLACE INTO OBS (ObsDay, ObsTime, MsCd, RainfallP1, SnowfallP1, SnowDepth) VALUES (?, ?, ?, ?, ?, ?)",
                                   (obs_day, obs_time, ms_cd, float(rain) if rain is not None else 0.0, snowfall, snow_depth))
                    inserted += 1
            
            self.conn.commit()
            self.txt_log2.append(f"✅ 完了: {inserted} 件の最新データを保存しました。")
            self.update_tab1_station_combo()
            
            if not self.t2_auto_timer.isActive(): QMessageBox.information(self, "完了", f"最新アメダスデータの取得が完了しました。\n保存件数: {inserted} 件")
            
        except Exception as e:
            err_msg = f"API取得エラー: {e}"
            self.txt_log2.append(f"❌ {err_msg}")
            if not self.t2_auto_timer.isActive(): QMessageBox.critical(self, "エラー", err_msg)

    def import_jma_csv(self):
        pass # 省略（前回実装済み）

    # ==========================================
    # ★ 新タブ8: 解析値 (GRIB2) 抽出＆マップレビュー機能
    # ==========================================
    def setup_tab8(self):
        scroll, inner_widget = self.create_scroll_tab(); layout = QHBoxLayout(inner_widget); layout.setSpacing(15)
        
        # 左パネル: 設定・DB書き込み・レビュー呼び出し
        left_panel = QWidget(); left_layout = QVBoxLayout(left_panel); left_panel.setFixedWidth(450)
        
        # --- モード選択 ---
        group_mode = QGroupBox("1. GRIB2 データ取得モード")
        mode_layout = QVBoxLayout()
        self.grib_radio_latest = QRadioButton("🌟 最新データを取得 (自動取得用)")
        self.grib_radio_latest.setChecked(True)
        self.grib_radio_range = QRadioButton("📅 過去データを期間指定して一括抽出")
        mode_group = QButtonGroup(); mode_group.addButton(self.grib_radio_latest); mode_group.addButton(self.grib_radio_range)
        
        date_layout = QHBoxLayout()
        self.grib_date_start = QDateEdit(QDate.currentDate().addDays(-1)); self.grib_date_start.setCalendarPopup(True); self.grib_date_start.setEnabled(False)
        self.grib_date_end = QDateEdit(QDate.currentDate()); self.grib_date_end.setCalendarPopup(True); self.grib_date_end.setEnabled(False)
        date_layout.addWidget(QLabel("開始:")); date_layout.addWidget(self.grib_date_start); date_layout.addWidget(QLabel("〜 終了:")); date_layout.addWidget(self.grib_date_end)
        self.grib_radio_range.toggled.connect(lambda checked: self.grib_date_start.setEnabled(checked))
        self.grib_radio_range.toggled.connect(lambda checked: self.grib_date_end.setEnabled(checked))
        
        mode_layout.addWidget(self.grib_radio_latest); mode_layout.addWidget(self.grib_radio_range); mode_layout.addLayout(date_layout)
        group_mode.setLayout(mode_layout); left_layout.addWidget(group_mode)

        # --- フォルダ設定 ＆ DB書き込み実行 ---
        group_folders = QGroupBox("2. 解析値データ フォルダ設定 ＆ DB一括書込")
        f_layout = QVBoxLayout()
        self.grib_folders = {"rain": "", "snowfall": "", "snowdepth": ""}
        self.grib_labels = {}
        for key, icon, name in [("rain", "🌧️", "解析雨量"), ("snowfall", "❄️", "解析降雪量"), ("snowdepth", "⛄", "解析積雪深")]:
            row = QVBoxLayout()
            lbl = QLabel(f"{icon} {name} フォルダ: 未設定")
            self.grib_labels[key] = lbl
            btn_layout = QHBoxLayout()
            btn_set = QPushButton("フォルダ選択"); btn_set.clicked.connect(lambda _, k=key: self.select_grib_folder(k))
            btn_ext = QPushButton(f"{name} 全座標をDBへ書込"); btn_ext.setStyleSheet("background-color: #0366D6; color: white;")
            btn_ext.clicked.connect(lambda _, k=key, n=name: self.run_grib_db_extraction(k, n))
            btn_layout.addWidget(btn_set); btn_layout.addWidget(btn_ext)
            row.addWidget(lbl); row.addLayout(btn_layout)
            line = QFrame(); line.setFrameShape(QFrame.Shape.HLine); line.setFrameShadow(QFrame.Shadow.Sunken)
            row.addWidget(line); f_layout.addLayout(row)
        group_folders.setLayout(f_layout); left_layout.addWidget(group_folders)

        # --- 地図レビュー呼び出し ---
        group_review = QGroupBox("3. データベースからの マップレビュー表示")
        r_layout = QVBoxLayout(); r_layout.setContentsMargins(15, 15, 15, 15)
        r_layout.addWidget(QLabel("※データベースに保存済みのデータを地図に展開します"))
        
        form_r = QFormLayout()
        self.rev_dt = QDateTimeEdit(QDateTime.currentDateTime())
        self.rev_dt.setDisplayFormat("yyyy-MM-dd HH:00")
        self.rev_element = QComboBox(); self.rev_element.addItems(["降水量 (RainfallP1)", "降雪量 (SnowfallP1)", "積雪深 (SnowDepth)"])
        form_r.addRow("表示日時 (JST):", self.rev_dt)
        form_r.addRow("表示要素:", self.rev_element)
        r_layout.addLayout(form_r)
        
        btn_review = QPushButton("🗺️ 指定日時のマップを描画"); btn_review.setObjectName("ActionBtn")
        btn_review.clicked.connect(self.load_map_review_from_db)
        r_layout.addWidget(btn_review)
        
        self.lbl_grib_selected = QLabel("📍 地図上の点をクリックして値を確認")
        self.lbl_grib_selected.setStyleSheet("color: #D73A49; font-weight: bold; font-size: 14px; padding: 10px; background-color: #FFF0F0; border: 1px solid #D73A49;")
        r_layout.addWidget(self.lbl_grib_selected)
        group_review.setLayout(r_layout); left_layout.addWidget(group_review)

        left_layout.addStretch(1)
        layout.addWidget(left_panel)

        # 右パネル: 地図エリア
        right_panel = QWidget(); right_layout = QVBoxLayout(right_panel)
        self.grib_figure = Figure()
        self.grib_canvas = FigureCanvas(self.grib_figure)
        self.grib_toolbar = NavigationToolbar(self.grib_canvas, self); self.grib_toolbar.hide()
        self.grib_canvas.mpl_connect('button_press_event', self.on_grib_map_click)

        map_controls = QHBoxLayout()
        self.btn_g_sel = QPushButton("👆 選択"); self.btn_g_sel.clicked.connect(self.set_grib_mode_select)
        self.btn_g_zoom = QPushButton("🔍 ズーム"); self.btn_g_zoom.clicked.connect(self.set_grib_mode_zoom)
        self.btn_g_pan = QPushButton("✋ 移動"); self.btn_g_pan.clicked.connect(self.set_grib_mode_pan)
        btn_g_home = QPushButton("🏠 全体表示"); btn_g_home.clicked.connect(self.grib_toolbar.home)
        for btn in [self.btn_g_sel, self.btn_g_zoom, self.btn_g_pan, btn_g_home]: btn.setStyleSheet("padding: 8px; font-weight: bold;")
        self.set_grib_mode_select()
        
        map_controls.addWidget(self.btn_g_sel); map_controls.addWidget(self.btn_g_zoom); map_controls.addWidget(self.btn_g_pan); map_controls.addWidget(btn_g_home); map_controls.addStretch(1)
        right_layout.addLayout(map_controls); right_layout.addWidget(self.grib_canvas)
        layout.addWidget(right_panel, stretch=1)
        
        self.tabs.insertTab(2, scroll, "3. 解析値 (GRIB2) 抽出＆マップ")
        self.plot_initial_grib_map()

    def set_grib_mode_select(self):
        if self.grib_toolbar.mode == 'zoom rect': self.grib_toolbar.zoom()
        if self.grib_toolbar.mode == 'pan/zoom': self.grib_toolbar.pan()
        self.btn_g_sel.setStyleSheet("background-color: #28A745; color: white;"); self.btn_g_zoom.setStyleSheet(""); self.btn_g_pan.setStyleSheet("")
    def set_grib_mode_zoom(self):
        if self.grib_toolbar.mode != 'zoom rect': self.grib_toolbar.zoom()
        self.btn_g_sel.setStyleSheet(""); self.btn_g_zoom.setStyleSheet("background-color: #0366D6; color: white;"); self.btn_g_pan.setStyleSheet("")
    def set_grib_mode_pan(self):
        if self.grib_toolbar.mode != 'pan/zoom': self.grib_toolbar.pan()
        self.btn_g_sel.setStyleSheet(""); self.btn_g_zoom.setStyleSheet(""); self.btn_g_pan.setStyleSheet("background-color: #0366D6; color: white;")

    def select_grib_folder(self, key):
        folder = QFileDialog.getExistingDirectory(self, "フォルダを選択")
        if folder:
            self.grib_folders[key] = folder
            self.grib_labels[key].setText(f"📁 {folder}")

    def plot_initial_grib_map(self):
        self.g_ax = self.grib_figure.add_subplot(111)
        self.g_ax.clear()
        self.g_ax.set_xlim(self.lon_min - 0.05, self.lon_max + 0.05)
        self.g_ax.set_ylim(self.lat_min - 0.05, self.lat_max + 0.05)
        self.g_ax.set_aspect(self.map_aspect_ratio, adjustable='box')
        self.g_ax.set_title("札幌圏 レビューマップ")
        if HAS_CX:
            try: cx.add_basemap(self.g_ax, crs="EPSG:4326", source=cx.providers.OpenStreetMap.Mapnik, alpha=0.8, zoom=13)
            except: pass
        self.g_ax.grid(True, linestyle='--', alpha=0.5)
        self.grib_canvas.draw()

    # --- GRIB2 全座標の抽出とDB完全書き込み ---
    def run_grib_db_extraction(self, data_type_key, data_name):
        folder_path = self.grib_folders[data_type_key]
        if not folder_path or not os.path.exists(folder_path):
            return QMessageBox.warning(self, "警告", f"{data_name} のフォルダが設定されていません。")
        if not os.path.exists(self.wgrib2_path):
            return QMessageBox.critical(self, "エラー", f"'{self.wgrib2_path}' が見つかりません。")

        if data_type_key == "rain": pattern = "*Prr60lv*.bin"
        elif data_type_key == "snowfall": pattern = "*Psflv*.bin"
        elif data_type_key == "snowdepth": pattern = "*Psdlv*.bin"

        bin_files = glob.glob(os.path.join(folder_path, pattern))
        if not bin_files:
            return QMessageBox.warning(self, "ファイルなし", "対象ファイルが見つかりません。")
        
        bin_files.sort()
        target_files = []
        if self.grib_radio_latest.isChecked():
            target_files = [bin_files[-1]]
        else:
            sd = self.grib_date_start.date().toString("yyyyMMdd")
            ed = self.grib_date_end.date().addDays(1).toString("yyyyMMdd")
            for f in bin_files:
                match = re.search(r'(20\d{6})', os.path.basename(f))
                if match and (sd <= match.group(1) < ed): target_files.append(f)
            if not target_files: return QMessageBox.warning(self, "該当なし", "指定期間内にファイルがありません。")

        temp_csv = os.path.abspath("grib_temp_export.csv")
        total_inserted = 0
        cursor = self.conn.cursor()

        try:
            for target_file in target_files:
                subprocess.run([self.wgrib2_path, target_file, "-csv", temp_csv], stdout=subprocess.PIPE, stderr=subprocess.PIPE, text=True)
                
                with open(temp_csv, 'r', encoding='utf-8', errors='replace') as f:
                    csv_lines = [line for line in f.readlines() if "," in line]
                if not csv_lines: continue
                
                df = pd.read_csv(io.StringIO("".join(csv_lines)), header=None)
                raw_lon = pd.to_numeric(df.iloc[:, -3], errors='coerce')
                raw_lat = pd.to_numeric(df.iloc[:, -2], errors='coerce')
                df["Value"] = pd.to_numeric(df.iloc[:, -1], errors='coerce')
                
                if raw_lat.mean() > raw_lon.mean(): df["Lon"], df["Lat"] = raw_lat, raw_lon
                else: df["Lon"], df["Lat"] = raw_lon, raw_lat
                df = df.dropna(subset=["Lon", "Lat", "Value"])
                
                # 札幌圏でフィルタリング
                f_df = df[(df["Lat"] >= self.lat_min) & (df["Lat"] <= self.lat_max) & (df["Lon"] >= self.lon_min) & (df["Lon"] <= self.lon_max)]
                if f_df.empty: continue
                
                # ★ 緯度・経度のリストからマスタ(GPV_Points)を更新し、絶対的PointIDを取得
                unique_coords = f_df[['Lon', 'Lat']].drop_duplicates().sort_values(by=['Lat', 'Lon'], ascending=[False, True]).reset_index(drop=True)
                
                for _, row in unique_coords.iterrows():
                    cursor.execute("INSERT OR IGNORE INTO GPV_Points (Lon, Lat) VALUES (?, ?)", (row['Lon'], row['Lat']))
                
                # DBからPointIDのリストを再取得しマージ
                db_points = pd.read_sql_query("SELECT PointID, Lon, Lat FROM GPV_Points", self.conn)
                f_df = pd.merge(f_df, db_points, on=['Lon', 'Lat'], how='left')

                # ファイル名からJST日時を算出 (UTC + 9h)
                match = re.search(r'(\d{12})', os.path.basename(target_file))
                if match:
                    dt_utc = datetime.strptime(match.group(1), "%Y%m%d%H%M")
                    dt_jst = dt_utc + timedelta(hours=9)
                    obs_day = dt_jst.strftime("%Y-%m-%d")
                    obs_time = dt_jst.strftime("%H:%M")
                else:
                    continue

                # OBSテーブルへ流し込み
                for _, r in f_df.iterrows():
                    ms_cd = f"GPV_{int(r['PointID'])}"
                    val = r['Value']
                    
                    # すでにレコードがあるか確認し、無ければ初期値で作成
                    cursor.execute("SELECT RainfallP1, SnowfallP1, SnowDepth FROM OBS WHERE ObsDay=? AND ObsTime=? AND MsCd=?", (obs_day, obs_time, ms_cd))
                    existing = cursor.fetchone()
                    if existing:
                        r_p = val if data_type_key == "rain" else existing[0]
                        s_p = val if data_type_key == "snowfall" else existing[1]
                        s_d = val if data_type_key == "snowdepth" else existing[2]
                        cursor.execute("UPDATE OBS SET RainfallP1=?, SnowfallP1=?, SnowDepth=? WHERE ObsDay=? AND ObsTime=? AND MsCd=?", (r_p, s_p, s_d, obs_day, obs_time, ms_cd))
                    else:
                        r_p = val if data_type_key == "rain" else 0.0
                        s_p = val if data_type_key == "snowfall" else 0.0
                        s_d = val if data_type_key == "snowdepth" else None
                        cursor.execute("INSERT INTO OBS (ObsDay, ObsTime, MsCd, RainfallP1, SnowfallP1, SnowDepth) VALUES (?,?,?,?,?,?)", (obs_day, obs_time, ms_cd, r_p, s_p, s_d))
                    total_inserted += 1

            self.conn.commit()
            self.update_tab1_station_combo()
            self.update_tab5_station_combo()
            QMessageBox.information(self, "書込完了", f"データベースへの書込が完了しました。\n処理ファイル数: {len(target_files)}\n書込座標データ数: {total_inserted} 件")

        except Exception as e:
            QMessageBox.critical(self, "エラー", f"抽出処理エラー:\n{e}")

    # --- DBからのマップレビュー表示 ---
    def load_map_review_from_db(self):
        dt_str = self.rev_dt.dateTime().toString("yyyy-MM-dd HH:mm")
        obs_day, obs_time = dt_str.split(" ")
        
        elem_txt = self.rev_element.currentText()
        if "RainfallP1" in elem_txt: col = "RainfallP1"; unit = "mm"
        elif "SnowfallP1" in elem_txt: col = "SnowfallP1"; unit = "cm"
        else: col = "SnowDepth"; unit = "cm"

        query = f"""
            SELECT p.PointID, p.Lon, p.Lat, o.{col}
            FROM OBS o
            JOIN GPV_Points p ON o.MsCd = 'GPV_' || p.PointID
            WHERE o.ObsDay = ? AND o.ObsTime = ? AND o.{col} IS NOT NULL
        """
        df = pd.read_sql_query(query, self.conn, params=(obs_day, obs_time))
        
        if df.empty:
            return QMessageBox.warning(self, "データなし", f"{dt_str} の {elem_txt} データはデータベースに存在しません。")

        self.grib_target_points = list(zip(df['Lon'], df['Lat']))
        self.grib_current_df = df # クリック時の値取得用
        self.current_grib_unit = unit
        self.grib_selected_point = None

        self.g_ax.clear()
        self.g_ax.set_xlim(self.lon_min - 0.05, self.lon_max + 0.05)
        self.g_ax.set_ylim(self.lat_min - 0.05, self.lat_max + 0.05)
        self.g_ax.set_aspect(self.map_aspect_ratio, adjustable='box')
        self.g_ax.set_title(f"マップレビュー: {dt_str} ({elem_txt}) - {len(df)}地点")
        if HAS_CX:
            try: cx.add_basemap(self.g_ax, crs="EPSG:4326", source=cx.providers.OpenStreetMap.Mapnik, alpha=0.8, zoom=13)
            except: pass
        self.g_ax.grid(True, linestyle='--', alpha=0.5)
        
        ps = 5 if len(df) > 1000 else 40
        pa = 0.6 if len(df) > 1000 else 1.0
        self.g_ax.scatter(df['Lon'], df['Lat'], c='red', marker='o', s=ps, alpha=pa, edgecolors='none' if len(df)>1000 else 'black')
        self.grib_canvas.draw()
        
        self.lbl_grib_selected.setText("📍 描画完了: 地図上の点をクリックして値を確認できます")

    def on_grib_map_click(self, event):
        if self.grib_toolbar.mode != '': return
        if event.xdata is None or event.ydata is None or not self.grib_target_points: return
            
        click_lon, click_lat = event.xdata, event.ydata
        closest_point = None
        min_dist = float('inf')
        for lon, lat in self.grib_target_points:
            dist = (lon - click_lon)**2 + (lat - click_lat)**2
            if dist < min_dist:
                min_dist = dist
                closest_point = (lon, lat)
                
        if closest_point:
            self.grib_selected_point = closest_point
            
            if hasattr(self, 'grib_current_df'):
                tr = self.grib_current_df[
                    (self.grib_current_df["Lon"].round(4) == round(closest_point[0], 4)) & 
                    (self.grib_current_df["Lat"].round(4) == round(closest_point[1], 4))
                ]
                if not tr.empty:
                    val = tr.iloc[0].iloc[3] # 4列目が値
                    pid = tr.iloc[0]["PointID"]
                    
                    lat_deg = int(closest_point[1]); lat_min = (closest_point[1] - lat_deg) * 60
                    lon_deg = int(closest_point[0]); lon_min = (closest_point[0] - lon_deg) * 60
                    
                    self.lbl_grib_selected.setText(
                        f"【選択地点】 🆔 GPV_{int(pid)}   |   📍 北緯 {lat_deg}°{lat_min:.1f}' / 東経 {lon_deg}°{lon_min:.1f}'\n"
                        f"📊 データ値: {val} {self.current_grib_unit}"
                    )
            
            # 選択点の青星を描画するため再描画
            self.g_ax.clear()
            self.g_ax.set_xlim(self.lon_min - 0.05, self.lon_max + 0.05)
            self.g_ax.set_ylim(self.lat_min - 0.05, self.lat_max + 0.05)
            self.g_ax.set_aspect(self.map_aspect_ratio, adjustable='box')
            self.g_ax.set_title(self.g_ax.get_title())
            if HAS_CX:
                try: cx.add_basemap(self.g_ax, crs="EPSG:4326", source=cx.providers.OpenStreetMap.Mapnik, alpha=0.8, zoom=13)
                except: pass
            self.g_ax.grid(True, linestyle='--', alpha=0.5)
            
            df = self.grib_current_df
            ps = 5 if len(df) > 1000 else 40
            pa = 0.6 if len(df) > 1000 else 1.0
            self.g_ax.scatter(df['Lon'], df['Lat'], c='red', marker='o', s=ps, alpha=pa, edgecolors='none' if len(df)>1000 else 'black')
            self.g_ax.scatter([self.grib_selected_point[0]], [self.grib_selected_point[1]], c='blue', marker='*', s=300, edgecolors='white', zorder=5)
            self.grib_canvas.draw()


    # ==========================================
    # 以降のタブ (3,4,5,6,7) は既存のロジックを継承
    # ==========================================
    def setup_tab3(self): pass # 省略 (既存処理と同じため、実際のシステムでは上記コードを維持)
    def setup_tab4(self): pass
    def setup_tab5(self): pass
    def setup_tab6(self): pass
    def setup_tab7(self): pass
    # ※ 実働させる際は、プロンプト上部の Tab3〜7のコード (load_target_stations 等) をそのまま挿入してください。


if __name__ == "__main__":
    app = QApplication(sys.argv)
    app.setQuitOnLastWindowClosed(False)
    window = WeatherVerificationApp()
    window.show()
    sys.exit(app.exec())