import psycopg2

from peewee import *
from playhouse.shortcuts import model_to_dict
from datetime import datetime


def set_storage(app, db_config):
	basename = db_config.get('basename')
	username = db_config.get('login')
	pwd = db_config.get('password')

	db = PostgresqlDatabase(basename, user=username)

	class Operator(Model):
		class Meta:
			database = db
			constraints = [SQL('foreign key(restaurant_id) references restaurant')]

		id = PrimaryKeyField()
		restaurant_id = IntegerField()
		last_name = CharField()
		first_name = CharField()
		father_name = CharField()
		login = CharField()
		password = CharField()
		last_login = DateTimeField()

	class Role(Model):
		class Meta:
			database = db
			constraints = [SQL('foreign key(operator_id) references Operator')]

		id = PrimaryKeyField()
		operator_id = IntegerField()
		can_order = BooleanField()
		can_admin = BooleanField()


	class Category(Model):
		class Meta:
			database = db
			constraints = [SQL('foreign key(parent_cat_id) references Category')]

		id = PrimaryKeyField()
		parent_cat_id = IntegerField(null = True)
		title = CharField()

	class Menu_Item(Model):
		class Meta:
			database = db
			constraints = [SQL('foreign key(cat_id) references Category')]

		id = PrimaryKeyField()
		cat_id = IntegerField(null = True)
		title = CharField()
		price = FloatField()

	class Restaurant(Model):
		class Meta:
			database = db

		id = PrimaryKeyField()
		address = CharField()

	class Order_Plate(Model):
		class Meta:
			database = db,
			constraints = [
				SQL('foreign key(order_id) references Restaurant_Order'),
				SQL('foreign key(menu_item_id) references Menu_Item')
			]

		id = PrimaryKeyField()
		order_id = IntegerField()
		menu_item_id = IntegerField()
		price = FloatField()

	class Restaurant_Order(Model):
		class Meta:
			database = db,
			constraints = [
				SQL('foreign key(restaurant_id) references Restaurant'),
				SQL('foreign key(operator_id) references Operator')
			]

		id = PrimaryKeyField()
		restaurant_id = IntegerField()
		operator_id = IntegerField()
		order_time = DateTimeField(default=datetime.now)

	class Storage:
		def __init__(self, app, db, db_config):
			self.app = app
			self.cfg = db_config
			self.db = db

			db.connect()


		def operator(self, login):
			try:
				op = Operator.get(Operator.login == login)
			except DoesNotExist as e:
				return None
			return model_to_dict(op)

		def restaurant(self, op):
			try:
				restaurant = Restaurant.get(Restaurant.id == op['restaurant_id'])
			except:
				return None
			return model_to_dict(restaurant)

		def find_admin(self, login, password):
			admin = Operator \
				.select().join(Role, on=(Role.operator_id == Operator.id)) \
				.where(
					Operator.login == login,
					Operator.password == password,
					Role.can_admin == 't')
			if len(admin) == 0:
				return False
			return model_to_dict(admin.get())


		def menu(self):
			items = [model_to_dict(item) for item in Menu_Item.select()]
			categories = [model_to_dict(cat) for cat in Category.select()]

			root_cats = [cat for cat in categories if cat['parent_cat_id'] is None]
			root_items = [item for item in items if item['cat_id'] is None]
			stack = [cat['id'] for cat in root_cats]
			while stack:
				cat_id = stack.pop()
				cur_cat = next(cat for cat in categories if cat['id'] == cat_id)

				child_cats = [cat for cat in categories if cat['parent_cat_id'] == cat_id]
				stack.extend([cat['id'] for cat in child_cats])
				cur_cat['child_cats'] = child_cats
				cur_cat['child_items'] = [item for item in items if item['cat_id'] == cur_cat['id']]

			return {
				'child_cats': root_cats,
				'child_items': root_items
			}


			for cat in categories:
				cat_items = [item for item in items if item['cat_id'] == cat['id']]
				cat['items'] = cat_items

			return categories

		def flat_items_cats(self, menu_list):
			items = menu_list['child_items']
			categories = []
			cats = menu_list['child_cats']
			while cats:
				cat = cats.pop()
				categories.append({
					'id': cat['id'],
					'title': cat['title']
				})
				cats.extend(cat['child_cats'])
				items.extend(cat['child_items'])

			app.logger.debug(items)
			app.logger.debug(categories)
			return items, categories

		def menu_update(self, menu_list):
			items, cats = self.flat_items_cats(menu_list)
			for item in items:
				app.logger.info('menu item(id: {}), title -> {}' \
					.format(item['id'], item['title']))
				Menu_Item \
					.update(title = item['title'], price = item['price']) \
					.where(Menu_Item.id == item['id']) \
					.execute()

			for cat in cats:
				app.logger.info('category(id: {}), title -> {}' \
					.format(cat['id'], cat['title']))
				Category \
					.update(title = cat['title']) \
					.where(Category.id == cat['id']) \
					.execute()

			return True

		#TODO: consider this as redundant code
		def id_or_none(self, parent_cat_id):
			if not parent_cat_id:
				return None
			if parent_cat_id in ['null', 'undefined']:
				return None
			try:
				i = int(parent_cat_id)
			except:
				i = None
			return i

		def create_menu_item(self, parent_cat_id=None):
			parent_cat_id = self.id_or_none(parent_cat_id)
			item = Menu_Item.create(
				title='New Menu Item',
				cat_id=parent_cat_id,
				price=0
			)
			return model_to_dict(item)

		def remove_menu_item(self, item_id):
			item = Menu_Item.get(Menu_Item.id == item_id)
			deleted = item.delete_instance()
			return bool(deleted)


		def create_menu_category(self, parent_cat_id=None):
			parent_cat_id = self.id_or_none(parent_cat_id)
			cat = Category.create(
				title='New Menu Category',
				parent_cat_id=parent_cat_id
			)
			return model_to_dict(cat)

		def remove_menu_category(self, cat_id):
			cat = Category.get(Category.id == cat_id)
			deleted = cat.delete_instance(recursive=True)
			return bool(deleted)

		def create_order(self, r_id, op_id, plates, created_at):
			self.db.begin()
			app.logger.debug('rid: {}'.format(r_id))
			app.logger.debug('op: {}'.format(op_id))
			order = Restaurant_Order.create(
				operator_id=op_id,
				restaurant_id=r_id,
				order_time=created_at
			)
			if not order:
				self.db.rollback()
				return False

			for plate in plates:
				try:
					plate = Menu_Item.get(Menu_Item.id == plate)
				except DoesNotExist as e:
					continue
				price = plate.get('price')
				plate_id = plate.get('id')
				order_plate = Order_Plate.create(
					order_id=order.id,
					menu_item_id=plate_id,
					price=price
				)
				if not order_plate:
					self.db.rollback()
					return False

			self.db.commit()
			return model_to_dict(order)

	return Storage(app, db, db_config)