import os
import sqlite3
from flask import Flask, g, jsonify, request, abort, render_template
from flask_cors import CORS, cross_origin
from datetime import datetime
import threading
import requests

DB_PATH = os.environ.get('DB_PATH') or "test.db"

app = Flask(__name__)
cors = CORS(app)
app.config['CORS_HEADERS'] = 'Content-Type'

def parseTitle(url):
    resp = requests.get(f"https://textance.herokuapp.com/rest/title/{url}")
    if (resp.status_code == 200):
        return resp.text
    return url

def mkDict(cursor, row):
    return dict((cursor.description[idx][0], value) for idx, value in enumerate(row))

def get_db():
    db = getattr(g, '_db', None)
    if (db is None):
        db = g._db = sqlite3.connect(DB_PATH)
        db.row_factory = mkDict
    return db

@app.teardown_appcontext
def close_db(exception):
    db = getattr(g, '_db', None)
    if (db is not None):
        db.close()

def query(query, *args, one=False):
    db = get_db()
    with db:
        cur = get_db().execute(query, args)
        rv = cur.fetchall()
        cur.close()
    return (rv[0] if rv else None) if one else rv

# ----------------------------------- #

def addToDb(url):
    def _run():
        with app.app_context():
            app.logger.info(f"adding `{url}`")
            now = datetime.now()
            title = parseTitle(url)
            query("INSERT INTO Urls VALUES (?,?,?)", datetime.now(), url, title)
            app.logger.info(f"done adding `{url}`")
    threading.Thread(target=_run).start()

def popAllFromDb():
    def _run():
        with app.app_context():
            app.logger.info("deleting all")
            query("DELETE FROM Urls;")
            app.logger.info("done deleting")
    threading.Thread(target=_run).start()

# ----------------------------------- #

@app.route('/addItem')
def addItem():
    url = request.args.get("url", None)
    if (url is None):
        app.logger.warn("no url given")
        abort(400)
    app.logger.info(f"adding item `{url}`")
    addToDb(url)
    return "item added...", 202

@app.route('/popAllItems')
def popAll():
    popAllFromDb()
    return "deleting..."

@app.route('/items', methods=['GET'])
@cross_origin()
def list_items():
    app.logger.debug("list all")
    return jsonify( query('SELECT rowid, url, title, addDate FROM Urls;') )

@app.route('/items', methods=['POST'])
@cross_origin()
def add_item():
    data = request.get_json()
    if (not data):
        abort(400)
    url = data.get("url", None)
    if (not url):
        abort(400)
    addToDb(url)
    return "", 202

@app.route('/items/<int:rowId>', methods=['DELETE'])
@cross_origin()
def delete_item(rowId):
    itemExists = 1 in query('SELECT EXISTS(SELECT 1 FROM Urls WHERE rowid=? LIMIT 1)', rowId, one=True).values()
    if (not itemExists):
        app.logger.warn("row does not exist")
        abort(404)
    query("DELETE FROM Urls WHERE rowid=?", rowId)
    return "", 204

@app.route('/')
@app.route('/<path:path>')
def staticFallback(path="index.html"):
    app.logger.debug("find path")
    fullPath = os.path.join(app.static_folder, path)
    app.logger.debug(f"fullPath: {fullPath}")
    if (os.path.exists(fullPath)):
        app.logger.debug("path exists")
        return app.send_static_file(path)
    app.logger.debug("path does not exist")
    return "", 404


if (not os.path.exists(DB_PATH)):
    with app.app_context():
        db = get_db()
        with app.open_resource('schema.sql', mode='r') as f:
            db.cursor().executescript(f.read())
        db.commit()