-- CreateEnum
CREATE TYPE "orderType" AS ENUM ('product', 'sample');

-- CreateEnum
CREATE TYPE "paymentStatus" AS ENUM ('paid', 'refunded', 'failed', 'free');

-- CreateEnum
CREATE TYPE "orderStatus" AS ENUM ('fulfilled', 'returned', 'pending', 'cancelled');

-- CreateTable
CREATE TABLE "tbl_order" (
    "id" BIGSERIAL NOT NULL,
    "user_id" BIGINT NOT NULL,
    "brand_id" BIGINT NOT NULL,
    "payment_id" BIGINT,
    "carrier_id" BIGINT,
    "tracking_number" VARCHAR(24),
    "order_no" VARCHAR(8),
    "order_type" "orderType",
    "payment_status" "paymentStatus",
    "order_status" "orderStatus",
    "refund" DECIMAL(13,2),
    "discount" DECIMAL(13,2),
    "sub_total" DECIMAL(13,2),
    "vat_charges" DECIMAL(13,2),
    "total" DECIMAL(13,2),
    "address" VARCHAR(256),
    "billing_user_name" VARCHAR(256),
    "customer_note" VARCHAR(256),
    "internal_note" VARCHAR(256),
    "is_active" BOOLEAN DEFAULT true,
    "is_delete" BOOLEAN DEFAULT false,
    "deleted_at" TIMESTAMP(3),
    "ordered_at" TIMESTAMP(3),
    "refunded_at" TIMESTAMP(3),
    "fulfilled_at" TIMESTAMP(3),
    "pending_at" TIMESTAMP(3),
    "returned_at" TIMESTAMP(3),
    "cancelled_at" TIMESTAMP(3),
    "inserted_at" TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP(3),

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

-- CreateTable
CREATE TABLE "tbl_order_item" (
    "id" BIGSERIAL NOT NULL,
    "order_id" BIGINT NOT NULL,
    "user_id" BIGINT NOT NULL,
    "brand_id" BIGINT NOT NULL,
    "product_id" BIGINT NOT NULL,
    "each_product_price" DECIMAL(13,2),
    "total_product_price" DECIMAL(13,2),
    "qty" INTEGER DEFAULT 1,
    "reason_of_return" VARCHAR(256),
    "return_qty" INTEGER,
    "is_refund_requested" BOOLEAN DEFAULT false,
    "is_active" BOOLEAN DEFAULT true,
    "is_delete" BOOLEAN DEFAULT false,
    "deleted_at" TIMESTAMP(3),
    "inserted_at" TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP(3),

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

-- CreateIndex
CREATE INDEX "tbl_order_user_id_idx" ON "tbl_order"("user_id");

-- CreateIndex
CREATE INDEX "tbl_order_brand_id_idx" ON "tbl_order"("brand_id");

-- CreateIndex
CREATE INDEX "tbl_order_payment_id_idx" ON "tbl_order"("payment_id");

-- CreateIndex
CREATE INDEX "tbl_order_order_no_idx" ON "tbl_order"("order_no");

-- CreateIndex
CREATE INDEX "tbl_order_order_type_idx" ON "tbl_order"("order_type");

-- CreateIndex
CREATE INDEX "tbl_order_payment_status_idx" ON "tbl_order"("payment_status");

-- CreateIndex
CREATE INDEX "tbl_order_order_status_idx" ON "tbl_order"("order_status");

-- CreateIndex
CREATE INDEX "tbl_order_total_idx" ON "tbl_order"("total");

-- CreateIndex
CREATE INDEX "tbl_order_item_user_id_idx" ON "tbl_order_item"("user_id");

-- CreateIndex
CREATE INDEX "tbl_order_item_product_id_idx" ON "tbl_order_item"("product_id");

-- CreateIndex
CREATE INDEX "tbl_order_item_brand_id_idx" ON "tbl_order_item"("brand_id");

-- AddForeignKey
ALTER TABLE "tbl_order" ADD CONSTRAINT "tbl_order_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "tbl_user"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "tbl_order" ADD CONSTRAINT "tbl_order_brand_id_fkey" FOREIGN KEY ("brand_id") REFERENCES "tbl_user"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "tbl_order_item" ADD CONSTRAINT "tbl_order_item_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "tbl_user"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "tbl_order_item" ADD CONSTRAINT "tbl_order_item_brand_id_fkey" FOREIGN KEY ("brand_id") REFERENCES "tbl_user"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "tbl_order_item" ADD CONSTRAINT "tbl_order_item_product_id_fkey" FOREIGN KEY ("product_id") REFERENCES "tbl_product"("id") ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "tbl_order_item" ADD CONSTRAINT "tbl_order_item_order_id_fkey" FOREIGN KEY ("order_id") REFERENCES "tbl_order"("id") ON DELETE CASCADE ON UPDATE CASCADE;
