from flask import Flask, render_template, request, redirect, session, url_for, abort, send_file
from flask_session import Session
from functools import wraps
import sqlite3
import os
from uuid import uuid4
import requests
import time
from datetime import datetime

try:
    from PIL import Image, ExifTags
except Exception:
    Image = None
    ExifTags = None

from werkzeug.utils import secure_filename
from werkzeug.security import generate_password_hash, check_password_hash


time.sleep(30)

# ---------------- Paths / Config ----------------
BASE_DIR = r"D:\www\data\htdocs\kl"

app = Flask(
    __name__,
    static_url_path="/kl/static",
    static_folder=os.path.join(BASE_DIR, "static"),
    template_folder=os.path.join(BASE_DIR, "templates"),
)
app.config["SECRET_KEY"] = "super-secret-key"
app.config["SESSION_TYPE"] = "filesystem"
app.config["MAX_CONTENT_LENGTH"] = 5 * 1024 * 1024  # 5MB

Session(app)

DB_NAME = os.path.join(BASE_DIR, "users.db")

UPLOAD_DIR = os.path.join(app.static_folder, "uploads")   # post images
AVATAR_DIR = os.path.join(app.static_folder, "avatars")   # user avatars
ALLOWED_EXTENSIONS = {"png", "jpg", "jpeg", "gif", "webp"}


os.makedirs(UPLOAD_DIR, exist_ok=True)
os.makedirs(AVATAR_DIR, exist_ok=True)

# ---------------- Warden API (server-to-server) ----------------
WARDEN_API_URL = "http://127.0.0.1:8777"
WARDEN_API_TOKEN = "dgvdjvsoivj_fvdfk_44tljk6n3lnljfclocal"


# ---------------- DB helpers ----------------
def get_conn():
    conn = sqlite3.connect(DB_NAME)
    conn.row_factory = sqlite3.Row
    return conn


def ensure_column_exists(conn, table: str, column: str, col_def_sql: str):
    cur = conn.cursor()
    cur.execute(f"PRAGMA table_info({table})")
    cols = [r["name"] for r in cur.fetchall()]
    if column not in cols:
        cur.execute(f"ALTER TABLE {table} ADD COLUMN {col_def_sql}")
        conn.commit()


def init_db():
    conn = get_conn()
    c = conn.cursor()

    c.execute("""
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            email TEXT UNIQUE NOT NULL,
            password TEXT NOT NULL,
            role TEXT DEFAULT 'user'
        )
    """)
    ensure_column_exists(conn, "users", "nickname", "nickname TEXT")
    ensure_column_exists(conn, "users", "avatar_filename", "avatar_filename TEXT")

    c.execute("""
        CREATE TABLE IF NOT EXISTS blocks (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            title TEXT NOT NULL,
            body TEXT NOT NULL,
            is_public INTEGER DEFAULT 0
        )
    """)
    ensure_column_exists(conn, "blocks", "image_filename", "image_filename TEXT")

    c.execute("""
        CREATE TABLE IF NOT EXISTS user_feed_items (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            block_id INTEGER NOT NULL,
            sort_order INTEGER DEFAULT 0,
            UNIQUE(user_id, block_id),
            FOREIGN KEY(user_id) REFERENCES users(id),
            FOREIGN KEY(block_id) REFERENCES blocks(id)
        )
    """)

    conn.commit()
    conn.close()


def _is_admin() -> bool:
    return session.get("role") == "admin"


def _parse_int(raw, default=1):
    try:
        n = int(raw)
        return n
    except Exception:
        return default


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


def save_uploaded_image(file_storage):
    if not file_storage or file_storage.filename == "":
        return None

    filename = secure_filename(file_storage.filename)
    if not allowed_file(filename):
        raise ValueError("Недопустимый формат файла (png/jpg/jpeg/gif/webp)")

    ext = filename.rsplit(".", 1)[1].lower()
    unique_name = f"{uuid4().hex}.{ext}"

    abs_path = os.path.join(UPLOAD_DIR, unique_name)
    file_storage.save(abs_path)
    return unique_name


