import { Injectable } from '@nestjs/common';
import { and, asc, count, desc, eq, inArray, sql } from 'drizzle-orm';
import { DatabaseService } from '../../common/database/database.service';
import {
  communityChannels,
  communityChatMessages,
  communityChats,
  communityPostComments,
  communityPostLikes,
  communityAnnouncementReads,
  communityAnnouncements,
  communityPosts,
  communitySettings,
  users,
} from '../../database/schema';

export type ChannelVisibility = 'MEMBERS' | 'ADMINS';
export type PostStatus = 'DRAFT' | 'PUBLISHED';
export type PostVariant = 'FULL' | 'SHORT';

export interface PostSummary {
  id: string;
  channelId: string;
  status: PostStatus;
  variant: PostVariant;
  title: string;
  excerpt: string | null;
  cover: string | null;
  authorName: string | null;
  legacyDate: string | null;
  publishedAt: Date | null;
  views: number;
  likes: number;
  comments: number;
  likedByMe: boolean;
}

/**
 * All community reads and writes. Visibility is expressed as a parameter rather
 * than read from a session here — the service decides who may see what, the
 * repository only applies it, so there is one place to audit each rule.
 */
@Injectable()
export class CommunityRepository {
  constructor(private readonly database: DatabaseService) {}

  private get db() {
    return this.database.db;
  }

  listChannels(visibilities: ChannelVisibility[]) {
    return this.db
      .select()
      .from(communityChannels)
      .where(inArray(communityChannels.visibility, visibilities))
      .orderBy(asc(communityChannels.position), asc(communityChannels.name));
  }

  async channelById(id: string) {
    const [row] = await this.db
      .select()
      .from(communityChannels)
      .where(eq(communityChannels.id, id))
      .limit(1);
    return row ?? null;
  }

  /**
   * Post summaries for the feed — deliberately without `body`, which holds a
   * whole newsletter and a cover image and would make a channel listing weigh
   * megabytes.
   */
  async listPosts(options: {
    channelIds: string[];
    statuses: PostStatus[];
    viewerId: string;
  }): Promise<PostSummary[]> {
    if (!options.channelIds.length) return [];

    const likeCount = this.db
      .select({
        postId: communityPostLikes.postId,
        value: count().as('like_count'),
      })
      .from(communityPostLikes)
      .groupBy(communityPostLikes.postId)
      .as('like_count');

    const commentCount = this.db
      .select({
        postId: communityPostComments.postId,
        value: count().as('comment_count'),
      })
      .from(communityPostComments)
      .groupBy(communityPostComments.postId)
      .as('comment_count');

    const rows = await this.db
      .select({
        id: communityPosts.id,
        channelId: communityPosts.channelId,
        status: communityPosts.status,
        variant: communityPosts.variant,
        title: communityPosts.title,
        excerpt: communityPosts.excerpt,
        cover: communityPosts.cover,
        authorName: communityPosts.authorName,
        legacyDate: communityPosts.legacyDate,
        publishedAt: communityPosts.publishedAt,
        views: communityPosts.views,
        likes: sql<number>`coalesce(${likeCount.value}, 0)`,
        comments: sql<number>`coalesce(${commentCount.value}, 0)`,
        likedByMe: sql<boolean>`${communityPostLikes.userId} is not null`,
      })
      .from(communityPosts)
      .leftJoin(likeCount, eq(likeCount.postId, communityPosts.id))
      .leftJoin(commentCount, eq(commentCount.postId, communityPosts.id))
      .leftJoin(
        communityPostLikes,
        and(
          eq(communityPostLikes.postId, communityPosts.id),
          eq(communityPostLikes.userId, options.viewerId),
        ),
      )
      .where(
        and(
          inArray(communityPosts.channelId, options.channelIds),
          inArray(communityPosts.status, options.statuses),
        ),
      )
      .orderBy(desc(communityPosts.publishedAt), desc(communityPosts.createdAt));

    return rows.map((row) => ({
      ...row,
      likes: Number(row.likes),
      comments: Number(row.comments),
    }));
  }

  async postById(id: string) {
    const [row] = await this.db
      .select()
      .from(communityPosts)
      .where(eq(communityPosts.id, id))
      .limit(1);
    return row ?? null;
  }

