\
import os
import sqlite3
from datetime import datetime
from functools import wraps
from flask import Flask, render_template, request, redirect, url_for, session, flash, abort

APP_DIR = os.path.dirname(os.path.abspath(__file__))
DB_PATH = os.path.join(APP_DIR, "store.db")
UPLOAD_FOLDER = os.path.join(APP_DIR, "static", "uploads")
os.makedirs(UPLOAD_FOLDER, exist_ok=True)

def create_app():
    app = Flask(__name__)
    app.config["UPLOAD_FOLDER"] = UPLOAD_FOLDER
    app.secret_key = os.environ.get("FLASK_SECRET_KEY") or os.urandom(24)

    with app.app_context():
        init_db()

    return app

def get_db():
    conn = sqlite3.connect(DB_PATH, detect_types=sqlite3.PARSE_DECLTYPES)
    conn.row_factory = sqlite3.Row
    return conn

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

    # Users (clients)
    cur.execute("""CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        email TEXT UNIQUE,
        phone TEXT,
        balance REAL DEFAULT 0,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )""")

    # Products
    cur.execute("""CREATE TABLE IF NOT EXISTS products (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        description TEXT,
        price REAL NOT NULL,
        image_path TEXT,
        is_active INTEGER DEFAULT 1,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )""")

    # Orders
    cur.execute("""CREATE TABLE IF NOT EXISTS orders (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER NOT NULL,
        product_id INTEGER NOT NULL,
        quantity INTEGER NOT NULL DEFAULT 1,
        total_price REAL NOT NULL,
        status TEXT NOT NULL DEFAULT 'pending',
        notes TEXT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP,
        FOREIGN KEY(user_id) REFERENCES users(id),
        FOREIGN KEY(product_id) REFERENCES products(id)
    )""")

    # Payments (money added)
    cur.execute("""CREATE TABLE IF NOT EXISTS payments (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER NOT NULL,
        amount REAL NOT NULL,
        method TEXT,
        status TEXT NOT NULL DEFAULT 'added',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY(user_id) REFERENCES users(id)
    )""")

    # Ledger of money changes
    cur.execute("""CREATE TABLE IF NOT EXISTS transactions (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id INTEGER,
        type TEXT NOT NULL,
        amount REAL NOT NULL,
        reason TEXT,
        ref_type TEXT,
        ref_id INTEGER,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY(user_id) REFERENCES users(id)
    )""")

    # Service flags (including global app enabled/disabled)
    cur.execute("""CREATE TABLE IF NOT EXISTS service_flags (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT UNIQUE NOT NULL,
        is_enabled INTEGER NOT NULL DEFAULT 1
    )""")

    # Seed required flags
    cur.execute("INSERT OR IGNORE INTO service_flags (name, is_enabled) VALUES ('application', 1)")
    cur.execute("INSERT OR IGNORE INTO service_flags (name, is_enabled) VALUES ('orders', 1)")
    cur.execute("INSERT OR IGNORE INTO service_flags (name, is_enabled) VALUES ('payments', 1)")

    conn.commit()
    conn.close()

def admin_required(f):
    @wraps(f)
    def wrapper(*args, **kwargs):
        if not session.get("admin_logged_in"):
            return redirect(url_for("admin_login"))
        return f(*args, **kwargs)
    return wrapper

app = create_app()

@app.route("/")
def index():
    return redirect(url_for("admin_login"))

# --- Admin Auth ---
@app.route("/admin/login", methods=["GET", "POST"])
def admin_login():
    if request.method == "POST":
        username = request.form.get("username")
        password = request.form.get("password")
        admin_user = os.environ.get("ADMIN_USER", "admin")
        admin_pass = os.environ.get("ADMIN_PASS", "admin123")
        if username == admin_user and password == admin_pass:
            session["admin_logged_in"] = True
            flash("Welcome back!", "success")
            return redirect(url_for("admin_dashboard"))
        flash("Invalid credentials", "danger")
    return render_template("login.html")

@app.route("/admin/logout")
def admin_logout():
    session.clear()
    flash("Logged out.", "info")
    return redirect(url_for("admin_login"))

# --- Dashboard ---
@app.route("/admin")
@admin_required
def admin_dashboard():
    conn = get_db()
    cur = conn.cursor()
    stats = {}
    stats["users"] = cur.execute("SELECT COUNT(*) as c FROM users").fetchone()["c"]
    stats["products"] = cur.execute("SELECT COUNT(*) as c FROM products").fetchone()["c"]
    stats["orders_pending"] = cur.execute("SELECT COUNT(*) as c FROM orders WHERE status='pending'").fetchone()["c"]
    stats["orders_done"] = cur.execute("SELECT COUNT(*) as c FROM orders WHERE status='done'").fetchone()["c"]
    stats["payments"] = cur.execute("SELECT COUNT(*) as c FROM payments").fetchone()["c"]
    app_enabled = cur.execute("SELECT is_enabled FROM service_flags WHERE name='application'").fetchone()["is_enabled"]
    conn.close()
    return render_template("dashboard.html", stats=stats, app_enabled=bool(app_enabled))

