"""output_vjsk_inst_pharm_data"""

from src.aws.s3 import VjskSendBucket
from src.batch.common.batch_context import BatchContext

from src.db.database import Database
from src.logging.get_logger import get_logger
import tempfile
import os.path as path
import csv

logger = get_logger('V実消化用施設データ作成処理')

sql_err_msg = "SQL実行エラーです。"


def exec():
    vjsk_csv_file_name = 'ComInst.csv'

    # バッチ共通設定を取得
    batch_context = BatchContext.get_instance()

    if not batch_context.is_ultmarc_imported:
        logger.info('アルトマーク取込が行われていないため、V実消化用施設データ作成処理をスキップします。')
        return

    db = Database.get_instance()
    try:
        logger.info('処理開始')

        try:
            # DB接続
            db.connect()
        except Exception as e:
            logger.info('DB接続エラーです。')
            raise e

        # CSVファイルの作成用のSQL実行(施設)
        record_inst = select_inst_record(db)
        # CSVファイルの作成用のSQL実行(薬局)
        record_pharm = select_pharm_record(db)
        # CSVファイル作成
        csv_file_path = make_csv_data(record_inst, record_pharm, vjsk_csv_file_name)

        vjsk_bucket = VjskSendBucket()
        try:
            # s3へデータ移動
            vjsk_bucket.upload_inst_pharm_csv_file(vjsk_csv_file_name, csv_file_path)
        except Exception as e:
            logger.info('S3バケットにCSVデータを作成できませんでした。')
            raise e

        try:
            # 処理後ファイルをバックアップ
            vjsk_bucket.backup_inst_pharm_csv_file(vjsk_csv_file_name, batch_context.syor_date)
        except Exception as e:
            logger.info('バックアップバケットへCSVデータをコピーできませんでした。')
            raise e

        csv_count = len(record_inst) + len(record_pharm)
        logger.info(f'CSV出力件数: {csv_count}')
        logger.info('正常終了')
    except Exception as e:
        raise e
    finally:
        db.disconnect()
    return


def select_inst_record(db):
    # CSVファイル作成用のSQL実行(施設)
    try:
        # 施設テーブル検索SQL
        sql = """\
            SELECT dcf_dsf_inst_cd,
                    inst_div_cd,
                    addr_unknown_reason_cd,
                    form_inst_name_kana,
                    inst_name_kana,
                    form_inst_name_kanji,
                    inst_name_kanji,
                    rltd_univ_prnt_cd,
                    bed_num,
                    close_flg,
                    estab_sche_flg,
                    close_start_ym,
                    estab_sche_ym,
                    ward_abolish_flg,
                    inst_repre_cd,
                    inst_repre_kana,
                    inst_repre,
                    phone_number_non_flg,
                    unconf_flg,
                    inst_phone_number,
                    inst_addr_kana,
                    inst_addr,
                    postal_number,
                    village_cd,
                    prefc_cd,
                    city_cd,
                    addr_display_number,
                    addr_cnt_kana,
                    addr_cnt,
                    manage_cd,
                    delete_sche_reason_cd,
                    hp_assrt_cd,
                    dup_opp_cd,
                    insp_item_micrb,
                    insp_item_serum,
                    insp_item_blood,
                    insp_item_patho,
                    insp_item_paras,
                    insp_item_biochem,
                    insp_item_ri,
                    re_exam_cd,
                    prmit_bed_num_other,
                    prmit_bed_num_mental,
                    prmit_bed_num_tuber,
                    prmit_bed_num_infection,
                    prmit_bed_num_sum,
                    prmit_bed_num_gen,
                    prmit_bed_num_rcup,
                    prmit_bed_maint_ymd,
                    inst_pharm_div,
                    abolish_ymd,
                    delete_flg,
                    filler_1,
                    filler_2,
                    filler_3,
                    filler_4,
                    filler_5,
                    regist_date,
                    create_user,
                    update_date,
                    update_user,
                    sys_regist_date,
                    regist_prgm_id,
                    sys_update_date,
                    update_prgm_id
            FROM src05.com_inst ORDER BY dcf_dsf_inst_cd
        """
        return db.execute_select(sql)
    except Exception as e:
        logger.debug(sql_err_msg)
        raise e


