# -*- coding: utf-8 -*-
import json
import os
import pytest
from StringIO import StringIO

from django.core.wsgi import get_wsgi_application
from webtest import TestApp
from django.contrib.auth.models import User
from django.core.files import File
from django.core.urlresolvers import reverse
from django.contrib.contenttypes.models import ContentType
from django.test import Client

from passerelle.base.models import ApiUser, AccessRight
from test_manager import login, admin_user

data = """121;69981;DELANOUE;Eliot;H
525;6;DANIEL WILLIAMS;Shanone;F
253;67742;MARTIN;Sandra;F
511;38142;MARCHETTI;Lucie;F
235;22;MARTIN;Sandra;F
620;52156;ARNAUD;Mathis;H
902;36;BRIGAND;Coline;F
2179;48548;THEBAULT;Salima;F
3420;46;WILSON-LUZAYADIO;Anaëlle;F
1421;55486;WONE;Fadouma;F
2841;51;FIDJI;Zakia;F
2431;59;BELICARD;Sacha;H
4273;60;GOUBERT;Adrien;H
4049;64;MOVSESSIAN;Dimitri;H
4605;67;ABDOU BACAR;Kyle;H
4135;22231;SAVERIAS;Marius;H
4809;75;COROLLER;Maelys;F
5427;117;KANTE;Aliou;H
116642;118;ZAHMOUM;Yaniss;H
216352;38;Dupont;Benoît;H
"""

data_bom = data.decode('utf-8').encode('utf-8-sig')

from csvdatasource.models import CsvDataSource, Query

pytestmark = pytest.mark.django_db

TEST_BASE_DIR = os.path.join(os.path.dirname(__file__), 'data', 'csvdatasource')


def get_file_content(filename):
    return file(os.path.join(TEST_BASE_DIR, filename)).read()

@pytest.fixture
def setup():

    def maker(columns_keynames='fam,id,lname,fname,sex', filename='data.csv', sheet_name='Feuille2', data=''):
        api = ApiUser.objects.create(username='all',
                        keytype='', key='')
        csv = CsvDataSource.objects.create(csv_file=File(StringIO(data), filename), sheet_name=sheet_name,
               columns_keynames=columns_keynames, slug='test', title='a title', description='a description')
        obj_type = ContentType.objects.get_for_model(csv)
        AccessRight.objects.create(codename='can_access', apiuser=api,
                resource_type=obj_type, resource_pk=csv.pk)
        url = reverse('csvdatasource-data', kwargs={'slug': csv.slug})
        return csv, url

    return maker

def parse_response(response):
    return json.loads(response.content)['data']

@pytest.fixture
def client():
    return Client()

@pytest.fixture(params=['data.csv', 'data.ods', 'data.xls', 'data.xlsx'])
def filetype(request):
    return request.param

def test_default_column_keynames(setup, filetype):
    csvdata = CsvDataSource.objects.create(csv_file=File(StringIO(data), filetype),
                                           sheet_name='Feuille2',
                                           slug='test2',
                                           title='a title',
                                           description='a description')
    assert len(csvdata.columns_keynames.split(',')) == 2
    assert 'id' in csvdata.columns_keynames
    assert 'text' in csvdata.columns_keynames

def test_sheet_name_error(setup, filetype, admin_user):
    csvdata, url = setup('field,,another_field,', filename=filetype, data=get_file_content(filetype))
    app = login(TestApp(get_wsgi_application()))
    resp = app.get('/manage/csvdatasource/test/edit')
    edit_form = resp.forms[0]
    edit_form['sheet_name'] = ''
    resp = edit_form.submit()

    if filetype != 'data.csv':
        assert resp.status_code == 200
        assert 'You must specify a sheet name' in resp.body
    else:
        assert resp.status_code == 302

def test_unfiltered_data(client, setup, filetype):
    csvdata, url = setup('field,,another_field,', filename=filetype, data=get_file_content(filetype))
    resp = client.get(url)
    result = parse_response(resp)
    for item in result:
        assert 'field' in item
        assert 'another_field' in item

