import pymysql
import pandas as pd
from datetime import datetime

##########################################################################################
### 関数
##########################################################################################

def get_sisetu_cd(facility_name):
    match = institution_df[institution_df["institution_name"] == facility_name]
    if not match.empty:
        return match.iloc[0]["SISETU_CD"]
    match_like = institution_df[institution_df["institution_name"].str.contains(facility_name, na=False)]
    if not match_like.empty:
        return match_like.iloc[0]["SISETU_CD"]
    return "700000"

def reset_tables(cursor):
    # 外部キー依存のあるテーブルを、子 → 親の順に並べる
    ordered_tables = [
        'T_IJI_MENEKI_YOKUSEI_R_LIV',
        'T_GAPPEI_R_LIV',
        'T_KANSEN_R_LIV',
        'T_KENSA_R_LIV',
        'T_LIVING_D_LIV',
        'T_DONOR_KIDNEY_LIV',
        'T_DONOR_LIV',
        'T_ISHOKU_KIHON_KIDNEY_LIV',
        'T_ISHOKU_KIHON_LIV'
    ]

    for table in ordered_tables:
        cursor.execute(f"DELETE FROM {table};")
        cursor.execute(f"ALTER TABLE {table} AUTO_INCREMENT = 1;")
    print("✅ 外部キー制約を考慮したテーブル初期化完了")


def render_sql(sql_template, values):
    # 単純な置換（%sを各値に）
    parts = sql_template.split("%s")
    rendered = ""
    for i, part in enumerate(parts[:-1]):
        rendered += part + repr(values[i])
    rendered += parts[-1]
    return rendered


##########################################################################################
### メイン処理
##########################################################################################

## CSVファイルとエクセルファイルの読み込み
institution_df = pd.read_csv("/csv/liver/shisetsu.csv", sep="\t", dtype=str).fillna("")
donor_df = pd.read_csv("/csv/kan/d-followups.csv", encoding="cp932", dtype=str).fillna("")
recipient_df = pd.read_csv("/csv/kan/r-followups.csv", encoding="cp932", dtype=str).fillna("")
transplants_df = pd.read_csv("/csv/kan/transplants.csv", encoding="cp932", dtype=str).fillna("")
transplants_df['ISYOKU_ISYOKUSISETU_CD'] = transplants_df['施設名'].apply(get_sisetu_cd)
mapping_dict = pd.read_excel("/csv/kan/移行データ対応表.xlsx", sheet_name=None, header=1)
df_mapping = mapping_dict["腎臓　生体"]
## 登録対象テーブル
target_tables = [
    'T_ISHOKU_KIHON_LIV',
    'T_ISHOKU_KIHON_KIDNEY_LIV',
    'T_DONOR_LIV',
    'T_DONOR_KIDNEY_LIV',
    'T_LIVING_D_LIV',
    'T_KENSA_R_LIV',
    'T_KANSEN_R_LIV',
    'T_GAPPEI_R_LIV',
    'T_IJI_MENEKI_YOKUSEI_R_LIV',
]
db_config = {
    "host": "db",  # コンテナ名
    "user": "root",
    "password": "123456",
    "database": "dev_tracer_db2",
    "charset": "utf8mb4",
        "cursorclass": pymysql.cursors.DictCursor
}

connection = pymysql.connect(**db_config)
    
with connection.cursor() as cursor:

    reset_tables(cursor)

    for transplant_index, transplant_raw in transplants_df.iterrows():
        for target_table in target_tables:
            filtered_mapping = df_mapping[df_mapping["テーブル物理名"] == target_table]
            if target_table == "T_ISHOKU_KIHON_LIV":
                insert_columns = ["ISYOKU_ISYOKUSISETU_CD", "DEL_FLG", "INS_USER_ID", "INS_PROGRAM_ID"]
                insert_values = [
                    transplant_raw["ISYOKU_ISYOKUSISETU_CD"],  # 例：施設コード（上で変換済）
                    0,                                         # DEL_FLG（削除フラグ）
                    1,                                         # INS_USER_ID（登録ユーザ）
                    1                                          # INS_PROGRAM_ID（登録プログラム）
                ]
                sql = f"""
                    INSERT INTO T_ISHOKU_KIHON_LIV ({",".join(insert_columns)})
                    VALUES ({",".join(["%s"] * len(insert_columns))})
                """
                print("▶ 実行SQL:", render_sql(sql, insert_values))

                cursor.execute(sql, insert_values)
                connection.commit()

                seitai_ishoku_id = cursor.lastrowid
                print(f"✅ 登録完了：SEITAI_ISYOKU_ID = {seitai_ishoku_id}")
                # TRACER_ID生成
                organ_code = "3"  # 肝臓
                transplant_type_code = "1"  # 生体間移植

                # 移植日 → 年取得（8桁: YYYYMMDD → YYYY）
                year = row["移植日"][:4] if "移植日" in row and row["移植日"] else "2024"

                # 同年・同TRACERプレフィックスの最大連番取得
                prefix = f"{organ_code}{transplant_type_code}{year}"
                cursor.execute(
                    "SELECT MAX(TRACER_ID) AS max_id FROM T_ISHOKU_KIHON_LIV WHERE TRACER_ID LIKE %s",
                    (f"{prefix}____",)
                )
                result = cursor.fetchone()
                if result["max_id"]:
                    last_seq = int(result["max_id"][-4:])
                    next_seq = last_seq + 1
                else:
                    next_seq = 1

                tracer_id = f"{prefix}{next_seq:04d}"

                # TRACER_ID更新
                cursor.execute(
                    "UPDATE T_ISHOKU_KIHON_LIV SET TRACER_ID = %s WHERE SEITAI_ISYOKU_ID = %s",
                    (tracer_id, seitai_ishoku_id)
                )