import pandas as pd
import json

def analyze_data(json_file_path, output_file_path):
    with open(json_file_path, 'r', encoding='utf-8') as f:
        data = json.load(f)

    df = pd.DataFrame(data['gsc_queries'])

    # Chuyển đổi cột ctr sang dạng số
    df['ctr'] = df['ctr'].str.replace('%', '').astype(float)

    # Bảng 1: Tổng quan so lieu
    overview = df[['impressions', 'clicks', 'ctr', 'position']].mean().to_frame(name='Gia tri trung binh')
    overview.loc['Tong so truy van'] = len(df)
    overview_str = "Bang 1: Tong quan so lieu\n" + overview.to_string() + "\n\n"

    # Bảng 2: Top 10 tu khoa theo Impressions
    top_impressions = df.sort_values(by='impressions', ascending=False).head(10)[['query', 'impressions', 'clicks', 'position']]
    top_impressions_str = "Bang 2: Top 10 tu khoa theo Impressions\n" + top_impressions.to_string(index=False) + "\n\n"

    # Bảng 3: Top 10 tu khoa theo Clicks
    top_clicks = df.sort_values(by='clicks', ascending=False).head(10)[['query', 'clicks', 'impressions', 'position']]
    top_clicks_str = "Bang 3: Top 10 tu khoa theo Clicks\n" + top_clicks.to_string(index=False) + "\n\n"

    # Bảng 4: Top 10 tu khoa co CTR cao nhat (voi so Impressions dang ke)
    # Loc nhung tu khoa co it nhat 100 impressions de dam bao tinh dai dien
    df_filtered_ctr = df[df['impressions'] >= 100].copy()
    top_ctr = df_filtered_ctr.sort_values(by='ctr', ascending=False).head(10)[['query', 'ctr', 'impressions', 'clicks', 'position']]
    top_ctr_str = "Bang 4: Top 10 tu khoa co CTR cao nhat (>= 100 Impressions)\n" + top_ctr.to_string(index=False) + "\n\n"

    # Bảng 5: Phan tich vi tri trung binh
    def get_position_group(pos):
        if pos <= 3: return 'Top 3'
        elif pos <= 10: return 'Top 10'
        else: return 'Khac'
    df['position_group'] = df['position'].apply(get_position_group)

    position_analysis = df.groupby('position_group')['query'].count().to_frame(name='So luong tu khoa')
    position_analysis_str = "Bang 5: Phan tich vi tri trung binh (duoc phan nhom)\n" + position_analysis.to_string() + "\n\n"

    with open(output_file_path, 'w', encoding='utf-8') as f:
        f.write(overview_str)
        f.write(top_impressions_str)
        f.write(top_clicks_str)
        f.write(top_ctr_str)
        f.write(position_analysis_str)

    print(f"Da xuat 5 bang phan tich vao {output_file_path}")

if __name__ == "__main__":
    json_input_path = "./projects/GLV/seo-dashboard-app/public/seo_raw_data.json"
    output_text_path = "/tmp/glv_analysis_data.txt"
    analyze_data(json_input_path, output_text_path)