storage.ts - main - masterOK/FEF

storage.ts
1 416 строк44 KB
masterOK
upload files
10 ноя 2025, 19:52
10 ноя 2025, 19:522cb4a49
import { db } from "./db";
import { eq, and, desc, sql, like, or, gte, lte } from "drizzle-orm";
import * as schema from "@shared/schema";
import type {
  User,
  InsertUser,
  Master,
  InsertMaster,
  Service,
  InsertService,
  Order,
  InsertOrder,
  Review,
  InsertReview,
  Article,
  InsertArticle,
  Category,
  Achievement,
  UserAchievement,
  ChatMessage,
  InsertChatMessage,
  Transaction,
  InsertTransaction,
  SupportTicket,
  InsertSupportTicket,
  SupportMessage,
  InsertSupportMessage,
  ArticleComment,
  InsertArticleComment,
  Listing,
  InsertListing,
  CarBrand,
  PortfolioItem,
  InsertPortfolioItem,
} from "@shared/schema";

export interface IStorage {
  getUserByEmail(email: string): Promise<User | undefined>;
  getUserById(id: string): Promise<User | undefined>;
  createUser(user: InsertUser): Promise<User>;
  updateUser(id: string, updates: Partial<User>): Promise<User | undefined>;
  
  getMasterById(id: string): Promise<Master | undefined>;
  getMastersByFilters(filters: {
    city?: string;
    categoryId?: string;
    minRating?: number;
    search?: string;
  }): Promise<Array<Master & { user: Omit<User, 'passwordHash'>; services: Service[] }>>;
  getTopMasters(filters: {
    categoryId?: string;
    limit?: number;
  }): Promise<Array<Master & { user: Omit<User, 'passwordHash'>; services: Service[] }>>;
  createMaster(master: InsertMaster): Promise<Master>;
  updateMaster(id: string, updates: Partial<Master>): Promise<Master | undefined>;
  
  getCategories(): Promise<Category[]>;
  getCategoryById(id: string): Promise<Category | undefined>;
  
  getServicesByMasterId(masterId: string): Promise<Service[]>;
  getServiceById(id: string): Promise<Service | undefined>;
  createService(service: InsertService): Promise<Service>;
  updateService(id: string, updates: Partial<Service>): Promise<Service | undefined>;
  deleteService(id: string): Promise<void>;
  
  getOrderById(id: string): Promise<Order | undefined>;
  getOrdersByUserId(userId: string): Promise<Order[]>;
  createOrder(order: InsertOrder): Promise<Order>;
  updateOrderStatus(orderId: string, status: string): Promise<Order | undefined>;
  
  getReviewsByMasterId(masterId: string): Promise<Array<Review & { client: Omit<User, 'passwordHash'> }>>;
  createReview(review: InsertReview): Promise<Review>;
  
  getPortfolioItemsByMasterId(masterId: string): Promise<PortfolioItem[]>;
  createPortfolioItem(item: InsertPortfolioItem): Promise<PortfolioItem>;
  deletePortfolioItem(id: string): Promise<void>;
  
  getArticles(filters?: { isPublished?: boolean; status?: "pending" | "approved" | "rejected" }): Promise<Array<Article & { author: Omit<User, 'passwordHash'> }>>;
  getArticleById(id: string): Promise<Article | undefined>;
  createArticle(article: InsertArticle): Promise<Article>;
  updateArticle(id: string, updates: Partial<Article>): Promise<Article | undefined>;
  approveArticle(id: string, moderatorId: string): Promise<Article>;
  rejectArticle(id: string, moderatorId: string, reason?: string): Promise<Article>;
  
  getAchievements(): Promise<Achievement[]>;
  getUserAchievements(userId: string): Promise<Array<UserAchievement & { achievement: Achievement }>>;
  awardAchievement(userId: string, achievementId: string): Promise<UserAchievement>;
  
  getChatMessages(orderId: string): Promise<Array<ChatMessage & { sender: Omit<User, 'passwordHash'>; receiver: Omit<User, 'passwordHash'> }>>;
  createChatMessage(message: InsertChatMessage): Promise<ChatMessage>;
  markMessagesAsRead(orderId: string, userId: string): Promise<void>;
  
  createTransaction(transaction: InsertTransaction): Promise<Transaction>;
  getTransactionById(id: string): Promise<Transaction | undefined>;
  getTransactionByOrderId(orderId: string): Promise<Transaction | undefined>;
  updateTransaction(id: string, updates: Partial<Transaction>): Promise<Transaction | undefined>;
  
  getAdminStats(): Promise<{
    totalUsers: number;
    activeOrders: number;
    totalRevenue: string;
    newReviews: number;
  }>;
  
  createSupportTicket(ticket: InsertSupportTicket): Promise<SupportTicket>;
  getSupportTicketsByUserId(userId: string): Promise<Array<SupportTicket & { messages: SupportMessage[] }>>;
  getSupportTicketById(id: string): Promise<(SupportTicket & { messages: SupportMessage[] }) | undefined>;
  updateSupportTicket(id: string, updates: Partial<SupportTicket>): Promise<SupportTicket | undefined>;
  getAllSupportTickets(): Promise<Array<SupportTicket & { user: Omit<User, 'passwordHash'>; messages: SupportMessage[] }>>;
  
  createSupportMessage(message: InsertSupportMessage): Promise<SupportMessage>;
  
  getArticleComments(articleId: string, userId?: string): Promise<Array<ArticleComment & { user: Omit<User, 'passwordHash'>; replies: any[]; hasUserUpvoted: boolean }>>;
  createArticleComment(comment: InsertArticleComment): Promise<ArticleComment>;
  updateArticleComment(id: string, updates: Partial<ArticleComment>): Promise<ArticleComment | undefined>;
  
