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

from blog import models
from blog.controller import article


def current_record(request, date):
    page = request.GET.get('page')
    if page is None:
        page = 0
    result = get_record_and_tags()
    context = {'current_record': 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)'''
    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