app.py

"""
THE Z CHAT — Backend Server
by The ZYZ Studio
Runs on Hugging Face Space (Docker)
"""

from fastapi import FastAPI, HTTPException, Depends, UploadFile, File, Form, WebSocket, WebSocketDisconnect
from fastapi.staticfiles import StaticFiles
from fastapi.responses import HTMLResponse, FileResponse
from fastapi.middleware.cors import CORSMiddleware
from pydantic import BaseModel
from typing import Optional, List
import sqlite3, hashlib, os, uuid, json, asyncio
from datetime import datetime
import shutil

app = FastAPI(title="THE Z CHAT API", version="1.0.0")

app.add_middleware(
    CORSMiddleware,
    allow_origins=["*"],
    allow_methods=["*"],
    allow_headers=["*"],
)

# ── Dirs ──────────────────────────────────────────────
os.makedirs("uploads/videos", exist_ok=True)
os.makedirs("uploads/images", exist_ok=True)
os.makedirs("uploads/audio",  exist_ok=True)
os.makedirs("uploads/avatars", exist_ok=True)
os.makedirs("static", exist_ok=True)

# ── DB ────────────────────────────────────────────────
DB = "thezchat.db"

def get_db():
    conn = sqlite3.connect(DB, check_same_thread=False)
    conn.row_factory = sqlite3.Row
    return conn

def init_db():
    conn = get_db()
    c = conn.cursor()
    c.executescript("""
    CREATE TABLE IF NOT EXISTS users (
        id         TEXT PRIMARY KEY,
        username   TEXT UNIQUE NOT NULL,
        email      TEXT UNIQUE NOT NULL,
        password   TEXT NOT NULL,
        name       TEXT NOT NULL,
        avatar     TEXT DEFAULT '',
        bio        TEXT DEFAULT '',
        verified   INTEGER DEFAULT 0,
        created_at TEXT DEFAULT CURRENT_TIMESTAMP
    );
    CREATE TABLE IF NOT EXISTS posts (
        id         TEXT PRIMARY KEY,
        user_id    TEXT NOT NULL,
        type       TEXT NOT NULL,
        caption    TEXT DEFAULT '',
        file_url   TEXT DEFAULT '',
        thumbnail  TEXT DEFAULT '',
        duration   INTEGER DEFAULT 0,
        views      INTEGER DEFAULT 0,
        created_at TEXT DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY(user_id) REFERENCES users(id)
    );
    CREATE TABLE IF NOT EXISTS likes (
        id      TEXT PRIMARY KEY,
        user_id TEXT NOT NULL,
        post_id TEXT NOT NULL,
        UNIQUE(user_id, post_id)
    );
    CREATE TABLE IF NOT EXISTS follows (
        id          TEXT PRIMARY KEY,
        follower_id TEXT NOT NULL,
        followee_id TEXT NOT NULL,
        UNIQUE(follower_id, followee_id)
    );
    CREATE TABLE IF NOT EXISTS comments (
        id         TEXT PRIMARY KEY,
        user_id    TEXT NOT NULL,
        post_id    TEXT NOT NULL,
        text       TEXT NOT NULL,
        created_at TEXT DEFAULT CURRENT_TIMESTAMP
    );
    CREATE TABLE IF NOT EXISTS messages (
        id          TEXT PRIMARY KEY,
        sender_id   TEXT NOT NULL,
        receiver_id TEXT NOT NULL,
        text        TEXT NOT NULL,
        file_url    TEXT DEFAULT '',
        file_type   TEXT DEFAULT '',
        seen        INTEGER DEFAULT 0,
        created_at  TEXT DEFAULT CURRENT_TIMESTAMP
    );
    CREATE TABLE IF NOT EXISTS stories (
        id         TEXT PRIMARY KEY,
        user_id    TEXT NOT NULL,
        file_url   TEXT DEFAULT '',
        text       TEXT DEFAULT '',
        bg_color   TEXT DEFAULT '#7c3aed',
        expires_at TEXT,
        created_at TEXT DEFAULT CURRENT_TIMESTAMP
    );
    CREATE TABLE IF NOT EXISTS notifications (
        id         TEXT PRIMARY KEY,
        user_id    TEXT NOT NULL,
        from_id    TEXT NOT NULL,
        type       TEXT NOT NULL,
        ref_id     TEXT DEFAULT '',
        seen       INTEGER DEFAULT 0,
        created_at TEXT DEFAULT CURRENT_TIMESTAMP
    );
    CREATE TABLE IF NOT EXISTS saved (
        id      TEXT PRIMARY KEY,
        user_id TEXT NOT NULL,
        post_id TEXT NOT NULL,
        UNIQUE(user_id, post_id)
    );
    CREATE TABLE IF NOT EXISTS reports (
        id         TEXT PRIMARY KEY,
        user_id    TEXT NOT NULL,
        post_id    TEXT NOT NULL,
        created_at TEXT DEFAULT CURRENT_TIMESTAMP,
        UNIQUE(user_id, post_id)
    );
    """)
    conn.commit()
    conn.close()

