import io
import json
import os
import re
import secrets
import smtplib
import sqlite3
import threading
import time
import urllib.request
import uuid
import zipfile
from datetime import datetime, timedelta
from email.message import EmailMessage
from functools import wraps

from markupsafe import Markup

from flask import (
    Flask, render_template, request, redirect, url_for,
    flash, session, g, abort, send_file, jsonify
)
from PIL import Image, ImageOps
from werkzeug.security import generate_password_hash, check_password_hash
from werkzeug.utils import secure_filename

BASE_DIR = os.path.dirname(os.path.abspath(__file__))
UPLOAD_FOLDER = os.path.join(BASE_DIR, "static", "uploads")
BACKUP_DIR = os.path.join(BASE_DIR, "backups")
BACKUP_KEEP = 14
DATABASE = os.path.join(BASE_DIR, "gold.db")
ALLOWED_EXTENSIONS = {"png", "jpg", "jpeg", "gif", "webp"}
MAX_PHOTOS = 10
MAX_IMAGE_SIZE = 1600          # px, longest side after compression
JPEG_QUALITY = 82
RATE_LIMIT_MAX = 10            # /sell submissions per IP...
RATE_LIMIT_WINDOW = 3600       # ...per hour

app = Flask(__name__)
app.config["SECRET_KEY"] = os.environ.get("SECRET_KEY", "change-this-secret-key")
app.config["UPLOAD_FOLDER"] = UPLOAD_FOLDER
app.config["MAX_CONTENT_LENGTH"] = 32 * 1024 * 1024  # 32 MB per request

# Password for the admin pages (set ADMIN_PASSWORD env var in production)
ADMIN_PASSWORD = os.environ.get("ADMIN_PASSWORD", "gold123")

os.makedirs(UPLOAD_FOLDER, exist_ok=True)


# ---------------------------------------------------------------- database

def get_db():
    if "db" not in g:
        g.db = sqlite3.connect(DATABASE)
        g.db.row_factory = sqlite3.Row
        g.db.execute("PRAGMA foreign_keys = ON")
    return g.db


@app.teardown_appcontext
def close_db(exc):
    db = g.pop("db", None)
    if db is not None:
        db.close()


def init_db():
    with sqlite3.connect(DATABASE) as db:
        db.executescript("""
            CREATE TABLE IF NOT EXISTS listings (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                seller_name TEXT NOT NULL,
                phone TEXT NOT NULL,
                location TEXT NOT NULL,
                item_title TEXT NOT NULL,
                details TEXT NOT NULL,
                created_at TEXT NOT NULL,
                contacted INTEGER NOT NULL DEFAULT 0
            );
            CREATE TABLE IF NOT EXISTS photos (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                listing_id INTEGER NOT NULL REFERENCES listings(id) ON DELETE CASCADE,
                filename TEXT NOT NULL
            );
            CREATE TABLE IF NOT EXISTS users (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL,
                email TEXT NOT NULL UNIQUE,
                phone TEXT NOT NULL,
                password_hash TEXT NOT NULL,
                created_at TEXT NOT NULL
            );
            CREATE TABLE IF NOT EXISTS settings (
                key TEXT PRIMARY KEY,
                value TEXT NOT NULL
            );
            CREATE TABLE IF NOT EXISTS password_resets (
                token TEXT PRIMARY KEY,
                user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
                expires_at TEXT NOT NULL
            );
        """)
        # columns added after the first release
        existing = {r[1] for r in db.execute("PRAGMA table_info(listings)")}
        if "price" not in existing:
            db.execute("ALTER TABLE listings ADD COLUMN price TEXT NOT NULL DEFAULT ''")
        if "user_id" not in existing:
            db.execute("ALTER TABLE listings ADD COLUMN user_id INTEGER REFERENCES users(id)")
        user_cols = {r[1] for r in db.execute("PRAGMA table_info(users)")}
        if "is_admin" not in user_cols:
            db.execute("ALTER TABLE users ADD COLUMN is_admin INTEGER NOT NULL DEFAULT 0")
        if "status" not in existing:
            db.execute("ALTER TABLE listings ADD COLUMN status TEXT NOT NULL DEFAULT 'new'")
            db.execute("UPDATE listings SET status = 'called' WHERE contacted = 1")
        if "notes" not in existing:
            db.execute("ALTER TABLE listings ADD COLUMN notes TEXT NOT NULL DEFAULT ''")
        for col in ("offer_amount", "meeting_at", "meeting_place"):
            if col not in existing:
                db.execute(f"ALTER TABLE listings ADD COLUMN {col} TEXT NOT NULL DEFAULT ''")


init_db()


# ---------------------------------------------------------------- helpers

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


@app.template_filter("wa")
def wa_number(phone):
    """Normalise a Kenyan phone number for wa.me links."""
    digits = re.sub(r"\D", "", phone or "")
    if digits.startswith("0"):
        digits = "254" + digits[1:]
    elif len(digits) == 9 and digits.startswith(("7", "1")):
        digits = "254" + digits
    return digits


# very small in-memory rate limiters
_buckets = {}
_buckets_lock = threading.Lock()


def _limited(bucket, ip, max_hits, window):
    now = time.time()
    key = (bucket, ip)
    with _buckets_lock:
        recent = [t for t in _buckets.get(key, []) if now - t < window]
        if len(recent) >= max_hits:
            _buckets[key] = recent
            return True
        recent.append(now)
        _buckets[key] = recent
        return False


def rate_limited(ip):
    return _limited("sell", ip, RATE_LIMIT_MAX, RATE_LIMIT_WINDOW)


