import random
from wordcloud import WordCloud
import seaborn as sns
import japanize_matplotlib

import matplotlib.pyplot as plt
import re

from typing import Literal
import pathlib


import warnings
import sys
import pandas as pd
sys.path.append("..")

from src.core.db.controller import execSQL, getTableList, getDataFrame  # noqa
from src.data_manager.preprocess_tools import DataPreProcessor  # noqa
from src.data_manager.data_loader import DataLoader  # noqa


warnings.filterwarnings("ignore")
# 接続先DB (このnotebookの場所が「notebook」フォルダにあるので一つ上の階層に戻ってDBファイルパスを生成)
root = pathlib.Path(".").absolute().parent
dbpath = root / "data" / "keibadata.db"


# 血統情報が入っているテーブルは「horseblood」という接頭辞がついたテーブルなので、その一覧を取得
horseblood_list = [tbl for tbl in getTableList(dbpath) if "horseblood" in tbl]
horseblood_list.sort(key=lambda x: int(x[-4:]))
horseblood_list
# DBから取得：concatでhorseblood_listにあるテーブル情報のDataFrameをすべて結合する
dfblood = pd.concat(
    [getDataFrame(tbl, dbpath) for tbl in horseblood_list], ignore_index=True)

start_year = 2000
end_year = 2023

data_loader = DataLoader(start_year, end_year, dbpath=dbpath)
dataPreP = DataPreProcessor()
df = data_loader.load_racedata()
df = dataPreP.exec_pipeline(df)


def add_blood_info_to_df(df: pd.DataFrame, mode: Literal["s", "ss", "sb", "b", "bs", "bb", "sss", "ssb", "sbs", "sbb", "bss", "bsb", "bbs", "bbb"]):
    index_map = {
        "s": (0, "stallion", "gen1"), "ss": (0, "sStallion", "gen2"), "sb": (8, "sBreed", "gen2"),
        "sss": (0, "s2Stallion", "gen3"), "ssb": (4, "s2Breed", "gen3"), "sbs": (8, "sbStallion", "gen3"), "sbb": (12, "sbBreed", "gen3"),
        "b": (16, "breed", "gen1"), "bs": (16, "bStallion", "gen2"), "bb": (24, "bBreed", "gen2"),
        "bss": (16, "bsStallion", "gen3"), "bsb": (20, "bsBreed", "gen3"), "bbs": (24, "b2Stallion", "gen3"), "bbb": (28, "b2Breed", "gen3"),
    }
    idx, prefix, genCol = index_map[mode]
    idf = dfblood.iloc[idx::32].reset_index(drop=True)
    idf[genCol] = idf[genCol].str.split("\n", expand=True)[
        0].str.replace("\n", "")
    df[f"{prefix}Id"] = df["horseId"].map(
        idf.set_index("horseId")[f"{genCol}ID"])
    df[f"{prefix}Name"] = df["horseId"].map(idf.set_index("horseId")[genCol])
    return df, idf[["horseId", genCol, f"{genCol}ID"]].rename(columns={genCol: "GenName", f"{genCol}ID": "GenID"})


df, dfstallion = add_blood_info_to_df(df, "s")
df, dfbStallion = add_blood_info_to_df(df, "bs")

listed_racename_list = [rname.replace("(L)", "") for rname in df[df["raceName"].str.contains(
    r"\(L\)", regex=True)]["raceName"].unique()]

listed_racename_list = df[df["raceName"].str.contains(
    "|".join(listed_racename_list))]["raceName"].unique().tolist()

listed_mapping = {rname: 5 for rname in listed_racename_list}


# それぞれのマッピングを作っておく
def map_race_grade(data: str):
    if re.search(r"\(G1\)", data) or re.search(r"\(GI\)", data):
        return 8

    if re.search(r"\(G2\)", data) or re.search(r"\(GII\)", data):
        return 7

    if re.search(r"\(G3\)", data) or re.search(r"\(GIII\)", data):
        return 6

    return None


