未分類

import sys
import os
import sqlite3
import pandas as pd
import numpy as np
from datetime import datetime, timedelta, timezone
import re
import subprocess
import glob
import io

from PyQt6.QtWidgets import (QApplication, QMainWindow, QWidget, 
                             QVBoxLayout, QHBoxLayout, QPushButton, QLabel, 
                             QFileDialog, QComboBox, QDateEdit, QMessageBox, 
                             QGroupBox, QSystemTrayIcon, QStyle, QRadioButton, 
                             QButtonGroup, QFrame, QGraphicsView, QGraphicsScene, QMenu)
from PyQt6.QtCore import QDate, Qt, QTimer
from PyQt6.QtGui import QAction, QPixmap, QImageReader

class ZoomableView(QGraphicsView):
    def __init__(self, scene, parent=None):
        super().__init__(scene, parent)
        self.setDragMode(QGraphicsView.DragMode.ScrollHandDrag)
        self.setTransformationAnchor(QGraphicsView.ViewportAnchor.AnchorUnderMouse)
        self.setResizeAnchor(QGraphicsView.ViewportAnchor.AnchorUnderMouse)

    def wheelEvent(self, event):
        zoom_in_factor = 1.15
        zoom_out_factor = 1.0 / zoom_in_factor
        if event.angleDelta().y() > 0:
            zoom_factor = zoom_in_factor
        else:
            zoom_factor = zoom_out_factor
        self.scale(zoom_factor, zoom_factor)