def chat_limited(ip):
    return _limited("chat", ip, 40, 3600)


def auth_limited(ip):
    """Login/registration attempts: 10 per 15 minutes per IP."""
    return _limited("auth", ip, 10, 900)


# ------------------------------------------------------------------- CSRF

def csrf_token():
    if "_csrf" not in session:
        session["_csrf"] = secrets.token_urlsafe(32)
    return session["_csrf"]


@app.context_processor
def inject_csrf():
    return {
        "csrf_token": csrf_token,
        "csrf_field": lambda: Markup(
            f'<input type="hidden" name="_csrf" value="{csrf_token()}">'),
    }


@app.before_request
def csrf_protect():
    if request.method == "POST":
        token = session.get("_csrf")
        sent = request.form.get("_csrf") or request.headers.get("X-CSRF-Token")
        if not token or not sent or not secrets.compare_digest(token, sent):
            abort(403, description="Invalid or missing CSRF token. "
                                   "Reload the page and try again.")


# ---------------------------------------------------------------- backups

def create_backup():
    """Zip the database and all uploaded photos into backups/."""
    os.makedirs(BACKUP_DIR, exist_ok=True)
    name = f"goldbuy-{datetime.now():%Y%m%d-%H%M%S}.zip"
    path = os.path.join(BACKUP_DIR, name)
    tmpdb = path + ".tmp.db"
    src = sqlite3.connect(DATABASE)
    dst = sqlite3.connect(tmpdb)
    with dst:
        src.backup(dst)
    dst.close()
    src.close()
    with zipfile.ZipFile(path, "w", zipfile.ZIP_DEFLATED) as z:
        z.write(tmpdb, "gold.db")
        for fn in os.listdir(UPLOAD_FOLDER):
            z.write(os.path.join(UPLOAD_FOLDER, fn), f"uploads/{fn}")
    os.remove(tmpdb)
    # keep only the newest BACKUP_KEEP archives
    zips = sorted(f for f in os.listdir(BACKUP_DIR) if f.endswith(".zip"))
    for old in zips[:-BACKUP_KEEP]:
        os.remove(os.path.join(BACKUP_DIR, old))
    return name


def _backup_loop():
    while True:
        time.sleep(24 * 3600)
        try:
            create_backup()
            print("[backup] daily backup created")
        except Exception as exc:
            print(f"[backup] daily backup failed: {exc}")


def start_backup_thread():
    # avoid double-start under the flask debug reloader
    if app.debug and os.environ.get("WERKZEUG_RUN_MAIN") != "true":
        return
    def boot():
        try:
            create_backup()
            print("[backup] startup backup created")
        except Exception as exc:
            print(f"[backup] startup backup failed: {exc}")
    threading.Thread(target=boot, daemon=True).start()
    threading.Thread(target=_backup_loop, daemon=True).start()


def save_photo(file_storage):
    """Compress an uploaded image and save it; returns the stored filename."""
    ext = file_storage.filename.rsplit(".", 1)[1].lower()
    if ext == "gif":  # keep animations untouched
        filename = secure_filename(f"{uuid.uuid4().hex}.gif")
        file_storage.save(os.path.join(app.config["UPLOAD_FOLDER"], filename))
        return filename
    try:
        img = Image.open(file_storage.stream)
        img = ImageOps.exif_transpose(img)
        if img.mode not in ("RGB", "L"):
            img = img.convert("RGB")
        img.thumbnail((MAX_IMAGE_SIZE, MAX_IMAGE_SIZE))
        filename = secure_filename(f"{uuid.uuid4().hex}.jpg")
        img.save(os.path.join(app.config["UPLOAD_FOLDER"], filename),
                 "JPEG", quality=JPEG_QUALITY, optimize=True)
        return filename
    except Exception:
        # not a readable image after all — store the raw upload
        file_storage.stream.seek(0)
        filename = secure_filename(f"{uuid.uuid4().hex}.{ext}")
        file_storage.save(os.path.join(app.config["UPLOAD_FOLDER"], filename))
        return filename


def admin_required(view):
    @wraps(view)
    def wrapped(*args, **kwargs):
        if not session.get("is_admin"):
            return redirect(url_for("admin_login", next=request.path))
        return view(*args, **kwargs)
    return wrapped


def login_required(view):
    @wraps(view)
    def wrapped(*args, **kwargs):
        if g.user is None:
            flash("Please log in first.", "error")
            return redirect(url_for("login", next=request.path))
        return view(*args, **kwargs)
    return wrapped


@app.before_request
def load_user():
    g.user = None
    user_id = session.get("user_id")
    if user_id:
        g.user = get_db().execute(
            "SELECT * FROM users WHERE id = ?", (user_id,)).fetchone()


SETTING_KEYS = ("smtp_host", "smtp_port", "smtp_user", "smtp_password",
                "smtp_security", "notify_email", "whatsapp_number",
                "gold_price_18k", "gold_price_21k", "gold_price_24k",
                "contact_phone", "service_area", "business_hours",
                "deepseek_api_key", "contact_email", "facebook_url",
                "instagram_url", "tiktok_url", "twitter_url", "youtube_url")

# lead statuses, in pipeline order
STATUSES = ("new", "called", "offer_made", "bought", "rejected")
STATUS_LABELS = {
    "new": "New lead",
    "called": "Called",
    "offer_made": "Offer made",
    "bought": "Bought",
    "rejected": "Rejected",
}
USER_STATUS_LABELS = {
    "new": "⏳ Waiting for our call",
    "called": "☎ We called you",
    "offer_made": "\U0001f4b0 Offer made — check your phone",
    "bought": "✓ Sold to us",
    "rejected": "✗ Not accepted",
}