  getListings(filters?: { categoryId?: string; ownerId?: string; status?: "active" | "sold" | "archived"; search?: string }): Promise<Array<Listing & { owner: Omit<User, "passwordHash">; category: Category }>>;
  getListingById(id: string): Promise<(Listing & { owner: Omit<User, "passwordHash">; category: Category }) | undefined>;
  createListing(listing: InsertListing): Promise<Listing>;
  updateListing(id: string, updates: Partial<Listing>): Promise<Listing | undefined>;
  deleteListing(id: string): Promise<void>;
  
  getCarBrands(): Promise<CarBrand[]>;
  getPopularCarBrands(): Promise<CarBrand[]>;
  
  upvoteComment(commentId: string, userId: string): Promise<{ comment: ArticleComment; reputationChange: number }>;
  
  getChatsByUserId(userId: string): Promise<Array<{ orderId: string; partnerId: string; partnerName: string; lastMessage: string; lastMessageAt: Date; unreadCount: number }>>;
  
  getBlogPosts(filters?: { published?: boolean; category?: string }): Promise<Array<schema.BlogPost & { author: Omit<schema.User, 'passwordHash'> }>>;
  getBlogPostById(id: string): Promise<schema.BlogPost | undefined>;
  getBlogPostBySlug(slug: string): Promise<(schema.BlogPost & { author: Omit<schema.User, 'passwordHash'> }) | undefined>;
  createBlogPost(post: schema.InsertBlogPost): Promise<schema.BlogPost>;
  updateBlogPost(id: string, updates: Partial<schema.BlogPost>): Promise<schema.BlogPost | undefined>;
  publishBlogPost(id: string): Promise<schema.BlogPost | undefined>;
}

const SAFE_USER_SELECTION = {
  id: schema.users.id,
  email: schema.users.email,
  role: schema.users.role,
  fullName: schema.users.fullName,
  phone: schema.users.phone,
  avatarUrl: schema.users.avatarUrl,
  city: schema.users.city,
  oauthProvider: schema.users.oauthProvider,
  oauthId: schema.users.oauthId,
  reputation: schema.users.reputation,
  latitude: schema.users.latitude,
  longitude: schema.users.longitude,
  createdAt: schema.users.createdAt,
  updatedAt: schema.users.updatedAt,
};

export class DatabaseStorage implements IStorage {
  async getUserByEmail(email: string): Promise<User | undefined> {
    const [user] = await db
      .select()
      .from(schema.users)
      .where(eq(schema.users.email, email))
      .limit(1);
    return user;
  }

  async getUserById(id: string): Promise<User | undefined> {
    const [user] = await db
      .select()
      .from(schema.users)
      .where(eq(schema.users.id, id))
      .limit(1);
    return user;
  }

  async createUser(insertUser: InsertUser): Promise<User> {
    const [user] = await db
      .insert(schema.users)
      .values(insertUser)
      .returning();
    return user;
  }

  async updateUser(id: string, updates: Partial<User>): Promise<User | undefined> {
    const [user] = await db
      .update(schema.users)
      .set({ ...updates, updatedAt: new Date() })
      .where(eq(schema.users.id, id))
      .returning();
    return user;
  }

  async getMasterById(id: string): Promise<Master | undefined> {
    const [master] = await db
      .select()
      .from(schema.masters)
      .where(eq(schema.masters.id, id))
      .limit(1);
    return master;
  }

  async getMastersByFilters(filters: {
    city?: string;
    categoryId?: string;
    minRating?: number;
    search?: string;
    verified?: boolean;
    nearMe?: boolean;
    availableToday?: boolean;
    maxPrice?: number;
    minExperience?: number;
    maxExperience?: number;
    userLat?: number;
    userLng?: number;
  }): Promise<Array<Master & { user: Omit<User, 'passwordHash'>; services: Service[] }>> {
    const conditions = [];
    
    if (filters.city) {
      conditions.push(eq(schema.users.city, filters.city));
    }
    if (filters.minRating) {
      conditions.push(gte(schema.masters.rating, filters.minRating.toString()));
    }
    if (filters.search) {
      conditions.push(
        or(
          like(schema.users.fullName, `%${filters.search}%`),
          like(schema.masters.specialization, `%${filters.search}%`),
          like(schema.masters.description, `%${filters.search}%`)
        )
      );
    }
    if (filters.verified) {
      conditions.push(eq(schema.masters.isVerified, true));
    }
    if (filters.minExperience !== undefined) {
      conditions.push(gte(schema.masters.experienceYears, filters.minExperience));
    }
    if (filters.maxExperience !== undefined) {
      conditions.push(lte(schema.masters.experienceYears, filters.maxExperience));
    }

    const masters = await db
      .select({
        master: schema.masters,
        user: SAFE_USER_SELECTION,
      })
      .from(schema.masters)
      .innerJoin(schema.users, eq(schema.masters.userId, schema.users.id))
      .where(conditions.length > 0 ? and(...conditions) : undefined)
      .orderBy(desc(schema.masters.rating));

    let mastersWithServices = await Promise.all(
      masters.map(async ({ master, user }) => {
        let services = await this.getServicesByMasterId(master.id);
        
        if (filters.categoryId) {
          services = services.filter(s => s.categoryId === filters.categoryId);
        }
        
        if (filters.maxPrice !== undefined) {
          services = services.filter(s => {
            const price = parseFloat(s.price);
            return price <= (filters.maxPrice || Infinity);
          });
        }
        
        return {
          ...master,
          user,
          services,
        };
      })
    );

    if (filters.categoryId || filters.maxPrice) {
      mastersWithServices = mastersWithServices.filter(m => m.services.length > 0);
    }

    if (filters.nearMe && filters.userLat && filters.userLng) {
      mastersWithServices = mastersWithServices.filter(m => {
        if (!m.latitude || !m.longitude) return false;
        
        const distance = this.calculateDistance(
          filters.userLat!,
          filters.userLng!,
          parseFloat(m.latitude),
          parseFloat(m.longitude)
        );
        
        return distance <= 10;
      });
    }

    if (filters.availableToday) {
      mastersWithServices = mastersWithServices.filter(m => m.availableNow);
    }

    return mastersWithServices;
  }

