import { Router } from "express";
import bcrypt from "bcryptjs";
import { db } from "@workspace/db";
import { usersTable, filesTable, conversionsTable, foldersTable, sharesTable, refreshTokensTable } from "@workspace/db";
import { eq, and, count, like, sql, ne } from "drizzle-orm";
import { requireAdmin, AuthRequest } from "../middlewares/auth.js";

function avg(col: any) { return sql<number>`AVG(${col})`; }

const router = Router();
router.use(requireAdmin);

// GET /api/admin/users
router.get("/users", async (req: AuthRequest, res) => {
  try {
    const { status, role, search } = req.query as Record<string, string>;
    const conditions: Parameters<typeof and>[0][] = [];
    if (status) conditions.push(eq(usersTable.status, status as "active" | "suspended" | "pending"));
    if (role) conditions.push(eq(usersTable.role, role as "user" | "admin"));
    if (search) conditions.push(like(usersTable.email, `%${search}%`));

    const users = conditions.length > 0
      ? await db.query.usersTable.findMany({ where: and(...conditions) })
      : await db.query.usersTable.findMany();

    const result = await Promise.all(users.map(async (u) => {
      const [{ count: fc }] = await db.select({ count: count() }).from(filesTable).where(eq(filesTable.ownerId, u.id));
      return {
        id: u.id, email: u.email, displayName: u.displayName, role: u.role, status: u.status,
        fileCount: Number(fc), lastLoginAt: u.lastLoginAt, createdAt: u.createdAt,
      };
    }));
    res.json(result);
  } catch (err) {
    req.log?.error({ err }, "admin list users error");
    res.status(500).json({ error: "server_error", message: "فشل جلب قائمة المستخدمين" });
  }
});

// POST /api/admin/users
router.post("/users", async (req: AuthRequest, res) => {
  try {
    const { email, password, displayName, role } = req.body;
    if (!email || !password) { res.status(400).json({ error: "validation", message: "البريد الإلكتروني وكلمة المرور مطلوبان" }); return; }
    if (password.length < 8) { res.status(400).json({ error: "validation", message: "كلمة المرور يجب أن تكون 8 أحرف على الأقل" }); return; }
    const existing = await db.query.usersTable.findFirst({ where: eq(usersTable.email, email.toLowerCase()) });
    if (existing) { res.status(409).json({ error: "conflict", message: "البريد الإلكتروني مستخدم بالفعل" }); return; }
    const passwordHash = await bcrypt.hash(password, 12);
    const [user] = await db.insert(usersTable).values({
      email: email.toLowerCase(), passwordHash,
      displayName: displayName || null, role: role || "user", status: "active",
    }).returning();
    res.status(201).json({ id: user.id, email: user.email, displayName: user.displayName, role: user.role, status: user.status, fileCount: 0, lastLoginAt: null, createdAt: user.createdAt });
  } catch (err) {
    req.log?.error({ err }, "admin create user error");
    res.status(500).json({ error: "server_error", message: "فشل إنشاء المستخدم" });
  }
});

// PATCH /api/admin/users/:id
router.patch("/users/:id", async (req: AuthRequest, res) => {
  try {
    const userId = req.params.id as string;
    const { role, status } = req.body;
    const user = await db.query.usersTable.findFirst({ where: eq(usersTable.id, userId) });
    if (!user) { res.status(404).json({ error: "not_found", message: "المستخدم غير موجود" }); return; }
    const [updated] = await db.update(usersTable)
      .set({ ...(role && { role }), ...(status && { status }), updatedAt: new Date() })
      .where(eq(usersTable.id, userId)).returning();
    const [{ count: fc }] = await db.select({ count: count() }).from(filesTable).where(eq(filesTable.ownerId, updated.id));
    res.json({ id: updated.id, email: updated.email, displayName: updated.displayName, role: updated.role, status: updated.status, fileCount: Number(fc), lastLoginAt: updated.lastLoginAt, createdAt: updated.createdAt });
  } catch (err) {
    req.log?.error({ err }, "admin update user error");
    res.status(500).json({ error: "server_error", message: "فشل تحديث المستخدم" });
  }
});

