folx-tv/server/storage.ts

import { type Article, type InsertArticle, articles, articleViews, type InsertPushSubscription, type PushSubscription, pushSubscriptions } from "@shared/schema";
import { db } from "./db";
import { eq, desc, sql, count, and } from "drizzle-orm";
import crypto from "crypto";

export interface IStorage {
  getArticles(): Promise<Article[]>;
  getArticleBySlug(slug: string): Promise<Article | undefined>;
  getArticleById(id: number): Promise<Article | undefined>;
  getFeaturedArticles(): Promise<Article[]>;
  getPopularArticles(limit: number): Promise<Article[]>;
  getArticlesByCategory(category: string): Promise<Article[]>;
  getArticlesByCategoryPaginated(category: string, page: number, limit: number): Promise<{ articles: Article[]; total: number }>;
  createArticle(article: InsertArticle): Promise<Article>;
  updateArticle(id: number, article: Partial<InsertArticle>): Promise<Article | undefined>;
  incrementViews(id: number, ip: string): Promise<boolean>;
  deleteArticle(id: number): Promise<void>;
  savePushSubscription(sub: InsertPushSubscription): Promise<PushSubscription>;
  deletePushSubscription(endpoint: string): Promise<void>;
  getAllPushSubscriptions(): Promise<PushSubscription[]>;
  getPushSubscriptionCount(): Promise<number>;
}

export class DatabaseStorage implements IStorage {
  async getArticles(): Promise<Article[]> {
    return db.select().from(articles).orderBy(desc(articles.publishedAt));
  }

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

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

  async getFeaturedArticles(): Promise<Article[]> {
    return db.select().from(articles).orderBy(desc(articles.publishedAt)).limit(9);
  }

  async getPopularArticles(limit: number): Promise<Article[]> {
    return db.select().from(articles).orderBy(desc(articles.views)).limit(limit);
  }

  async getArticlesByCategory(category: string): Promise<Article[]> {
    return db.select().from(articles).where(eq(articles.category, category)).orderBy(desc(articles.publishedAt));
  }

  async getArticlesByCategoryPaginated(category: string, page: number, limit: number): Promise<{ articles: Article[]; total: number }> {
    const offset = (page - 1) * limit;
    const [totalResult] = await db.select({ count: count() }).from(articles).where(eq(articles.category, category));
    const total = totalResult.count;
    const items = await db.select().from(articles).where(eq(articles.category, category)).orderBy(desc(articles.publishedAt)).limit(limit).offset(offset);
    return { articles: items, total };
  }

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

  async updateArticle(id: number, article: Partial<InsertArticle>): Promise<Article | undefined> {
    const [updated] = await db.update(articles).set(article).where(eq(articles.id, id)).returning();
    return updated;
  }

  async incrementViews(id: number, ip: string): Promise<boolean> {
    const ipHash = crypto.createHash("sha256").update(ip + "folx-salt").digest("hex").substring(0, 16);
    const [existing] = await db.select().from(articleViews)
      .where(and(eq(articleViews.articleId, id), eq(articleViews.ipHash, ipHash)))
      .limit(1);
    if (existing) return false;
    await db.insert(articleViews).values({ articleId: id, ipHash });
    await db.update(articles).set({ views: sql`${articles.views} + 1` }).where(eq(articles.id, id));
    return true;
  }

  async deleteArticle(id: number): Promise<void> {
    await db.delete(articles).where(eq(articles.id, id));
  }

  async savePushSubscription(sub: InsertPushSubscription): Promise<PushSubscription> {
    const [existing] = await db.select().from(pushSubscriptions).where(eq(pushSubscriptions.endpoint, sub.endpoint)).limit(1);
    if (existing) {
      const [updated] = await db.update(pushSubscriptions).set(sub).where(eq(pushSubscriptions.endpoint, sub.endpoint)).returning();
      return updated;
    }
    const [created] = await db.insert(pushSubscriptions).values(sub).returning();
    return created;
  }

  async deletePushSubscription(endpoint: string): Promise<void> {
    await db.delete(pushSubscriptions).where(eq(pushSubscriptions.endpoint, endpoint));
  }

  async getAllPushSubscriptions(): Promise<PushSubscription[]> {
    return db.select().from(pushSubscriptions);
  }

  async getPushSubscriptionCount(): Promise<number> {
    const [result] = await db.select({ count: count() }).from(pushSubscriptions);
    return result.count;
  }
}

export const storage = new DatabaseStorage();