Gerhard Scheikl
118 lines
4.2 KiB
SQL
118 lines
4.2 KiB
SQL
-- CreateTable
|
|
CREATE TABLE "ShopSettings" (
|
|
"id" TEXT NOT NULL PRIMARY KEY,
|
|
"shopDomain" TEXT NOT NULL,
|
|
"companyName" TEXT NOT NULL DEFAULT '',
|
|
"legalForm" TEXT NOT NULL DEFAULT '',
|
|
"ownerName" TEXT NOT NULL DEFAULT '',
|
|
"addressLine1" TEXT NOT NULL DEFAULT '',
|
|
"addressLine2" TEXT NOT NULL DEFAULT '',
|
|
"postalCode" TEXT NOT NULL DEFAULT '',
|
|
"city" TEXT NOT NULL DEFAULT '',
|
|
"countryCode" TEXT NOT NULL DEFAULT 'AT',
|
|
"phone" TEXT NOT NULL DEFAULT '',
|
|
"email" TEXT NOT NULL DEFAULT '',
|
|
"website" TEXT NOT NULL DEFAULT '',
|
|
"vatId" TEXT NOT NULL DEFAULT '',
|
|
"taxNumber" TEXT NOT NULL DEFAULT '',
|
|
"registrationNo" TEXT NOT NULL DEFAULT '',
|
|
"registrationCourt" TEXT NOT NULL DEFAULT '',
|
|
"bankName" TEXT NOT NULL DEFAULT '',
|
|
"iban" TEXT NOT NULL DEFAULT '',
|
|
"bic" TEXT NOT NULL DEFAULT '',
|
|
"giroCodeEnabled" BOOLEAN NOT NULL DEFAULT true,
|
|
"numberingMode" TEXT NOT NULL DEFAULT 'shopify_order_number',
|
|
"invoicePrefix" TEXT NOT NULL DEFAULT 'RE-',
|
|
"invoiceSeed" INTEGER NOT NULL DEFAULT 1000,
|
|
"defaultLanguage" TEXT NOT NULL DEFAULT 'de',
|
|
"paymentTermDays" INTEGER NOT NULL DEFAULT 14,
|
|
"footerNote" TEXT NOT NULL DEFAULT '',
|
|
"kleinunternehmer" BOOLEAN NOT NULL DEFAULT false,
|
|
"logoUrl" TEXT NOT NULL DEFAULT '',
|
|
"smtpHost" TEXT NOT NULL DEFAULT '',
|
|
"smtpPort" INTEGER NOT NULL DEFAULT 587,
|
|
"smtpSecure" BOOLEAN NOT NULL DEFAULT false,
|
|
"smtpUser" TEXT NOT NULL DEFAULT '',
|
|
"smtpPassword" TEXT NOT NULL DEFAULT '',
|
|
"smtpFromName" TEXT NOT NULL DEFAULT '',
|
|
"smtpFromEmail" TEXT NOT NULL DEFAULT '',
|
|
"smtpReplyTo" TEXT NOT NULL DEFAULT '',
|
|
"createdAt" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" DATETIME NOT NULL
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "Invoice" (
|
|
"id" TEXT NOT NULL PRIMARY KEY,
|
|
"shopDomain" TEXT NOT NULL,
|
|
"orderId" TEXT NOT NULL,
|
|
"orderName" TEXT NOT NULL,
|
|
"orderNumber" INTEGER NOT NULL,
|
|
"invoiceNumber" TEXT NOT NULL,
|
|
"language" TEXT NOT NULL DEFAULT 'de',
|
|
"kind" TEXT NOT NULL DEFAULT 'invoice',
|
|
"version" INTEGER NOT NULL DEFAULT 1,
|
|
"cancelsInvoiceId" TEXT,
|
|
"pdfFileGid" TEXT NOT NULL DEFAULT '',
|
|
"pdfUrl" TEXT NOT NULL DEFAULT '',
|
|
"totalsJson" TEXT NOT NULL DEFAULT '{}',
|
|
"customerJson" TEXT NOT NULL DEFAULT '{}',
|
|
"issuedAt" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"sentAt" DATETIME,
|
|
"cancelledAt" DATETIME,
|
|
"status" TEXT NOT NULL DEFAULT 'issued',
|
|
"lastError" TEXT NOT NULL DEFAULT '',
|
|
"createdAt" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updatedAt" DATETIME NOT NULL,
|
|
CONSTRAINT "Invoice_shopDomain_fkey" FOREIGN KEY ("shopDomain") REFERENCES "ShopSettings" ("shopDomain") ON DELETE RESTRICT ON UPDATE CASCADE
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "InvoiceCounter" (
|
|
"id" TEXT NOT NULL PRIMARY KEY,
|
|
"shopDomain" TEXT NOT NULL,
|
|
"lastValue" INTEGER NOT NULL DEFAULT 0,
|
|
"updatedAt" DATETIME NOT NULL
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "EmailLog" (
|
|
"id" TEXT NOT NULL PRIMARY KEY,
|
|
"shopDomain" TEXT NOT NULL,
|
|
"invoiceId" TEXT NOT NULL,
|
|
"toAddress" TEXT NOT NULL,
|
|
"subject" TEXT NOT NULL,
|
|
"status" TEXT NOT NULL,
|
|
"error" TEXT NOT NULL DEFAULT '',
|
|
"sentAt" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "LogoCache" (
|
|
"id" TEXT NOT NULL PRIMARY KEY,
|
|
"shopDomain" TEXT NOT NULL,
|
|
"sourceUrl" TEXT NOT NULL,
|
|
"bytes" BLOB NOT NULL,
|
|
"contentType" TEXT NOT NULL DEFAULT 'image/png',
|
|
"etag" TEXT NOT NULL DEFAULT '',
|
|
"fetchedAt" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "ShopSettings_shopDomain_key" ON "ShopSettings"("shopDomain");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Invoice_shopDomain_orderId_idx" ON "Invoice"("shopDomain", "orderId");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "Invoice_shopDomain_invoiceNumber_idx" ON "Invoice"("shopDomain", "invoiceNumber");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "InvoiceCounter_shopDomain_key" ON "InvoiceCounter"("shopDomain");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "EmailLog_shopDomain_invoiceId_idx" ON "EmailLog"("shopDomain", "invoiceId");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "LogoCache_shopDomain_key" ON "LogoCache"("shopDomain");
|