  private calculateDistance(lat1: number, lng1: number, lat2: number, lng2: number): number {
    const R = 6371;
    const dLat = (lat2 - lat1) * Math.PI / 180;
    const dLng = (lng2 - lng1) * Math.PI / 180;
    const a = 
      Math.sin(dLat/2) * Math.sin(dLat/2) +
      Math.cos(lat1 * Math.PI / 180) * Math.cos(lat2 * Math.PI / 180) *
      Math.sin(dLng/2) * Math.sin(dLng/2);
    const c = 2 * Math.atan2(Math.sqrt(a), Math.sqrt(1-a));
    return R * c;
  }

  async getTopMasters(filters: {
    categoryId?: string;
    limit?: number;
  }): Promise<Array<Master & { user: Omit<User, 'passwordHash'>; services: Service[] }>> {
    const limit = filters.limit || 10;

    if (filters.categoryId) {
      const allMasters = await db
        .select({
          master: schema.masters,
          user: SAFE_USER_SELECTION,
        })
        .from(schema.masters)
        .innerJoin(schema.users, eq(schema.masters.userId, schema.users.id))
        .innerJoin(schema.services, eq(schema.services.masterId, schema.masters.id))
        .where(
          and(
            gte(schema.masters.rating, "4.0"),
            eq(schema.services.categoryId, filters.categoryId),
            eq(schema.services.isActive, true)
          )
        )
        .orderBy(desc(schema.masters.rating), desc(schema.masters.completedOrders));

      const seenMasterIds = new Set<string>();
      const uniqueMasters: Array<{ master: any; user: any }> = [];

      for (const item of allMasters) {
        if (!seenMasterIds.has(item.master.id)) {
          seenMasterIds.add(item.master.id);
          uniqueMasters.push(item);
          
          if (uniqueMasters.length >= limit) {
            break;
          }
        }
      }

      const mastersWithServices = await Promise.all(
        uniqueMasters.map(async ({ master, user }) => {
          const services = await this.getServicesByMasterId(master.id);
          const filteredServices = services.filter(s => s.categoryId === filters.categoryId);
          
          return {
            ...master,
            user,
            services: filteredServices,
          };
        })
      );

      return mastersWithServices;
    } else {
      const masters = await db
        .select({
          master: schema.masters,
          user: SAFE_USER_SELECTION,
        })
        .from(schema.masters)
        .innerJoin(schema.users, eq(schema.masters.userId, schema.users.id))
        .where(gte(schema.masters.rating, "4.0"))
        .orderBy(desc(schema.masters.rating), desc(schema.masters.completedOrders))
        .limit(limit);

      const mastersWithServices = await Promise.all(
        masters.map(async ({ master, user }) => {
          const services = await this.getServicesByMasterId(master.id);
          
          return {
            ...master,
            user,
            services,
          };
        })
      );

      return mastersWithServices;
    }
  }

  async createMaster(insertMaster: InsertMaster): Promise<Master> {
    const [master] = await db
      .insert(schema.masters)
      .values(insertMaster)
      .returning();
    return master;
  }

  async updateMaster(id: string, updates: Partial<Master>): Promise<Master | undefined> {
    const [master] = await db
      .update(schema.masters)
      .set(updates)
      .where(eq(schema.masters.id, id))
      .returning();
    return master;
  }

  async getCategories(): Promise<Category[]> {
    return await db
      .select()
      .from(schema.categories)
      .where(eq(schema.categories.isActive, true));
  }

  async getCategoryById(id: string): Promise<Category | undefined> {
    const [category] = await db
      .select()
      .from(schema.categories)
      .where(eq(schema.categories.id, id))
      .limit(1);
    return category;
  }

  async getServicesByMasterId(masterId: string): Promise<Service[]> {
    return await db
      .select()
      .from(schema.services)
      .where(and(
        eq(schema.services.masterId, masterId),
        eq(schema.services.isActive, true)
      ));
  }

  async getServiceById(id: string): Promise<Service | undefined> {
    const [service] = await db
      .select()
      .from(schema.services)
      .where(eq(schema.services.id, id))
      .limit(1);
    return service;
  }

  async createService(insertService: InsertService): Promise<Service> {
    const [service] = await db
      .insert(schema.services)
      .values(insertService)
      .returning();
    return service;
  }

  async updateService(id: string, updates: Partial<Service>): Promise<Service | undefined> {
    const [service] = await db
      .update(schema.services)
      .set(updates)
      .where(eq(schema.services.id, id))
      .returning();
    return service;
  }

  async deleteService(id: string): Promise<void> {
    await db
      .delete(schema.services)
      .where(eq(schema.services.id, id));
  }

