import flask
import sqlite3
from datetime import datetime

from flask import Flask, render_template, redirect, url_for, request, jsonify
from flask_sqlalchemy import SQLAlchemy

import sqlalchemy.exc
from sqlalchemy_utils import PasswordType

# from sqlalchemy import Column, Integer, String
# from sqlalchemy.ext.declarative import declarative_base

# TODO: sqlalchemy?

# schema:
#   Utente: uid, username, password
#   TipoRadio: trid, tipo
#   Avvistamento: aid, uid, tipo_radio, data, commento

app = Flask(__name__)
app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///map.db"
db = SQLAlchemy(app)


class User(db.Model):
    __tablename__ = "user"

    uid = db.Column(db.Integer, primary_key=True, nullable=False)
    username = db.Column(db.String, unique=True)
    password = db.Column(PasswordType(schemes=["pbkdf2_sha512"]))

    rapporti = db.relationship("RapportoRicezione")


class TipoRadio(db.Model):
    __tablename__ = "tiporadio"
    tid = db.Column(db.Integer, primary_key=True, nullable=False)
    nome = db.Column(db.Unicode)
    descrizione = db.Column(db.Unicode)

    rapporti = db.relationship("RapportoRicezione")


class RapportoRicezione(db.Model):
    __tablename__ = "rapporto"

    rid = db.Column(db.Integer, primary_key=True)
    uid = db.Column(db.Integer, db.ForeignKey("user.uid"))
    tid = db.Column(db.Integer, db.ForeignKey("tiporadio.tid"))

    date = db.Column(db.Date, default=lambda: datetime.now())

    lat = db.Column(db.Float)
    lng = db.Column(db.Float)

    comprensibile = db.Column(db.Integer)  # TODO: >= 0 <= 5
    stabilita = db.Column(db.Integer)  # TODO: >= 0 <= 5

    author = db.relationship(User, back_populates="rapporti")
    tipo_radio = db.relationship(TipoRadio, back_populates="rapporti")

    def serialize(self):
        d = {k:v for k,v in self.__dict__.items() if not k.startswith('_')}
        d['colore'] = self.colore
        d['radius'] = self.radius
        d['explaination'] = self.explaination
        return d

    @property
    def colore(self):
        c = self.comprensibile
        if c > 3:
            return 'green'
        if c > 1:
            return 'yellow'
        return 'red'

    @property
    def radius(self):
        return self.stabilita * 70

    @property
    def explaination(self):
        return 'Aggiunto il %s da %s' % (self.date, self.author.username)

    def __repr__(self):
        return "<Rapporto %s del %s>" % (self.rid, self.date)


@app.before_first_request
def init_db():
    db.create_all()

    try:
        anon = User(username="anonymous", password="antani")
        db.session.add(anon)
        db.session.commit()
    except sqlalchemy.exc.IntegrityError:
        db.session.rollback()

    for nome in ["radiolina portatile", "autoradio", "stereo fisso dentro casa"]:
        try:
            t = TipoRadio(nome=nome)
            db.session.add(t)
            db.session.commit()
        except sqlalchemy.exc.IntegrityError:
            db.session.rollback()

    cnt = int(RapportoRicezione.query.count())
    if cnt == 0:
        for i in range(6):
            r = RapportoRicezione(
                uid=User.query.filter(User.username == "anonymous")[0].uid,
                tid=TipoRadio.query.all()[0].tid,
                stabilita=i,
                comprensibile=i,
                lat=43.8199 - i * 0.01,
                lng=11.22185 + i * 0.03,
            )
            db.session.add(r)
            db.session.commit()


@app.route("/")
def home():
    return render_template("index.html")

@app.route("/add")
def add():
    return render_template("add.html")


@app.route("/api/rapporti/get")
def get_rapporti():
    query = RapportoRicezione.query.all()
    # TODO: usa query params per filtrare in base a $cose
    return jsonify(dict(rapporti=[r.serialize() for r in query]))

@app.route("/api/rapporti/add", methods=['POST'])
def add_rapporto():
    anon = User.query.filter(User.username=='anonymous')[0].uid
    r = RapportoRicezione(
        uid=anon,
        lat=request.form['lat'],
        lng=request.form['lng'],
        stabilita=request.form['stabilita'],
        comprensibile=request.form['comprensibile'],

    )
    db.session.add(r)
    db.session.commit()

    r = RapportoRicezione.query.get(r.rid)
    return jsonify(r.serialize())

@app.route("/api/rapporti/delete", methods=['POST'])
def delete_rapporto():
    r = RapportoRicezione.query.get(int(request.form['rid']))
    db.session.delete(r)
    db.session.commit()

    return jsonify(True)