import os
import sqlite3
import random
import sys
from sugar.activity import activity
from xmlio import Xmlio
from path import path
import subprocess

import traceback

random.seed()

global debug_info
debug_info = True

'''
Handles pysqlite Interaction: query("SELECT..."), commit("UPDATE...")
'''
class Question:
    imgfn = u''
    sndfn = u''
    map = u''
    cat = 0
    subcat = 0
    lang = 1    # 1 = English
    text = u''
    answer = u''
    answer_link = ''
    
class Database:

    db_filename = "main.db"
    cur = '';
    con = '';
    
    def __init__(self):
        pass

    def query(self, q):
        dataList = []
        try:
            self.cur.execute(q)
        except:
            # If DB connection get's lost on Activity-Startup, then Reset
            print 'query reset=', q
            self.con = sqlite3.connect(self.db_fn)
            self.cur = self.con.cursor()
            self.cur.execute("-- types unicode")
            try:
                self.cur.execute(q)
            except:
                print 'execute failed', q
                        
        data = self.cur.fetchall()
        #if data:
            #print 'data', len(data)
        #else:
            #print 'data = None'
        if data: dataList = [list(row) for row in data]
        #print 'dbmgr return dataList', len(dataList)
        return dataList

    def commit(self, q):
        try:
            self.cur.execute(q)
        except:
            # If DB connection get's lost on Activity-Startup, then Reset
            print 'execute reset=', q
            self.con = sqlite3.connect(self.db_fn)
            self.cur = self.con.cursor()
            self.cur.execute("-- types unicode")
            self.cur.execute(q)
            
        try:
            self.con.commit()
        except:
            print 'failure on commit'
        
    def load(self, services):   
        # Init DB
        db = os.path.join(activity.get_activity_root(), "data")
        fullname = os.path.join(db, self.db_filename)
        self.db_fn = fullname
        #for testing, remove db
        #subprocess.call("rm -rf " + fullname, shell=True)
        if os.path.isfile(fullname):
            self.con = sqlite3.connect(fullname)
            self.cur = self.con.cursor()
            self.cur.execute("-- types unicode")
        else:
            # Check for Database Setup
            self.con = sqlite3.connect(fullname)
            self.cur = self.con.cursor()
            self.cur.execute("-- types unicode")
            # Create image, sound folders
            self.imagepath = os.path.join(db,'image')
            subprocess.call("mkdir -p " + self.imagepath, shell=True)
            self.soundpath = os.path.join(db,'sound')
            subprocess.call("mkdir -p " + self.soundpath, shell=True)
            # Setup New Database
            self.cur.execute("CREATE TABLE 'categories' ('id' INTEGER PRIMARY KEY AUTOINCREMENT, 'text' TEXT);")
            self.cur.execute("CREATE TABLE 'questions' ('id' INTEGER PRIMARY KEY AUTOINCREMENT, 'prompt' TEXT, 'response' TEXT, 'image_fn' TEXT, 'sound_fn' TEXT, 'map' TEXT, 'answer_link' VARCHAR ( 1024 ));")
            self.cur.execute("CREATE TABLE 'Leitner' ('id' INTEGER PRIMARY KEY AUTOINCREMENT, 'question_id' INT, 'count_found' INT, 'count notfound' INT, 'box' INT, 'time' INT, 'day' INT);")
            self.cur.execute("CREATE TABLE 'catlink' ('id' INTEGER PRIMARY KEY AUTOINCREMENT, 'parent_id' INT, 'child_id' INT);") 
            self.cur.execute("CREATE TABLE 'quizlink' ('id' INTEGER PRIMARY KEY AUTOINCREMENT, 'quiz_id' INT, 'question_id' INT);")  

            # Add questions, quizzes from builtins
            self.load_builtins()
            
            self.con.commit()
                
            print "* database created"
                     
        if debug_info: print "- database loaded"
        return True

    #loads preinstalled quizzes from activity-bundle/imagequiz_library into db
    def load_builtins(self):         
        self.builtins = path(activity.get_bundle_path()) / 'imagequiz_library'
        categorypaths = self.builtins.listdir()
        categories = []
        for categorypath in categorypaths:
            categories.append(categorypath.name)
        categories.sort()
        
        for quiz in categories:
             if not (quiz == 'image' or quiz == 'sound'):
                 #create category entry in db
                 self.cat_id = self.add_cat(path(quiz).namebase)
                 if 'xml' in path(quiz).name:
                     self.add_questions_xml(quiz)
                 else:
                     self.add_questions_csv(quiz)

    def add_questions_xml(self, quizname):
        quiztree = Xmlio(path(self.builtins) / quizname)
        quiz = quiztree.getroot()
        question = Question()
        #what we need to do:
        for card in quiz:
           question.prompt = card.findtext('question')
           question.response = card.findtext('answer')
           if card.find('answer').findtext('image'):
               question.imgfn = card.find('answer').findtext('image')
           else:
               question.imgfn = ""
           if card.find('question').findtext('sound'):
               question.sndfn = card.find('question').findtext('sound')
           else:
               question.sndfn = ""
           question.map = ""
           question.answer_link = ""
           self.add_question(question)
           if question.imgfn and len(question.imgfn) > 0:
               srcpath = path(self.builtins) / 'image' / question.imgfn
               dstpath = path(self.imagepath) / question.imgfn
               if srcpath.exists():
                   path.copy(srcpath,dstpath)
           if question.sndfn and len(question.sndfn) > 0:
               srcpath = path(self.builtins) / 'sound' / question.sndfn
               dstpath = path(self.soundpath) / question.sndfn
               if srcpath.exists():
                   path.copy(srcpath,dstpath)
                   
    def add_questions_csv(self, quiz):
        question = Question()
        csvquiz = open(path(self.builtins) / quiz)
        csvtext = csvquiz.read()
        csvquiz.close()

        questions = csvtext.split(";;;<br>")
        for q in questions:
            csv = q.split(";;")
            if len(csv) < 2:
                #no more questions
                return
            question.prompt = csv[1].replace("'", "")
            question.response = csv[4].replace("'", "")
            question.imgfn = "%s" % (csv[0])
            question.sndfn = ""
            question.map = csv[2]	
            if len(csv) > 5:
                question.answer_link = csv[5].replace("'", "")
            else:
                question.answer_link = ""
            self.add_question(question)
            #copy resources to resource folders
            if question.imgfn and len(question.imgfn) > 0:
                srcpath = path(self.builtins) / 'image' / question.imgfn
                dstpath = path(self.imagepath) / question.imgfn
                if srcpath.exists():
                    path.copy(srcpath,dstpath)
            if question.sndfn and len(question.sndfn) > 0:
                srcpath = path(self.builtins) / 'sound' / question.sndfn
                dstpath = path(self.soundpath) / question.sndfn
                if srcpath.exists():
                    path.copy(srcpath,dstpath)
        
    def add_cat(self, cat_namebase):
        # returns new cat_id
        # Category exists?
        q = u"SELECT count(*) FROM categories WHERE text='%s'" % (cat_namebase)
        res = self.query(q)
        if res[0][0] == 0:
            # No, insert
            q = u"INSERT INTO categories (text) VALUES ('%s')" % (cat_namebase)
            self.commit(q)
        q = u"SELECT id FROM categories WHERE text='%s'" % (cat_namebase)
        res = self.query(q)
        id = res[0][0]        
        return id;


    def add_question(self, question):
       
        if len(question.answer_link) > 0:
            question.answer_link = question.answer_link.replace("'", '"')

        # Insert Question
        q = u'INSERT INTO questions (prompt, response, image_fn, sound_fn, map, answer_link) VALUES ("%s", "%s", "%s", "%s", "%s", "%s")' % (question.prompt, question.response, question.imgfn, question.sndfn,  question.map, question.answer_link)
        self.commit(q)

        # Get Question_ID of last question inserted
        question_id = self.cur.lastrowid
            
        # Link question to quiz
        q = u"INSERT INTO quizlink (quiz_id, question_id) VALUES (%i, %i)" % (self.cat_id, question_id)
        self.commit(q)

        return True
          