def test_good_filter_data(client, setup, filetype):
    filter_criteria = 'Zakia'
    csvdata, url = setup(',id,,text,', filename=filetype, data=get_file_content(filetype))
    filters = {'text': filter_criteria}
    resp = client.get(url, filters)
    result = parse_response(resp)
    assert len(result)
    for item in result:
        assert 'id' in item
        assert 'text' in item
        assert filter_criteria in item['text']

def test_bad_filter_data(client, setup, filetype):
    filter_criteria = 'bad'
    csvdata, url = setup(',id,,text,', filename=filetype, data=get_file_content(filetype))
    filters = {'text': filter_criteria}
    resp = client.get(url, filters)
    result = parse_response(resp)
    assert len(result) == 0

def test_useless_filter_data(client, setup, filetype):
    csvdata, url = setup('id,,nom,prenom,sexe', filename=filetype, data=get_file_content(filetype))
    filters = {'text': 'Ali'}
    resp = client.get(url, filters)
    result = parse_response(resp)
    assert len(result) == 20

def test_columns_keynames_with_spaces(client, setup, filetype):
    csvdata, url = setup('id , , nom,text , ', filename=filetype, data=get_file_content(filetype))
    filters = {'text': 'Yaniss'}
    resp = client.get(url, filters)
    result = parse_response(resp)
    assert len(result) == 1

def test_skipped_header_data():
    csv = CsvDataSource.objects.create(csv_file=File(StringIO(get_file_content('data.csv')), 'data.csv'),
                                       columns_keynames=',id,,text,',
                                       skip_header=True)
    result = csv.get_data({'text': 'Eliot'})
    assert len(result) == 0

def test_data(client, setup, filetype):
    csvdata, url = setup('fam,id,, text,sexe ', filename=filetype, data=get_file_content(filetype))
    filters = {'text': 'Sacha'}
    resp = client.get(url, filters)
    result = parse_response(resp)
    assert result[0] == {'id': '59', 'text': 'Sacha',
                         'fam': '2431', 'sexe': 'H'}

def test_unicode_filter_data(client, setup, filetype):
    filter_criteria = u'Benoît'
    csvdata, url = setup(',id,,text,', filename=filetype, data=get_file_content(filetype))
    filters = {'text': filter_criteria}
    resp = client.get(url, filters)
    result = parse_response(resp)
    assert len(result)
    for item in result:
        assert 'id' in item
        assert 'text' in item
        assert filter_criteria in item['text']

def test_unicode_case_insensitive_filter_data(client, setup, filetype):
    csvdata, url = setup(',id,,text,', filename=filetype, data=get_file_content(filetype))
    filter_criteria = u'anaëlle'
    filters = {'text':filter_criteria, 'case-insensitive':''}
    resp = client.get(url, filters)
    result = parse_response(resp)
    assert len(result)
    for item in result:
        assert 'id' in item
        assert 'text' in item
        assert filter_criteria.lower() in item['text'].lower()

def test_data_bom(client, setup):
    csvdata, url = setup('fam,id,, text,sexe ', data=data_bom)
    filters = {'text':'Eliot'}
    resp = client.get(url, filters)
    result = parse_response(resp)
    assert result[0] == {'id': '69981', 'text': 'Eliot', 'fam': '121', 'sexe': 'H'}

def test_multi_filter(client, setup, filetype):
    csvdata, url = setup('fam,id,, text,sexe ', filename=filetype, data=get_file_content(filetype))
    filters = {'sexe':'F'}
    resp = client.get(url, filters)
    result = parse_response(resp)
    assert result[0] == {'id': '6', 'text': 'Shanone',
                         'fam': '525', 'sexe': 'F'}
    assert len(result) == 10