init_db()

# ── WebSocket Manager ─────────────────────────────────
class ConnectionManager:
    def __init__(self):
        self.active: dict[str, WebSocket] = {}

    async def connect(self, user_id: str, ws: WebSocket):
        await ws.accept()
        self.active[user_id] = ws

    def disconnect(self, user_id: str):
        self.active.pop(user_id, None)

    async def send_to(self, user_id: str, data: dict):
        ws = self.active.get(user_id)
        if ws:
            try:
                await ws.send_json(data)
            except:
                pass

manager = ConnectionManager()

# ── Helpers ───────────────────────────────────────────
def hash_pw(pw: str) -> str:
    return hashlib.sha256(pw.encode()).hexdigest()

def new_id() -> str:
    return str(uuid.uuid4())

def row_to_dict(row):
    return dict(row) if row else None

def user_public(row: dict, current_user_id: str = None, conn=None) -> dict:
    if not row: return {}
    u = dict(row)
    u.pop("password", None)
    if conn and current_user_id:
        c = conn.cursor()
        u["followers_count"] = c.execute("SELECT COUNT(*) FROM follows WHERE followee_id=?", (u["id"],)).fetchone()[0]
        u["following_count"] = c.execute("SELECT COUNT(*) FROM follows WHERE follower_id=?", (u["id"],)).fetchone()[0]
        u["posts_count"]     = c.execute("SELECT COUNT(*) FROM posts WHERE user_id=?", (u["id"],)).fetchone()[0]
        u["is_following"]    = bool(c.execute("SELECT 1 FROM follows WHERE follower_id=? AND followee_id=?", (current_user_id, u["id"])).fetchone())
    return u

# ── Models ────────────────────────────────────────────
class RegisterBody(BaseModel):
    username: str
    email: str
    password: str
    name: str

class LoginBody(BaseModel):
    email: str
    password: str

class PostCreate(BaseModel):
    user_id: str
    type: str          # photo | video | reel | audio | text | story
    caption: str = ""
    file_url: str = ""

class PostUpdate(BaseModel):
    user_id: str
    caption: str = ""
    thumbnail: str = ""

class CommentBody(BaseModel):
    user_id: str
    post_id: str
    text: str

class MessageBody(BaseModel):
    sender_id: str
    receiver_id: str
    text: str
    file_url: str = ""
    file_type: str = ""

class StoryBody(BaseModel):
    user_id: str
    text: str = ""
    bg_color: str = "#7c3aed"
    file_url: str = ""

class UpdateProfile(BaseModel):
    user_id: str
    name: str = ""
    bio: str = ""

# ═══════════════════════════════════════════════════════
#  AUTH
# ═══════════════════════════════════════════════════════
@app.post("/api/register")
def register(body: RegisterBody):
    conn = get_db()
    c = conn.cursor()
    if c.execute("SELECT 1 FROM users WHERE email=?", (body.email,)).fetchone():
        raise HTTPException(400, "البريد الإلكتروني مستخدم مسبقاً")
    if c.execute("SELECT 1 FROM users WHERE username=?", (body.username,)).fetchone():
        raise HTTPException(400, "اسم المستخدم مستخدم مسبقاً")
    uid = new_id()
    c.execute("INSERT INTO users VALUES (?,?,?,?,?,?,?,?,?)",
              (uid, body.username, body.email, hash_pw(body.password),
               body.name, "", "", 0, datetime.utcnow().isoformat()))
    conn.commit()
    u = row_to_dict(c.execute("SELECT * FROM users WHERE id=?", (uid,)).fetchone())
    conn.close()
    return {"success": True, "user": user_public(u)}

