#!/usr/bin/python3

import re
import mysql.connector
import mysql.connector.pooling
import datetime
import json

class Ledger:
	def __init__(self, test=False):
		with open('config.json', 'r') as cfg_file:
				self.cfg = json.loads(cfg_file.read())
		if test: self.cfg["database"] = "ledger_test"
		self.pool = mysql.connector.pooling.MySQLConnectionPool(
			pool_name = 'ledger_pool',
			pool_size = 5,
			pool_reset_session=True,
			**self.cfg)

		self.setup()

	def clean_up(self):
		self.cursor.close()
		self.connection.close()

	def query(self, model, data=""):
		conn = self.pool.get_connection()
		cursor = conn.cursor()
		if type(data) is list:
			cursor.executemany(model, data)
		else:
			cursor.execute(model, data)
		last = cursor.lastrowid
		cursor.close()
		conn.close()
		return last

	def select(self, model, data=""): #returns a list of tuples
		conn = self.pool.get_connection()
		cursor = conn.cursor()
		cursor.execute(model, data)
		rows = cursor.fetchall()
		cursor.close()
		conn.close()

		return rows

	def insert_from_file(self, file):
		query = 'INSERT IGNORE INTO records (date,type,descr,amount,notes,id_category,id_place) VALUES (%s,%s,%s,%s,%s,%s,%s)'
		with open(file, 'r') as f:
			records = re.findall(('<tr>'
				'<td border="1">[0-9]{2}/[0-9]{2}/[0-9]{4}</td>'
				'<td border="1">([0-9]{2}/[0-9]{2}/[0-9]{4})</td>'
				'<td border="1">(.*?)</td>'
				'<td border="1">(.*?)</td>'
				'<td class="excelCurrency" border="1">&euro; (.*?)</td>'
				'</tr>'), f.read()) # date, type, descr, amount
		matched = self.match_records(records)
		self.query(query, matched)

	def match_records(self, records):
		matched = []
		places = self.select('SELECT * FROM places')
		for record in records:
			record = self.format_record(record)
			for place in places:
				if re.search(place[2], record[2]):
					record = record[:5] + (place[3], place[0])
			matched += [record]
		return matched

	def format_record(self, record):
		date = self.str2date(record[0])
		amount = float(record[3].replace('.', '').replace(',', '.'))
		descr = record[2].lower()
		return (date, record[1], descr, amount, None, None, None)

	def get_cat_id_by_name(self, name):
		query = "SELECT ID FROM categories WHERE name=%s"
		r = self.select(query, (name,))
		return r[0][0]

	def get_categories(self):
		return self.select("SELECT * FROM categories")

	def delete_category(self, id_cat):
		self.query("DELETE FROM categories WHERE ID=%s", (id_cat,))

		recs = self.select("SELECT * FROM records WHERE id_category=%s", (id_cat,))
		for rec in recs:
			self.edit_rec_cat(rec[0], None)

		places = self.select("SELECT * FROM places WHERE id_category=%s", (id_cat,))
		for place in places:
			self.edit_place(place[0], (place[1], place[2], None))

	def create_category(self, name_cat):
		self.query("INSERT INTO categories (name) VALUES (%s)", (name_cat,))

	def rename_category(self, id_cat, new_name):
		self.query("UPDATE categories SET name=%s WHERE ID=%s", (new_name, id_cat))

	def edit_place(self, id_place, new_data):
		params = new_data+(id_place,)
		self.query("UPDATE places SET name=%s, pattern=%s, id_category=%s WHERE ID=%s", params)
		recs = self.select("SELECT * FROM records WHERE id_place=%s OR id_place IS NULL", (id_place,))
		for rec in recs:
			if re.search(new_data[1], rec[3]):
				self.edit_rec_place(rec[0], id_place)
				#if rec[6]==None:
				self.edit_rec_cat(rec[0], new_data[2])
			elif rec[7]!=None:
				self.edit_rec_place(rec[0], None)
				self.edit_rec_cat(rec[0], None)

	def get_places(self):
		return self.select("SELECT * FROM places")

	def delete_place(self, id_place):
		self.query("DELETE FROM places WHERE ID=%s", (id_place,))
		recs = self.select("SELECT * FROM records WHERE id_place=%s", (id_place,))
		for rec in recs:
			self.edit_rec_place(rec[0], None)
			self.edit_rec_cat(rec[0], None)

	def create_place(self, data): #(name, pattern, id_cat)
		p_id = self.query("INSERT INTO places (name, pattern, id_category) VALUES (%s, %s, %s)", data)
		recs = self.select("SELECT * FROM records WHERE id_place IS NULL")
		for rec in recs:
			if re.search(data[1], rec[3]):
				self.edit_rec_place(rec[0], p_id)
				if rec[6]==None: self.edit_rec_cat(rec[0], data[2])

	def edit_rec_place(self, r_id, place):
		self.query("UPDATE records SET id_place=%s WHERE ID=%s", (place, r_id))

	def edit_rec_cat(self, r_id, cat):
		self.query("UPDATE records SET id_category=%s WHERE ID=%s", (cat, r_id))

	def get_records(self, year, month):
		return self.select("SELECT * FROM records WHERE YEAR(date)=%s AND MONTH(date)=%s", (year, month))

	def create_record(self, data):
		query = 'INSERT IGNORE INTO records (date,type,descr,amount,notes,id_category,id_place) VALUES (%s,%s,%s,%s,%s,%s,%s)'
		self.query(query, data)

	def edit_record(self, id_rec, new_data):
		query = "UPDATE records SET date=%s, descr=%s, amount=%s, notes=%s, id_category=%s, id_place=%s WHERE ID=%s"
		params = new_data+(id_rec,)
		self.query(query, params)

	def delete_record(self, id_rec):
		self.query("DELETE FROM records WHERE ID=%s", (id_rec,))

	def get_cat_sum(self, id_cat, year):
		now = datetime.datetime.now()
		query = "SELECT sum(amount) FROM records WHERE YEAR(date)=%s AND MONTH(date)=%s AND id_category=%s"
		v = [0.0]*13 # [jan_sum, feb_sum, ..., avg]
		for m in range(0,12):
			s = self.select(query, (year, m+1, id_cat))[0][0]
			if not s: s = 0.0
			v[m] = float(s)
			v[12] += float(s)
		if str(now.year)==year:	v[12] //= now.month
		else: v[12] //= 12
		return v

	def get_year_cats(self, year):
		y = []
		tots = []
		for cat in self.get_categories():
			cat_report = self.get_cat_sum(cat[0], year)
			y.append({
				"cat_id": cat[0],
				"cat_name": cat[1],
				"report": cat_report
				})
			tots.append(cat_report)
		y.append({
			"cat_id": -1,
			"cat_name": "Tot",
			"report": self.vec_sum(tots)
			})
		return y

	# in: a list of lists
	# out: a list where the i element is the sum of the input lists' i elements
	def vec_sum(self, lists):
		return [round(sum(z),1) for z in zip(*lists)]

	def str2date(self, date_str):
		datetime_obj = datetime.datetime.strptime(date_str, '%d/%m/%Y')
		return datetime_obj.date()

	def setup(self):
		places = (
			"CREATE TABLE IF NOT EXISTS `places` ("
			"  `ID` INT NOT NULL AUTO_INCREMENT,"
			"  `name` TINYTEXT,"
			"  `pattern` TINYTEXT,"
			"  `id_category` INT REFERENCES categories(ID),"
			"  PRIMARY KEY (`ID`)"
			") ENGINE=InnoDB")

		records = (
			"CREATE TABLE IF NOT EXISTS `records` ("
			"  `ID` INT NOT NULL AUTO_INCREMENT,"
			"  `date` DATE,"
			"  `type` TINYTEXT,"
			"  `descr` TEXT,"
			"  `amount` DECIMAL(16, 2),"
			"  `notes` TEXT,"
			"  `id_category` INT REFERENCES categories(ID),"
			"  `id_place` INT REFERENCES places(ID),"
			"  UNIQUE KEY unique_record (date,type(255),descr(500),amount),"
			"  PRIMARY KEY (`ID`)"
			") ENGINE=InnoDB")

		categories = (
			"CREATE TABLE IF NOT EXISTS `categories` ("
			"  `ID` INT NOT NULL AUTO_INCREMENT,"
			"  `name` TINYTEXT,"
			"  PRIMARY KEY (`ID`)"
			") ENGINE=InnoDB")

		self.query(places)
		self.query(records)
		self.query(categories)

	def populate(self):
		cat_q = 'INSERT INTO categories (name) VALUES (%s)'
		cat_d = [('Alimentari',), ('Benzina',), ('Bollette',), ('Lavori',), ('Salute',), ('Casa',)]
		self.query(cat_q, cat_d)

		c = self.get_cat_id_by_name

		places_q = 'INSERT INTO places (name, pattern, id_category) VALUES (%s, %s, %s)'

		places_d = [('Lidl', 'lidl', c('Alimentari')),
			('Farmacia', 'farmacia', c('Salute')),
			('Maurys', 'maury s', c('Casa')),
			('Mutuo', 'mutuo', c('Bollette'))]

		self.query(places_q, places_d)