  async getOrderById(id: string): Promise<any> {
    const [result] = await db
      .select({
        order: schema.orders,
        master: schema.masters,
        masterUser: SAFE_USER_SELECTION,
        service: schema.services,
        review: schema.reviews,
      })
      .from(schema.orders)
      .leftJoin(schema.masters, eq(schema.orders.masterId, schema.masters.id))
      .leftJoin(schema.users, eq(schema.masters.userId, schema.users.id))
      .leftJoin(schema.services, eq(schema.orders.serviceId, schema.services.id))
      .leftJoin(schema.reviews, eq(schema.orders.id, schema.reviews.orderId))
      .where(eq(schema.orders.id, id))
      .limit(1);

    if (!result) return undefined;

    return {
      ...result.order,
      master: result.master ? {
        ...result.master,
        user: result.masterUser,
      } : undefined,
      service: result.service,
      review: result.review,
    };
  }

  async getOrdersByUserId(userId: string): Promise<Order[]> {
    return await db
      .select()
      .from(schema.orders)
      .where(
        or(
          eq(schema.orders.clientId, userId),
          eq(schema.orders.masterId, userId)
        )
      )
      .orderBy(desc(schema.orders.createdAt));
  }

  async createOrder(insertOrder: InsertOrder): Promise<Order> {
    const [order] = await db
      .insert(schema.orders)
      .values(insertOrder)
      .returning();
    return order;
  }

  async updateOrderStatus(orderId: string, status: string): Promise<Order | undefined> {
    const updates: any = { status };
    if (status === 'completed') {
      updates.completedAt = new Date();
    }
    
    const [order] = await db
      .update(schema.orders)
      .set(updates)
      .where(eq(schema.orders.id, orderId))
      .returning();
    return order;
  }

  async getReviewsByMasterId(masterId: string): Promise<Array<Review & { client: Omit<User, 'passwordHash'> }>> {
    const reviews = await db
      .select({
        review: schema.reviews,
        client: SAFE_USER_SELECTION,
      })
      .from(schema.reviews)
      .innerJoin(schema.users, eq(schema.reviews.clientId, schema.users.id))
      .where(eq(schema.reviews.masterId, masterId))
      .orderBy(desc(schema.reviews.createdAt));

    return reviews.map(({ review, client }) => ({
      ...review,
      client,
    }));
  }

  async createReview(insertReview: InsertReview): Promise<Review> {
    const [review] = await db
      .insert(schema.reviews)
      .values(insertReview)
      .returning();

    const reviews = await db
      .select()
      .from(schema.reviews)
      .where(eq(schema.reviews.masterId, insertReview.masterId));

    const avgRating = reviews.reduce((sum, r) => sum + r.rating, 0) / reviews.length;
    
    await db
      .update(schema.masters)
      .set({ rating: avgRating.toFixed(2) })
      .where(eq(schema.masters.id, insertReview.masterId));

    return review;
  }

  async getPortfolioItemsByMasterId(masterId: string): Promise<PortfolioItem[]> {
    const items = await db
      .select()
      .from(schema.portfolioItems)
      .where(eq(schema.portfolioItems.masterId, masterId))
      .orderBy(desc(schema.portfolioItems.createdAt));
    return items;
  }

  async createPortfolioItem(insertItem: InsertPortfolioItem): Promise<PortfolioItem> {
    const [item] = await db
      .insert(schema.portfolioItems)
      .values(insertItem)
      .returning();
    return item;
  }

  async deletePortfolioItem(id: string): Promise<void> {
    await db
      .delete(schema.portfolioItems)
      .where(eq(schema.portfolioItems.id, id));
  }

  async getArticles(filters?: { isPublished?: boolean; status?: "pending" | "approved" | "rejected" }): Promise<Array<Article & { author: Omit<User, 'passwordHash'> }>> {
    const conditions = [];
    if (filters?.isPublished !== undefined) {
      conditions.push(eq(schema.articles.isPublished, filters.isPublished));
    }
    if (filters?.status !== undefined) {
      conditions.push(eq(schema.articles.status, filters.status));
    }

    const articles = await db
      .select({
        article: schema.articles,
        author: SAFE_USER_SELECTION,
      })
      .from(schema.articles)
      .innerJoin(schema.users, eq(schema.articles.authorId, schema.users.id))
      .where(conditions.length > 0 ? and(...conditions) : undefined)
      .orderBy(desc(schema.articles.createdAt));

    return articles.map(({ article, author }) => ({
      ...article,
      author,
    }));
  }

  async getArticleById(id: string): Promise<Article | undefined> {
    const [article] = await db
      .select()
      .from(schema.articles)
      .where(eq(schema.articles.id, id))
      .limit(1);
    return article;
  }

  async createArticle(insertArticle: InsertArticle): Promise<Article> {
    const [article] = await db
      .insert(schema.articles)
      .values(insertArticle)
      .returning();
    return article;
  }

  async updateArticle(id: string, updates: Partial<Article>): Promise<Article | undefined> {
    const [article] = await db
      .update(schema.articles)
      .set({ ...updates, updatedAt: new Date() })
      .where(eq(schema.articles.id, id))
      .returning();
    return article;
  }

  async approveArticle(id: string, moderatorId: string): Promise<Article> {
    const existing = await this.getArticleById(id);
    if (!existing) {
      throw new Error("Статья не найдена");
    }
    if (existing.status !== "pending") {
      throw new Error("Можно одобрить только статьи со статусом 'pending'");
    }

    const [article] = await db
      .update(schema.articles)
      .set({
        status: "approved",
        isPublished: true,
        moderatedBy: moderatorId,
        moderatedAt: new Date(),
        rejectionReason: null,
        updatedAt: new Date(),
      })
      .where(eq(schema.articles.id, id))
      .returning();
    return article;
  }

