# Python 3.5.2 にて動作を確認
# sqlite3 標準モジュールをインポート
import sqlite3
import sys
from datetime import datetime as dt
import json
import pandas as pd
import datetime
import calendar


keys_json = open('config.json', 'r')
keys = json.load(keys_json)

#任意に変更してください
DATABASE_PATH = keys["database_path"]

db_path = DATABASE_PATH

def init(dbpath):
    con = sqlite3.connect(dbpath)
    #con.row_factory = sqlite3.Row
    # 自動コミットにする場合は下記を指定（コメントアウトを解除のこと）
    # connection.isolation_level = None
    return con

#データベースをつくる
def createTablebuyKindleItems():
    con = init(db_path)
    
    con.execute("CREATE TABLE IF NOT EXISTS buy_kindle_items (id INTEGER PRIMARY KEY AUTOINCREMENT, asin TEXT, title TEXT, price INT, point INT, created DATETIME)")
    con.commit()

def checkItem(asin):
    #c = conn.cursor()
    
    con = init(db_path)
    cursor = con.cursor()
    cursor.execute('SELECT * FROM buy_kindle_items WHERE asin = ?', (asin,))

    return cursor.fetchone()

#データをデータベースに格納する
def insertData(book_data):
    con = init(db_path)
    cursor = con.cursor()
    cursor.execute("INSERT INTO buy_kindle_items (asin,title,created,price,point) VALUES (?,?,?,?,?)",(book_data['asin'],book_data['title'],dt.now().strftime("%Y-%m-%d %H:%M:%S"),book_data['price'],book_data['point']))
    # cursor.execute("INSERT INTO buy_kindle_items VALUES (1, 'asin', 'あパンパン', '2020-02-27')")
    con.commit()

def getDfBuyKindleItems():
    con = init(db_path)    
    df = pd.read_sql('SELECT title,asin,price,point,created FROM buy_kindle_items where price <> -1', con)

    return df
def getMonthBuyItems():

    # print("aaaa")
    year = int(dt.now().strftime("%Y"))
    month  = int(dt.now().strftime("%m"))
    last_day = calendar.monthrange(year, month)[1]
    start_time = dt.now().strftime("%Y-%m-01 00:00:00")
    # start_time = dt.now().strftime("%Y-09-01 00:00:00")
    end_time = dt.now().strftime("%Y-%m-"+ str(last_day) +" 23:59:59")


    con = init(db_path)
    con.row_factory = sqlite3.Row
    cursor = con.cursor()
    cursor.execute('SELECT * FROM buy_kindle_items WHERE price <> -1 AND created >= ? AND created <= ?', (start_time,end_time))
    # df = pd.read_sql('SELECT * FROM buy_kindle_items WHERE created >= ? AND created <= ?', (start_time,end_time))

    return cursor

def getDayBuyItems():

    start_time = dt.now().strftime("%Y-%m-%d 00:00:00")
    # start_time = dt.now().strftime("%Y-09-01 00:00:00")
    end_time = dt.now().strftime("%Y-%m-%d 23:59:59")

    con = init(db_path)
    con.row_factory = sqlite3.Row
    cursor = con.cursor()
    cursor.execute('SELECT * FROM buy_kindle_items WHERE price <> -1 AND created >= ? AND created <= ?', (start_time,end_time))
    # df = pd.read_sql('SELECT * FROM buy_kindle_items WHERE created >= ? AND created <= ?', (start_time,end_time))

    return cursor