import { Router } from "express";
import { z } from "zod";
import { Property, City, Review, User, PropertyUpdate } from "@/db/models";
import { createId } from "@/db/ids";
import { AppError, asyncHandler } from "@/lib/errors";
import { requireAuth, requireRoles } from "@/middleware/auth";
import { serializeProperty, toPlain } from "@/lib/serialize";
import { findCitiesByQuery } from "@/lib/cities";
import { busyPropertyIds, unavailableDatesForProperty } from "@/services/availability";
import {
  isAuthMysql,
  isCitiesMysql,
  isPropertiesMysql,
  isReviewsMysql,
} from "@/db/activeDatabase";
import {
  cityRowToApi,
  findCitiesByIdsMysql,
  findCityByIdMysql,
} from "@/db/mysql/cities";
import {
  createPropertyMysql,
  createPropertyUpdateMysql,
  findPropertyByIdMysql,
  listPropertiesMysql,
  listPropertyUpdatesMysql,
  propertyToApi,
  propertyUpdateToApi,
  softDeletePropertyMysql,
  softDeletePropertyUpdateMysql,
  updatePropertyMysql,
} from "@/db/mysql/properties";
import {
  latestCommentedReviewsMysql,
  listReviewsByPropertyMysql,
  reviewStatsByPropertyIdsMysql,
  reviewToApi,
} from "@/db/mysql/reviews";
import { findUsersByIdsMysql } from "@/db/mysql/users";
import { isStaffRole } from "@/lib/auth/rbac";

export const propertiesRouter = Router();

const PropertyBodySchema = z.object({
  cityId: z.string().min(1),
  titleAr: z.string().trim().min(2),
  titleEn: z.string().trim().min(2),
  slug: z.string().min(3).optional(),
  descriptionAr: z.string().trim().min(5),
  descriptionEn: z.string().trim().min(5),
  address: z.string().trim().min(3),
  latitude: z.number().optional().nullable(),
  longitude: z.number().optional().nullable(),
  bedrooms: z.coerce.number().int().min(0),
  bathrooms: z.coerce.number().int().min(0),
  maxGuests: z.coerce.number().int().min(1),
  wifi: z.boolean().optional(),
  parking: z.boolean().optional(),
  airConditioning: z.boolean().optional(),
  kitchen: z.boolean().optional(),
  hospitalNearby: z.boolean().optional(),
  universityNearby: z.boolean().optional(),
  petFriendly: z.boolean().optional(),
  instantBooking: z.boolean().optional(),
  basePriceTnd: z.coerce.number().positive(),
  cleaningFeeTnd: z.coerce.number().min(0).optional(),
  checkInTime: z
    .string()
    .regex(/^([01]\d|2[0-3]):[0-5]\d$/, "Invalid check-in time")
    .optional()
    .default("15:00"),
  checkOutTime: z
    .string()
    .regex(/^([01]\d|2[0-3]):[0-5]\d$/, "Invalid check-out time")
    .optional()
    .default("11:00"),
  cancellationPolicy: z.string().min(5).optional().default("Free cancellation within 48 hours."),
  houseRules: z.string().optional().nullable(),
  featured: z.boolean().optional(),
  status: z.enum(["DRAFT", "PUBLISHED", "PAUSED", "REJECTED"]).optional(),
  images: z
    .array(z.object({ url: z.string().min(1), sortOrder: z.number().optional() }))
    .optional(),
  videos: z
    .array(z.object({ url: z.string().min(1), sortOrder: z.number().optional() }))
    .optional(),
  blockedDates: z.array(z.string().regex(/^\d{4}-\d{2}-\d{2}$/)).optional(),
});

function slugify(input: string) {
  return input
    .toLowerCase()
    .replace(/[^a-z0-9]+/g, "-")
    .replace(/(^-|-$)/g, "")
    .slice(0, 60);
}

async function attachCitiesMongo(properties: any[]) {
  const cityIds = [...new Set(properties.map((p) => p.cityId).filter(Boolean))];
  const cities = await City.find({ _id: { $in: cityIds } }).lean();
  const byId = new Map(cities.map((c) => [c._id, c]));
  return properties.map((p) => ({ ...p, city: byId.get(p.cityId) }));
}

