import csv
from orator import DatabaseManager, Model, Schema

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

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

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

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

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

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

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

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

def setup_tables(db):
    schema = Schema(db)

    with schema.create('parts') as table:
        table.string('name')
        table.primary('name')

    with schema.create('books') as table:
        table.string('canonical_name').primary()

    with schema.create('alternative_book_names') as table:
        table.string('name').primary()
        table.string('book')
        table.foreign('book').references('canonical_name').on('books')

    with schema.create('verses') as table:
        table.string('book')
        table.small_integer('chapter')
        table.small_integer('nr')
        table.foreign('book').references('canonical_name').on('books')
        table.primary(['book', 'chapter', 'nr'])

    with schema.create('translations') as table:
        table.string('name').primary()
    
    with schema.create('translated_verses') as table:
        table.string('translation')
        table.string('book')
        table.small_integer('chapter')
        table.small_integer('nr')
        table.string('text')
        table.foreign('translation').references('name').on('translations')
        table.foreign(['book', 'chapter', 'nr'])\
                .references(['book', 'chapter', 'nr']).on('verses')\
                .on_delete('restrict').on_update('cascade')
        table.primary(['translation', 'book', 'chapter', 'nr'])

    with schema.create('passages') as table:
        table.increments('id')
        table.string('fst_book')
        table.small_integer('fst_chapter')
        table.small_integer('fst_nr')
        table.string('snd_book')
        table.small_integer('snd_chapter')
        table.small_integer('snd_nr')
        table.foreign(['fst_book', 'fst_chapter', 'fst_nr'])\
                .references(['book', 'chapter', 'nr']).on('verses')\
                .on_delete('restrict').on_update('cascade')
        table.foreign(['snd_book', 'snd_chapter', 'snd_nr'])\
                .references(['book', 'chapter', 'nr']).on('verses')\
                .on_delete('restrict').on_update('cascade')
        table.unique(['fst_book', 'fst_chapter', 'fst_nr',
            'snd_book', 'snd_chapter', 'snd_nr'])

    with schema.create('crossreferences') as table:
        table.integer('passage_id1')
        table.integer('passage_id2')
        table.timestamps()
        table.small_integer('relevance').default(0)
        table.foreign('passage_id1').references('id').on('passages')
        table.foreign('passage_id2').references('id').on('passages')
        table.primary(['passage_id1', 'passage_id2'])

def setup_fill_alternative_book_names(db):
    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()