''' 
Main Game Backend (Kernel)
'''

class Kernel:   
    services = []
    def __init__(self):
                pass
        
    def load(self, s):
        pass
        
    def add_service(self, descriptor, function):
        print "* registering service:",descriptor,
        self.services.append([descriptor, function])
        print "... ok"
            
    def start_service(self, descriptor, params=False):
        for s in self.services:
            if s[0] == descriptor: 
                print "* starting service:",s[0],"(",params,")"

                try: 
                    print s[1](params)
                except:
                    try: 
                        print s[1]()
                    except: 
                        print "! starting service %s failed" % s[0]
                        traceback.print_exc()
    def hex2rgb(hex): 
        if hex[0:1] == '#': hex = hex[1:]; 
        return (hex2dec(hex[:2]), hex2dec(hex[2:4]), hex2dec(hex[4:6]))

''' 
 Class PluginManager: 
 ====================
 This class loads the plugins and hooks in the services
 (Loads all .py files from plugin_dir/ not starting with _)
    - plugin1.py will be loaded
    - _plugin1.py will not be loaded
 Usage:
    plugger = PluginManager()
    plugger.load_plugins()
    plugger.debug()
'''
def empty(): pass

class PluginManager:
    plugins = []
    plugin_dir = 'plugins'
        
    services = ''
    
    def __init__(self):
        pass 
        
    def load(self, services):
        self.services = services
        if debug_info: print "- plugger loaded"
    
    def load_plugins(self):
        filenames = os.listdir(self.plugin_dir)
        for fn in filenames:
            # All .py files not starting with _
            if fn[-3:] == '.py' and fn[:1] != '_':
                # Extract File without Extension
                plugin_fn = os.path.splitext(fn)[0]
                
                # Import
                p = __import__(os.path.join(self.plugin_dir, plugin_fn))
                self.plugins.append(p)
                

                if debug_info: 
                    try:
                        print "- plugin import:", p.__PLUGIN_NAME__
                    except:
                        print "no plugin name for ", plugin_fn
                
                p.__SERVICES__ = self.services
                                    
                try: p.load()
                except: traceback.print_exc()

    def close_plugins(self):
        for p in self.plugins:
            try: p.close()
            except: traceback.print_exc()
        
    def debug(self):
        print "Activated Plugins:"
        for p in self.plugins:
            print "-", p.__PLUGIN_NAME__