#!/usr/bin/python2
# -*- coding: utf-8 -*-

import MySQLdb as mysql
import sqlite3
import sys

class Definition(object):

	#ID is relative to the word type, eg noun 1, noun 2, verb 1, verb 2, not to the entire list
	id = 0
	word = ""
	dictionary = ""
	word_type = ""
	definition = ""
	uses = []
	synonyms = []
	antonyms = []

	def __init__(self, word, id, dictionary, word_type, definition, uses, synonyms, antonyms):
		self.word = word
		self.id = id
		self.dictionary = dictionary
		self.word_type = word_type
		self.definition = definition
		self.uses = uses
		self.synonyms = synonyms
		self.antonyms = antonyms

def create():
	try:
		con = sqlite3.connect('dictionaries/wordnet.db');

		with con:
			cur = con.cursor()
			cur.execute("CREATE TABLE types (id integer primary key not null , type text, abbreviation text)")
			cur.execute("INSERT INTO types (type, abbreviation) VALUES('noun', 'n')")
			cur.execute("INSERT INTO types (type, abbreviation) VALUES('verb', 'v')")
			cur.execute("INSERT INTO types (type, abbreviation) VALUES('adjective', 'adj')")
			cur.execute("INSERT INTO types (type, abbreviation) VALUES('adverb', 'adv')")
			cur.execute("CREATE TABLE definitions (id integer primary key not null , word text, type_id int, sub_id int, synset_id bigint, definition text)")
			cur.execute("CREATE TABLE uses (id integer primary key not null, definition_id bigint, quote text)")
			cur.execute("CREATE TABLE info (id integer primary key not null , key text, value text)")
			cur.execute("INSERT INTO info (key, value) VALUES('version', '1')")

	except sqlite3.Error, e:
		print "Database Error %d: %s" % (e.args[0],e.args[1])
		sys.exit(1)

def select_wn():
	try:
		con = mysql.connect('localhost', 'wordnet', 'words', 'wordnet');

		with con:
			cur = con.cursor(mysql.cursors.DictCursor)
			cur.execute("SELECT lemma, pos, sensenum, synsetid, definition, sampleset from dict")
			rows = cur.fetchall()
			print len(rows)
	except mysql.Error, e:
		print "Database Error %d: %s" % (e.args[0],e.args[1])
		sys.exit(1)
	
	return rows

def insert_wn(rows):
	try:
		con = sqlite3.connect('dictionaries/wordnet.db');

		with con:
			cur = con.cursor()

			for row in rows:
				if row['pos'] == 'n':
					type_id = 1
				elif row['pos'] == 'v':
					type_id = 2
				elif row['pos'] == 'a' or row['pos'] == 's':
					type_id = 3
				elif row['pos'] == 'r':
					type_id = 4

				cur.execute("INSERT INTO definitions(word, type_id, sub_id, synset_id, definition) values(?, ?, ?, ?, ?)", [row['lemma'], type_id, row['sensenum'], row['synsetid'], row['definition']])

				row_id = cur.lastrowid

				if row['sampleset'] is not None:
					uses = row['sampleset'].split("|")

					for use in uses:
						cur.execute("INSERT INTO uses(definition_id, quote) values(?, ?)", [row_id, use])

	except sqlite3.Error, e:
		print "Database Error %s" % (e.args[0])
		sys.exit(1)

def get_db_version():
	try:
		con = sqlite3.connect('dictionaries/wordnet.db');

		with con:
			cur = con.cursor()

			# Check if info table exists at all, if not then version 0
			cur.execute("SELECT count(name) FROM sqlite_master WHERE type='table' AND name='info'")
			row = cur.fetchone()
			if row[0] == 0:
				return 0
			else:
				cur.execute("SELECT value FROM info WHERE key = 'version'")
				row = cur.fetchone()
				return row[0]

	except sqlite3.Error, e:
		print "Database Error %s" % (e.args[0])
		sys.exit(1)

def update_db(version):
		try:
			con = sqlite3.connect('dictionaries/wordnet.db');
			with con:
				cur = con.cursor()

				if version < 1:
					cur.execute("CREATE TABLE info (id integer primary key not null , key text, value text)")
					cur.execute("INSERT INTO info (key, value) VALUES('version', '1')")

				if version < 2:
					cur = con.cursor()
					cur.execute("INSERT INTO types (type, abbreviation) VALUES('urban', 'urb.')")
					cur.execute("ALTER TABLE definitions ADD COLUMN dictionary_id int")
					cur.execute("CREATE TABLE categories (id integer primary key not null, category text)")
					cur.execute("CREATE TABLE dictionaries (id integer primary key not null, name text, abbreviation text)")
					cur.execute("INSERT INTO dictionaries (name, abbreviation) VALUES('WordNet', 'wn')")
					cur.execute("INSERT INTO dictionaries (name, abbreviation) VALUES('Urban Dictionary', 'urban')")
					cur.execute("CREATE TABLE definition_categories (id integer primary key not null, definition_id int category_id int)")
					cur.execute("UPDATE info set value = 2 where key = 'version'")

		except sqlite3.Error, e:
			print "Database Error %s" % (e.args[0])
			sys.exit(1)


def update_db_1():
	try:
		con = sqlite3.connect('dictionaries/wordnet.db');

		with con:
			cur = con.cursor()
			cur.execute("INSERT INTO types (type, abbreviation) VALUES('urban', 'urb.')")
			cur.execute("ALTER TABLE definitions ADD COLUMN dictionary_id int")
			cur.execute("CREATE TABLE categories (id integer primary key not null, category text)")
			cur.execute("CREATE TABLE dictionaries (id integer primary key not null, name text, abbreviation text")
			cur.execute("INSERT INTO dictionaries (name, abbreviation) VALUES('WordNet', 'wn')")
			cur.execute("INSERT INTO dictionaries (name, abbreviation) VALUES('Urban Dictionary', 'urban')")
			cur.execute("CREATE TABLE definition_categories (id integer primary key not null, definition_id int category_id int)")
			cur.execute("UPDATE info set value = 2 where key = 'version'")

	except sqlite3.Error, e:
		print "Database Error %d: %s" % (e.args[0],e.args[1])
		sys.exit(1)

def main():

	version = get_db_version()

	if version == 0:
		create()
		items = select()
		insert(items)

	update_db(version)


if __name__ == "__main__":
	main()