# --- Users (Clients) ---
@app.route("/admin/clients")
@admin_required
def clients_list():
    q = request.args.get("q", "").strip()
    conn = get_db()
    cur = conn.cursor()
    if q:
        rows = cur.execute("""SELECT * FROM users WHERE name LIKE ? OR email LIKE ? OR phone LIKE ? ORDER BY created_at DESC""",
                           (f"%{q}%", f"%{q}%", f"%{q}%")).fetchall()
    else:
        rows = cur.execute("SELECT * FROM users ORDER BY created_at DESC").fetchall()
    conn.close()
    return render_template("clients_list.html", rows=rows, q=q)

@app.route("/admin/clients/new", methods=["GET", "POST"])
@admin_required
def clients_new():
    if request.method == "POST":
        name = request.form.get("name", "").strip()
        email = request.form.get("email", "").strip()
        phone = request.form.get("phone", "").strip()
        conn = get_db()
        cur = conn.cursor()
        cur.execute("INSERT INTO users (name, email, phone) VALUES (?, ?, ?)", (name, email, phone))
        conn.commit()
        conn.close()
        flash("Client created.", "success")
        return redirect(url_for("clients_list"))
    return render_template("clients_new.html")

@app.route("/admin/clients/<int:user_id>/edit", methods=["GET", "POST"])
@admin_required
def clients_edit(user_id):
    conn = get_db()
    cur = conn.cursor()
    row = cur.execute("SELECT * FROM users WHERE id=?", (user_id,)).fetchone()
    if not row:
        conn.close()
        abort(404)
    if request.method == "POST":
        name = request.form.get("name", "").strip()
        email = request.form.get("email", "").strip()
        phone = request.form.get("phone", "").strip()
        cur.execute("UPDATE users SET name=?, email=?, phone=? WHERE id=?", (name, email, phone, user_id))
        conn.commit()
        conn.close()
        flash("Client updated.", "success")
        return redirect(url_for("clients_list"))
    conn.close()
    return render_template("clients_edit.html", row=row)

# Add payment to client
@app.route("/admin/clients/<int:user_id>/add_payment", methods=["POST"])
@admin_required
def clients_add_payment(user_id):
    amount = float(request.form.get("amount", "0") or "0")
    method = request.form.get("method", "manual")
    status = "added"
    conn = get_db()
    cur = conn.cursor()
    user = cur.execute("SELECT * FROM users WHERE id=?", (user_id,)).fetchone()
    if not user:
        conn.close()
        abort(404)
    # Create payment
    cur.execute("INSERT INTO payments (user_id, amount, method, status) VALUES (?, ?, ?, ?)",
                (user_id, amount, method, status))
    payment_id = cur.lastrowid
    # Update balance
    new_balance = (user["balance"] or 0) + amount
    cur.execute("UPDATE users SET balance=? WHERE id=?", (new_balance, user_id))
    # Ledger
    cur.execute("INSERT INTO transactions (user_id, type, amount, reason, ref_type, ref_id) VALUES (?, 'add', ?, 'Payment added', 'payment', ?)",
                (user_id, amount, payment_id))
    conn.commit()
    conn.close()
    flash(f"Payment of {amount:.2f} added to {user['name']}'s balance.", "success")
    return redirect(url_for("clients_list"))

# --- Products CRUD ---
@app.route("/admin/products")
@admin_required
def products_list():
    conn = get_db()
    cur = conn.cursor()
    rows = cur.execute("SELECT * FROM products ORDER BY created_at DESC").fetchall()
    conn.close()
    return render_template("products_list.html", rows=rows)

@app.route("/admin/products/new", methods=["GET", "POST"])
@admin_required
def products_new():
    if request.method == "POST":
        name = request.form.get("name", "").strip()
        description = request.form.get("description", "").strip()
        price = float(request.form.get("price", "0") or "0")
        image_path = None
        file = request.files.get("image")
        if file and file.filename:
            filename = f"{int(datetime.now().timestamp())}_{file.filename}"
            save_path = os.path.join(UPLOAD_FOLDER, filename)
            file.save(save_path)
            image_path = f"uploads/{filename}"
        conn = get_db()
        cur = conn.cursor()
        cur.execute("INSERT INTO products (name, description, price, image_path) VALUES (?, ?, ?, ?)",
                    (name, description, price, image_path))
        conn.commit()
        conn.close()
        flash("Product created.", "success")
        return redirect(url_for("products_list"))
    return render_template("products_new.html")

