355 lines
14 KiB
JavaScript
355 lines
14 KiB
JavaScript
#!/usr/bin/env node
|
|
/**
|
|
* Wartungs-Abo: legt die Collections subscriptions, hourCredits, takeoverCredits
|
|
* (Uebernahmegutschriften), aboOffers (individuelle Angebote), portalFeatures (Portal-Funktionen je
|
|
* Projekt), newsPosts (News-Pinnwand) und customerViewLog (Protokoll der Kundenansicht) in woms-database an und
|
|
* ergaenzt worksheets um das Flag countsToPlan ("zaehlt aufs Abo-Stundenkonto").
|
|
* Idempotent: existierende Collection/Attribute/Indexe werden uebersprungen.
|
|
* Usage (im Container): docker exec -w /app webklar-kundenbereich-server node scripts/setup-abo-collections.mjs
|
|
*/
|
|
const endpoint = (process.env.APPWRITE_ENDPOINT || 'https://ticket.webklar.com/v1').replace(/\/$/, '')
|
|
const projectId = process.env.APPWRITE_PROJECT_ID || '6a1058610003c5a13a05'
|
|
const apiKey = process.env.APPWRITE_API_KEY
|
|
const databaseId = process.env.APPWRITE_DATABASE_ID || 'woms-database'
|
|
|
|
if (!apiKey) {
|
|
console.error('APPWRITE_API_KEY erforderlich')
|
|
process.exit(1)
|
|
}
|
|
|
|
async function aw(path, { method = 'GET', body } = {}) {
|
|
const response = await fetch(`${endpoint}${path}`, {
|
|
method,
|
|
headers: {
|
|
'Content-Type': 'application/json',
|
|
'X-Appwrite-Project': projectId,
|
|
'X-Appwrite-Key': apiKey,
|
|
},
|
|
body: body ? JSON.stringify(body) : undefined,
|
|
})
|
|
const data = await response.json().catch(() => ({}))
|
|
if (!response.ok) {
|
|
const err = new Error(data.message || `Appwrite ${response.status}`)
|
|
err.status = response.status
|
|
throw err
|
|
}
|
|
return data
|
|
}
|
|
|
|
async function ensure(label, fn) {
|
|
try {
|
|
await fn()
|
|
console.log(`+ ${label}`)
|
|
} catch (e) {
|
|
if (e.status === 409 || /already exists/i.test(e.message)) {
|
|
console.log(`= ${label} (existiert)`)
|
|
} else {
|
|
throw new Error(`${label}: ${e.message}`)
|
|
}
|
|
}
|
|
}
|
|
|
|
async function waitForAttributes(collectionId, keys, maxSec = 60) {
|
|
// Ohne Limit liefert Appwrite nur 25 Attribute -> spaetere Attribute waeren nie "verfuegbar"
|
|
const limit = encodeURIComponent(JSON.stringify({ method: 'limit', values: [100] }))
|
|
for (let i = 0; i < maxSec; i += 1) {
|
|
const { attributes } = await aw(`/databases/${databaseId}/collections/${collectionId}/attributes?queries[]=${limit}`)
|
|
const pending = keys.filter((k) => attributes.find((a) => a.key === k)?.status !== 'available')
|
|
if (!pending.length) return
|
|
await new Promise((r) => setTimeout(r, 1000))
|
|
}
|
|
throw new Error(`Attribute in ${collectionId} wurden nicht verfuegbar`)
|
|
}
|
|
|
|
const col = (id) => `/databases/${databaseId}/collections/${id}`
|
|
|
|
/**
|
|
* Vergroessert ein bestehendes String-Attribut, falls es kleiner als size ist (ensureCollection
|
|
* ueberspringt vorhandene Attribute und prueft ihre Groesse nicht). Verkleinern tut es nie.
|
|
*/
|
|
async function ensureStringSize(collectionId, key, size) {
|
|
const attr = await aw(`${col(collectionId)}/attributes/${key}`)
|
|
if (attr.size >= size) {
|
|
console.log(`= ${collectionId}.${key} Groesse ${attr.size} (ok)`)
|
|
return
|
|
}
|
|
await aw(`${col(collectionId)}/attributes/string/${key}`, {
|
|
method: 'PATCH',
|
|
body: { required: Boolean(attr.required), default: attr.default ?? null, size },
|
|
})
|
|
await waitForAttributes(collectionId, [key])
|
|
console.log(`+ ${collectionId}.${key} Groesse ${attr.size} -> ${size}`)
|
|
}
|
|
|
|
async function ensureCollection(id, name, attributes, indexes) {
|
|
// Keine Dokument-Permissions: Zugriff ausschliesslich ueber den Server-API-Key
|
|
await ensure(`Collection ${id}`, () =>
|
|
aw(`/databases/${databaseId}/collections`, {
|
|
method: 'POST',
|
|
body: { collectionId: id, name, permissions: [], documentSecurity: false },
|
|
})
|
|
)
|
|
for (const a of attributes) {
|
|
const { type, key, ...rest } = a
|
|
await ensure(`${id}.${key}`, () =>
|
|
aw(`${col(id)}/attributes/${type}`, { method: 'POST', body: { key, required: false, ...rest } })
|
|
)
|
|
}
|
|
await waitForAttributes(id, attributes.map((a) => a.key))
|
|
for (const idx of indexes) {
|
|
await ensure(`${id} index ${idx.key}`, () =>
|
|
aw(`${col(id)}/indexes`, { method: 'POST', body: idx })
|
|
)
|
|
}
|
|
}
|
|
|
|
await ensureCollection(
|
|
'subscriptions',
|
|
'Abos (Stripe)',
|
|
[
|
|
{ type: 'string', key: 'customerId', size: 64 },
|
|
{ type: 'string', key: 'email', size: 255 },
|
|
{ type: 'string', key: 'stripeCustomerId', size: 64 },
|
|
{ type: 'string', key: 'stripeSubscriptionId', size: 64 },
|
|
{ type: 'string', key: 'status', size: 32 },
|
|
{ type: 'string', key: 'planKey', size: 64 },
|
|
{ type: 'integer', key: 'priceAmount' },
|
|
{ type: 'datetime', key: 'startedAt' },
|
|
{ type: 'datetime', key: 'currentPeriodEnd' },
|
|
{ type: 'boolean', key: 'cancelAtPeriodEnd' },
|
|
{ type: 'datetime', key: 'canceledAt' },
|
|
{ type: 'datetime', key: 'endedAt' },
|
|
{ type: 'string', key: 'termsVersion', size: 32 },
|
|
{ type: 'datetime', key: 'termsAcceptedAt' },
|
|
{ type: 'datetime', key: 'earlyStartConsentAt' },
|
|
{ type: 'datetime', key: 'cancelRequestedAt' },
|
|
{ type: 'datetime', key: 'withdrawalRequestedAt' },
|
|
{ type: 'datetime', key: 'confirmationSentAt' },
|
|
// v2 (25.09.2026): Wahl des Leistungsbeginns + Nachweis der Zustimmung
|
|
{ type: 'string', key: 'startChoice', size: 16 },
|
|
{ type: 'datetime', key: 'trialEnd' },
|
|
{ type: 'string', key: 'consentIp', size: 64 },
|
|
{ type: 'string', key: 'consentUserAgent', size: 300 },
|
|
{ type: 'string', key: 'consentTextVersion', size: 32 },
|
|
// v3: Beendigungszeitpunkt (Mindestlaufzeit / Wunschtermin)
|
|
{ type: 'datetime', key: 'cancelAt' },
|
|
// Individuelles Angebot, mit dem das Abo abgeschlossen wurde (Nachweis der Preisabrede)
|
|
{ type: 'string', key: 'offerId', size: 64 },
|
|
{ type: 'string', key: 'offerText', size: 300 },
|
|
// Plan 1: Produkt des Abos (wartung | hosting)
|
|
{ type: 'string', key: 'productKey', size: 16 },
|
|
// Plan 2 (Hosting-Abo + Zusatzspeicher): Wechseltag (Beginn der neuen Mindestlaufzeit) und bezahlter Speicherstand
|
|
{ type: 'datetime', key: 'termStartAt' },
|
|
{ type: 'integer', key: 'storageUnits' },
|
|
{ type: 'integer', key: 'storagePaidUnits' },
|
|
{ type: 'datetime', key: 'storagePaidPeriodEnd' },
|
|
],
|
|
[
|
|
{ key: 'idx_customer', type: 'key', attributes: ['customerId'] },
|
|
{ key: 'idx_stripe_customer', type: 'key', attributes: ['stripeCustomerId'] },
|
|
]
|
|
)
|
|
|
|
await ensureCollection(
|
|
'hourCredits',
|
|
'Abo-Stundengutschriften',
|
|
[
|
|
{ type: 'string', key: 'customerId', size: 64 },
|
|
{ type: 'string', key: 'stripeSubscriptionId', size: 64 },
|
|
{ type: 'string', key: 'stripeInvoiceId', size: 64 },
|
|
{ type: 'integer', key: 'minutes' },
|
|
{ type: 'datetime', key: 'grantedAt' },
|
|
{ type: 'datetime', key: 'expiresAt' },
|
|
{ type: 'string', key: 'source', size: 32 },
|
|
{ type: 'string', key: 'note', size: 500 },
|
|
],
|
|
[{ key: 'idx_customer', type: 'key', attributes: ['customerId'] }]
|
|
)
|
|
|
|
// Uebernahmegutschrift: Antrag (Kunde) -> Pruefung/Entscheidung (Admin) -> Verrechnung (Stripe-Guthaben).
|
|
// Lange Texte mit size > 16381 werden in Appwrite zu TEXT-Spalten und zaehlen nicht zur Zeilenbreite.
|
|
await ensureCollection(
|
|
'takeoverCredits',
|
|
'Uebernahmegutschriften',
|
|
[
|
|
{ type: 'string', key: 'customerId', size: 64 },
|
|
{ type: 'string', key: 'email', size: 255 },
|
|
{ type: 'string', key: 'services', size: 128 },
|
|
{ type: 'string', key: 'provider', size: 200 },
|
|
{ type: 'string', key: 'domain', size: 255 },
|
|
{ type: 'integer', key: 'invoiceAmountCents' },
|
|
{ type: 'integer', key: 'billingMonths' },
|
|
{ type: 'datetime', key: 'paidUntil' },
|
|
{ type: 'string', key: 'message', size: 20000 },
|
|
{ type: 'string', key: 'files', size: 20000 },
|
|
{ type: 'string', key: 'status', size: 20 },
|
|
{ type: 'string', key: 'history', size: 60000 },
|
|
{ type: 'datetime', key: 'takeoverFrom' },
|
|
{ type: 'integer', key: 'amountCents' },
|
|
{ type: 'string', key: 'calculation', size: 1000 },
|
|
{ type: 'string', key: 'checks', size: 1000 },
|
|
{ type: 'string', key: 'decisionNote', size: 20000 },
|
|
{ type: 'datetime', key: 'decidedAt' },
|
|
{ type: 'string', key: 'decidedBy', size: 255 },
|
|
{ type: 'string', key: 'stripeCustomerId', size: 64 },
|
|
{ type: 'string', key: 'stripeBalanceTransactionId', size: 64 },
|
|
{ type: 'datetime', key: 'appliedAt' },
|
|
{ type: 'string', key: 'conditionsVersion', size: 32 },
|
|
{ type: 'datetime', key: 'consentAt' },
|
|
{ type: 'string', key: 'consentIp', size: 64 },
|
|
],
|
|
[
|
|
{ key: 'idx_customer', type: 'key', attributes: ['customerId'] },
|
|
{ key: 'idx_status', type: 'key', attributes: ['status'] },
|
|
]
|
|
)
|
|
|
|
// Individuelle Abo-Angebote: Admin legt vorab fest (z. B. erste 2 Monate je 25 €), Stripe-Coupon je Angebot
|
|
await ensureCollection(
|
|
'aboOffers',
|
|
'Abo-Angebote',
|
|
[
|
|
{ type: 'string', key: 'customerId', size: 64 },
|
|
// Plan 2: Angebot gilt fuer dieses Produkt (wartung | hosting)
|
|
{ type: 'string', key: 'productKey', size: 16 },
|
|
{ type: 'string', key: 'status', size: 20 },
|
|
{ type: 'string', key: 'scope', size: 16 },
|
|
{ type: 'integer', key: 'monthlyPriceCents' },
|
|
{ type: 'integer', key: 'regularPriceCents' },
|
|
{ type: 'integer', key: 'months' },
|
|
{ type: 'datetime', key: 'validUntil' },
|
|
{ type: 'string', key: 'note', size: 500 },
|
|
{ type: 'string', key: 'internalNote', size: 2000 },
|
|
{ type: 'string', key: 'stripeCouponId', size: 64 },
|
|
{ type: 'string', key: 'stripeSubscriptionId', size: 64 },
|
|
{ type: 'datetime', key: 'appliedAt' },
|
|
{ type: 'string', key: 'createdBy', size: 255 },
|
|
{ type: 'datetime', key: 'withdrawnAt' },
|
|
{ type: 'string', key: 'withdrawnBy', size: 255 },
|
|
{ type: 'datetime', key: 'notifiedAt' },
|
|
],
|
|
[
|
|
{ key: 'idx_customer', type: 'key', attributes: ['customerId'] },
|
|
{ key: 'idx_status', type: 'key', attributes: ['status'] },
|
|
]
|
|
)
|
|
|
|
// Vertragsereignisse: ein Dokument je Bestellung/Aenderung (Neuabschluss, Wechsel, Zusatzspeicher,
|
|
// Kuendigung, Widerruf) mit den Nachweisen der Zustimmung, der Widerrufsfrist und - bei Zusatzspeicher -
|
|
// dem Stand der Aufgabe fuers Team.
|
|
await ensureCollection(
|
|
'contractEvents',
|
|
'Vertragsereignisse',
|
|
[
|
|
{ type: 'string', key: 'customerId', size: 64 },
|
|
{ type: 'string', key: 'subscriptionId', size: 64 },
|
|
{ type: 'string', key: 'type', size: 16 },
|
|
{ type: 'string', key: 'productKey', size: 16 },
|
|
{ type: 'string', key: 'details', size: 2000 },
|
|
{ type: 'string', key: 'termsVersion', size: 32 },
|
|
{ type: 'datetime', key: 'consentAt' },
|
|
{ type: 'string', key: 'consentIp', size: 64 },
|
|
{ type: 'string', key: 'consentUserAgent', size: 300 },
|
|
{ type: 'datetime', key: 'earlyStartConsentAt' },
|
|
{ type: 'string', key: 'status', size: 16 },
|
|
{ type: 'datetime', key: 'dueAt' },
|
|
{ type: 'datetime', key: 'doneAt' },
|
|
{ type: 'string', key: 'doneBy', size: 255 },
|
|
{ type: 'datetime', key: 'confirmationSentAt' },
|
|
],
|
|
[
|
|
{ key: 'idx_customer', type: 'key', attributes: ['customerId'] },
|
|
{ key: 'idx_type_status', type: 'key', attributes: ['type', 'status'] },
|
|
]
|
|
)
|
|
|
|
// Portal-Funktionen je Projekt (Zustand, Kauf, Zustimmungsnachweis). Die Collection existiert in
|
|
// Produktion teils schon; ensureCollection ueberspringt Vorhandenes (409) und ergaenzt Fehlendes.
|
|
await ensureCollection(
|
|
'portalFeatures',
|
|
'Portal-Funktionen',
|
|
[
|
|
{ type: 'string', key: 'customerId', size: 64 },
|
|
{ type: 'string', key: 'projectId', size: 64 },
|
|
{ type: 'string', key: 'featureKey', size: 64 },
|
|
{ type: 'boolean', key: 'enabled' },
|
|
{ type: 'boolean', key: 'unlockedByPurchase' },
|
|
{ type: 'string', key: 'purchaseStatus', size: 64 },
|
|
{ type: 'string', key: 'state', size: 16 },
|
|
{ type: 'string', key: 'siteKey', size: 16 },
|
|
{ type: 'string', key: 'ticketId', size: 64 },
|
|
{ type: 'string', key: 'stripeSessionId', size: 128 },
|
|
{ type: 'string', key: 'stripeInvoiceId', size: 64 },
|
|
{ type: 'string', key: 'invoiceUrl', size: 500 },
|
|
{ type: 'datetime', key: 'orderedAt' },
|
|
{ type: 'datetime', key: 'activatedAt' },
|
|
{ type: 'datetime', key: 'disabledAt' },
|
|
{ type: 'string', key: 'consentText', size: 1000 },
|
|
{ type: 'datetime', key: 'consentAt' },
|
|
{ type: 'string', key: 'consentIp', size: 64 },
|
|
{ type: 'string', key: 'consentUserAgent', size: 300 },
|
|
{ type: 'string', key: 'adminNote', size: 300 },
|
|
],
|
|
[
|
|
{ key: 'idx_customer', type: 'key', attributes: ['customerId'] },
|
|
{ key: 'idx_state', type: 'key', attributes: ['state'] },
|
|
{ key: 'idx_sitekey', type: 'unique', attributes: ['siteKey'] },
|
|
{ key: 'idx_session', type: 'key', attributes: ['stripeSessionId'] },
|
|
]
|
|
)
|
|
// Live-Fehler 30.09.2026: cs_live-Sitzungs-IDs (~66 Zeichen) passten nicht in 64
|
|
await ensureStringSize('portalFeatures', 'stripeSessionId', 128)
|
|
|
|
// News-Pinnwand: Beitraege je Projekt
|
|
await ensureCollection(
|
|
'newsPosts',
|
|
'News-Beiträge',
|
|
[
|
|
{ type: 'string', key: 'customerId', size: 64 },
|
|
{ type: 'string', key: 'projectId', size: 64 },
|
|
{ type: 'string', key: 'type', size: 8 },
|
|
{ type: 'string', key: 'title', size: 60 },
|
|
{ type: 'string', key: 'text', size: 400 },
|
|
{ type: 'string', key: 'imageId', size: 64 },
|
|
{ type: 'string', key: 'paper', size: 8 },
|
|
{ type: 'string', key: 'duration', size: 8 },
|
|
{ type: 'datetime', key: 'visibleFrom' },
|
|
{ type: 'datetime', key: 'visibleUntil' },
|
|
{ type: 'datetime', key: 'takenDownAt' },
|
|
{ type: 'datetime', key: 'createdAt' },
|
|
{ type: 'datetime', key: 'updatedAt' },
|
|
],
|
|
[
|
|
{ key: 'idx_project_from', type: 'key', attributes: ['projectId', 'visibleFrom'] },
|
|
]
|
|
)
|
|
|
|
// Worksheet-Flag: nur markierte Arbeitszeit wird vom Abo-Guthaben abgezogen
|
|
await ensure('worksheets.countsToPlan', () =>
|
|
aw(`${col('worksheets')}/attributes/boolean`, {
|
|
method: 'POST',
|
|
body: { key: 'countsToPlan', required: false, default: false },
|
|
})
|
|
)
|
|
await waitForAttributes('worksheets', ['countsToPlan'])
|
|
|
|
// Kundenansicht: wer wann das Portal eines Kunden geoeffnet oder darin etwas geaendert hat (12 Monate)
|
|
await ensureCollection(
|
|
'customerViewLog',
|
|
'Kundenansicht-Protokoll',
|
|
[
|
|
{ type: 'string', key: 'customerId', size: 64 },
|
|
{ type: 'string', key: 'staffUserId', size: 64 },
|
|
{ type: 'string', key: 'staffName', size: 128 },
|
|
{ type: 'string', key: 'action', size: 32 },
|
|
{ type: 'string', key: 'detail', size: 300 },
|
|
{ type: 'datetime', key: 'createdAt' },
|
|
],
|
|
[
|
|
{ key: 'idx_customer_created', type: 'key', attributes: ['customerId', 'createdAt'] },
|
|
{ key: 'idx_created', type: 'key', attributes: ['createdAt'] },
|
|
]
|
|
)
|
|
|
|
console.log('Fertig.')
|