import sqlite3
import random
import os

# Initialize the item database
conn = sqlite3.connect('/mnt/data/inventory.db')
c = conn.cursor()

# Create the 'items' table
c.execute('''
CREATE TABLE IF NOT EXISTS items (
	character TEXT,
	name TEXT,
	description TEXT,
	quantity INTEGER,
	quality TEXT,
	status TEXT,
	PRIMARY KEY (character, name)
)
''')
c.execute('''
CREATE TABLE IF NOT EXISTS characters (
	character TEXT PRIMARY KEY,
	profession TEXT,
	level INTEGER DEFAULT 1
)
''')
conn.commit()


def remove_item(character, name, quantity):
	# Check current quantity
	c.execute('SELECT quantity FROM items WHERE character = ? AND name = ?', (character, name))
	result = c.fetchone()
	if result:
		current_quantity = result[0]
		if current_quantity >= quantity:
			# Update the quantity if there's enough to remove
			c.execute('UPDATE items SET quantity = quantity - ? WHERE character = ? AND name = ?', (quantity, character, name))
		else:
			# Raise an exception if there's not enough quantity
			raise Exception(f"Not enough quantity of '{name}' to remove. Requested: {quantity}, Available: {current_quantity}")
	else:
		# Raise an exception if the item doesn't exist
		raise Exception(f"Item '{name}' not found in {character}'s inventory.")
	conn.commit()
# Function to read the items in a character's inventory
def read_inventory(character):
	c.execute('SELECT * FROM items WHERE character = ?', (character,))
	return c.fetchall()

# Function to add items to the inventory
def add_item(character, name, description, quantity, quality, status):
	c.execute('''
	INSERT INTO items (character, name, description, quantity, quality, status)
	VALUES (?, ?, ?, ?, ?, ?)
	ON CONFLICT(character, name) DO UPDATE SET
	quantity = quantity + EXCLUDED.quantity,
	description = EXCLUDED.description,
	quality = EXCLUDED.quality,
	status = EXCLUDED.status
	''', (character, name, description, quantity, quality, status))
	conn.commit()

# Function to add or update a character's profession level
def set_profession_level(character, profession, level):
	# Check if the character already exists
	c.execute('SELECT profession FROM characters WHERE character = ?', (character,))
	result = c.fetchone()

	if result:
		# If the profession matches, increase the level; otherwise, add the new profession with level 2
		if result[0] == profession:
			c.execute('UPDATE characters SET level = ? WHERE character = ? AND profession = ?', (level, character, profession))
		else:
			c.execute('INSERT OR REPLACE INTO characters (character, profession, level) VALUES (?, ?, ?)', (character, profession, level))
	else:
		# Add new character with the profession at level 2
		c.execute('INSERT INTO characters (character, profession, level) VALUES (?, ?, ?)', (character, profession, level))
	conn.commit()

def describe_character_professions(character):
	c.execute('SELECT profession, level FROM characters WHERE character = ?', (character, ))
	return c.fetchall()

# Modified roll_for_outcome function to include character and profession
def roll_for_outcome(character, profession, difficulty_level):
	# Fetch the character's level for the given profession
	c.execute('SELECT level FROM characters WHERE character = ? AND profession = ?', (character, profession))
	result = c.fetchone()

	profession_level = result[0] if result else 1  # Default to 1 if profession not found

	# Roll a d6 for each profession level and sum the results
	total_roll = sum(random.randint(1, 6) for _ in range(profession_level) )
	print('dices',profession_level)
	print('roll', total_roll)
	print('difficulty', difficulty_level)
	# Calculate the difference
	difference = total_roll - difficulty_level
	print('differnce', difference)
	# Determine the outcome
	if difference >= 8:
		return "critical success"
	elif difference >= 4:
		return "success"
	elif difference >= 2:
		return "minor progress"
	elif difference <= -8:
		return "critical failure"
	elif difference <= -4:
		return "failure"
	elif difference <= -2:
		return "minor setback"
	return "need to try again"