import os

# 最後に Google Sheets に書き込む必要があるため、プロキシを有効にします。
# DeepSeek と Bright Data のインターフェースはプロキシを経由せず、Google Sheets のリクエストのみプロキシを使用します。
os.environ["HTTP_PROXY"]  = "http://127.0.0.1:7897"
os.environ["HTTPS_PROXY"] = "http://127.0.0.1:7897"
os.environ["NO_PROXY"]    = "api.deepseek.com"
os.environ["NO_PROXY"]    = ",api.brightdata.com"

import pandas as pd
import json
import time
from openai import OpenAI
import gspread
from google.oauth2.service_account import Credentials
import requests

# ================= 設定エリア =================
Liang_KEY = 'Bright DataのAPIキー（コピーしたコード内にあります）'
API_KEY = "DeepSeekのAPIキー"
MODEL_NAME = "deepseek-chat"
BASE_URL = "https://api.deepseek.com/v1"

# ファイルパス設定
INPUT_JSON_FILE = "tiktok_data.jsonl"  # スクレイピングしたデータの保存先
CREDENTIALS_FILE = "credentials.json"  # Google Cloud からダウンロードした JSON 認証情報

# Google Sheets 設定
SPREADSHEET_NAME = "TikTokバズり分析レポート_2026"
WORKSHEET_NAME = "今日の内容洞察"

# ✅ DeepSeek は OpenAI SDK と互換性があります
client = OpenAI(api_key=API_KEY, base_url=BASE_URL)

# ================= 1. Bright Data API を実行して内容をスクレイピング =================
def trigger_brightdata_task(keyword, num_of_posts=500, country=""):
    headers = {
        "Authorization": f"Bearer {Liang_KEY}",
        "Content-Type": "application/json",
    }
    data = json.dumps({
        "input": [{"search_keyword": keyword, "num_of_posts": num_of_posts, "country": country}],
    })
    response = requests.post(
        "https://api.brightdata.com/datasets/v3/scrape?dataset_id=gd_lu702nij2f790tmv9h&notify=false&include_errors=true&type=discover_new&discover_by=keyword",
        headers=headers,
        data=data
    )
    snapshot_id = response.json().get("snapshot_id")
    print("snapshot_id:", snapshot_id)
    return snapshot_id

# ================= 2. Bright Data API からスクレイピング結果を取得 =================
def download_snapshot_result(snapshot_id, output_file="tiktok_data.jsonl", poll_interval=120):
    url = f'https://api.brightdata.com/datasets/v3/snapshot/{snapshot_id}'
    headers = {
        "Authorization": f"Bearer {Liang_KEY}",
        "Content-Type": "application/json",
    }
    # 期待する Content-Type を定義
    expected_content_type = "application/jsonl"
    
    while True:
        try:
            response = requests.get(
                url, headers=headers, stream=True,
                proxies={"http": None, "https": None}
            )
            response.raise_for_status()
            content_type = response.headers.get('Content-Type', '').lower()
            
            if expected_content_type in content_type:
                print("✅ データのスクレイピングに成功しました!")
                break
            else:
                print(f"⏳ スクレイピング中です。{poll_interval//60} 分後に再試行します...")
                time.sleep(poll_interval)
        except requests.exceptions.RequestException as e:
            print(f"リクエストエラー: {e}")
            time.sleep(poll_interval)

    with open(output_file, 'wb') as file:
        for chunk in response.iter_content(chunk_size=8192):
            file.write(chunk)
    print(f"✅ ダウンロード完了: {output_file}")
    return output_file

# ================= 3. データの読み込み =================
def load_and_clean_data(file_path):
    df = pd.read_json(file_path, lines=True)  # JSONL を直接読み込む
    df = df[~df["error"].notna()] if "error" in df.columns else df
    df = df[df["digg_count"].notna() & df["description"].notna()]
    print(f"✅ データの読み込み完了：有効データ {len(df)} 件")
    return df

