-- CreateEnum
CREATE TYPE "CustomerCouponStatus" AS ENUM ('ASSIGNED', 'REDEEMED', 'EXPIRED');

-- AlterTable
ALTER TABLE "sales" ADD COLUMN     "appliedCouponId" TEXT,
ADD COLUMN     "customerLat" DOUBLE PRECISION,
ADD COLUMN     "customerLng" DOUBLE PRECISION,
ADD COLUMN     "discountAmount" INTEGER NOT NULL DEFAULT 0,
ADD COLUMN     "originalAmount" INTEGER;

-- AlterTable
ALTER TABLE "shops" ADD COLUMN     "latitude" DOUBLE PRECISION,
ADD COLUMN     "longitude" DOUBLE PRECISION;

-- CreateTable
CREATE TABLE "customer_shops" (
    "userId" TEXT NOT NULL,
    "shopId" TEXT NOT NULL,
    "firstSeenAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "lastSeenAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT "customer_shops_pkey" PRIMARY KEY ("userId","shopId")
);

-- CreateTable
CREATE TABLE "customer_coupons" (
    "id" TEXT NOT NULL,
    "couponId" TEXT NOT NULL,
    "customerId" TEXT NOT NULL,
    "status" "CustomerCouponStatus" NOT NULL DEFAULT 'ASSIGNED',
    "assignedAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "redeemedAt" TIMESTAMP(3),
    "redeemedSaleId" TEXT,

    CONSTRAINT "customer_coupons_pkey" PRIMARY KEY ("id")
);

-- CreateIndex
CREATE INDEX "customer_shops_shopId_idx" ON "customer_shops"("shopId");

-- CreateIndex
CREATE INDEX "customer_coupons_couponId_status_idx" ON "customer_coupons"("couponId", "status");

-- CreateIndex
CREATE INDEX "customer_coupons_customerId_status_idx" ON "customer_coupons"("customerId", "status");

-- CreateIndex
CREATE UNIQUE INDEX "customer_coupons_customerId_couponId_key" ON "customer_coupons"("customerId", "couponId");

-- AddForeignKey
ALTER TABLE "customer_shops" ADD CONSTRAINT "customer_shops_userId_fkey" FOREIGN KEY ("userId") REFERENCES "users"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "customer_shops" ADD CONSTRAINT "customer_shops_shopId_fkey" FOREIGN KEY ("shopId") REFERENCES "shops"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "sales" ADD CONSTRAINT "sales_appliedCouponId_fkey" FOREIGN KEY ("appliedCouponId") REFERENCES "coupons"("id") ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "customer_coupons" ADD CONSTRAINT "customer_coupons_couponId_fkey" FOREIGN KEY ("couponId") REFERENCES "coupons"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "customer_coupons" ADD CONSTRAINT "customer_coupons_customerId_fkey" FOREIGN KEY ("customerId") REFERENCES "users"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "customer_coupons" ADD CONSTRAINT "customer_coupons_redeemedSaleId_fkey" FOREIGN KEY ("redeemedSaleId") REFERENCES "sales"("id") ON DELETE SET NULL ON UPDATE CASCADE;
