import os
import sqlite3
import json
from datetime import datetime
import pandas as pd
import pandas_ta as ta
import ccxt
from dotenv import load_dotenv

# [2026-02-13] v1.2.0: BTC BB Strategy with Integrated Analysis Packet
# .envからAPIキーを読み込み（.envは常に秘匿・ブラックリスト扱い）
load_dotenv()

class BtcBbCompoundBot:
    def __init__(self, db_name="strategy_core.db"):
        self.symbol = 'BTC/JPY'
        self.db_name = db_name
        self.exchange = ccxt.bitbank({
            'apiKey': os.getenv('BITBANK_API_KEY'),
            'secret': os.getenv('BITBANK_API_SECRET'),
        })
        self._setup_db()
        self._load_config()

    def _setup_db(self):
        """DB初期化：設定履歴、稼働状態、資産推移をすべて管理"""
        with sqlite3.connect(self.db_name) as conn:
            c = conn.cursor()
            # 1. 戦略パラメータ履歴 (Git風)
            c.execute('''CREATE TABLE IF NOT EXISTS config_history (
                version_id INTEGER PRIMARY KEY AUTOINCREMENT,
                timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
                message TEXT,
                length INTEGER, mult REAL, buy_ratio REAL, sell_ratio REAL,
                total_equity_jpy REAL
            )''')
            # 2. 取引履歴
            c.execute('''CREATE TABLE IF NOT EXISTS trade_log (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
                side TEXT, price REAL, qty REAL, fee REAL
            )''')
            # 3. ボットステータス (平均取得単価など)
            c.execute('''CREATE TABLE IF NOT EXISTS bot_status (
                id INTEGER PRIMARY KEY,
                avg_price REAL DEFAULT 0,
                total_qty REAL DEFAULT 0,
                is_running INTEGER DEFAULT 1
            )''')
            c.execute("INSERT OR IGNORE INTO bot_status (id, avg_price, total_qty) VALUES (1, 0, 0)")
            conn.commit()

    def _load_config(self):
        """最新のパラメータをロード"""
        with sqlite3.connect(self.db_name) as conn:
            conn.row_factory = sqlite3.Row
            row = conn.execute('SELECT * FROM config_history ORDER BY version_id DESC LIMIT 1').fetchone()
            if row:
                self.config = dict(row)
            else:
                # 初期設定がない場合はデフォルト値をコミット
                self.commit_config("First Boot", 20, 0.5, 0.5, 0.3)

    def commit_config(self, message, length, mult, buy_ratio, sell_ratio):
        """パラメータ変更を履歴として保存"""
        equity = self.get_total_equity()
        with sqlite3.connect(self.db_name) as conn:
            conn.execute('''INSERT INTO config_history 
                (message, length, mult, buy_ratio, sell_ratio, total_equity_jpy)
                VALUES (?, ?, ?, ?, ?, ?)''', (message, length, mult, buy_ratio, sell_ratio, equity))
        self._load_config()

    def get_total_equity(self):
        """現在の時価総額資産（JPY換算）を算出"""
        try:
            balance = self.exchange.fetch_balance()
            ticker = self.exchange.fetch_ticker(self.symbol)
            jpy = balance['total'].get('JPY', 0)
            btc = balance['total'].get('BTC', 0)
            return jpy + (btc * ticker['last'])
        except: return 0

    def generate_gemini_packet(self):
        """Web版Geminiが即座に分析可能なJSON/MD統合レポートを出力"""
        with sqlite3.connect(self.db_name) as conn:
            conn.row_factory = sqlite3.Row
            status = dict(conn.execute('SELECT * FROM bot_status WHERE id=1').fetchone())
            configs = [dict(r) for r in conn.execute('SELECT * FROM config_history ORDER BY version_id DESC LIMIT 10').fetchall()]
            trades = [dict(r) for r in conn.execute('SELECT * FROM trade_log ORDER BY id DESC LIMIT 20').fetchall()]

        analysis_data = {
            "bot_summary": status,
            "strategy_history": configs,
            "recent_trades": trades,
            "current_market": {"symbol": self.symbol, "timestamp": datetime.now().isoformat()}
        }

        report = f"""
### 📊 STRATEGY_ANALYSIS_PACKET
```json
{json.dumps(analysis_data, indent=2, ensure_ascii=False)}