# passerelle - uniform access to multiple data sources and services
# Copyright (C) 2016 Entr'ouvert
#
# This program is free software: you can redistribute it and/or modify it
# under the terms of the GNU Affero General Public License as published
# by the Free Software Foundation, either version 3 of the License, or
# (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU Affero General Public License for more details.
#
# You should have received a copy of the GNU Affero General Public License
# along with this program.  If not, see <http://www.gnu.org/licenses/>.

import re
import csv
import itertools
from collections import OrderedDict
from pyexcel_ods import get_data as get_data_ods
from pyexcel_xls import get_data as get_data_xls

import jsonfield

from django.utils.encoding import smart_text
from django.db import models
from django.core.exceptions import ValidationError
from django.shortcuts import get_object_or_404
from django.utils.translation import ugettext_lazy as _

from passerelle.base.models import BaseResource
from passerelle.utils.jsonresponse import APIError
from passerelle.utils.api import endpoint

import lookups

identifier_re = re.compile(r"^[^\d\W]\w*\Z", re.UNICODE)


code_cache = OrderedDict()


def get_code(expr):
    # limit size of code cache to 1024
    if len(code_cache) > 1024:
        for key in code_cache.keys()[:len(code_cache) - 1024]:
            code_cache.pop(key)
    if expr not in code_cache:
        code_cache[expr] = compile(expr, '<inline>', 'eval')
    return code_cache[expr]


class Query(models.Model):
    resource = models.ForeignKey('CsvDataSource')
    slug = models.SlugField(_('Name (slug)'))
    label = models.CharField(_('Label'), max_length=100)
    description = models.TextField(_('Description'), blank=True)
    filters = models.TextField(_('Filters'), blank=True,
            help_text=_('List of filter clauses (Python expression)'))
    projections = models.TextField(_('Projections'), blank=True,
            help_text=_('List of projections (name:expression)'))
    order = models.TextField(_('Order'), blank=True,
            help_text=_('Ordering columns'))
    distinct = models.TextField(_('Distinct'), blank=True,
            help_text=_('Distinct columns'))
    structure = models.CharField(_('Structure'),
            max_length=20,
            choices=[
                ('array', _('Array')),
                ('dict', _('Dictionary')),
                ('tuples', _('Tuples')),
                ('onerow', _('Single Row')),
                ('one', _('Single Value'))],
            default='dict',
            help_text=_('Data structure used for the response'))

    class Meta:
        ordering = ['slug']

    def get_list(self, attribute):
        if not getattr(self, attribute):
            return []
        return getattr(self, attribute).strip().splitlines()