async function attachCitiesForMysql(properties: ReturnType<typeof propertyToApi>[]): Promise<any[]> {
  const cityIds = [...new Set(properties.map((p) => p.cityId).filter(Boolean))];
  if (isCitiesMysql()) {
    const cities = await findCitiesByIdsMysql(cityIds);
    const byId = new Map(cities.map((c) => [c.id, cityRowToApi(c)]));
    return properties.map((p) => ({ ...p, city: byId.get(p.cityId) }));
  }
  const cities = await City.find({ _id: { $in: cityIds } }).lean();
  const byId = new Map(cities.map((c) => [c._id, c]));
  return properties.map((p) => ({ ...p, city: byId.get(p.cityId) }));
}

propertiesRouter.get(
  "/",
  asyncHandler(async (req, res) => {
    const q = z
      .object({
        ids: z.string().optional(),
        city: z.string().optional(),
        cityId: z.string().optional(),
        guests: z.coerce.number().optional(),
        instantBooking: z.enum(["true", "false"]).optional(),
        featured: z.enum(["true", "false"]).optional(),
        minPrice: z.coerce.number().optional(),
        maxPrice: z.coerce.number().optional(),
        wifi: z.enum(["true", "false"]).optional(),
        parking: z.enum(["true", "false"]).optional(),
        checkIn: z.string().optional(),
        checkOut: z.string().optional(),
        ownerId: z.string().optional(),
        status: z.string().optional(),
        take: z.coerce.number().max(100).optional(),
        skip: z.coerce.number().min(0).optional(),
      })
      .parse(req.query);

    if (isPropertiesMysql()) {
      const idList = (q.ids || "")
        .split(",")
        .map((s) => s.trim())
        .filter(Boolean)
        .slice(0, 3);

      let cityMatch: { id: string; nameEn: string; nameAr: string } | null | undefined =
        undefined;
      let cityIds: string[] | undefined;

      if (!idList.length) {
        if (q.cityId) {
          const city = isCitiesMysql()
            ? await findCityByIdMysql(q.cityId)
            : await City.findOne({ _id: q.cityId, deletedAt: null }).lean();
          if (!city) {
            return res.json({ properties: [], cityMatch: null, total: 0, _db: "mysql" });
          }
          const id = "id" in city ? String((city as any).id ?? (city as any)._id) : String((city as any)._id);
          const nameEn = String((city as any).nameEn);
          const nameAr = String((city as any).nameAr);
          cityIds = [id];
          cityMatch = { id, nameEn, nameAr };
        } else if (q.city) {
          const cities = await findCitiesByQuery(q.city);
          if (cities.length === 0) {
            return res.json({ properties: [], cityMatch: null, total: 0, _db: "mysql" });
          }
          cityIds = cities.map((c: any) => String(c.id ?? c._id));
          cityMatch =
            cities.length === 1
              ? {
                  id: String((cities[0] as any).id ?? (cities[0] as any)._id),
                  nameEn: cities[0].nameEn,
                  nameAr: cities[0].nameAr,
                }
              : undefined;
        }
      }

      let excludeIds: string[] | undefined;
      if (!idList.length && q.checkIn && q.checkOut) {
        excludeIds = await busyPropertyIds(new Date(q.checkIn), new Date(q.checkOut));
      }

      const take = idList.length ? idList.length : (q.take ?? 50);
      const skip = idList.length ? 0 : (q.skip ?? 0);
      const status = idList.length
        ? "PUBLISHED"
        : q.ownerId
          ? q.status
          : "PUBLISHED";

      const { properties, total } = await listPropertiesMysql({
        ids: idList.length ? idList : undefined,
        cityIds,
        ownerId: q.ownerId,
        status,
        guests: q.guests,
        instantBooking: q.instantBooking === "true",
        featured: q.featured === "true",
        wifi: q.wifi === "true",
        parking: q.parking === "true",
        minPrice: q.minPrice,
        maxPrice: q.maxPrice,
        excludeIds,
        take,
        skip,
      });

      let apiProps: any[] = properties.map((p) => propertyToApi(p));
      apiProps = await attachCitiesForMysql(apiProps);
      const ids = apiProps.map((p) => p.id);

      let statsMap = new Map<string, { count: number; averageRating: number }>();
      let latestMap = new Map<string, any>();
      let authorMap = new Map<string, string>();

      if (isReviewsMysql()) {
        const stats = await reviewStatsByPropertyIdsMysql(ids);
        statsMap = new Map(stats.map((s) => [s.propertyId, s]));
        const latest = await latestCommentedReviewsMysql(ids);
        latestMap = new Map(latest.map((r) => [r.propertyId, r]));
        const authorIds = [...new Set(latest.map((r) => r.authorId))];
        const authors = await findUsersByIdsMysql(authorIds);
        authorMap = new Map(authors.map((a) => [a.id, a.fullName]));
      } else {
        const [reviewStats, latestReviews] = await Promise.all([
          Review.aggregate([
            { $match: { propertyId: { $in: ids } } },
            {
              $group: {
                _id: "$propertyId",
                count: { $sum: 1 },
                averageRating: { $avg: "$rating" },
              },
            },
          ]),
          Review.aggregate([
            { $match: { propertyId: { $in: ids }, comment: { $exists: true, $nin: [null, ""] } } },
            { $sort: { createdAt: -1 } },
            {
              $group: {
                _id: "$propertyId",
                comment: { $first: "$comment" },
                rating: { $first: "$rating" },
                authorId: { $first: "$authorId" },
              },
            },
          ]),
        ]);
        statsMap = new Map(
          reviewStats.map((r: any) => [
            r._id,
            { count: r.count, averageRating: r.averageRating },
          ]),
        );
        latestMap = new Map(latestReviews.map((r: any) => [r._id, r]));
        const authorIds = [...new Set(latestReviews.map((r: any) => r.authorId).filter(Boolean))];
        const authors = authorIds.length
          ? await User.find({ _id: { $in: authorIds } }).select("fullName").lean()
          : [];
        authorMap = new Map(authors.map((a) => [a._id, a.fullName]));
      }

      const serialized = apiProps.map((p) => {
        const stats = statsMap.get(p.id);
        const latest = latestMap.get(p.id);
        return serializeProperty({
          ...p,
          _count: { reviews: stats?.count ?? 0 },
          averageRating: stats?.averageRating
            ? Number(Number(stats.averageRating).toFixed(1))
            : 0,
          latestReview: latest
            ? {
                comment: latest.comment,
                rating: latest.rating,
                authorName: authorMap.get(latest.authorId) || undefined,
              }
            : null,
        });
      });

      res.json({
        properties: serialized,
        total,
        ...(cityMatch !== undefined ? { cityMatch } : {}),
        _db: "mysql",
      });
      return;
    }

    const filter: Record<string, any> = { deletedAt: null };
    let cityMatch: { id: string; nameEn: string; nameAr: string } | null | undefined = undefined;

    const idList = (q.ids || "")
      .split(",")
      .map((s) => s.trim())
      .filter(Boolean)
      .slice(0, 3);

    if (idList.length) {
      filter._id = { $in: idList };
      filter.status = "PUBLISHED";
    } else if (q.ownerId) {
      filter.ownerId = q.ownerId;
      if (q.status) filter.status = q.status;
    } else {
      filter.status = "PUBLISHED";
    }

    if (!idList.length) {
      if (q.cityId) {
        const city = await City.findOne({ _id: q.cityId, deletedAt: null }).lean();
        if (!city) {
          return res.json({ properties: [], cityMatch: null, total: 0, _db: "mongodb" });
        }
        filter.cityId = city._id;
        cityMatch = { id: city._id, nameEn: city.nameEn, nameAr: city.nameAr };
      } else if (q.city) {
        const cities = await findCitiesByQuery(q.city);
        if (cities.length === 0) {
          return res.json({ properties: [], cityMatch: null, total: 0, _db: "mongodb" });
        }
        filter.cityId = { $in: cities.map((c: any) => c._id ?? c.id) };
        cityMatch =
          cities.length === 1
            ? {
                id: (cities[0] as any)._id ?? (cities[0] as any).id,
                nameEn: cities[0].nameEn,
                nameAr: cities[0].nameAr,
              }
            : undefined;
      }
      if (q.guests) filter.maxGuests = { $gte: q.guests };
      if (q.instantBooking === "true") filter.instantBooking = true;
      if (q.featured === "true") filter.featured = true;
      if (q.wifi === "true") filter.wifi = true;
      if (q.parking === "true") filter.parking = true;
      if (q.minPrice || q.maxPrice) {
        filter.basePriceTnd = {};
        if (q.minPrice) filter.basePriceTnd.$gte = q.minPrice;
        if (q.maxPrice) filter.basePriceTnd.$lte = q.maxPrice;
      }

      if (q.checkIn && q.checkOut) {
        const busyIds = await busyPropertyIds(new Date(q.checkIn), new Date(q.checkOut));
        filter._id = { ...(filter._id || {}), $nin: busyIds };
      }
    }

    const take = idList.length ? idList.length : (q.take ?? 50);
    const skip = idList.length ? 0 : (q.skip ?? 0);
    const [found, totalCount] = await Promise.all([
      Property.find(filter)
        .sort(idList.length ? { createdAt: -1 } : { featured: -1, createdAt: -1 })
        .skip(skip)
        .limit(take)
        .lean(),
      idList.length ? Promise.resolve(idList.length) : Property.countDocuments(filter),
    ]);
    let properties = found;

    if (idList.length) {
      const byId = new Map(properties.map((p) => [String(p._id), p]));
      properties = idList.map((id) => byId.get(id)).filter(Boolean) as typeof properties;
    }

    properties = await attachCitiesMongo(properties);

    const ids = properties.map((p) => p._id);
    const [reviewStats, latestReviews] = await Promise.all([
      isReviewsMysql()
        ? reviewStatsByPropertyIdsMysql(ids).then((rows) =>
            rows.map((r) => ({
              _id: r.propertyId,
              count: r.count,
              averageRating: r.averageRating,
            })),
          )
        : Review.aggregate([
            { $match: { propertyId: { $in: ids } } },
            {
              $group: {
                _id: "$propertyId",
                count: { $sum: 1 },
                averageRating: { $avg: "$rating" },
              },
            },
          ]),
      isReviewsMysql()
        ? latestCommentedReviewsMysql(ids).then((rows) =>
            rows.map((r) => ({
              _id: r.propertyId,
              comment: r.comment,
              rating: r.rating,
              authorId: r.authorId,
            })),
          )
        : Review.aggregate([
            { $match: { propertyId: { $in: ids }, comment: { $exists: true, $nin: [null, ""] } } },
            { $sort: { createdAt: -1 } },
            {
              $group: {
                _id: "$propertyId",
                comment: { $first: "$comment" },
                rating: { $first: "$rating" },
                authorId: { $first: "$authorId" },
              },
            },
          ]),
    ]);
    const statsMap = new Map<string, any>(reviewStats.map((r: any) => [r._id, r]));
    const latestMap = new Map<string, any>(latestReviews.map((r: any) => [r._id, r]));
    const authorIds = [...new Set(latestReviews.map((r: any) => r.authorId).filter(Boolean))];
    const authors = authorIds.length
      ? await User.find({ _id: { $in: authorIds } }).select("fullName").lean()
      : [];
    const authorMap = new Map(authors.map((a) => [a._id, a.fullName]));

    const sortedImages = properties.map((p) => {
      const stats = statsMap.get(p._id);
      const latest = latestMap.get(p._id);
      return {
        ...p,
        images: [...(p.images || [])].sort((a: any, b: any) => (a.sortOrder ?? 0) - (b.sortOrder ?? 0)),
        _count: { reviews: stats?.count ?? 0 },
        averageRating: stats?.averageRating ? Number(Number(stats.averageRating).toFixed(1)) : 0,
        latestReview: latest
          ? {
              comment: latest.comment,
              rating: latest.rating,
              authorName: authorMap.get(latest.authorId) || undefined,
            }
          : null,
      };
    });

    const serialized = sortedImages.map(serializeProperty);
    res.json({
      properties: serialized,
      total: idList.length ? serialized.length : totalCount,
      ...(cityMatch !== undefined ? { cityMatch } : {}),
      _db: "mongodb",
    });
  }),
);