@app.route("/admin/products/<int:product_id>/edit", methods=["GET", "POST"])
@admin_required
def products_edit(product_id):
    conn = get_db()
    cur = conn.cursor()
    row = cur.execute("SELECT * FROM products WHERE id=?", (product_id,)).fetchone()
    if not row:
        conn.close()
        abort(404)
    if request.method == "POST":
        name = request.form.get("name", "").strip()
        description = request.form.get("description", "").strip()
        price = float(request.form.get("price", "0") or "0")
        is_active = 1 if request.form.get("is_active") == "on" else 0
        image_path = row["image_path"]
        file = request.files.get("image")
        if file and file.filename:
            filename = f"{int(datetime.now().timestamp())}_{file.filename}"
            save_path = os.path.join(UPLOAD_FOLDER, filename)
            file.save(save_path)
            image_path = f"uploads/{filename}"
        cur.execute("UPDATE products SET name=?, description=?, price=?, image_path=?, is_active=? WHERE id=?",
                    (name, description, price, image_path, is_active, product_id))
        conn.commit()
        conn.close()
        flash("Product updated.", "success")
        return redirect(url_for("products_list"))
    conn.close()
    return render_template("products_edit.html", row=row)

@app.route("/admin/products/<int:product_id>/delete", methods=["POST"])
@admin_required
def products_delete(product_id):
    conn = get_db()
    cur = conn.cursor()
    cur.execute("DELETE FROM products WHERE id=?", (product_id,))
    conn.commit()
    conn.close()
    flash("Product deleted.", "info")
    return redirect(url_for("products_list"))

# --- Orders ---
@app.route("/admin/orders")
@admin_required
def orders_list():
    status = request.args.get("status", "").strip()
    conn = get_db()
    cur = conn.cursor()
    if status:
        rows = cur.execute("SELECT o.*, u.name as user_name, p.name as product_name FROM orders o JOIN users u ON o.user_id=u.id JOIN products p ON o.product_id=p.id WHERE o.status=? ORDER BY o.created_at DESC", (status,)).fetchall()
    else:
        rows = cur.execute("SELECT o.*, u.name as user_name, p.name as product_name FROM orders o JOIN users u ON o.user_id=u.id JOIN products p ON o.product_id=p.id ORDER BY o.created_at DESC").fetchall()
    conn.close()
    return render_template("orders_list.html", rows=rows, status=status)

@app.route("/admin/orders/new", methods=["GET", "POST"])
@admin_required
def orders_new():
    conn = get_db()
    cur = conn.cursor()
    users = cur.execute("SELECT * FROM users ORDER BY name").fetchall()
    products = cur.execute("SELECT * FROM products WHERE is_active=1 ORDER BY name").fetchall()
    if request.method == "POST":
        user_id = int(request.form.get("user_id"))
        product_id = int(request.form.get("product_id"))
        quantity = int(request.form.get("quantity", "1"))
        notes = request.form.get("notes", "").strip()
        product = cur.execute("SELECT * FROM products WHERE id=?", (product_id,)).fetchone()
        user = cur.execute("SELECT * FROM users WHERE id=?", (user_id,)).fetchone()
        if not product or not user:
            conn.close()
            abort(400)
        total_price = (product["price"] or 0) * quantity
        status = "pending"
        cur.execute("INSERT INTO orders (user_id, product_id, quantity, total_price, status, notes) VALUES (?, ?, ?, ?, ?, ?)",
                    (user_id, product_id, quantity, total_price, status, notes))
        order_id = cur.lastrowid
        conn.commit()
        conn.close()
        flash("Order created (pending).", "success")
        return redirect(url_for("orders_list"))
    conn.close()
    return render_template("orders_new.html", users=users, products=products)

def _update_order_status(order_id, new_status, deduct_balance=False, refund_balance=False):
    conn = get_db()
    cur = conn.cursor()
    order = cur.execute("SELECT * FROM orders WHERE id=?", (order_id,)).fetchone()
    if not order:
        conn.close()
        abort(404)
    user = cur.execute("SELECT * FROM users WHERE id=?", (order["user_id"],)).fetchone()

    # Balance operations
    if deduct_balance and user:
        new_balance = (user["balance"] or 0) - (order["total_price"] or 0)
        cur.execute("UPDATE users SET balance=? WHERE id=?", (new_balance, user["id"]))
        cur.execute("INSERT INTO transactions (user_id, type, amount, reason, ref_type, ref_id) VALUES (?, 'deduct', ?, 'Order accepted', 'order', ?)",
                    (user["id"], order["total_price"], order_id))

    if refund_balance and user:
        new_balance = (user["balance"] or 0) + (order["total_price"] or 0)
        cur.execute("UPDATE users SET balance=? WHERE id=?", (new_balance, user["id"]))
        cur.execute("INSERT INTO transactions (user_id, type, amount, reason, ref_type, ref_id) VALUES (?, 'add', ?, 'Order cancelled/refunded', 'order', ?)",
                    (user["id"], order["total_price"], order_id))

    cur.execute("UPDATE orders SET status=?, updated_at=CURRENT_TIMESTAMP WHERE id=?", (new_status, order_id))
    conn.commit()
    conn.close()