def test_query(client, setup, filetype):
    csvdata, url = setup('fam,id,, text,sexe ', filename=filetype, data=get_file_content(filetype))
    filters =  {'q':'liot'}
    resp = client.get(url, filters)
    result = parse_response(resp)
    assert result[0]['text'] == 'Eliot'
    assert len(result) == 1

def test_query_insensitive(client, setup, filetype):
    csvdata, url = setup('fam,id,, text,sexe ', filename=filetype, data=get_file_content(filetype))
    filters = {'q':'elIo', 'case-insensitive':''}
    resp = client.get(url, filters)
    result = parse_response(resp)
    assert result[0]['text'] == 'Eliot'
    assert len(result) == 1

def test_advanced_filters(client, setup, filetype):
    csvdata, url = setup(filename=filetype, data=get_file_content(filetype))
    filters = {'id__gt':20, 'id__lt': 40}
    resp = client.get(url, filters)
    result = parse_response(resp)
    assert len(result) == 3
    for stuff in result:
        assert stuff['id'] in ('22', '36', '38')

def test_advanced_filters_combo(client, setup, filetype):
    csvdata, url = setup(filename=filetype, data=get_file_content(filetype))
    filters = {
        'id__ge': '20',
        'id__lt': '40',
        'fam__gt': '234',
        'fam__le': '235',
        'fname__icontains': 'Sandra'
    }
    resp = client.get(url, filters)
    result = parse_response(resp)
    assert len(result) == 1
    assert result[0]['id'] == '22'
    assert result[0]['lname'] == 'MARTIN'

def test_unknown_operator(client, setup, filetype):
    csvdata, url = setup(filename=filetype, data=get_file_content(filetype))
    filters = {'id__whatever': '25', 'fname__icontains':'Eliot'}
    resp = client.get(url, filters)
    result = json.loads(resp.content)
    assert result['err'] == 1
    assert result['err_class'] == 'csvdatasource.lookups.InvalidOperatorError'
    assert result['err_desc'] == 'whatever is not a valid operator'


def test_dialect(client, setup):
    csvdata, url = setup(data=data)
    expected = {
        'lineterminator': '\r\n',
        'skipinitialspace': False,
        'quoting': 0,
        'delimiter': ';',
        'quotechar': '"',
        'doublequote': False
    }

    assert expected == csvdata.dialect_options
    filters = {'id__gt': '20', 'id__lt': '40', 'fname__icontains': 'Sandra'}
    resp = client.get(url, filters)
    result = parse_response(resp)
    assert len(result) == 1
    assert result[0]['id'] == '22'
    assert result[0]['lname'] == 'MARTIN'

def test_on_the_fly_dialect_detection(client, setup):
    # fake a connector that was not initialized during .save(), because it's
    # been migrated and we didn't do dialect detection at save() time.
    csvdata, url = setup(data=data)
    CsvDataSource.objects.all().update(_dialect_options=None)
    resp = client.get(url)
    result = json.loads(resp.content)
    assert result['err'] == 0
    assert len(result['data']) == 20


def test_query_array(app, setup, filetype):
    csvdata, url = setup('id,whatever,nom,prenom,sexe', filename=filetype, data=get_file_content(filetype))
    url = reverse('generic-endpoint', kwargs={
        'connector': 'csvdatasource',
        'slug': csvdata.slug,
        'endpoint': 'query/query-1_/',
    })
    query = Query(slug='query-1_', resource=csvdata, structure='array')
    query.projections = '\n'.join(['id:int(id)', 'prenom:prenom'])
    query.save()
    response = app.get(url)
    assert response.json['err'] == 0
    assert len(response.json['data'])
    for row in response.json['data']:
        assert len(row) == 2
        assert isinstance(row[0], int)
        assert isinstance(row[1], unicode)


