import os
import sqlite3
from datetime import datetime
from mimetypes import guess_type

from flask import Flask, g, render_template, request, redirect, url_for, flash, send_from_directory


BASE_DIR = os.path.dirname(os.path.abspath(__file__))
DB_PATH = os.path.join(BASE_DIR, "bugtracker.db")
UPLOAD_FOLDER = os.path.join(BASE_DIR, "uploads")
ALLOWED_EXTENSIONS = {"png", "jpg", "jpeg", "gif", "webp", "bmp", "pdf"}


def ensure_dirs():
    os.makedirs(UPLOAD_FOLDER, exist_ok=True)


def get_db():
    db = getattr(g, "_db", None)
    if db is None:
        db = g._db = sqlite3.connect(DB_PATH)
        db.row_factory = sqlite3.Row
        db.execute("PRAGMA foreign_keys = ON;")
    return db


def close_db(e=None):
    db = getattr(g, "_db", None)
    if db is not None:
        db.close()


def init_db():
    db = sqlite3.connect(DB_PATH)
    db.execute("PRAGMA foreign_keys = ON;")
    cur = db.cursor()
    cur.execute(
        """
        CREATE TABLE IF NOT EXISTS projects (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            description TEXT,
            created_at TEXT NOT NULL
        );
        """
    )
    cur.execute(
        """
        CREATE TABLE IF NOT EXISTS tickets (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            project_id INTEGER NOT NULL,
            title TEXT NOT NULL,
            description TEXT,
            status TEXT NOT NULL DEFAULT 'open',
            priority TEXT NOT NULL DEFAULT 'normal',
            reporter TEXT,
            assignee TEXT,
            created_at TEXT NOT NULL,
            updated_at TEXT NOT NULL,
            FOREIGN KEY(project_id) REFERENCES projects(id) ON DELETE CASCADE
        );
        """
    )
    cur.execute(
        """
        CREATE TABLE IF NOT EXISTS attachments (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            ticket_id INTEGER NOT NULL,
            filename TEXT NOT NULL,
            stored_name TEXT NOT NULL,
            content_type TEXT,
            uploaded_at TEXT NOT NULL,
            FOREIGN KEY(ticket_id) REFERENCES tickets(id) ON DELETE CASCADE
        );
        """
    )
    cur.execute(
        """
        CREATE TABLE IF NOT EXISTS activities (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            ticket_id INTEGER NOT NULL,
            type TEXT NOT NULL,
            author TEXT,
            message TEXT NOT NULL,
            created_at TEXT NOT NULL,
            FOREIGN KEY(ticket_id) REFERENCES tickets(id) ON DELETE CASCADE
        );
        """
    )
    db.commit()
    db.close()


def allowed_file(filename: str) -> bool:
    return "." in filename and filename.rsplit(".", 1)[1].lower() in ALLOWED_EXTENSIONS


