CREATE TYPE "SocialReportTargetType" AS ENUM ('POST', 'COMMENT');
CREATE TYPE "SocialReportReason" AS ENUM ('SPAM', 'NUDITY', 'VIOLENCE', 'HATE', 'HARASSMENT', 'FALSE_INFORMATION', 'SCAM', 'INTELLECTUAL_PROPERTY', 'OTHER');
CREATE TYPE "SocialReportStatus" AS ENUM ('PENDING', 'REVIEWING', 'RESOLVED', 'REJECTED');

CREATE TABLE "social_content_reports" (
    "id" UUID NOT NULL,
    "reporter_id" UUID NOT NULL,
    "target_type" "SocialReportTargetType" NOT NULL,
    "target_id" UUID NOT NULL,
    "reason" "SocialReportReason" NOT NULL,
    "details" VARCHAR(500),
    "status" "SocialReportStatus" NOT NULL DEFAULT 'PENDING',
    "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMPTZ(3) NOT NULL,

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

CREATE UNIQUE INDEX "social_content_reports_reporter_id_target_type_target_id_key"
ON "social_content_reports"("reporter_id", "target_type", "target_id");
CREATE INDEX "social_content_reports_status_created_at_idx"
ON "social_content_reports"("status", "created_at");
CREATE INDEX "social_content_reports_target_type_target_id_idx"
ON "social_content_reports"("target_type", "target_id");

ALTER TABLE "social_content_reports"
ADD CONSTRAINT "social_content_reports_reporter_id_fkey"
FOREIGN KEY ("reporter_id") REFERENCES "identity_users"("id")
ON DELETE CASCADE ON UPDATE CASCADE;