update sql queries

7 files changed, 177 insertions, 344 deletions

diff --git a/apps/web/src/app/page.tsx b/apps/web/src/app/page.tsx
index ccceffe0..f3dc1bd4 100644
--- a/apps/web/src/app/page.tsx
+++ b/apps/web/src/app/page.tsx
@@ -7,13 +7,10 @@ import {
   storedContent,
   users,
 } from "@/server/db/schema";
-import { eq, inArray } from "drizzle-orm";
+import { and, eq, inArray, not } from "drizzle-orm";
 import { cookies, headers } from "next/headers";
 import { redirect } from "next/navigation";
-import Sidebar from "@/components/Sidebar/index";
-import Main from "@/components/Main";
-import MessagePoster from "./MessagePoster";
-import { transformContent } from "../../types/memory";
+import { fetchContentForSpace, fetchFreeMemories, transformContent } from "../../types/memory";
 import { MemoryProvider } from "@/contexts/MemoryContext";
 import Content from "./content";
 
@@ -49,35 +46,36 @@ export default async function Home() {
     return redirect("/api/auth/signin");
   }
 
-  // Fetch all content for the user
-  const contents = await db
+
+  const collectedSpaces = await db
     .select()
-    .from(storedContent)
-    .where(eq(storedContent.user, userData.id))
-    .all();
+    .from(space)
+    .where(
+      and(eq(storedContent.user, userData.id), not(eq(space.name, "none"))),
+    );
+
+
+  // Fetch only first 3 content of each spaces
+  let contents: typeof storedContent.$inferSelect[] = []
+	
+	await Promise.all([collectedSpaces.forEach(async (space) => {
+		contents = [...contents, ...(await fetchContentForSpace(space.id, {
+			offset: 0,
+			limit: 3
+		}))]
+	})])
 
-  const collectedSpaces =
-    contents.length > 0 ? await transformContent(contents) : [];
+	// freeMemories
+	const freeMemories = await fetchFreeMemories(userData.id)
 
