-- Add new columns to Card table for analytics and scheduling
ALTER TABLE "Card" ADD COLUMN "customSlug" TEXT;
ALTER TABLE "Card" ADD COLUMN "isActive" BOOLEAN NOT NULL DEFAULT true;
ALTER TABLE "Card" ADD COLUMN "activeFrom" DATETIME;
ALTER TABLE "Card" ADD COLUMN "activeUntil" DATETIME;
ALTER TABLE "Card" ADD COLUMN "scanCount" INTEGER NOT NULL DEFAULT 0;

-- Create unique index for customSlug
CREATE UNIQUE INDEX "Card_customSlug_key" ON "Card"("customSlug");

-- Create index for customSlug lookups
CREATE INDEX "Card_customSlug_idx" ON "Card"("customSlug");

-- Create Scan table for tracking QR code scans
CREATE TABLE "Scan" (
    "id" TEXT NOT NULL PRIMARY KEY,
    "cardId" TEXT NOT NULL,
    "timestamp" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "userAgent" TEXT,
    "country" TEXT,
    "city" TEXT,
    "referer" TEXT,
    CONSTRAINT "Scan_cardId_fkey" FOREIGN KEY ("cardId") REFERENCES "Card" ("id") ON DELETE CASCADE ON UPDATE CASCADE
);

-- Create indexes for Scan table
CREATE INDEX "Scan_cardId_idx" ON "Scan"("cardId");
CREATE INDEX "Scan_timestamp_idx" ON "Scan"("timestamp");