def delete_image_file(image_filename: str):
    if not image_filename:
        return
    abs_path = os.path.join(UPLOAD_DIR, image_filename)
    if os.path.exists(abs_path):
        try:
            os.remove(abs_path)
        except OSError:
            pass


def save_avatar(file_storage):
    if not file_storage or file_storage.filename == "":
        return None

    filename = secure_filename(file_storage.filename)
    if not allowed_file(filename):
        raise ValueError("Аватар: допустимы png/jpg/jpeg/gif/webp")

    ext = filename.rsplit(".", 1)[1].lower()
    unique_name = f"{uuid4().hex}.{ext}"

    abs_path = os.path.join(AVATAR_DIR, unique_name)
    file_storage.save(abs_path)
    return unique_name


def delete_avatar(filename: str):
    if not filename:
        return
    abs_path = os.path.join(AVATAR_DIR, filename)
    if os.path.exists(abs_path):
        try:
            os.remove(abs_path)
        except OSError:
            pass


# ---------------- Decorators ----------------
def login_required(f):
    @wraps(f)
    def decorated(*args, **kwargs):
        if "user_id" not in session:
            return redirect(url_for("login"))
        return f(*args, **kwargs)
    return decorated


def admin_required(f):
    @wraps(f)
    def decorated(*args, **kwargs):
        if "user_id" not in session:
            return redirect(url_for("login"))
        if session.get("role") != "admin":
            abort(403)
        return f(*args, **kwargs)
    return decorated


# ---------------- Routes ----------------
@app.route("/kl/")
def index():
    return redirect(url_for("feed"))


@app.route("/kl/profile", methods=["GET", "POST"])
@login_required
def profile():
    user_id = session["user_id"]

    conn = get_conn()
    c = conn.cursor()

    if request.method == "POST":
        try:
            new_avatar = save_avatar(request.files.get("avatar"))
        except ValueError as e:
            c.execute("SELECT nickname, email, avatar_filename FROM users WHERE id=?", (user_id,))
            user = c.fetchone()
            conn.close()
            return render_template("profile.html", user=user, error=str(e))

        if new_avatar:
            c.execute("SELECT avatar_filename FROM users WHERE id=?", (user_id,))
            old = c.fetchone()
            if old and old["avatar_filename"]:
                delete_avatar(old["avatar_filename"])

            c.execute("UPDATE users SET avatar_filename=? WHERE id=?", (new_avatar, user_id))
            conn.commit()
            session["avatar_filename"] = new_avatar

        conn.close()
        return redirect(url_for("profile"))

    c.execute("SELECT nickname, email, avatar_filename FROM users WHERE id=?", (user_id,))
    user = c.fetchone()
    conn.close()
    return render_template("profile.html", user=user)


@app.route("/kl/login", methods=["GET", "POST"])
def login():
    if "user_id" in session:
        return redirect(url_for("feed"))

    if request.method == "POST":
        email = request.form["email"].strip()
        password = request.form["password"]

        conn = get_conn()
        c = conn.cursor()
        c.execute(
            "SELECT id, role, email, nickname, avatar_filename, password FROM users WHERE email=?",
            (email,)
        )
        user = c.fetchone()
        conn.close()

        if not user:
            return render_template("login.html", error="Неверный логин или пароль!")

        stored = user["password"]

        if not stored.startswith("pbkdf2:sha256:") and stored == password:
            new_hash = generate_password_hash(password, method="pbkdf2:sha256", salt_length=16)
            conn2 = get_conn()
            conn2.execute("UPDATE users SET password=? WHERE id=?", (new_hash, user["id"]))
            conn2.commit()
            conn2.close()
            stored = new_hash

        if check_password_hash(stored, password):
            session["user_id"] = int(user["id"])
            session["role"] = user["role"]
            session["email"] = user["email"]
            session["nickname"] = user["nickname"]
            session["avatar_filename"] = user["avatar_filename"]
            return redirect(url_for("feed"))

        return render_template("login.html", error="Неверный логин или пароль!")

    return render_template("login.html")


