== Transactions ==

=== Transaction for getting build details ===

{{{
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,
                quantity: buildComponentsTable.numComponents
            })
            .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,
                    quantity: c.quantity
            })),
            reviews: reviews.map(r => ({
                username: r.username,
                content: r.content,
                createdAt: r.createdAt
            })),
            ratingStatistics: ratingStatistics,
            userRating,
            userReview,
            isFavorite
        };
    });
}}}

=== Transaction for cloning existing build ===

{{{
db.transaction(async (tx) => {
        const [buildToClone] = await tx
            .select()
            .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
            });

        if(!newBuild) return null;

        const existing = await tx
            .select({
                componentId: buildComponentsTable.componentId,
                numComponents: buildComponentsTable.numComponents
            })
            .from(buildComponentsTable)
            .where(eq(buildComponentsTable.buildId, buildId));

        if (existing.length > 0) {
            await tx.insert(buildComponentsTable).values(
                existing.map((r) => ({
                    buildId: newBuild.id,
                    componentId: r.componentId,
                    numComponents: r.numComponents
                })),
            );
        }

        return newBuild.id;
    });
}}}

=== Transaction for getting current build state ===

{{{
db.transaction(async (tx) => {
        const [build] = await tx
            .select({
                id: buildsTable.id,
                userId: buildsTable.userId,
                isApproved: buildsTable.isApproved,
                name: buildsTable.name,
                description: buildsTable.description,
                totalPrice: buildsTable.totalPrice,
            })
            .from(buildsTable)
            .where(
                and(
                    eq(buildsTable.id, buildId),
                    eq(buildsTable.userId, userId)
                )
            )
            .limit(1);

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

        const components = await tx
            .select({
                componentId: buildComponentsTable.componentId,
                quantity: buildComponentsTable.numComponents
            })
            .from(buildComponentsTable)
            .where(
                eq(buildComponentsTable.buildId, buildId)
            );

        return {
            build,
            components: components.map(c => ({
                id: c.componentId,
                quantity: c.quantity
            }))
        };
    });
}}}

=== Transaction for adding a new component to the database ===

{{{
db.transaction(async (tx) => {
        const [newComponent] = await tx
            .insert(componentsTable)
            .values({
                name: name,
                brand: brand,
                price: price.toFixed(2),
                imgUrl: imgUrl,
                type: type
            })
            .returning({
                id: componentsTable.id
            });

        const componentId = newComponent.id;

        const config = typeConfigMap[type as ComponentType];

        await tx
            .insert(config.table)
            .values({
                componentId: componentId,
                ...specificData
            });

        if (type === 'case') {
            if (specificData.storageFormFactors) {
                await tx.insert(caseStorageFormFactorsTable).values(
                    specificData.storageFormFactors.map((sf: any) => ({
                        caseId: componentId,
                        formFactor: sf.formFactor,
                        numSlots: sf.numSlots,
                    }))
                );
            }
            if (specificData.psFormFactors) {
                await tx.insert(casePsFormFactorsTable).values(
                    specificData.psFormFactors.map((pf: any) => ({
                        caseId: componentId,
                        formFactor: pf.formFactor,
                    }))
                );
            }
            if (specificData.moboFormFactors) {
                await tx.insert(caseMoboFormFactorsTable).values(
                    specificData.moboFormFactors.map((mf: any) => ({
                        caseId: componentId,
                        formFactor: mf.formFactor,
                    }))
                );
            }
        }

        if (type === 'cooler' && specificData.cpuSockets) {
            await tx.insert(coolerCPUSocketsTable).values(
                specificData.cpuSockets.map((socket: any) => ({ coolerId: componentId, socket }))
            );
        }

        return componentId;
    });
}}}

=== Transaction for adding a component to a build ===

{{{
db.transaction(async (tx) => {
        const [build] = await tx
            .select()
            .from(buildsTable)
            .where(
                and(
                    eq(buildsTable.id, buildId),
                    eq(buildsTable.userId, userId)
                )
            )
            .limit(1);

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

        await tx
            .insert(buildComponentsTable)
            .values({
                buildId,
                componentId,
                numComponents: 1
            })
            .onConflictDoUpdate({
                target: [buildComponentsTable.buildId, buildComponentsTable.componentId],
                set: {
                    numComponents: sql`${buildComponentsTable.numComponents} + 1`
                }
            });

        const buildComponents = await tx
            .select({
                price:  componentsTable.price,
                quantity: buildComponentsTable.numComponents
            })
            .from(buildComponentsTable)
            .innerJoin(
                componentsTable,
                eq(buildComponentsTable.componentId, componentsTable.id)
            )
            .where(
                eq(buildComponentsTable.buildId, buildId)
            );

        // const totalPrice = buildComponents.reduce((sum, c) =>
        //     sum + (Number(c.price) * c.quantity), 0
        // );

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

        return buildId;
    })
}}}

=== Transaction for removing a component from a build ===

{{{
db.transaction(async (tx) => {
        const [build] = await tx
            .select()
            .from(buildsTable)
            .where(
                and(
                    eq(buildsTable.id, buildId),
                    eq(buildsTable.userId, userId)
                )
            )
            .limit(1);

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

        const [existing] = await tx
            .select({
                quantity: buildComponentsTable.numComponents
            })
            .from(buildComponentsTable)
            .where(
                and(
                    eq(buildComponentsTable.buildId, buildId),
                    eq(buildComponentsTable.componentId, componentId)
                )
            )
            .limit(1);

        if (!existing) return null;

        if (existing.quantity > 1) {
            await tx
                .update(buildComponentsTable)
                .set({
                    numComponents: sql`${buildComponentsTable.numComponents} - 1`
                })
                .where(
                    and(
                        eq(buildComponentsTable.buildId, buildId),
                        eq(buildComponentsTable.componentId, componentId)
                    )
                );
        } else {
            await tx
                .delete(buildComponentsTable)
                .where(
                    and(
                        eq(buildComponentsTable.buildId, buildId),
                        eq(buildComponentsTable.componentId, componentId)
                    )
                );
        }

        const buildComponents = await tx
            .select({
                price:  componentsTable.price,
                quantity: buildComponentsTable.numComponents
            })
            .from(buildComponentsTable)
            .innerJoin(
                componentsTable,
                eq(buildComponentsTable.componentId, componentsTable.id)
            )
            .where(
                eq(buildComponentsTable.buildId, buildId)
            );

        // const totalPrice = buildComponents.reduce((sum, c) =>
        //     sum + (Number(c.price) * c.quantity), 0
        // );

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

        return componentId;
    })
}}}