from flask import Blueprint, render_template, request, g, url_for, redirect, Response
from datetime import datetime
import sqlite3


keep = Blueprint('keep', __name__,template_folder='templates')

def get_db():
    db = getattr(g, "_database", None)
    if db is None:
        db = g._database = sqlite3.connect("app/db/keepalive.db")
    return db




@keep.route('/keepalive/')
def dispkeepalive():
    conn = get_db()
    c = conn.cursor()
    items_from_db = c.execute("""SELECT
                    i.id, i.location, i.time, i.date
                    FROM
                    keepalive2 AS i
                    ORDER BY i.id DESC
    """)
    #db_teardown(keep)
    items = []
    for row in items_from_db:
        item = {
            "id": row[0],
            "location": row[1],
            "time": row[2],
            "date": row[3]
        }
        items.append(item)

    return render_template("keepaliveshowpage.html", items=items)





@keep.route('/loc/<location>/', methods=["POST"])
def getalive(location):
    conn = get_db()
    c = conn.cursor()
    c.execute("""CREATE TABLE IF NOT EXISTS keepalive2 (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    location TEXT,
                    date TEXT,
                    time TEXT)""")

    # Get actual system-local time (assumed to be in ET)
    now = datetime.now()

    c.execute("""INSERT INTO keepalive2 (location, date, time)
                 VALUES (?, ?, ?)""",
              (location,
               now.strftime("%m/%d/%y"),
               now.strftime("%H:%M:%S")))
    conn.commit()
    conn.close()

    return f"Keepalive recorded for {location}"


@keep.route('/loc/<location>/', methods=["GET"])
def show_location(location):
    return f"<h1>Received keepalive from: {location}</h1>"

@keep.route('/delete/<int:item_id>/', methods=["POST"])
def delete_keepalive(item_id):
    conn = get_db()
    c = conn.cursor()
    c.execute("DELETE FROM keepalive2 WHERE id = ?", (item_id,))
    conn.commit()
    return redirect(url_for('keep.dispkeepalive'))


@keep.route('/download_csv')
def download_csv():
    conn = get_db()
    c = conn.cursor()
    c.execute("SELECT location, date, time FROM keepalive2 ORDER BY id DESC")
    rows = c.fetchall()
    conn.close()

    output = "Location,Date,Time\n"
    for row in rows:
        output += f"{row[0]},{row[1]},{row[2]}\n"

    return Response(
        output,
        mimetype='text/csv',
        headers={"Content-Disposition": "attachment;filename=keepalive.csv"}
    )