@app.post("/api/login")
def login(body: LoginBody):
    conn = get_db()
    c = conn.cursor()
    u = c.execute("SELECT * FROM users WHERE email=?", (body.email,)).fetchone()
    if not u or dict(u)["password"] != hash_pw(body.password):
        raise HTTPException(401, "البريد أو كلمة المرور غير صحيحة")
    user = row_to_dict(u)
    conn.close()
    return {"success": True, "user": user_public(user)}

@app.get("/api/user/{user_id}")
def get_user(user_id: str, current: str = ""):
    conn = get_db()
    c = conn.cursor()
    u = row_to_dict(c.execute("SELECT * FROM users WHERE id=?", (user_id,)).fetchone())
    if not u: raise HTTPException(404, "المستخدم غير موجود")
    result = user_public(u, current or user_id, conn)
    conn.close()
    return result

@app.get("/api/user/by-username/{username}")
def get_user_by_username(username: str, current: str = ""):
    conn = get_db()
    c = conn.cursor()
    u = row_to_dict(c.execute("SELECT * FROM users WHERE username=?", (username,)).fetchone())
    if not u: raise HTTPException(404, "المستخدم غير موجود")
    result = user_public(u, current or u["id"], conn)
    conn.close()
    return result

@app.put("/api/user/update")
def update_profile(body: UpdateProfile):
    conn = get_db()
    c = conn.cursor()
    if body.name:
        c.execute("UPDATE users SET name=? WHERE id=?", (body.name, body.user_id))
    if body.bio is not None:
        c.execute("UPDATE users SET bio=? WHERE id=?", (body.bio, body.user_id))
    conn.commit()
    u = row_to_dict(c.execute("SELECT * FROM users WHERE id=?", (body.user_id,)).fetchone())
    conn.close()
    return user_public(u)

@app.get("/api/search/users")
def search_users(q: str, current: str = ""):
    conn = get_db()
    c = conn.cursor()
    rows = c.execute("SELECT * FROM users WHERE username LIKE ? OR name LIKE ? LIMIT 20",
                     (f"%{q}%", f"%{q}%")).fetchall()
    conn.close()
    return [user_public(dict(r), current) for r in rows]

@app.get("/api/users/suggested")
def suggested_users(current: str, limit: int = 10):
    conn = get_db()
    c = conn.cursor()
    followed = [r[0] for r in c.execute("SELECT followee_id FROM follows WHERE follower_id=?", (current,)).fetchall()]
    excluded = followed + [current]
    placeholders = ",".join("?" * len(excluded))
    rows = c.execute(f"SELECT * FROM users WHERE id NOT IN ({placeholders}) LIMIT ?",
                     excluded + [limit]).fetchall()
    result = [user_public(dict(r), current, conn) for r in rows]
    conn.close()
    return result

# ═══════════════════════════════════════════════════════
#  UPLOAD
# ═══════════════════════════════════════════════════════
@app.post("/api/upload")
async def upload_file(file: UploadFile = File(...), kind: str = Form("video")):
    ext  = os.path.splitext(file.filename)[1].lower() if file.filename else ".bin"
    name = f"{uuid.uuid4()}{ext}"
    dirs = {"video": "uploads/videos", "image": "uploads/images",
            "audio": "uploads/audio",  "avatar": "uploads/avatars"}
    folder = dirs.get(kind, "uploads/images")
    path = os.path.join(folder, name)
    with open(path, "wb") as f:
        shutil.copyfileobj(file.file, f)
    return {"url": f"/{path}"}

# ═══════════════════════════════════════════════════════
#  POSTS
# ═══════════════════════════════════════════════════════
@app.post("/api/posts")
def create_post(body: PostCreate):
    conn = get_db()
    c = conn.cursor()
    pid = new_id()
    c.execute("INSERT INTO posts VALUES (?,?,?,?,?,?,?,?,?)",
              (pid, body.user_id, body.type, body.caption,
               body.file_url, "", 0, 0, datetime.utcnow().isoformat()))
    conn.commit()
    post = _enrich_post(pid, body.user_id, conn)
    conn.close()
    return post

