import sqlite3
import time
from datetime import date

class CreditPayments:

    def __init__(self):
        # connect to DB
        # save cursor object to self property
        self.conn = sqlite3.connect("mydatabase.db")
        self.cursor = self.conn.cursor()

    #method convert date from MM.yyyy in unix format
    def get_timestamp(self, x):
        # Calc date in unix format
        date_arr = x.split(".")
        d = date(int(date_arr[1]), int(date_arr[0]), 1)
        unixtime = time.mktime(d.timetuple())
        return unixtime

    #method getRestOfCredit
    def getRestOfCredit(self, id, date):
        # get summ of credit
        # query payments from DB where deal_id=id AND pdate<=date
        # calculate rest of credit and return it
        sql = "SELECT SUM(summ) FROM payment WHERE deal_id = ? AND pdate <= ?"
        self.cursor.execute(sql, [(id), (self.get_timestamp(date))])
        payment = self.cursor.fetchone()[0]
        return self.getSummOfCredit(id) - payment

    def getAllRestOfCredit(self, id):
        return self.getRestOfCredit(id, "12.2018")

    #method getSummOfCredit
    def getSummOfCredit(self, id):
        # query summ of credit from DB where id=id
        # return it
        sql = "SELECT summ FROM credit WHERE id = ?"
        self.cursor.execute(sql, [id])
        return self.cursor.fetchone()[0]

    def deleteCredit(self, id):
        sql = "DELETE FROM payment WHERE deal_id = ?"
        self.cursor.execute(sql, [id])
        sql = "DELETE FROM credit WHERE id = ?"
        self.cursor.execute(sql, [id])
        self.conn.commit()