from flask import Flask, render_template, request, redirect, url_for, flash
import sqlite3
from pathlib import Path
from datetime import date
import sys, csv, io

def app_dir():
    if getattr(sys, "frozen", False):
        return Path(sys.executable).resolve().parent
    return Path(__file__).resolve().parent

APP_DIR = app_dir()
DB_PATH = APP_DIR / "fuel_contract.db"

app = Flask(__name__)
app.secret_key = "parga-fuel-contract-app-change-me"

def get_db():
    con = sqlite3.connect(DB_PATH)
    con.row_factory = sqlite3.Row
    con.execute("PRAGMA foreign_keys = ON")
    return con

def column_exists(con, table, column):
    rows = con.execute(f"PRAGMA table_info({table})").fetchall()
    return any(r["name"] == column for r in rows)

def fnum(value):
    try:
        return float(str(value).replace(",", "."))
    except Exception:
        return 0.0

def init_db():
    con = get_db()
    cur = con.cursor()

    cur.execute("""
    CREATE TABLE IF NOT EXISTS contract_items (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        fuel_type TEXT NOT NULL,
        unit TEXT NOT NULL DEFAULT 'λίτρα',
        contract_qty REAL NOT NULL DEFAULT 0,
        contract_amount REAL NOT NULL DEFAULT 0,
        notes TEXT
    )
    """)

    cur.execute("""
    CREATE TABLE IF NOT EXISTS delivery_headers (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        delivery_date TEXT NOT NULL,
        document_no TEXT NOT NULL,
        supplier TEXT,
        total_amount REAL NOT NULL DEFAULT 0,
        notes TEXT
    )
    """)

    cur.execute("""
    CREATE TABLE IF NOT EXISTS delivery_lines (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        header_id INTEGER NOT NULL,
        item_id INTEGER NOT NULL,
        line_no TEXT,
        line_date TEXT,
        qty REAL NOT NULL DEFAULT 0,
        amount REAL NOT NULL DEFAULT 0,
        vehicle TEXT,
        service TEXT,
        notes TEXT,
        FOREIGN KEY(header_id) REFERENCES delivery_headers(id) ON DELETE CASCADE,
        FOREIGN KEY(item_id) REFERENCES contract_items(id)
    )
    """)

    # Upgrade παλαιότερης έκδοσης: προσθήκη αρ. δελτίου και ημερομηνίας δελτίου
    if not column_exists(con, "delivery_lines", "line_no"):
        cur.execute("ALTER TABLE delivery_lines ADD COLUMN line_no TEXT")
    if not column_exists(con, "delivery_lines", "line_date"):
        cur.execute("ALTER TABLE delivery_lines ADD COLUMN line_date TEXT")

    cnt = cur.execute("SELECT COUNT(*) AS c FROM contract_items").fetchone()["c"]
    if cnt == 0:
        cur.executemany("""
            INSERT INTO contract_items (fuel_type, unit, contract_qty, contract_amount, notes)
            VALUES (?, 'λίτρα', 0, 0, '')
        """, [
            ("Πετρέλαιο Κίνησης",),
            ("Πετρέλαιο Θέρμανσης",),
            ("Αμόλυβδη Βενζίνη",),
        ])

    con.commit()
    con.close()

def dashboard_rows():
    con = get_db()
    rows = con.execute("""
        SELECT ci.id, ci.fuel_type, ci.unit, ci.contract_qty, ci.contract_amount,
               COALESCE(SUM(dl.qty), 0) AS used_qty,
               COALESCE(SUM(dl.amount), 0) AS used_amount
        FROM contract_items ci
        LEFT JOIN delivery_lines dl ON dl.item_id = ci.id
        GROUP BY ci.id
        ORDER BY ci.fuel_type
    """).fetchall()
    con.close()

    result = []
    totals = {"contract_amount": 0, "used_amount": 0, "remaining_amount": 0}
    for r in rows:
        rem_qty = r["contract_qty"] - r["used_qty"]
        rem_amount = r["contract_amount"] - r["used_amount"]
        pct_qty = (r["used_qty"] / r["contract_qty"] * 100) if r["contract_qty"] else 0
        pct_amount = (r["used_amount"] / r["contract_amount"] * 100) if r["contract_amount"] else 0
        status = "ok"
        if rem_qty < 0 or rem_amount < 0:
            status = "danger"
        elif pct_qty >= 90 or pct_amount >= 90:
            status = "warning"
        d = dict(r) | {
            "remaining_qty": rem_qty, "remaining_amount": rem_amount,
            "pct_qty": pct_qty, "pct_amount": pct_amount, "status": status
        }
        result.append(d)
        totals["contract_amount"] += r["contract_amount"]
        totals["used_amount"] += r["used_amount"]
        totals["remaining_amount"] += rem_amount
    return result, totals