@app.context_processor
def inject_statuses():
    return {"STATUSES": STATUSES, "STATUS_LABELS": STATUS_LABELS,
            "USER_STATUS_LABELS": USER_STATUS_LABELS}


def get_settings():
    rows = get_db().execute("SELECT key, value FROM settings").fetchall()
    return {r["key"]: r["value"] for r in rows}


def save_settings(values):
    """Insert or update only the keys present in `values`."""
    db = get_db()
    for key, value in values.items():
        db.execute(
            "INSERT INTO settings (key, value) VALUES (?, ?) "
            "ON CONFLICT(key) DO UPDATE SET value = excluded.value",
            (key, value),
        )
    db.commit()


# ------------------------------------------------------------------ email

def send_email(cfg, subject, body, to_addr=None):
    """Send an email using the admin-configured SMTP settings.
    Sends to the admin notify address unless to_addr is given.
    Raises on failure so callers can report the error."""
    host = cfg.get("smtp_host", "").strip()
    to_addr = (to_addr or cfg.get("notify_email", "")).strip()
    if not host or not to_addr:
        raise RuntimeError("SMTP is not configured (host / notify email missing).")

    security = cfg.get("smtp_security", "tls")
    port = int(cfg.get("smtp_port") or (465 if security == "ssl" else 587))
    user = cfg.get("smtp_user", "").strip()
    password = cfg.get("smtp_password", "")

    msg = EmailMessage()
    msg["Subject"] = subject
    msg["From"] = user or to_addr
    msg["To"] = to_addr
    msg.set_content(body)

    if security == "ssl":
        server = smtplib.SMTP_SSL(host, port, timeout=20)
    else:
        server = smtplib.SMTP(host, port, timeout=20)
    with server:
        if security == "tls":
            server.starttls()
        if user:
            server.login(user, password)
        server.send_message(msg)


def notify_new_listing(cfg, listing, photo_count, admin_url):
    body = (
        "A new gold item has been submitted on GoldBuy.\n\n"
        f"Item:      {listing['item_title']}\n"
        f"Asking:    KSh {listing['price']}\n"
        f"Seller:    {listing['seller_name']}\n"
        f"Phone:     {listing['phone']}\n"
        f"Location:  {listing['location']}\n"
        f"Photos:    {photo_count}\n\n"
        f"Details:\n{listing['details']}\n\n"
        f"Call the seller back on {listing['phone']}.\n"
        f"View it in the admin page: {admin_url}\n"
    )
    try:
        send_email(cfg, f"New gold item: {listing['item_title']}", body)
    except Exception as exc:  # never break a submission over a mail problem
        print(f"[email] failed to send new-listing notification: {exc}")


# what the seller is told for each status (skip 'new' — no email on submit)
SELLER_STATUS_EMAILS = {
    "called": ("We tried to reach you about your gold item",
               "Our team has reviewed your item \"{item}\" and is trying to reach "
               "you on {phone}. Please keep your line open — we would love to "
               "make you an offer."),
    "offer_made": ("We have an offer for your gold item",
                   "Good news! We have made an offer on your item \"{item}\". "
                   "Please check your phone ({phone}) for our call, or reply to "
                   "arrange the next steps."),
    "bought": ("Thank you for selling to GoldBuy",
               "Thank you! We have completed the purchase of your item \"{item}\". "
               "It was a pleasure doing business with you — tell your friends "
               "about GoldBuy!"),
    "rejected": ("An update on your gold item",
                 "Thank you for offering us your item \"{item}\". Unfortunately we "
                 "are unable to proceed with this one at the moment. You are "
                 "welcome to post other items any time."),
}


def notify_status_change(cfg, user, listing, status):
    entry = SELLER_STATUS_EMAILS.get(status)
    if not entry:
        return
    subject, template = entry
    body = (f"Hello {user['name']},\n\n"
            + template.format(item=listing["item_title"], phone=listing["phone"])
            + "\n\n— The GoldBuy team\n")
    try:
        send_email(cfg, subject, body, user["email"])
    except Exception as exc:
        print(f"[email] failed to send status update to {user['email']}: {exc}")


# ---------------------------------------------------------------- public

@app.context_processor
def inject_site_settings():
    cfg = get_settings()
    return {
        "site_whatsapp": cfg.get("whatsapp_number", "").strip(),
        "site_phone": cfg.get("contact_phone", "").strip(),
        "site_area": cfg.get("service_area", "").strip()
                     or "Nairobi & environs — we come to you",
        "site_hours": cfg.get("business_hours", "").strip()
                      or "Monday – Saturday, 8am – 6pm",
        "chat_enabled": bool(cfg.get("deepseek_api_key", "").strip()),
        "site_email": cfg.get("contact_email", "").strip(),
        "site_socials": {
            name: cfg.get(f"{name}_url", "").strip()
            for name in ("facebook", "instagram", "tiktok", "twitter", "youtube")
            if cfg.get(f"{name}_url", "").strip()
        },
    }


@app.route("/")
def index():
    cfg = get_settings()
    prices = [(k, cfg.get(f"gold_price_{k}", "").strip())
              for k in ("24k", "21k", "18k")]
    prices = [(k, v) for k, v in prices if v]

    db = get_db()
    bought = db.execute(
        "SELECT l.item_title, l.location, l.created_at, "
        "(SELECT filename FROM photos p WHERE p.listing_id = l.id LIMIT 1) photo "
        "FROM listings l WHERE l.status = 'bought' ORDER BY l.id DESC LIMIT 6"
    ).fetchall()
    return render_template("index.html", prices=prices, bought=bought)