class CsvDataSource(BaseResource):
    csv_file = models.FileField(_('CSV File'), upload_to='csv')
    columns_keynames = models.CharField(
        max_length=256,
        verbose_name=_('Column keynames'),
        default='id, text',
        help_text=_('ex: id,text,data1,data2'), blank=True)
    skip_header = models.BooleanField(_('Skip first line'), default=False)
    _dialect_options = jsonfield.JSONField(editable=False, null=True)
    sheet_name = models.CharField(_('Sheet name'), blank=True, max_length=150)

    category = _('Data Sources')

    class Meta:
        verbose_name = _('Spreadsheet File')

    def clean(self, *args, **kwargs):
        file_type = self.csv_file.name.split('.')[-1]
        if file_type in ('ods', 'xls', 'xlsx') and not self.sheet_name:
            raise ValidationError(_('You must specify a sheet name'))
        return super(CsvDataSource, self).clean(*args, **kwargs)

    def save(self, *args, **kwargs):
        file_type = self.csv_file.name.split('.')[-1]
        if file_type not in ('ods', 'xls', 'xlsx'):
            content = self.get_content_without_bom()
            dialect = csv.Sniffer().sniff(content)
            self.dialect_options = {
                k: v for k, v in vars(dialect).items() if not k.startswith('_')
            }
        return super(CsvDataSource, self).save(*args, **kwargs)

    @property
    def dialect_options(self):
        """turn dict items into string
        """
        # Set dialect_options if None
        if self._dialect_options is None:
            self.save()

        options = {}
        for k, v in self._dialect_options.items():
            if isinstance(v, unicode):
                v = v.encode('ascii')
            options[k.encode('ascii')] = v

        return options

    @dialect_options.setter
    def dialect_options(self, value):
        self._dialect_options = value

    @classmethod
    def get_verbose_name(cls):
        return cls._meta.verbose_name

    @classmethod
    def get_icon_class(cls):
        return 'grid'

    @classmethod
    def is_enabled(cls):
        return True

    def get_content_without_bom(self):
        self.csv_file.seek(0)
        content = self.csv_file.read()
        return content.decode('utf-8-sig', 'ignore').encode('utf-8')

    def get_rows(self):
        file_type = self.csv_file.name.split('.')[-1]
        if file_type not in ('ods', 'xls', 'xlsx'):
            content = self.get_content_without_bom()
            reader = csv.reader(content.splitlines(), **self.dialect_options)
            rows = list(reader)

        else:
            if file_type == 'ods':
                content = get_data_ods(self.csv_file)

            elif file_type == 'xls' or file_type == 'xlsx':
                content = get_data_xls(self.csv_file.path)

            if self.sheet_name not in content:
                return []

            rows = content[self.sheet_name]

        if not rows:
            return []

        if self.skip_header:
            rows = rows[1:]

        return [[smart_text(x) for x in y] for y in rows]

    def get_data(self, filters=None):
        titles = [t.strip() for t in self.columns_keynames.split(',')]
        indexes = [titles.index(t) for t in titles if t]
        caption = [titles[i] for i in indexes]

        # validate filters (appropriate columns must exist)
        if filters:
            for filter_key in filters.keys():
                if not filter_key.split(lookups.DELIMITER)[0] in titles:
                    del filters[filter_key]

        rows = self.get_rows()

        data = []

        # build a generator of all filters
        def filters_generator(filters, titles):
            if not filters:
                return
            for key, value in filters.items():
                try:
                    key, op = key.split(lookups.DELIMITER)
                except (ValueError,):
                    op = 'eq'

                index = titles.index(key)

                yield lookups.get_lookup(op, index, value)

        # apply filters to data
        def super_filter(filters, data):
            for f in filters:
                data = itertools.ifilter(f, data)
            return data

        matches = super_filter(
            filters_generator(filters, titles), rows
        )

        for row in matches:
            line = []
            for i in indexes:
                try:
                    line.append(row[i])
                except IndexError:
                    line.append('')

            data.append(dict(zip(caption, line)))

        return data

    @property
    def titles(self):
        return [smart_text(t.strip()) for t in self.columns_keynames.split(',')]

    @endpoint('json-api', perm='can_access', methods=['get'],
              name='query', pattern='^(?P<query_name>[\w-]+)/$')
    def select(self, request, query_name, **kwargs):
        try:
            query = Query.objects.get(resource=self.id, slug=query_name)
        except Query.DoesNotExist:
            raise APIError(u'no such query')

        titles = self.titles
        rows = self.get_rows()
        data = [dict(zip(titles, line)) for line in rows]

        def stream_expressions(expressions, data, kind, titles=None):
            codes = []
            for i, expr in enumerate(expressions):
                try:
                    code = get_code(expr)
                except (TypeError, SyntaxError) as e:
                    data = {
                        'expr': expr,
                        'error': unicode(e)
                    }
                    if titles:
                        data['name'] = titles[i]
                    else:
                        data['idx'] = i
                    raise APIError(u'invalid %s expression' % kind, data=data)
                codes.append((code, expr))
            for row in data:
                new_row = []
                row_vars = dict(row)
                row_vars['query'] = kwargs
                for i, (code, expr) in enumerate(codes):
                    try:
                        result = eval(code, {}, row_vars)
                    except Exception as e:
                        data = {
                            'expr': expr,
                            'row': repr(row),
                        }
                        if titles:
                            data['name'] = titles[i]
                        else:
                            data['idx'] = i
                        raise APIError(u'invalid %s expression' % kind, data=data)
                    new_row.append(result)
                yield new_row, row

        filters = query.get_list('filters')

        if filters:
            data = [row for new_row, row in stream_expressions(filters, data, kind='filters')
                    if all(new_row)]

        order = query.get_list('order')
        if order:
            generator = stream_expressions(order, data, kind='order')
            new_data = [(tuple(new_row), row) for new_row, row in generator]
            new_data.sort(key=lambda (k, row): k)
            data = [row for key, row in new_data]

        distinct = query.get_list('distinct')
        if distinct:
            generator = stream_expressions(distinct, data, kind='distinct')
            seen = set()
            new_data = []
            for new_row, row in generator:
                new_row = tuple(new_row)
                try:
                    hash(new_row)
                except TypeError:
                    raise APIError(u'distinct value is unhashable',
                                   data={
                                       'row': repr(row),
                                       'distinct': repr(new_row),
                                   })
                if new_row in seen:
                    continue
                new_data.append(row)
                seen.add(new_row)
            data = new_data

        projection = query.get_list('projections')
        if projection:
            expressions = []
            titles = []
            for mapping in projection:
                name, expr = mapping.split(':', 1)
                if not identifier_re.match(name):
                    raise APIError(u'invalid projection name', data=name)
                titles.append(name)
                expressions.append(expr)
            new_data = []
            for new_row, row in stream_expressions(expressions, data, kind='projection',
                                                   titles=titles):
                new_data.append(dict(zip(titles, new_row)))
            data = new_data

        # allow jsonp queries by select2
        # filtering is done there afater projection because we need a projection named text for
        # retro-compatibility with previous use of the csvdatasource with select2
        if 'q' in request.GET:
            if 'case-insensitive' in request.GET:
                filters = ["query['q'].lower() in text.lower()"]
            else:
                filters = ["query['q'] in text"]
            data = [row for new_row, row in stream_expressions(filters, data, kind='filters')
                    if new_row[0]]

        if query.structure == 'array':
            return [[row[t] for t in titles] for row in data]
        elif query.structure == 'dict':
            return data
        elif query.structure == 'tuples':
            return [[[t, row[t]] for t in titles] for row in data]
        elif query.structure == 'onerow':
            if len(data) != 1:
                raise APIError('more or less than one row', data=data)
            return data[0]
        elif query.structure == 'one':
            if len(data) != 1:
                raise APIError('more or less than one row', data=data)
            if len(data[0]) != 1:
                raise APIError('more or less than one column', data=data)
            return data[0].values()[0]
