source: database/drizzle/queries/builds.ts@ 76b980b

import type { Database } from "../db";
import {
    buildComponentsTable,
    buildsTable,
    componentsTable,
    favoriteBuildsTable,
    ratingBuildsTable,
    reviewsTable, usersTable
} from "../schema";
import {eq, desc, and, sql, ilike, asc} from "drizzle-orm";
import {inArray} from "drizzle-orm/sql/expressions/conditions";

export async function getPendingBuilds(db: Database) {
    const pendingBuildsList = await db
        .select({
            id: buildsTable.id,
            user_id: buildsTable.userId,
            name: buildsTable.name,
            created_at: buildsTable.createdAt,
            total_price: buildsTable.totalPrice
        })
        .from(buildsTable)
        .where(
            eq(buildsTable.isApproved, false)
        );

    return pendingBuildsList;
}

export async function getUserBuilds(db: Database, userId: number) {
    const userBuildsList = await db
        .select({
            id: buildsTable.id,
            user_id: buildsTable.userId,
            name: buildsTable.name,
            created_at: buildsTable.createdAt,
            total_price: buildsTable.totalPrice
        })
        .from(buildsTable)
        .where(
            eq(buildsTable.userId, userId)
        );

    return userBuildsList;
}

export async function setBuildApprovalStatus(db: Database, buildId: number, isApproved: boolean){
    const result = await db
        .update(buildsTable)
        .set({
            isApproved: isApproved
        })
        .where(
            and(
                eq(buildsTable.id, buildId),
                eq(buildsTable.isApproved, !isApproved)
            )
        )
        .returning({
            id: buildsTable.id
        })

    return result.length;
}

export async function getFavoriteBuilds(db: Database, userId: number) {
    const favoriteBuildsList = await db
        .select({
            id: buildsTable.id,
            user_id: buildsTable.userId,
            name: buildsTable.name,
            created_at: buildsTable.createdAt,
            total_price: buildsTable.totalPrice
        })
        .from(buildsTable)
        .innerJoin(
            favoriteBuildsTable,
            eq(buildsTable.id, favoriteBuildsTable.buildId)
        )
        .where(
            eq(favoriteBuildsTable.userId, userId)
        );

    return favoriteBuildsList;
}

export async function getApprovedBuilds(db: Database, limit?: number, sort?: string, q?: string) {
    let queryConditions = [eq(buildsTable.isApproved, true)];
    let sortConditions = [];

    if (q) {
        queryConditions.push(
            ilike(buildsTable.name, `%${q}%`)
        );
    }

    switch(sort) {
        case 'price_asc':
            sortConditions.push(
                asc(buildsTable.totalPrice)
            );
            break;
        case 'price_desc':
            sortConditions.push(
                desc(buildsTable.totalPrice)
            );
            break;
        case 'rating_desc':
            sortConditions.push(
                desc(sql<number>`COALESCE(AVG(${ratingBuildsTable.value}::float),0)`)
            );
            break;
        case 'oldest':
            sortConditions.push(
                asc(buildsTable.createdAt)
            );
            break;
        case 'newest':
            sortConditions.push(
                desc(buildsTable.createdAt)
            );
            break;
        default:
            sortConditions.push(
                desc(buildsTable.createdAt)
            );
            break;
        }

    const approvedBuildsList = await db
        .select({
            id: buildsTable.id,
            user_id: buildsTable.userId,
            name: buildsTable.name,
            created_at: buildsTable.createdAt,
            total_price: buildsTable.totalPrice,
            avgRating: sql<number>`AVG(${ratingBuildsTable.value}::float)`
        })
        .from(buildsTable)
        .leftJoin(
            ratingBuildsTable,
            eq(buildsTable.id, ratingBuildsTable.buildId)
        )
        .groupBy(
            buildsTable.userId,
            buildsTable.name,
            buildsTable.createdAt,
            buildsTable.totalPrice
        )
        .where(
            and (
                ...queryConditions
            )
        )
        .orderBy(
            ...sortConditions
        )
        .limit(limit || 100); // 100 placeholder

    return approvedBuildsList;
}

export async function getHighestRankedBuilds(db: Database, limit? : number) {
    const highestRankedBuildsList = await db
        .select({
            id: buildsTable.id,
            user_id: buildsTable.userId,
            name: buildsTable.name,
            created_at: buildsTable.createdAt,
            total_price: buildsTable.totalPrice,
            avgRating: sql<number>`AVG(${ratingBuildsTable.value}::float)`
        })
        .from(buildsTable)
        .innerJoin(
            ratingBuildsTable,
            eq(buildsTable.id, ratingBuildsTable.buildId)
        )
        .where(
            eq(buildsTable.isApproved, true)
        )
        .groupBy(buildsTable.id)
        .orderBy(
            desc(sql<number>`AVG(${ratingBuildsTable.value}::float)`)
        )
        .limit(limit || 100); // 100 placeholder

    return highestRankedBuildsList;
}

