# 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

# # データベースファイルのパス
# dbpath = 'autokindle.sqlite'
 
# # データベース接続とカーソル生成
# connection = sqlite3.connect(dbpath)
# # 自動コミットにする場合は下記を指定（コメントアウトを解除のこと）
# # connection.isolation_level = None
# cursor = connection.cursor()
 
# # エラー処理（例外処理）
# try:
#     # CREATE
 
#     # INSERT
#     cursor.execute("INSERT INTO buy_kindle_items VALUES (1, 'a12b4', 'あパンパン', '2020-02-27')")

#     # SEARCH
#     cursor.execute

# except sqlite3.Error as e:
#     print('sqlite3.Error occurred:', e.args[0])
 
# # 保存を実行（忘れると保存されないので注意）
# connection.commit()
 
# # 接続を閉じる
# connection.close()
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 createTableKindleItems():
    con = init(db_path)
    
    con.execute("CREATE TABLE IF NOT EXISTS kindle_items (id INTEGER PRIMARY KEY AUTOINCREMENT, asin TEXT, tracking_id TEXT,product_category TEXT, merchant_name TEXT, title TEXT, price INT, point INT, created DATETIME, updated DATETIME)")
    con.commit()
    #con.execute("ALTER TABLE buy_kindle_items ADD COLUMN price INT")
    #con.execute("ALTER TABLE buy_kindle_items ADD COLUMN price INT")
    #                    ALTER TABLE テーブル名 ADD COLUMN IF NOT EXISTS カラム名 データ型;
    con.commit()

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

    return cursor.fetchone()

#データをデータベースに格納する
def insertData(data):
    con = init(db_path)
    cursor = con.cursor()
    cursor.execute("INSERT INTO kindle_items (asin,title,tracking_id,product_category,merchant_name,price,point,created,updated) VALUES (?,?,?,?,?,?,?,?,?)",(
            data['asin'],
            data['title'],
            data['tracking_id'],
            data['product_category'],
            data['merchant_name'],
            data['price'],
            data['point'],
            dt.now().strftime("%Y-%m-%d %H:%M:%S"),
            dt.now().strftime("%Y-%m-%d %H:%M:%S"),
        )
    )
    # 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 getMonthItems():

    # 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")
    # print(year)
    # print(month)
    # print(start_time)
    # print(end_time)
    # print(dt.now().strftime("%Y-%m-01 00:00:00"))
    # print(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 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 getDayItems():

    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")
    # print(year)
    # print(month)
    # print(start_time)
    # print(end_time)
    # print(dt.now().strftime("%Y-%m-01 00:00:00"))
    # print(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 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