grade_mapping_dict = {
    '4歳未勝利': 0, '4歳新馬': 0, '4歳以上500万下': 1, '4歳500万下': 1, '4歳以上900万下': 2, '4歳以上オープン': 4,
    '4歳以上1600万下': 3, '4歳オープン': 4, '4歳未出走': 0, '5歳以上オープン': 4, '4歳900万下': 2, '3歳新馬': 0,
    '3歳未勝利': 0, '3歳オープン': 4, '3歳500万下': 1, '3歳未出走': 0, '3歳以上オープン': 4, '3歳900万下': 2,
    '2歳新馬': 0, '3歳以上500万下': 1, '3歳以上1000万下': 2, '2歳未勝利': 0, '2歳オープン': 4, '3歳以上1600万下': 3,
    '2歳500万下': 1, '4歳以上1000万下': 2, '3歳1000万下': 2, '3歳以上1勝クラス': 1, '3歳以上2勝クラス': 2,
    '3歳以上3勝クラス': 3, '2歳1勝クラス': 1, '3歳1勝クラス': 1, '4歳以上1勝クラス': 1, '4歳以上2勝クラス': 2,
    '4歳以上3勝クラス': 3
}


# クラスをグレードに置き換える
idf = df["raceName"].map(map_race_grade)
idf = idf[~idf.isna()]
idf2 = df[~df.index.isin(idf.index)]["raceName"].map(listed_mapping)
idf2 = idf2[~idf2.isna()]

df["raceGrade"] = pd.concat([idf, idf2, df[~df.index.isin(idf.index.tolist(
)+idf2.index.tolist())]["raceDetail"].map(grade_mapping_dict)]).astype(int)

df["birthY"] = df["horseId"].str[:4].astype(int)
df["birthY"].sort_values().unique()

filter_raceId_list = df[df["birthY"].isin(
    list(range(1998, 2024)))]["raceId"].unique()
dfG = df[df["raceId"].isin(filter_raceId_list)]
dfG["year"] = dfG["raceDate"].dt.year

dfG["win"] = dfG["label"].isin([1]).astype(int)

Ghorse_list = dfG[~dfG["win"].isin([0]) & dfG["birthY"].isin(
    list(range(1998, 2024)))]["horseId"].unique()



dfG = dfG[dfG["horseId"].isin(Ghorse_list)]

dfG.groupby("horseId")["horseId"].value_counts().describe().to_frame().T

# 関数を使いまわす
def count_raceNum_for_G(target: list[int], firstwin=list(range(1, 9)), mode=True, dfG=None):
    group_df = dfG.groupby("horseId")[["label", "raceGrade"]].filter(
        lambda group: group[group["raceGrade"].isin(target)]["label"].isin([1]).any())
    dfG1 = dfG.loc[group_df.index][["horseId", "birthY", "raceGrade", "label"]]

    dfG1 = dfG1[dfG1["birthY"].isin(list(range(1998, 2024)))]
    dfG1["win"] = (dfG1["label"].isin([1]) &
                   dfG1["raceGrade"].isin(firstwin)).astype(int)
    dfG1["win"] = dfG1.groupby("horseId")["win"].cumsum()
    win_horseId_list = []
    dfG2 = dfG1[dfG1["win"].isin([0])]
    if 0 in target:
        win_horseId_list = list(
            set(dfG1["horseId"].unique()) - set(dfG2["horseId"].unique()))
    dfG_cnt = dfG2.groupby("horseId")["horseId"].count()
    dfG_cnt = pd.concat([dfG_cnt, pd.Series(0, index=win_horseId_list)])
    if mode:
        return dfG_cnt.describe()
    return dfG_cnt

dfW2des = count_raceNum_for_G(list(range(9)), dfG=dfG)
dfW2des