@app.put("/api/posts/{post_id}")
def update_post(post_id: str, body: PostUpdate):
    conn = get_db()
    c = conn.cursor()
    p = c.execute("SELECT * FROM posts WHERE id=?", (post_id,)).fetchone()
    if not p: raise HTTPException(404, "المنشور غير موجود")
    if dict(p)["user_id"] != body.user_id: raise HTTPException(403, "غير مسموح")
    c.execute("UPDATE posts SET caption=? WHERE id=?", (body.caption, post_id))
    if body.thumbnail:
        c.execute("UPDATE posts SET thumbnail=? WHERE id=?", (body.thumbnail, post_id))
    conn.commit()
    post = _enrich_post(post_id, body.user_id, conn)
    conn.close()
    return post

def _enrich_post(post_id: str, current_user_id: str, conn):
    c = conn.cursor()
    p = row_to_dict(c.execute("SELECT * FROM posts WHERE id=?", (post_id,)).fetchone())
    if not p: return {}
    u = row_to_dict(c.execute("SELECT * FROM users WHERE id=?", (p["user_id"],)).fetchone())
    p["user"]           = user_public(u)
    p["likes_count"]    = c.execute("SELECT COUNT(*) FROM likes WHERE post_id=?", (post_id,)).fetchone()[0]
    p["comments_count"] = c.execute("SELECT COUNT(*) FROM comments WHERE post_id=?", (post_id,)).fetchone()[0]
    p["is_liked"]       = bool(c.execute("SELECT 1 FROM likes WHERE user_id=? AND post_id=?",
                                          (current_user_id, post_id)).fetchone())
    p["is_saved"]       = bool(c.execute("SELECT 1 FROM saved WHERE user_id=? AND post_id=?",
                                          (current_user_id, post_id)).fetchone())
    return p

@app.get("/api/feed")
def get_feed(user_id: str, page: int = 1, limit: int = 20):
    conn = get_db()
    c = conn.cursor()
    offset = (page - 1) * limit
    followed = [r[0] for r in c.execute("SELECT followee_id FROM follows WHERE follower_id=?", (user_id,)).fetchall()]
    ids = followed + [user_id]
    # FIX: If user follows nobody, show ALL public posts so new users see content
    if len(ids) <= 1:
        rows = c.execute("SELECT id FROM posts ORDER BY created_at DESC LIMIT ? OFFSET ?",
                         (limit, offset)).fetchall()
    else:
        placeholders = ",".join("?" * len(ids))
        rows = c.execute(f"SELECT id FROM posts WHERE user_id IN ({placeholders}) ORDER BY created_at DESC LIMIT ? OFFSET ?",
                         ids + [limit, offset]).fetchall()
    posts = [_enrich_post(r[0], user_id, conn) for r in rows]
    conn.close()
    return {"posts": posts, "page": page}

@app.get("/api/reels")
def get_reels(user_id: str, page: int = 1, limit: int = 10):
    conn = get_db()
    c = conn.cursor()
    offset = (page - 1) * limit
    rows = c.execute("SELECT id FROM posts WHERE type IN ('reel','video') ORDER BY RANDOM() LIMIT ? OFFSET ?",
                     (limit, offset)).fetchall()
    posts = [_enrich_post(r[0], user_id, conn) for r in rows]
    conn.close()
    return {"reels": posts}

@app.get("/api/explore")
def get_explore(user_id: str, page: int = 1, limit: int = 30):
    conn = get_db()
    c = conn.cursor()
    offset = (page - 1) * limit
    rows = c.execute("SELECT id FROM posts ORDER BY views DESC, created_at DESC LIMIT ? OFFSET ?",
                     (limit, offset)).fetchall()
    posts = [_enrich_post(r[0], user_id, conn) for r in rows]
    conn.close()
    return {"posts": posts}

@app.get("/api/user/{user_id}/posts")
def user_posts(user_id: str, current: str = "", page: int = 1):
    conn = get_db()
    c = conn.cursor()
    offset = (page - 1) * 18
    rows = c.execute("SELECT id FROM posts WHERE user_id=? ORDER BY created_at DESC LIMIT 18 OFFSET ?",
                     (user_id, offset)).fetchall()
    posts = [_enrich_post(r[0], current or user_id, conn) for r in rows]
    conn.close()
    return {"posts": posts}