@app.route("/faq")
def faq():
    return render_template("faq.html")


# ------------------------------------------------------------ AI support

def build_support_prompt(cfg):
    prices = ", ".join(
        f"{k}: KSh {cfg.get(f'gold_price_{k}', '').strip()}/gram"
        for k in ("24k", "21k", "18k") if cfg.get(f"gold_price_{k}", "").strip())
    contact_bits = []
    if cfg.get("contact_phone", "").strip():
        contact_bits.append(f"phone {cfg['contact_phone'].strip()}")
    if cfg.get("whatsapp_number", "").strip():
        contact_bits.append(f"WhatsApp {cfg['whatsapp_number'].strip()}")
    if cfg.get("contact_email", "").strip():
        contact_bits.append(f"email {cfg['contact_email'].strip()}")
    area = cfg.get("service_area", "").strip() or "Nairobi and environs — we come to you"
    hours = cfg.get("business_hours", "").strip() or "Monday to Saturday, 8am to 6pm"

    return f"""You are Grace, a customer support agent for GoldBuy, a Kenyan business
that buys gold from the public. You chat with website visitors.

How GoldBuy works:
- Sellers post their gold item on the website (goldbuy "Sell Your Gold" page): item
  details, asking price, location, phone number and photos. It is free.
- Our buying team reviews it and CALLS THE SELLER BACK, usually the same day.
- If we agree on a price we meet in a safe public place (bank hall, mall, an office
  the seller chooses), test the gold in front of the seller and pay on the spot via
  M-Pesa, bank transfer or cash. Quotes are free and there is no obligation to sell.
- We buy: jewellery (even broken), rings, chains, coins, bars, nuggets, gold dust,
  dental gold and gold watches — any karat, any condition.
- Pricing is by weight and purity (karat), against the day's market rate.
{f'- Today’s indicative prices: {prices}.' if prices else '- Exact daily prices are confirmed on the call.'}
- Service area: {area}. Hours: {hours}.
{f'- Direct contact: {", ".join(contact_bits)}.' if contact_bits else ''}

How to chat:
- Write like a real person on WhatsApp: short, warm, natural. 1-3 sentences per
  reply. Use the visitor's language (English or Swahili).
- Your main goal: get them to post their item via the "Sell Your Gold" page so the
  team can call them, or answer their worries (safety, pricing, process).
- NEVER invent a specific valuation for someone's item — explain it depends on
  weight and karat which we test for free when we meet.
- Never promise a specific price or that we will definitely buy.
- If asked whether you are a bot or a human: be honest that you are GoldBuy's
  virtual assistant. If a direct contact is listed above, offer it for reaching
  a human; otherwise say the team will call them once they post their item.
- NEVER make up a phone number, email or address. Only give contact details that
  are explicitly listed above.
- If a question is outside GoldBuy topics, politely steer back to gold selling.
- Do not use markdown formatting; plain chat text only."""


def call_deepseek(api_key, messages):
    payload = {
        "model": "deepseek-chat",
        "messages": messages,
        "max_tokens": 400,
        "temperature": 0.8,
    }
    req = urllib.request.Request(
        "https://api.deepseek.com/chat/completions",
        data=json.dumps(payload).encode(),
        headers={"Content-Type": "application/json",
                 "Authorization": f"Bearer {api_key}"})
    with urllib.request.urlopen(req, timeout=45) as r:
        data = json.loads(r.read().decode())
    return data["choices"][0]["message"]["content"].strip()


@app.route("/api/chat", methods=["POST"])
def api_chat():
    cfg = get_settings()
    api_key = cfg.get("deepseek_api_key", "").strip()
    if not api_key:
        return jsonify({"error": "chat is not configured"}), 503
    if chat_limited(request.remote_addr or "?"):
        return jsonify({"reply": "You have sent quite a few messages — give me a "
                                 "moment and try again shortly, or reach us on "
                                 "WhatsApp."}), 200

    data = request.get_json(silent=True) or {}
    history = data.get("messages", [])
    if not isinstance(history, list) or not history:
        abort(400)

    messages = [{"role": "system", "content": build_support_prompt(cfg)}]
    for m in history[-16:]:
        role = m.get("role")
        content = str(m.get("content", ""))[:2000].strip()
        if role in ("user", "assistant") and content:
            messages.append({"role": role, "content": content})
    if messages[-1]["role"] != "user":
        abort(400)

    try:
        reply = call_deepseek(api_key, messages)
    except Exception as exc:
        print(f"[chat] DeepSeek call failed: {exc}")
        reply = ("Sorry, I'm having a connection problem right now. "
                 "Please try again in a minute, or post your item on the "
                 "Sell Your Gold page and we will call you back.")
    return jsonify({"reply": reply})