// DELETE /api/admin/users/:id — cascade delete all user data
router.delete("/users/:id", async (req: AuthRequest, res) => {
  try {
    const userId = req.params.id as string;

    // Guard: cannot delete your own account
    if (userId === req.userId) {
      res.status(400).json({ error: "bad_request", message: "لا يمكنك حذف حسابك الخاص" });
      return;
    }

    // Cascade: delete shares, conversions, files, folders, refresh tokens, then user
    await db.delete(sharesTable).where(eq(sharesTable.ownerId, userId));
    await db.delete(sharesTable).where(eq(sharesTable.sharedWithId, userId));

    // Get user's files to delete their conversions
    const userFiles = await db.query.filesTable.findMany({ where: eq(filesTable.ownerId, userId) });
    for (const f of userFiles) {
      await db.delete(conversionsTable).where(eq(conversionsTable.fileId, f.id));
    }

    await db.delete(filesTable).where(eq(filesTable.ownerId, userId));
    await db.delete(foldersTable).where(eq(foldersTable.ownerId, userId));
    await db.delete(refreshTokensTable).where(eq(refreshTokensTable.userId, userId));
    await db.delete(usersTable).where(eq(usersTable.id, userId));

    res.json({ message: "تم حذف المستخدم وجميع بياناته" });
  } catch (err) {
    req.log?.error({ err }, "admin delete user error");
    res.status(500).json({ error: "server_error", message: "فشل حذف المستخدم" });
  }
});

// GET /api/admin/join-requests
router.get("/join-requests", async (req: AuthRequest, res) => {
  try {
    const pending = await db.query.usersTable.findMany({ where: eq(usersTable.status, "pending") });
    res.json(pending.map((u) => ({ id: u.id, email: u.email, displayName: u.displayName, requestedAt: u.createdAt })));
  } catch (err) {
    req.log?.error({ err }, "list join requests error");
    res.status(500).json({ error: "server_error", message: "فشل جلب الطلبات" });
  }
});

// PATCH /api/admin/join-requests/:id
router.patch("/join-requests/:id", async (req: AuthRequest, res) => {
  try {
    const requestId = req.params.id as string;
    const { action } = req.body;
    if (!action || !["approve", "reject"].includes(action)) {
      res.status(400).json({ error: "validation", message: "الإجراء يجب أن يكون approve أو reject" }); return;
    }
    const user = await db.query.usersTable.findFirst({ where: eq(usersTable.id, requestId) });
    if (!user) { res.status(404).json({ error: "not_found", message: "الطلب غير موجود" }); return; }
    if (action === "approve") {
      await db.update(usersTable).set({ status: "active", updatedAt: new Date() }).where(eq(usersTable.id, requestId));
      res.json({ message: "تمت الموافقة على الطلب" });
    } else {
      await db.delete(usersTable).where(eq(usersTable.id, requestId));
      res.json({ message: "تم رفض الطلب" });
    }
  } catch (err) {
    req.log?.error({ err }, "process join request error");
    res.status(500).json({ error: "server_error", message: "فشلت العملية" });
  }
});