def test_query_q_filter(app, setup, filetype):
    csvdata, url = setup('id,whatever,nom,prenom,sexe', filename=filetype,
                         data=get_file_content(filetype))
    url = reverse('generic-endpoint', kwargs={
        'connector': 'csvdatasource',
        'slug': csvdata.slug,
        'endpoint': 'query/query-1_/',
    })
    query = Query(slug='query-1_', resource=csvdata, structure='array')
    query.projections = '\n'.join(['id:int(id)', 'text:prenom'])
    query.save()
    response = app.get(url + '?q=Sandra')
    assert response.json['err'] == 0
    assert len(response.json['data']) == 2
    response = app.get(url + '?q=sandra')
    assert response.json['err'] == 0
    assert len(response.json['data']) == 0
    response = app.get(url + '?q=sandra&case-insensitive')
    assert response.json['err'] == 0
    assert len(response.json['data']) == 2


def test_query_dict(app, setup, filetype):
    csvdata, url = setup('id,whatever,nom,prenom,sexe', filename=filetype, data=get_file_content(filetype))
    url = reverse('generic-endpoint', kwargs={
        'connector': 'csvdatasource',
        'slug': csvdata.slug,
        'endpoint': 'query/query-1_/',
    })
    query = Query(slug='query-1_', resource=csvdata, structure='dict')
    query.projections = '\n'.join(['id:int(id)', 'prenom:prenom'])
    query.save()
    response = app.get(url)
    assert response.json['err'] == 0
    assert len(response.json['data'])
    for row in response.json['data']:
        assert len(row) == 2
        assert isinstance(row['id'], int)
        assert isinstance(row['prenom'], unicode)


def test_query_tuples(app, setup, filetype):
    csvdata, url = setup('id,whatever,nom,prenom,sexe', filename=filetype, data=get_file_content(filetype))
    url = reverse('generic-endpoint', kwargs={
        'connector': 'csvdatasource',
        'slug': csvdata.slug,
        'endpoint': 'query/query-1_/',
    })
    query = Query(slug='query-1_', resource=csvdata, structure='tuples')
    query.projections = '\n'.join(['id:int(id)', 'prenom:prenom'])
    query.save()
    response = app.get(url)
    assert response.json['err'] == 0
    assert len(response.json['data'])
    for row in response.json['data']:
        assert len(row) == 2
        assert row[0][0] == 'id'
        assert isinstance(row[0][1], int)
        assert row[1][0] == 'prenom'
        assert isinstance(row[1][1], unicode)


def test_query_onerow(app, setup, filetype):
    csvdata, url = setup('id,whatever,nom,prenom,sexe', filename=filetype, data=get_file_content(filetype))
    url = reverse('generic-endpoint', kwargs={
        'connector': 'csvdatasource',
        'slug': csvdata.slug,
        'endpoint': 'query/query-1_/',
    })
    query = Query(slug='query-1_', resource=csvdata, structure='onerow')
    query.projections = '\n'.join(['id:int(id)', 'prenom:prenom'])
    query.filters = 'int(id) == 525'
    query.save()
    response = app.get(url)
    assert response.json['err'] == 0
    assert isinstance(response.json['data'], dict)
    assert response.json['data']['prenom'] == 'Shanone'


def test_query_one(app, setup, filetype):
    csvdata, url = setup('id,whatever,nom,prenom,sexe', filename=filetype, data=get_file_content(filetype))
    url = reverse('generic-endpoint', kwargs={
        'connector': 'csvdatasource',
        'slug': csvdata.slug,
        'endpoint': 'query/query-1_/',
    })
    query = Query(slug='query-1_', resource=csvdata, structure='one')
    query.projections = 'wtf:prenom'
    query.filters = 'int(id) == 525'
    query.save()
    response = app.get(url)
    assert response.json['err'] == 0
    assert response.json['data'] == 'Shanone'


def test_query_filter_param(app, setup, filetype):
    csvdata, url = setup('id,whatever,nom,prenom,sexe', filename=filetype, data=get_file_content(filetype))
    url = reverse('generic-endpoint', kwargs={
        'connector': 'csvdatasource',
        'slug': csvdata.slug,
        'endpoint': 'query/query-1_/',
    })
    query = Query(slug='query-1_', resource=csvdata, structure='one')
    query.projections = 'wtf:prenom'
    query.filters = 'int(id) == int(query.get("foobar"))'
    query.save()
    response = app.get(url + '?foobar=525')
    assert response.json['err'] == 0
    assert response.json['data'] == 'Shanone'


