-- BASELINE: entspricht dem bestehenden, von Prisma erzeugten Schema (verifiziert 16.09.2026).
-- Wird NICHT ausgeführt, da die Tabellen schon existieren -- dient nur als Referenz/Dokumentation
-- und als Startpunkt für migrate.mjs (siehe _migrations-Tabelle unten).
-- Reale künftige Änderungen kommen in 001_*.sql, 002_*.sql usw.

CREATE TABLE IF NOT EXISTS "Wishlist" (
    "id" TEXT NOT NULL PRIMARY KEY,
    "shop" TEXT NOT NULL,
    "customerId" TEXT NOT NULL,
    "name" TEXT NOT NULL DEFAULT 'Ma liste',
    "isDefault" BOOLEAN NOT NULL DEFAULT false,
    "position" INTEGER NOT NULL DEFAULT 0,
    "autoBackInStock" BOOLEAN NOT NULL DEFAULT false,
    "shareToken" TEXT,
    "shareEnabled" BOOLEAN NOT NULL DEFAULT false,
    "shareConsentAt" DATETIME,
    "createdAt" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" DATETIME NOT NULL
);
CREATE UNIQUE INDEX IF NOT EXISTS "Wishlist_shareToken_key" ON "Wishlist"("shareToken");
CREATE INDEX IF NOT EXISTS "Wishlist_shop_customerId_idx" ON "Wishlist"("shop", "customerId");
CREATE INDEX IF NOT EXISTS "Wishlist_shareToken_idx" ON "Wishlist"("shareToken");

