🃏 Anki Deck Importer

\n );\n}\n\nfunction ViewDeck() {\n const [cards, setCards] = useState([]);\n const [error, setError] = useState(\"\");\n const [editingCard, setEditingCard] = useState<{ id: number; field: string } | null>(null);\n const [sortColumn, setSortColumn] = useState(\"last_random_time\");\n const [sortDirection, setSortDirection] = useState<\"asc\" | \"desc\">(\"desc\");\n\n useEffect(() => {\n fetchCards();\n }, []);\n\n async function fetchCards() {\n try {\n const response = await fetch(\"/view\");\n if (!response.ok) {\n throw new Error(\"Failed to fetch cards\");\n }\n const data = await response.json();\n setCards(data);\n } catch (err) {\n setError(err.message);\n }\n }\n\n const handleSort = (column: string) => {\n // If clicking the same column, toggle direction\n if (sortColumn === column) {\n setSortDirection(sortDirection === \"asc\" ? \"desc\" : \"asc\");\n } else {\n // If new column, start with descending for last_random_time, ascending for others\n setSortColumn(column);\n setSortDirection(column === \"last_random_time\" ? \"desc\" : \"asc\");\n }\n\n // Perform client-side sorting\n const sortedCards = [...cards].sort((a, b) => {\n if (a[column] == null || b[column] == null) return 0;\n\n // Handle numeric columns\n if ([\"id\", \"random_count\"].includes(column)) {\n return sortDirection === \"asc\"\n ? a[column] - b[column]\n : b[column] - a[column];\n }\n\n // Handle string columns (case-insensitive)\n if (typeof a[column] === \"string\") {\n return sortDirection === \"asc\"\n ? a[column].localeCompare(b[column])\n : b[column].localeCompare(a[column]);\n }\n\n // Handle date columns\n if ([\"created_at\", \"last_random_time\"].includes(column)) {\n return sortDirection === \"asc\"\n ? new Date(a[column]).getTime() - new Date(b[column]).getTime()\n : new Date(b[column]).getTime() - new Date(a[column]).getTime();\n }\n\n return 0;\n });\n\n setCards(sortedCards);\n };\n\n const renderSortIcon = (column: string) => {\n if (sortColumn !== column) return null;\n return sortDirection === \"asc\" ? \"▲\" : \"▼\";\n };\n\n const handleEditStart = (id: number, field: string) => {\n setEditingCard({ id, field });\n };\n\n const handleEditSave = async (id: number, field: string, newValue: string) => {\n try {\n const response = await fetch(\"/update\", {\n method: \"POST\",\n body: JSON.stringify({ id, field, value: newValue }),\n headers: {\n \"Content-Type\": \"application/json\",\n },\n });\n\n if (!response.ok) {\n throw new Error(\"Failed to update card\");\n }\n\n // Update local state\n setCards(prevCards => prevCards.map(card => card.id === id ? { ...card, [field]: newValue } : card));\n\n setEditingCard(null);\n } catch (err) {\n setError(err.message);\n }\n };\n\n const renderEditableCell = (card: any, field: string) => {\n const isEditing = editingCard?.id === card.id && editingCard?.field === field;\n\n if (isEditing) {\n return (\n \n handleEditSave(card.id, field, e.target.value)}\n onKeyDown={(e) => {\n if (e.key === \"Enter\") {\n handleEditSave(card.id, field, (e.target as HTMLInputElement).value);\n }\n }}\n autoFocus\n style={{ width: \"100%\", border: \"1px solid #007bff\" }}\n />\n \n );\n }\n\n return (\n \n {card[field]}\n handleEditStart(card.id, field)}\n style={{\n position: \"absolute\",\n right: \"0\",\n top: \"0\",\n backgroundColor: \"transparent\",\n border: \"none\",\n color: \"#007bff\",\n cursor: \"pointer\",\n }}\n >\n ✏️\n \n \n );\n };\n\n return (\n

📚 Anki Deck Cards

\n {error &&

{error}

}\n \n \n \n handleSort(\"id\")}\n >\n ID {renderSortIcon(\"id\")}\n \n handleSort(\"clue\")}\n >\n Clue {renderSortIcon(\"clue\")}\n \n handleSort(\"answer\")}\n >\n Answer {renderSortIcon(\"answer\")}\n \n handleSort(\"created_at\")}\n >\n Created At {renderSortIcon(\"created_at\")}\n \n handleSort(\"random_count\")}\n >\n Times appeared {renderSortIcon(\"random_count\")}\n \n handleSort(\"last_random_time\")}\n >\n Last appeared {renderSortIcon(\"last_random_time\")}\n \n \n \n \n {cards.map((card: any) => (\n \n \n {renderEditableCell(card, \"clue\")}\n {renderEditableCell(card, \"answer\")}\n \n \n \n \n ))}\n \n

{card.id}	{card.created_at}	{card.random_count}	{card.last_random_time}

\n );\n}\n\n// Rest of the code remains unchanged from the previous implementation\nfunction RandomCard() {\n const [randomCard, setRandomCard] = useState<\n {\n katakanaWord: string;\n englishWord: string;\n exampleSentence: string;\n } | null\n >(null);\n const [error, setError] = useState(\"\");\n\n const fetchRandomCard = async () => {\n try {\n const response = await fetch(\"/random\");\n if (!response.ok) {\n throw new Error(\"Failed to fetch random card\");\n }\n const data = await response.json();\n setRandomCard(data);\n } catch (err) {\n setError(err.message);\n }\n };\n\n return (\n

🎲 Random Card

\n \n Get Random Card\n \n\n {error &&

{error}

}\n\n {randomCard && (\n \n

\n Katakana Word: {randomCard.katakanaWord}\n

\n English Word: {randomCard.englishWord}\n

\n Example Sentence: {randomCard.exampleSentence}\n

\n )}\n \n );\n}\n\nfunction App() {\n const [view, setView] = useState<\"import\" | \"view\" | \"random\">(\"import\");\n\n return (\n

\n \n setView(\"import\")}\n style={{\n backgroundColor: view === \"import\" ? \"#007bff\" : \"#6c757d\",\n color: \"white\",\n border: \"none\",\n padding: \"10px 20px\",\n }}\n >\n Import Deck\n \n setView(\"view\")}\n style={{\n backgroundColor: view === \"view\" ? \"#007bff\" : \"#6c757d\",\n color: \"white\",\n border: \"none\",\n padding: \"10px 20px\",\n }}\n >\n View Deck\n \n setView(\"random\")}\n style={{\n backgroundColor: view === \"random\" ? \"#007bff\" : \"#6c757d\",\n color: \"white\",\n border: \"none\",\n padding: \"10px 20px\",\n }}\n >\n Random Card\n \n

\n\n {view === \"import\" && }\n {view === \"view\" && }\n {view === \"random\" && }\n\n \n View Source\n \n \n );\n}\n\nfunction client() {\n createRoot(document.getElementById(\"root\")).render();\n}\nif (typeof document !== \"undefined\") { client(); }\n\nexport default async function server(request: Request): Promise {\n const { sqlite } = await import(\"https://esm.town/v/stevekrouse/sqlite\");\n const { OpenAI } = await import(\"https://esm.town/v/std/openai\");\n const KEY = \"ankiDeckImporterFrontend\";\n const SCHEMA_VERSION = 2;\n\n // last two rows are backfilled from ${KEY}_anki_cards_1\n await sqlite.execute(`\n CREATE TABLE IF NOT EXISTS ${KEY}_anki_cards_${SCHEMA_VERSION} (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n clue TEXT NOT NULL,\n answer TEXT NOT NULL,\n created_at DATETIME DEFAULT CURRENT_TIMESTAMP,\n \n random_count INTEGER DEFAULT 0,\n last_random_time DATETIME DEFAULT CURRENT_TIMESTAMP\n )\n `);\n\n const url = new URL(request.url);\n\n if (request.method === \"POST\" && url.pathname === \"/submit\") {\n try {\n const { deckText } = await request.json();\n const cards = deckText.split(\"\\n\")\n .filter(line => line.trim() !== \"\")\n .map(line => line.split(\"\\t\"));\n\n for (const [clue, answer] of cards) {\n if (clue && answer) {\n await sqlite.execute(\n `\n INSERT INTO ${KEY}_anki_cards_${SCHEMA_VERSION} (clue, answer) \n VALUES (?, ?)\n `,\n [clue, answer],\n );\n }\n }\n\n return new Response(`Successfully imported ${cards.length} cards!`, { status: 200 });\n } catch (error) {\n return new Response(`Error: ${error.message}`, { status: 500 });\n }\n }\n\n if (request.method === \"GET\" && url.pathname === \"/view\") {\n try {\n const result = await sqlite.execute(`\n SELECT * FROM ${KEY}_anki_cards_${SCHEMA_VERSION} \n ORDER BY last_random_time DESC\n `);\n\n return new Response(JSON.stringify(result.rows), {\n headers: { \"Content-Type\": \"application/json\" },\n });\n } catch (error) {\n return new Response(`Error: ${error.message}`, { status: 500 });\n }\n }\n\n if (request.method === \"POST\" && url.pathname === \"/update\") {\n try {\n const { id, field, value } = await request.json();\n\n // Validate input\n if (![\"clue\", \"answer\"].includes(field)) {\n return new Response(\"Invalid field\", { status: 400 });\n }\n\n await sqlite.execute(\n `\n UPDATE ${KEY}_anki_cards_${SCHEMA_VERSION} \n SET ${field} = ? \n WHERE id = ?\n `,\n [value, id],\n );\n\n return new Response(\"Card updated successfully\", { status: 200 });\n } catch (error) {\n return new Response(`Error: ${error.message}`, { status: 500 });\n }\n }\n\n if (request.method === \"GET\" && (url.pathname === \"/random\" || url.pathname === \"/random.json\")) {\n try {\n const result = await sqlite.execute(`\n SELECT * FROM ${KEY}_anki_cards_${SCHEMA_VERSION} \n ORDER BY RANDOM() \n LIMIT 1\n `);\n\n if (result.rows.length === 0) {\n return new Response(\"No cards found\", { status: 404 });\n }\n\n const card = result.rows[0];\n\n // Update random_count and last_random_time\n await sqlite.execute(\n `\n UPDATE ${KEY}_anki_cards_${SCHEMA_VERSION} \n SET random_count = ?, last_random_time = ?\n WHERE id = ?\n `,\n [\n parseInt(card.random_count ?? 0) + 1,\n new Date().toISOString().slice(0, 19).replace(\"T\", \" \"),\n card.id,\n ],\n );\n\n const openai = new OpenAI();\n\n const completion = await openai.chat.completions.create({\n messages: [\n {\n role: \"system\",\n content:\n \"You are a helpful assistant that converts Japanese words to the specified format. Always respond in valid JSON.\",\n },\n {\n role: \"user\",\n content: `Convert this Japanese word to the following format:\n- Remove any parenthetical explanations or HTML\n- Create a katakana version of the word\n- Create an English example sentence with the katakana version of the word in place of the original word\n\nKatakana: ${card.clue}\nEnglish word: ${card.answer}\nJSON Format:\n{\n \"katakanaWord\": \"...\",\n \"englishWord\": \"...\",\n \"exampleSentence\": \"...\"\n}`,\n },\n ],\n model: \"gpt-4o-mini\",\n response_format: { type: \"json_object\" },\n });\n\n const parsedResponse = JSON.parse(completion.choices[0].message.content);\n\n return new Response(JSON.stringify(parsedResponse), {\n headers: { \"Content-Type\": \"application/json\" },\n });\n } catch (error) {\n return new Response(`Error: ${error.message}`, { status: 500 });\n }\n }\n\n return new Response(\n `\n \n \n Anki Deck Importer\n \n \n \n

\n \n \n \n \n `,\n {\n headers: { \"Content-Type\": \"text/html\" },\n },\n );\n}",[],[104],"files",[163,183],"counts",{"_195":33,"_196":171,"_197":171,"_198":199,"_202":203},"pulls","branches","forks","envVars",{"_200":33,"_201":33},"count","emptyCount","connections",{"_200":33,"_204":33},"unfilledCount","contents",[],{"_108":184,"_100":104,"_166":121,"_168":-5,"_169":151,"_170":150,"_172":114,"_110":208,"_105":186,"_174":187,"_176":188,"_178":22,"_179":-5,"_103":209},["D",1734983052913],[104],"upstreamProject","upstreamBranch","emptyEnvVars","isNew","flags",[],"routes/_app.x.$handle.$name.branch.$branch.code.$",{"_98":99,"_100":101,"_102":46,"_105":-7,"_103":104,"_106":218,"_161":223,"_191":232,"_175":233,"_205":236,"_237":-5,"_238":-5,"_239":-5,"_240":-5,"_241":242,"_198":-5},{"_108":109,"_100":101,"_110":219,"_112":-5,"_113":114,"_115":116,"_117":-5,"_118":-5,"_14":119,"_120":121,"_122":114,"_123":-5,"_124":-5,"_125":33,"_16":126,"_127":-5,"_128":-5,"_129":-5,"_130":-5,"_131":132,"_133":132,"_134":135,"_136":135,"_137":135,"_138":132,"_139":132,"_23":220,"_143":221,"_148":222,"_160":22},["D",1746226417737],{"_98":99,"_141":142},{"_98":99,"_145":142,"_14":119,"_146":147},{"_105":150,"_100":46,"_108":151,"_152":109,"_153":-5,"_154":-5,"_155":-5,"_156":-5,"_157":-5,"_158":101,"_159":99},[224,228],{"_108":164,"_100":165,"_166":167,"_168":-5,"_169":151,"_170":171,"_172":114,"_110":225,"_105":33,"_174":175,"_176":177,"_178":22,"_179":-5,"_180":226,"_103":227},["D",1732417581699],[],[165],{"_108":184,"_100":104,"_166":121,"_168":-5,"_169":151,"_170":150,"_172":114,"_110":229,"_105":186,"_174":187,"_176":188,"_178":22,"_179":-5,"_180":230,"_103":231},["D",1734983052913],[],[104],[224,228],{"_108":184,"_169":151,"_166":121,"_100":104,"_176":188,"_174":187,"_105":186,"_168":-5,"_110":234,"_170":150,"_179":-5,"_103":235},["D",1734983052913],[104],[],"upstream","pull","ahead","behind","templates",[],"actionData","errors"]

jdan

ankiDeckImporterFrontend