  async rejectArticle(id: string, moderatorId: string, reason?: string): Promise<Article> {
    if (!reason || reason.trim().length === 0) {
      throw new Error("Необходимо указать причину отклонения");
    }

    const existing = await this.getArticleById(id);
    if (!existing) {
      throw new Error("Статья не найдена");
    }
    if (existing.status !== "pending") {
      throw new Error("Можно отклонить только статьи со статусом 'pending'");
    }

    const [article] = await db
      .update(schema.articles)
      .set({
        status: "rejected",
        moderatedBy: moderatorId,
        moderatedAt: new Date(),
        rejectionReason: reason,
        updatedAt: new Date(),
      })
      .where(eq(schema.articles.id, id))
      .returning();
    return article;
  }

  async getAchievements(): Promise<Achievement[]> {
    return await db.select().from(schema.achievements);
  }

  async getUserAchievements(userId: string): Promise<Array<UserAchievement & { achievement: Achievement }>> {
    const userAchievements = await db
      .select({
        userAchievement: schema.userAchievements,
        achievement: schema.achievements,
      })
      .from(schema.userAchievements)
      .innerJoin(
        schema.achievements,
        eq(schema.userAchievements.achievementId, schema.achievements.id)
      )
      .where(eq(schema.userAchievements.userId, userId));

    return userAchievements.map(({ userAchievement, achievement }) => ({
      ...userAchievement,
      achievement,
    }));
  }

  async awardAchievement(userId: string, achievementId: string): Promise<UserAchievement> {
    const [userAchievement] = await db
      .insert(schema.userAchievements)
      .values({ userId, achievementId })
      .returning();
    return userAchievement;
  }

  async getChatMessages(orderId: string): Promise<Array<ChatMessage & { sender: Omit<User, 'passwordHash'>; receiver: Omit<User, 'passwordHash'> }>> {
    const messages = await db
      .select({
        message: schema.chatMessages,
        sender: SAFE_USER_SELECTION,
      })
      .from(schema.chatMessages)
      .innerJoin(schema.users, eq(schema.chatMessages.senderId, schema.users.id))
      .where(eq(schema.chatMessages.orderId, orderId))
      .orderBy(schema.chatMessages.createdAt);

    const messagesWithReceiver = await Promise.all(
      messages.map(async ({ message, sender }) => {
        const [receiver] = await db
          .select(SAFE_USER_SELECTION)
          .from(schema.users)
          .where(eq(schema.users.id, message.receiverId))
          .limit(1);

        return {
          ...message,
          sender,
          receiver,
        };
      })
    );

    return messagesWithReceiver;
  }

  async createChatMessage(insertMessage: InsertChatMessage): Promise<ChatMessage> {
    const [message] = await db
      .insert(schema.chatMessages)
      .values(insertMessage)
      .returning();
    return message;
  }

  async markMessagesAsRead(orderId: string, userId: string): Promise<void> {
    await db
      .update(schema.chatMessages)
      .set({ isRead: true })
      .where(
        and(
          eq(schema.chatMessages.orderId, orderId),
          eq(schema.chatMessages.receiverId, userId)
        )
      );
  }

  async createTransaction(insertTransaction: InsertTransaction): Promise<Transaction> {
    const [transaction] = await db
      .insert(schema.transactions)
      .values(insertTransaction)
      .returning();
    return transaction;
  }

  async getTransactionById(id: string): Promise<Transaction | undefined> {
    const [transaction] = await db
      .select()
      .from(schema.transactions)
      .where(eq(schema.transactions.id, id))
      .limit(1);
    return transaction;
  }

  async getTransactionByOrderId(orderId: string): Promise<Transaction | undefined> {
    const [transaction] = await db
      .select()
      .from(schema.transactions)
      .where(eq(schema.transactions.orderId, orderId))
      .limit(1);
    return transaction;
  }

  async updateTransaction(id: string, updates: Partial<Transaction>): Promise<Transaction | undefined> {
    const [transaction] = await db
      .update(schema.transactions)
      .set(updates)
      .where(eq(schema.transactions.id, id))
      .returning();
    return transaction;
  }

  async getAllUsers(): Promise<Omit<User, 'passwordHash'>[]> {
    const users = await db.select().from(schema.users);
    return users.map(({ passwordHash, ...user }) => user);
  }

  async getAllOrders(): Promise<Order[]> {
    return await db.select().from(schema.orders).orderBy(desc(schema.orders.createdAt));
  }

  async getAllReviews(): Promise<Review[]> {
    return await db.select().from(schema.reviews).orderBy(desc(schema.reviews.createdAt));
  }

  async getAdminStats(): Promise<{
    totalUsers: number;
    activeOrders: number;
    totalRevenue: string;
    newReviews: number;
  }> {
    const [usersCount] = await db
      .select({ count: sql<number>`count(*)` })
      .from(schema.users);

    const [ordersCount] = await db
      .select({ count: sql<number>`count(*)` })
      .from(schema.orders)
      .where(
        or(
          eq(schema.orders.status, 'new'),
          eq(schema.orders.status, 'accepted'),
          eq(schema.orders.status, 'in_progress')
        )
      );

    const [revenue] = await db
      .select({ total: sql<string>`COALESCE(SUM(total_price), 0)` })
      .from(schema.orders)
      .where(eq(schema.orders.status, 'completed'));

    const oneWeekAgo = new Date();
    oneWeekAgo.setDate(oneWeekAgo.getDate() - 7);

    const [reviewsCount] = await db
      .select({ count: sql<number>`count(*)` })
      .from(schema.reviews)
      .where(gte(schema.reviews.createdAt, oneWeekAgo));

    return {
      totalUsers: Number(usersCount.count) || 0,
      activeOrders: Number(ordersCount.count) || 0,
      totalRevenue: revenue.total || '0',
      newReviews: Number(reviewsCount.count) || 0,
    };
  }