def test_query_distinct(app, setup, filetype):
    csvdata, url = setup('id,whatever,nom,prenom,sexe', filename=filetype, data=get_file_content(filetype))
    url = reverse('generic-endpoint', kwargs={
        'connector': 'csvdatasource',
        'slug': csvdata.slug,
        'endpoint': 'query/query-1_/',
    })
    query = Query(slug='query-1_', resource=csvdata, distinct='sexe')
    query.save()
    response = app.get(url)
    assert response.json['err'] == 0
    assert isinstance(response.json['data'], list)
    assert len(response.json['data']) == 2


def test_query_order(app, setup, filetype):
    csvdata, url = setup('id,whatever,nom,prenom,sexe', filename=filetype, data=get_file_content(filetype))
    url = reverse('generic-endpoint', kwargs={
        'connector': 'csvdatasource',
        'slug': csvdata.slug,
        'endpoint': 'query/query-1_/',
    })
    query = Query(slug='query-1_', resource=csvdata, order='prenom.lower()')
    query.save()
    response = app.get(url)
    assert response.json['err'] == 0
    assert isinstance(response.json['data'], list)
    assert response.json['data'] == sorted(response.json['data'], key=lambda row:
                                           row['prenom'].lower())


def test_query_error(app, setup, filetype):
    csvdata, url = setup('id,whatever,nom,prenom,sexe', filename=filetype, data=get_file_content(filetype))
    url = reverse('generic-endpoint', kwargs={
        'connector': 'csvdatasource',
        'slug': csvdata.slug,
        'endpoint': 'query/query-1_/',
    })

    response = app.get(url)
    assert response.json['err'] == 1
    assert response.json['err_desc'] == 'no such query'

    query = Query(slug='query-1_', resource=csvdata)
    query.save()

    query.projections = '1a:prenom'
    query.save()
    response = app.get(url)
    assert response.json['err'] == 1
    assert response.json['err_desc'] == 'invalid projection name'
    assert response.json['data'] == '1a'

    query.projections = 'id:prenom.'
    query.save()
    response = app.get(url)
    assert response.json['err'] == 1
    assert response.json['err_desc'] == 'invalid projection expression'
    assert response.json['data']['name'] == 'id'
    assert response.json['data']['expr'] == 'prenom.'

    query.projections = 'id:prenom\0'
    query.save()
    response = app.get(url)
    assert response.json['err'] == 1
    assert response.json['err_desc'] == 'invalid projection expression'
    assert response.json['data']['name'] == 'id'
    assert response.json['data']['expr'] == 'prenom\0'

    query.projections = 'id:zob'
    query.save()
    response = app.get(url)
    assert response.json['err'] == 1
    assert response.json['err_desc'] == 'invalid projection expression'
    assert response.json['data']['name'] == 'id'
    assert response.json['data']['expr'] == 'zob'
    assert 'row' in response.json['data']

    query.projections = ''
    query.filters = 'prenom.'
    query.save()
    response = app.get(url)
    assert response.json['err'] == 1
    assert response.json['err_desc'] == 'invalid filters expression'
    assert response.json['data']['expr'] == 'prenom.'

    query.filters = 'zob'
    query.save()
    response = app.get(url)
    assert response.json['err'] == 1
    assert response.json['err_desc'] == 'invalid filters expression'
    assert response.json['data']['expr'] == 'zob'
    assert 'row' in response.json['data']

    query.filters = ''
    query.order = 'prenom.'
    query.save()
    response = app.get(url)
    assert response.json['err'] == 1
    assert response.json['err_desc'] == 'invalid order expression'
    assert response.json['data']['expr'] == 'prenom.'

    query.order = 'zob'
    query.save()
    response = app.get(url)
    assert response.json['err'] == 1
    assert response.json['err_desc'] == 'invalid order expression'
    assert response.json['data']['expr'] == 'zob'
    assert 'row' in response.json['data']

    query.order = ''
    query.distinct = 'prenom.'
    query.save()
    response = app.get(url)
    assert response.json['err'] == 1
    assert response.json['err_desc'] == 'invalid distinct expression'
    assert response.json['data']['expr'] == 'prenom.'

    query.distinct = 'zob'
    query.save()
    response = app.get(url)
    assert response.json['err'] == 1
    assert response.json['err_desc'] == 'invalid distinct expression'
    assert response.json['data']['expr'] == 'zob'
    assert 'row' in response.json['data']