@app.delete("/api/posts/{post_id}")
def delete_post(post_id: str, user_id: str):
    conn = get_db()
    c = conn.cursor()
    p = c.execute("SELECT * FROM posts WHERE id=?", (post_id,)).fetchone()
    if not p: raise HTTPException(404)
    if dict(p)["user_id"] != user_id: raise HTTPException(403)
    c.execute("DELETE FROM posts WHERE id=?", (post_id,))
    c.execute("DELETE FROM likes WHERE post_id=?", (post_id,))
    c.execute("DELETE FROM comments WHERE post_id=?", (post_id,))
    c.execute("DELETE FROM saved WHERE post_id=?", (post_id,))
    conn.commit()
    conn.close()
    return {"success": True}

@app.post("/api/posts/{post_id}/view")
def view_post(post_id: str):
    conn = get_db()
    conn.cursor().execute("UPDATE posts SET views=views+1 WHERE id=?", (post_id,))
    conn.commit()
    conn.close()
    return {"success": True}

# ═══════════════════════════════════════════════════════
#  LIKES
# ═══════════════════════════════════════════════════════
@app.post("/api/posts/{post_id}/like")
def toggle_like(post_id: str, user_id: str):
    conn = get_db()
    c = conn.cursor()
    existing = c.execute("SELECT 1 FROM likes WHERE user_id=? AND post_id=?", (user_id, post_id)).fetchone()
    if existing:
        c.execute("DELETE FROM likes WHERE user_id=? AND post_id=?", (user_id, post_id))
        liked = False
    else:
        c.execute("INSERT INTO likes VALUES (?,?,?)", (new_id(), user_id, post_id))
        liked = True
        p = c.execute("SELECT user_id FROM posts WHERE id=?", (post_id,)).fetchone()
        if p and dict(p)["user_id"] != user_id:
            c.execute("INSERT INTO notifications VALUES (?,?,?,?,?,?,?)",
                      (new_id(), dict(p)["user_id"], user_id, "like", post_id, 0, datetime.utcnow().isoformat()))
    count = c.execute("SELECT COUNT(*) FROM likes WHERE post_id=?", (post_id,)).fetchone()[0]
    conn.commit()
    conn.close()
    return {"liked": liked, "count": count}

# ═══════════════════════════════════════════════════════
#  COMMENTS
# ═══════════════════════════════════════════════════════
@app.post("/api/comments")
def add_comment(body: CommentBody):
    conn = get_db()
    c = conn.cursor()
    cid = new_id()
    c.execute("INSERT INTO comments VALUES (?,?,?,?,?)",
              (cid, body.user_id, body.post_id, body.text, datetime.utcnow().isoformat()))
    # Notify post owner
    p = c.execute("SELECT user_id FROM posts WHERE id=?", (body.post_id,)).fetchone()
    if p and dict(p)["user_id"] != body.user_id:
        c.execute("INSERT INTO notifications VALUES (?,?,?,?,?,?,?)",
                  (new_id(), dict(p)["user_id"], body.user_id, "comment", body.post_id, 0, datetime.utcnow().isoformat()))
    conn.commit()
    u = row_to_dict(c.execute("SELECT * FROM users WHERE id=?", (body.user_id,)).fetchone())
    conn.close()
    return {"id": cid, "user": user_public(u), "text": body.text, "created_at": datetime.utcnow().isoformat()}

@app.get("/api/posts/{post_id}/comments")
def get_comments(post_id: str):
    conn = get_db()
    c = conn.cursor()
    rows = c.execute("SELECT * FROM comments WHERE post_id=? ORDER BY created_at ASC", (post_id,)).fetchall()
    result = []
    for r in rows:
        cm = dict(r)
        u = row_to_dict(c.execute("SELECT * FROM users WHERE id=?", (cm["user_id"],)).fetchone())
        cm["user"] = user_public(u)
        result.append(cm)
    conn.close()
    return result