// GET /api/admin/stats — real computed stats
router.get("/stats", async (req: AuthRequest, res) => {
  try {
    const [{ count: totalUsers }] = await db.select({ count: count() }).from(usersTable);
    const [{ count: activeUsers }] = await db.select({ count: count() }).from(usersTable).where(eq(usersTable.status, "active"));
    const [{ count: pendingRequests }] = await db.select({ count: count() }).from(usersTable).where(eq(usersTable.status, "pending"));
    const [{ count: totalFiles }] = await db.select({ count: count() }).from(filesTable).where(eq(filesTable.status, "done"));

    const today = new Date(); today.setHours(0, 0, 0, 0);
    const weekAgo = new Date(); weekAgo.setDate(weekAgo.getDate() - 7);

    const [{ count: todayFiles }] = await db.select({ count: count() }).from(conversionsTable)
      .where(sql`${conversionsTable.createdAt} >= ${today} AND ${conversionsTable.status} = 'done'`);
    const [{ count: weekFiles }] = await db.select({ count: count() }).from(conversionsTable)
      .where(sql`${conversionsTable.createdAt} >= ${weekAgo} AND ${conversionsTable.status} = 'done'`);
    const [{ count: failedFiles }] = await db.select({ count: count() }).from(conversionsTable).where(eq(conversionsTable.status, "failed"));
    const [{ count: allConversions }] = await db.select({ count: count() }).from(conversionsTable);

    // Real averages from DB
    const [{ avg: avgQuality }] = await db.select({ avg: avg(filesTable.qualityScore) }).from(filesTable).where(eq(filesTable.status, "done"));
    const [{ avg: avgSeconds }] = await db.select({ avg: avg(conversionsTable.elapsedSeconds) }).from(conversionsTable).where(eq(conversionsTable.status, "done"));

    // Real file type distribution
    const typeRows = await db.select({ type: filesTable.originalType, cnt: count() })
      .from(filesTable)
      .where(eq(filesTable.status, "done"))
      .groupBy(filesTable.originalType);

    const conversionsByType: Record<string, number> = {};
    let topFileType = "PDF";
    let topCount = 0;
    for (const row of typeRows) {
      const label = row.type ? row.type.split("/")[1]?.toUpperCase() || row.type.toUpperCase() : "غير معروف";
      conversionsByType[label] = (conversionsByType[label] || 0) + Number(row.cnt);
      if (Number(row.cnt) > topCount) { topCount = Number(row.cnt); topFileType = label; }
    }

    const total = Number(allConversions);
    const failed = Number(failedFiles);
    const successRate = total > 0 ? ((total - failed) / total) * 100 : 0;

    res.json({
      totalUsers: Number(totalUsers),
      activeUsers: Number(activeUsers),
      pendingRequests: Number(pendingRequests),
      totalFiles: Number(totalFiles),
      filesConvertedToday: Number(todayFiles),
      filesConvertedThisWeek: Number(weekFiles),
      successRate: Math.round(successRate * 10) / 10,
      averageQualityScore: avgQuality ? Math.round(Number(avgQuality) * 10) / 10 : 0,
      averageProcessingSeconds: avgSeconds ? Math.round(Number(avgSeconds) * 10) / 10 : 0,
      topFileType,
      conversionsByType,
    });
  } catch (err) {
    req.log?.error({ err }, "get stats error");
    res.status(500).json({ error: "server_error", message: "فشل جلب الإحصائيات" });
  }
});

// GET /api/admin/trash — admin can see ALL users' trashed items
router.get("/trash", async (req: AuthRequest, res) => {
  try {
    const files = await db.query.filesTable.findMany({ where: eq(filesTable.status, "trashed") });
    const folders = await db.query.foldersTable.findMany({ where: eq(foldersTable.trashed, true) });

    const fileOwnerIds = [...new Set(files.map(f => f.ownerId))];
    const folderOwnerIds = [...new Set(folders.map(f => f.ownerId))];
    const allOwnerIds = [...new Set([...fileOwnerIds, ...folderOwnerIds])];

    const owners: Record<string, string> = {};
    for (const oid of allOwnerIds) {
      const u = await db.query.usersTable.findFirst({ where: eq(usersTable.id, oid) });
      owners[oid] = u?.email || oid;
    }

    res.json({
      files: files.map((f) => ({
        id: f.id, name: f.name, folderId: f.folderId, ownerId: f.ownerId,
        ownerEmail: owners[f.ownerId] || f.ownerId,
        originalName: f.originalName, originalType: f.originalType, sizeBytes: f.sizeBytes,
        status: f.status, qualityScore: f.qualityScore, wordCount: f.wordCount,
        language: f.language, isShared: false, sharedWith: [], permission: "owner",
        createdAt: f.createdAt, updatedAt: f.updatedAt,
      })),
      folders: folders.map((f) => ({
        id: f.id, name: f.name, parentId: f.parentId, ownerId: f.ownerId,
        ownerEmail: owners[f.ownerId] || f.ownerId,
        fileCount: 0, createdAt: f.createdAt, updatedAt: f.updatedAt,
      })),
    });
  } catch (err) {
    req.log?.error({ err }, "list trash error");
    res.status(500).json({ error: "server_error", message: "فشل جلب المهملات" });
  }
});

// DELETE /api/admin/trash/empty — admin empties their own trash only
router.delete("/trash/empty", async (req: AuthRequest, res) => {
  try {
    const userId = req.userId!;
    await db.delete(filesTable).where(and(eq(filesTable.ownerId, userId), eq(filesTable.status, "trashed")));
    await db.delete(foldersTable).where(and(eq(foldersTable.ownerId, userId), eq(foldersTable.trashed, true)));
    res.json({ message: "تم إفراغ سلة المهملات" });
  } catch (err) {
    req.log?.error({ err }, "empty trash error");
    res.status(500).json({ error: "server_error", message: "فشل إفراغ السلة" });
  }
});

export default router;