import pymysql
import MarketDB
import requests
from datetime import datetime
import pandas as pd

# 장이 열리는날이 아니면 프로그램 종료
import exchange_calendars as ecals
import datetime as dt

XKRX = ecals.get_calendar("XKRX") # 한국 코드
ismarketopen= XKRX.is_session(dt.date.today().strftime("%Y-%m-%d")) # 오늘은 개장일인지 확인

if ismarketopen == False :
    exit(0)



def read_naver(code, company, pages_to_fetch=1):
    """네이버에서 주식 시세를 읽어서 데이터프레임으로 반환"""
    try:
        url = f"http://[Log in to view URL]"
        df = pd.DataFrame()
        page_url = '{}&page={}'.format(url, 1)
        html = requests.get(page_url, headers={'User-agent': 'Mozilla/5.0'}).text
        df = pd.concat([df, pd.read_html(html)[0]])
        tmnow = datetime.now().strftime('%Y-%m-%d %H:%M')
        print('[{}] {} ({}) pages are downloading...'.
              format(tmnow, company, code), end="\r")
        df = df.rename(columns={'날짜': 'date', '종가': 'close', '전일비': 'diff'
            , '시가': 'open', '고가': 'high', '저가': 'low', '거래량': 'volume'})
        df['date'] = df['date'].replace('.', '-')  # .을 -로 바꾼다.
        df = df.dropna()
        # 타입 인트 지정하면 자리수가 넘어가면 에러남 그냥 없애서 자동으로 플로트계산하고 들어가도록
        # df[['close', 'diff', 'open', 'high', 'low', 'volume']] = df[['close',
        #                                                              'diff', 'open', 'high', 'low',
        #                                                              'volume']].astype(int)
        df['tran_amount'] = df['close'] * df['volume']
        df['tran_amount'] = df['tran_amount']

        df = df[['date', 'open', 'high', 'low', 'close', 'diff', 'volume', 'tran_amount']]
    except Exception as e:
        print('Exception occured :', str(e))
        return None
    return df

def replace_into_db(df, num, code, company):
    """네이버에서 읽어온 주식 시세를 DB에 REPLACE"""

    with conn.cursor() as curs:
        for r in df.itertuples():
            sql = f"REPLACE INTO daily_price VALUES ('{code}', " \
                  f"'{r.date}', {r.open}, {r.high}, {r.low}, {r.close}, " \
                  f"{r.diff}, {r.volume}, {r.tran_amount})"
            curs.execute(sql)
        conn.commit()
        print('[{}] #{:04d} {} ({}) : {} rows > REPLACE INTO daily_' \
              'price [OK]'.format(datetime.now().strftime('%Y-%m-%d' \
                                                          ' %H:%M:%S'), num + 1, company, code, len(df)))


def tran_amount_update():
    """판다스로 에러나는 거래대금 db에서 직접 업데이트"""
    with conn.cursor() as curs:
        sql = f"Update daily_price Set tran_amount = close*volume"
        curs.execute(sql)
        conn.commit()

def make_only_date():
    """판다스로 에러나는 거래대금 db에서 직접 업데이트"""
    with conn.cursor() as curs:
        sql = f"DELETE FROM only_date"
        curs.execute(sql)
        conn.commit()
        sql = f"replace INTO only_date(DATE) SELECT max(DATE) FROM daily_price GROUP BY DATE"
        curs.execute(sql)
        conn.commit()

def send_message(msg):
    """디스코드 메세지 전송"""
    now = datetime.now()
    message = {"content": f"[{now.strftime('%Y-%m-%d %H:%M:%S')}] {str(msg)}"}
    requests.post(
        'https://[Log in to view URL]',
        data=message)
    print(message)


if __name__ == '__main__':
    send_message(f'15시_DB_업데이터_네이버용_2/6, 4/6, 6/6번째 -START')

    conn = pymysql.connect(host='localhost', user='root',
                           password='njmr0623$$', db='stock_analysis', charset='utf8')

    mk_codes = MarketDB.MarketDB().codes

    while True:  # 계속돈다는거지 true니까
        t_now = datetime.now()
        t_1500 = t_now.replace(hour=15, minute=00, second=0, microsecond=0)
        t_1515 = t_now.replace(hour=15, minute=16, second=0, microsecond=0)
        t_1520 = t_now.replace(hour=15, minute=20, second=0, microsecond=0)
        today = datetime.today().weekday()
        # if today == 5 or today == 6:  # 토요일이나 일요일이면 자동 종료, 0부터 월요일
        #     send_message(f"\n주말이므로 프로그램을 종료합니다."
        #                  f"\n====================================")
        #     break
        if t_now  < t_1515 : #원복필요
            for idx, code in enumerate(mk_codes):
                if idx % 6 == 2:
                    df = read_naver(code, mk_codes[code], 1)
                    if df is None:
                        continue
                    replace_into_db(df, idx, code, mk_codes[code])
                if idx % 6 == 4:
                    df = read_naver(code, mk_codes[code], 1)
                    if df is None:
                        continue
                    replace_into_db(df, idx, code, mk_codes[code])
                if idx % 6 == 0:
                    df = read_naver(code, mk_codes[code], 1)
                    if df is None:
                        continue
                    replace_into_db(df, idx, code, mk_codes[code])
            # tran_amount_update()  # 거래대금 하나 추가함
            # make_only_date()  # db에서 백테스팅시 날짜구하는게 너무 느려서 따로 날짜만 만듬

        if t_1515 < t_now : #원복필요
            send_message(f'15시_DB_업데이터_네이버용_4번째 -END-')
            exit(0)