import os
import re
import sys
import sqlite3
import xlwings as xw
import string
import numpy


def FindDirs(dir):
    """List directories"""
    dirs = []
    pattern = re.compile("^[^.]+$")
    for content in os.listdir(dir):
        if pattern.match(content):
            dirs.append(os.path.abspath(content))
    return dirs


def WhereIs(verbose=False):
    """Display whats in the script's folder"""
    pathname = os.path.dirname(sys.argv[0])
    return os.path.abspath(pathname)
    if verbose is True:
        print("\nPath where the script is saved:\n", pathname, "\n")
        print("\nFiles contained in the folder:\n", os.listdir(pathname), "\n")


def CreateTable(pdb, listvars):
    try:
        GenerateRows(pdb, listvars)
    except:
        print("Table %s already exists." % (pdb))
        ans = input("Try to overwrite table? (y/n)\n")
        if ans == "y":
            db.cur.execute("DROP TABLE IF EXISTS %s" % (pdb))
            GenerateRows(pdb, listvars)
        else:
            quit()


def GenerateRows(pdb, listvars):
    """Generate table for DMS data"""
    db.cur.execute("CREATE TABLE %s(id INTEGER PRIMARY KEY, position INTEGER, wt TEXT, mutation TEXT)" % (pdb))
    if len(listvars) > 0:
        for i in listvars:
            db.cur.execute("ALTER TABLE %s ADD COLUMN %s FLOAT" % (pdb, i))
    else:
        print("Only one column supplied")
        db.cur.execute("ALTER TABLE %s ADD COLUMN fitness FLOAT" % (pdb))



class DatabaseSelector():
    """Manage database connection"""
    def __init__(self):
        current_path = WhereIs()
        self.local_dirs = FindDirs(current_path)
        self.local_dirs.append(current_path)
        self.DatabaseListing()
        if self.database_list:
            print("Finding databases...")
            self.DatabaseIterator()
        else:
            print("No database found!")

    def DatabaseListing(self):
        self.database_list = []
        for directory in self.local_dirs:
            for unique_database in self.FindDb(directory):
                self.database_list.append(os.path.join(directory, unique_database))

    def DatabaseIterator(self):
        for database in self.database_list:
            answer_selector = input("Is this the correct database: %s (y|n|q)?\n" % (database))
            if answer_selector == "yes" or answer_selector == "y":
                try:
                    self.conn = sqlite3.connect(database)
                    self.cur = self.conn.cursor()
                    print("Connected to: %s !" % (database))
                    break
                except:
                    print("Unable to connect to database %s" % (database))
                    continue
            elif answer_selector == "no" or answer_selector == "n":
                continue
            elif answer_selector == "quit" or answer_selector == "q":
                print("Exiting program...")
                quit()

    def FindDb(self, directory, p1="^.+.sqlite3", p2="^database.+"):
        """Find output files"""
        files = []
        pattern1 = re.compile(p1)
        pattern2 = re.compile(p2)
        for content in os.listdir(directory):
            if pattern1.match(content):
                files.append(content)
        if not files:
            for content in os.listdir(directory):
                if pattern2.match(content):
                    files.append(content)
        return files