# ================= 2. 統計分析とフィルタリングロジック =================
def find_breakthroughs(df, keywords):
    if df.empty:
        return pd.DataFrame()
    
    # 前処理：キーワードを小文字・トリミング
    target_keywords = [str(k).lower().strip() for k in keywords]
    df = df.copy()

    # --- 1. データクレンジング：数値列を数値型に変換 ---
    count_cols = ['digg_count', 'comment_count', 'share_count', 'collect_count']
    for col in count_cols:
        if col in df.columns:
            df[col] = pd.to_numeric(df[col], errors='coerce')
        else:
            df[col] = 0
    df[count_cols] = df[count_cols].fillna(0)

    # 総合エンゲージメントスコアの計算
    df['score'] = (df['digg_count'] * 1) + \
                  (df['comment_count'] * 2) + \
                  (df['share_count'] * 3) + \
                  (df['collect_count'] * 4)

    def check_topic(row, t_keywords):
        desc = str(row.get('description', '') or '').lower()
        tags_raw = row.get('hashtags')
        
        if not isinstance(tags_raw, list):
            tags_list = []
        else:
            tags_list = [str(tag).lower() for tag in tags_raw]

        has_keyword_in_desc = any(k in desc for k in t_keywords)
        has_keyword_in_tags = any(
            any(k in tag for k in t_keywords)
            for tag in tags_list
        )
        return has_keyword_in_desc or has_keyword_in_tags

    df['is_target_topic'] = df.apply(lambda row: check_topic(row, target_keywords), axis=1)
    
    target_df = df[df['is_target_topic'] == True].copy()
    if target_df.empty:
        print(f"⚠️ キーワード {target_keywords} に関連する内容が見つかりませんでした。")
        return pd.DataFrame()

    print(f"✅ キーワード [{', '.join(target_keywords)}] により {len(target_df)} 件の関連データを抽出。")

    # 統計指標の計算
    mean_score = target_df['score'].mean()
    std_score = target_df['score'].std()

    if std_score == 0 or std_score is None or pd.isna(std_score):
        target_df['z_score'] = 0
    else:
        target_df['z_score'] = (target_df['score'] - mean_score) / std_score

    if mean_score == 0 or pd.isna(mean_score):
        target_df['breakthrough_ratio'] = 0
    else:
        target_df['breakthrough_ratio'] = target_df['score'] / mean_score

    # フィルタリング戦略
    breakthroughs = target_df[(target_df['z_score'] > 0.5) | (target_df['breakthrough_ratio'] > 1.5)]
    
    if not breakthroughs.empty:
        print(breakthroughs.sort_values(by='z_score', ascending=False))
    
    return breakthroughs.sort_values(by='z_score', ascending=False)

# ================= 3. LLM 分析関数 =================
def analyze_with_llm(row):
    desc = row['description'][:150]  # 長すぎる説明文をカット
    followers = row['profile_followers']
    ratio = row['breakthrough_ratio']
    plays = row['play_count']
    
    size_label = "微小アカウント (<1w)" if followers < 10000 else \
                 "小規模アカウント (1w-10w)" if followers < 100000 else \
                 "中規模アカウント (10w-100w)" if followers < 1000000 else \
                 "大規模アカウント (>100w)"

    prompt = f"""
    あなたは TikTok 美容セクターの成長エキスパートです。
    「ジェネリック代替品 (Dupes)」セクターで異常に優れたパフォーマンスを示した動画を発見しました。

    【データ概要】
    - アカウント規模：{size_label} ({followers:,} フォロワー)
    - 再生数：{plays:,}
    - 突破指数：同ジャンル平均の {ratio:.2f} 倍
    - 動画文案："{desc}"

    【タスク】
    なぜこの動画が平均を大きく上回ったのか分析してください。再現可能なタイトル/内容の公式をまとめてください。

    【出力要求】
    簡潔な日本語の洞察（80文字以内）のみを出力し、直接結論を述べてください。
    """

    try:
        response = client.chat.completions.create(
            model=MODEL_NAME,
            messages=[{"role": "user", "content": prompt}],
            temperature=0.7,
            max_tokens=100,
            timeout=10
        )
        result = response.choices[0].message.content.strip()
        print(f"\n✅ AI 分析結果 ({row['url']}):\n{result}\n")
        return result
    except Exception as e:
        return f"❌ AI Error: {str(e)}"