def create_app():
    ensure_dirs()
    init_db()

    app = Flask(__name__)
    app.config["SECRET_KEY"] = os.environ.get("SECRET_KEY", "dev-secret-key-change-me")
    app.config["UPLOAD_FOLDER"] = UPLOAD_FOLDER

    @app.teardown_appcontext
    def teardown_db(exception):
        close_db()

    @app.route("/")
    def home():
        return redirect(url_for("list_tickets"))

    # Projects
    @app.route("/projects", methods=["GET", "POST"])
    def list_projects():
        db = get_db()
        if request.method == "POST":
            name = request.form.get("name", "").strip()
            description = request.form.get("description", "").strip()
            if not name:
                flash("Projektname darf nicht leer sein.", "danger")
            else:
                db.execute(
                    "INSERT INTO projects (name, description, created_at) VALUES (?, ?, ?)",
                    (name, description, datetime.utcnow().isoformat()),
                )
                db.commit()
                flash("Projekt erstellt.", "success")
                return redirect(url_for("list_projects"))
        projects = db.execute("SELECT * FROM projects ORDER BY created_at DESC").fetchall()
        return render_template("projects_list.html", projects=projects)

    @app.route("/projects/<int:project_id>")
    def project_detail(project_id: int):
        db = get_db()
        project = db.execute("SELECT * FROM projects WHERE id=?", (project_id,)).fetchone()
        if project is None:
            flash("Projekt nicht gefunden.", "warning")
            return redirect(url_for("list_projects"))
        tickets = db.execute(
            "SELECT * FROM tickets WHERE project_id=? ORDER BY created_at DESC",
            (project_id,),
        ).fetchall()
        return render_template("project_detail.html", project=project, tickets=tickets)

    # Tickets
    @app.route("/tickets")
    def list_tickets():
        db = get_db()
        project_id = request.args.get("project_id")
        q = request.args.get("q", "").strip()
        status = request.args.get("status")
        priority = request.args.get("priority")

        sql = [
            "SELECT t.*, p.name as project_name FROM tickets t JOIN projects p ON t.project_id=p.id"
        ]
        where = []
        params = []

        if project_id:
            where.append("t.project_id = ?")
            params.append(project_id)

        if q:
            where.append("(t.title LIKE ? OR t.description LIKE ?)")
            like = f"%{q}%"
            params.extend([like, like])

        allowed_status = {"open", "in_progress", "closed"}
        if status in allowed_status:
            where.append("t.status = ?")
            params.append(status)

        allowed_priority = {"low", "normal", "high", "urgent"}
        if priority in allowed_priority:
            where.append("t.priority = ?")
            params.append(priority)

        if where:
            sql.append("WHERE " + " AND ".join(where))
        sql.append("ORDER BY t.created_at DESC")
        query = "\n".join(sql)

        tickets = db.execute(query, tuple(params)).fetchall()
        projects = db.execute("SELECT * FROM projects ORDER BY name ASC").fetchall()
        return render_template(
            "tickets_list.html",
            tickets=tickets,
            projects=projects,
            selected_project=project_id,
            q=q,
            selected_status=status,
            selected_priority=priority,
        )

    @app.route("/tickets/new", methods=["GET", "POST"])
    def new_ticket():
        db = get_db()
        projects = db.execute("SELECT * FROM projects ORDER BY name ASC").fetchall()
        if not projects:
            flash("Bitte zuerst ein Projekt anlegen.", "info")
            return redirect(url_for("list_projects"))
        if request.method == "POST":
            title = request.form.get("title", "").strip()
            description = request.form.get("description", "").strip()
            project_id = request.form.get("project_id")
            status = request.form.get("status", "open")
            priority = request.form.get("priority", "normal")
            reporter = request.form.get("reporter", "").strip()
            assignee = request.form.get("assignee", "").strip()

            if not title or not project_id:
                flash("Titel und Projekt sind Pflichtfelder.", "danger")
            else:
                now = datetime.utcnow().isoformat()
                cur = db.execute(
                    """
                    INSERT INTO tickets (project_id, title, description, status, priority, reporter, assignee, created_at, updated_at)
                    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
                    """,
                    (project_id, title, description, status, priority, reporter, assignee, now, now),
                )
                db.commit()
                ticket_id = cur.lastrowid
                db.execute(
                    "INSERT INTO activities (ticket_id, type, author, message, created_at) VALUES (?, ?, ?, ?, ?)",
                    (ticket_id, "ticket_create", reporter or None, f"Ticket erstellt: {title}", now),
                )
                db.commit()
                flash("Ticket erstellt.", "success")
                return redirect(url_for("ticket_detail", ticket_id=ticket_id))

        return render_template("ticket_new.html", projects=projects)

    @app.route("/tickets/<int:ticket_id>", methods=["GET", "POST"])
    def ticket_detail(ticket_id: int):
        db = get_db()
        ticket = db.execute(
            "SELECT t.*, p.name as project_name FROM tickets t JOIN projects p ON t.project_id=p.id WHERE t.id=?",
            (ticket_id,),
        ).fetchone()
        if ticket is None:
            flash("Ticket nicht gefunden.", "warning")
            return redirect(url_for("list_tickets"))

        if request.method == "POST":
            # Update ticket meta (status/priority/assignee)
            old_status = ticket["status"]
            old_priority = ticket["priority"]
            old_assignee = ticket["assignee"]

            status = request.form.get("status", old_status) or old_status
            priority = request.form.get("priority", old_priority) or old_priority
            assignee = request.form.get("assignee", old_assignee) or old_assignee

            now = datetime.utcnow().isoformat()
            db.execute(
                "UPDATE tickets SET status=?, priority=?, assignee=?, updated_at=? WHERE id=?",
                (status, priority, assignee, now, ticket_id),
            )
            # Log changes as activities
            if status != old_status:
                db.execute(
                    "INSERT INTO activities (ticket_id, type, author, message, created_at) VALUES (?, ?, ?, ?, ?)",
                    (ticket_id, "status_change", None, f"Status: {old_status} → {status}", now),
                )
            if priority != old_priority:
                db.execute(
                    "INSERT INTO activities (ticket_id, type, author, message, created_at) VALUES (?, ?, ?, ?, ?)",
                    (ticket_id, "priority_change", None, f"Priorität: {old_priority} → {priority}", now),
                )
            if (assignee or "") != (old_assignee or ""):
                db.execute(
                    "INSERT INTO activities (ticket_id, type, author, message, created_at) VALUES (?, ?, ?, ?, ?)",
                    (ticket_id, "assignee_change", None, f"Zuständig: {old_assignee or '–'} → {assignee or '–'}", now),
                )

            db.commit()
            flash("Ticket aktualisiert.", "success")
            return redirect(url_for("ticket_detail", ticket_id=ticket_id))

        attachments = db.execute(
            "SELECT * FROM attachments WHERE ticket_id=? ORDER BY uploaded_at DESC",
            (ticket_id,),
        ).fetchall()
        activities = db.execute(
            "SELECT * FROM activities WHERE ticket_id=? ORDER BY created_at DESC, id DESC",
            (ticket_id,),
        ).fetchall()
        return render_template("ticket_detail.html", ticket=ticket, attachments=attachments, activities=activities)

    @app.route("/tickets/<int:ticket_id>/upload", methods=["POST"])
    def upload_attachment(ticket_id: int):
        db = get_db()
        # ensure ticket exists
        row = db.execute("SELECT id FROM tickets WHERE id=?", (ticket_id,)).fetchone()
        if not row:
            flash("Ticket nicht gefunden.", "warning")
            return redirect(url_for("list_tickets"))

        if "file" not in request.files:
            flash("Keine Datei ausgewählt.", "danger")
            return redirect(url_for("ticket_detail", ticket_id=ticket_id))
        file = request.files["file"]
        if file and file.filename and allowed_file(file.filename):
            orig = os.path.basename(file.filename)
            ext = orig.rsplit(".", 1)[-1].lower()
            safe_base = os.path.splitext(orig)[0].replace(" ", "_")[:60]
            stored_name = f"t{ticket_id}_{int(datetime.utcnow().timestamp())}_{safe_base}.{ext}"
            path = os.path.join(UPLOAD_FOLDER, stored_name)
            file.save(path)
            content_type = guess_type(orig)[0]
            db.execute(
                "INSERT INTO attachments (ticket_id, filename, stored_name, content_type, uploaded_at) VALUES (?, ?, ?, ?, ?)",
                (ticket_id, orig, stored_name, content_type, datetime.utcnow().isoformat()),
            )
            db.execute(
                "INSERT INTO activities (ticket_id, type, author, message, created_at) VALUES (?, ?, ?, ?, ?)",
                (ticket_id, "attachment_add", None, f"Anhang hochgeladen: {orig}", datetime.utcnow().isoformat()),
            )
            db.commit()
            flash("Datei hochgeladen.", "success")
        else:
            flash("Ungültige Datei oder Dateiendung.", "danger")
        return redirect(url_for("ticket_detail", ticket_id=ticket_id))

    @app.route("/attachments/<int:attachment_id>/delete", methods=["POST"])
    def delete_attachment(attachment_id: int):
        db = get_db()
        att = db.execute("SELECT * FROM attachments WHERE id=?", (attachment_id,)).fetchone()
        if not att:
            flash("Anhang nicht gefunden.", "warning")
            return redirect(url_for("list_tickets"))
        ticket_id = att["ticket_id"]
        try:
            os.remove(os.path.join(UPLOAD_FOLDER, att["stored_name"]))
        except FileNotFoundError:
            pass
        db.execute("DELETE FROM attachments WHERE id=?", (attachment_id,))
        db.execute(
            "INSERT INTO activities (ticket_id, type, author, message, created_at) VALUES (?, ?, ?, ?, ?)",
            (ticket_id, "attachment_delete", None, f"Anhang gelöscht: {att['filename']}", datetime.utcnow().isoformat()),
        )
        db.commit()
        flash("Anhang gelöscht.", "success")
        return redirect(url_for("ticket_detail", ticket_id=ticket_id))

    @app.route("/tickets/<int:ticket_id>/comment", methods=["POST"])
    def add_comment(ticket_id: int):
        db = get_db()
        ticket = db.execute("SELECT id FROM tickets WHERE id=?", (ticket_id,)).fetchone()
        if not ticket:
            flash("Ticket nicht gefunden.", "warning")
            return redirect(url_for("list_tickets"))
        author = request.form.get("author", "").strip() or None
        content = request.form.get("content", "").strip()
        if not content:
            flash("Kommentar darf nicht leer sein.", "danger")
            return redirect(url_for("ticket_detail", ticket_id=ticket_id))
        db.execute(
            "INSERT INTO activities (ticket_id, type, author, message, created_at) VALUES (?, ?, ?, ?, ?)",
            (ticket_id, "comment", author, content, datetime.utcnow().isoformat()),
        )
        db.commit()
        flash("Kommentar hinzugefügt.", "success")
        return redirect(url_for("ticket_detail", ticket_id=ticket_id))

    @app.route("/uploads/<path:filename>")
    def uploaded_file(filename):
        return send_from_directory(UPLOAD_FOLDER, filename, as_attachment=False)

    return app


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