@app.route("/sell", methods=["GET", "POST"])
def sell():
    if request.method == "POST":
        # honeypot: humans never see this field; bots fill it in
        if request.form.get("website", ""):
            return redirect(url_for("success"))
        if rate_limited(request.remote_addr or "?"):
            flash("You have sent too many items in a short time. "
                  "Please try again in an hour.", "error")
            return render_template("sell.html", form=request.form), 429

        seller_name = request.form.get("seller_name", "").strip()
        phone = request.form.get("phone", "").strip()
        location = request.form.get("location", "").strip()
        item_title = request.form.get("item_title", "").strip()
        price = request.form.get("price", "").strip()
        details = request.form.get("details", "").strip()
        files = [f for f in request.files.getlist("photos") if f and f.filename]

        errors = []
        if not seller_name:
            errors.append("Please enter your name.")
        if not phone:
            errors.append("Please enter your phone number so we can call you back.")
        if not location:
            errors.append("Please enter your location.")
        if not item_title:
            errors.append("Please enter a title for your item.")
        if not price:
            errors.append("Please enter how much you are selling the item for.")
        else:
            try:
                if float(price) <= 0:
                    errors.append("The price must be greater than zero.")
            except ValueError:
                errors.append("The price must be a number (e.g. 45000).")
        if not details:
            errors.append("Please describe your item.")
        if not files:
            errors.append("Please attach at least one picture of your item.")
        if len(files) > MAX_PHOTOS:
            errors.append(f"You can upload a maximum of {MAX_PHOTOS} pictures.")
        for f in files:
            if not allowed_file(f.filename):
                errors.append(f"'{f.filename}' is not a supported image type "
                              "(use JPG, PNG, GIF or WEBP).")

        if errors:
            for e in errors:
                flash(e, "error")
            return render_template("sell.html", form=request.form), 400

        db = get_db()
        cur = db.execute(
            """INSERT INTO listings
               (seller_name, phone, location, item_title, price, details,
                created_at, user_id)
               VALUES (?, ?, ?, ?, ?, ?, ?, ?)""",
            (seller_name, phone, location, item_title, price, details,
             datetime.now().strftime("%Y-%m-%d %H:%M"),
             g.user["id"] if g.user else None),
        )
        listing_id = cur.lastrowid

        for f in files:
            db.execute(
                "INSERT INTO photos (listing_id, filename) VALUES (?, ?)",
                (listing_id, save_photo(f)),
            )
        db.commit()

        # notify the admin by email in the background
        cfg = get_settings()
        if cfg.get("smtp_host") and cfg.get("notify_email"):
            listing = {"item_title": item_title, "price": price,
                       "seller_name": seller_name, "phone": phone,
                       "location": location, "details": details}
            admin_url = url_for("admin", _external=True)
            threading.Thread(
                target=notify_new_listing,
                args=(cfg, listing, len(files), admin_url),
                daemon=True,
            ).start()

        return redirect(url_for("success"))

    form = {}
    if g.user:
        form = {"seller_name": g.user["name"], "phone": g.user["phone"]}
    return render_template("sell.html", form=form)


@app.route("/success")
def success():
    return render_template("success.html")


# --------------------------------------------------------------- accounts

@app.route("/join", methods=["GET", "POST"])
def join():
    if g.user:
        return redirect(url_for("sell"))
    if request.method == "POST":
        if auth_limited(request.remote_addr or "?"):
            flash("Too many attempts. Please wait a few minutes and try again.",
                  "error")
            return render_template("join.html", form=request.form), 429

        name = request.form.get("name", "").strip()
        email = request.form.get("email", "").strip().lower()
        phone = request.form.get("phone", "").strip()
        password = request.form.get("password", "")
        confirm = request.form.get("confirm", "")

        errors = []
        if not name:
            errors.append("Please enter your name.")
        if not email or "@" not in email:
            errors.append("Please enter a valid email address.")
        if not phone:
            errors.append("Please enter your phone number.")
        if len(password) < 6:
            errors.append("The password must be at least 6 characters.")
        if password != confirm:
            errors.append("The two passwords do not match.")

        db = get_db()
        if not errors and db.execute(
                "SELECT 1 FROM users WHERE email = ?", (email,)).fetchone():
            errors.append("An account with that email already exists. Try logging in.")

        if errors:
            for e in errors:
                flash(e, "error")
            return render_template("join.html", form=request.form), 400

        cur = db.execute(
            "INSERT INTO users (name, email, phone, password_hash, created_at) "
            "VALUES (?, ?, ?, ?, ?)",
            (name, email, phone, generate_password_hash(password),
             datetime.now().strftime("%Y-%m-%d %H:%M")),
        )
        db.commit()
        session["user_id"] = cur.lastrowid
        flash(f"Welcome, {name}! Your account is ready.", "success")
        return redirect(url_for("sell"))

    return render_template("join.html", form={})


@app.route("/login", methods=["GET", "POST"])
def login():
    if g.user:
        return redirect(url_for("my_items"))
    if request.method == "POST":
        if auth_limited(request.remote_addr or "?"):
            flash("Too many login attempts. Please wait a few minutes and try again.",
                  "error")
            return render_template("login.html", form=request.form), 429

        email = request.form.get("email", "").strip().lower()
        password = request.form.get("password", "")
        user = get_db().execute(
            "SELECT * FROM users WHERE email = ?", (email,)).fetchone()
        if user and check_password_hash(user["password_hash"], password):
            session["user_id"] = user["id"]
            if user["is_admin"]:
                session["is_admin"] = True
            flash(f"Welcome back, {user['name']}!", "success")
            return redirect(request.args.get("next") or url_for("my_items"))
        flash("Wrong email or password.", "error")
        return render_template("login.html", form=request.form), 401
    return render_template("login.html", form={})


@app.route("/logout")
def logout():
    session.pop("user_id", None)
    session.pop("is_admin", None)
    return redirect(url_for("index"))