  async createSupportTicket(ticket: InsertSupportTicket): Promise<SupportTicket> {
    const [newTicket] = await db
      .insert(schema.supportTickets)
      .values(ticket)
      .returning();
    return newTicket;
  }

  async getSupportTicketsByUserId(userId: string): Promise<Array<SupportTicket & { messages: SupportMessage[] }>> {
    const tickets = await db
      .select()
      .from(schema.supportTickets)
      .where(eq(schema.supportTickets.userId, userId))
      .orderBy(desc(schema.supportTickets.createdAt));

    const ticketsWithMessages = await Promise.all(
      tickets.map(async (ticket) => {
        const messages = await db
          .select()
          .from(schema.supportMessages)
          .where(eq(schema.supportMessages.ticketId, ticket.id))
          .orderBy(schema.supportMessages.createdAt);
        return { ...ticket, messages };
      })
    );

    return ticketsWithMessages;
  }

  async getSupportTicketById(id: string): Promise<(SupportTicket & { messages: SupportMessage[] }) | undefined> {
    const [ticket] = await db
      .select()
      .from(schema.supportTickets)
      .where(eq(schema.supportTickets.id, id))
      .limit(1);

    if (!ticket) return undefined;

    const messages = await db
      .select()
      .from(schema.supportMessages)
      .where(eq(schema.supportMessages.ticketId, id))
      .orderBy(schema.supportMessages.createdAt);

    return { ...ticket, messages };
  }

  async updateSupportTicket(id: string, updates: Partial<SupportTicket>): Promise<SupportTicket | undefined> {
    const [ticket] = await db
      .update(schema.supportTickets)
      .set(updates)
      .where(eq(schema.supportTickets.id, id))
      .returning();
    return ticket;
  }

  async getAllSupportTickets(): Promise<Array<SupportTicket & { user: Omit<User, 'passwordHash'>; messages: SupportMessage[] }>> {
    const tickets = await db
      .select()
      .from(schema.supportTickets)
      .orderBy(desc(schema.supportTickets.createdAt));

    const ticketsWithUserAndMessages = await Promise.all(
      tickets.map(async (ticket) => {
        const [user] = await db
          .select(SAFE_USER_SELECTION)
          .from(schema.users)
          .where(eq(schema.users.id, ticket.userId))
          .limit(1);

        const messages = await db
          .select()
          .from(schema.supportMessages)
          .where(eq(schema.supportMessages.ticketId, ticket.id))
          .orderBy(schema.supportMessages.createdAt);

        return { ...ticket, user, messages };
      })
    );

    return ticketsWithUserAndMessages;
  }

  async createSupportMessage(message: InsertSupportMessage): Promise<SupportMessage> {
    const [newMessage] = await db
      .insert(schema.supportMessages)
      .values(message)
      .returning();
    return newMessage;
  }

  async getArticleComments(articleId: string, userId?: string): Promise<Array<ArticleComment & { user: Omit<User, 'passwordHash'>; replies: any[]; hasUserUpvoted: boolean }>> {
    const comments = await db
      .select()
      .from(schema.articleComments)
      .where(
        and(
          eq(schema.articleComments.articleId, articleId),
          sql`${schema.articleComments.parentId} IS NULL`
        )
      )
      .orderBy(desc(schema.articleComments.createdAt));

    const commentsWithUserAndReplies = await Promise.all(
      comments.map(async (comment) => {
        const [user] = await db
          .select(SAFE_USER_SELECTION)
          .from(schema.users)
          .where(eq(schema.users.id, comment.userId))
          .limit(1);

        const replies = await db
          .select()
          .from(schema.articleComments)
          .where(eq(schema.articleComments.parentId, comment.id))
          .orderBy(schema.articleComments.createdAt);

        const repliesWithUser = await Promise.all(
          replies.map(async (reply) => {
            const [replyUser] = await db
              .select(SAFE_USER_SELECTION)
              .from(schema.users)
              .where(eq(schema.users.id, reply.userId))
              .limit(1);

            let replyHasUserUpvoted = false;
            if (userId) {
              const [replyVote] = await db
                .select()
                .from(schema.articleCommentVotes)
                .where(
                  and(
                    eq(schema.articleCommentVotes.commentId, reply.id),
                    eq(schema.articleCommentVotes.userId, userId)
                  )
                )
                .limit(1);
              replyHasUserUpvoted = !!replyVote;
            }

            return { ...reply, user: replyUser, hasUserUpvoted: replyHasUserUpvoted, replies: [] };
          })
        );

        let hasUserUpvoted = false;
        if (userId) {
          const [vote] = await db
            .select()
            .from(schema.articleCommentVotes)
            .where(
              and(
                eq(schema.articleCommentVotes.commentId, comment.id),
                eq(schema.articleCommentVotes.userId, userId)
              )
            )
            .limit(1);
          hasUserUpvoted = !!vote;
        }

        return { ...comment, user, replies: repliesWithUser, hasUserUpvoted };
      })
    );

    return commentsWithUserAndReplies;
  }

