-- CreateEnum
CREATE TYPE "TaxMode" AS ENUM ('INCLUSIVE', 'EXCLUSIVE');

-- CreateTable
CREATE TABLE "commerce_events" (
    "id" UUID NOT NULL,
    "organization_id" UUID NOT NULL,
    "order_id" UUID NOT NULL,
    "kind" VARCHAR(80) NOT NULL,
    "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

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

-- CreateTable
CREATE TABLE "event_deliveries" (
    "event_id" UUID NOT NULL,
    "attempts" INTEGER NOT NULL DEFAULT 0,
    "available_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "lease_token" UUID,
    "lease_expires_at" TIMESTAMPTZ(3),
    "delivered_at" TIMESTAMPTZ(3),
    "last_error_code" VARCHAR(80),

    CONSTRAINT "event_deliveries_pkey" PRIMARY KEY ("event_id")
);

-- CreateTable
CREATE TABLE "pricing_policies" (
    "id" UUID NOT NULL,
    "organization_id" UUID NOT NULL,
    "name" VARCHAR(100) NOT NULL,
    "currency" CHAR(3) NOT NULL,
    "country_code" CHAR(2) NOT NULL,
    "region" VARCHAR(100) NOT NULL DEFAULT '',
    "tax_mode" "TaxMode" NOT NULL,
    "merchandise_tax_bps" INTEGER NOT NULL,
    "shipping_fee" DECIMAL(12,2) NOT NULL,
    "shipping_tax_bps" INTEGER NOT NULL,
    "free_shipping_minimum" DECIMAL(12,2),
    "active" BOOLEAN NOT NULL DEFAULT false,
    "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

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

-- CreateTable
CREATE TABLE "order_pricing" (
    "order_id" UUID NOT NULL,
    "organization_id" UUID NOT NULL,
    "policy_id" UUID NOT NULL,
    "policy_snapshot" JSONB NOT NULL,
    "merchandise_tax" DECIMAL(16,2) NOT NULL,
    "shipping_net" DECIMAL(16,2) NOT NULL,
    "shipping_tax" DECIMAL(16,2) NOT NULL,
    "tax_total" DECIMAL(16,2) NOT NULL,
    "payable_total" DECIMAL(16,2) NOT NULL,
    "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT "order_pricing_pkey" PRIMARY KEY ("order_id")
);

-- CreateIndex
CREATE INDEX "commerce_events_organization_id_created_at_id_idx" ON "commerce_events"("organization_id", "created_at", "id");

-- CreateIndex
CREATE UNIQUE INDEX "commerce_events_order_id_kind_key" ON "commerce_events"("order_id", "kind");

-- CreateIndex
CREATE INDEX "event_deliveries_delivered_at_available_at_lease_expires_at_idx" ON "event_deliveries"("delivered_at", "available_at", "lease_expires_at");

-- CreateIndex
CREATE INDEX "pricing_policies_organization_id_currency_country_code_regi_idx" ON "pricing_policies"("organization_id", "currency", "country_code", "region", "active");

-- CreateIndex
CREATE UNIQUE INDEX "pricing_policies_id_organization_id_key" ON "pricing_policies"("id", "organization_id");

-- CreateIndex
CREATE UNIQUE INDEX "order_pricing_order_id_organization_id_key" ON "order_pricing"("order_id", "organization_id");

-- AddForeignKey
ALTER TABLE "commerce_events" ADD CONSTRAINT "commerce_events_order_id_organization_id_fkey" FOREIGN KEY ("order_id", "organization_id") REFERENCES "orders"("id", "organization_id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "event_deliveries" ADD CONSTRAINT "event_deliveries_event_id_fkey" FOREIGN KEY ("event_id") REFERENCES "commerce_events"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "pricing_policies" ADD CONSTRAINT "pricing_policies_organization_id_fkey" FOREIGN KEY ("organization_id") REFERENCES "organizations"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "order_pricing" ADD CONSTRAINT "order_pricing_order_id_organization_id_fkey" FOREIGN KEY ("order_id", "organization_id") REFERENCES "orders"("id", "organization_id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "order_pricing" ADD CONSTRAINT "order_pricing_policy_id_organization_id_fkey" FOREIGN KEY ("policy_id", "organization_id") REFERENCES "pricing_policies"("id", "organization_id") ON DELETE RESTRICT ON UPDATE CASCADE;