@app.route("/forgot-password", methods=["GET", "POST"])
def forgot_password():
    if request.method == "POST":
        email = request.form.get("email", "").strip().lower()
        db = get_db()
        user = db.execute("SELECT * FROM users WHERE email = ?",
                          (email,)).fetchone()
        cfg = get_settings()
        if user and cfg.get("smtp_host"):
            token = secrets.token_urlsafe(32)
            db.execute(
                "INSERT INTO password_resets (token, user_id, expires_at) "
                "VALUES (?, ?, ?)",
                (token, user["id"],
                 (datetime.now() + timedelta(hours=1)).strftime("%Y-%m-%d %H:%M")))
            db.commit()
            link = url_for("reset_password", token=token, _external=True)
            body = (f"Hello {user['name']},\n\n"
                    "Someone asked to reset the password for your GoldBuy account. "
                    "If this was you, open the link below (valid for 1 hour):\n\n"
                    f"{link}\n\n"
                    "If it was not you, just ignore this email.\n")
            threading.Thread(
                target=lambda: _try_send(cfg, "Reset your GoldBuy password",
                                         body, email),
                daemon=True).start()
        # same message either way, so the form can't be used to probe emails
        flash("If that email has an account, we have sent a reset link to it.",
              "success")
        return redirect(url_for("login"))
    return render_template("forgot.html")


def _try_send(cfg, subject, body, to_addr):
    try:
        send_email(cfg, subject, body, to_addr)
    except Exception as exc:
        print(f"[email] failed to send to {to_addr}: {exc}")


@app.route("/reset-password/<token>", methods=["GET", "POST"])
def reset_password(token):
    db = get_db()
    row = db.execute(
        "SELECT * FROM password_resets WHERE token = ? AND expires_at >= ?",
        (token, datetime.now().strftime("%Y-%m-%d %H:%M"))).fetchone()
    if row is None:
        flash("That reset link is invalid or has expired. Request a new one.",
              "error")
        return redirect(url_for("forgot_password"))
    if request.method == "POST":
        password = request.form.get("password", "")
        confirm = request.form.get("confirm", "")
        if len(password) < 6:
            flash("The password must be at least 6 characters.", "error")
        elif password != confirm:
            flash("The two passwords do not match.", "error")
        else:
            db.execute("UPDATE users SET password_hash = ? WHERE id = ?",
                       (generate_password_hash(password), row["user_id"]))
            db.execute("DELETE FROM password_resets WHERE user_id = ?",
                       (row["user_id"],))
            db.commit()
            flash("Password changed — you can log in now.", "success")
            return redirect(url_for("login"))
    return render_template("reset.html", token=token)


@app.route("/my-items")
@login_required
def my_items():
    db = get_db()
    listings = db.execute(
        "SELECT * FROM listings WHERE user_id = ? ORDER BY id DESC",
        (g.user["id"],)).fetchall()
    photos = {}
    for row in db.execute(
            "SELECT p.listing_id, p.filename FROM photos p "
            "JOIN listings l ON l.id = p.listing_id WHERE l.user_id = ?",
            (g.user["id"],)):
        photos.setdefault(row["listing_id"], []).append(row["filename"])
    return render_template("my_items.html", listings=listings, photos=photos)


# ---------------------------------------------------------------- admin

@app.route("/admin/login", methods=["GET", "POST"])
def admin_login():
    if request.method == "POST":
        if auth_limited(request.remote_addr or "?"):
            flash("Too many attempts. Please wait a few minutes and try again.",
                  "error")
            return render_template("admin_login.html"), 429
        if secrets.compare_digest(request.form.get("password", ""), ADMIN_PASSWORD):
            session["is_admin"] = True
            return redirect(request.args.get("next") or url_for("admin"))
        flash("Wrong password.", "error")
    return render_template("admin_login.html")


@app.route("/admin/logout")
def admin_logout():
    session.pop("is_admin", None)
    return redirect(url_for("index"))


@app.route("/admin")
@admin_required
def admin():
    db = get_db()

    total = db.execute("SELECT COUNT(*) c FROM listings").fetchone()["c"]
    by_status = {s: 0 for s in STATUSES}
    for row in db.execute("SELECT status, COUNT(*) c FROM listings GROUP BY status"):
        if row["status"] in by_status:
            by_status[row["status"]] = row["c"]
    user_count = db.execute("SELECT COUNT(*) c FROM users").fetchone()["c"]

    week_ago = (datetime.now() - timedelta(days=7)).strftime("%Y-%m-%d")
    this_week = db.execute(
        "SELECT COUNT(*) c FROM listings WHERE substr(created_at,1,10) >= ?",
        (week_ago,)).fetchone()["c"]

    pipeline_value = db.execute(
        "SELECT COALESCE(SUM(CAST(price AS REAL)), 0) v FROM listings "
        "WHERE status IN ('new','called','offer_made')").fetchone()["v"]
    bought_value = db.execute(
        "SELECT COALESCE(SUM(CAST(price AS REAL)), 0) v FROM listings "
        "WHERE status = 'bought'").fetchone()["v"]

    # submissions per day, last 14 days, for the dashboard chart
    counts = {r["d"]: r["c"] for r in db.execute(
        "SELECT substr(created_at,1,10) d, COUNT(*) c FROM listings "
        "WHERE substr(created_at,1,10) >= ? GROUP BY d",
        ((datetime.now() - timedelta(days=13)).strftime("%Y-%m-%d"),))}
    chart = []
    peak = max(counts.values(), default=0) or 1
    for i in range(13, -1, -1):
        day = datetime.now() - timedelta(days=i)
        n = counts.get(day.strftime("%Y-%m-%d"), 0)
        chart.append({"label": day.strftime("%d %b"), "count": n,
                      "pct": round(n / peak * 100)})

    recent_listings = db.execute(
        "SELECT * FROM listings ORDER BY id DESC LIMIT 6").fetchall()
    recent_users = db.execute(
        "SELECT u.*, (SELECT COUNT(*) FROM listings l WHERE l.user_id = u.id) items "
        "FROM users u ORDER BY u.id DESC LIMIT 5").fetchall()

    return render_template(
        "admin_dashboard.html", total=total, by_status=by_status,
        user_count=user_count, this_week=this_week,
        pipeline_value=pipeline_value, bought_value=bought_value,
        chart=chart, recent_listings=recent_listings, recent_users=recent_users,
        smtp_ready=bool(get_settings().get("smtp_host")))


