import os
import sqlite3
from typing import List, Tuple

DATABASE = os.path.join(os.path.dirname(os.path.abspath(__file__)), "database.db")

class Connection:
	def __init__(self):
		self.connection = sqlite3.connect(DATABASE)
		self.cursor = self.connection.cursor()
		self.do_rollback = False

	def commit(self):
		self.connection.commit()
		self.do_rollback = False

	def execute(self, sql: str, tuple = ()):
		self.do_rollback = True
		self.cursor.execute(sql, tuple)

	def fetch_all(self):
		return self.cursor.fetchall()

	def close(self):
		if self.do_rollback:
			self.connection.rollback()

		self.connection.close()

	def __enter__(self):
		return self

	def __exit__(self, *args):
		self.close()

with Connection() as conn:
	conn.execute('''CREATE TABLE IF NOT EXISTS Workspaces (
		id integer NOT NULL PRIMARY KEY AUTOINCREMENT,
		name text NOT NULL,
		address text NOT NULL,
		main_photo text NOT NULL,
		description text NOT NULL,
		website text NOT NULL,
		email text NOT NULL,
		phone_number text NOT NULL,
		opening_hours text,
		checkin_instructions text)''')
	conn.execute('''CREATE TABLE IF NOT EXISTS AdditionalPhotos (
		id integer NOT NULL PRIMARY KEY AUTOINCREMENT,
		url text NOT NULL,
		workspace_id integer NOT NULL,
		FOREIGN KEY (workspace_id) REFERENCES Workspaces(id))''')
	conn.execute('''CREATE TABLE IF NOT EXISTS AddressToLatLong (
		id integer NOT NULL PRIMARY KEY AUTOINCREMENT,
		address text NOT NULL,
		latlong text NOT NULL)''')
	conn.commit()

def lookup_address(address: str):
	with Connection() as conn:
		conn.execute("SELECT latlong FROM AddressToLatLong WHERE address = ?", (address,))
		res = conn.cursor.fetchone()
		if res == None:
			return None

		(lat, long) = res[0].split(",")
		return (float(lat), float(long))

def set_address_latlong(address: str, latlong: Tuple[float, float]):
	with Connection() as conn:
		latlong_str = f"{ repr(latlong[0]) },{ repr(latlong[1]) }"
		conn.execute("INSERT INTO AddressToLatLong (address, latlong) VALUES (?, ?)", (address, latlong_str))
		conn.commit()

class Workspace:
	def __init__(self, name: str,
		address: str, main_photo: str, additional_photos: List[str], description: str,
		website: str, email: str, phone_number: str, opening_hours: str, checkin_instructions: str):
		self.id = None
		self.name = name
		self.address = address
		self.main_photo = main_photo
		self.additional_photos = additional_photos
		self.description = description
		self.website = website
		self.email = email
		self.phone_number = phone_number
		self.opening_hours = opening_hours
		self.checkin_instructions = checkin_instructions

	def validate(self):
		errors = []
		if len(self.name.strip()) <= 0:
			errors.append("Name must not be empty")
		if len(self.address.strip()) <= 0:
			errors.append("Address must not be empty")
		if len(self.main_photo.strip()) <= 0:
			errors.append("Main photo must not be empty")
		if len(self.description.strip()) <= 0:
			errors.append("Description must not be empty")
		if len(self.website.strip()) <= 0:
			errors.append("Website must not be empty")
		if len(self.email.strip()) <= 0:
			errors.append("Email must not be empty")
		if len(self.phone_number.strip()) <= 0:
			errors.append("Phone number must not be empty")
		if len(self.opening_hours.strip()) <= 0:
			errors.append("Opening hours must not be empty")

		for x in self.additional_photos:
			if len(x.strip()) <= 0:
				errors.append("Each edditional photos must not be empty")

		return errors

	def from_query(conn, tuple):
		(id, name, address, main_photo, description, website, email, phone_number, opening_hours, checkin_instructions) = tuple
		additional_photos = []

		conn.execute("SELECT url FROM AdditionalPhotos WHERE workspace_id = ?", (str(id)))
		for x in conn.cursor.fetchall():
			additional_photos.append(x[0])

		workspace = Workspace(name, address, main_photo, additional_photos, description, website, email, phone_number, opening_hours, checkin_instructions)
		workspace.id = id
		return workspace

def add_workspace(workspace: Workspace):
	with Connection() as conn:
		conn.execute(
			"INSERT INTO Workspaces (name, address, main_photo, description, website, email, phone_number, opening_hours, checkin_instructions) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)",
			(workspace.name, workspace.address, workspace.main_photo, workspace.description, workspace.website, workspace.email, workspace.phone_number, workspace.opening_hours, workspace.checkin_instructions)
		)
		id = conn.cursor.lastrowid
		for url in workspace.additional_photos:
			conn.execute("INSERT INTO AdditionalPhotos (url, workspace_id) VALUES (?, ?)", (url, id))
		conn.commit()
		return id

def get_workspaces():
	with Connection() as conn:
		conn.execute("SELECT * FROM Workspaces")
		return [Workspace.from_query(conn, x) for x in conn.cursor.fetchall()]
		

print(get_workspaces())