from django.db import models

from sqlalchemy import create_engine
from sqlalchemy.sql import text, bindparam
from sqlalchemy.pool import NullPool

from passerelle.repost.models import BaseRepost
from passerelle.utils import render_template_vars

class BindingError(Exception):
    pass

class SQLFormatError(Exception):
    pass

class SQLRepost(BaseRepost):
    engine_options = models.CharField(max_length = 64,
                                      help_text = u'a pythonic({\'key\': \'value\'} form) dictionnary that will be passed to the database driver')

    parameters = '*'

    def repost(self, data, **kwargs):
        options = eval(render_template_vars(self.engine_options))
        engine = create_engine(render_template_vars(self.url),
                               poolclass=NullPool, **options)
        bindparams = []
        for binding in self.bindings.all():
            try:
                param = bindparam(binding.name, value = binding.value(kwargs))
                bindparams.append(param)
            except:
                raise BindingError('Binding params error')

        response = {}
        statements = self.statements.all()

        # return only the result array with no keyword
        if statements.count() == 1 and not statements[0].output_var:
            statement = statements[0]
            sql = statement.sql(bindparams)
            result = engine.execute(sql)
            return statement.convert_rows(result)

        for index, statement in enumerate(statements):
            sql = statement.sql(bindparams)
            result = engine.execute(sql)
            section = statement.output_var or index
            if result.returns_rows:
                response[section] = statement.convert_rows(result)
            else:
                response[section] = None
        return response

class SQLStatement(models.Model):
    FIRST_ROW_TO_DICT = 0
    ALL_ROWS_TO_DICT = 1
    TABLE_WITH_HEADER = 2

    MAPPING_CHOICES = (
        (FIRST_ROW_TO_DICT, 'first row to dict'),
        (ALL_ROWS_TO_DICT, 'all rows to dict'),
        (TABLE_WITH_HEADER, 'table with header as first row'),
    )
    sqlrepost = models.ForeignKey('SQLRepost', related_name = 'statements')
    statement = models.TextField()
    output_var = models.CharField(max_length = 64, null = True, blank = True)
    mapping = models.IntegerField(choices = MAPPING_CHOICES)

    class Meta:
        ordering = ('id',)

    def sql(self, bindings):
        try:
            return text(self.statement, bindparams = bindings)
        except:
            raise SQLFormatError('Error while formatting the SQL request')

    def convert_rows(self, rows):
        if self.mapping == SQLStatement.FIRST_ROW_TO_DICT:
            first = rows.fetchone()
            if first:
                return dict(zip(rows.keys(), first))
            else:
                return None
        elif self.mapping == SQLStatement.ALL_ROWS_TO_DICT:
            keys = rows.keys()
            return [dict(zip(keys, row)) for row in rows.fetchall()]
        elif self.mapping == SQLStatement.TABLE_WITH_HEADER:
            result = [rows.keys()]
            result.extend(map(lambda r: list(r), rows.fetchall()))
            return result
        else:
            raise NotImplementedError('unknown mapping value %d' % self.mapping)

class SQLBinding(models.Model):
    sqlreport = models.ForeignKey('SQLRepost', related_name = 'bindings')
    name = models.CharField(max_length = 32)
    expression = models.TextField()

    class Meta:
        ordering = ('id', )

    def value(self, data):
        return self.expression.format(**data)