CREATE TABLE IF NOT EXISTS "WishlistItem" (
    "id" TEXT NOT NULL PRIMARY KEY,
    "wishlistId" TEXT NOT NULL,
    "shop" TEXT NOT NULL,
    "productId" TEXT NOT NULL,
    "variantId" TEXT NOT NULL,
    "title" TEXT NOT NULL,
    "variantTitle" TEXT,
    "sku" TEXT,
    "priceCents" INTEGER NOT NULL,
    "currency" TEXT NOT NULL DEFAULT 'EUR',
    "imageUrl" TEXT,
    "available" BOOLEAN NOT NULL DEFAULT true,
    "preorderable" BOOLEAN NOT NULL DEFAULT false,
    "addedAt" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "packagingUnit" INTEGER,
    "handle" TEXT,
    "note" TEXT,
    "orderQuantity" INTEGER,
    "position" INTEGER,
    CONSTRAINT "WishlistItem_wishlistId_fkey" FOREIGN KEY ("wishlistId") REFERENCES "Wishlist" ("id") ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE INDEX IF NOT EXISTS "WishlistItem_shop_productId_idx" ON "WishlistItem"("shop", "productId");
CREATE INDEX IF NOT EXISTS "WishlistItem_shop_variantId_idx" ON "WishlistItem"("shop", "variantId");
CREATE UNIQUE INDEX IF NOT EXISTS "WishlistItem_wishlistId_variantId_key" ON "WishlistItem"("wishlistId", "variantId");

CREATE TABLE IF NOT EXISTS "BackInStockSubscription" (
    "id" TEXT NOT NULL PRIMARY KEY,
    "shop" TEXT NOT NULL,
    "customerId" TEXT,
    "guestEmail" TEXT,
    "guestName" TEXT,
    "productId" TEXT NOT NULL,
    "variantId" TEXT NOT NULL,
    "title" TEXT NOT NULL,
    "variantTitle" TEXT,
    "sku" TEXT,
    "imageUrl" TEXT,
    "priceCents" INTEGER NOT NULL,
    "currency" TEXT NOT NULL DEFAULT 'EUR',
    "wishlistId" TEXT,
    "wishlistNameAtSubscription" TEXT,
    "locale" TEXT NOT NULL DEFAULT 'fr',
    "status" TEXT NOT NULL DEFAULT 'WAITING',
    "notifiedAt" DATETIME,
    "orderCheckedAt" DATETIME,
    "convertedOrderId" TEXT,
    "convertedAt" DATETIME,
    "revenueCents" INTEGER,
    "rateLimitKey" TEXT,
    "createdAt" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" DATETIME NOT NULL,
    CONSTRAINT "BackInStockSubscription_wishlistId_fkey" FOREIGN KEY ("wishlistId") REFERENCES "Wishlist" ("id") ON DELETE SET NULL ON UPDATE CASCADE
);
CREATE INDEX IF NOT EXISTS "BackInStockSubscription_shop_variantId_status_idx" ON "BackInStockSubscription"("shop", "variantId", "status");
CREATE INDEX IF NOT EXISTS "BackInStockSubscription_shop_customerId_idx" ON "BackInStockSubscription"("shop", "customerId");
CREATE INDEX IF NOT EXISTS "BackInStockSubscription_shop_guestEmail_idx" ON "BackInStockSubscription"("shop", "guestEmail");
CREATE INDEX IF NOT EXISTS "BackInStockSubscription_status_idx" ON "BackInStockSubscription"("status");

CREATE TABLE IF NOT EXISTS "Translation" (
    "id" TEXT NOT NULL PRIMARY KEY,
    "shop" TEXT NOT NULL,
    "key" TEXT NOT NULL,
    "locale" TEXT NOT NULL,
    "value" TEXT NOT NULL,
    "updatedAt" DATETIME NOT NULL
);
CREATE INDEX IF NOT EXISTS "Translation_shop_locale_idx" ON "Translation"("shop", "locale");
CREATE UNIQUE INDEX IF NOT EXISTS "Translation_shop_key_locale_key" ON "Translation"("shop", "key", "locale");

CREATE TABLE IF NOT EXISTS "Setting" (
    "id" TEXT NOT NULL PRIMARY KEY,
    "shop" TEXT NOT NULL,
    "key" TEXT NOT NULL,
    "value" TEXT NOT NULL,
    "updatedAt" DATETIME NOT NULL
);
CREATE UNIQUE INDEX IF NOT EXISTS "Setting_shop_key_key" ON "Setting"("shop", "key");

CREATE TABLE IF NOT EXISTS "StatEvent" (
    "id" TEXT NOT NULL PRIMARY KEY,
    "shop" TEXT NOT NULL,
    "type" TEXT NOT NULL,
    "customerId" TEXT,
    "productId" TEXT,
    "variantId" TEXT,
    "wishlistId" TEXT,
    "subscriptionId" TEXT,
    "revenueCents" INTEGER,
    "createdAt" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS "StatEvent_shop_type_createdAt_idx" ON "StatEvent"("shop", "type", "createdAt");
CREATE INDEX IF NOT EXISTS "StatEvent_shop_variantId_type_idx" ON "StatEvent"("shop", "variantId", "type");

-- Session-Tabelle: wird nach Umstellung auf @shopify/shopify-app-session-storage-sqlite
-- von diesem Paket selbst verwaltet (eigenes Schema, evtl. leicht abweichend von der
-- bisherigen Prisma-Session-Tabelle) -- NICHT hier manuell pflegen, siehe Schritt 6
-- im Migrationsplan. Aktuelles Schema nur zur Referenz:
--
-- CREATE TABLE IF NOT EXISTS "Session" (
--     "id" TEXT NOT NULL PRIMARY KEY,
--     "shop" TEXT NOT NULL,
--     "state" TEXT NOT NULL,
--     "isOnline" BOOLEAN NOT NULL DEFAULT false,
--     "scope" TEXT,
--     "expires" DATETIME,
--     "accessToken" TEXT NOT NULL,
--     "userId" BIGINT,
--     "firstName" TEXT,
--     "lastName" TEXT,
--     "email" TEXT,
--     "accountOwner" BOOLEAN NOT NULL DEFAULT false,
--     "locale" TEXT,
--     "collaborator" BOOLEAN DEFAULT false,
--     "emailVerified" BOOLEAN DEFAULT false
-- );

CREATE TABLE IF NOT EXISTS "_migrations" (
    "name" TEXT NOT NULL PRIMARY KEY,
    "appliedAt" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);