import { db } from "@/lib/db";
import {
    detectedObjects,
    marketplaceMatches,
    youtubeVideos,
    youtubeChannels,
    adminModeration,
    interestPledges,
} from "@/lib/db/schema";
import { eq, and, or, desc, sql, asc } from "drizzle-orm";

export interface AdminQueueFilters {
    category?: string;
    minConfidence?: number;
    sortBy?: "confidence" | "date";
    sortOrder?: "asc" | "desc";
    limit?: number;
    offset?: number;
    minPledges?: number;
    status?: "flagged" | "pending_creator" | "live" | "rejected" | "all";
}

export class AdminModerationService {
    /**
     * Fetches the admin moderation queue (flagged detections)
     * Admins see ALL flagged items across all creators.
     */
    async getAdminQueue(filters?: AdminQueueFilters) {
        const limit = filters?.limit || 20;
        const offset = filters?.offset || 0;

        const pledgeCountSubquery = db.select({
            detectedObjectId: interestPledges.detectedObjectId,
            count: sql<number>`count(*)::int`.as('pledge_count')
        }).from(interestPledges).groupBy(interestPledges.detectedObjectId).as('pc');

        const baseQuery = db
            .select({
                detection: detectedObjects,
                video: {
                    id: youtubeVideos.id,
                    title: youtubeVideos.title,
                    thumbnailUrl: youtubeVideos.thumbnailUrl,
                },
                channel: {
                    creatorId: youtubeChannels.creatorId,
                    channelName: youtubeChannels.channelName,
                },
                marketplaceMatches: sql<Record<string, unknown>[]>`
          COALESCE(
            json_agg(
              json_build_object(
                'id', ${marketplaceMatches.id},
                'marketplace', ${marketplaceMatches.marketplace},
                'productName', ${marketplaceMatches.productName},
                'price', ${marketplaceMatches.price},
                'availability', ${marketplaceMatches.availabilityStatus},
                'affiliateUrl', ${marketplaceMatches.affiliateUrl}
              )
            ) FILTER (WHERE ${marketplaceMatches.id} IS NOT NULL),
            '[]'::json
          )
        `.as("marketplace_matches"),
                pledgeCount: sql<number>`COALESCE(${pledgeCountSubquery.count}, 0)`.as("pledge_count"),
            })
            .from(detectedObjects)
            .leftJoin(youtubeVideos, eq(detectedObjects.videoId, youtubeVideos.id))
            .leftJoin(
                youtubeChannels,
                eq(youtubeVideos.channelId, youtubeChannels.id)
            )
            .leftJoin(
                marketplaceMatches,
                eq(detectedObjects.id, marketplaceMatches.objectId)
            )
            .leftJoin(
                pledgeCountSubquery,
                eq(detectedObjects.id, pledgeCountSubquery.detectedObjectId)
            )
            .where(
                and(
                    filters?.status && filters.status !== 'all'
                        ? filters.status === 'flagged'
                            ? and(
                                or(eq(detectedObjects.status, 'flagged'), eq(detectedObjects.status, 'pending_review')),
                                eq(detectedObjects.moderationStatus, 'PENDING')
                            )
                            : filters.status === 'pending_creator'
                                ? and(eq(detectedObjects.status, 'approved'), eq(detectedObjects.moderationStatus, 'PENDING'))
                                : filters.status === 'live'
                                    ? eq(detectedObjects.moderationStatus, 'APPROVED')
                                    : filters.status === 'rejected'
                                        ? or(eq(detectedObjects.status, 'rejected'), eq(detectedObjects.moderationStatus, 'REJECTED'))
                                        : undefined
                        : !filters?.status
                            ? or(
                                and(eq(detectedObjects.status, 'flagged'), eq(detectedObjects.moderationStatus, 'PENDING')),
                                sql`COALESCE(${pledgeCountSubquery.count}, 0) >= ${filters?.minPledges ?? 0}`
                            )
                            : undefined,
                    filters?.category ? eq(detectedObjects.category, filters.category as any) : undefined,
                    filters?.minConfidence
                        ? sql`${detectedObjects.confidenceScore} >= ${filters.minConfidence}`
                        : undefined
                )
            )
            .groupBy(
                detectedObjects.id,
                youtubeVideos.id,
                youtubeChannels.id,
                pledgeCountSubquery.count
            );

        // Sorting logic
        let orderBy;
        if (filters?.sortBy === "confidence") {
            orderBy =
                filters.sortOrder === "asc"
                    ? asc(detectedObjects.confidenceScore)
                    : desc(detectedObjects.confidenceScore);
        } else {
            orderBy =
                filters?.sortOrder === "asc"
                    ? asc(detectedObjects.createdAt)
                    : desc(detectedObjects.createdAt);
        }

        console.log('--- getAdminQueue Filters ---', filters);
        const queue = (await baseQuery.orderBy(orderBy).limit(limit).offset(offset)) as any[];
        console.log('--- getAdminQueue Result Count ---', queue.length);

        return queue.map(item => ({
            ...item,
            video: item.video?.id ? item.video : { id: 'unknown', title: 'Unknown Video', thumbnailUrl: '' },
            channel: item.channel?.creatorId ? item.channel : { creatorId: 'unknown', channelName: 'Unknown Creator' },
        }));
    }

    /**
     * Fetches stats for the admin moderation dashboard
     */
    async getAdminQueueStats() {
        const flaggedCountResult = await db
            .select({ count: sql<number>`count(*)::int` })
            .from(detectedObjects)
            .where(
                and(
                    or(eq(detectedObjects.status, 'flagged'), eq(detectedObjects.status, 'pending_review')),
                    eq(detectedObjects.moderationStatus, 'PENDING')
                )
            );

        const pendingCreatorCountResult = await db
            .select({ count: sql<number>`count(*)::int` })
            .from(detectedObjects)
            .where(and(eq(detectedObjects.status, "approved"), eq(detectedObjects.moderationStatus, "PENDING")));

        const liveCountResult = await db
            .select({ count: sql<number>`count(*)::int` })
            .from(detectedObjects)
            .where(eq(detectedObjects.moderationStatus, "APPROVED"));

        const rejectedCountResult = await db
            .select({ count: sql<number>`count(*)::int` })
            .from(detectedObjects)
            .where(or(eq(detectedObjects.status, "rejected"), eq(detectedObjects.moderationStatus, "REJECTED")));

        const totalCountResult = await db
            .select({ count: sql<number>`count(*)::int` })
            .from(detectedObjects)
            .where(eq(detectedObjects.moderationStatus, "APPROVED"));

        return {
            flagged: flaggedCountResult[0]?.count || 0,
            pendingCreator: pendingCreatorCountResult[0]?.count || 0,
            live: liveCountResult[0]?.count || 0,
            rejected: rejectedCountResult[0]?.count || 0,
            total: totalCountResult[0]?.count || 0,
        };
    }

    /**
     * Fetches the audit log for a specific detection
     */
    async getAdminAuditLog(detectionId: string) {
        return db
            .select()
            .from(adminModeration)
            .where(eq(adminModeration.detectionId, detectionId))
            .orderBy(desc(adminModeration.createdAt));
    }
}