"""
database_example_formatted.py
:copyright: (c) 2015 by C. W.
:license: New BSD
"""
from flask import Flask, request, jsonify, redirect, url_for
import flask_excel as excel
from flask_sqlalchemy import SQLAlchemy
from datetime import datetime

# please uncomment the following line if you use pyexcel < 0.2.2
# import pyexcel.ext.xls

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


class Post(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(80))
    body = db.Column(db.Text)
    pub_date = db.Column(db.DateTime)

    category_id = db.Column(db.Integer, db.ForeignKey("category.id"))
    category = db.relationship(
        "Category", backref=db.backref("posts", lazy="dynamic")
    )

    def __init__(self, title, body, category, pub_date=None):
        self.title = title
        self.body = body
        if pub_date is None:
            pub_date = datetime.utcnow()
        self.pub_date = pub_date
        self.category = category

    def __repr__(self):
        return "<Post %r>" % self.title


class Category(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50))

    def __init__(self, name):
        self.name = name

    def __repr__(self):
        return "<Category %r>" % self.name


db.create_all()


@app.route("/upload", methods=["GET", "POST"])
def upload_file():
    if request.method == "POST":
        return jsonify({"result": request.get_array("file")})
    return """
    <!doctype html>
    <title>Upload an excel file</title>
    <h1>Excel file upload (csv, tsv, csvz, tsvz only)</h1>
    <form action="" method=post enctype=multipart/form-data><p>
    <input type=file name=file><input type=submit value=Upload>
    </form>
    """


@app.route("/download", methods=["GET"])
def download_file():
    return excel.make_response_from_array([[1, 2], [3, 4]], "csv")


@app.route("/import", methods=["GET", "POST"])
def doimport():
    if request.method == "POST":

        def category_init_func(row):
            c = Category(row["name"])
            c.id = row["id"]
            return c

        def post_init_func(row):
            c = Category.query.filter_by(name=row["category"]).first()
            p = Post(row["title"], row["body"], c, row["pub_date"])
            return p

        request.save_book_to_database(
            field_name="file",
            session=db.session,
            tables=[Category, Post],
            initializers=[category_init_func, post_init_func],
        )
        return redirect(url_for(".handson_table"), code=302)
    return """
    <!doctype html>
    <title>Upload an excel file</title>
    <h1>Excel file upload (xls, xlsx, ods please)</h1>
    <form action="" method=post enctype=multipart/form-data><p>
    <input type=file name=file><input type=submit value=Upload>
    </form>
    """


@app.route("/export", methods=["GET"])
def doexport():
    return excel.make_response_from_tables(db.session, [Category, Post], "xls")


@app.route("/custom_export", methods=["GET"])
def docustomexport():
    query_sets = Category.query.filter_by(id=1).all()
    column_names = ["id", "name"]
    return excel.make_response_from_query_sets(
        query_sets, column_names, "xls", dest_sheet_name="custom_sheet"
    )


@app.route("/handson_view", methods=["GET"])
def handson_table():
    return excel.make_response_from_tables(
        db.session, [Category, Post], "handsontable.html"
    )


if __name__ == "__main__":
    app.run()