propertiesRouter.get(
  "/:id",
  asyncHandler(async (req, res) => {
    if (isPropertiesMysql()) {
      const property = await findPropertyByIdMysql(req.params.id);
      if (!property) throw new AppError(404, "Property not found");

      const city = isCitiesMysql()
        ? await findCityByIdMysql(property.cityId)
        : await City.findById(property.cityId).lean();

      const ownerRow = (await findUsersByIdsMysql([property.ownerId]))[0];
      // Never fall back to mongoose when AUTH is MySQL — buffering hangs ~10s → 500.
      const ownerMongo =
        ownerRow || isAuthMysql()
          ? null
          : await User.findById(property.ownerId)
              .select("fullName avatarUrl trustedOwner ownerVerificationStatus")
              .lean();

      const reviews = isReviewsMysql()
        ? await listReviewsByPropertyMysql(property.id)
        : await Review.find({ propertyId: property.id }).sort({ createdAt: -1 }).lean();

      const authorIds = Array.from(
        new Set<string>(reviews.map((r: any) => String(r.authorId || "")).filter(Boolean)),
      );
      const authorsMysql = await findUsersByIdsMysql(authorIds);
      const authorMap = new Map(authorsMysql.map((a) => [a.id, a]));
      if (authorsMysql.length < authorIds.length && !isAuthMysql()) {
        const missing = authorIds.filter((id) => !authorMap.has(id));
        if (missing.length) {
          const authors = await User.find({ _id: { $in: missing } })
            .select("fullName avatarUrl")
            .lean();
          for (const a of authors) {
            authorMap.set(a._id, {
              id: a._id,
              fullName: a.fullName,
              avatarUrl: a.avatarUrl,
            } as any);
          }
        }
      }

      const reviewsWithAuthors = reviews.map((r: any) => {
        const api = isReviewsMysql() ? reviewToApi(r) : r;
        const author: any = authorMap.get(api.authorId || r.authorId);
        return {
          ...api,
          author: author
            ? {
                id: author.id ?? author._id,
                fullName: author.fullName,
                avatarUrl: author.avatarUrl,
              }
            : undefined,
        };
      });

      const avg =
        reviewsWithAuthors.length > 0
          ? reviewsWithAuthors.reduce((s, r) => s + Number(r.rating), 0) /
            reviewsWithAuthors.length
          : 0;

      const updates = await listPropertyUpdatesMysql(property.id, 10);
      const unavailableDates = await unavailableDatesForProperty(property.id);

      const owner = ownerRow
        ? {
            id: ownerRow.id,
            fullName: ownerRow.fullName,
            avatarUrl: ownerRow.avatarUrl,
            trustedOwner: Boolean(ownerRow.trustedOwner),
          }
        : ownerMongo
          ? {
              id: ownerMongo._id,
              fullName: ownerMongo.fullName,
              avatarUrl: ownerMongo.avatarUrl,
              trustedOwner: Boolean((ownerMongo as any).trustedOwner),
            }
          : undefined;

      res.setHeader("Cache-Control", "no-store, no-cache, must-revalidate");
      res.json({
        property: {
          ...serializeProperty({
            ...propertyToApi(property, isCitiesMysql() ? (city as any) : null),
            city: city
              ? isCitiesMysql()
                ? cityRowToApi(city as any)
                : city
              : undefined,
            owner,
            reviews: reviewsWithAuthors,
          }),
          unavailableDates,
          averageRating: Number(avg.toFixed(1)),
          updates: updates.map(propertyUpdateToApi),
        },
        _db: "mysql",
      });
      return;
    }

    const property = await Property.findOne({ _id: req.params.id, deletedAt: null }).lean();
    if (!property) throw new AppError(404, "Property not found");

    const [city, owner, reviews, updates] = await Promise.all([
      City.findById(property.cityId).lean(),
      User.findById(property.ownerId)
        .select("fullName avatarUrl trustedOwner ownerVerificationStatus")
        .lean(),
      isReviewsMysql()
        ? listReviewsByPropertyMysql(property._id)
        : Review.find({ propertyId: property._id }).sort({ createdAt: -1 }).lean(),
      PropertyUpdate.find({ propertyId: property._id, deletedAt: null })
        .sort({ createdAt: -1 })
        .limit(10)
        .lean(),
    ]);

    const authorIds = [...new Set(reviews.map((r: any) => r.authorId))];
    const authors = await User.find({ _id: { $in: authorIds } })
      .select("fullName avatarUrl")
      .lean();
    const authorMap = new Map(authors.map((a) => [a._id, a]));

    const reviewsWithAuthors = reviews.map((r: any) => {
      const api = isReviewsMysql() ? reviewToApi(r) : r;
      const author: any = authorMap.get(api.authorId || r.authorId);
      return {
        ...api,
        author: author
          ? { id: author._id, fullName: author.fullName, avatarUrl: author.avatarUrl }
          : undefined,
      };
    });

    const avg =
      reviewsWithAuthors.length > 0
        ? reviewsWithAuthors.reduce((s, r) => s + Number(r.rating), 0) /
          reviewsWithAuthors.length
        : 0;

    const images = [...(property.images || [])].sort(
      (a: any, b: any) => (a.sortOrder ?? 0) - (b.sortOrder ?? 0),
    );
    const videos = [...(property.videos || [])].sort(
      (a: any, b: any) => (a.sortOrder ?? 0) - (b.sortOrder ?? 0),
    );

    const unavailableDates = await unavailableDatesForProperty(property._id);

    res.setHeader("Cache-Control", "no-store, no-cache, must-revalidate");
    res.json({
      property: {
        ...serializeProperty({
          ...property,
          city,
          owner: owner
            ? {
                id: owner._id,
                fullName: owner.fullName,
                avatarUrl: owner.avatarUrl,
                trustedOwner: Boolean((owner as any).trustedOwner),
              }
            : undefined,
          images,
          videos,
          reviews: reviewsWithAuthors,
        }),
        unavailableDates,
        averageRating: Number(avg.toFixed(1)),
        updates: updates.map(toPlain),
      },
      _db: "mongodb",
    });
  }),
);