@app.route("/")
def index():
    rows, totals = dashboard_rows()
    con = get_db()
    recent = con.execute("""
        SELECT h.*, COALESCE(SUM(dl.qty),0) AS total_qty,
               COALESCE(SUM(dl.amount),0) AS lines_amount, COUNT(dl.id) AS lines_count
        FROM delivery_headers h
        LEFT JOIN delivery_lines dl ON dl.header_id = h.id
        GROUP BY h.id
        ORDER BY h.delivery_date DESC, h.id DESC
        LIMIT 8
    """).fetchall()
    con.close()
    return render_template("index.html", rows=rows, totals=totals, recent=recent)

@app.route("/items")
def items():
    con = get_db()
    rows = con.execute("SELECT * FROM contract_items ORDER BY fuel_type").fetchall()
    con.close()
    return render_template("items.html", rows=rows)

@app.route("/items/add", methods=["GET", "POST"])
def add_item():
    if request.method == "POST":
        con = get_db()
        con.execute("""
            INSERT INTO contract_items (fuel_type, unit, contract_qty, contract_amount, notes)
            VALUES (?, ?, ?, ?, ?)
        """, (
            request.form["fuel_type"].strip(),
            request.form.get("unit", "λίτρα").strip(),
            fnum(request.form.get("contract_qty")),
            fnum(request.form.get("contract_amount")),
            request.form.get("notes", "").strip()
        ))
        con.commit()
        con.close()
        flash("Το είδος καυσίμου καταχωρήθηκε.")
        return redirect(url_for("items"))
    return render_template("item_form.html", item=None)

@app.route("/items/<int:item_id>/edit", methods=["GET", "POST"])
def edit_item(item_id):
    con = get_db()
    item = con.execute("SELECT * FROM contract_items WHERE id = ?", (item_id,)).fetchone()
    if not item:
        con.close()
        flash("Δεν βρέθηκε το είδος καυσίμου.")
        return redirect(url_for("items"))
    if request.method == "POST":
        con.execute("""
            UPDATE contract_items
            SET fuel_type=?, unit=?, contract_qty=?, contract_amount=?, notes=?
            WHERE id=?
        """, (
            request.form["fuel_type"].strip(),
            request.form.get("unit", "λίτρα").strip(),
            fnum(request.form.get("contract_qty")),
            fnum(request.form.get("contract_amount")),
            request.form.get("notes", "").strip(),
            item_id
        ))
        con.commit()
        con.close()
        flash("Τα στοιχεία σύμβασης ενημερώθηκαν.")
        return redirect(url_for("items"))
    con.close()
    return render_template("item_form.html", item=item)

@app.route("/deliveries")
def deliveries():
    con = get_db()
    rows = con.execute("""
        SELECT h.*, COALESCE(SUM(dl.qty),0) AS total_qty,
               COALESCE(SUM(dl.amount),0) AS lines_amount, COUNT(dl.id) AS lines_count
        FROM delivery_headers h
        LEFT JOIN delivery_lines dl ON dl.header_id = h.id
        GROUP BY h.id
        ORDER BY h.delivery_date DESC, h.id DESC
    """).fetchall()
    con.close()
    return render_template("deliveries.html", rows=rows)

@app.route("/deliveries/add", methods=["GET", "POST"])
def add_delivery():
    if request.method == "POST":
        document_no = request.form.get("document_no", "").strip()
        if not document_no:
            flash("Συμπληρώστε αριθμό παραστατικού.")
            return redirect(url_for("add_delivery"))

        con = get_db()
        con.execute("""
            INSERT INTO delivery_headers (delivery_date, document_no, supplier, total_amount, notes)
            VALUES (?, ?, ?, ?, ?)
        """, (
            request.form.get("delivery_date") or str(date.today()),
            document_no,
            request.form.get("supplier", "").strip(),
            fnum(request.form.get("total_amount")),
            request.form.get("notes", "").strip()
        ))
        header_id = con.execute("SELECT last_insert_rowid() AS id").fetchone()["id"]
        con.commit()
        con.close()
        flash("Το παραστατικό δημιουργήθηκε. Προσθέστε τα δελτία στην υποκαρτέλα.")
        return redirect(url_for("edit_delivery", header_id=header_id))
    return render_template("delivery_form.html", today=str(date.today()))