-  // collectedSpaces.push({
-  //   id: 2,
-  //   title: "Test",
-  //   content: [
-  //     {
-  //       id: 1,
-  //       content: "Test",
-  //       title: "Vscode",
-  //       description: "Test",
-  //       url: "https://vscode-remake.vercel.app/",
-  //       savedAt: new Date(),
-  //       baseUrl: "https://vscode-remake.vercel.app/",
-  //       image: "https://vscode-remake.vercel.app/favicon.svg",
-  //     },
-  //   ],
-  // });
+  collectedSpaces.push({
+    id: 1,
+    name: "Cool tech",
+    user: null,
+  });
 
   return (
-    <MemoryProvider spaces={collectedSpaces} freeMemories={[]}>
+    <MemoryProvider spaces={collectedSpaces} freeMemories={freeMemories} cachedMemories={contents}>
       <Content jwt={token} />
       {/* <MessagePoster jwt={token} /> */}
     </MemoryProvider>
diff --git a/apps/web/src/components/Sidebar/CategoryItem.tsx b/apps/web/src/components/Sidebar/CategoryItem.tsx
deleted file mode 100644
index 7fb571b5..00000000
--- a/apps/web/src/components/Sidebar/CategoryItem.tsx
+++ /dev/null
@@ -1,298 +0,0 @@
-"use client";
-import { cleanUrl } from "@/lib/utils";
-import { StoredContent } from "@/server/db/schema";
-import {
-  DropdownMenu,
-  DropdownMenuTrigger,
-  DropdownMenuContent,
-  DropdownMenuItem,
-} from "../ui/dropdown-menu";
-import { Label } from "../ui/label";
-import {
-  ArrowUpRight,
-  MoreHorizontal,
-  Tags,
-  ChevronDown,
-  Edit3,
-  Trash2,
-  Save,
-  ChevronRight,
-  Plus,
-  Minus,
-} from "lucide-react";
-import { useState } from "react";
-import {
-  Drawer,
-  DrawerContent,
-  DrawerHeader,
-  DrawerTitle,
-  DrawerDescription,
-  DrawerFooter,
-  DrawerClose,
-} from "../ui/drawer";
-import { Input } from "../ui/input";
-import { Textarea } from "../ui/textarea";
-import { Popover, PopoverContent, PopoverTrigger } from "../ui/popover";
-import {
-  AnimatePresence,
-  motion,
-  Reorder,
-  useMotionValue,
-} from "framer-motion";
-
-const pages: StoredContent[] = [
-  {
-    id: 1,
-    content: "",
-    title: "Visual Studio Code",
-    url: "https://code.visualstudio.com",
-    description: "",
-    image: "https://code.visualstudio.com/favicon.ico",
-    baseUrl: "https://code.visualstudio.com",
-    savedAt: new Date(),
-  },
-  {
-    id: 2,
-    content: "",
-    title: "yxshv/vscode: An unofficial remake of vscode's landing page",
-    url: "https://github.com/yxshv/vscode",
-    description: "",
-    image: "https://github.com/favicon.ico",
-    baseUrl: "https://github.com",
-    savedAt: new Date(),
-  },
-  {
-    id: 3,
-    content: "",
-    title: "yxshv/vscode: An unofficial remake of vscode's landing page",
-    url: "https://github.com/yxshv/vscode",
-    description: "",
-    image: "https://github.com/favicon.ico",
-    baseUrl: "https://github.com",
-    savedAt: new Date(),
-  },
-  {
-    id: 4,
-    content: "",
-    title: "yxshv/vscode: An unofficial remake of vscode's landing page",
-    url: "https://github.com/yxshv/vscode",
-    description: "",
-    image: "https://github.com/favicon.ico",
-    baseUrl: "https://github.com",
-    savedAt: new Date(),
-  },
-  {
-    id: 5,
-    content: "",
-    title: "yxshv/vscode: An unofficial remake of vscode's landing page",
-    url: "https://github.com/yxshv/vscode",
-    description: "",
-    image: "https://github.com/favicon.ico",
-    baseUrl: "https://github.com",
-    savedAt: new Date(),
-  },
-  {
-    id: 6,
-    content: "",
-    title: "yxshv/vscode: An unofficial remake of vscode's landing page",
-    url: "https://github.com/yxshv/vscode",
-    description: "",
-    image: "https://github.com/favicon.ico",
-    baseUrl: "https://github.com",
-    savedAt: new Date(),
-  },
-  {
-    id: 7,
-    content: "",
-    title: "yxshv/vscode: An unofficial remake of vscode's landing page",
-    url: "https://github.com/yxshv/vscode",
-    description: "",
-    image: "https://github.com/favicon.ico",
-    baseUrl: "https://github.com",
-    savedAt: new Date(),
-  },
-  {
-    id: 8,
-    content: "",
-    title: "yxshv/vscode: An unofficial remake of vscode's landing page",
-    url: "https://github.com/yxshv/vscode",
-    description: "",
-    image: "https://github.com/favicon.ico",
-    baseUrl: "https://github.com",
-    savedAt: new Date(),
-  },
-  {
-    id: 9,
-    content: "",
-    title: "yxshv/vscode: An unofficial remake of vscode's landing page",
-    url: "https://github.com/yxshv/vscode",
-    description: "",
-    image: "https://github.com/favicon.ico",
-    baseUrl: "https://github.com",
-    savedAt: new Date(),
-  },
-];
-export const CategoryItem: React.FC<{ item: StoredContent }> = ({ item }) => {
-  const [isExpanded, setIsExpanded] = useState(false);
-  const [isEditDrawerOpen, setIsEditDrawerOpen] = useState(false);
-
-  const [items, setItems] = useState<StoredContent[]>(pages);
-
-  return (
-    <>
-      <div className="hover:bg-rgray-5 has-[button:focus]:bg-rgray-5 flex w-full items-center rounded-full py-1 pl-3 pr-2 transition [&:hover>button>div>[data-down-icon]]:scale-125 [&:hover>button>div>[data-down-icon]]:opacity-100 [&:hover>button>div>[data-down-icon]]:delay-150 [&:hover>button>div>[data-tags-icon]]:scale-75 [&:hover>button>div>[data-tags-icon]]:opacity-0 [&:hover>button>div>[data-tags-icon]]:delay-0 [&:hover>button]:opacity-100">
-        <button
-          onClick={() => setIsExpanded((prev) => !prev)}
-          className="flex w-full items-center gap-2 focus-visible:outline-none"
-        >
-          <div className="relative h-5 min-w-5">
-            <Tags
-              data-tags-icon
-              className="z-1 h-5 w-5 transition-[transform,opacity] delay-150 duration-150"
-              strokeWidth={1.5}
-            />
-            <ChevronDown
-              data-down-icon
-              className={`absolute left-1/2 top-1/2 z-[2] h-4 w-4 min-w-4 -translate-x-1/2 -translate-y-1/2 scale-75 opacity-0 transition-[transform,opacity] duration-150 ${isExpanded ? "rotate-180" : "rotate-0"}`}
-              strokeWidth={1.5}
-            />
-          </div>
-
-          <span className="w-full truncate text-nowrap text-left">
-            {item.title ?? "Untitled website"}
-          </span>
-        </button>
-        <Drawer
-          shouldScaleBackground
-          open={isEditDrawerOpen}
-          onOpenChange={setIsEditDrawerOpen}
-        >
-          <DrawerContent className="pb-10 lg:px-[25vw]">
-            <DrawerHeader className="relative mt-10 px-0">
-              <DrawerTitle className=" flex w-full justify-between">
-                Edit Page Details
-              </DrawerTitle>
-              <DrawerDescription>Change the page details</DrawerDescription>
-              <a
-                target="_blank"
-                href={item.url}
-                className="text-rgray-11/90 bg-rgray-3 text-md absolute right-0 top-0 flex w-min translate-y-1/2 items-center justify-center gap-1 rounded-full px-5 py-1"
-              >
-                <img src={item.image ?? "/brain.png"} className="h-4 w-4" />
-                {cleanUrl(item.url)}
-              </a>
-            </DrawerHeader>
-
-            <div className="mt-5">
-              <Label>Title</Label>
-              <Input
-                className=""
-                required
-                value={item.title ?? ""}
-                placeholder={item.title ?? "Enter the title for the page"}
-              />
-            </div>
-            <div className="mt-5">
-              <Label>Additional Context</Label>
-              <Textarea
-                className=""
-                value={item.content ?? ""}
-                placeholder={"Enter additional context for this page"}
-              />
-            </div>
-            <DrawerFooter className="flex flex-row-reverse items-center justify-end px-0 pt-5">
-              <DrawerClose className="flex items-center justify-center rounded-md px-3 py-2 ring-2 ring-transparent transition hover:bg-blue-100 hover:text-blue-400 focus-visible:bg-blue-100 focus-visible:text-blue-400 focus-visible:outline-none focus-visible:ring-blue-200 dark:hover:bg-blue-100/10 dark:focus-visible:bg-blue-100/10 dark:focus-visible:ring-blue-200/30">
-                <Save className="mr-2 h-4 w-4 " strokeWidth={1.5} />
-                Save
-              </DrawerClose>
-              <DrawerClose className="hover:bg-rgray-3 focus-visible:bg-rgray-4 focus-visible:ring-rgray-7 flex items-center justify-center rounded-md px-3 py-2 ring-2 ring-transparent transition focus-visible:outline-none">
-                Cancel
-              </DrawerClose>
-              <DrawerClose className="mr-auto flex items-center justify-center rounded-md bg-red-100 px-3 py-2 text-red-400 ring-2 ring-transparent transition focus-visible:outline-none focus-visible:ring-red-200 dark:bg-red-100/10 dark:focus-visible:ring-red-200/30">
-                <Trash2 className="mr-2 h-4 w-4 " strokeWidth={1.5} />
-                Delete
-              </DrawerClose>
-            </DrawerFooter>
-          </DrawerContent>
-        </Drawer>
-      </div>
-      <AnimatePresence>
-        {isExpanded && (
-          <Reorder.Group
-            axis="y"
-            values={items}
-            onReorder={setItems}
-            as="div"
-            initial={{ height: 0 }}
-            animate={{ height: "auto" }}
-            exit={{
-              height: 0,
-              transition: {},
-            }}
-            layoutScroll
-            className="flex max-h-32 w-full flex-col items-center overflow-y-auto pl-7"
-          >
-            <AnimatePresence>
-              {items.map((item, i) => (
-                <CategoryPage
-                  key={item.id}
-                  index={i}
-                  item={item}
-                  onRemove={() =>
-                    setItems((prev) => prev.filter((_, index) => i !== index))
-                  }
-                />
-              ))}
-            </AnimatePresence>
-          </Reorder.Group>
-        )}
-      </AnimatePresence>
-    </>
-  );
-};
-
-export const CategoryPage: React.FC<{
-  item: StoredContent;
-  index: number;
-  onRemove?: () => void;
-}> = ({ item, onRemove, index }) => {
-  return (
-    <Reorder.Item
-      value={item}
-      as="div"
-      key={index}
-      exit={{ opacity: 0, scale: 0.8 }}
-      dragListener={false}
-      className="hover:bg-rgray-5  has-[a:focus]:bg-rgray-5 flex w-full items-center rounded-full py-1 pl-3 pr-2 transition [&:hover>a>div>[data-icon]]:scale-125 [&:hover>a>div>[data-icon]]:opacity-100 [&:hover>a>div>[data-icon]]:delay-150 [&:hover>a>div>img]:scale-75 [&:hover>a>div>img]:opacity-0 [&:hover>a>div>img]:delay-0 [&:hover>button]:opacity-100"
-    >
-      <a
-        href={item.url}
-        target="_blank"
-        className="flex w-[90%] items-center gap-2 focus-visible:outline-none"
-      >
-        <div className="relative h-4 min-w-4">
-          <img
-            src={item.image ?? "/brain.png"}
-            alt={item.title ?? "Untitiled website"}
-            className="z-1 h-4 w-4 transition-[transform,opacity] delay-150 duration-150"
-          />
-          <ArrowUpRight
-            data-icon
-            className="absolute left-1/2 top-1/2 z-[2] h-4 w-4 min-w-4 -translate-x-1/2 -translate-y-1/2 scale-75 opacity-0 transition-[transform,opacity] duration-150"
-            strokeWidth={1.5}
-          />
-        </div>
-
-        <span className="w-full truncate text-nowrap">
-          {item.title ?? "Untitled website"}
-        </span>
-      </a>
-      <button
-        onClick={() => onRemove?.()}
-        className="ml-auto w-4 min-w-4 rounded-[0.15rem] opacity-0 focus-visible:opacity-100 focus-visible:outline-none"
-      >
-        <Minus className="h-4 w-4 min-w-4" />
-      </button>
-    </Reorder.Item>
-  );
-};
diff --git a/apps/web/src/components/Sidebar/ExpandedSpace.tsx b/apps/web/src/components/Sidebar/ExpandedSpace.tsx
new file mode 100644
index 00000000..22ad4201
--- /dev/null
+++ b/apps/web/src/components/Sidebar/ExpandedSpace.tsx
@@ -0,0 +1,56 @@
+import { useMemory } from "@/contexts/MemoryContext"
+import { space, StoredContent } from '@/server/db/schema'
+
+const tempSpace: typeof space.$inferSelect = {
+  id: 1,
+  name: "Cool tech",
+  user: null
+}
+
+const spaceItems: StoredContent[] = [
+  {
+    id: 1,
+    title: "How to build a website",
+    content: "This is how you build a website",
+    baseUrl: "https://www.google.com",
+    url: "https://www.google.com",
+    savedAt: new Date(),
+    type: "page",
+    description: null,
+    image: '/icons/logo_without_bg.png',
+  },
+  {
+    id: 2,
+    title: "How to build a editor",
+    content: "This is how you build a website",
+    baseUrl: "https://www.google.com",
+    url: "https://www.google.com",
+    savedAt: new Date(),
+    type: "page",
+    description: null,
+    image: '/icons/logo_without_bg.png',
+  },
+  {
+    id: 3,
+    title: "How to build a editor",
+    content: "This is how you build a website",
+    baseUrl: "",
+    url: "",
+    savedAt: new Date(),
+    type: "note",
+    description: null,
+    image: '/icons/logo_without_bg.png',
+  },
+
+]
+
+export function ExpandedSpace({spaceId}: {spaceId: number}) {
+
+  const { spaces } = useMemory()
+
+  return (
+    <div className="text-rgray-11 flex w-full flex-col items-start py-8 text-left">
+
+    </div>
+  )
+}
\ No newline at end of file
diff --git a/apps/web/src/components/Sidebar/MemoriesBar.tsx b/apps/web/src/components/Sidebar/MemoriesBar.tsx
index 66c3138b..133e9957 100644
--- a/apps/web/src/components/Sidebar/MemoriesBar.tsx
+++ b/apps/web/src/components/Sidebar/MemoriesBar.tsx
@@ -41,6 +41,8 @@ import useViewport from "@/hooks/useViewport";
 import useTouchHold from "@/hooks/useTouchHold";
 import { DialogTrigger } from "@radix-ui/react-dialog";
 import { AddMemoryPage, NoteAddPage, SpaceAddPage } from "./AddMemoryDialog";
+import { ExpandedSpace } from "./ExpandedSpace";
+import { StoredSpace } from "@/server/db/schema";
 
 export function MemoriesBar() {
   const [parent, enableAnimations] = useAutoAnimate();
@@ -51,6 +53,17 @@ export function MemoriesBar() {
     "page" | "note" | "space" | null
   >(null);
 
+  const [expandedSpace, setExpandedSpace] = useState<number | null>(null);
+
+  if (expandedSpace) {
+    return (
+      <ExpandedSpace
+        spaceId={expandedSpace}
+        // close={() => setExpandedSpace(null)}
+      />
+    );
+  }
+
   return (
     <div className="text-rgray-11 flex w-full flex-col items-start py-8 text-left">
       <div className="w-full px-8">
@@ -113,6 +126,7 @@ export function MemoriesBar() {
           <SpaceItem
             onDelete={() => deleteSpace(space.id)}
             key={space.id}
+            onClick={() => setExpandedSpace(space.id)}
             {...space}
           />
         ))}
@@ -132,11 +146,11 @@ const SpaceExitVariant: Variant = {
 };
 
 export function SpaceItem({
-  title,
-  content,
+  name,
   id,
   onDelete,
-}: CollectedSpaces & { onDelete: () => void }) {
+  onClick,
+}: StoredSpace & { onDelete: () => void, onClick?: () => void }) {
   const [itemRef, animateItem] = useAnimate();
   const { width } = useViewport();
 
@@ -152,10 +166,11 @@ export function SpaceItem({
     <motion.div
       ref={itemRef}
       {...touchEventProps}
+      onClick={onClick}
       className="hover:bg-rgray-2 has-[[data-state='true']]:bg-rgray-2 has-[[data-space-text]:focus-visible]:bg-rgray-2 has-[[data-space-text]:focus-visible]:ring-rgray-7 [&:has-[[data-space-text]:focus-visible]>[data-more-button]]:opacity-100 relative flex select-none flex-col-reverse items-center justify-center rounded-md p-2 pb-4 text-center font-normal ring-transparent transition has-[[data-space-text]:focus-visible]:outline-none has-[[data-space-text]:focus-visible]:ring-2 md:has-[[data-state='true']]:bg-transparent [&:hover>[data-more-button]]:opacity-100"
     >
       <button data-space-text className="focus-visible:outline-none">
-        {title}
+        {name}
       </button>
       <SpaceMoreButton
         isOpen={moreDropdownOpen}
diff --git a/apps/web/src/contexts/MemoryContext.tsx b/apps/web/src/contexts/MemoryContext.tsx
index 68a22434..46242a63 100644
--- a/apps/web/src/contexts/MemoryContext.tsx
+++ b/apps/web/src/contexts/MemoryContext.tsx
@@ -1,40 +1,47 @@
 "use client";
 import React, { useCallback } from "react";
 import { CollectedSpaces } from "../../types/memory";
-import { StoredContent, storedContent } from "@/server/db/schema";
-import { useSession } from "next-auth/react";
+import { StoredContent, storedContent, StoredSpace } from "@/server/db/schema";
 import { addMemory } from "@/actions/db";
 
 // temperory (will change)
 export const MemoryContext = React.createContext<{
-  spaces: CollectedSpaces[];
+  spaces: StoredSpace[];
   deleteSpace: (id: number) => Promise<void>;
   freeMemories: StoredContent[];
-  addSpace: (space: CollectedSpaces) => Promise<void>;
+  addSpace: (space: StoredSpace) => Promise<void>;
   addMemory: (
     memory: typeof storedContent.$inferInsert,
     spaces?: number[],
   ) => Promise<void>;
+  cachedMemories: StoredContent[];
 }>({
   spaces: [],
   freeMemories: [],
   addMemory: async () => {},
   addSpace: async () => {},
   deleteSpace: async () => {},
+  cachedMemories: [],
 });
 
 export const MemoryProvider: React.FC<
   {
-    spaces: CollectedSpaces[];
+    spaces: StoredSpace[];
     freeMemories: StoredContent[];
+		cachedMemories: StoredContent[]
   } & React.PropsWithChildren
-> = ({ children, spaces: initalSpaces, freeMemories: initialFreeMemories }) => {
-  const [spaces, setSpaces] = React.useState<CollectedSpaces[]>(initalSpaces);
+> = ({ children, spaces: initalSpaces, freeMemories: initialFreeMemories, cachedMemories: initialCachedMemories }) => {
+
+  const [spaces, setSpaces] = React.useState<StoredSpace[]>(initalSpaces);
   const [freeMemories, setFreeMemories] =
     React.useState<StoredContent[]>(initialFreeMemories);
 
+  const [cachedMemories, setCachedMemories] = React.useState<StoredContent[]>(
+    initialCachedMemories
+  );
+
   const addSpace = useCallback(
-    async (space: CollectedSpaces) => {
+    async (space: StoredSpace) => {
       setSpaces((prev) => [...prev, space]);
     },
     [spaces],
@@ -68,6 +75,7 @@ export const MemoryProvider: React.FC<
         addSpace,
         deleteSpace,
         freeMemories,
+        cachedMemories,
         addMemory: _addMemory,
       }}
     >
diff --git a/apps/web/src/server/db/schema.ts b/apps/web/src/server/db/schema.ts
index ea90e5e9..c66cb590 100644
--- a/apps/web/src/server/db/schema.ts
+++ b/apps/web/src/server/db/schema.ts
@@ -131,3 +131,4 @@ export const space = createTable(
 );
 
 export type StoredContent = Omit<typeof storedContent.$inferSelect, "user">;
+export type StoredSpace = typeof space.$inferSelect;
diff --git a/apps/web/types/memory.tsx b/apps/web/types/memory.tsx
index 63e1469f..ff0dc94c 100644
--- a/apps/web/types/memory.tsx
+++ b/apps/web/types/memory.tsx
@@ -1,12 +1,62 @@
 import { db } from "@/server/db";
-import { contentToSpace, space, StoredContent } from "@/server/db/schema";
-import { eq, inArray, or } from "drizzle-orm";
+import {
+  contentToSpace,
+  space,
+  storedContent,
+  StoredContent,
+} from "@/server/db/schema";
+import { asc, and, eq, inArray, notExists } from "drizzle-orm";
+
+export async function fetchContentForSpace(
+  spaceId: number,
+  range?: {
+    offset: number;
+    limit: number;
+  },
+) {
+
+  const query = db
+    .select()
+    .from(storedContent)
+    .where(
+      inArray(
+        storedContent.id,
+        db.select().from(space).where(eq(space.id, spaceId)),
+      ),
+    ).orderBy(asc(storedContent.title))
+
+	return range ? await query.limit(range.limit).offset(range.offset) : await query.all()
+}
+
+export async function fetchFreeMemories(
+	userId: string,
+	range?: {
+		offset: number;
+		limit: number;
+	}
+) {
+	const query = db
+    .select()
+    .from(storedContent)
+    .where(
+			and(
+				notExists(
+					db.select().from(contentToSpace).where(eq(contentToSpace.contentId, storedContent.id)),
+				),
+				eq(storedContent.user, userId),
+			)
+      
+    ).orderBy(asc(storedContent.title))
+
+	return range ? await query.limit(range.limit).offset(range.offset) : await query.all()
+}
 
 export const transformContent = async (
   content: StoredContent[],
+  limit?: number,
 ): Promise<CollectedSpaces[]> => {
   // Retrieve spaces and their associated content from the database
-  const spacesWithContent = await db
+  const query = db
     .select({
       id: space.id,
       name: space.name,
@@ -19,8 +69,11 @@ export const transformContent = async (
         contentToSpace.contentId,
         content.map((c: StoredContent) => c.id),
       ),
-    )
-    .all();
+    );
+
+  const spacesWithContent = limit
+    ? await query.limit(limit)
+    : await query.all();
 
   // Group content by id
   const contentById = content.reduce(