# ================= 4. Google Sheets へのエクスポート =================
def export_to_google_sheets(df, sheet_name, worksheet_name):
    print(f"📡 Google Sheets に接続中: '{sheet_name}'...")
    scopes = [
        'https://www.googleapis.com/auth/spreadsheets',
        'https://www.googleapis.com/auth/drive'
    ]
    
    if not os.path.exists(CREDENTIALS_FILE):
        print(f"❌ エラー：認証ファイル '{CREDENTIALS_FILE}' が見つかりません。")
        return None

    try:
        creds = Credentials.from_service_account_file(CREDENTIALS_FILE, scopes=scopes)
        gc = gspread.authorize(creds)
    except Exception as e:
        print(f"❌ Google 認証失敗：{e}")
        return None

    try:
        sh = gc.open(sheet_name)
        print(f"📂 既存のシートを発見: {sheet_name}")
    except gspread.exceptions.SpreadsheetNotFound:
        sh = gc.create(sheet_name)
        print(f"✨ 新規シートを作成: {sheet_name}")

    try:
        worksheet = sh.worksheet(worksheet_name)
        worksheet.clear()
        print(f"🧹 ワークシートをクリア: {worksheet_name}")
    except gspread.exceptions.WorksheetNotFound:
        worksheet = sh.add_worksheet(title=worksheet_name, rows=100, cols=20)
        print(f"➕ 新規ワークシートを作成: {worksheet_name}")

    # エクスポートする列の準備
    cols_to_export = ['profile_username', 'profile_followers', 'play_count', 'breakthrough_ratio', 'description',
                      'ai_insight', 'url']
    available_cols = [c for c in cols_to_export if c in df.columns]
    df_export = df[available_cols].copy()
    df_export['分析時間'] = time.strftime("%Y-%m-%d %H:%M:%S")

    data_values = [df_export.columns.tolist()] + df_export.values.tolist()
    worksheet.update(values=data_values, range_name='A1')
    
    # ヘッダーのフォーマット設定
    worksheet.format('A1:Z1', {
        'textFormat': {'bold': True},
        'backgroundColor': {'red': 0.9, 'green': 0.9, 'blue': 0.9}
    })

    print(f"✅ {len(df_export)} 件のデータを Google Sheets にエクスポート成功!")
    print(f"🔗 リンクを確認: {sh.url}")
    return sh.url

# ================= 5. メインプロセスの実行 =================
def main():
    # 1. スクレイピング API を実行
    snapshot_id = trigger_brightdata_task("#dupe alternative", 2000)
    
    # 2. スクレイピング結果をダウンロード
    download_snapshot_result(snapshot_id, INPUT_JSON_FILE)
    
    # 3. ファイルからデータを読み込み
    df = load_and_clean_data(INPUT_JSON_FILE)
    if df.empty:
        return

    # 美容 Dupes キーワード：クレンジング段階でノイズを除去
    beauty_dupe_keywords = ['dupe', 'dupes', 'alternative', 'alternatives', 'swap', 'swaps']

    # 4. ブレイクスルー・コンテンツの選別
    print("🔍 統計指標の計算と選別を実行中...")
    breakthroughs = find_breakthroughs(df, keywords=beauty_dupe_keywords)
    
    if breakthroughs.empty:
        print("条件に合うブレイクスルー・コンテンツは見つかりませんでした。")
        return
    
    print(f"🎯 {len(breakthroughs)} 件のブレイクスルー・コンテンツを特定。")

    # 5. サーキットブレーカー（保護）：最大 Top 20 のみを分析
    MAX_TO_ANALYZE = 20
    if len(breakthroughs) > MAX_TO_ANALYZE:
        print(f"⚠️ 保護トリガー：パフォーマンス最高の Top {MAX_TO_ANALYZE} 件のみを分析します。")
        breakthroughs = breakthroughs.head(MAX_TO_ANALYZE)

    # 6. AI を呼び出し
    print("🤖 AI 大規模モデルによる深い要因分析を実行中...")
    breakthroughs['ai_insight'] = breakthroughs.apply(analyze_with_llm, axis=1)

    # 7. Google Sheets にエクスポート
    export_to_google_sheets(breakthroughs, SPREADSHEET_NAME, WORKSHEET_NAME)

if __name__ == "__main__":
    main()

import os

# プロキシ設定（環境に合わせて調整）
# os.environ["HTTP_PROXY"]  = "http://127.0.0.1:7897"
# os.environ["HTTPS_PROXY"] = "http://127.0.0.1:7897"
# os.environ["NO_PROXY"]    = "api.deepseek.com,api.brightdata.com"

import pandas as pd
import json
import time
from openai import OpenAI
import gspread
from google.oauth2.service_account import Credentials
import requests

# ================= 設定エリア =================
Liang_KEY = 'Bright DataのAPIキーをここに貼り付け'
API_KEY = "DeepSeekのAPIキーをここに貼り付け"
MODEL_NAME = "deepseek-chat"
BASE_URL = "https://api.deepseek.com/v1"

INPUT_JSON_FILE = "tiktok_data.jsonl"
CREDENTIALS_FILE = "credentials.json"

SPREADSHEET_NAME = "TikTokバズり分析レポート_2026"
WORKSHEET_NAME = "今日の内容洞察"

client = OpenAI(api_key=API_KEY, base_url=BASE_URL)

# ================= 各関数の定義 =================