def test_edit_connector_queries(admin_user, app, setup, filetype):
    csvdata, url = setup('id,whatever,nom,prenom,sexe', filename=filetype, data=get_file_content(filetype))
    url = reverse('view-connector', kwargs={'connector': 'csvdatasource', 'slug': csvdata.slug})
    resp = app.get(url)
    assert not 'New Query' in resp.body
    resp = app.get(reverse('csv-new-query', kwargs={'connector_slug': csvdata.slug}),
        status=302)
    assert resp.location.startswith('http://localhost:80/login/')

    app = login(app)
    resp = app.get(url)
    resp = resp.click('New Query')
    resp.form['slug'] = 'foobar'
    resp.form['label'] = 'Lucy alone'
    resp.form['filters'] = 'int(id) =< 525'
    resp = resp.form.submit()
    assert 'Syntax error' in resp.body
    resp.form['filters'] = 'int(id) == 525'
    resp.form['projections'] = 'id'
    resp = resp.form.submit()
    assert 'Syntax error' in resp.body
    resp.form['projections'] = 'id:id\nprenom:prenom'
    resp = resp.form.submit().follow()
    resp = resp.click('foobar', index=1) # 0th is the newly created endpoint
    resp.form['filters'] = 'int(id) == 511'
    resp.form['description'] = 'in the sky without diamonds'
    resp = resp.form.submit().follow()
    assert 'Lucy alone' in resp.body
    assert 'in the sky without diamonds' in resp.body
    resp = resp.click('foobar', index=0) # 0th is the newly created endpoint
    assert len(resp.json['data']) == 1
    assert resp.json['data'][0]['prenom'] == 'Lucie'

def test_download_file(setup, filetype, admin_user):
    csvdata, url = setup('field,,another_field,', filename=filetype, data=get_file_content(filetype))
    app = TestApp(get_wsgi_application())
    assert '/login' in app.get('/manage/csvdatasource/test/download/').location
    app = login(TestApp(get_wsgi_application()))
    resp = app.get('/manage/csvdatasource/test/download/', status=200)
    if filetype == 'data.csv':
        assert resp.headers['Content-Type'] == 'text/csv; charset=utf-8'
        assert resp.headers['Content-Length'] == str(len(data))
    elif filetype == 'data.ods':
        assert resp.headers['Content-Type'] == 'application/vnd.oasis.opendocument.spreadsheet'
    elif filetype == 'data.xls':
        assert resp.headers['Content-Type'] == 'application/vnd.ms-excel'
    elif filetype == 'data.xlsx':
        assert resp.headers['Content-Type'] == 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'


def test_query_filter_multiline(app, setup, filetype):
    csvdata, url = setup('id,whatever,nom,prenom,sexe', filename=filetype,
                         data=get_file_content(filetype))
    url = reverse('generic-endpoint', kwargs={
        'connector': 'csvdatasource',
        'slug': csvdata.slug,
        'endpoint': 'query/query-1_/',
    })
    query = Query(slug='query-1_', resource=csvdata)
    query.filters = '\n'.join(['int(id) <= 525', 'int(id) >= 511'])
    query.save()
    response = app.get(url)
    assert response.json['err'] == 0
    assert len(response.json['data']) == 2