@app.route("/admin/orders/<int:order_id>/accept", methods=["POST"])
@admin_required
def orders_accept(order_id):
    _update_order_status(order_id, "accepted", deduct_balance=True)
    flash("Order accepted and user balance deducted.", "success")
    return redirect(url_for("orders_list"))

@app.route("/admin/orders/<int:order_id>/reject", methods=["POST"])
@admin_required
def orders_reject(order_id):
    _update_order_status(order_id, "rejected")
    flash("Order rejected.", "info")
    return redirect(url_for("orders_list"))

@app.route("/admin/orders/<int:order_id>/done", methods=["POST"])
@admin_required
def orders_done(order_id):
    _update_order_status(order_id, "done")
    flash("Order marked as done.", "success")
    return redirect(url_for("orders_list"))

@app.route("/admin/orders/<int:order_id>/cancel", methods=["POST"])
@admin_required
def orders_cancel(order_id):
    _update_order_status(order_id, "cancelled", refund_balance=True)
    flash("Order cancelled and refunded to user balance.", "warning")
    return redirect(url_for("orders_list"))

# --- Payments & Money views ---
@app.route("/admin/payments")
@admin_required
def payments_list():
    conn = get_db()
    cur = conn.cursor()
    rows = cur.execute("""
        SELECT p.*, u.name as user_name 
        FROM payments p JOIN users u ON p.user_id=u.id
        ORDER BY p.created_at DESC
    """).fetchall()
    conn.close()
    return render_template("payments_list.html", rows=rows)

@app.route("/admin/money/added")
@admin_required
def money_added():
    conn = get_db()
    cur = conn.cursor()
    rows = cur.execute("""
        SELECT t.*, u.name as user_name 
        FROM transactions t LEFT JOIN users u ON t.user_id=u.id
        WHERE t.type='add' AND t.ref_type='payment'
        ORDER BY t.created_at DESC
    """).fetchall()
    conn.close()
    return render_template("money_added.html", rows=rows)

@app.route("/admin/money/changes")
@admin_required
def money_changes():
    conn = get_db()
    cur = conn.cursor()
    rows = cur.execute("""
        SELECT t.*, u.name as user_name 
        FROM transactions t LEFT JOIN users u ON t.user_id=u.id
        ORDER BY t.created_at DESC
    """).fetchall()
    conn.close()
    return render_template("money_changes.html", rows=rows)

# --- Service Flags (Application disable / stop any service) ---
@app.route("/admin/services", methods=["GET", "POST"])
@admin_required
def services_flags():
    conn = get_db()
    cur = conn.cursor()
    if request.method == "POST":
        for name in ["application", "orders", "payments"]:
            val = 1 if request.form.get(name) == "on" else 0
            cur.execute("UPDATE service_flags SET is_enabled=? WHERE name=?", (val, name))
        conn.commit()
        flash("Service flags updated.", "success")
    rows = cur.execute("SELECT * FROM service_flags ORDER BY name").fetchall()
    conn.close()
    return render_template("services_flags.html", rows=rows)

# --- Utility: create a quick dummy dataset ---
@app.route("/admin/dev/seed")
@admin_required
def dev_seed():
    conn = get_db()
    cur = conn.cursor()

    # Create users
    cur.execute("INSERT INTO users (name, email, phone, balance) VALUES ('Ali', 'ali@example.com', '70123456', 50)")
    cur.execute("INSERT INTO users (name, email, phone, balance) VALUES ('Sara', 'sara@example.com', '76111222', 20)")

    # Create products
    cur.execute("INSERT INTO products (name, description, price, is_active) VALUES ('VIP Box Basic', 'Basic package', 10, 1)")
    cur.execute("INSERT INTO products (name, description, price, is_active) VALUES ('VIP Box Pro', 'Pro package', 25, 1)")

    conn.commit()
    conn.close()
    flash("Seeded some dummy users and products.", "info")
    return redirect(url_for("admin_dashboard"))

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