  async createArticleComment(comment: InsertArticleComment): Promise<ArticleComment> {
    const [newComment] = await db
      .insert(schema.articleComments)
      .values(comment)
      .returning();
    return newComment;
  }

  async updateArticleComment(id: string, updates: Partial<ArticleComment>): Promise<ArticleComment | undefined> {
    const [comment] = await db
      .update(schema.articleComments)
      .set(updates)
      .where(eq(schema.articleComments.id, id))
      .returning();
    return comment;
  }

  async getListings(filters?: { categoryId?: string; ownerId?: string; status?: "active" | "sold" | "archived"; search?: string }): Promise<Array<Listing & { owner: Omit<User, 'passwordHash'>; category: Category }>> {
    let query = db.select().from(schema.listings).$dynamic();

    const conditions = [];
    if (filters?.categoryId) {
      conditions.push(eq(schema.listings.categoryId, filters.categoryId));
    }
    if (filters?.ownerId) {
      conditions.push(eq(schema.listings.ownerId, filters.ownerId));
    }
    if (filters?.status) {
      conditions.push(eq(schema.listings.status, filters.status));
    }
    if (filters?.search) {
      conditions.push(
        or(
          like(schema.listings.title, `%${filters.search}%`),
          like(schema.listings.description, `%${filters.search}%`)
        )
      );
    }

    if (conditions.length > 0) {
      query = query.where(and(...conditions)) as any;
    }

    const listings = await query.orderBy(desc(schema.listings.createdAt));

    const ownerSelection = {
      id: schema.users.id,
      email: schema.users.email,
      role: schema.users.role,
      fullName: schema.users.fullName,
      phone: schema.users.phone,
      avatarUrl: schema.users.avatarUrl,
      city: schema.users.city,
      oauthProvider: schema.users.oauthProvider,
      oauthId: schema.users.oauthId,
      reputation: schema.users.reputation,
      latitude: schema.users.latitude,
      longitude: schema.users.longitude,
      createdAt: schema.users.createdAt,
      updatedAt: schema.users.updatedAt,
    };

    const listingsWithDetails = await Promise.all(
      listings.map(async (listing) => {
        const [owner] = await db
          .select(ownerSelection)
          .from(schema.users)
          .where(eq(schema.users.id, listing.ownerId))
          .limit(1);

        const [category] = await db
          .select()
          .from(schema.categories)
          .where(eq(schema.categories.id, listing.categoryId))
          .limit(1);

        return { ...listing, owner, category };
      })
    );

    return listingsWithDetails;
  }

  async getListingById(id: string): Promise<(Listing & { owner: Omit<User, 'passwordHash'>; category: Category }) | undefined> {
    const [listing] = await db
      .select()
      .from(schema.listings)
      .where(eq(schema.listings.id, id))
      .limit(1);

    if (!listing) return undefined;

    const ownerSelection = {
      id: schema.users.id,
      email: schema.users.email,
      role: schema.users.role,
      fullName: schema.users.fullName,
      phone: schema.users.phone,
      avatarUrl: schema.users.avatarUrl,
      city: schema.users.city,
      oauthProvider: schema.users.oauthProvider,
      oauthId: schema.users.oauthId,
      reputation: schema.users.reputation,
      latitude: schema.users.latitude,
      longitude: schema.users.longitude,
      createdAt: schema.users.createdAt,
      updatedAt: schema.users.updatedAt,
    };

    const [owner] = await db
      .select(ownerSelection)
      .from(schema.users)
      .where(eq(schema.users.id, listing.ownerId))
      .limit(1);

    const [category] = await db
      .select()
      .from(schema.categories)
      .where(eq(schema.categories.id, listing.categoryId))
      .limit(1);

    return { ...listing, owner, category };
  }

  async createListing(listing: InsertListing): Promise<Listing> {
    const [newListing] = await db
      .insert(schema.listings)
      .values(listing)
      .returning();
    return newListing;
  }

  async updateListing(id: string, updates: Partial<Listing>): Promise<Listing | undefined> {
    const [listing] = await db
      .update(schema.listings)
      .set(updates)
      .where(eq(schema.listings.id, id))
      .returning();
    return listing;
  }

  async deleteListing(id: string): Promise<void> {
    await db
      .delete(schema.listings)
      .where(eq(schema.listings.id, id));
  }

  async getCarBrands(): Promise<CarBrand[]> {
    const brands = await db
      .select()
      .from(schema.carBrands)
      .orderBy(schema.carBrands.name);
    return brands;
  }

  async getPopularCarBrands(): Promise<CarBrand[]> {
    const brands = await db
      .select()
      .from(schema.carBrands)
      .where(eq(schema.carBrands.isPopular, true))
      .orderBy(schema.carBrands.name);
    return brands;
  }