  async commentsForPost(postId: string) {
    return this.db
      .select({
        id: communityPostComments.id,
        authorId: communityPostComments.authorId,
        authorName: communityPostComments.authorName,
        accountName: users.displayName,
        body: communityPostComments.body,
        createdAt: communityPostComments.createdAt,
      })
      .from(communityPostComments)
      .leftJoin(users, eq(users.id, communityPostComments.authorId))
      .where(eq(communityPostComments.postId, postId))
      .orderBy(asc(communityPostComments.createdAt));
  }

  async likeCount(postId: string): Promise<number> {
    const [row] = await this.db
      .select({ value: count() })
      .from(communityPostLikes)
      .where(eq(communityPostLikes.postId, postId));
    return Number(row?.value ?? 0);
  }

  async hasLiked(postId: string, userId: string): Promise<boolean> {
    const [row] = await this.db
      .select({ postId: communityPostLikes.postId })
      .from(communityPostLikes)
      .where(and(eq(communityPostLikes.postId, postId), eq(communityPostLikes.userId, userId)))
      .limit(1);
    return Boolean(row);
  }

  async setLike(postId: string, userId: string, liked: boolean): Promise<void> {
    if (liked) {
      await this.db
        .insert(communityPostLikes)
        .values({ postId, userId })
        .onConflictDoNothing({ target: [communityPostLikes.postId, communityPostLikes.userId] });
      return;
    }
    await this.db
      .delete(communityPostLikes)
      .where(and(eq(communityPostLikes.postId, postId), eq(communityPostLikes.userId, userId)));
  }

  /** Counted in the database so concurrent readers cannot overwrite each other. */
  async incrementViews(postId: string): Promise<void> {
    await this.db
      .update(communityPosts)
      .set({ views: sql`${communityPosts.views} + 1` })
      .where(eq(communityPosts.id, postId));
  }

  async createPost(values: typeof communityPosts.$inferInsert) {
    const [row] = await this.db.insert(communityPosts).values(values).returning();
    return row;
  }

  async updatePost(id: string, patch: Partial<typeof communityPosts.$inferInsert>) {
    const [row] = await this.db
      .update(communityPosts)
      .set({ ...patch, updatedAt: new Date() })
      .where(eq(communityPosts.id, id))
      .returning();
    return row ?? null;
  }

  async deletePost(id: string): Promise<void> {
    await this.db.delete(communityPosts).where(eq(communityPosts.id, id));
  }

  async createComment(values: typeof communityPostComments.$inferInsert) {
    const [row] = await this.db.insert(communityPostComments).values(values).returning();
    return row;
  }

  async commentById(id: string) {
    const [row] = await this.db
      .select()
      .from(communityPostComments)
      .where(eq(communityPostComments.id, id))
      .limit(1);
    return row ?? null;
  }

  async deleteComment(id: string): Promise<void> {
    await this.db.delete(communityPostComments).where(eq(communityPostComments.id, id));
  }

  listChats() {
    return this.db.select().from(communityChats).orderBy(asc(communityChats.createdAt));
  }

  async chatById(id: string) {
    const [row] = await this.db
      .select()
      .from(communityChats)
      .where(eq(communityChats.id, id))
      .limit(1);
    return row ?? null;
  }

  async createChat(values: typeof communityChats.$inferInsert) {
    const [row] = await this.db.insert(communityChats).values(values).returning();
    return row;
  }

  messagesForChat(chatId: string, limit: number) {
    return this.db
      .select({
        id: communityChatMessages.id,
        authorId: communityChatMessages.authorId,
        authorName: communityChatMessages.authorName,
        accountName: users.displayName,
        body: communityChatMessages.body,
        createdAt: communityChatMessages.createdAt,
      })
      .from(communityChatMessages)
      .leftJoin(users, eq(users.id, communityChatMessages.authorId))
      .where(eq(communityChatMessages.chatId, chatId))
      .orderBy(desc(communityChatMessages.createdAt))
      .limit(limit);
  }

  async createMessage(values: typeof communityChatMessages.$inferInsert) {
    const [row] = await this.db.insert(communityChatMessages).values(values).returning();
    return row;
  }

  async createChannel(values: typeof communityChannels.$inferInsert) {
    const [row] = await this.db.insert(communityChannels).values(values).returning();
    return row;
  }

