-- CreateEnum
CREATE TYPE "organizationVisibility" AS ENUM ('public', 'hidden');

-- CreateEnum
CREATE TYPE "deliveryOption" AS ENUM ('flat', 'weight');

-- CreateTable
CREATE TABLE "tbl_product" (
    "id" BIGSERIAL NOT NULL,
    "user_id" BIGINT NOT NULL,
    "product_name" VARCHAR(256),
    "product_size_per_unit" VARCHAR(256),
    "weight_type" VARCHAR(256),
    "weight" DECIMAL(13,2),
    "width_inch" DECIMAL(13,2),
    "height_inch" DECIMAL(13,2),
    "depth_inch" DECIMAL(13,2),
    "width_cm" DECIMAL(13,2),
    "height_cm" DECIMAL(13,2),
    "depth_cm" DECIMAL(13,2),
    "description" TEXT,
    "ingredients" TEXT,
    "diet_ids" VARCHAR(256),
    "nutrition_ids" VARCHAR(256),
    "shelf_life_id" BIGINT,
    "storage_id" BIGINT,
    "production_ids" VARCHAR(256),
    "allergens_ids" VARCHAR(256),
    "values_ids" VARCHAR(256),
    "sku_no" VARCHAR(256),
    "product_origin" VARCHAR(256),
    "product_price" DECIMAL(13,2),
    "is_on_sale" BOOLEAN DEFAULT false,
    "retail_resale_price" DECIMAL(13,2),
    "product_stock" VARCHAR(64),
    "is_unlimited_stock" BOOLEAN DEFAULT false,
    "is_samples" BOOLEAN DEFAULT false,
    "sample_price" DECIMAL(13,2),
    "sample_stock" VARCHAR(64),
    "is_sample_unlimited_stock" BOOLEAN DEFAULT false,
    "fulfilment_id" BIGINT,
    "organization_visibility" "organizationVisibility",
    "category_ids" VARCHAR(256),
    "is_publish" BOOLEAN DEFAULT false,
    "is_featured_product" BOOLEAN DEFAULT false,
    "is_active" BOOLEAN DEFAULT true,
    "is_delete" BOOLEAN DEFAULT false,
    "publish_at" TIMESTAMP(3),
    "deleted_at" TIMESTAMP(3),
    "inserted_at" TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP(3),

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

-- CreateTable
CREATE TABLE "tbl_product_images" (
    "id" BIGSERIAL NOT NULL,
    "product_id" BIGINT NOT NULL,
    "image" VARCHAR(256),
    "is_thumbnail" BOOLEAN DEFAULT false,
    "is_image_link" BOOLEAN DEFAULT false,
    "is_active" BOOLEAN DEFAULT true,
    "inserted_at" TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP(3),

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

-- CreateTable
CREATE TABLE "tbl_fulfilment" (
    "id" BIGSERIAL NOT NULL,
    "delivery_option" "deliveryOption",
    "option_name" VARCHAR(256),
    "per_order_fee" DECIMAL(13,2),
    "per_order_currency" VARCHAR(64),
    "fee_per_item" DECIMAL(13,2),
    "fee_per_item_currency" VARCHAR(64),
    "country_ids" VARCHAR(256),
    "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_fulfilment_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "tbl_fulfilment_weight" (
    "id" BIGSERIAL NOT NULL,
    "fulfilment_id" BIGINT NOT NULL,
    "starting_weight" DECIMAL(13,2),
    "ending_weight" DECIMAL(13,2),
    "cost" DECIMAL(13,2),
    "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_fulfilment_weight_pkey" PRIMARY KEY ("id")
);

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

-- AddForeignKey
ALTER TABLE "tbl_product" ADD CONSTRAINT "tbl_product_shelf_life_id_fkey" FOREIGN KEY ("shelf_life_id") REFERENCES "tbl_dropdown_list"("id") ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "tbl_product" ADD CONSTRAINT "tbl_product_storage_id_fkey" FOREIGN KEY ("storage_id") REFERENCES "tbl_dropdown_list"("id") ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "tbl_product" ADD CONSTRAINT "tbl_product_fulfilment_id_fkey" FOREIGN KEY ("fulfilment_id") REFERENCES "tbl_fulfilment"("id") ON DELETE SET NULL ON UPDATE CASCADE;

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

-- AddForeignKey
ALTER TABLE "tbl_fulfilment_weight" ADD CONSTRAINT "tbl_fulfilment_weight_fulfilment_id_fkey" FOREIGN KEY ("fulfilment_id") REFERENCES "tbl_fulfilment"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