@app.route("/kl/register", methods=["GET", "POST"])
def register():
    if request.method == "POST":
        nickname = request.form["nickname"].strip()
        email = request.form["email"].strip()
        password = request.form["password"]

        if len(nickname) > 16:
            return render_template("register.html", error="Никнейм должен быть не длиннее 16 символов.")

        password_hash = generate_password_hash(password, method="pbkdf2:sha256", salt_length=16)

        conn = get_conn()
        c = conn.cursor()
        try:
            c.execute(
                "INSERT INTO users (nickname, email, password) VALUES (?, ?, ?)",
                (nickname, email, password_hash)
            )
            conn.commit()
            return redirect(url_for("login"))
        except sqlite3.IntegrityError:
            return render_template("register.html", error="Пользователь с таким email уже существует!")
        finally:
            conn.close()

    return render_template("register.html")


@app.route("/kl/logout")
def logout():
    session.clear()
    return redirect(url_for("login"))


# ---------------- Main: feed ----------------
@app.route("/kl/feed")
@login_required
def feed():
    user_id = session["user_id"]

    conn = get_conn()
    c = conn.cursor()

    c.execute("SELECT * FROM blocks WHERE is_public=1 ORDER BY id DESC")
    public_blocks = c.fetchall()

    c.execute("""
        SELECT b.*
        FROM user_feed_items ufi
        JOIN blocks b ON b.id = ufi.block_id
        WHERE ufi.user_id=?
        ORDER BY ufi.id DESC
    """, (user_id,))
    personal_blocks = c.fetchall()

    blocks = list(public_blocks) + list(personal_blocks)
    conn.close()
    return render_template("feed.html", blocks=blocks)



# ---------------- Admin: users ----------------
@app.route("/kl/admin/users")
@admin_required
def admin_users():
    conn = get_conn()
    c = conn.cursor()
    c.execute("SELECT id, nickname, role FROM users ORDER BY id ASC")
    users = c.fetchall()
    conn.close()
    return render_template("admin_users.html", users=users)


# ---------------- Admin: blocks ----------------
@app.route("/kl/admin/blocks", methods=["GET", "POST"])
@admin_required
def admin_blocks():
    conn = get_conn()
    c = conn.cursor()

    if request.method == "POST":
        action = request.form.get("action")

        if action == "create":
            title = request.form["title"].strip()
            body = request.form["body"].strip()
            is_public = 1 if request.form.get("is_public") == "on" else 0

            try:
                image_filename = save_uploaded_image(request.files.get("image"))
            except ValueError as e:
                c.execute("SELECT * FROM blocks ORDER BY id DESC")
                blocks = c.fetchall()
                conn.close()
                return render_template("admin_blocks.html", blocks=blocks, error=str(e))

            c.execute(
                "INSERT INTO blocks (title, body, is_public, image_filename) VALUES (?, ?, ?, ?)",
                (title, body, is_public, image_filename)
            )
            conn.commit()

        elif action == "toggle_public":
            block_id = int(request.form["block_id"])
            c.execute("SELECT is_public FROM blocks WHERE id=?", (block_id,))
            row = c.fetchone()
            if row is not None:
                new_val = 0 if int(row["is_public"]) == 1 else 1
                c.execute("UPDATE blocks SET is_public=? WHERE id=?", (new_val, block_id))
                conn.commit()

        elif action == "delete":
            block_id = int(request.form["block_id"])

            c.execute("SELECT image_filename FROM blocks WHERE id=?", (block_id,))
            row = c.fetchone()
            if row and row["image_filename"]:
                delete_image_file(row["image_filename"])

            c.execute("DELETE FROM user_feed_items WHERE block_id=?", (block_id,))
            c.execute("DELETE FROM blocks WHERE id=?", (block_id,))
            conn.commit()

        conn.close()
        return redirect(url_for("admin_blocks"))

    c.execute("SELECT * FROM blocks ORDER BY id DESC")
    blocks = c.fetchall()
    conn.close()
    return render_template("admin_blocks.html", blocks=blocks)