  async upvoteComment(commentId: string, userId: string): Promise<{ comment: ArticleComment; reputationChange: number }> {
    return await db.transaction(async (tx) => {
      const [comment] = await tx
        .select()
        .from(schema.articleComments)
        .where(eq(schema.articleComments.id, commentId))
        .limit(1);

      if (!comment) {
        throw new Error("Comment not found");
      }

      if (comment.userId === userId) {
        throw new Error("Cannot upvote your own comment");
      }

      const [existingVote] = await tx
        .select()
        .from(schema.articleCommentVotes)
        .where(
          and(
            eq(schema.articleCommentVotes.commentId, commentId),
            eq(schema.articleCommentVotes.userId, userId)
          )
        )
        .limit(1);

      if (existingVote) {
        return { comment, reputationChange: 0 };
      }

      try {
        await tx.insert(schema.articleCommentVotes).values({
          commentId,
          userId,
        });
      } catch (error: any) {
        if (error?.code === '23505') {
          return { comment, reputationChange: 0 };
        }
        throw error;
      }

      const [updatedComment] = await tx
        .update(schema.articleComments)
        .set({ upvotes: sql`${schema.articleComments.upvotes} + 1` })
        .where(eq(schema.articleComments.id, commentId))
        .returning();

      const [updatedAuthor] = await tx
        .update(schema.users)
        .set({ reputation: sql`${schema.users.reputation} + 1` })
        .where(eq(schema.users.id, comment.userId))
        .returning();

      const achievements = await tx
        .select()
        .from(schema.achievements)
        .where(eq(schema.achievements.criteriaType, "comment_upvotes"));

      for (const achievement of achievements) {
        if (updatedAuthor.reputation >= achievement.criteriaValue) {
          const [existingAchievement] = await tx
            .select()
            .from(schema.userAchievements)
            .where(
              and(
                eq(schema.userAchievements.userId, comment.userId),
                eq(schema.userAchievements.achievementId, achievement.id)
              )
            )
            .limit(1);

          if (!existingAchievement) {
            await tx.insert(schema.userAchievements).values({
              userId: comment.userId,
              achievementId: achievement.id,
            });
          }
        }
      }

      return { comment: updatedComment, reputationChange: 1 };
    });
  }

  async getChatsByUserId(userId: string): Promise<Array<{ orderId: string; partnerId: string; partnerName: string; lastMessage: string; lastMessageAt: Date; unreadCount: number }>> {
    const messages = await db
      .select()
      .from(schema.chatMessages)
      .where(
        or(
          eq(schema.chatMessages.senderId, userId),
          eq(schema.chatMessages.receiverId, userId)
        )
      )
      .orderBy(desc(schema.chatMessages.createdAt));

    const chatMap = new Map<string, {
      orderId: string;
      partnerId: string;
      partnerName: string;
      lastMessage: string;
      lastMessageAt: Date;
      unreadCount: number;
    }>();

    for (const message of messages) {
      if (!chatMap.has(message.orderId)) {
        const partnerId = message.senderId === userId ? message.receiverId : message.senderId;
        const [partner] = await db
          .select()
          .from(schema.users)
          .where(eq(schema.users.id, partnerId))
          .limit(1);

        const unreadMessages = await db
          .select({ count: sql<number>`count(*)` })
          .from(schema.chatMessages)
          .where(
            and(
              eq(schema.chatMessages.orderId, message.orderId),
              eq(schema.chatMessages.receiverId, userId),
              eq(schema.chatMessages.isRead, false)
            )
          );

        chatMap.set(message.orderId, {
          orderId: message.orderId,
          partnerId,
          partnerName: partner.fullName,
          lastMessage: message.message,
          lastMessageAt: message.createdAt,
          unreadCount: Number(unreadMessages[0]?.count || 0),
        });
      }
    }

    return Array.from(chatMap.values());
  }

  async getBlogPosts(filters?: { published?: boolean; category?: string }): Promise<Array<schema.BlogPost & { author: Omit<schema.User, 'passwordHash'> }>> {
    let query = db
      .select({
        post: schema.blogPosts,
        author: SAFE_USER_SELECTION,
      })
      .from(schema.blogPosts)
      .leftJoin(schema.users, eq(schema.blogPosts.authorId, schema.users.id));

    const conditions: any[] = [];
    if (filters?.published !== undefined) {
      conditions.push(eq(schema.blogPosts.published, filters.published));
    }
    if (filters?.category) {
      conditions.push(eq(schema.blogPosts.category, filters.category));
    }

    if (conditions.length > 0) {
      query = query.where(and(...conditions)) as any;
    }

    const results = await query.orderBy(desc(schema.blogPosts.createdAt));
    return results.map(r => ({ ...r.post, author: r.author! }));
  }

  async getBlogPostById(id: string): Promise<schema.BlogPost | undefined> {
    const [post] = await db
      .select()
      .from(schema.blogPosts)
      .where(eq(schema.blogPosts.id, id))
      .limit(1);
    return post;
  }

  async getBlogPostBySlug(slug: string): Promise<(schema.BlogPost & { author: Omit<schema.User, 'passwordHash'> }) | undefined> {
    const [result] = await db
      .select({
        post: schema.blogPosts,
        author: SAFE_USER_SELECTION,
      })
      .from(schema.blogPosts)
      .leftJoin(schema.users, eq(schema.blogPosts.authorId, schema.users.id))
      .where(eq(schema.blogPosts.slug, slug))
      .limit(1);

    return result ? { ...result.post, author: result.author! } : undefined;
  }

  async createBlogPost(post: schema.InsertBlogPost): Promise<schema.BlogPost> {
    const [created] = await db
      .insert(schema.blogPosts)
      .values(post)
      .returning();
    return created;
  }

  async updateBlogPost(id: string, updates: Partial<schema.BlogPost>): Promise<schema.BlogPost | undefined> {
    const [updated] = await db
      .update(schema.blogPosts)
      .set({ ...updates, updatedAt: sql`now()` })
      .where(eq(schema.blogPosts.id, id))
      .returning();
    return updated;
  }

  async publishBlogPost(id: string): Promise<schema.BlogPost | undefined> {
    const [published] = await db
      .update(schema.blogPosts)
      .set({
        published: true,
        publishedAt: sql`now()`,
        updatedAt: sql`now()`,
      })
      .where(eq(schema.blogPosts.id, id))
      .returning();
    return published;
  }
}

export const storage = new DatabaseStorage();