@app.route("/deliveries/<int:header_id>", methods=["GET", "POST"])
def edit_delivery(header_id):
    con = get_db()
    header = con.execute("SELECT * FROM delivery_headers WHERE id = ?", (header_id,)).fetchone()
    if not header:
        con.close()
        flash("Δεν βρέθηκε το παραστατικό.")
        return redirect(url_for("deliveries"))

    if request.method == "POST":
        con.execute("""
            UPDATE delivery_headers
            SET delivery_date=?, document_no=?, supplier=?, total_amount=?, notes=?
            WHERE id=?
        """, (
            request.form.get("delivery_date") or str(date.today()),
            request.form.get("document_no", "").strip(),
            request.form.get("supplier", "").strip(),
            fnum(request.form.get("total_amount")),
            request.form.get("notes", "").strip(),
            header_id
        ))
        con.commit()
        con.close()
        flash("Το παραστατικό ενημερώθηκε.")
        return redirect(url_for("edit_delivery", header_id=header_id))

    items = con.execute("SELECT * FROM contract_items ORDER BY fuel_type").fetchall()
    lines = con.execute("""
        SELECT dl.*, ci.fuel_type, ci.unit
        FROM delivery_lines dl
        JOIN contract_items ci ON ci.id = dl.item_id
        WHERE dl.header_id = ?
        ORDER BY COALESCE(dl.line_date, ''), dl.id
    """, (header_id,)).fetchall()
    summary = con.execute("""
        SELECT ci.fuel_type, ci.unit, COALESCE(SUM(dl.qty),0) AS total_qty,
               COALESCE(SUM(dl.amount),0) AS total_amount
        FROM contract_items ci
        LEFT JOIN delivery_lines dl ON dl.item_id = ci.id AND dl.header_id = ?
        GROUP BY ci.id
        HAVING total_qty > 0 OR total_amount > 0
        ORDER BY ci.fuel_type
    """, (header_id,)).fetchall()
    con.close()
    return render_template("delivery_edit.html", header=header, items=items, lines=lines, summary=summary, today=str(date.today()))

@app.route("/deliveries/<int:header_id>/line/add", methods=["POST"])
def add_delivery_line(header_id):
    con = get_db()
    item_id = int(request.form["item_id"])
    qty = fnum(request.form.get("qty"))
    amount = fnum(request.form.get("amount"))

    totals = con.execute("""
        SELECT ci.contract_qty, ci.contract_amount,
               COALESCE(SUM(dl.qty),0) AS used_qty,
               COALESCE(SUM(dl.amount),0) AS used_amount
        FROM contract_items ci
        LEFT JOIN delivery_lines dl ON dl.item_id = ci.id
        WHERE ci.id = ?
        GROUP BY ci.id
    """, (item_id,)).fetchone()

    if totals and (totals["used_qty"] + qty > totals["contract_qty"] or totals["used_amount"] + amount > totals["contract_amount"]):
        flash("ΠΡΟΣΟΧΗ: Το δελτίο προκαλεί υπέρβαση ποσότητας ή ποσού στο συγκεκριμένο είδος καυσίμου.")

    con.execute("""
        INSERT INTO delivery_lines (header_id, item_id, line_no, line_date, qty, amount, vehicle, service, notes)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
    """, (
        header_id,
        item_id,
        request.form.get("line_no", "").strip(),
        request.form.get("line_date") or str(date.today()),
        qty,
        amount,
        request.form.get("vehicle", "").strip(),
        request.form.get("service", "").strip(),
        request.form.get("notes", "").strip()
    ))
    con.commit()
    con.close()
    flash("Το δελτίο προστέθηκε.")
    return redirect(url_for("edit_delivery", header_id=header_id) + "#lines")

