CREATE TYPE "public"."content_kind" AS ENUM(
  'article',
  'post',
  'comment',
  'reply',
  'review',
  'manual'
);
--> statement-breakpoint
CREATE TYPE "public"."membership_status" AS ENUM('invited', 'active', 'suspended');
--> statement-breakpoint
CREATE TYPE "public"."tenant_status" AS ENUM('active', 'suspended', 'archived');
--> statement-breakpoint
CREATE TABLE "tenants" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
  "slug" varchar(80) NOT NULL,
  "display_name" varchar(200) NOT NULL,
  "status" "tenant_status" DEFAULT 'active' NOT NULL,
  "default_time_zone" varchar(80) DEFAULT 'Africa/Johannesburg' NOT NULL,
  "white_label_enabled" boolean DEFAULT true NOT NULL,
  "created_at" timestamp with time zone DEFAULT now() NOT NULL,
  "updated_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "workspaces" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
  "tenant_id" uuid NOT NULL,
  "slug" varchar(80) NOT NULL,
  "display_name" varchar(200) NOT NULL,
  "created_at" timestamp with time zone DEFAULT now() NOT NULL,
  "updated_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "users" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
  "email" varchar(320) NOT NULL,
  "display_name" varchar(200) NOT NULL,
  "mfa_required" boolean DEFAULT true NOT NULL,
  "created_at" timestamp with time zone DEFAULT now() NOT NULL,
  "updated_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
CREATE TABLE "tenant_memberships" (
  "tenant_id" uuid NOT NULL,
  "user_id" uuid NOT NULL,
  "role" varchar(60) NOT NULL,
  "status" "membership_status" DEFAULT 'active' NOT NULL,
  "created_at" timestamp with time zone DEFAULT now() NOT NULL,
  "updated_at" timestamp with time zone DEFAULT now() NOT NULL,
  CONSTRAINT "tenant_memberships_pk" PRIMARY KEY("tenant_id", "user_id")
);
--> statement-breakpoint
CREATE TABLE "content_items" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
  "tenant_id" uuid NOT NULL,
  "workspace_id" uuid NOT NULL,
  "connector" varchar(40) NOT NULL,
  "source_account_id" varchar(200) NOT NULL,
  "external_id" varchar(240) NOT NULL,
  "kind" "content_kind" NOT NULL,
  "canonical_url" text,
  "captured_text" text,
  "language" varchar(20),
  "synthetic" boolean DEFAULT false NOT NULL,
  "source_published_at" timestamp with time zone,
  "received_at" timestamp with time zone DEFAULT now() NOT NULL,
  "metadata" jsonb DEFAULT '{}'::jsonb NOT NULL
);
--> statement-breakpoint
CREATE TABLE "audit_events" (
  "id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
  "tenant_id" uuid,
  "actor_type" varchar(40) NOT NULL,
  "actor_id" varchar(200),
  "action" varchar(160) NOT NULL,
  "target_type" varchar(80) NOT NULL,
  "target_id" varchar(200),
  "correlation_id" varchar(200),
  "metadata" jsonb DEFAULT '{}'::jsonb NOT NULL,
  "occurred_at" timestamp with time zone DEFAULT now() NOT NULL
);
--> statement-breakpoint
ALTER TABLE "workspaces"
  ADD CONSTRAINT "workspaces_tenant_id_tenants_id_fk"
  FOREIGN KEY ("tenant_id") REFERENCES "public"."tenants"("id")
  ON DELETE restrict ON UPDATE no action;
--> statement-breakpoint
ALTER TABLE "tenant_memberships"
  ADD CONSTRAINT "tenant_memberships_tenant_id_tenants_id_fk"
  FOREIGN KEY ("tenant_id") REFERENCES "public"."tenants"("id")
  ON DELETE cascade ON UPDATE no action;
--> statement-breakpoint
ALTER TABLE "tenant_memberships"
  ADD CONSTRAINT "tenant_memberships_user_id_users_id_fk"
  FOREIGN KEY ("user_id") REFERENCES "public"."users"("id")
  ON DELETE cascade ON UPDATE no action;
--> statement-breakpoint
ALTER TABLE "content_items"
  ADD CONSTRAINT "content_items_tenant_id_tenants_id_fk"
  FOREIGN KEY ("tenant_id") REFERENCES "public"."tenants"("id")
  ON DELETE restrict ON UPDATE no action;