class GPVExtractorApp(QMainWindow):
    def __init__(self):
        super().__init__()
        self.setWindowTitle("GPV解析値 (GRIB2) 抽出エンジン (内部時間軸UTC統一版)")
        self.resize(1100, 700)
        self.really_quit = False 
        
        self.setStyleSheet("""
            QMainWindow { background-color: #F4F6F9; font-family: 'Segoe UI', 'Meiryo', sans-serif; }
            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#AutoBtn { background-color: #D73A49; }
            QLineEdit, QComboBox, QDateEdit { border: 1px solid #D0D7DE; border-radius: 4px; padding: 5px; background-color: #FAFBFC; min-height: 25px; }
        """)

        # 共有データベースの設定
        self.db_path = "weather_verification(削除禁止).db"
        
        # 札幌圏 座標設定 (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.wgrib2_path = "wgrib2.exe"

        self.setup_tray_icon()
        self.init_db()
        self.init_ui()

        self.t8_auto_timer = QTimer(self)
        self.t8_auto_timer.timeout.connect(self.auto_fetch_grib2)
        QTimer.singleShot(100, self.reload_reference_map)

    def setup_tray_icon(self):
        self.tray_icon = QSystemTrayIcon(self)
        self.tray_icon.setIcon(self.style().standardIcon(QStyle.StandardPixmap.SP_DriveNetIcon))
        self.tray_icon.setToolTip("GPV抽出エンジン - 稼働中")
        
        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)

    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("バックグラウンドで実行中", "GPV自動抽出処理は継続しています。\n完全に終了する場合は完全終了ボタンを使用してください。", QSystemTrayIcon.MessageIcon.Information, 3000)
        else:
            event.accept()

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

    def init_db(self):
        self.conn = sqlite3.connect(self.db_path)
        cursor = self.conn.cursor()
        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 GPV_Points (PointID INTEGER PRIMARY KEY AUTOINCREMENT, Lon REAL, Lat REAL, UNIQUE(Lon, Lat))''')
        self.conn.commit()

    def init_ui(self):
        main_widget = QWidget()
        main_layout = QVBoxLayout(main_widget)
        
        # ヘッダー
        header = QHBoxLayout()
        lbl_info = QLabel(f"🔗 接続先DB: {self.db_path} (※メインアプリと共有・内部UTC同期)")
        lbl_info.setStyleSheet("font-weight: bold; color: #0366D6; font-size: 14px;")
        btn_quit = QPushButton("❌ 完全に終了する")
        btn_quit.setStyleSheet("background-color: #CB2431;")
        btn_quit.clicked.connect(self.force_quit)
        header.addWidget(lbl_info)
        header.addStretch()
        header.addWidget(btn_quit)
        main_layout.addLayout(header)

        # メインコンテンツ (左右分割)
        content_layout = QHBoxLayout()
        content_layout.setSpacing(15)

        # === 左パネル ===
        left_panel = QWidget()
        left_layout = QVBoxLayout(left_panel)
        left_panel.setFixedWidth(450)
        
        # 1. 取得モード
        group_mode = QGroupBox("1. GRIB2 データ取得モード (※元データはUTC基準です)")
        mode_layout = QVBoxLayout()
        auto_layout = QHBoxLayout()
        self.btn_t8_auto = QPushButton("▶ GRIB2 自動取得を開始 (1時間毎)")
        self.btn_t8_auto.setObjectName("AutoBtn")
        self.btn_t8_auto.clicked.connect(self.toggle_t8_auto)
        self.lbl_t8_auto = QLabel("停止中")
        self.lbl_t8_auto.setStyleSheet("color: #D73A49; font-weight: bold;")
        auto_layout.addWidget(self.btn_t8_auto)
        auto_layout.addWidget(self.lbl_t8_auto)
        auto_layout.addStretch()
        mode_layout.addLayout(auto_layout)

        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("開始(JST):"))
        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)

        # 2. フォルダ設定
        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)

        # 3. 簡易データ確認
        group_query = QGroupBox("3. 解析値 簡易データ確認 (※日本時間JSTで指定可能)")
        query_layout = QVBoxLayout()
        dt_layout = QHBoxLayout()
        self.t8_q_date = QDateEdit(QDate.currentDate())
        self.t8_q_date.setCalendarPopup(True)
        self.t8_q_time = QComboBox()
        self.t8_q_time.addItem("日合計 / 日最大 (24h)")
        self.t8_q_time.addItems([f"{str(h).zfill(2)}:00" for h in range(24)])
        dt_layout.addWidget(QLabel("日時(JST):"))
        dt_layout.addWidget(self.t8_q_date)
        dt_layout.addWidget(self.t8_q_time)
        query_layout.addLayout(dt_layout)
        
        pt_layout = QHBoxLayout()
        self.t8_q_point = QComboBox()
        self.update_t8_query_points()
        pt_layout.addWidget(QLabel("地点:"))
        pt_layout.addWidget(self.t8_q_point)
        query_layout.addLayout(pt_layout)
        
        el_layout = QHBoxLayout()
        self.t8_q_element = QComboBox()
        self.t8_q_element.addItems(["降水量 (RainfallP1)", "降雪量 (SnowfallP1)", "積雪深 (SnowDepth)"])
        el_layout.addWidget(QLabel("要素:"))
        el_layout.addWidget(self.t8_q_element)
        query_layout.addLayout(el_layout)
        
        res_layout = QHBoxLayout()
        btn_query = QPushButton("値を確認")
        btn_query.setObjectName("ActionBtn")
        btn_query.clicked.connect(self.run_t8_simple_query)
        self.lbl_t8_q_result = QLabel("結果: --")
        self.lbl_t8_q_result.setStyleSheet("font-size: 15px; font-weight: bold; color: #D73A49;")
        res_layout.addWidget(btn_query)
        res_layout.addWidget(self.lbl_t8_q_result)
        res_layout.addStretch()
        query_layout.addLayout(res_layout)
        group_query.setLayout(query_layout)
        left_layout.addWidget(group_query)
        
        left_layout.addStretch()
        content_layout.addWidget(left_panel)

        # === 右パネル ===
        right_panel = QWidget()
        right_layout = QVBoxLayout(right_panel)
        group_map = QGroupBox("4. GPV座標 リファレンスマップ (地点番号確認用)")
        map_layout = QVBoxLayout()
        map_layout.addWidget(QLabel("※マウスホイールでズーム、ドラッグで移動ができます。\nここで確認した数字をメインアプリ側の「GPV_〇」と紐付けて検証してください。"))
        
        self.scene = QGraphicsScene()
        self.view = ZoomableView(self.scene) 
        self.view.setStyleSheet("background-color: #E1E4E8; border: 1px solid #D0D7DE;")
        map_layout.addWidget(self.view)
        
        btn_reload = QPushButton("🔄 地図画像を再読み込み")
        btn_reload.clicked.connect(self.reload_reference_map)
        map_layout.addWidget(btn_reload)
        group_map.setLayout(map_layout)
        right_layout.addWidget(group_map)

        content_layout.addWidget(right_panel, stretch=1)
        main_layout.addLayout(content_layout)
        self.setCentralWidget(main_widget)

    def update_t8_query_points(self):
        if not hasattr(self, 't8_q_point'):
            return 
        self.t8_q_point.clear()
        try:
            cursor = self.conn.cursor()
            cursor.execute("SELECT DISTINCT MsCd FROM OBS ORDER BY MsCd")
            points = cursor.fetchall()
            
            cursor.execute("SELECT PointID, Lon, Lat FROM GPV_Points")
            gpv_map = {f"GPV_{r[0]}": (r[1], r[2]) for r in cursor.fetchall()}
            
            display_items = []
            for p in points:
                code = str(p[0])
                if code in gpv_map:
                    lon, lat = gpv_map[code]
                    display_items.append(f"{code} (Lon: {lon:.3f}, Lat: {lat:.3f})")
                else:
                    display_items.append(code)
            self.t8_q_point.addItems(display_items)
        except Exception:
            pass

    def toggle_t8_auto(self):
        if self.t8_auto_timer.isActive():
            self.t8_auto_timer.stop()
            self.lbl_t8_auto.setText("停止中")
            self.btn_t8_auto.setText("▶ GRIB2 自動取得を開始 (1時間毎)")
        else:
            self.t8_auto_timer.start(3600000) 
            self.lbl_t8_auto.setText("🔴 自動取得中")
            self.btn_t8_auto.setText("■ GRIB2 自動取得を停止")
            self.auto_fetch_grib2()

    def auto_fetch_grib2(self):
        self.grib_radio_latest.setChecked(True)
        for key, name in [("rain", "解析雨量"), ("snowfall", "解析降雪量"), ("snowdepth", "解析積雪深")]:
            if self.grib_folders.get(key):
                self.run_grib_db_extraction(key, name, silent=True)

    def reload_reference_map(self):
        self.scene.clear()
        pixmap = QPixmap("gpv_reference_map.png")
        if not pixmap.isNull(): 
            self.scene.addPixmap(pixmap)
            self.view.fitInView(self.scene.sceneRect(), Qt.AspectRatioMode.KeepAspectRatio)
        else: 
            self.scene.addText("画像 'gpv_reference_map.png' が見つかりません。")

    def run_t8_simple_query(self):
        date_str = self.t8_q_date.date().toString("yyyy-MM-dd")
        time_str = self.t8_q_time.currentText()
        ms_cd = self.t8_q_point.currentText().split(" (")[0] 
        el_str = self.t8_q_element.currentText()
        
        if "Rain" in el_str: col, unit = "RainfallP1", "mm"
        elif "Snowfall" in el_str: col, unit = "SnowfallP1", "cm"
        else: col, unit = "SnowDepth", "cm"
        
        try:
            cursor = self.conn.cursor()
            if "日合計" in time_str:
                # JSTの1日分を内部UTC時間範囲に直して集計
                jst_start = datetime.combine(self.t8_q_date.date().toPyDate(), datetime.min.time())
                utc_start = jst_start - timedelta(hours=9)
                utc_end = utc_start + timedelta(hours=23)
                
                u_start_str = utc_start.strftime("%Y-%m-%d %H:%M")
                u_end_str = utc_end.strftime("%Y-%m-%d %H:%M")
                
                if col == "SnowDepth": 
                    cursor.execute(f"SELECT MAX({col}) FROM OBS WHERE ObsDay || ' ' || ObsTime BETWEEN ? AND ? AND MsCd=?", (u_start_str, u_end_str, ms_cd))
                else:
                    cursor.execute(f"SELECT SUM({col}) FROM OBS WHERE ObsDay || ' ' || ObsTime BETWEEN ? AND ? AND MsCd=?", (u_start_str, u_end_str, ms_cd))
            else:
                # 特定時間のJST指定をUTCに変換してピンポイント検索
                jst_dt = datetime.strptime(f"{date_str} {time_str}", "%Y-%m-%d %H:%M")
                utc_dt = jst_dt - timedelta(hours=9)
                u_day = utc_dt.strftime("%Y-%m-%d")
                u_time = utc_dt.strftime("%H:%M")
                
                cursor.execute(f"SELECT {col} FROM OBS WHERE ObsDay=? AND ObsTime=? AND MsCd=?", (u_day, u_time, ms_cd))
                
            row = cursor.fetchone()
            if row and row[0] is not None:
                self.lbl_t8_q_result.setText(f"結果: {row[0]:.1f} {unit}")
            else:
                self.lbl_t8_q_result.setText("結果: データなし")
        except Exception:
            self.lbl_t8_q_result.setText("結果: エラー")

    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 run_grib_db_extraction(self, data_type_key, data_name, silent=False):
        folder_path = self.grib_folders[data_type_key]
        if not folder_path or not os.path.exists(folder_path):
            if not silent: QMessageBox.warning(self, "警告", f"{data_name} のフォルダが設定されていません。")
            return
        
        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), recursive=True)
        if not bin_files:
            if not silent: QMessageBox.warning(self, "ファイルなし", "対象ファイルが見つかりません。")
            return
        
        bin_files.sort()
        target_files = []
        if self.grib_radio_latest.isChecked():
            target_files = [bin_files[-1]]
        else:
            sd_utc = (self.grib_date_start.date().toPyDate() - timedelta(days=1)).strftime("%Y%m%d")
            ed_utc = self.grib_date_end.date().toPyDate().strftime("%Y%m%d")
            for f in bin_files:
                match = re.search(r'(20\d{6})', os.path.basename(f))
                if match and (sd_utc <= match.group(1)[:8] <= ed_utc): target_files.append(f)
            if not target_files: 
                if not silent: QMessageBox.warning(self, "該当なし", "指定期間内にファイルがありません。")
                return

        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
                
                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_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')

                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")
                    obs_day = dt_utc.strftime("%Y-%m-%d")
                    obs_time = dt_utc.strftime("%H:%M")
                    
                    if not obs_time.endswith("00"):
                        continue

                count_in_file = 0
                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 None
                        s_p = val if data_type_key == "snowfall" else None
                        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))
                    count_in_file += 1
                
                total_inserted += count_in_file

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

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

if __name__ == "__main__":
    app = QApplication(sys.argv)
    app.setQuitOnLastWindowClosed(False)
    QImageReader.setAllocationLimit(0)
    window = GPVExtractorApp()
    window.show()
    sys.exit(app.exec())