export async function getBuildDetails(db: Database, buildId: number, userId?: number) {
    return db.transaction(async (tx) => {
        const [buildDetails] = await tx
            .select({
                id: buildsTable.id,
                userId: buildsTable.userId,
                name: buildsTable.name,
                createdAt: buildsTable.createdAt,
                description: buildsTable.description,
                totalPrice: buildsTable.totalPrice,
                isApproved: buildsTable.isApproved,
                creator: usersTable.username
            })
            .from(buildsTable)
            .innerJoin(
                usersTable,
                eq(buildsTable.userId, usersTable.id)
            )
            .where(
                eq(buildsTable.id, buildId)
            )
            .limit(1);

        if (!buildDetails) return null;

        const components = await tx
            .select({
                componentId: buildComponentsTable.componentId,
                component: componentsTable
            })
            .from(buildComponentsTable)
            .innerJoin(
                componentsTable,
                eq(buildComponentsTable.componentId, componentsTable.id)
            )
            .where(
                eq(buildComponentsTable.buildId, buildId)
            );

        const reviews = await tx
            .select({
                username: usersTable.username,
                content: reviewsTable.content,
                createdAt: reviewsTable.createdAt
            })
            .from(reviewsTable)
            .innerJoin(
                usersTable,
                eq(reviewsTable.userId, usersTable.id)
            )
            .where(
                eq(reviewsTable.buildId, buildId)
            )
            .orderBy(
                desc(reviewsTable.createdAt)
            );

        let [ratingStatistics] = await tx
            .select({
                averageRating: sql<number>`COALESCE(AVG(${ratingBuildsTable.value}::float), 0)`.as("averageRating"),
                ratingCount: sql<number>`COUNT(${ratingBuildsTable.value})`.as("ratingCount")
            })
            .from(ratingBuildsTable)
            .where(
                eq(ratingBuildsTable.buildId, buildId)
            )
            .groupBy(ratingBuildsTable.buildId);

        ratingStatistics = {
            averageRating: Number(ratingStatistics?.averageRating ?? 0),
            ratingCount: Number(ratingStatistics?.ratingCount ?? 0),
        }

        let userRating = null;
        let isFavorite = false;
        let userReview = null;

        if(userId) {
            const [rating] = await tx
                .select()
                .from(ratingBuildsTable)
                .where(
                    and(
                        eq(ratingBuildsTable.buildId, buildId),
                        eq(ratingBuildsTable.userId, userId)
                    )
                )
                .limit(1);

            userRating = rating?.value ? Number(rating.value) : null;

            const [favorite] = await tx
                .select()
                .from(favoriteBuildsTable)
                .where(
                    and(
                        eq(favoriteBuildsTable.buildId, buildId),
                        eq(favoriteBuildsTable.userId, userId)
                    )
                )
                .limit(1);

            isFavorite = !!favorite;

            const [review] = await tx
                .select()
                .from(reviewsTable)
                .where(
                    and(
                        eq(reviewsTable.buildId, buildId),
                        eq(reviewsTable.userId, userId)
                    )
                )
                .limit(1);

            userReview = review?.content;
        }

        return {
            ...buildDetails,
            components: components.map(c => c.component),
            reviews: reviews.map(r => ({
                username: r.username,
                content: r.content,
                createdAt: r.createdAt
            })),
            ratingStatistics: ratingStatistics,
            userRating,
            userReview,
            isFavorite
        };
    });
}

export async function toggleFavoriteBuild(db: Database, userId: number, buildId: number) {
    const existing = await db
        .select()
        .from(favoriteBuildsTable)
        .where(
            and(
                eq(favoriteBuildsTable.userId, userId),
                eq(favoriteBuildsTable.buildId, buildId)
            )
        )
        .limit(1);

    if (existing.length) {
        await db
            .delete(favoriteBuildsTable)
            .where(
                and(
                    eq(favoriteBuildsTable.userId, userId),
                    eq(favoriteBuildsTable.buildId, buildId)
                )
            );

        return { favorite: false };
    }

    await db
        .insert(favoriteBuildsTable)
        .values({
            userId,
            buildId,
        });

    return { favorite: true };
}

export async function setBuildRating(db: Database, userId: number, buildId: number, value: number) {
    const [result] = await db
        .insert(ratingBuildsTable)
        .values({
            userId,
            buildId,
            value: value
        })
        .onConflictDoUpdate({
            target: [ratingBuildsTable.userId, ratingBuildsTable.buildId],
            set: {
                value: value,
            },
        })
        .returning({
            userId: ratingBuildsTable.userId,
            buildId: ratingBuildsTable.buildId,
            value: ratingBuildsTable.value
        })

    return result;
}

export async function setBuildReview(db: Database, userId: number,  buildId: number, content: string) {
    const [result] = await db
        .insert(reviewsTable)
        .values({
            userId,
            buildId,
            content,
            createdAt: new Date().toISOString().split('T')[0]
        })
        .onConflictDoUpdate({
            target: [reviewsTable.userId, reviewsTable.buildId],
            set: {
                content: content,
                createdAt: new Date().toISOString().split('T')[0]
            },
        })
        .returning({
            userId: reviewsTable.userId,
            buildId: reviewsTable.buildId,
            content: reviewsTable.content,
            createdAt: reviewsTable.createdAt
        })

    return result;
}

