CREATE EXTENSION IF NOT EXISTS postgis;
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS unaccent;

CREATE TYPE "OutboxStatus" AS ENUM ('PENDING', 'PROCESSING', 'PROCESSED', 'FAILED');

CREATE TABLE "identity_users" (
  "id" UUID NOT NULL,
  "phone" VARCHAR(20) NOT NULL,
  "display_name" VARCHAR(120) NOT NULL,
  "roles" TEXT[] NOT NULL,
  "permissions" TEXT[] NOT NULL,
  "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "updated_at" TIMESTAMPTZ(3) NOT NULL,
  CONSTRAINT "identity_users_pkey" PRIMARY KEY ("id")
);

CREATE UNIQUE INDEX "identity_users_phone_key" ON "identity_users"("phone");

CREATE TABLE "platform_outbox_events" (
  "id" UUID NOT NULL,
  "event_type" VARCHAR(160) NOT NULL,
  "aggregate_type" VARCHAR(100) NOT NULL,
  "aggregate_id" VARCHAR(160) NOT NULL,
  "payload" JSONB NOT NULL,
  "status" "OutboxStatus" NOT NULL DEFAULT 'PENDING',
  "attempts" INTEGER NOT NULL DEFAULT 0,
  "available_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "locked_at" TIMESTAMPTZ(3),
  "locked_by" VARCHAR(120),
  "processed_at" TIMESTAMPTZ(3),
  "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT "platform_outbox_events_pkey" PRIMARY KEY ("id")
);

CREATE INDEX "platform_outbox_events_status_available_at_idx"
  ON "platform_outbox_events"("status", "available_at");
CREATE INDEX "platform_outbox_events_aggregate_type_aggregate_id_idx"
  ON "platform_outbox_events"("aggregate_type", "aggregate_id");

CREATE TABLE "platform_processed_events" (
  "event_id" UUID NOT NULL,
  "processor" VARCHAR(120) NOT NULL,
  "processed_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT "platform_processed_events_pkey" PRIMARY KEY ("event_id")
);

CREATE TABLE "integrations_external_requests" (
  "id" UUID NOT NULL,
  "provider" VARCHAR(80) NOT NULL,
  "operation" VARCHAR(120) NOT NULL,
  "idempotency_key" VARCHAR(160),
  "tracking_id" VARCHAR(160),
  "status" VARCHAR(40) NOT NULL,
  "response" JSONB,
  "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  "updated_at" TIMESTAMPTZ(3) NOT NULL,
  CONSTRAINT "integrations_external_requests_pkey" PRIMARY KEY ("id")
);

CREATE UNIQUE INDEX "integrations_external_requests_provider_operation_idempotency_key_key"
  ON "integrations_external_requests"("provider", "operation", "idempotency_key");

CREATE TABLE "audit_logs" (
  "id" UUID NOT NULL,
  "actor_id" UUID,
  "action" VARCHAR(160) NOT NULL,
  "aggregate_type" VARCHAR(100) NOT NULL,
  "aggregate_id" VARCHAR(160) NOT NULL,
  "correlation_id" UUID NOT NULL,
  "metadata" JSONB,
  "created_at" TIMESTAMPTZ(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT "audit_logs_pkey" PRIMARY KEY ("id")
);

CREATE INDEX "audit_logs_aggregate_type_aggregate_id_created_at_idx"
  ON "audit_logs"("aggregate_type", "aggregate_id", "created_at");