const UpdateBodySchema = z.object({
  titleAr: z.string().min(3).max(120),
  titleEn: z.string().min(3).max(120),
  bodyAr: z.string().min(10).max(2000),
  bodyEn: z.string().min(10).max(2000),
});

propertiesRouter.get(
  "/:id/updates",
  asyncHandler(async (req, res) => {
    if (isPropertiesMysql()) {
      const property = await findPropertyByIdMysql(req.params.id);
      if (!property) throw new AppError(404, "Property not found");
      const updates = await listPropertyUpdatesMysql(property.id, 30);
      res.json({ updates: updates.map(propertyUpdateToApi), _db: "mysql" });
      return;
    }
    const property = await Property.findOne({ _id: req.params.id, deletedAt: null }).lean();
    if (!property) throw new AppError(404, "Property not found");
    const updates = await PropertyUpdate.find({ propertyId: property._id, deletedAt: null })
      .sort({ createdAt: -1 })
      .limit(30)
      .lean();
    res.json({ updates: updates.map(toPlain), _db: "mongodb" });
  }),
);

propertiesRouter.post(
  "/:id/updates",
  requireAuth,
  requireRoles("OWNER", "ADMIN"),
  asyncHandler(async (req, res) => {
    if (isPropertiesMysql()) {
      const property = await findPropertyByIdMysql(req.params.id);
      if (!property) throw new AppError(404, "Property not found");
      if (!isStaffRole(req.user!.role) && property.ownerId !== req.user!.id) {
        throw new AppError(403, "Forbidden");
      }
      const body = UpdateBodySchema.parse(req.body);
      const update = await createPropertyUpdateMysql({
        propertyId: property.id,
        ownerId: property.ownerId,
        ...body,
      });
      res.status(201).json({ update: propertyUpdateToApi(update), _db: "mysql" });
      return;
    }

    const property = await Property.findOne({ _id: req.params.id, deletedAt: null });
    if (!property) throw new AppError(404, "Property not found");
    if (!isStaffRole(req.user!.role) && property.ownerId !== req.user!.id) {
      throw new AppError(403, "Forbidden");
    }
    const body = UpdateBodySchema.parse(req.body);
    const update = await PropertyUpdate.create({
      propertyId: property._id,
      ownerId: property.ownerId,
      ...body,
    });
    res.status(201).json({ update: toPlain(update.toObject()), _db: "mongodb" });
  }),
);

