-- CreateEnum
CREATE TYPE "userType" AS ENUM ('buyer', 'seller', 'buyer_team', 'seller_team');

-- CreateEnum
CREATE TYPE "dropDownType" AS ENUM ('allergen', 'production', 'diet', 'shelf_life', 'storage', 'supplier_value', 'ship_time', 'product_origin', 'here_about_us');

-- CreateTable
CREATE TABLE "country" (
    "id" BIGSERIAL NOT NULL,
    "short_name" VARCHAR(190) NOT NULL,
    "name" VARCHAR(190) NOT NULL,
    "phone_code" VARCHAR(190) NOT NULL,

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

-- CreateTable
CREATE TABLE "state" (
    "id" BIGSERIAL NOT NULL,
    "name" VARCHAR(190) NOT NULL,
    "country_id" BIGINT NOT NULL,

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

-- CreateTable
CREATE TABLE "city" (
    "id" BIGSERIAL NOT NULL,
    "name" VARCHAR(190) NOT NULL,
    "state_id" BIGINT NOT NULL,

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

-- CreateTable
CREATE TABLE "tbl_user" (
    "id" BIGSERIAL NOT NULL,
    "reseller_id" BIGINT DEFAULT 0,
    "parent_id" BIGINT DEFAULT 0,
    "first_name" VARCHAR(216),
    "last_name" VARCHAR(216),
    "email" VARCHAR(256) NOT NULL,
    "password" VARCHAR(256) NOT NULL,
    "country_code" VARCHAR(8),
    "mobile_number" VARCHAR(18),
    "profile_image" VARCHAR(256),
    "type" "userType",
    "business_name" VARCHAR(256),
    "business_email" VARCHAR(256),
    "type_of_store_id" BIGINT,
    "address" VARCHAR(256),
    "city" VARCHAR(128),
    "country" VARCHAR(128),
    "city_id" BIGINT,
    "country_id" BIGINT,
    "state_id" BIGINT,
    "zip_code" VARCHAR(64),
    "wholesale_accounts_products" BIGINT DEFAULT 0,
    "wholesale_products" BIGINT DEFAULT 0,
    "website" VARCHAR(256),
    "minimum_order_amount_currency" VARCHAR(128),
    "minimum_order_amount" DECIMAL(13,2),
    "category_in_stock" VARCHAR(256),
    "permission" VARCHAR(256),
    "note" TEXT,
    "role_id" BIGINT,
    "here_about_us_id" BIGINT,
    "otp" VARCHAR(6),
    "otp_expiry" TIMESTAMP(0),
    "forgot_token" TEXT,
    "forgot_token_expiry" TIMESTAMP(0),
    "forgoted_at" TIMESTAMP(3),
    "is_offering_samples" BOOLEAN DEFAULT false,
    "is_offering_subscription" BOOLEAN DEFAULT false,
    "is_individual_updates_for_order" BOOLEAN DEFAULT false,
    "is_daily_summary_reminder_for_order" BOOLEAN DEFAULT false,
    "is_email_reminder_for_messages" BOOLEAN DEFAULT false,
    "is_email_reminder_for_payment" BOOLEAN DEFAULT false,
    "is_email_reminder_for_new_features" BOOLEAN DEFAULT false,
    "is_email_reminder_for_trending_products" BOOLEAN DEFAULT false,
    "is_account_reviewed_by_admin" BOOLEAN DEFAULT false,
    "is_verify" BOOLEAN DEFAULT false,
    "is_active" BOOLEAN DEFAULT true,
    "is_delete" BOOLEAN DEFAULT false,
    "verified_at" TIMESTAMP(3),
    "deleted_at" TIMESTAMP(3),
    "inserted_at" TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP(3),

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

-- CreateTable
CREATE TABLE "tbl_dropdown_list" (
    "id" BIGSERIAL NOT NULL,
    "name" VARCHAR(256),
    "icon_image" VARCHAR(256),
    "type" "dropDownType",
    "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_dropdown_list_pkey" PRIMARY KEY ("id")
);

-- CreateTable
CREATE TABLE "tbl_role" (
    "id" BIGSERIAL NOT NULL,
    "role_name" 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_role_pkey" PRIMARY KEY ("id")
);

-- CreateIndex
CREATE UNIQUE INDEX "tbl_user_email_key" ON "tbl_user"("email");

-- CreateIndex
CREATE UNIQUE INDEX "tbl_user_mobile_number_key" ON "tbl_user"("mobile_number");

-- AddForeignKey
ALTER TABLE "state" ADD CONSTRAINT "state_country_id_fkey" FOREIGN KEY ("country_id") REFERENCES "country"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "city" ADD CONSTRAINT "city_state_id_fkey" FOREIGN KEY ("state_id") REFERENCES "state"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "tbl_user" ADD CONSTRAINT "tbl_user_country_id_fkey" FOREIGN KEY ("country_id") REFERENCES "country"("id") ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "tbl_user" ADD CONSTRAINT "tbl_user_state_id_fkey" FOREIGN KEY ("state_id") REFERENCES "state"("id") ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "tbl_user" ADD CONSTRAINT "tbl_user_city_id_fkey" FOREIGN KEY ("city_id") REFERENCES "city"("id") ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "tbl_user" ADD CONSTRAINT "tbl_user_role_id_fkey" FOREIGN KEY ("role_id") REFERENCES "tbl_role"("id") ON DELETE SET NULL ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE "tbl_user" ADD CONSTRAINT "tbl_user_here_about_us_id_fkey" FOREIGN KEY ("here_about_us_id") REFERENCES "tbl_dropdown_list"("id") ON DELETE SET NULL ON UPDATE CASCADE;
