import csv

from orator import DatabaseManager, Model, Schema
from orator.exceptions.orm import ModelNotFound

import re

from config import *

class Part(Model):
    __timestamps__ = False
    __fillable__ = ['name']
    __primary_key__ = 'name'

class Book(Model):
    __timestamps__ = False
    __fillable__ = ['canonical_name']
    __primary_key__ = 'canonical_name'

class AlternativeBookName(Model):
    __timestamps__ = False
    __fillable__ = ['book', 'name']
    __primary_key__ = 'name'

class Verse(Model):
    __timestamps__ = False
    __fillable__ = ['book', 'chapter', 'nr']

    def __repr__(self):
        return self.book + ' ' + str(self.chapter) + ':' + str(self.nr)

    def __str__(self):
        return str(self.get_translated())

    def get_translated(self, translation=None):
        """Get the TranslatedVerse of this Verse and a Translation

        translation -- may be None, a string, or a Translation object
            When None, the default (as in config.py) is used.
        """
        if translation == None:
            translation = PYBLE_SETTINGS['default_translation']
        tv = TranslatedVerse.where(
                {'translation': str(translation),
                    'book': self.book,
                    'chapter': self.chapter,
                    'nr': self.nr }).first()
        return tv

class Translation(Model):
    __timestamps__ = False
    __fillable__ = ['name']
    __primary_key__ = 'name'

    def __str__(self):
        return self.name

class TranslatedVerse(Model):
    __timestamps__ = False
    __table__ = 'translated_verses'
    __fillable__ = ['translation', 'book', 'chapter', 'nr', 'text']

    def __repr__(self):
        return book + ' ' + str(chapter) + ' ' + str(nr) + ' in ' + translation

    def __str__(self):
        return self.text

class Passage(Model):
    __timestamps__ = False
    __fillable__ = ['fst', 'snd']

    def __repr__(self):
        return repr(self.fst) + ' - ' + repr(self.snd)

    def __str__(self):
        return ' '.join([str(v) for v in self.verses()])

    def verses(self):
        """An ordered list of the verses in this passage"""
        vs = []
        book = self.fst.book
        for c in range(self.fst.chapter, self.snd.chapter + 1):
            vs_new = Verse.where({'book': book, 'chapter': c})
            if c == self.fst.chapter:
                vs_new = vs_new.where('nr', '>=', self.fst.nr)
            if c == self.snd.chapter:
                vs_new = vs_new.where('nr', '<=', self.snd.nr)
            vs = vs + list(vs_new.get())
        return vs

    @staticmethod
    def parse(ref):
        """Parse a Bible reference into a Passage object"""
        rgx = re.compile(r"^([\s\w]+?[a-z]\b)(?:\s+"
                         "(\d+)(?::(\d+)(?:\-(\d+)(?::(\d+))?)?)?)?", re.I)
        m = rgx.match(ref)
        book, chap1, verse1, n1, n2 = m.groups()
        chap1 = int(chap1)

        try:
            book = AlternativeBookName.find_or_fail(book).book
        except ModelNotFound:
            return None

        p = Passage()
        if verse1 == None:
            try:
                p.fst = Verse.where(
                        {'book': book,
                            'chapter': chap1,
                            'nr': 1}).first()
                p.snd = Verse.where({'book': book, 'chapter': chap1})\
                        .order_by('nr', 'desc').first()
            except ModelNotFound:
                return None
        else:
            chap1 = int(chap1)
            try:
                p.fst = Verse.where(
                        {'book': book,
                            'chapter': chap1,
                            'nr': verse1}).first()
                if n1 == None:
                    p.snd = p.fst
                elif n2 == None:
                    n1 = int(n1)
                    p.snd = Verse.where(
                            {'book': book,
                                'chapter': chap1,
                                'nr': n1}).first()
                else:
                    n1 = int(n1)
                    n2 = int(n2)
                    p.snd = Verse.where(
                            {'book': book,
                                'chapter': n1,
                                'nr': n2}).first()
            except ModelNotFound:
                return None

        return p

class CrossReference(Model):
    __fillable__ = ['passage_id1', 'passage_id2', 'relevance']

def setup_fill_alternative_book_names(db):
    """Fill the database with alternative book names from a CSV file"""
    with open('alternative_book_names.csv', 'r') as csvfile:
        reader = csv.reader(csvfile, delimiter=',', quotechar='"')
        db.begin_transaction()
        for names in reader:
            canonical_name = names[0]
            Book.first_or_create(canonical_name=canonical_name)
            for name in names:
                AlternativeBookName.first_or_create(
                        book=canonical_name, name=name)
        db.commit()