propertiesRouter.delete(
  "/:id/updates/:updateId",
  requireAuth,
  requireRoles("OWNER", "ADMIN"),
  asyncHandler(async (req, res) => {
    if (isPropertiesMysql()) {
      const property = await findPropertyByIdMysql(req.params.id);
      if (!property) throw new AppError(404, "Property not found");
      if (!isStaffRole(req.user!.role) && property.ownerId !== req.user!.id) {
        throw new AppError(403, "Forbidden");
      }
      const ok = await softDeletePropertyUpdateMysql(req.params.updateId, property.id);
      if (!ok) throw new AppError(404, "Update not found");
      res.json({ ok: true, _db: "mysql" });
      return;
    }

    const property = await Property.findOne({ _id: req.params.id, deletedAt: null });
    if (!property) throw new AppError(404, "Property not found");
    if (!isStaffRole(req.user!.role) && property.ownerId !== req.user!.id) {
      throw new AppError(403, "Forbidden");
    }
    const update = await PropertyUpdate.findOne({
      _id: req.params.updateId,
      propertyId: property._id,
      deletedAt: null,
    });
    if (!update) throw new AppError(404, "Update not found");
    update.deletedAt = new Date();
    await update.save();
    res.json({ ok: true, _db: "mongodb" });
  }),
);