@app.delete("/api/comments/{comment_id}")
def delete_comment(comment_id: str, user_id: str):
    conn = get_db()
    c = conn.cursor()
    cm = c.execute("SELECT * FROM comments WHERE id=?", (comment_id,)).fetchone()
    if not cm: raise HTTPException(404)
    if dict(cm)["user_id"] != user_id: raise HTTPException(403)
    c.execute("DELETE FROM comments WHERE id=?", (comment_id,))
    conn.commit()
    conn.close()
    return {"success": True}

# ═══════════════════════════════════════════════════════
#  FOLLOW
# ═══════════════════════════════════════════════════════
@app.post("/api/follow")
def toggle_follow(follower_id: str, followee_id: str):
    if follower_id == followee_id: raise HTTPException(400)
    conn = get_db()
    c = conn.cursor()
    existing = c.execute("SELECT 1 FROM follows WHERE follower_id=? AND followee_id=?",
                          (follower_id, followee_id)).fetchone()
    if existing:
        c.execute("DELETE FROM follows WHERE follower_id=? AND followee_id=?", (follower_id, followee_id))
        following = False
    else:
        c.execute("INSERT INTO follows VALUES (?,?,?)", (new_id(), follower_id, followee_id))
        following = True
        c.execute("INSERT INTO notifications VALUES (?,?,?,?,?,?,?)",
                  (new_id(), followee_id, follower_id, "follow", "", 0, datetime.utcnow().isoformat()))
    count = c.execute("SELECT COUNT(*) FROM follows WHERE followee_id=?", (followee_id,)).fetchone()[0]
    conn.commit()
    conn.close()
    return {"following": following, "followers_count": count}

@app.get("/api/user/{user_id}/followers")
def get_followers(user_id: str, current: str = ""):
    conn = get_db()
    c = conn.cursor()
    rows = c.execute("SELECT follower_id FROM follows WHERE followee_id=?", (user_id,)).fetchall()
    result = []
    for r in rows:
        u = row_to_dict(c.execute("SELECT * FROM users WHERE id=?", (r[0],)).fetchone())
        if u: result.append(user_public(u, current, conn))
    conn.close()
    return result

@app.get("/api/user/{user_id}/following")
def get_following(user_id: str, current: str = ""):
    conn = get_db()
    c = conn.cursor()
    rows = c.execute("SELECT followee_id FROM follows WHERE follower_id=?", (user_id,)).fetchall()
    result = []
    for r in rows:
        u = row_to_dict(c.execute("SELECT * FROM users WHERE id=?", (r[0],)).fetchone())
        if u: result.append(user_public(u, current, conn))
    conn.close()
    return result

# ═══════════════════════════════════════════════════════
#  MESSAGES
# ═══════════════════════════════════════════════════════
@app.post("/api/messages")
async def send_message(body: MessageBody):
    conn = get_db()
    c = conn.cursor()
    mid = new_id()
    c.execute("INSERT INTO messages VALUES (?,?,?,?,?,?,?,?)",
              (mid, body.sender_id, body.receiver_id, body.text,
               body.file_url, body.file_type, 0, datetime.utcnow().isoformat()))
    conn.commit()
    sender = row_to_dict(c.execute("SELECT * FROM users WHERE id=?", (body.sender_id,)).fetchone())
    conn.close()
    msg = {"id": mid, "sender_id": body.sender_id, "receiver_id": body.receiver_id,
           "text": body.text, "file_url": body.file_url, "file_type": body.file_type,
           "seen": 0, "created_at": datetime.utcnow().isoformat(), "sender": user_public(sender)}
    await manager.send_to(body.receiver_id, {"type": "new_message", "message": msg})
    return msg

@app.get("/api/messages/{user1}/{user2}")
def get_messages(user1: str, user2: str, page: int = 1):
    conn = get_db()
    c = conn.cursor()
    offset = (page - 1) * 50
    rows = c.execute("""SELECT * FROM messages 
        WHERE (sender_id=? AND receiver_id=?) OR (sender_id=? AND receiver_id=?)
        ORDER BY created_at DESC LIMIT 50 OFFSET ?""",
        (user1, user2, user2, user1, offset)).fetchall()
    c.execute("""UPDATE messages SET seen=1 
        WHERE sender_id=? AND receiver_id=? AND seen=0""", (user2, user1))
    conn.commit()
    result = []
    for r in rows:
        m = dict(r)
        u = row_to_dict(c.execute("SELECT * FROM users WHERE id=?", (m["sender_id"],)).fetchone())
        m["sender"] = user_public(u)
        result.append(m)
    conn.close()
    return list(reversed(result))