  async updateChannel(id: string, patch: Partial<typeof communityChannels.$inferInsert>) {
    const [row] = await this.db
      .update(communityChannels)
      .set({ ...patch, updatedAt: new Date() })
      .where(eq(communityChannels.id, id))
      .returning();
    return row ?? null;
  }

  async countPostsInChannel(channelId: string): Promise<number> {
    const [row] = await this.db
      .select({ value: count() })
      .from(communityPosts)
      .where(eq(communityPosts.channelId, channelId));
    return Number(row?.value ?? 0);
  }

  async deleteChannel(id: string): Promise<void> {
    await this.db.delete(communityChannels).where(eq(communityChannels.id, id));
  }

  /**
   * Announcements with their reach. Pinned first, then newest — an editor pins
   * the thing that must not scroll away.
   */
  async listAnnouncements(options: { statuses: PostStatus[]; viewerId: string }) {
    const readCount = this.db
      .select({
        announcementId: communityAnnouncementReads.announcementId,
        value: count().as('read_count'),
      })
      .from(communityAnnouncementReads)
      .groupBy(communityAnnouncementReads.announcementId)
      .as('read_count');

    const rows = await this.db
      .select({
        id: communityAnnouncements.id,
        status: communityAnnouncements.status,
        title: communityAnnouncements.title,
        summary: communityAnnouncements.summary,
        body: communityAnnouncements.body,
        cover: communityAnnouncements.cover,
        pinned: communityAnnouncements.pinned,
        authorName: communityAnnouncements.authorName,
        publishedAt: communityAnnouncements.publishedAt,
        createdAt: communityAnnouncements.createdAt,
        reads: sql<number>`coalesce(${readCount.value}, 0)`,
        readByMe: sql<boolean>`${communityAnnouncementReads.userId} is not null`,
      })
      .from(communityAnnouncements)
      .leftJoin(readCount, eq(readCount.announcementId, communityAnnouncements.id))
      .leftJoin(
        communityAnnouncementReads,
        and(
          eq(communityAnnouncementReads.announcementId, communityAnnouncements.id),
          eq(communityAnnouncementReads.userId, options.viewerId),
        ),
      )
      .where(inArray(communityAnnouncements.status, options.statuses))
      .orderBy(
        desc(communityAnnouncements.pinned),
        desc(communityAnnouncements.publishedAt),
        desc(communityAnnouncements.createdAt),
      );

    return rows.map((row) => ({ ...row, reads: Number(row.reads) }));
  }

  async announcementById(id: string) {
    const [row] = await this.db
      .select()
      .from(communityAnnouncements)
      .where(eq(communityAnnouncements.id, id))
      .limit(1);
    return row ?? null;
  }

  async createAnnouncement(values: typeof communityAnnouncements.$inferInsert) {
    const [row] = await this.db.insert(communityAnnouncements).values(values).returning();
    return row;
  }

  async updateAnnouncement(id: string, patch: Partial<typeof communityAnnouncements.$inferInsert>) {
    const [row] = await this.db
      .update(communityAnnouncements)
      .set({ ...patch, updatedAt: new Date() })
      .where(eq(communityAnnouncements.id, id))
      .returning();
    return row ?? null;
  }

  async deleteAnnouncement(id: string): Promise<void> {
    await this.db.delete(communityAnnouncements).where(eq(communityAnnouncements.id, id));
  }

  async markAnnouncementRead(announcementId: string, userId: string): Promise<void> {
    await this.db
      .insert(communityAnnouncementReads)
      .values({ announcementId, userId })
      .onConflictDoNothing({
        target: [communityAnnouncementReads.announcementId, communityAnnouncementReads.userId],
      });
  }

  /** How many accounts could see an announcement — the denominator for reach. */
  async activeMemberCount(): Promise<number> {
    const [row] = await this.db
      .select({ value: count() })
      .from(users)
      .where(eq(users.isActive, true));
    return Number(row?.value ?? 0);
  }

  async settings(): Promise<Record<string, unknown>> {
    const rows = await this.db.select().from(communitySettings);
    return Object.fromEntries(rows.map((row) => [row.key, row.value]));
  }

  async setSetting(key: string, value: unknown): Promise<void> {
    await this.db
      .insert(communitySettings)
      .values({ key, value })
      .onConflictDoUpdate({
        target: communitySettings.key,
        set: { value, updatedAt: new Date() },
      });
  }
}