import { sqlite } from "https://esm.town/v/std/sqlite";
import { asc, desc, eq, sql, or, like } from "npm:drizzle-orm";
import { drizzle } from "npm:drizzle-orm/libsql";
import { integer, sqliteTable, text } from "npm:drizzle-orm/sqlite-core";
export type BedtimeStory = {
color: string;
animal: string;
adjective: string;
activity: string;
title: string;
story: string;
summary: string;
pictureUrl: string;
};
const bedtimeStories = sqliteTable("bedtime_stories", {
id: integer('id').primaryKey(),
color: text("color").notNull(),
animal: text("animal").notNull(),
adjective: text("adjective").notNull(),
activity: text("activity").notNull(),
title: text("title").notNull(),
story: text("story").notNull(),
summary: text("summary"),
pictureUrl: text("picture_url"),
});
const schema = { bedtimeStories };
const db = drizzle(sqlite as any, { schema });
export const setupDB = async (reset = false) => {
if (reset) {
await sqlite.execute(`DROP TABLE bedtime_stories`);
}
await sqlite.execute(`
CREATE TABLE IF NOT EXISTS bedtime_stories (
id INTEGER PRIMARY KEY AUTOINCREMENT,
color TEXT NOT NULL,
animal TEXT NOT NULL,
adjective TEXT NOT NULL,
activity TEXT NOT NULL,
title TEXT NOT NULL,
story TEXT NOT NULL,
summary TEXT,
picture_url TEXT
);`);
return;
};
export const getAllBedtimeStories = async () => {
await setupDB();
return db.query.bedtimeStories.findMany({
orderBy: [asc(bedtimeStories.title)],
});
};
export const getBedtimeStoryById = async ( id: BedtimeStory['id']) : BedtimeStory => {
await setupDB();
return db.query.bedtimeStories.findFirst({
where: eq(bedtimeStories.id, id),
});
};
export const createBedtimeStory = async (
story: BedtimeStory
) => {
await setupDB();
console.log('>>> createBedtimeStory', story);
const result = await db.insert(bedtimeStories).values(story);
console.log('>>> createBedtimeStory', result, result.lastInsertRowid);
const createdStory = await getBedtimeStoryById(result.lastInsertRowid);
return createdStory;
};
export const updateBedtimeStoryWithSummaryAndPictureUrl = async (
id: number,
summary: string,
pictureUrl: string
) => {
await setupDB();
return db
.update(bedtimeStories)
.set({ summary: summary, pictureUrl: pictureUrl })
.where(eq(bedtimeStories.id, id));
};
export const getRecentBedtimeStories = async (
limit: number