@app.get("/api/conversations/{user_id}")
def get_conversations(user_id: str):
    conn = get_db()
    c = conn.cursor()
    rows = c.execute("""
        SELECT DISTINCT 
            CASE WHEN sender_id=? THEN receiver_id ELSE sender_id END AS other_id,
            MAX(created_at) as last_time
        FROM messages WHERE sender_id=? OR receiver_id=?
        GROUP BY other_id ORDER BY last_time DESC""",
        (user_id, user_id, user_id)).fetchall()
    result = []
    for r in rows:
        other_id = r["other_id"]
        u = row_to_dict(c.execute("SELECT * FROM users WHERE id=?", (other_id,)).fetchone())
        last_msg = row_to_dict(c.execute("""SELECT * FROM messages 
            WHERE (sender_id=? AND receiver_id=?) OR (sender_id=? AND receiver_id=?)
            ORDER BY created_at DESC LIMIT 1""", (user_id, other_id, other_id, user_id)).fetchone())
        unread = c.execute("SELECT COUNT(*) FROM messages WHERE sender_id=? AND receiver_id=? AND seen=0",
                            (other_id, user_id)).fetchone()[0]
        if u:
            result.append({
                "user": user_public(u),
                "last_message": last_msg,
                "unread_count": unread
            })
    conn.close()
    return result

# ═══════════════════════════════════════════════════════
#  STORIES
# ═══════════════════════════════════════════════════════
@app.post("/api/stories")
def create_story(body: StoryBody):
    conn = get_db()
    c = conn.cursor()
    sid = new_id()
    expires = datetime.utcnow().replace(hour=23, minute=59).isoformat()
    c.execute("INSERT INTO stories VALUES (?,?,?,?,?,?,?)",
              (sid, body.user_id, body.file_url, body.text, body.bg_color,
               expires, datetime.utcnow().isoformat()))
    conn.commit()
    conn.close()
    return {"id": sid, "success": True}

@app.get("/api/stories")
def get_stories(user_id: str):
    conn = get_db()
    c = conn.cursor()
    followed = [r[0] for r in c.execute("SELECT followee_id FROM follows WHERE follower_id=?", (user_id,)).fetchall()]
    ids = followed + [user_id]
    placeholders = ",".join("?" * len(ids))
    rows = c.execute(f"""SELECT s.*, u.name, u.username, u.avatar FROM stories s 
        JOIN users u ON s.user_id=u.id
        WHERE s.user_id IN ({placeholders}) AND s.expires_at > ?
        ORDER BY s.created_at DESC""",
        ids + [datetime.utcnow().isoformat()]).fetchall()
    conn.close()
    return [dict(r) for r in rows]

# ═══════════════════════════════════════════════════════
#  NOTIFICATIONS
# ═══════════════════════════════════════════════════════
@app.get("/api/notifications/{user_id}")
def get_notifications(user_id: str):
    conn = get_db()
    c = conn.cursor()
    rows = c.execute("SELECT * FROM notifications WHERE user_id=? ORDER BY created_at DESC LIMIT 50",
                     (user_id,)).fetchall()
    result = []
    for r in rows:
        n = dict(r)
        u = row_to_dict(c.execute("SELECT * FROM users WHERE id=?", (n["from_id"],)).fetchone())
        n["from_user"] = user_public(u)
        result.append(n)
    c.execute("UPDATE notifications SET seen=1 WHERE user_id=?", (user_id,))
    conn.commit()
    conn.close()
    return result

@app.get("/api/notifications/{user_id}/unread-count")
def unread_notif(user_id: str):
    conn = get_db()
    count = conn.cursor().execute("SELECT COUNT(*) FROM notifications WHERE user_id=? AND seen=0",
                                   (user_id,)).fetchone()[0]
    conn.close()
    return {"count": count}