# ---------------- Admin: assign blocks to user + manage profile ----------------
@app.route("/kl/admin/user/<int:user_id>/feed", methods=["GET", "POST"])
@admin_required
def admin_user_feed(user_id):
    conn = get_conn()
    c = conn.cursor()

    c.execute("SELECT id, nickname, email, role, avatar_filename FROM users WHERE id=?", (user_id,))
    user = c.fetchone()
    if not user:
        conn.close()
        abort(404)

    if request.method == "POST":
        action = request.form.get("action")

        if action == "toggle_admin":
            if user_id == session.get("user_id"):
                conn.close()
                abort(400)
            new_role = "user" if user["role"] == "admin" else "admin"
            c.execute("UPDATE users SET role=? WHERE id=?", (new_role, user_id))
            conn.commit()
            conn.close()
            return redirect(url_for("admin_user_feed", user_id=user_id))

        if action == "delete_user":
            if user_id == session.get("user_id"):
                conn.close()
                abort(400)

            if user["avatar_filename"]:
                delete_avatar(user["avatar_filename"])

            c.execute("DELETE FROM user_feed_items WHERE user_id=?", (user_id,))
            c.execute("DELETE FROM users WHERE id=?", (user_id,))
            conn.commit()
            conn.close()
            return redirect(url_for("admin_users"))

        if action == "add":
            block_id = int(request.form["block_id"])
            c.execute("SELECT id FROM user_feed_items WHERE user_id=? AND block_id=?", (user_id, block_id))
            exists = c.fetchone()
            if not exists:
                c.execute(
                    "INSERT INTO user_feed_items (user_id, block_id, sort_order) VALUES (?, ?, 0)",
                    (user_id, block_id)
                )
                conn.commit()

        elif action == "remove":
            item_id = int(request.form["item_id"])
            c.execute("DELETE FROM user_feed_items WHERE id=? AND user_id=?", (item_id, user_id))
            conn.commit()

        elif action == "clear":
            c.execute("DELETE FROM user_feed_items WHERE user_id=?", (user_id,))
            conn.commit()

        conn.close()
        return redirect(url_for("admin_user_feed", user_id=user_id))

    c.execute("""
        SELECT ufi.id as item_id, b.id as block_id, b.title
        FROM user_feed_items ufi
        JOIN blocks b ON b.id = ufi.block_id
        WHERE ufi.user_id=?
        ORDER BY ufi.id DESC
    """, (user_id,))
    items = c.fetchall()

    c.execute("SELECT id, title FROM blocks ORDER BY id DESC")
    all_blocks = c.fetchall()

    conn.close()
    return render_template("admin_user_feed.html", user=user, items=items, all_blocks=all_blocks)


# ---------------- Admin: warden db (server-to-server proxy) ----------------
@app.route("/kl/admin/warden-db")
@admin_required
def admin_warden_db():
    error = None
    data = None

    try:
        r = requests.get(
            f"{WARDEN_API_URL}/api/db",
            headers={"Authorization": f"Bearer {WARDEN_API_TOKEN}"},
            timeout=30
        )

        if r.status_code != 200:
            error = f"Warden API вернул статус {r.status_code}: {r.text[:500]}"
        else:
            payload = r.json()
            if not isinstance(payload, dict):
                error = "Warden API вернул некорректный JSON (ожидался объект-словарь)."
            else:
                normalized = {}
                for table_name, t in payload.items():
                    if isinstance(t, dict) and "columns" in t and "rows" in t:
                        normalized[table_name] = {
                            "columns": t.get("columns") or [],
                            "rows": t.get("rows") or []
                        }
                    elif isinstance(t, list):
                        rows = t
                        cols = list(rows[0].keys()) if rows and isinstance(rows[0], dict) else []
                        normalized[table_name] = {"columns": cols, "rows": rows}
                    else:
                        normalized[table_name] = {"columns": [], "rows": []}
                data = normalized

    except requests.Timeout:
        error = "Warden API не ответил за 30 секунд (timeout)."
    except requests.RequestException as e:
        error = f"Ошибка запроса к Warden API: {str(e)}"
    except ValueError:
        error = "Warden API вернул не-JSON (не удалось распарсить)."

    return render_template(
        "warden_db.html",
        data=data,
        error=error
    )


# ---------------- Run ----------------
if __name__ == "__main__":
    init_db()
    app.run(host="127.0.0.1", port=5000, debug=True)
