416 lines
13 KiB
JavaScript
416 lines
13 KiB
JavaScript
import { Client, Databases, ID, Permission, Role } from "node-appwrite";
|
|
|
|
const requiredEnv = [
|
|
"APPWRITE_ENDPOINT",
|
|
"APPWRITE_PROJECT_ID",
|
|
"APPWRITE_API_KEY",
|
|
"DB_ID",
|
|
"DB_NAME",
|
|
"TABLE_PRODUCTS",
|
|
"TABLE_QUESTIONS",
|
|
"TABLE_SUBMISSIONS",
|
|
"TABLE_ANSWERS",
|
|
"TABLE_ORDERS",
|
|
"PRODUCT_ID",
|
|
"PRODUCT_SLUG",
|
|
"PRODUCT_TITLE",
|
|
"PRODUCT_PRICE_CENTS",
|
|
"PRODUCT_CURRENCY"
|
|
];
|
|
|
|
for (const k of requiredEnv) {
|
|
if (!process.env[k]) {
|
|
console.error(`Missing env var: ${k}`);
|
|
process.exit(1);
|
|
}
|
|
}
|
|
|
|
const client = new Client()
|
|
.setEndpoint(process.env.APPWRITE_ENDPOINT)
|
|
.setProject(process.env.APPWRITE_PROJECT_ID)
|
|
.setKey(process.env.APPWRITE_API_KEY);
|
|
|
|
const db = new Databases(client);
|
|
|
|
const DB_ID = process.env.DB_ID;
|
|
const DB_NAME = process.env.DB_NAME;
|
|
const T_PRODUCTS = process.env.TABLE_PRODUCTS;
|
|
const T_QUESTIONS = process.env.TABLE_QUESTIONS;
|
|
const T_SUBMISSIONS = process.env.TABLE_SUBMISSIONS;
|
|
const T_ANSWERS = process.env.TABLE_ANSWERS;
|
|
const T_ORDERS = process.env.TABLE_ORDERS;
|
|
|
|
async function ensureDatabase() {
|
|
try {
|
|
await db.get(DB_ID);
|
|
console.log("DB exists:", DB_ID);
|
|
} catch {
|
|
await db.create(DB_ID, DB_NAME);
|
|
console.log("DB created:", DB_ID);
|
|
}
|
|
}
|
|
|
|
// Helper: create table if missing
|
|
async function ensureTable(tableId, name, permissions) {
|
|
try {
|
|
await db.getCollection(DB_ID, tableId);
|
|
console.log("Collection exists:", tableId);
|
|
} catch {
|
|
await db.createCollection(DB_ID, tableId, name, permissions, true);
|
|
console.log("Collection created:", tableId);
|
|
}
|
|
}
|
|
|
|
// Helper: create column if missing
|
|
async function ensureColumn(tableId, key, fnCreate) {
|
|
const columns = await db.listAttributes(DB_ID, tableId);
|
|
const exists = columns.attributes?.some(c => c.key === key);
|
|
if (exists) return;
|
|
await fnCreate();
|
|
console.log("Column created:", tableId, key);
|
|
}
|
|
|
|
// Basic permissions strategy
|
|
const PERM_READ_ANY = [Permission.read(Role.any())];
|
|
const PERM_SERVER_ONLY = [];
|
|
const PERM_SUBMISSION_TABLE = [Permission.create(Role.any())];
|
|
|
|
async function setupSchema() {
|
|
await ensureDatabase();
|
|
|
|
// Tables
|
|
await ensureTable(T_PRODUCTS, "Products", PERM_SERVER_ONLY);
|
|
await ensureTable(T_QUESTIONS, "Questions", PERM_SERVER_ONLY);
|
|
await ensureTable(T_SUBMISSIONS, "Submissions", PERM_SUBMISSION_TABLE);
|
|
await ensureTable(T_ANSWERS, "Answers", PERM_SUBMISSION_TABLE);
|
|
await ensureTable(T_ORDERS, "Orders", PERM_SERVER_ONLY);
|
|
|
|
// PRODUCTS columns
|
|
await ensureColumn(T_PRODUCTS, "slug", () =>
|
|
db.createStringAttribute(DB_ID, T_PRODUCTS, "slug", 128, true));
|
|
await ensureColumn(T_PRODUCTS, "title", () =>
|
|
db.createStringAttribute(DB_ID, T_PRODUCTS, "title", 256, true));
|
|
await ensureColumn(T_PRODUCTS, "description", () =>
|
|
db.createStringAttribute(DB_ID, T_PRODUCTS, "description", 4096, false));
|
|
await ensureColumn(T_PRODUCTS, "priceCents", () =>
|
|
db.createIntegerAttribute(DB_ID, T_PRODUCTS, "priceCents", true, 0, 999999999));
|
|
await ensureColumn(T_PRODUCTS, "currency", () =>
|
|
db.createStringAttribute(DB_ID, T_PRODUCTS, "currency", 8, true));
|
|
await ensureColumn(T_PRODUCTS, "isActive", () =>
|
|
db.createBooleanAttribute(DB_ID, T_PRODUCTS, "isActive", true));
|
|
|
|
// QUESTIONS columns
|
|
await ensureColumn(T_QUESTIONS, "productId", () =>
|
|
db.createStringAttribute(DB_ID, T_QUESTIONS, "productId", 64, true));
|
|
await ensureColumn(T_QUESTIONS, "key", () =>
|
|
db.createStringAttribute(DB_ID, T_QUESTIONS, "key", 64, true));
|
|
await ensureColumn(T_QUESTIONS, "label", () =>
|
|
db.createStringAttribute(DB_ID, T_QUESTIONS, "label", 256, true));
|
|
await ensureColumn(T_QUESTIONS, "helpText", () =>
|
|
db.createStringAttribute(DB_ID, T_QUESTIONS, "helpText", 1024, false));
|
|
await ensureColumn(T_QUESTIONS, "type", () =>
|
|
db.createStringAttribute(DB_ID, T_QUESTIONS, "type", 32, true));
|
|
await ensureColumn(T_QUESTIONS, "required", () =>
|
|
db.createBooleanAttribute(DB_ID, T_QUESTIONS, "required", true));
|
|
await ensureColumn(T_QUESTIONS, "step", () =>
|
|
db.createIntegerAttribute(DB_ID, T_QUESTIONS, "step", true, 1, 9999));
|
|
await ensureColumn(T_QUESTIONS, "order", () =>
|
|
db.createIntegerAttribute(DB_ID, T_QUESTIONS, "order", true, 1, 999999));
|
|
await ensureColumn(T_QUESTIONS, "optionsJson", () =>
|
|
db.createStringAttribute(DB_ID, T_QUESTIONS, "optionsJson", 8192, false));
|
|
await ensureColumn(T_QUESTIONS, "isActive", () =>
|
|
db.createBooleanAttribute(DB_ID, T_QUESTIONS, "isActive", true));
|
|
|
|
// SUBMISSIONS columns
|
|
await ensureColumn(T_SUBMISSIONS, "productId", () =>
|
|
db.createStringAttribute(DB_ID, T_SUBMISSIONS, "productId", 64, true));
|
|
await ensureColumn(T_SUBMISSIONS, "status", () =>
|
|
db.createStringAttribute(DB_ID, T_SUBMISSIONS, "status", 32, true));
|
|
await ensureColumn(T_SUBMISSIONS, "customerEmail", () =>
|
|
db.createEmailAttribute(DB_ID, T_SUBMISSIONS, "customerEmail", false));
|
|
await ensureColumn(T_SUBMISSIONS, "customerName", () =>
|
|
db.createStringAttribute(DB_ID, T_SUBMISSIONS, "customerName", 256, false));
|
|
await ensureColumn(T_SUBMISSIONS, "utmJson", () =>
|
|
db.createStringAttribute(DB_ID, T_SUBMISSIONS, "utmJson", 2048, false));
|
|
await ensureColumn(T_SUBMISSIONS, "finalSummaryJson", () =>
|
|
db.createStringAttribute(DB_ID, T_SUBMISSIONS, "finalSummaryJson", 8192, false));
|
|
await ensureColumn(T_SUBMISSIONS, "priceCents", () =>
|
|
db.createIntegerAttribute(DB_ID, T_SUBMISSIONS, "priceCents", true, 0, 999999999));
|
|
await ensureColumn(T_SUBMISSIONS, "currency", () =>
|
|
db.createStringAttribute(DB_ID, T_SUBMISSIONS, "currency", 8, true));
|
|
|
|
// ANSWERS columns - simplified
|
|
await ensureColumn(T_ANSWERS, "submissionId", () =>
|
|
db.createStringAttribute(DB_ID, T_ANSWERS, "submissionId", 64, true));
|
|
await ensureColumn(T_ANSWERS, "answersJson", () =>
|
|
db.createStringAttribute(DB_ID, T_ANSWERS, "answersJson", 16384, true));
|
|
|
|
// ORDERS columns - simplified
|
|
await ensureColumn(T_ORDERS, "submissionId", () =>
|
|
db.createStringAttribute(DB_ID, T_ORDERS, "submissionId", 64, true));
|
|
await ensureColumn(T_ORDERS, "orderDataJson", () =>
|
|
db.createStringAttribute(DB_ID, T_ORDERS, "orderDataJson", 8192, true));
|
|
}
|
|
|
|
async function seedProductAndQuestions() {
|
|
const productId = process.env.PRODUCT_ID;
|
|
|
|
// Upsert product
|
|
try {
|
|
await db.getDocument(DB_ID, T_PRODUCTS, productId);
|
|
console.log("Product exists:", productId);
|
|
} catch {
|
|
await db.createDocument(
|
|
DB_ID,
|
|
T_PRODUCTS,
|
|
productId,
|
|
{
|
|
slug: process.env.PRODUCT_SLUG,
|
|
title: process.env.PRODUCT_TITLE,
|
|
description: "Personalisiere dein Postfach und bekomme ein klares Regel-Setup (Labels/Ordner/Filter) fuer Inbox Zero.",
|
|
priceCents: Number(process.env.PRODUCT_PRICE_CENTS),
|
|
currency: process.env.PRODUCT_CURRENCY,
|
|
isActive: true
|
|
},
|
|
[Permission.read(Role.any())]
|
|
);
|
|
console.log("Product created:", productId);
|
|
}
|
|
|
|
// 13 Kernfragen
|
|
const QUESTIONS = [
|
|
// Step 1: Kontakt
|
|
{
|
|
key:"customer_name",
|
|
label:"Wie soll ich dich nennen?",
|
|
type:"text",
|
|
required:false,
|
|
step:1,
|
|
order:1,
|
|
helpText:"Optional, fuer persoenliche Auslieferung."
|
|
},
|
|
{
|
|
key:"customer_email",
|
|
label:"Wohin sollen wir dein Setup schicken?",
|
|
type:"email",
|
|
required:true,
|
|
step:1,
|
|
order:2,
|
|
helpText:"Wir schicken dir das Ergebnis + Anleitung."
|
|
},
|
|
|
|
// Step 2: Provider + Volumen
|
|
{
|
|
key:"provider",
|
|
label:"Welchen E-Mail Provider nutzt du?",
|
|
type:"select",
|
|
required:true,
|
|
step:2,
|
|
order:1,
|
|
optionsJson: JSON.stringify({
|
|
options:[
|
|
{value:"gmail", label:"Gmail"},
|
|
{value:"outlook", label:"Outlook / Microsoft 365"},
|
|
{value:"icloud", label:"iCloud Mail"},
|
|
{value:"imap", label:"IMAP (anderer Anbieter)"}
|
|
]
|
|
})
|
|
},
|
|
{
|
|
key:"daily_volume",
|
|
label:"Wie viele E-Mails bekommst du pro Tag?",
|
|
type:"select",
|
|
required:true,
|
|
step:2,
|
|
order:2,
|
|
optionsJson: JSON.stringify({
|
|
options:[
|
|
{value:"0-10", label:"0-10"},
|
|
{value:"10-30", label:"10-30"},
|
|
{value:"30-100", label:"30-100"},
|
|
{value:"100+", label:"100+"}
|
|
]
|
|
})
|
|
},
|
|
|
|
// Step 3: Ziel + Striktheit
|
|
{
|
|
key:"primary_goal",
|
|
label:"Was ist dein Hauptziel?",
|
|
type:"select",
|
|
required:true,
|
|
step:3,
|
|
order:1,
|
|
optionsJson: JSON.stringify({
|
|
options:[
|
|
{value:"inbox_zero", label:"Inbox Zero (Posteingang leer)"},
|
|
{value:"priority_focus", label:"Wichtiges sofort sehen"},
|
|
{value:"newsletter_cleanup", label:"Newsletter/Promo aufraeumen"},
|
|
{value:"client_speed", label:"Kundenmails schneller bearbeiten"},
|
|
{value:"finance_clean", label:"Rechnungen/Belege sauber sammeln"}
|
|
]
|
|
})
|
|
},
|
|
{
|
|
key:"strictness",
|
|
label:"Wie strikt soll sortiert werden?",
|
|
type:"select",
|
|
required:true,
|
|
step:3,
|
|
order:2,
|
|
optionsJson: JSON.stringify({
|
|
options:[
|
|
{value:"light", label:"Leicht (nur Stoerer)"},
|
|
{value:"medium", label:"Mittel (balanced)"},
|
|
{value:"hard", label:"Hart (Inbox wird fast leer)"}
|
|
]
|
|
})
|
|
},
|
|
|
|
// Step 4: Kategorien + Limits
|
|
{
|
|
key:"categories",
|
|
label:"Welche Kategorien willst du aktiv nutzen?",
|
|
type:"multiselect",
|
|
required:true,
|
|
step:4,
|
|
order:1,
|
|
optionsJson: JSON.stringify({
|
|
options:[
|
|
{value:"vip", label:"VIP / Wichtig"},
|
|
{value:"clients", label:"Kunden / Projekte"},
|
|
{value:"leads", label:"Leads / Anfragen"},
|
|
{value:"billing", label:"Rechnungen / Belege"},
|
|
{value:"banking", label:"Banking / Payments"},
|
|
{value:"shipping", label:"Bestellungen / Versand"},
|
|
{value:"newsletters", label:"Newsletter"},
|
|
{value:"promos", label:"Promotions / Werbung"},
|
|
{value:"social", label:"Social / Plattformen"},
|
|
{value:"security", label:"Security / 2FA Codes"},
|
|
{value:"calendar", label:"Kalender / Einladungen"},
|
|
{value:"review", label:"Review / Unklar"}
|
|
]
|
|
})
|
|
},
|
|
{
|
|
key:"max_labels",
|
|
label:"Wie viele Labels/Ordner maximal (damit es clean bleibt)?",
|
|
type:"select",
|
|
required:true,
|
|
step:4,
|
|
order:2,
|
|
optionsJson: JSON.stringify({
|
|
options:[
|
|
{value:"5", label:"max 5"},
|
|
{value:"10", label:"max 10"},
|
|
{value:"20", label:"max 20"},
|
|
{value:"no_limit", label:"egal"}
|
|
]
|
|
})
|
|
},
|
|
|
|
// Step 5: Ausnahmen
|
|
{
|
|
key:"vip_senders",
|
|
label:"VIP Absender (nie aussortieren). Eine pro Zeile.",
|
|
type:"textarea",
|
|
required:false,
|
|
step:5,
|
|
order:1,
|
|
helpText:"E-Mail oder Domain, z.B. boss@firma.de oder firma.de"
|
|
},
|
|
{
|
|
key:"block_senders",
|
|
label:"Absender die immer weg duerfen. Eine pro Zeile.",
|
|
type:"textarea",
|
|
required:false,
|
|
step:5,
|
|
order:2
|
|
},
|
|
|
|
// Step 6: Newsletter/Invoices
|
|
{
|
|
key:"newsletter_policy",
|
|
label:"Wie sollen Newsletter behandelt werden?",
|
|
type:"select",
|
|
required:true,
|
|
step:6,
|
|
order:1,
|
|
optionsJson: JSON.stringify({
|
|
options:[
|
|
{value:"label_only", label:"Nur labeln"},
|
|
{value:"move", label:"In Newsletter Ordner verschieben"},
|
|
{value:"archive", label:"Automatisch archivieren"},
|
|
{value:"aggressive", label:"Aggressiv (fast alles weg, Review fuer Ausnahmen)"}
|
|
]
|
|
})
|
|
},
|
|
{
|
|
key:"invoice_policy",
|
|
label:"Wie sollen Rechnungen/Belege behandelt werden?",
|
|
type:"select",
|
|
required:true,
|
|
step:6,
|
|
order:2,
|
|
optionsJson: JSON.stringify({
|
|
options:[
|
|
{value:"label_only", label:"Nur labeln"},
|
|
{value:"move", label:"In Rechnungen Ordner verschieben"},
|
|
{value:"forward", label:"Weiterleiten an Buchhaltung Adresse (spaeter in n8n)"},
|
|
{value:"move_and_forward", label:"Verschieben + Weiterleiten (spaeter in n8n)"}
|
|
]
|
|
})
|
|
},
|
|
|
|
// Step 7: Sprache
|
|
{
|
|
key:"email_language",
|
|
label:"Welche Sprache ist in deinen E-Mails meist?",
|
|
type:"select",
|
|
required:true,
|
|
step:7,
|
|
order:1,
|
|
optionsJson: JSON.stringify({
|
|
options:[
|
|
{value:"de", label:"Deutsch"},
|
|
{value:"en", label:"Englisch"},
|
|
{value:"mixed", label:"Gemischt"}
|
|
]
|
|
})
|
|
}
|
|
];
|
|
|
|
// Seed questions - create with unique IDs
|
|
for (const q of QUESTIONS) {
|
|
await db.createDocument(
|
|
DB_ID,
|
|
T_QUESTIONS,
|
|
ID.unique(),
|
|
{
|
|
productId,
|
|
key: q.key,
|
|
label: q.label,
|
|
helpText: q.helpText || null,
|
|
type: q.type,
|
|
required: q.required,
|
|
step: q.step,
|
|
order: q.order,
|
|
optionsJson: q.optionsJson || null,
|
|
isActive: true
|
|
},
|
|
[Permission.read(Role.any())]
|
|
);
|
|
}
|
|
|
|
console.log("Seeded product + 13 questions.");
|
|
}
|
|
|
|
(async () => {
|
|
await setupSchema();
|
|
await seedProductAndQuestions();
|
|
console.log("DONE");
|
|
})().catch((e) => {
|
|
console.error(e);
|
|
process.exit(1);
|
|
});
|