# 2勝するまでの出走回数が0のものを除外
dfW2 = count_raceNum_for_G(list(range(9)), dfG=dfG, mode=False)
dfW2 = dfW2[~dfW2.isin([0])]
dfW2.describe()

pickup = 15
threash = 0.25
dflist = []
multiColumns = []
# 2019年以降に出走したことのある競走馬のみに絞る
dfG2 = dfG[dfG["horseId"].isin(dfG[dfG["raceDate"].dt.year.isin(
    list(range(2019, 2024)))]["horseId"].unique())]
for g, idfG in dfG2.groupby(["field", "dist_cat"]):
    # field, dist_catごとに出走したことのある競走馬に対して、
    dfG_cnt = count_raceNum_for_G(
        list(range(9)), mode=False, dfG=idfG).rename(f"Grace")
    upperhorse = dfG_cnt[dfG_cnt.quantile(threash) >= dfG_cnt].index.tolist()
    lowerhorse = dfG_cnt[dfG_cnt.quantile(1-threash) < dfG_cnt].index.tolist()
    if len(upperhorse) == 0:
        upperhorse = dfG_cnt[dfG_cnt.isin([dfG_cnt.min()])].index.tolist()
    if len(lowerhorse) == 0:
        lowerhorse = dfG_cnt[dfG_cnt.isin([dfG_cnt.max()])].index.tolist()

    dfGhigh25 = dfG2[dfG2["horseId"].isin(upperhorse)]
    dfGlow25 = dfG2[dfG2["horseId"].isin(lowerhorse)]

    # 種牡馬
    dftarget = dfstallion
    dflist += [dftarget[dftarget["horseId"].isin(dfGhigh25["horseId"].unique())]["GenName"].value_counts().sort_values(
        ascending=False).head(pickup).reset_index().apply(lambda x: ", ".join([str(x) for x in x.tolist()]), axis=1)]
    dflist += [dftarget[dftarget["horseId"].isin(dfGlow25["horseId"].unique())]["GenName"].value_counts().sort_values(
        ascending=False).head(pickup).reset_index().apply(lambda x: ", ".join([str(x) for x in x.tolist()]), axis=1)]

    multiColumns += [[g[0], f"high{int(threash*100)}%", g[1]],
                     [g[0], f"low{int(threash*100)}%", g[1]]]
threash_col = [f"high{int(threash*100)}%", f"low{int(threash*100)}%"]
idfgen = pd.concat(dflist, axis=1).rename(
    index=lambda idx: f"{1+idx}位").fillna("")
idfgen.columns = pd.MultiIndex.from_tuples(multiColumns)
idfgen = idfgen.T.sort_index(key=lambda x: [({"ダ": 0, "芝": 1, f"high{int(threash*100)}%": 0} | {
                             k: n for n, k in enumerate("SMILE")}).get(ix, 1) for ix in x])
idfgen[idfgen.columns[:5]]

colormap = "RdYlBu"
# linux系なら以下あたりにフォントファイルがあるかも？調べてもらう方が早いと思います。
# font_path="/usr/share/fonts/truetype/fonts-japanese-gothic.ttf"

# Windowsの人は以下あたりにあるかと
font_path = "C:/Windows/Fonts/meiryo.ttc"

for field in ["ダ", "芝"]:
    idfg = idfgen.loc[field]
    ncols = len(idfg.loc[idfg.index[0][0]])
    nrows = len(threash_col)
    plt.figure(figsize=(27, 9))
    cnt = 1
    for row, idx in enumerate(threash_col, start=1):
        for cidx in "SMILE"[:ncols]:
            word_list = []
            for word in idfg.loc[(idx, cidx)].tolist():
                if word == "":
                    break
                word_list += [word.split(", ")[0]]*int(word.split(", ")[1])
            if len(word_list) == 0:
                break
            random.shuffle(word_list)
            plt.subplot(nrows, ncols, cnt)
            wordcloud = WordCloud(
                background_color="gray",
                width=800,
                height=800,
                font_path=font_path,
                colormap=colormap,
            ).generate(" ".join(word_list))
            plt.imshow(wordcloud, interpolation="bilinear")
            plt.title(", ".join([field, idx, cidx]))
            plt.axis("off")
            cnt += 1
    plt.show()