@app.route("/admin/listings")
@admin_required
def admin_listings():
    db = get_db()
    status = request.args.get("status", "")
    q = request.args.get("q", "").strip()

    sql = "SELECT * FROM listings"
    where, params = [], []
    if status in STATUSES:
        where.append("status = ?")
        params.append(status)
    if q:
        like = f"%{q}%"
        where.append("(item_title LIKE ? OR seller_name LIKE ? "
                     "OR phone LIKE ? OR location LIKE ?)")
        params += [like, like, like, like]
    if where:
        sql += " WHERE " + " AND ".join(where)
    sql += (" ORDER BY CASE status WHEN 'new' THEN 0 WHEN 'called' THEN 1 "
            "WHEN 'offer_made' THEN 2 WHEN 'bought' THEN 3 ELSE 4 END, id DESC")
    listings = db.execute(sql, params).fetchall()

    photos = {}
    for row in db.execute("SELECT listing_id, filename FROM photos"):
        photos.setdefault(row["listing_id"], []).append(row["filename"])

    tab_counts = {"": db.execute("SELECT COUNT(*) c FROM listings").fetchone()["c"]}
    for row in db.execute("SELECT status, COUNT(*) c FROM listings GROUP BY status"):
        tab_counts[row["status"]] = row["c"]

    return render_template("admin_listings.html", listings=listings,
                           photos=photos, active_status=status, q=q,
                           tab_counts=tab_counts)


@app.route("/admin/export.xlsx")
@admin_required
def export_listings():
    from openpyxl import Workbook
    from openpyxl.styles import Font

    db = get_db()
    rows = db.execute(
        "SELECT l.*, u.email user_email FROM listings l "
        "LEFT JOIN users u ON u.id = l.user_id ORDER BY l.id").fetchall()
    photo_counts = {r["listing_id"]: r["c"] for r in db.execute(
        "SELECT listing_id, COUNT(*) c FROM photos GROUP BY listing_id")}

    wb = Workbook()
    ws = wb.active
    ws.title = "Listings"
    headers = ["ID", "Submitted", "Status", "Item", "Asking price (KSh)",
               "Seller", "Phone", "Location", "Account email", "Photos",
               "Details", "Notes"]
    ws.append(headers)
    for c in ws[1]:
        c.font = Font(bold=True)
    for l in rows:
        ws.append([l["id"], l["created_at"], STATUS_LABELS.get(l["status"], l["status"]),
                   l["item_title"], l["price"], l["seller_name"], l["phone"],
                   l["location"], l["user_email"] or "guest",
                   photo_counts.get(l["id"], 0), l["details"], l["notes"]])
    widths = [6, 17, 12, 30, 16, 20, 15, 18, 24, 8, 50, 40]
    for i, w in enumerate(widths, 1):
        ws.column_dimensions[ws.cell(row=1, column=i).column_letter].width = w
    ws.freeze_panes = "A2"

    buf = io.BytesIO()
    wb.save(buf)
    buf.seek(0)
    return send_file(
        buf, as_attachment=True,
        download_name=f"goldbuy-listings-{datetime.now():%Y-%m-%d}.xlsx",
        mimetype="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")


@app.route("/admin/status/<int:listing_id>", methods=["POST"])
@admin_required
def set_status(listing_id):
    status = request.form.get("status", "")
    if status not in STATUSES:
        abort(400)
    db = get_db()
    listing = db.execute("SELECT * FROM listings WHERE id = ?",
                         (listing_id,)).fetchone()
    if listing is None:
        abort(404)
    changed = listing["status"] != status
    db.execute("UPDATE listings SET status = ?, contacted = ? WHERE id = ?",
               (status, 0 if status == "new" else 1, listing_id))
    db.commit()

    # notify the registered seller by email when the status changes
    if changed and listing["user_id"]:
        cfg = get_settings()
        user = db.execute("SELECT * FROM users WHERE id = ?",
                          (listing["user_id"],)).fetchone()
        if user and cfg.get("smtp_host"):
            threading.Thread(
                target=notify_status_change,
                args=(cfg, dict(user), dict(listing), status),
                daemon=True).start()
    return redirect(request.form.get("next") or url_for("admin_listings"))


@app.route("/admin/deal/<int:listing_id>", methods=["POST"])
@admin_required
def save_deal(listing_id):
    db = get_db()
    if db.execute("SELECT 1 FROM listings WHERE id = ?",
                  (listing_id,)).fetchone() is None:
        abort(404)
    db.execute(
        "UPDATE listings SET offer_amount = ?, meeting_at = ?, meeting_place = ? "
        "WHERE id = ?",
        (request.form.get("offer_amount", "").strip(),
         request.form.get("meeting_at", "").strip(),
         request.form.get("meeting_place", "").strip(), listing_id))
    db.commit()
    flash(f"Deal details saved for listing #{listing_id}.", "success")
    return redirect(request.form.get("next") or url_for("admin_listings"))