def trigger_brightdata_task(keyword, num_of_posts=500, country=""):
    headers = {"Authorization": f"Bearer {Liang_KEY}", "Content-Type": "application/json"}
    data = json.dumps({"input": [{"search_keyword": keyword, "num_of_posts": num_of_posts, "country": country}]})
    response = requests.post(
        "https://api.brightdata.com/datasets/v3/scrape?dataset_id=gd_lu702nij2f790tmv9h&notify=false&include_errors=true&type=discover_new&discover_by=keyword",
        headers=headers, data=data
    )
    return response.json().get("snapshot_id")

def download_snapshot_result(snapshot_id, output_file="tiktok_data.jsonl", poll_interval=120):
    url = f'https://api.brightdata.com/datasets/v3/snapshot/{snapshot_id}'
    headers = {"Authorization": f"Bearer {Liang_KEY}"}
    while True:
        response = requests.get(url, headers=headers, stream=True)
        if "application/jsonl" in response.headers.get('Content-Type', '').lower():
            break
        print(f"⏳ 収集中... {poll_interval//60}分後に再確認します")
        time.sleep(poll_interval)
    with open(output_file, 'wb') as f:
        for chunk in response.iter_content(chunk_size=8192): f.write(chunk)
    return output_file

def load_and_clean_data(file_path):
    df = pd.read_json(file_path, lines=True)
    df = df[~df["error"].notna()] if "error" in df.columns else df
    return df[df["digg_count"].notna() & df["description"].notna()]

def find_breakthroughs(df, keywords):
    if df.empty: return pd.DataFrame()
    target_keywords = [str(k).lower().strip() for k in keywords]
    df = df.copy()
    count_cols = ['digg_count', 'comment_count', 'share_count', 'collect_count']
    for col in count_cols:
        df[col] = pd.to_numeric(df[col], errors='coerce').fillna(0)
    df['score'] = (df['digg_count'] * 1) + (df['comment_count'] * 2) + (df['share_count'] * 3) + (df['collect_count'] * 4)
    
    def check_topic(row, t_keywords):
        desc = str(row.get('description', '') or '').lower()
        tags = [str(tag).lower() for tag in (row.get('hashtags') or [])]
        return any(k in desc for k in t_keywords) or any(any(k in tag for k in t_keywords) for tag in tags)

    df['is_target_topic'] = df.apply(lambda row: check_topic(row, target_keywords), axis=1)
    target_df = df[df['is_target_topic']].copy()
    if target_df.empty: return pd.DataFrame()
    
    mean_s = target_df['score'].mean()
    std_s = target_df['score'].std()
    target_df['z_score'] = (target_df['score'] - mean_s) / std_s if std_s > 0 else 0
    target_df['breakthrough_ratio'] = target_df['score'] / mean_s if mean_s > 0 else 0
    return target_df[(target_df['z_score'] > 0.5) | (target_df['breakthrough_ratio'] > 1.5)].sort_values(by='z_score', ascending=False)

def analyze_with_llm(row):
    prompt = f"分析 TikTok 動画: 再生 {row['play_count']}, 突破指数 {row['breakthrough_ratio']:.2f}. 文案: {row['description'][:150]}. 80字以内の日本語でバズり要因の結論を出して。"
    try:
        res = client.chat.completions.create(model=MODEL_NAME, messages=[{"role": "user", "content": prompt}])
        return res.choices[0].message.content.strip()
    except: return "AI 分析エラー"

def export_to_google_sheets(df, sheet_name, worksheet_name):
    scopes = ['https://www.googleapis.com/auth/spreadsheets', 'https://www.googleapis.com/auth/drive']
    creds = Credentials.from_service_account_file(CREDENTIALS_FILE, scopes=scopes)
    gc = gspread.authorize(creds)
    try: sh = gc.open(sheet_name)
    except: sh = gc.create(sheet_name)
    try: ws = sh.worksheet(worksheet_name)
    except: ws = sh.add_worksheet(worksheet_name, 100, 20)
    ws.clear()
    df_export = df[['profile_username', 'profile_followers', 'play_count', 'breakthrough_ratio', 'description', 'ai_insight', 'url']].copy()
    ws.update([df_export.columns.tolist()] + df_export.values.tolist())
    print(f"✅ シート完成: {sh.url}")

def main():
    sid = trigger_brightdata_task("#dupe alternative", 2000)
    download_snapshot_result(sid, INPUT_JSON_FILE)
    df = load_and_clean_data(INPUT_JSON_FILE)
    breakthroughs = find_breakthroughs(df, ['dupe', 'alternative', 'swap'])
    if not breakthroughs.empty:
        breakthroughs = breakthroughs.head(20)
        breakthroughs['ai_insight'] = breakthroughs.apply(analyze_with_llm, axis=1)
        export_to_google_sheets(breakthroughs, SPREADSHEET_NAME, WORKSHEET_NAME)

if __name__ == "__main__":
    main()