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

# 保存場所とファイル名を自身のものに変更
with open(r"C:\Users\ユーザー名\Downloads\GTM-AAAAAA_workspace.json", 'r', encoding='utf-8') as f:
    data = json.load(f)

tags = data.get('containerVersion', {}).get('tag', [])
triggers = data.get('containerVersion', {}).get('trigger', [])
variables = data.get('containerVersion', {}).get('variable', [])
folders = data.get('containerVersion', {}).get('folder', [])


folder_id_to_name = {folder['folderId']: folder['name'] for folder in folders}

trigger_id_to_name = {str(trigger['triggerId']): trigger['name'] for trigger in triggers}
trigger_id_to_name['2147479553'] = 'All Pages'
trigger_id_to_name['2147479573'] = 'Initialization'
trigger_id_to_name['2147479572'] = 'Consent InitiaConsent'


tag_rows = []
for tag in tags:
    firing_trigger_ids = tag.get('firingTriggerId', [])
    blocking_trigger_ids = tag.get('blockingTriggerId', [])

    firing_trigger_names = [trigger_id_to_name.get(str(tid), str(tid)) for tid in firing_trigger_ids]
    blocking_trigger_names = [trigger_id_to_name.get(str(tid), str(tid)) for tid in blocking_trigger_ids]

    tag_row = {
        'Name': tag.get('name'),
        'ParentFolder': folder_id_to_name.get(tag.get('parentFolderId', ''), ''),
        'Type': tag.get('type'),
        'FiringTriggerId': "\r\n".join(str(tid) for tid in firing_trigger_ids),
        'FiringTriggerNames': "\r\n".join(firing_trigger_names),
        'BlockingTriggerId': "\r\n".join(str(tid) for tid in blocking_trigger_ids),
        'BlockingTriggerNames': "\r\n".join(blocking_trigger_names),
        'Parameters': "\r\n".join([f"{param.get('key', '')}: {param.get('value', '')}" for param in tag.get('parameter', [])])
    }
    tag_rows.append(tag_row)

df_tags = pd.DataFrame(tag_rows)


trigger_rows = []
for trigger in triggers:
    trigger_row = {
        'Name': trigger.get('name'),
        'ParentFolder': folder_id_to_name.get(trigger.get('parentFolderId', ''), ''),
        'Type': trigger.get('type'),
        'Filter': "\r\n".join([f"{filt.get('type', '')}: {', '.join([param.get('value', '') for param in filt.get('parameter', [])])}" for filt in trigger.get('filter', [])])
    }
    trigger_rows.append(trigger_row)

df_triggers = pd.DataFrame(trigger_rows)


variable_rows = []
for variable in variables:
    variable_row = {
        'Name': variable.get('name'),
        'ParentFolder': folder_id_to_name.get(variable.get('parentFolderId', ''), ''),
        'Type': variable.get('type'),
        'Parameters': "\r\n".join([f"{param.get('key', '')}: {param.get('value', '')}" for param in variable.get('parameter', [])])
    }
    variable_rows.append(variable_row)

df_variables = pd.DataFrame(variable_rows)


wb = Workbook()

ws_tags = wb.active
ws_tags.title = "Tags"
for r in dataframe_to_rows(df_tags, index=False, header=True):
    ws_tags.append(r)

ws_triggers = wb.create_sheet(title="Triggers")
for r in dataframe_to_rows(df_triggers, index=False, header=True):
    ws_triggers.append(r)

ws_variables = wb.create_sheet(title="Variables")
for r in dataframe_to_rows(df_variables, index=False, header=True):
    ws_variables.append(r)


def apply_cell_styles(ws):
    for row in ws.iter_rows():
        for cell in row:
            cell.alignment = Alignment(horizontal='left', vertical='center', wrap_text=True)  

apply_cell_styles(ws_tags)
apply_cell_styles(ws_triggers)
apply_cell_styles(ws_variables)


def adjust_row_heights_and_column_widths(ws):
    for idx, row in enumerate(ws.iter_rows(min_row=1, max_row=ws.max_row), 1):
        max_length = max(len(str(cell.value)) for cell in row if cell.value)
        num_lines = max(cell.value.count('\r\n') + 1 for cell in row if cell.value)
        ws.row_dimensions[idx].height = max(min(num_lines * 15, 105), 35)  # 高さを最大105、最低35に制限

    for col_idx, col in enumerate(ws.iter_cols(min_col=1, max_col=ws.max_column), 1):
        max_length = max(len(str(cell.value)) for cell in col if cell.value)
        width = max(min(max_length * 0.8, 105), 15)  # 幅を最大105、最低15に制限
        ws.column_dimensions[openpyxl.utils.get_column_letter(col_idx)].width = width

adjust_row_heights_and_column_widths(ws_tags)
adjust_row_heights_and_column_widths(ws_triggers)
adjust_row_heights_and_column_widths(ws_variables)

wb.save("GTM_data.xlsx")
print("Excelファイルをエクスポートしました")