637 lines
22 KiB
SQL
637 lines
22 KiB
SQL
-- CreateEnum
|
|
CREATE TYPE "UserType" AS ENUM ('CUSTOMER', 'STAFF', 'ADMIN', 'SUPER_ADMIN');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "UserStatus" AS ENUM ('ACTIVE', 'INVITED', 'SUSPENDED');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "MediaKind" AS ENUM ('IMAGE', 'VIDEO', 'DOCUMENT');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "CollectionType" AS ENUM ('MANUAL', 'AUTOMATED');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "ProductStatus" AS ENUM ('DRAFT', 'ACTIVE', 'ARCHIVED');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "VariantStatus" AS ENUM ('ACTIVE', 'ARCHIVED');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "Currency" AS ENUM ('VND', 'USD');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "GenderTarget" AS ENUM ('MEN', 'WOMEN', 'KIDS', 'UNISEX');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "SportType" AS ENUM ('RUNNING', 'FOOTBALL', 'TRAINING', 'GYM', 'BADMINTON', 'LIFESTYLE');
|
|
|
|
-- CreateEnum
|
|
CREATE TYPE "StockMovementReason" AS ENUM ('PURCHASE_RECEIPT', 'SALE', 'RETURN', 'MANUAL_ADJUSTMENT', 'STOCK_TAKE', 'TRANSFER_IN', 'TRANSFER_OUT', 'DAMAGE');
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "users" (
|
|
"id" UUID NOT NULL,
|
|
"email" VARCHAR(255) NOT NULL,
|
|
"password_hash" TEXT,
|
|
"type" "UserType" NOT NULL,
|
|
"status" "UserStatus" NOT NULL DEFAULT 'ACTIVE',
|
|
"first_name" VARCHAR(80),
|
|
"last_name" VARCHAR(80),
|
|
"phone" VARCHAR(20),
|
|
"avatar_id" UUID,
|
|
"email_verified_at" TIMESTAMPTZ(3),
|
|
"last_login_at" TIMESTAMPTZ(3),
|
|
"created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updated_at" TIMESTAMPTZ(3) NOT NULL,
|
|
"deleted_at" TIMESTAMPTZ(3),
|
|
|
|
CONSTRAINT "users_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "roles" (
|
|
"id" UUID NOT NULL,
|
|
"key" VARCHAR(64) NOT NULL,
|
|
"name" VARCHAR(120) NOT NULL,
|
|
"description" TEXT,
|
|
"is_system" BOOLEAN NOT NULL DEFAULT false,
|
|
"created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updated_at" TIMESTAMPTZ(3) NOT NULL,
|
|
|
|
CONSTRAINT "roles_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "permissions" (
|
|
"id" UUID NOT NULL,
|
|
"key" VARCHAR(64) NOT NULL,
|
|
"resource" VARCHAR(40) NOT NULL,
|
|
"action" VARCHAR(40) NOT NULL,
|
|
"description" TEXT,
|
|
|
|
CONSTRAINT "permissions_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "role_permissions" (
|
|
"role_id" UUID NOT NULL,
|
|
"permission_id" UUID NOT NULL,
|
|
|
|
CONSTRAINT "role_permissions_pkey" PRIMARY KEY ("role_id","permission_id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "user_roles" (
|
|
"user_id" UUID NOT NULL,
|
|
"role_id" UUID NOT NULL,
|
|
"assigned_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "user_roles_pkey" PRIMARY KEY ("user_id","role_id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "sessions" (
|
|
"id" UUID NOT NULL,
|
|
"user_id" UUID NOT NULL,
|
|
"refresh_token_hash" VARCHAR(64) NOT NULL,
|
|
"family_id" UUID NOT NULL,
|
|
"replaced_by_id" UUID,
|
|
"expires_at" TIMESTAMPTZ(3) NOT NULL,
|
|
"revoked_at" TIMESTAMPTZ(3),
|
|
"user_agent" TEXT,
|
|
"ip_address" VARCHAR(45),
|
|
"created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "sessions_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "audit_logs" (
|
|
"id" UUID NOT NULL,
|
|
"actor_user_id" UUID,
|
|
"action" VARCHAR(80) NOT NULL,
|
|
"resource_type" VARCHAR(60) NOT NULL,
|
|
"resource_id" TEXT,
|
|
"changes" JSONB,
|
|
"ip_address" VARCHAR(45),
|
|
"created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "audit_logs_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "customers" (
|
|
"id" UUID NOT NULL,
|
|
"user_id" UUID NOT NULL,
|
|
"accepts_marketing" BOOLEAN NOT NULL DEFAULT false,
|
|
"date_of_birth" DATE,
|
|
"note" TEXT,
|
|
"created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updated_at" TIMESTAMPTZ(3) NOT NULL,
|
|
|
|
CONSTRAINT "customers_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "addresses" (
|
|
"id" UUID NOT NULL,
|
|
"customer_id" UUID NOT NULL,
|
|
"full_name" VARCHAR(160) NOT NULL,
|
|
"phone" VARCHAR(20) NOT NULL,
|
|
"line1" VARCHAR(255) NOT NULL,
|
|
"line2" VARCHAR(255),
|
|
"ward" VARCHAR(120),
|
|
"ward_code" VARCHAR(20),
|
|
"district" VARCHAR(120),
|
|
"district_code" VARCHAR(20),
|
|
"province" VARCHAR(120) NOT NULL,
|
|
"province_code" VARCHAR(20),
|
|
"country_code" CHAR(2) NOT NULL DEFAULT 'VN',
|
|
"postal_code" VARCHAR(20),
|
|
"is_default_shipping" BOOLEAN NOT NULL DEFAULT false,
|
|
"is_default_billing" BOOLEAN NOT NULL DEFAULT false,
|
|
"created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updated_at" TIMESTAMPTZ(3) NOT NULL,
|
|
|
|
CONSTRAINT "addresses_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "media_assets" (
|
|
"id" UUID NOT NULL,
|
|
"kind" "MediaKind" NOT NULL DEFAULT 'IMAGE',
|
|
"storage_key" VARCHAR(512) NOT NULL,
|
|
"mime_type" VARCHAR(120) NOT NULL,
|
|
"size_bytes" INTEGER NOT NULL,
|
|
"width" INTEGER,
|
|
"height" INTEGER,
|
|
"blur_data_url" TEXT,
|
|
"alt_text" VARCHAR(255),
|
|
"metadata" JSONB,
|
|
"uploaded_by_user_id" UUID,
|
|
"created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "media_assets_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "brands" (
|
|
"id" UUID NOT NULL,
|
|
"name" VARCHAR(160) NOT NULL,
|
|
"slug" VARCHAR(180) NOT NULL,
|
|
"description" TEXT,
|
|
"logo_id" UUID,
|
|
"is_active" BOOLEAN NOT NULL DEFAULT true,
|
|
"meta_title" VARCHAR(255),
|
|
"meta_description" TEXT,
|
|
"created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updated_at" TIMESTAMPTZ(3) NOT NULL,
|
|
|
|
CONSTRAINT "brands_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "categories" (
|
|
"id" UUID NOT NULL,
|
|
"parent_id" UUID,
|
|
"name" VARCHAR(160) NOT NULL,
|
|
"slug" VARCHAR(180) NOT NULL,
|
|
"path" VARCHAR(512) NOT NULL,
|
|
"depth" INTEGER NOT NULL DEFAULT 0,
|
|
"position" INTEGER NOT NULL DEFAULT 0,
|
|
"description" TEXT,
|
|
"image_id" UUID,
|
|
"is_active" BOOLEAN NOT NULL DEFAULT true,
|
|
"meta_title" VARCHAR(255),
|
|
"meta_description" TEXT,
|
|
"created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updated_at" TIMESTAMPTZ(3) NOT NULL,
|
|
|
|
CONSTRAINT "categories_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "collections" (
|
|
"id" UUID NOT NULL,
|
|
"name" VARCHAR(160) NOT NULL,
|
|
"slug" VARCHAR(180) NOT NULL,
|
|
"type" "CollectionType" NOT NULL DEFAULT 'MANUAL',
|
|
"description" TEXT,
|
|
"banner_id" UUID,
|
|
"rules" JSONB,
|
|
"starts_at" TIMESTAMPTZ(3),
|
|
"ends_at" TIMESTAMPTZ(3),
|
|
"is_active" BOOLEAN NOT NULL DEFAULT true,
|
|
"position" INTEGER NOT NULL DEFAULT 0,
|
|
"meta_title" VARCHAR(255),
|
|
"meta_description" TEXT,
|
|
"created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updated_at" TIMESTAMPTZ(3) NOT NULL,
|
|
|
|
CONSTRAINT "collections_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "product_collections" (
|
|
"product_id" UUID NOT NULL,
|
|
"collection_id" UUID NOT NULL,
|
|
"position" INTEGER NOT NULL DEFAULT 0,
|
|
|
|
CONSTRAINT "product_collections_pkey" PRIMARY KEY ("product_id","collection_id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "products" (
|
|
"id" UUID NOT NULL,
|
|
"name" VARCHAR(255) NOT NULL,
|
|
"slug" VARCHAR(280) NOT NULL,
|
|
"description" TEXT,
|
|
"short_description" VARCHAR(500),
|
|
"status" "ProductStatus" NOT NULL DEFAULT 'DRAFT',
|
|
"published_at" TIMESTAMPTZ(3),
|
|
"brand_id" UUID,
|
|
"primary_category_id" UUID,
|
|
"gender_targets" "GenderTarget"[],
|
|
"sport_types" "SportType"[],
|
|
"meta_title" VARCHAR(255),
|
|
"meta_description" TEXT,
|
|
"metadata" JSONB,
|
|
"created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updated_at" TIMESTAMPTZ(3) NOT NULL,
|
|
"deleted_at" TIMESTAMPTZ(3),
|
|
|
|
CONSTRAINT "products_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "product_options" (
|
|
"id" UUID NOT NULL,
|
|
"product_id" UUID NOT NULL,
|
|
"name" VARCHAR(60) NOT NULL,
|
|
"key" VARCHAR(40) NOT NULL,
|
|
"position" INTEGER NOT NULL DEFAULT 0,
|
|
|
|
CONSTRAINT "product_options_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "product_option_values" (
|
|
"id" UUID NOT NULL,
|
|
"option_id" UUID NOT NULL,
|
|
"label" VARCHAR(80) NOT NULL,
|
|
"value" VARCHAR(80) NOT NULL,
|
|
"position" INTEGER NOT NULL DEFAULT 0,
|
|
"swatch_hex" VARCHAR(9),
|
|
"swatch_image_id" UUID,
|
|
|
|
CONSTRAINT "product_option_values_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "product_variants" (
|
|
"id" UUID NOT NULL,
|
|
"product_id" UUID NOT NULL,
|
|
"sku" VARCHAR(64) NOT NULL,
|
|
"barcode" VARCHAR(64),
|
|
"title" VARCHAR(255) NOT NULL,
|
|
"currency" "Currency" NOT NULL DEFAULT 'VND',
|
|
"price_amount" INTEGER NOT NULL,
|
|
"sale_price_amount" INTEGER,
|
|
"compare_at_amount" INTEGER,
|
|
"cost_amount" INTEGER,
|
|
"weight_grams" INTEGER,
|
|
"length_mm" INTEGER,
|
|
"width_mm" INTEGER,
|
|
"height_mm" INTEGER,
|
|
"status" "VariantStatus" NOT NULL DEFAULT 'ACTIVE',
|
|
"position" INTEGER NOT NULL DEFAULT 0,
|
|
"created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updated_at" TIMESTAMPTZ(3) NOT NULL,
|
|
"deleted_at" TIMESTAMPTZ(3),
|
|
|
|
CONSTRAINT "product_variants_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "product_variant_option_values" (
|
|
"variant_id" UUID NOT NULL,
|
|
"option_id" UUID NOT NULL,
|
|
"option_value_id" UUID NOT NULL,
|
|
|
|
CONSTRAINT "product_variant_option_values_pkey" PRIMARY KEY ("variant_id","option_id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "product_images" (
|
|
"id" UUID NOT NULL,
|
|
"product_id" UUID NOT NULL,
|
|
"media_id" UUID NOT NULL,
|
|
"option_value_id" UUID,
|
|
"position" INTEGER NOT NULL DEFAULT 0,
|
|
|
|
CONSTRAINT "product_images_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "product_attributes" (
|
|
"id" UUID NOT NULL,
|
|
"product_id" UUID NOT NULL,
|
|
"key" VARCHAR(60) NOT NULL,
|
|
"label" VARCHAR(120) NOT NULL,
|
|
"value" VARCHAR(500) NOT NULL,
|
|
"group" VARCHAR(60),
|
|
"position" INTEGER NOT NULL DEFAULT 0,
|
|
"is_filterable" BOOLEAN NOT NULL DEFAULT false,
|
|
|
|
CONSTRAINT "product_attributes_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "inventory_locations" (
|
|
"id" UUID NOT NULL,
|
|
"name" VARCHAR(120) NOT NULL,
|
|
"code" VARCHAR(40) NOT NULL,
|
|
"is_default" BOOLEAN NOT NULL DEFAULT false,
|
|
"is_active" BOOLEAN NOT NULL DEFAULT true,
|
|
"created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updated_at" TIMESTAMPTZ(3) NOT NULL,
|
|
|
|
CONSTRAINT "inventory_locations_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "stock_levels" (
|
|
"variant_id" UUID NOT NULL,
|
|
"location_id" UUID NOT NULL,
|
|
"on_hand" INTEGER NOT NULL DEFAULT 0,
|
|
"reserved" INTEGER NOT NULL DEFAULT 0,
|
|
"reorder_point" INTEGER,
|
|
"updated_at" TIMESTAMPTZ(3) NOT NULL,
|
|
|
|
CONSTRAINT "stock_levels_pkey" PRIMARY KEY ("variant_id","location_id")
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "stock_movements" (
|
|
"id" UUID NOT NULL,
|
|
"variant_id" UUID NOT NULL,
|
|
"location_id" UUID NOT NULL,
|
|
"quantity_delta" INTEGER NOT NULL,
|
|
"reason" "StockMovementReason" NOT NULL,
|
|
"reference_id" TEXT,
|
|
"note" TEXT,
|
|
"created_by_user_id" UUID,
|
|
"created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT "stock_movements_pkey" PRIMARY KEY ("id")
|
|
);
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "users_email_key" ON "users"("email");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "users_type_status_idx" ON "users"("type", "status");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "users_created_at_idx" ON "users"("created_at");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "roles_key_key" ON "roles"("key");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "permissions_key_key" ON "permissions"("key");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "permissions_resource_idx" ON "permissions"("resource");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "role_permissions_permission_id_idx" ON "role_permissions"("permission_id");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "user_roles_role_id_idx" ON "user_roles"("role_id");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "sessions_refresh_token_hash_key" ON "sessions"("refresh_token_hash");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "sessions_replaced_by_id_key" ON "sessions"("replaced_by_id");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "sessions_user_id_revoked_at_idx" ON "sessions"("user_id", "revoked_at");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "sessions_family_id_idx" ON "sessions"("family_id");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "sessions_expires_at_idx" ON "sessions"("expires_at");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "audit_logs_resource_type_resource_id_idx" ON "audit_logs"("resource_type", "resource_id");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "audit_logs_actor_user_id_created_at_idx" ON "audit_logs"("actor_user_id", "created_at");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "customers_user_id_key" ON "customers"("user_id");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "addresses_customer_id_idx" ON "addresses"("customer_id");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "media_assets_storage_key_key" ON "media_assets"("storage_key");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "media_assets_kind_created_at_idx" ON "media_assets"("kind", "created_at");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "brands_slug_key" ON "brands"("slug");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "categories_path_key" ON "categories"("path");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "categories_path_idx" ON "categories"("path");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "categories_parent_id_position_idx" ON "categories"("parent_id", "position");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "categories_parent_id_slug_key" ON "categories"("parent_id", "slug");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "collections_slug_key" ON "collections"("slug");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "collections_is_active_starts_at_ends_at_idx" ON "collections"("is_active", "starts_at", "ends_at");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "product_collections_collection_id_position_idx" ON "product_collections"("collection_id", "position");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "products_slug_key" ON "products"("slug");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "products_status_published_at_idx" ON "products"("status", "published_at");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "products_brand_id_idx" ON "products"("brand_id");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "products_primary_category_id_idx" ON "products"("primary_category_id");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "products_gender_targets_idx" ON "products" USING GIN ("gender_targets");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "products_sport_types_idx" ON "products" USING GIN ("sport_types");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "product_options_product_id_position_idx" ON "product_options"("product_id", "position");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "product_options_product_id_key_key" ON "product_options"("product_id", "key");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "product_option_values_option_id_position_idx" ON "product_option_values"("option_id", "position");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "product_option_values_option_id_value_key" ON "product_option_values"("option_id", "value");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "product_variants_sku_key" ON "product_variants"("sku");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "product_variants_barcode_key" ON "product_variants"("barcode");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "product_variants_product_id_position_idx" ON "product_variants"("product_id", "position");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "product_variants_status_idx" ON "product_variants"("status");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "product_variant_option_values_option_value_id_idx" ON "product_variant_option_values"("option_value_id");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "product_images_product_id_position_idx" ON "product_images"("product_id", "position");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "product_images_product_id_media_id_option_value_id_key" ON "product_images"("product_id", "media_id", "option_value_id");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "product_attributes_key_value_idx" ON "product_attributes"("key", "value");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "product_attributes_product_id_key_key" ON "product_attributes"("product_id", "key");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "inventory_locations_code_key" ON "inventory_locations"("code");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "stock_levels_location_id_idx" ON "stock_levels"("location_id");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "stock_movements_variant_id_created_at_idx" ON "stock_movements"("variant_id", "created_at");
|
|
|
|
-- CreateIndex
|
|
CREATE INDEX "stock_movements_reference_id_idx" ON "stock_movements"("reference_id");
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "users" ADD CONSTRAINT "users_avatar_id_fkey" FOREIGN KEY ("avatar_id") REFERENCES "media_assets"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "role_permissions" ADD CONSTRAINT "role_permissions_role_id_fkey" FOREIGN KEY ("role_id") REFERENCES "roles"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "role_permissions" ADD CONSTRAINT "role_permissions_permission_id_fkey" FOREIGN KEY ("permission_id") REFERENCES "permissions"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "user_roles" ADD CONSTRAINT "user_roles_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "users"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "user_roles" ADD CONSTRAINT "user_roles_role_id_fkey" FOREIGN KEY ("role_id") REFERENCES "roles"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "sessions" ADD CONSTRAINT "sessions_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "users"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "sessions" ADD CONSTRAINT "sessions_replaced_by_id_fkey" FOREIGN KEY ("replaced_by_id") REFERENCES "sessions"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "audit_logs" ADD CONSTRAINT "audit_logs_actor_user_id_fkey" FOREIGN KEY ("actor_user_id") REFERENCES "users"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "customers" ADD CONSTRAINT "customers_user_id_fkey" FOREIGN KEY ("user_id") REFERENCES "users"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "addresses" ADD CONSTRAINT "addresses_customer_id_fkey" FOREIGN KEY ("customer_id") REFERENCES "customers"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "brands" ADD CONSTRAINT "brands_logo_id_fkey" FOREIGN KEY ("logo_id") REFERENCES "media_assets"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "categories" ADD CONSTRAINT "categories_parent_id_fkey" FOREIGN KEY ("parent_id") REFERENCES "categories"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "categories" ADD CONSTRAINT "categories_image_id_fkey" FOREIGN KEY ("image_id") REFERENCES "media_assets"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "collections" ADD CONSTRAINT "collections_banner_id_fkey" FOREIGN KEY ("banner_id") REFERENCES "media_assets"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "product_collections" ADD CONSTRAINT "product_collections_product_id_fkey" FOREIGN KEY ("product_id") REFERENCES "products"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "product_collections" ADD CONSTRAINT "product_collections_collection_id_fkey" FOREIGN KEY ("collection_id") REFERENCES "collections"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "products" ADD CONSTRAINT "products_brand_id_fkey" FOREIGN KEY ("brand_id") REFERENCES "brands"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "products" ADD CONSTRAINT "products_primary_category_id_fkey" FOREIGN KEY ("primary_category_id") REFERENCES "categories"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "product_options" ADD CONSTRAINT "product_options_product_id_fkey" FOREIGN KEY ("product_id") REFERENCES "products"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "product_option_values" ADD CONSTRAINT "product_option_values_option_id_fkey" FOREIGN KEY ("option_id") REFERENCES "product_options"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "product_option_values" ADD CONSTRAINT "product_option_values_swatch_image_id_fkey" FOREIGN KEY ("swatch_image_id") REFERENCES "media_assets"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "product_variants" ADD CONSTRAINT "product_variants_product_id_fkey" FOREIGN KEY ("product_id") REFERENCES "products"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "product_variant_option_values" ADD CONSTRAINT "product_variant_option_values_variant_id_fkey" FOREIGN KEY ("variant_id") REFERENCES "product_variants"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "product_variant_option_values" ADD CONSTRAINT "product_variant_option_values_option_id_fkey" FOREIGN KEY ("option_id") REFERENCES "product_options"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "product_variant_option_values" ADD CONSTRAINT "product_variant_option_values_option_value_id_fkey" FOREIGN KEY ("option_value_id") REFERENCES "product_option_values"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "product_images" ADD CONSTRAINT "product_images_product_id_fkey" FOREIGN KEY ("product_id") REFERENCES "products"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "product_images" ADD CONSTRAINT "product_images_media_id_fkey" FOREIGN KEY ("media_id") REFERENCES "media_assets"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "product_images" ADD CONSTRAINT "product_images_option_value_id_fkey" FOREIGN KEY ("option_value_id") REFERENCES "product_option_values"("id") ON DELETE SET NULL ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "product_attributes" ADD CONSTRAINT "product_attributes_product_id_fkey" FOREIGN KEY ("product_id") REFERENCES "products"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "stock_levels" ADD CONSTRAINT "stock_levels_variant_id_fkey" FOREIGN KEY ("variant_id") REFERENCES "product_variants"("id") ON DELETE CASCADE ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "stock_levels" ADD CONSTRAINT "stock_levels_location_id_fkey" FOREIGN KEY ("location_id") REFERENCES "inventory_locations"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "stock_movements" ADD CONSTRAINT "stock_movements_variant_id_fkey" FOREIGN KEY ("variant_id") REFERENCES "product_variants"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|
|
|
|
-- AddForeignKey
|
|
ALTER TABLE "stock_movements" ADD CONSTRAINT "stock_movements_location_id_fkey" FOREIGN KEY ("location_id") REFERENCES "inventory_locations"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
|