# ═══════════════════════════════════════════════════════
#  SAVED
# ═══════════════════════════════════════════════════════
@app.post("/api/posts/{post_id}/save")
def toggle_save(post_id: str, user_id: str):
    conn = get_db()
    c = conn.cursor()
    existing = c.execute("SELECT 1 FROM saved WHERE user_id=? AND post_id=?", (user_id, post_id)).fetchone()
    if existing:
        c.execute("DELETE FROM saved WHERE user_id=? AND post_id=?", (user_id, post_id))
        saved = False
    else:
        c.execute("INSERT INTO saved VALUES (?,?,?)", (new_id(), user_id, post_id))
        saved = True
    conn.commit()
    conn.close()
    return {"saved": saved}

@app.post("/api/posts/{post_id}/report")
def report_post(post_id: str, user_id: str):
    conn = get_db()
    c = conn.cursor()
    # One report per user per post (UNIQUE constraint)
    try:
        c.execute("INSERT INTO reports VALUES (?,?,?,?)",
                  (new_id(), user_id, post_id, datetime.utcnow().isoformat()))
        conn.commit()
    except Exception:
        conn.close()
        raise HTTPException(400, "لقد أبلغت عن هذا المنشور مسبقاً")
    # Count total unique reports for this post
    count = c.execute("SELECT COUNT(*) FROM reports WHERE post_id=?", (post_id,)).fetchone()[0]
    deleted = False
    if count >= 2:
        # Auto-delete post and all related data
        c.execute("DELETE FROM posts    WHERE id=?",      (post_id,))
        c.execute("DELETE FROM likes    WHERE post_id=?", (post_id,))
        c.execute("DELETE FROM comments WHERE post_id=?", (post_id,))
        c.execute("DELETE FROM saved    WHERE post_id=?", (post_id,))
        c.execute("DELETE FROM reports  WHERE post_id=?", (post_id,))
        conn.commit()
        deleted = True
    conn.close()
    return {"reported": True, "deleted": deleted, "count": count}

@app.get("/api/user/{user_id}/saved")
def get_saved(user_id: str):
    conn = get_db()
    c = conn.cursor()
    rows = c.execute("SELECT post_id FROM saved WHERE user_id=?", (user_id,)).fetchall()
    posts = [_enrich_post(r[0], user_id, conn) for r in rows]
    conn.close()
    return posts

# ═══════════════════════════════════════════════════════
#  WEBSOCKET
# ═══════════════════════════════════════════════════════
@app.websocket("/ws/{user_id}")
async def websocket_endpoint(ws: WebSocket, user_id: str):
    await manager.connect(user_id, ws)
    try:
        while True:
            data = await ws.receive_text()
            msg = json.loads(data)

            # Ping/pong
            if msg.get("type") == "ping":
                await ws.send_json({"type": "pong"})
                continue

            # Real-time call signaling relay
            if msg.get("type", "").startswith("call_"):
                target = msg.get("to") or msg.get("target") or msg.get("target_id")
                if target:
                    payload = {
                        "type": msg["type"],
                        "from": user_id,
                        "from_name": msg.get("from_name", ""),
                        "call_id": msg.get("call_id", ""),
                        "call_type": msg.get("call_type", ""),
                        "sdp": msg.get("sdp"),
                        "candidate": msg.get("candidate"),
                        "reason": msg.get("reason", "")
                    }
                    await manager.send_to(target, payload)
    except WebSocketDisconnect:
        manager.disconnect(user_id)

# ═══════════════════════════════════════════════════════
#  STATIC
# ═══════════════════════════════════════════════════════
app.mount("/uploads", StaticFiles(directory="uploads"), name="uploads")
app.mount("/static",  StaticFiles(directory="static"),  name="static")

@app.get("/", response_class=HTMLResponse)
def root():
    with open("static/index.html", "r", encoding="utf-8") as f:
        return f.read()

@app.get("/health")
def health():
    return {"status": "ok", "app": "THE Z CHAT", "by": "The ZYZ Studio"}

# ─── Catch-all: serve index.html for any unknown path (SPA routing) ───────────
# This ensures /post/xxx and other deep links open the app instead of 404
@app.get("/{full_path:path}", response_class=HTMLResponse)
def spa_fallback(full_path: str):
    # Don't catch API/upload paths
    if full_path.startswith(("api/", "uploads/", "static/", "ws/")):
        from fastapi import Response
        return Response(status_code=404)
    with open("static/index.html", "r", encoding="utf-8") as f:
        return f.read()