@app.route("/lines/<int:line_id>/edit", methods=["GET", "POST"])
def edit_line(line_id):
    con = get_db()
    line = con.execute("""
        SELECT dl.*, ci.fuel_type, ci.unit
        FROM delivery_lines dl
        JOIN contract_items ci ON ci.id = dl.item_id
        WHERE dl.id = ?
    """, (line_id,)).fetchone()
    if not line:
        con.close()
        flash("Δεν βρέθηκε το δελτίο.")
        return redirect(url_for("deliveries"))

    header = con.execute("SELECT * FROM delivery_headers WHERE id = ?", (line["header_id"],)).fetchone()
    items = con.execute("SELECT * FROM contract_items ORDER BY fuel_type").fetchall()

    if request.method == "POST":
        item_id = int(request.form["item_id"])
        qty = fnum(request.form.get("qty"))
        amount = fnum(request.form.get("amount"))

        totals = con.execute("""
            SELECT ci.contract_qty, ci.contract_amount,
                   COALESCE(SUM(CASE WHEN dl.id != ? THEN dl.qty ELSE 0 END),0) AS used_qty,
                   COALESCE(SUM(CASE WHEN dl.id != ? THEN dl.amount ELSE 0 END),0) AS used_amount
            FROM contract_items ci
            LEFT JOIN delivery_lines dl ON dl.item_id = ci.id
            WHERE ci.id = ?
            GROUP BY ci.id
        """, (line_id, line_id, item_id)).fetchone()

        if totals and (totals["used_qty"] + qty > totals["contract_qty"] or totals["used_amount"] + amount > totals["contract_amount"]):
            flash("ΠΡΟΣΟΧΗ: Η αλλαγή προκαλεί υπέρβαση ποσότητας ή ποσού στο συγκεκριμένο είδος καυσίμου.")

        con.execute("""
            UPDATE delivery_lines
            SET item_id=?, line_no=?, line_date=?, qty=?, amount=?, vehicle=?, service=?, notes=?
            WHERE id=?
        """, (
            item_id,
            request.form.get("line_no", "").strip(),
            request.form.get("line_date") or str(date.today()),
            qty,
            amount,
            request.form.get("vehicle", "").strip(),
            request.form.get("service", "").strip(),
            request.form.get("notes", "").strip(),
            line_id
        ))
        con.commit()
        header_id = line["header_id"]
        con.close()
        flash("Το δελτίο ενημερώθηκε.")
        return redirect(url_for("edit_delivery", header_id=header_id) + "#lines")

    con.close()
    return render_template("line_edit.html", header=header, line=line, items=items)

@app.route("/lines/<int:line_id>/delete", methods=["POST"])
def delete_line(line_id):
    con = get_db()
    row = con.execute("SELECT header_id FROM delivery_lines WHERE id = ?", (line_id,)).fetchone()
    if row:
        header_id = row["header_id"]
        con.execute("DELETE FROM delivery_lines WHERE id = ?", (line_id,))
        con.commit()
        con.close()
        flash("Το δελτίο διαγράφηκε.")
        return redirect(url_for("edit_delivery", header_id=header_id) + "#lines")
    con.close()
    return redirect(url_for("deliveries"))

@app.route("/deliveries/<int:header_id>/delete", methods=["POST"])
def delete_delivery(header_id):
    con = get_db()
    con.execute("DELETE FROM delivery_lines WHERE header_id = ?", (header_id,))
    con.execute("DELETE FROM delivery_headers WHERE id = ?", (header_id,))
    con.commit()
    con.close()
    flash("Το παραστατικό και τα δελτία του διαγράφηκαν.")
    return redirect(url_for("deliveries"))

@app.route("/reports")
def reports():
    rows, totals = dashboard_rows()
    return render_template("reports.html", rows=rows, totals=totals)

@app.route("/export.csv")
def export_csv():
    con = get_db()
    rows = con.execute("""
        SELECT h.delivery_date, h.document_no, h.supplier,
               dl.line_no, dl.line_date, ci.fuel_type, dl.qty, dl.amount,
               dl.vehicle, dl.service, dl.notes
        FROM delivery_lines dl
        JOIN delivery_headers h ON h.id = dl.header_id
        JOIN contract_items ci ON ci.id = dl.item_id
        ORDER BY h.delivery_date, h.document_no, dl.line_date, dl.line_no, dl.id
    """).fetchall()
    con.close()

    output = io.StringIO()
    writer = csv.writer(output, delimiter=";")
    writer.writerow(["Ημ/νία Παραστατικού", "Παραστατικό", "Προμηθευτής", "Αρ. Δελτίου", "Ημ/νία Δελτίου", "Καύσιμο", "Ποσότητα", "Ποσό", "Όχημα/Μηχάνημα", "Υπηρεσία/ΚΑ", "Παρατηρήσεις"])
    for r in rows:
        writer.writerow([r[k] for k in r.keys()])

    return app.response_class(
        output.getvalue(),
        mimetype="text/csv; charset=utf-8",
        headers={"Content-Disposition": "attachment; filename=fuel_contract_export.csv"}
    )

if __name__ == "__main__":
    init_db()
    app.run(host="0.0.0.0", port=5000, debug=True)
