#!/usr/bin/env python3
import click

from orator import DatabaseManager, Model
import orator

from pyble import *
import config

import re
import time

def setup_database():
    """Setup a connection with the database"""
    db = DatabaseManager(config.DATABASES)
    Model.set_connection_resolver(db)
    return db

@click.group()
def cli():
    """Wrapper for CLI commands"""
    pass

@cli.command()
def init():
    """Initialise an SQLite database in filename, and create tables"""
    click.echo('Initialising pyble database...')
    db = setup_database()
    click.echo('Inserting common book names...')
    setup_fill_alternative_book_names(db)
    click.echo('Done.')

def load_biblehub_line(columns, line):
    """Split a line from a Bible Hub dump into a usable dictionary
    
    Arguments:
    columns -- the columns from the first line of the dump
    line -- the line to read
    """
    pieces = line.split('\t')
    ref = pieces[0]
    refm = re.search('([\w ]+?)\s*(\d+):(\d+)', ref)
    try:
        parsed = { 'book' : refm.group(1),
                'chapter' : int(refm.group(2)),
                'verse' : int(refm.group(3)),
                'translations' : {} }
        for i, col in enumerate(columns[1:]):
            parsed['translations'][col] = pieces[i + 1].strip()
        return parsed
    except AttributeError:
        print('Something wrong with your regex.\n', str(pieces))

def load_biblehub(db, f):
    """Load a Bible Hub dump into the database"""
    content = f.read().splitlines()
    columns = content[0]
    columns, verses = columns.split('\t'), content[1:]

    for translation in columns[1:]:
        Translation.first_or_create(name=translation)

    db.begin_transaction()
    conn = db.connection().get_connection()

    cur_book = None
    for v in verses:
        v = load_biblehub_line(columns, v)

        if cur_book != v['book']:
            if cur_book != None:
                click.echo(str(round(time.time() - timer, 3)) + 's')
            timer = time.time()
            
            click.echo(v['book'] + '...', nl=False)
            cur_book = v['book']

            book = Book.first_or_create(canonical_name=cur_book)

        # We don't use Orator here to make things ~4x faster
        conn.execute('INSERT OR IGNORE INTO verses (book, chapter, nr) '
                'VALUES (?, ?, ?)', (v['book'], v['chapter'], v['verse']))
        
        for translation, text in v['translations'].items():
            text = text.strip()
            if text != '':
                conn.execute('INSERT OR IGNORE INTO translated_verses '
                        '(translation, book, chapter, nr, text) '
                        'VALUES (?, ?, ?, ?, ?)',
                        (translation, cur_book, v['chapter'], v['verse'], text))

    click.echo(str(round(time.time() - timer, 3)) + 's')
    db.commit()

@cli.command()
@click.option('--type', required=True)
@click.argument('filename', type=click.Path())
def load(type, filename):
    """Load external data into the database"""
    db = setup_database()
    if type == 'biblehub':
        with open(filename) as f:
            load_biblehub(db, f)
    else:
        click.secho('I don\'t know a type ' + type, fg='red')

if __name__ == '__main__':
    cli()