-- Create asset_categories table
CREATE TABLE "public"."asset_categories" (
  "id" uuid NOT NULL DEFAULT gen_random_uuid(),
  "name" text NOT NULL,
  "icon" text,
  "color" text,
  "card_type" text,
  "featured" boolean NOT NULL DEFAULT false,
  "description" text,
  "created_at" timestamptz NOT NULL DEFAULT now(),
  "updated_at" timestamptz NOT NULL DEFAULT now(),
  "deleted_at" timestamptz,
  PRIMARY KEY ("id")
);
CREATE INDEX "asset_categories_deleted_at_idx" ON "public"."asset_categories" ("deleted_at");

-- Create assets table (references profiles and asset_categories)
CREATE TABLE "public"."assets" (
  "id" uuid NOT NULL DEFAULT gen_random_uuid(),
  "profile_id" uuid NOT NULL,
  "status" integer NOT NULL DEFAULT 0,
  "asset_category_id" uuid NOT NULL,
  "title" text NOT NULL,
  "description" text,
  "link" text,
  "analytics" jsonb,
  "created_at" timestamptz NOT NULL DEFAULT now(),
  "updated_at" timestamptz NOT NULL DEFAULT now(),
  "deleted_at" timestamptz,
  PRIMARY KEY ("id"),
  CONSTRAINT "assets_profile_id_fk" FOREIGN KEY ("profile_id") REFERENCES "public"."profiles" ("id") ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT "assets_asset_category_id_fk" FOREIGN KEY ("asset_category_id") REFERENCES "public"."asset_categories" ("id") ON UPDATE CASCADE ON DELETE CASCADE
);
CREATE INDEX "assets_profile_id_idx" ON "public"."assets" ("profile_id");
CREATE INDEX "assets_category_id_idx" ON "public"."assets" ("asset_category_id");
CREATE INDEX "assets_deleted_at_idx" ON "public"."assets" ("deleted_at");

-- Create asset_artifacts table
CREATE TABLE "public"."asset_artifacts" (
  "id" uuid NOT NULL DEFAULT gen_random_uuid(),
  "asset_id" uuid NOT NULL,
  "type" text NOT NULL,
  "download_url" text,
  "price" integer NOT NULL DEFAULT 0,
  "title" text,
  "description" text,
  "created_at" timestamptz NOT NULL DEFAULT now(),
  "updated_at" timestamptz NOT NULL DEFAULT now(),
  "deleted_at" timestamptz,
  PRIMARY KEY ("id"),
  CONSTRAINT "asset_artifacts_asset_id_fk" FOREIGN KEY ("asset_id") REFERENCES "public"."assets" ("id") ON UPDATE CASCADE ON DELETE CASCADE
);
CREATE INDEX "asset_artifacts_asset_id_idx" ON "public"."asset_artifacts" ("asset_id");
CREATE INDEX "asset_artifacts_deleted_at_idx" ON "public"."asset_artifacts" ("deleted_at");