import re


def schema_to_dtypes(schemaFile):
    """
    Parse the SQL file containing the CREATE TABLE definitions and convert to
    a dtype format that python can understand. This can be used to initialise
    numpy record arrays for storing the tables in memory. Assumes the
    create statement has the form:
    
    'CREATE TABLE myTableName (entry1 double [args+], entry2 int(2), ... );'

    The 'CREATE TABLE' statement must have the same case. WARNING: recognises
    only , 'DOUBLE', 'INTEGER' and 'VARCHAR(n)' types in the SQL statement.
    """

    # Return these
    tableNameLst = []
    tableDefDict = {}
    tableSQLdict = {}
    
    # Compile a few useful regular expressions
    spaces = re.compile('\s+')
    commaAndSpaces = re.compile(',\s+')
    comment = re.compile('#.*')
    quotes = re.compile('\'[^\']*\'')
    brackets = re.compile('[\[|\]\(|\)|\{|\}]')
    createRe =  re.compile('^(CREATE TABLE|create table) (\w+)\s*\((.+)\)\s*$')
    
    # Translation function for data types: SQL to recarray
    # RecArray dtypes:
    #   bytes                = b<n>, e.g. b1
    #   ints                 = i<n>, e.g. i2, i4, i8,
    #   unsigned ints        = u<n>, e.g. u1, u2, u4, u8
    #   floats               = f<n>, e.g. f2, f4, f8
    #   complex              = c<n>, e.g. c8, c16
    #   fixed length strings = a<n>, e.g. a10, a100
    # where <n> is the number of bytes / chars, so float32=f4, float64=f8
    def trtype(dtype):
        floatRe = re.compile('^FLOAT')
        doubleRe = re.compile('^DOUBLE')
        intRe = re.compile('^INT')
        charRe = re.compile('^VARCHAR\((\d+)\)')
        if floatRe.match(dtype):
            return 'f4'
        if doubleRe.match(dtype):
            return 'f8'
        if intRe.match(dtype):
            return 'i8'
        mch = charRe.match(dtype)
        if mch:
             return 'a' + mch.group(1)
        return 'f8'    # default to float64
        
    # Loop through the SQL statements
    sqlLst = open(schemaFile).read().split(';')
    for sql in sqlLst:

        # Simplify the SQL statement
        sql = sql.replace('\r', ' ')        # kill carriage-return
        sql = sql.replace('\n', ' ')        # kill newlines
        sql = sql.strip()                   # kill external whitespace
        sql = spaces.sub(' ', sql)          # shrink internal whitespaces
        sql = commaAndSpaces.sub(',', sql)  # kill ambiguous spaces

        mch = createRe.match(sql)
        if mch:
            tableName = mch.group(2)
            tableSQLdict[tableName] = mch.group(0)
            tableNameLst.append(tableName)
            colDefLst = mch.group(3).strip().split(',')
            colDefLst = [x.split(' ')[:2] for x in colDefLst]

            # Translate the data types into a python recarray dtype list
            for i in range(len(colDefLst)):
                colDefLst[i][1] = trtype(colDefLst[i][1])
                colDefLst[i] = tuple(colDefLst[i])

            # Add to the table definition dictionary
            tableDefDict[tableName] = colDefLst

    return tableDefDict, tableSQLdict