propertiesRouter.post(
  "/",
  requireAuth,
  requireRoles("OWNER", "ADMIN"),
  asyncHandler(async (req, res) => {
    const body = PropertyBodySchema.parse(req.body);
    const slug = body.slug || `${slugify(body.titleEn)}-${Date.now().toString(36)}`;

    if (isPropertiesMysql()) {
      const property = await createPropertyMysql({
        ownerId: req.user!.id,
        cityId: body.cityId,
        titleAr: body.titleAr,
        titleEn: body.titleEn,
        slug,
        descriptionAr: body.descriptionAr,
        descriptionEn: body.descriptionEn,
        address: body.address,
        latitude: body.latitude,
        longitude: body.longitude,
        bedrooms: body.bedrooms,
        bathrooms: body.bathrooms,
        maxGuests: body.maxGuests,
        wifi: body.wifi ?? false,
        parking: body.parking ?? false,
        airConditioning: body.airConditioning ?? false,
        kitchen: body.kitchen ?? false,
        hospitalNearby: body.hospitalNearby ?? false,
        universityNearby: body.universityNearby ?? false,
        petFriendly: body.petFriendly ?? false,
        instantBooking: body.instantBooking ?? false,
        basePriceTnd: body.basePriceTnd,
        cleaningFeeTnd: body.cleaningFeeTnd ?? 0,
        checkInTime: body.checkInTime || "15:00",
        checkOutTime: body.checkOutTime || "11:00",
        cancellationPolicy: body.cancellationPolicy,
        houseRules: body.houseRules ?? undefined,
        featured: isStaffRole(req.user!.role) ? (body.featured ?? false) : false,
        status: body.status ?? "DRAFT",
        images: body.images,
        videos: body.videos,
        blockedDates: body.blockedDates,
      });
      const city = isCitiesMysql()
        ? await findCityByIdMysql(property.cityId)
        : await City.findById(property.cityId).lean();
      res.status(201).json({
        property: serializeProperty({
          ...propertyToApi(property),
          city: city ? (isCitiesMysql() ? cityRowToApi(city as any) : city) : undefined,
        }),
        _db: "mysql",
      });
      return;
    }

    const property = await Property.create({
      ownerId: req.user!.id,
      cityId: body.cityId,
      titleAr: body.titleAr,
      titleEn: body.titleEn,
      slug,
      descriptionAr: body.descriptionAr,
      descriptionEn: body.descriptionEn,
      address: body.address,
      latitude: body.latitude ?? undefined,
      longitude: body.longitude ?? undefined,
      bedrooms: body.bedrooms,
      bathrooms: body.bathrooms,
      maxGuests: body.maxGuests,
      wifi: body.wifi ?? false,
      parking: body.parking ?? false,
      airConditioning: body.airConditioning ?? false,
      kitchen: body.kitchen ?? false,
      hospitalNearby: body.hospitalNearby ?? false,
      universityNearby: body.universityNearby ?? false,
      petFriendly: body.petFriendly ?? false,
      instantBooking: body.instantBooking ?? false,
      basePriceTnd: body.basePriceTnd,
      cleaningFeeTnd: body.cleaningFeeTnd ?? 0,
      checkInTime: body.checkInTime || "15:00",
      checkOutTime: body.checkOutTime || "11:00",
      cancellationPolicy: body.cancellationPolicy,
      houseRules: body.houseRules ?? undefined,
      featured: isStaffRole(req.user!.role) ? (body.featured ?? false) : false,
      status: body.status ?? "DRAFT",
      images: (body.images ?? []).map((img, i) => ({
        _id: createId(),
        url: img.url,
        sortOrder: img.sortOrder ?? i,
        createdAt: new Date(),
      })),
      videos: (body.videos ?? []).map((vid, i) => ({
        _id: createId(),
        url: vid.url,
        sortOrder: vid.sortOrder ?? i,
        createdAt: new Date(),
      })),
      blockedDates: body.blockedDates ?? [],
    });

    const city = await City.findById(property.cityId).lean();
    res.status(201).json({
      property: serializeProperty({ ...property.toObject(), city }),
      _db: "mongodb",
    });
  }),
);