--> statement-breakpoint
ALTER TABLE "content_items"
  ADD CONSTRAINT "content_items_workspace_id_workspaces_id_fk"
  FOREIGN KEY ("workspace_id") REFERENCES "public"."workspaces"("id")
  ON DELETE restrict ON UPDATE no action;
--> statement-breakpoint
ALTER TABLE "audit_events"
  ADD CONSTRAINT "audit_events_tenant_id_tenants_id_fk"
  FOREIGN KEY ("tenant_id") REFERENCES "public"."tenants"("id")
  ON DELETE restrict ON UPDATE no action;
--> statement-breakpoint
CREATE UNIQUE INDEX "tenants_slug_unique" ON "tenants" USING btree ("slug");
--> statement-breakpoint
CREATE UNIQUE INDEX "workspaces_tenant_slug_unique"
  ON "workspaces" USING btree ("tenant_id", "slug");
--> statement-breakpoint
CREATE INDEX "workspaces_tenant_idx" ON "workspaces" USING btree ("tenant_id");
--> statement-breakpoint
CREATE UNIQUE INDEX "users_email_unique" ON "users" USING btree ("email");
--> statement-breakpoint
CREATE INDEX "tenant_memberships_user_idx"
  ON "tenant_memberships" USING btree ("user_id");
--> statement-breakpoint
CREATE UNIQUE INDEX "content_items_provider_identity_unique"
  ON "content_items" USING btree (
    "tenant_id",
    "connector",
    "source_account_id",
    "external_id"
  );
--> statement-breakpoint
CREATE INDEX "content_items_tenant_received_idx"
  ON "content_items" USING btree ("tenant_id", "received_at");
--> statement-breakpoint
CREATE INDEX "content_items_workspace_received_idx"
  ON "content_items" USING btree ("workspace_id", "received_at");
--> statement-breakpoint
CREATE INDEX "audit_events_tenant_occurred_idx"
  ON "audit_events" USING btree ("tenant_id", "occurred_at");
--> statement-breakpoint
CREATE INDEX "audit_events_target_idx"
  ON "audit_events" USING btree ("target_type", "target_id");
--> statement-breakpoint
ALTER TABLE "workspaces" ENABLE ROW LEVEL SECURITY;
--> statement-breakpoint
ALTER TABLE "tenant_memberships" ENABLE ROW LEVEL SECURITY;
--> statement-breakpoint
ALTER TABLE "content_items" ENABLE ROW LEVEL SECURITY;
--> statement-breakpoint
ALTER TABLE "audit_events" ENABLE ROW LEVEL SECURITY;
--> statement-breakpoint
CREATE POLICY "workspaces_tenant_isolation" ON "workspaces"
  USING (
    "tenant_id" = nullif(current_setting('app.current_tenant_id', true), '')::uuid
  )
  WITH CHECK (
    "tenant_id" = nullif(current_setting('app.current_tenant_id', true), '')::uuid
  );
--> statement-breakpoint
CREATE POLICY "memberships_tenant_isolation" ON "tenant_memberships"
  USING (
    "tenant_id" = nullif(current_setting('app.current_tenant_id', true), '')::uuid
  )
  WITH CHECK (
    "tenant_id" = nullif(current_setting('app.current_tenant_id', true), '')::uuid
  );
--> statement-breakpoint
CREATE POLICY "content_items_tenant_isolation" ON "content_items"
  USING (
    "tenant_id" = nullif(current_setting('app.current_tenant_id', true), '')::uuid
  )
  WITH CHECK (
    "tenant_id" = nullif(current_setting('app.current_tenant_id', true), '')::uuid
  );
--> statement-breakpoint
CREATE POLICY "audit_events_tenant_isolation" ON "audit_events"
  USING (
    "tenant_id" = nullif(current_setting('app.current_tenant_id', true), '')::uuid
  )
  WITH CHECK (
    "tenant_id" = nullif(current_setting('app.current_tenant_id', true), '')::uuid
  );
--> statement-breakpoint
CREATE FUNCTION prevent_audit_event_mutation()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  RAISE EXCEPTION 'audit_events are append-only';
END;
$$;
--> statement-breakpoint
CREATE TRIGGER "audit_events_no_update_or_delete"
  BEFORE UPDATE OR DELETE ON "audit_events"
  FOR EACH ROW EXECUTE FUNCTION prevent_audit_event_mutation();