def select_pharm_record(db):
    # CSVファイル作成用のSQL実行(薬局)
    try:
        # 薬局テーブル検索SQL
        sql = """\
            SELECT dcf_dsf_inst_cd,
                    inst_div_cd,
                    addr_unknown_reason_cd,
                    form_inst_name_kana,
                    inst_name_kana,
                    form_inst_name_kanji,
                    inst_name_kanji,
                    '' AS rltd_univ_prnt_cd,
                    '' AS bed_num,
                    close_flg,
                    estab_sche_flg,
                    close_start_ym,
                    estab_sche_ym,
                    '' AS ward_abolish_flg,
                    '' AS inst_repre_cd,
                    inst_repre_kana,
                    inst_repre,
                    phone_number_non_flg,
                    unconf_flg,
                    inst_phone_number,
                    inst_addr_kana,
                    inst_addr,
                    postal_number,
                    village_cd,
                    prefc_cd,
                    city_cd,
                    addr_display_number,
                    addr_cnt_kana,
                    addr_cnt,
                    manage_cd,
                    delete_sche_reason_cd,
                    '' AS hp_assrt_cd,
                    dup_opp_cd,
                    '' AS insp_item_micrb,
                    '' AS insp_item_serum,
                    '' AS insp_item_blood,
                    '' AS insp_item_patho,
                    '' AS insp_item_paras,
                    '' AS insp_item_biochem,
                    '' AS insp_item_ri,
                    '' AS re_exam_cd,
                    '' AS prmit_bed_num_other,
                    '' AS prmit_bed_num_mental,
                    '' AS prmit_bed_num_tuber,
                    '' AS prmit_bed_num_infection,
                    '' AS prmit_bed_num_sum,
                    '' AS prmit_bed_num_gen,
                    '' AS prmit_bed_num_rcup,
                    '' AS prmit_bed_maint_ymd,
                    inst_pharm_div,
                    abolish_ymd,
                    delete_flg,
                    filler_1,
                    filler_2,
                    filler_3,
                    filler_4,
                    filler_5,
                    regist_date,
                    create_user,
                    update_date,
                    update_user,
                    sys_regist_date,
                    regist_prgm_id,
                    sys_update_date,
                    update_prgm_id
            FROM src05.com_pharm ORDER BY dcf_dsf_inst_cd
        """
        return db.execute_select(sql)
    except Exception as e:
        logger.debug(sql_err_msg)
        raise e


def make_csv_data(record_inst: list, record_pharm: list, vjsk_csv_file_name: str):
    # 一時ファイルとして保存する(CSVファイル)
    try:

        temporary_dir = tempfile.mkdtemp()
        csv_file_path = path.join(temporary_dir, vjsk_csv_file_name)

        head_str = ['DCF_DSF_INST_CD', 'INST_DIV_CD', 'ADDR_UNKNOWN_REASON_CD', 'FORM_INST_NAME_KANA', 'INST_NAME_KANA',
                    'FORM_INST_NAME_KANJI', 'INST_NAME_KANJI', 'RLTD_UNIV_PRNT_CD', 'BED_NUM', 'CLOSE_FLG', 'ESTAB_SCHE_FLG',
                    'CLOSE_START_YM', 'ESTAB_SCHE_YM', 'WARD_ABOLISH_FLG', 'INST_REPRE_CD', 'INST_REPRE_KANA', 'INST_REPRE',
                    'PHONE_NUMBER_NON_FLG', 'UNCONF_FLG', 'INST_PHONE_NUMBER', 'INST_ADDR_KANA', 'INST_ADDR', 'POSTAL_NUMBER',
                    'VILLAGE_CD', 'PREFC_CD', 'CITY_CD', 'ADDR_DISPLAY_NUMBER', 'ADDR_CNT_KANA', 'ADDR_CNT', 'MANAGE_CD',
                    'DELETE_SCHE_REASON_CD', 'HP_ASSRT_CD', 'DUP_OPP_CD', 'INSP_ITEM_MICRB', 'INSP_ITEM_SERUM', 'INSP_ITEM_BLOOD',
                    'INSP_ITEM_PATHO', 'INSP_ITEM_PARAS', 'INSP_ITEM_BIOCHEM', 'INSP_ITEM_RI', 'RE_EXAM_CD', 'PRMIT_BED_NUM_OTHER',
                    'PRMIT_BED_NUM_MENTAL', 'PRMIT_BED_NUM_TUBER', 'PRMIT_BED_NUM_INFECTION', 'PRMIT_BED_NUM_SUM', 'PRMIT_BED_NUM_GEN',
                    'PRMIT_BED_NUM_RCUP', 'PRMIT_BED_MAINT_YMD', 'INST_PHARM_DIV', 'ABOLISH_YMD', 'DELETE_FLG', 'FILLER_1', 'FILLER_2',
                    'FILLER_3', 'FILLER_4', 'FILLER_5', 'REGIST_DATE', 'CREATE_USER', 'UPDATE_DATE', 'UPDATE_USER', 'SYS_REGIST_DATE',
                    'REGIST_PRGM_ID', 'SYS_UPDATE_DATE', 'UPDATE_PRGM_ID']

        with open(csv_file_path, mode='w', encoding='UTF-8') as csv_file:
            # ヘッダ行書き込み（くくり文字をつけない為にwriterowではなく、writeを使用しています）
            csv_file.write(f"{','.join(head_str)}\n")

            # Shift-JIS、CRLF、価囲いありで書き込む
            writer = csv.writer(csv_file, delimiter=',', lineterminator='\n',
                                quotechar='"', doublequote=True, quoting=csv.QUOTE_ALL,
                                strict=True
                                )

            # データ部分書き込み(施設)
            for record_inst_data in record_inst:
                record_inst_value = list(record_inst_data.values())
                csv_data = ['' if n is None else n for n in record_inst_value]
                writer.writerow(csv_data)

            # データ部分書き込み(薬局)
            for record_pharm_data in record_pharm:
                record_pharm_value = list(record_pharm_data.values())
                csv_data = ['' if n is None else n for n in record_pharm_value]
                writer.writerow(csv_data)

    except Exception as e:
        logger.info('CSVデータの作成に失敗しました。')
        raise e

    return csv_file_path
