import pandas as pd
from openpyxl import Workbook
from openpyxl.utils.dataframe import dataframe_to_rows
from openpyxl.styles import Font, Alignment

# 1. CSVファイルを読み込む（encodingエラーに対処）
df_a = pd.read_csv('A.csv', encoding='utf-16', delimiter='\t', on_bad_lines='skip')
df_b = pd.read_csv('B.csv', encoding='utf-16', delimiter='\t', on_bad_lines='skip')
df_c = pd.read_csv('C.csv', encoding='utf-16', delimiter='\t', on_bad_lines='skip')

# 重複削除処理を関数化

def remove_duplicates(df_base, df_compare, key_column='Keyword'):
    # 重複する行を削除（特定の列をキーに重複を判断）
    columns_to_keep = ['Keyword', 'Volume', 'CPC', 'CPS', 'Parent Keyword', 'Traffic potential', 'Intents']
    existing_columns = [col for col in columns_to_keep if col in df_base.columns]
    merged_df = df_base[existing_columns][~df_base[key_column].isin(df_compare[key_column])]
    # 必要な列だけを残す
    filtered_df = merged_df[existing_columns]
    # 特定の列の文言でフィルタリングして、特定のキーワードを含むものだけ残すが「Branded」および「Local」は除外
    target_column = 'Intents'
    if target_column in filtered_df.columns:
        keywords_to_keep = ['Informational', 'Non-branded', 'Non-local']
        final_df = filtered_df[filtered_df[target_column].str.contains('|'.join(keywords_to_keep), na=False) & ~filtered_df[target_column].str.contains('Branded|Local', na=False)]
    else:
        final_df = filtered_df
    # 列の順番を変更して「Parent Keyword」を「Keyword」の左側に配置
    columns_order = [col for col in ['Parent Keyword', 'Keyword', 'Volume', 'CPC', 'CPS', 'Traffic potential', 'Intents'] if col in final_df.columns]
    final_df = final_df[columns_order]
    return final_df

# Bを土台にしてAを整理
df_sajest = remove_duplicates(df_b, df_a)
# Cを土台にしてAを整理
df_question = remove_duplicates(df_c, df_a)

# ネスト処理を関数化

def create_nested_dataframe(final_df):
    nested_dict = {}
    no_parent_rows = []
    parent_id_counter = 1
    for _, row in final_df.iterrows():
        parent_keyword = row.get('Parent Keyword', '')
        if pd.isna(parent_keyword) or parent_keyword == '':
            row['Parent Keyword'] = 'no-parent'
            row['Parent id'] = 'no-parent'
            row['is_base'] = False
            no_parent_rows.append(row)
        else:
            if parent_keyword not in nested_dict:
                nested_dict[parent_keyword] = {
                    'parent_id': parent_id_counter,
                    'children': []
                }
                parent_id_counter += 1
            row['Parent id'] = nested_dict[parent_keyword]['parent_id']
            row['is_base'] = True if len(nested_dict[parent_keyword]['children']) == 0 else False
            nested_dict[parent_keyword]['children'].append(row.to_dict())

    nested_output = []
    for parent, data in nested_dict.items():
        parent_id = data['parent_id']
        children = data['children']
        for i, child in enumerate(children):
            if i == 0:
                child['Parent Keyword'] = parent
                child['is_base'] = True
            else:
                child['Parent Keyword'] = ''
                child['is_base'] = False
            child['Parent id'] = parent_id
            nested_output.append(child)

    for row in no_parent_rows:
        nested_output.append(row.to_dict())

    nested_df = pd.DataFrame(nested_output)
    columns_order = [col for col in ['Parent id', 'Parent Keyword', 'Keyword', 'Volume', 'CPC', 'CPS', 'Traffic potential', 'Intents', 'is_base'] if col in nested_df.columns]
    nested_df = nested_df[columns_order]
    nested_df.sort_values(by=['Parent id', 'is_base'], ascending=[True, False], inplace=True)
    return nested_df

# ネスト化データフレームの作成
df_sajest_nested = create_nested_dataframe(df_sajest)
df_question_nested = create_nested_dataframe(df_question)

# 9. 結果を新しいExcelファイルに保存し、フォーマットを整える
wb = Workbook()

# サジェストシート
ws_sajest = wb.active
ws_sajest.title = "サジェスト"
for r_idx, row in enumerate(dataframe_to_rows(df_sajest_nested, index=False, header=True), 1):
    ws_sajest.append(row)
    if r_idx == 1:
        for cell in ws_sajest[r_idx]:
            cell.font = Font(bold=True)

# 質問シート
ws_question = wb.create_sheet(title="質問")
for r_idx, row in enumerate(dataframe_to_rows(df_question_nested, index=False, header=True), 1):
    ws_question.append(row)
    if r_idx == 1:
        for cell in ws_question[r_idx]:
            cell.font = Font(bold=True)

# 列幅の調整とフォントサイズの設定
for ws in [ws_sajest, ws_question]:
    for col in ws.columns:
        max_length = 0
        for cell in col:
            try:
                max_length = max(max_length, len(str(cell.value)))
            except:
                pass
        adjusted_width = (max_length + 4)
        ws.column_dimensions[col[0].column_letter].width = adjusted_width
        for cell in col:
            cell.font = Font(size=14)
            cell.alignment = Alignment(vertical='center')

# Excelファイルを保存
wb.save('output.xlsx')

print("処理が完了しました。結果は output.xlsx に保存されています。")