propertiesRouter.patch(
  "/:id",
  requireAuth,
  requireRoles("OWNER", "ADMIN"),
  asyncHandler(async (req, res) => {
    if (isPropertiesMysql()) {
      const existing = await findPropertyByIdMysql(req.params.id);
      if (!existing) throw new AppError(404, "Property not found");
      if (!isStaffRole(req.user!.role) && existing.ownerId !== req.user!.id) {
        throw new AppError(403, "Forbidden");
      }

      const body = PropertyBodySchema.partial().parse(req.body);
      const data: any = { ...body };
      if (!isStaffRole(req.user!.role)) {
        delete data.featured;
        if (data.status === "REJECTED") delete data.status;
      }

      const updated = await updatePropertyMysql(existing.id, data);
      const city = isCitiesMysql()
        ? await findCityByIdMysql(updated!.cityId)
        : await City.findById(updated!.cityId).lean();
      res.json({
        property: serializeProperty({
          ...propertyToApi(updated!),
          city: city ? (isCitiesMysql() ? cityRowToApi(city as any) : city) : undefined,
        }),
        _db: "mysql",
      });
      return;
    }

    const existing = await Property.findOne({ _id: req.params.id, deletedAt: null });
    if (!existing) throw new AppError(404, "Property not found");
    if (!isStaffRole(req.user!.role) && existing.ownerId !== req.user!.id) {
      throw new AppError(403, "Forbidden");
    }

    const body = PropertyBodySchema.partial().parse(req.body);
    const { images, videos, ...rest } = body;

    if (images) {
      existing.images = images.map((img, i) => ({
        _id: createId(),
        url: img.url,
        sortOrder: img.sortOrder ?? i,
        createdAt: new Date(),
      })) as any;
    }
    if (videos) {
      existing.videos = videos.map((vid, i) => ({
        _id: createId(),
        url: vid.url,
        sortOrder: vid.sortOrder ?? i,
        createdAt: new Date(),
      })) as any;
    }

    const data: any = { ...rest };
    if (!isStaffRole(req.user!.role)) {
      delete data.featured;
      if (data.status === "REJECTED") delete data.status;
    }
    if (Array.isArray(data.blockedDates)) {
      existing.blockedDates = data.blockedDates;
      existing.markModified("blockedDates");
      delete data.blockedDates;
    }
    Object.assign(existing, data);
    await existing.save();

    const city = await City.findById(existing.cityId).lean();
    const obj = existing.toObject();
    const imagesSorted = [...(obj.images || [])].sort(
      (a: any, b: any) => (a.sortOrder ?? 0) - (b.sortOrder ?? 0),
    );
    res.json({
      property: serializeProperty({ ...obj, city, images: imagesSorted }),
      _db: "mongodb",
    });
  }),
);

propertiesRouter.delete(
  "/:id",
  requireAuth,
  requireRoles("OWNER", "ADMIN"),
  asyncHandler(async (req, res) => {
    if (isPropertiesMysql()) {
      const existing = await findPropertyByIdMysql(req.params.id);
      if (!existing) throw new AppError(404, "Property not found");
      if (!isStaffRole(req.user!.role) && existing.ownerId !== req.user!.id) {
        throw new AppError(403, "Forbidden");
      }
      await softDeletePropertyMysql(existing.id);
      res.json({ ok: true, _db: "mysql" });
      return;
    }

    const existing = await Property.findOne({ _id: req.params.id, deletedAt: null });
    if (!existing) throw new AppError(404, "Property not found");
    if (!isStaffRole(req.user!.role) && existing.ownerId !== req.user!.id) {
      throw new AppError(403, "Forbidden");
    }
    existing.deletedAt = new Date();
    existing.status = "PAUSED";
    await existing.save();
    res.json({ ok: true, _db: "mongodb" });
  }),
);