import pandas as pd
from sqlalchemy import create_engine


def get_engine():
    username = 'envision'
    password = 'envision'
    host = '172.21.6.37'
    port = 3306
    dbname = 'envision'
    return create_engine(f'mysql+pymysql://{username}:{password}@{host}:{port}/{dbname}')


def generate_sql(df: pd.DataFrame, wind_name: str):
    print(f"开始执行{wind_name}")
    wind_nos = df['风机号'].unique()

    all_sqls = list()

    begin_time_str = '2025-01-01 00:00:00'
    end_time_str = '2025-03-01 00:00:00'
    for wind_no in wind_nos:
        wind_sqls = []
        index = 0

        stander_cols = list()
        stander_cols.append('wind_factory')
        stander_cols.append('wind_no')
        stander_cols.append('occur_time')

        wind_df = df[df['风机号'] == wind_no]
        for wind_factory, table, col, en_name in zip(wind_df['风场'], wind_df['历史采样表名'], wind_df['历史采样域名'],
                                                     wind_df['en_name']):
            stander_cols.append(en_name)
            if index == 0:
                wind_sqls.append(
                    f"select * from  (select '{wind_factory}' as wind_factory,{wind_no} as wind_no, occur_time, {col} as {en_name}"
                    f" from {table} where occur_time >= '{begin_time_str}' and occur_time <'{end_time_str}') a{index}")
                index = index + 1
            else:
                wind_sqls.append(
                    f"\n inner join  "
                    f"(select '{wind_factory}' as wind_factory,{wind_no} as wind_no, occur_time, {col} as {en_name}"
                    f" from {table} where occur_time >= '{begin_time_str}' and occur_time <'{end_time_str}') a{index} "
                    f"on a{index - 1}.wind_factory = a{index}.wind_factory and a{index - 1}.wind_no = a{index}.wind_no"
                    f" and a{index - 1}.occur_time = a{index}.occur_time")
                index = index + 1

        result_sql = "".join(wind_sqls) + ";"
        stander_cols_str = "'" + "',".join(stander_cols) + "'"
        all_sqls.append((result_sql, stander_cols_str))

    return all_sqls


def show_sqls(stander_cols_str, datas):
    for index, data in enumerate(datas):
        print(f'String [] cols = {"".join(stander_cols_str[index])}')
        print()
        print(data)
    print()


def save_to_csv(datas, name):
    dfs = list()
    for data in datas:
        dfs.append(pd.read_sql(data, get_engine()))

    df = pd.concat(dfs, ignore_index=True)

    df.to_csv(name + ".csv", encoding='utf8', index=False)


if __name__ == '__main__':
    df = pd.read_csv(r"C:\Users\wzl\Desktop\中广核104测点\min_tables.csv")

    datas = generate_sql(df[df['风场'] == '右玉'], '右玉')

    show_sqls(datas[1], datas[0])

    # save_to_csv(datas[0], '右玉')