from django.db import connection
from django.http import HttpResponseRedirect, JsonResponse
from django.shortcuts import render
from django.views.decorators.clickjacking import xframe_options_sameorigin

from blog import models
from blog.controller import article

json_str = '''{
  "results": {
    "category1": {
      "name": "Category 1",
      "results": [
        {
          "title": "Result Title",
          "url": "/optional/url/on/click",
          "image": "optional-image.jpg",
          "price": "Optional Price",
          "description": "Optional Description"
        },
        {
          "title": "Result Title",
          "url": "/optional/url/on/click",
          "image": "optional-image.jpg",
          "price": "Optional Price",
          "description": "Optional Description"
        }
      ]
    },
    "category2": {
      "name": "Category 2",
      "results": [
        {
          "title": "Result Title",
          "url": "/optional/url/on/click",
          "image": "optional-image.jpg",
          "price": "Optional Price",
          "description": "Optional Description"
        }
      ]
    }
  },
  // optional action below results
  "action": {
    "url": '/path/to/results',
    "text": "View all 202 results"
  }
}'''


def search(request):
    if request.method == 'GET':
        keywords = request.GET.get('keywords')
        if keywords is not None:
            search_dict = dict()
            search_dict['title__contains'] = keywords
            search_dict['status'] = 1

            result = models.Article.objects.filter(**search_dict).values('id', 'intro', 'title', 'category__name')
            result_list = {}

            for item in result:
                if result_list.get(item['category__name']) == None:
                    result_list[item['category__name']] = {}
                    result_list[item['category__name']]['title'] = item['category__name']
                    result_list[item['category__name']]['results'] = [
                        {
                            'title': item['title'],
                            'description': item['intro'],
                            'url': '/article/{0}.html'.format(item['id'])
                        }
                    ]
                else:
                    result_list[item['category__name']]['results'].append({
                        'title': item['title'],
                        'description': item['intro'],
                        'url': '/article/{0}.html'.format(item['id'])
                    })
            return JsonResponse({'results':result_list}, safe=False)
        else:
            return JsonResponse({'results': []}, safe=False)
    else:
        return JsonResponse({"success": False, "message": "FUCK YOU"}, safe=False)


def current_record(request, date):
    page = request.GET.get('page')
    if page is None:
        page = 0
    result = get_record_and_tags()
    context = {'current_category': date,
               'articles': article.get_article(is_paginator=True, page=int(page), date_record=date),
               'records': result['records'],
               'tags': result['tags']
               }

    return render(request, 'category.html', context=context)


def avatar(request, pk):
    sql = '''select avatar from auth_user au left join avatar_avatar aa on au.id = aa.user_id where au.id = {0}'''.format(
        pk)
    cursor = connection.cursor()
    cursor.execute(sql)
    fetchall = cursor.fetchall()
    return HttpResponseRedirect('/' + fetchall[0][0])  # 跳转到主界面


# Create your views here.

@xframe_options_sameorigin
def index(request):
    article_sql = '''SELECT count(*) as count,strftime('%Y-%m',created_time) as datetime FROM blog_article group by strftime('%Y,%m',created_time)'''
    tag_sql = '''SELECT bt.id,bt.name,count() as count FROM blog_tags bt  left join blog_article_tags bat on bt.id = bat.tags_id group by bt.name
order by count desc limit 10'''
    cursor = connection.cursor()
    cursor.execute(article_sql)
    fetchall = cursor.fetchall()

    records = []
    for obj in fetchall:
        dic = {}
        dic['count'] = obj[0]
        dic['datetime'] = obj[1]
        records.append(dic)
    cursor.execute(tag_sql)
    fetchall = cursor.fetchall()
    tags = []
    for obj in fetchall:
        dic = {}
        dic['id'] = obj[0]
        dic['name'] = obj[1]
        dic['count'] = obj[2]
        tags.append(dic)
    context = {
        'articles_new': article.get_article(20),
        'records': records,
        'tags': tags
    }
    return render(request, 'index.html', context=context)


def get_record_and_tags():
    article_sql = '''SELECT count(*) as count,strftime('%Y-%m',created_time) as datetime FROM blog_article group by strftime('%Y,%m',created_time) order by created_time desc'''
    tag_sql = '''SELECT bt.id,bt.name,count() as count FROM blog_tags bt  left join blog_article_tags bat on bt.id = bat.tags_id group by bt.name
    order by count desc limit 10'''
    cursor = connection.cursor()
    cursor.execute(article_sql)
    fetchall = cursor.fetchall()

    records = []
    for obj in fetchall:
        dic = {}
        dic['count'] = obj[0]
        dic['datetime'] = obj[1]
        records.append(dic)
    cursor.execute(tag_sql)
    fetchall = cursor.fetchall()
    tags = []
    for obj in fetchall:
        dic = {}
        dic['id'] = obj[0]
        dic['name'] = obj[1]
        dic['count'] = obj[2]
        tags.append(dic)
    context = {
        'records': records,
        'tags': tags
    }
    return context