@app.route("/admin/notes/<int:listing_id>", methods=["POST"])
@admin_required
def save_notes(listing_id):
    db = get_db()
    if db.execute("SELECT 1 FROM listings WHERE id = ?",
                  (listing_id,)).fetchone() is None:
        abort(404)
    db.execute("UPDATE listings SET notes = ? WHERE id = ?",
               (request.form.get("notes", "").strip(), listing_id))
    db.commit()
    flash(f"Notes saved for listing #{listing_id}.", "success")
    return redirect(request.form.get("next") or url_for("admin_listings"))


@app.route("/admin/users")
@admin_required
def admin_users():
    users = get_db().execute(
        "SELECT u.*, (SELECT COUNT(*) FROM listings l WHERE l.user_id = u.id) items "
        "FROM users u ORDER BY u.is_admin DESC, u.id DESC").fetchall()
    return render_template("admin_users.html", users=users)


@app.route("/admin/users/<int:user_id>/toggle-admin", methods=["POST"])
@admin_required
def toggle_user_admin(user_id):
    db = get_db()
    user = db.execute("SELECT * FROM users WHERE id = ?", (user_id,)).fetchone()
    if user is None:
        abort(404)
    if g.user and g.user["id"] == user_id:
        flash("You cannot remove your own admin rights.", "error")
        return redirect(url_for("admin_users"))
    db.execute("UPDATE users SET is_admin = ? WHERE id = ?",
               (0 if user["is_admin"] else 1, user_id))
    db.commit()
    flash(f"{user['name']} is {'no longer' if user['is_admin'] else 'now'} an admin.",
          "success")
    return redirect(url_for("admin_users"))


@app.route("/admin/users/<int:user_id>/delete", methods=["POST"])
@admin_required
def delete_user(user_id):
    db = get_db()
    user = db.execute("SELECT * FROM users WHERE id = ?", (user_id,)).fetchone()
    if user is None:
        abort(404)
    if g.user and g.user["id"] == user_id:
        flash("You cannot delete your own account from here.", "error")
        return redirect(url_for("admin_users"))
    # keep their listings as guest submissions
    db.execute("UPDATE listings SET user_id = NULL WHERE user_id = ?", (user_id,))
    db.execute("DELETE FROM users WHERE id = ?", (user_id,))
    db.commit()
    flash(f"Account '{user['name']}' deleted. Their listings were kept.", "success")
    return redirect(url_for("admin_users"))


@app.route("/admin/settings", methods=["GET", "POST"])
@admin_required
def admin_settings():
    if request.method == "POST":
        values = {key: request.form.get(key, "").strip()
                  for key in SETTING_KEYS if key in request.form}
        # keep stored secrets if their field was left blank
        for secret_key in ("smtp_password", "deepseek_api_key"):
            if not values.get(secret_key):
                values.pop(secret_key, None)
        save_settings(values)
        flash("Settings saved.", "success")
        return redirect(url_for("admin_settings"))
    return render_template("admin_settings.html", cfg=get_settings())


@app.route("/admin/settings/test", methods=["POST"])
@admin_required
def admin_settings_test():
    try:
        send_email(get_settings(), "GoldBuy test email",
                   "Your GoldBuy SMTP settings work. "
                   "New listings will be sent to this address.")
        flash("Test email sent — check the inbox.", "success")
    except Exception as exc:
        flash(f"Test email failed: {exc}", "error")
    return redirect(url_for("admin_settings"))


@app.route("/admin/delete/<int:listing_id>", methods=["POST"])
@admin_required
def delete_listing(listing_id):
    db = get_db()
    for row in db.execute("SELECT filename FROM photos WHERE listing_id = ?",
                          (listing_id,)):
        path = os.path.join(app.config["UPLOAD_FOLDER"], row["filename"])
        if os.path.exists(path):
            os.remove(path)
    db.execute("DELETE FROM listings WHERE id = ?", (listing_id,))
    db.commit()
    return redirect(request.form.get("next") or url_for("admin_listings"))


@app.route("/admin/backup", methods=["POST"])
@admin_required
def admin_backup():
    try:
        name = create_backup()
        flash(f"Backup created: {name}", "success")
    except Exception as exc:
        flash(f"Backup failed: {exc}", "error")
    return redirect(url_for("admin_settings"))


@app.route("/admin/backup/download")
@admin_required
def admin_backup_download():
    if not os.path.isdir(BACKUP_DIR):
        abort(404)
    zips = sorted(f for f in os.listdir(BACKUP_DIR) if f.endswith(".zip"))
    if not zips:
        try:
            create_backup()
            zips = sorted(f for f in os.listdir(BACKUP_DIR) if f.endswith(".zip"))
        except Exception:
            abort(404)
    return send_file(os.path.join(BACKUP_DIR, zips[-1]), as_attachment=True)


# --------------------------------------------------------------------- SEO

@app.route("/robots.txt")
def robots():
    lines = [
        "User-agent: *",
        "Disallow: /admin",
        "Disallow: /my-items",
        "Disallow: /api/",
        f"Sitemap: {url_for('sitemap', _external=True)}",
    ]
    return app.response_class("\n".join(lines) + "\n", mimetype="text/plain")


@app.route("/sitemap.xml")
def sitemap():
    pages = ["index", "sell", "faq", "join", "login"]
    urls = "".join(
        f"<url><loc>{url_for(p, _external=True)}</loc>"
        f"<changefreq>weekly</changefreq></url>" for p in pages)
    xml = ('<?xml version="1.0" encoding="UTF-8"?>'
           '<urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">'
           f"{urls}</urlset>")
    return app.response_class(xml, mimetype="application/xml")


start_backup_thread()


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