"""Upgrade the Luna LMS database to the current version.
"""

# Work started on 2024-07-30

import site
site.addsitedir("../")
import luna_lms

from optparse import OptionParser
import os.path
import sqlite3


UPDATE_REGISTRY = {}


def register(f):
	"""Register handler functions for database updates.
	"""

	numerical_version = int(f.__name__.split("_update_to_")[1])

	UPDATE_REGISTRY[numerical_version] = f

	return f


@register
def _update_to_1006(connection):
	"""Update the database to 0.1.6.
	"""

	print("Updating database at connection {} to 0.1.6".format(connection))

	cursor = connection.cursor()

	# Check if the database is actually in the claimed format

	result = None

	try:
		result = cursor.execute('SELECT * FROM formats;')
		result = result.fetchone()

	except sqlite3.OperationalError:

		print("Database is in pre 0.1.6 format")

	if result is not None:

		# Table exists, so database is not actually pre 0.1.6

		connection.commit()
		connection.close()

		raise Exception("The database is not in pre 0.1.6 format")

	# Create new table

	result = cursor.execute('''
CREATE TABLE "formats" (
	"key"	INTEGER,
	"identifier"	TEXT UNIQUE,
	"publisher"	TEXT NOT NULL,
	"media_type"	TEXT NOT NULL,
	"language"	TEXT,
	"sha256"	TEXT NOT NULL,
	PRIMARY KEY("key")
);
''')

	result.fetchone()

	# Populate table with bare minimum standard values

	result = cursor.execute('''
INSERT INTO "formats" (
	"key",
	"identifier",
	"publisher",
	"media_type",
	"language",
	"sha256"
)
VALUES
(
	1,
	'luna.text.html.en',
	'luna',
	'text/html',
	'en',
	'82b141764d5ea3957d1969894a15c3904321fcad0a33f1ed75aad78d71e23836'
),
(
	2,
	'luna.text.html.de',
	'luna',
	'text/html',
	'de',
	'49b429fc7f07a05e81a66dd215fc72ed9305751baa4ae7d8622eef81769be69b'
);
''')

	result.fetchone()
	connection.commit()

	# Add foreign key constraint referencing the new table
	#
	# Adding new foreign keys in place taken from this answer to "How do I add a
	# foreign key to an existing SQLite table?":
	# https://stackoverflow.com/a/48016934/1132250

	print("Existing SQL for table variants:")
	result = cursor.execute("select sql from SQLITE_MASTER where name = 'variants' and type = 'table';")
	result = result.fetchone()
	print(result)

	print("Updated SQL for table variants:")
	result = cursor.execute('''
select replace(
	sql,
	'PRIMARY KEY("key")',
    'PRIMARY KEY("key"), FOREIGN KEY("format") REFERENCES "formats"("identifier") ON UPDATE CASCADE ON DELETE SET NULL'
)
from SQLITE_MASTER
where name = 'variants' and type = 'table';
''')

	result = result.fetchone()
	print(result)

	print("Writing updated SQL ...")

	cursor.execute("pragma writable_schema=1;")

	result = cursor.execute('''
update SQLITE_MASTER set sql = replace(
	sql,
	'PRIMARY KEY("key")',
    'PRIMARY KEY("key"), FOREIGN KEY("format") REFERENCES "formats"("identifier") ON UPDATE CASCADE ON DELETE RESTRICT'
)
where name = 'variants' and type = 'table';
''')
	result = result.fetchone()
	connection.commit()

	cursor.execute("pragma writable_schema=0;")

	print("Done.")

	# Update previous formats

	result = cursor.execute('''
UPDATE variants
SET format = 'luna.text.html.en'
WHERE format = 'text/html';
''')

	result = result.fetchone()
	connection.commit()

	print('''INFO: The previous format 'text/html' has been unconditionally replaced by
'luna.text.html.en'. If your HTML content is not in English, you should add a
respective format in table 'formats', and replace the format value of the
variant accordingly.
''')

	# Finally, update required version number

	result = cursor.execute("UPDATE course SET requires = 'Luna LMS 0.1.6';")
	result = result.fetchone()
	connection.commit()

	print("Update to 0.1.6 successful.")


def main():

	parser = OptionParser(usage = "%prog [options] SQLITE_FILE")

	(options, args) = parser.parse_args()

			UPDATE_REGISTRY[numerical_handler_version](connection)

	print("Closing connection")

	connection.commit()
	connection.close()

	
if __name__ == "__main__":

	main()