export async function cloneBuild(db: Database, userId: number, buildId: number) {
    return db.transaction(async (tx) => {
        const [buildToClone] = await tx
            .select({
                id: buildsTable.id,
                userId: buildsTable.userId,
                name: buildsTable.name,
                description: buildsTable.description,
                totalPrice: buildsTable.totalPrice,
            })
            .from(buildsTable)
            .where(
                eq(buildsTable.id, buildId)
            )
            .limit(1);

        if (!buildToClone) return null;

        const [newBuild] = await tx
            .insert(buildsTable)
            .values({
                userId: userId,
                name: `${buildToClone.name} (copy)`,
                createdAt: new Date().toISOString().split('T')[0],
                description: buildToClone.description,
                totalPrice: buildToClone.totalPrice,
                isApproved: false
            })
            .returning({
                id: buildsTable.id
            });

        const components = await tx
            .select()
            .from(buildComponentsTable)
            .where(
                eq(buildComponentsTable.buildId, buildId)
            );

        if(components.length) {
            await tx
                .insert(buildComponentsTable)
                .values(
                    components.map(component => ({
                        buildId: newBuild.id,
                        componentId: component.componentId
                    }))
                );
        }

        const [clonedBuild] = await tx
            .select({
                id: buildsTable.id,
                userId: buildsTable.userId,
                name: buildsTable.name,
                createdAt: buildsTable.createdAt,
                description: buildsTable.description,
                totalPrice: buildsTable.totalPrice
            })
            .from(buildsTable)
            .innerJoin(
                usersTable,
                eq(buildsTable.userId, usersTable.id)
            )
            .where(
                eq(buildsTable.id, newBuild.id)
            )
            .limit(1);

        const clonedComponents = await tx
            .select({
                componentId: buildComponentsTable.componentId,
                component: componentsTable
            })
            .from(buildComponentsTable)
            .innerJoin(
                componentsTable,
                eq(buildComponentsTable.componentId, componentsTable.id)
            )
            .where(
                eq(buildComponentsTable.buildId, newBuild.id)
            );

        return {
            ...clonedBuild,
            components: clonedComponents.map(c => c.component)
        };
    });
}

export async function deleteBuild(db: Database, userId: number, buildId: number) {
    const result = await db
        .delete(buildsTable)
        .where(
            and(
                eq(buildsTable.id, buildId),
                eq(buildsTable.userId, userId)
            )
        )
        .returning({
            id: buildsTable.id
        })

    return result.length;
}

export async function addNewBuild(db: Database, userId: number, name: string, description: string, componentIds: number[]) {
    return db.transaction(async (tx) => {
        const components = await tx
            .select({
                price:  componentsTable.price
            })
            .from(componentsTable)
            .where(
                inArray(componentsTable.id, componentIds)
            );

        const totalPrice = components.reduce((sum, c) => sum + Number(c.price), 0);

        const [newBuild] = await tx
            .insert(buildsTable)
            .values({
                userId: userId,
                name: name,
                createdAt: new Date().toISOString().split('T')[0],
                description: description,
                totalPrice: totalPrice.toFixed(2),
                isApproved: false
            })
            .returning({
                id: buildsTable.id
            });

        if(components.length) {
            await tx.insert(buildComponentsTable)
                .values(
                    componentIds.map(componentId => ({
                        buildId: newBuild.id,
                        componentId: componentId
                    }))
                );
        }

        return newBuild.id;
    });
}

export async function editBuild(db: Database, userId: number, buildId: number, name: string, description: string, componentIds: number[]) {
    return db.transaction(async (tx) => {
        const [build] = await tx
            .select({
                id: buildsTable.id,
                userId: buildsTable.userId,
                isApproved: buildsTable.isApproved
            })
            .from(buildsTable)
            .where(
                and(
                    eq(buildsTable.id, buildId),
                    eq(buildsTable.userId, userId)
                )
            )
            .limit(1);

        if (!build) return null;
        if (build.isApproved) return null;

        const components = await tx
            .select({
                id: componentsTable.id,
                price: componentsTable.price
            })
            .from(componentsTable)
            .where(
                inArray(componentsTable.id, componentIds)
            );

        const totalPrice = components.reduce((sum, c) => sum + Number(c.price), 0);

        await tx
            .update(buildsTable)
            .set({
                name: name,
                description: description,
                totalPrice: totalPrice.toFixed(2),
            })
            .where(
                and(
                    eq(buildsTable.id, buildId),
                    eq(buildsTable.userId, userId)
                )
            );

        await tx
            .delete(buildComponentsTable)
            .where(
                eq(buildComponentsTable.buildId, buildId)
            );

        if(components.length) {
            await tx.insert(buildComponentsTable)
                .values(
                    componentIds.map(componentId => ({
                        buildId: buildId,
                        componentId: componentId
                    }))
                );
        }

        return build.id;
    });
}