pickup = 15
threash = 0.25
dflist = []
multiColumns = []
# 2019年以降に出走したことのある競走馬のみに絞る
dfG2 = dfG[dfG["horseId"].isin(dfG[dfG["raceDate"].dt.year.isin(
    list(range(2019, 2024)))]["horseId"].unique())]
for g, idfG in dfG2.groupby(["field", "dist_cat"]):
    # field, dist_catごとに出走したことのある競走馬に対して、
    dfG_cnt = count_raceNum_for_G(
        list(range(9)), mode=False, dfG=idfG).rename(f"Grace")
    upperhorse = dfG_cnt[dfG_cnt.quantile(threash) >= dfG_cnt].index.tolist()
    lowerhorse = dfG_cnt[dfG_cnt.quantile(1-threash) < dfG_cnt].index.tolist()
    if len(upperhorse) == 0:
        upperhorse = dfG_cnt[dfG_cnt.isin([dfG_cnt.min()])].index.tolist()
    if len(lowerhorse) == 0:
        lowerhorse = dfG_cnt[dfG_cnt.isin([dfG_cnt.max()])].index.tolist()

    dfGhigh25 = dfG2[dfG2["horseId"].isin(upperhorse)]
    dfGlow25 = dfG2[dfG2["horseId"].isin(lowerhorse)]

    # 母父
    dftarget = dfbStallion
    dflist += [dftarget[dftarget["horseId"].isin(dfGhigh25["horseId"].unique())]["GenName"].value_counts().sort_values(
        ascending=False).head(pickup).reset_index().apply(lambda x: ", ".join([str(x) for x in x.tolist()]), axis=1)]
    dflist += [dftarget[dftarget["horseId"].isin(dfGlow25["horseId"].unique())]["GenName"].value_counts().sort_values(
        ascending=False).head(pickup).reset_index().apply(lambda x: ", ".join([str(x) for x in x.tolist()]), axis=1)]

    multiColumns += [[g[0], f"high{int(threash*100)}%", g[1]],
                     [g[0], f"low{int(threash*100)}%", g[1]]]
threash_col = [f"high{int(threash*100)}%", f"low{int(threash*100)}%"]
idfgen = pd.concat(dflist, axis=1).rename(
    index=lambda idx: f"{1+idx}位").fillna("")
idfgen.columns = pd.MultiIndex.from_tuples(multiColumns)
idfgen = idfgen.T.sort_index(key=lambda x: [({"ダ": 0, "芝": 1, f"high{int(threash*100)}%": 0} | {
                             k: n for n, k in enumerate("SMILE")}).get(ix, 1) for ix in x])
idfgen[idfgen.columns[:5]]

for field in ["ダ", "芝"]:
    idfg = idfgen.loc[field]
    ncols = len(idfg.loc[idfg.index[0][0]])
    nrows = len(threash_col)
    plt.figure(figsize=(27, 9))
    cnt = 1
    for row, idx in enumerate(threash_col, start=1):
        for cidx in "SMILE"[:ncols]:
            word_list = []
            for word in idfg.loc[(idx, cidx)].tolist():
                if word == "":
                    break
                word_list += [word.split(", ")[0]]*int(word.split(", ")[1])
            if len(word_list) == 0:
                break
            random.shuffle(word_list)
            plt.subplot(nrows, ncols, cnt)
            wordcloud = WordCloud(
                background_color="gray",
                width=800,
                height=800,
                font_path=font_path,
                colormap=colormap,
            ).generate(" ".join(word_list))
            plt.imshow(wordcloud, interpolation="bilinear")
            plt.title(", ".join([field, idx, cidx]))
            plt.axis("off")
            cnt += 1
    plt.show()