from flask import Flask, request, make_response, send_file
from flask_cors import *
from gevent import pywsgi
from urllib import parse
from main.main import *
from main.distance import *
from main.sa_pa import *
from settings import *

import json
import os
import logging
import sqlite3
import time

app = Flask(__name__)

app.logger.setLevel(logging.INFO)
handler = logging.FileHandler('flask.log')
handler.setLevel(logging.INFO)
formatter = logging.Formatter('%(asctime)s - %(levelname)s - %(message)s')
handler.setFormatter(formatter)
app.logger.addHandler(handler)

CORS(app)

db_conn = sqlite3.connect('dashboard.db')

@app.route('/api/pov-element/common', methods=["GET"])
def common():
    query_data = request.args.get('data')
    json_data = json.loads(query_data)
    theme = request.args.get('theme')
    app.logger.info('[{}] {}'.format(request.remote_addr, parse.unquote(query_data)))
    track_request(request.remote_addr, '直行和出口预告', parse.unquote(query_data))
    data, path = gen_common(json_data, theme)
    res = {
        'code': 0,
        'image': data,
    }
    response = make_response(res)
    AUTO_CLEAN and os.remove(path)
    return response

@app.route('/api/pov-element/distance', methods=["GET"])
def distance():
    query_data = request.args.get('data')
    json_data = json.loads(query_data)
    theme = request.args.get('theme')
    app.logger.info('[{}] {}'.format(request.remote_addr, parse.unquote(query_data)))
    track_request(request.remote_addr, '地点距离预告', parse.unquote(query_data))
    data, path = gen_distance(json_data, theme)
    res = {
        'code': 0,
        'image': data,
    }
    response = make_response(res)
    AUTO_CLEAN and os.remove(path)
    return response

@app.route('/api/pov-element/service', methods=["GET"])
def service():
    query_data = request.args.get('data')
    json_data = json.loads(query_data)
    app.logger.info('[{}] {}'.format(request.remote_addr, parse.unquote(query_data)))
    track_request(request.remote_addr, '服务区预告', parse.unquote(query_data))
    data, path = gen_service(json_data)
    res = {
        'code': 0,
        'image': data,
    }
    response = make_response(res)
    AUTO_CLEAN and os.remove(path)
    return response

@app.route('/api/pov-element/dashboard', methods=["GET"])
def dashboard():
    start_time = request.args.get('start_time')
    end_time = request.args.get('end_time')
    data = fetch_dashboard(start_time, end_time)
    res = {
        'code': 0,
        'image': data,
    }
    response = make_response(res)
    return response

@app.route('/api/pov-element/database', methods=["GET"])
def database():
    return send_file('dashboard.db')

def track_request(ip, type, data):
    cursor = db_conn.cursor()
    sql = """
        INSERT INTO POV(TIME, IP, TYPE, DATA) VALUES(?, ?, ?, ?);
    """
    param = (time.strftime('%Y-%m-%d %H:%M:%S'), ip, type, data)
    try:
        cursor.execute(sql, param)
    except sqlite3.OperationalError as e:
        app.logger.info(e)
    finally:
        cursor.close()
        db_conn.commit()

def fetch_dashboard(start_time, end_time):
    cursor = db_conn.cursor()
    sql = """
        SELECT TIME, TYPE, COUNT(*) FROM POV WHERE TIME BETWEEN ? AND ? GROUP BY TYPE, TIME;
    """
    param = (start_time, end_time)
    try:
        cursor.execute(sql, param)
        result = cursor.fetchall()
        app.logger.info(result)
    except sqlite3.OperationalError as e:
        app.logger.info(e)
    finally:
        cursor.close()
        db_conn.commit()


def init_dashboard():
    cursor = db_conn.cursor()
    sql = """
        CREATE TABLE IF NOT EXISTS POV(
            TIME TEXT,
            IP TEXT,
            TYPE INTEGER,
            DATA TEXT
        );
    """
    try:
        cursor.execute(sql)
    except sqlite3.OperationalError as e:
        app.logger.info(e)
    finally:
        cursor.close()
        db_conn.commit()


if __name__ == '__main__':
    init_dashboard()
    server = pywsgi.WSGIServer(('0.0.0.0', 5000), app)
    server.serve_forever()